All language subtitles for [SubtitleTools.com] The Data Dictionary - Learning Oracle 12c [Video]

af Afrikaans
ak Akan
sq Albanian
am Amharic
hy Armenian
az Azerbaijani
eu Basque
be Belarusian
bem Bemba
bn Bengali
bh Bihari
bs Bosnian
br Breton
bg Bulgarian
km Cambodian
ca Catalan
ceb Cebuano
chr Cherokee
ny Chichewa
zh-CN Chinese (Simplified)
zh-TW Chinese (Traditional)
co Corsican
hr Croatian
cs Czech
da Danish
nl Dutch
en English
eo Esperanto
et Estonian
ee Ewe
fo Faroese
tl Filipino
fi Finnish
fr French
fy Frisian
gaa Ga
gl Galician
ka Georgian
de German
el Greek
gn Guarani
gu Gujarati
ht Haitian Creole
ha Hausa
haw Hawaiian
iw Hebrew
hi Hindi
hmn Hmong
hu Hungarian
is Icelandic
ig Igbo
id Indonesian
ia Interlingua
ga Irish
it Italian
ja Japanese
jw Javanese
kn Kannada
kk Kazakh
rw Kinyarwanda
rn Kirundi
kg Kongo
ko Korean
kri Krio (Sierra Leone)
ku Kurdish
ckb Kurdish (Soranรฎ)
ky Kyrgyz
lo Laothian
la Latin
lv Latvian
ln Lingala
lt Lithuanian
loz Lozi
lg Luganda
ach Luo
lb Luxembourgish
mk Macedonian
mg Malagasy
ms Malay
ml Malayalam
mt Maltese
mi Maori
mr Marathi
mfe Mauritian Creole
mo Moldavian
mn Mongolian
my Myanmar (Burmese)
sr-ME Montenegrin
ne Nepali
pcm Nigerian Pidgin
nso Northern Sotho
no Norwegian
nn Norwegian (Nynorsk)
oc Occitan
or Oriya
om Oromo
ps Pashto
fa Persian
pl Polish
pt-BR Portuguese (Brazil)
pt Portuguese (Portugal)
pa Punjabi
qu Quechua
ro Romanian
rm Romansh
nyn Runyakitara
ru Russian
sm Samoan
gd Scots Gaelic
sr Serbian
sh Serbo-Croatian
st Sesotho
tn Setswana
crs Seychellois Creole
sn Shona
sd Sindhi
si Sinhalese
sk Slovak
sl Slovenian
so Somali
es Spanish
es-419 Spanish (Latin American)
su Sundanese
sw Swahili
sv Swedish
tg Tajik
ta Tamil
tt Tatar
te Telugu
th Thai
ti Tigrinya
to Tonga
lua Tshiluba
tum Tumbuka
tr Turkish
tk Turkmen
tw Twi
ug Uighur
uk Ukrainian
ur Urdu
uz Uzbek
vi Vietnamese
cy Welsh
wo Wolof
xh Xhosa
yi Yiddish
yo Yoruba
zu Zulu

Original subtitles

In this lesson, we'll be taking a look

at the data dictionary, particularly

the data dictionary views, as we refer to them.

The data dictionary is an extremely important part

of any DBA's tool kit because it is

their source for nearly all the information in the database.

Anything that you want to know about the Oracle database,

other than error log information,

would be found in the data dictionary.

So that could be anything from information about tables,

the columns on tables, the constraints on tables, indexes,

PL SQL objects, even the PL SQL code.

All of that's stored in the data dictionary, as well

as structural information, like table space information,

data files, redo logs, archive logs.

All of that is in the data dictionary.

And learning to leverage the data dictionary

is really a career-long effort for a DBA

because the data dictionary is vast.

It is huge, and there are many, many hundreds, even thousands

of data dictionary views.

But it's important that we understand

that we don't have to master all of the data dictionary views

in order to get useful information from it.

There are the basic data dictionary

views that we sort of pick up through experience

and a little bit of research and some learning,

and that can help us with a lot of information

just right there.

So I wanted to say a word about GUI tools at this point

and give kind of my opinion on this.

There's always the question between GUI tools

and command line, one of them having

more ease of use in the GUI tool and the other

having more power in the command line.

So what we are talking about here

when we are learning the data dictionary

is primarily command-line information

because we're going to use SELECT statements to gather

information from the data dictionary.

In my experience, the use of GUI tools has its place

and can allow a DBA to do things quicker and easier.

But they should not be a crutch.

They should not be something that a DBA relies on solely,

and I have seen that in my career, DBAs

that were really limited because they could only

use a certain tool to gather the information

they needed to do, at least in any kind of effective way.

The data dictionary has the benefit over the GUI tools,

I think, for this reason, because a GUI tool will always

be limited by the designer of it.

So it will only be able to do the things, the tasks

and find the information, that the creator intended,

whereas the data dictionary itself, though much more

complex, is unlimited in the information that we can get.

So learning to rely on the data dictionary

can be an important thing for DBAs,

and both have their place.

But the data dictionary should never

be neglected just because it's somewhat more complex to learn.

So what is the data dictionary now

that we've outlined its importance?

Well, the data dictionary is a set of views

that overlay Oracle base tables.

So the Oracle base tables are the actual tables

that store the information in the database.

So they store everything about the database that's going on,

that exists in the database, that occurs in the database.

All of those things are stored in the Oracle base tables.

