All language subtitles for [SubtitleTools.com] Datafiles - Learning Oracle 12c [Video]

In this lesson, we're
going to take a look

at a collection of segments
forming our next larger unit

of space called a datafile.

So a datafile will be a
collection of segments.

So in our case, we have
tables called EMP, and BONUS,

and DEPT. And each
one of those tables

has an actual physical
segment that belongs

to it, that stores its data.

So if we look at the
rows in the EMP table,

they're actually stored
in the EMP segment,

which is made up of extents.

And those extents are
made up of blocks.

So a datafile is
going to contain all

of those different segments.

So when we actually go out
to the operating system,

here's where we
can actually start

to see the physical
constructs that Oracle stores.

So we can actually go and
see a data01.dbf file--

for instance.

So all of those
different segments

will be contained in a datafile.

We have a few different
types of datafiles--

the most common being the
ones that are non-specific

data datafiles-- if you will.

And so those are going to be
the segments and tables that

contain application data.

So user data, application data--

very much, the lion's share and
the largest part of a database

will be in those
non-specific datafiles.

But there are a couple
of datafiles that we

might call specific files.

And that would be-- for
instance-- the undo data.

So whenever a transaction
occurs and there

is a before image of
data that's generated,

it's stored in
undo segments that

are in an undo table space.

And so that undo table space
contains those datafiles.

Also, temporary data.

So whenever sorts occur,
they occur in memory,

unless they fill
the memory and have

to be written out to disk--

in which case, they're stored
in temporary datafiles that

are referred to as temp files.

So a little bit of knowledge
about the limits of datafiles

can help us calculate
both the size

of datafiles that we can
have and the maximum size

of an Oracle database.

So the limits on the
size of a datafile

are 4,194,304 times the
size of the database block.

So in our example, there--
if we use an 8K block size,

then the maximum size
of a datafile for us

will be 32 gigabytes.

Now, that's not terribly large.

It is certainly not outside
the realm of possibility

that you could have tablespaces
that require datafiles

that are larger than that.

But that's the limit
for a datafile.

The maximum number of datafiles
in a database is 65,533.

So if we calculate the
maximum block size--

which is 32K times
the 4 million plus--

we come up with a maximum
database size of 8 petabytes.

So in this case, the datafiles
that we're talking about here--

the maximum size that we
could have for a database

is 8 petabytes.

Now you might think that's out
of the realm of possibility,

but it's most certainly not.

There are data stores
around the world that are

petabyte orders of magnitude.

So that's where the limits
of the database that Oracle

provides are pushed
against the limits.

So we can discover
information about datafiles

using data dictionary views.

And they're going to be the
views that you see here,

primarily.

There are other views, as well.

But the most common
for datafiles--

at least non-specific
datafiles--

will be DBA_DATA_FILES
and V$DATAFILE.

And for temp files--

will be DBA_TEMP_FILES
and V$TEMPFILE.

So we can take a look
at the data dictionary

and see the kind of
information that we

get from DBA_DATA_FILES.

Now the reason that we can't
see this is that our user--

Scott-- is not provided
with the proper permissions

to look at these views.

So these are DBA
level views and they

require DBA level privileges.

So we need our
second connection--

system at ORCL.

So this is the
system user, which

is a user with high level
rights, that can see things

like data dictionary views.

While the Scott user can
see things like the tables

that he owns, he can't see the
more system-oriented aspects

of the database.

So this is the
kind of information

that DBA_DATA_FILES provides us.

First of all, we
have the file name.

So we mentioned that a datafile
is a physical file that

exists on disk.

And in our case, here is
the name of that datafile.

Files are named in this way
because our database by default

is using Oracle managed
files, which means that Oracle

controls the file naming.

And then our full path is here.

So if we went out onto
the operating system

and found this path, we
would see this datafile.

It's the table space
that it belongs to,

the number of bytes,
the number of blocks.

And then this information
goes on and on,

as we scroll to the right.

Let's take a look
at a similar view--

V$DATAFILE.

This gives us much of
the same information.

It gives us a file
number, which is called

file ID in DBA_DATA_FILES.

And we have to come
over a little more,

we see the size information.

And then a column
called Name that has

the name of the
datafiles and their path.

So what about temp files?

So in this case, we have one
temp file in the database,

and its path is here.

DBA_TEMP_FILES will
be structured much

like DBA_DATA_FILES is.

A file ID number for the name
of the table space associated

with it, the bytes, the
blocks, and so on and so forth.

Similarly, we can
look at V$TEMPFILE.

Again, structured
much like V$DATAFILE.

We'll see size and
name information

over as we scroll to the right.

One important designation
that we might make here

because you might think,
well, why would there

be two data dictionary views--

such as DBA_DATA_FILES
and V$DATAFILE--

that both have the
same information?

Well, first and
foremost, they don't have

exactly the same information.

There will be certain
information in one that's not

in the other and vise versa.

But the V$ views are useful
when a database is in a recovery

mode so the database is
down for some reason,

there's recovery
that has to occur.

So often, in those situations,
we can look at the data files

that we have in the views,
as opposed to the DBA views.