Afrikaans
Akan
Albanian
Amharic
Armenian
Azerbaijani
Basque
Belarusian
Bemba
Bengali
Bihari
Bosnian
Breton
Bulgarian
Cambodian
Catalan
Cebuano
Cherokee
Chichewa
Chinese (Simplified)
Chinese (Traditional)
Corsican
Croatian
Czech
Danish
Dutch
English
Esperanto
Estonian
Ewe
Faroese
Filipino
Finnish
French
Frisian
Ga
Galician
Georgian
German
Greek
Guarani
Gujarati
Haitian Creole
Hausa
Hawaiian
Hebrew
Hindi
Hmong
Hungarian
Icelandic
Igbo
Indonesian
Interlingua
Irish
Italian
Japanese
Javanese
Kannada
Kazakh
Kinyarwanda
Kirundi
Kongo
Korean
Krio (Sierra Leone)
Kurdish
Kurdish (Soranรฎ)
Kyrgyz
Laothian
Latin
Latvian
Lingala
Lithuanian
Lozi
Luganda
Luo
Luxembourgish
Macedonian
Malagasy
Malay
Malayalam
Maltese
Maori
Marathi
Mauritian Creole
Moldavian
Mongolian
Myanmar (Burmese)
Montenegrin
Nepali
Nigerian Pidgin
Northern Sotho
Norwegian
Norwegian (Nynorsk)
Occitan
Oriya
Oromo
Pashto
Persian
Polish
Portuguese (Brazil)
Portuguese (Portugal)
Punjabi
Quechua
Romanian
Romansh
Runyakitara
Russian
Samoan
Scots Gaelic
Serbian
Serbo-Croatian
Sesotho
Setswana
Seychellois Creole
Shona
Sindhi
Sinhalese
Slovak
Slovenian
Somali
Spanish
Spanish (Latin American)
Sundanese
Swahili
Swedish
Tajik
Tamil
Tatar
Telugu
Thai
Tigrinya
Tonga
Tshiluba
Tumbuka
Turkish
Turkmen
Twi
Uighur
Ukrainian
Urdu
Uzbek
Vietnamese
Welsh
Wolof
Xhosa
Yiddish
Yoruba
Zulu
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.