But the base tables are definitely not user friendly.

They're not something we would want

to query directly primarily because they're

very difficult to read.

They begin with names like X$ or end with dollar signs,

and their column names are not descriptive.

The data, even, that's in the tables themselves,

it can be very difficult to make any sense of that.

And so that's why Oracle created the data dictionary

as a set of views that overlay that information.

And while not easy to use in every case,

the data dictionary views are much more user friendly

and much easier to get information.

So there's two types of data dictionary view, static views

and dynamic views.

When we talk about static data dictionary views,

we're primarily talking about views

that hold information that does not change moment to moment.

So information about tables and indexes,

database objects, those kinds of things

we would find in static data dictionary views.

Now, obviously, that information does change.

Tables are added.

Indexes are added.

Columns are removed.

Things do change in static data dictionary views,

so they're not truly static.

But we wouldn't normally think of the structure of tables

and the existence of tables as changing moment by moment.

So those are usually what we classify under the static data

dictionary views.

There is some gray areas and some overlap.

But for the most part, that's the kind

of data that would be in a static data dictionary view.

So the first type of static data dictionary view would be

the USER_ views.

So the USER_ views are going to contain information primarily

about database objects owned by the user that logs in.

So for instance, if I logged in as the Scott user

and selected star from user_tables,

I will get a list of table information about the tables

that I own or that the Scott user owns.

So we could say that those user_views

are going to be schema-specific information.

If you log in as Kara, you would see Kara's tables.

If you log in as Scott, you would see Scott's tables.

The second type of static data dictionary view are the ALL_

views.

And the ALL_ views are sort of like a superset

of the user_views because the ALL views will give you access

to all of the information about objects, generally,

that you have access to.

So if the Scott user logs in and selects from the ALL_ views,

he would see not only the tables that he owns,

but information about tables that he has access to, that

he's been granted access to.

The third type of static data dictionary views are the DBA_

views.

And the DBA_ views are limited only to those users with

the DBA role.

So they have to have been granted the DBA role in order

to see the information in the DBA_ views.

So they are powerful views, and they

tend to contain all of the information

about a particular object.

So if we select star from dba_tables,

we see a list of all of the tables in the database.

The second type of data dictionary view

is the dynamic data dictionary views,

and these are called dynamic because they

tend to have information that changes

on a moment-by-moment basis.

So there is no V$ tables view.

That information is more reserved for the static views.

V$ views would have information like session information,

process information.

They are formally referred to, and sometimes

still are, as the dynamic performance views

because they tend to have information that's

used in performance tuning, so information

about what various sessions are waiting

on in the database, the various actions and activities that

are occurring on a system level, all of those types of things.

And the GV$ views, and the G standing for Global,

are the V$ views that are used in RAC.

So in any given instance of a RAC system,

you would have V$ views.

But you also have GV$ views that look over the entire RAC,

so all of the instances together.

So while V$ session would allow you to see the sessions on one

particular instance, the GV$ view, for GV$ session,

would allow you to see all of the sessions across the RAC

itself.

So let's take a look at some data dictionary views.

First, I'm going to connect with the Kara user.

I'm going to select star from user_tables.

So this is going to be a USER_ view.

And we show that Kara has one table called TEST_TAB.

But let's take a look at the information that we have here.

There's much more than maybe we would need at this point.

But it does serve to show the scope that the data dictionary

views have.

So we have the name of the table, TABLESPACE_NAME,

things like the status, the percent free,

the initial extent size, the next extent, number of rows,

number of blocks.

And this information, you can see, goes on and on and on.

And over time and experience, we learn to leverage

this information more and more, and for every query

that we want, we won't always need

to know all of this information, but some of it is here.

And sometimes the information that we want

is in those data dictionary views.

So that's an example of Kara.

And just as a point of information here,

when we expand the tables list for Kara in SQL Developer,

notice that we see TEST_TAB there, as well,

because these are the objects that Kara owns.

So let's do select star from all_tables.

And I'll do, for simplicity, order by owner.

So here we see a new column that's

added that's different than user_tables.

So the user_tables all pertain to a particular owner,

so they don't need to delineate that.

But all_tables actually needs to just

be able to specify what the owner of the table

is because it's all of the tables

that Kara has access to see.

So we see her TEST_TAB, and then we also see a number of tables

that she had access to from the Scott schema.

And the Scott user created those objects.

So ALL views are going to be the ones that

give us information about all the objects

that we have access to.

So let's try to do a select star from dba_tables.

Well, the user Kara is going to get an error in this case

because it's just simply going to say table or view does not

exist, but that's because Kara doesn't have the DBA

role granted to her.

So she cannot see those DBA views.

What if we tried another one of the V$ views?

We get the same kind of error here.

So she does not have access to these V$ dynamic views,

as well.

So in order to find someone who does,

we'll need to connect as system and then

select star from dba_tables, which Kara could not do.

And then we have a list of all of the tables in the database.

A number of them are going to be owned by SYS.

So it's a very extensive list.

Again, the data dictionary is owned by SYS,

those objects are.

What if we start with a select star from v$session for the V$

views.

Well, the system user can see those, as well,

as what a DBA, a user that's been granted the DBA privilege.

We have session information here.

And this is dynamic information.

It changes moment to moment.

New sessions connect in, old sessions disconnect.

That's the type of information we'll have in the V$ views.

So that's some examples, at least,

of the static data dictionary views and the dynamic data

dictionary views.

Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.