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

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.