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

In this lesson,
we're going to look

at tablespaces, specifically
smallfile tablespaces.

So a tablespace is
the logical name

given to one or more
physical data files.

So the benefit of having
something like a tablespace

is so that when a user wants
to create a table that belongs

in a certain place
or a data file,

they don't need to specify
the entire data file name.

So the physical aspect
of it is the data file.

And the tablespace
is the logical name.

Now, smallfile tablespaces
are the default type.

They're not the only type.

There's also
bigfile tablespaces.

But for the purposes
of this lesson,

we're looking at
smallfile tablespaces.

So when you create a tablespace
using the default method,

if you will,
smallfile tablespace

is what you'll have created.

And the maximum number of data
files per smallfile tablespace

is 1,022.

So you can create a tablespace
with 1,022 data files in it.

So again, it's that idea of
a logical grouping or logical

name on top of physical files.

The data dictionary views that
we use for tablespaces are

DBA_TABLESPACES
and V$TABLESPACE.

So let's take a look at what we
have in the way of tablespaces.

Now, in order to look at
data dictionary information

of this type, since it
is more administrative,

we can't use our Scott
account, because Scott

is an account that owns some
objects and can do work.

But it can't do
administrative activities.

So we're going to click the
plus by our system Oracle

connection.

And we're going to select
star from DBA_TABLESPACES.

So this gives us a list of the
tablespaces that we have here.

So we have SYSTEM, which holds
the data dictionary; SYSAUX,

which holds the
workload repository;

UNDO for undo tablespace--

that holds undo data;
TEMP for temporary data,

and then a USERS
tablespace that's actually

a tablespace with a
non-specific data file in it

that's just for user data.

We can see similar types
of information when we look

at V$_TABLESPACE.

A little bit less
information here.

That's why a lot of times you'll
see DBAs use DBA_TABLESPACES

to get more information.

So let's do some activities
with tablespaces.

So the first activity we want
to do is create a tablespace.

Now, when we create
a tablespace,

we specify the name
of the tablespace,

the data file that we
want to use, and the size.

So the first thing
we need to know

is where are the data files
located, because we don't want

to create a database data
file for this tablespace

in a non-standard
place, in the wrong C

drive or the wrong directory
or anything of that nature.

So we're going to refer
back to DBA_DATA_FILES.

We see that data file locations
are of this path in my example,

so we will create
tablespace example datafile.

And it'll be this entire
path, which I'm actually

just going to copy.

Take away the file name that I
copied, and put example01.dbf.

size 100 meg.

So a tablespace name, a
data file name and location,

full path, and the size.

And now I create.

A tablespace is created.

And if we select
from DBA_TABLESPACES,

we now see our tablespace.

If we select from
DBA_DATA_FILES,

we see it in
DBA_DATA_FILES as well.

Now, there's two
ways to increase

the size of a tablespace.

We can either increase the
size of the data files,

or we can add data files to it.

So in this example, let's add
a data file to our tablespace.

This time I'm going to
call it example02.dbf.

size, 50 meg.

And now when we select
from DBA_DATA_FILES,

we see that we have two
data files, both belonging

to the example tablespace.

So we said that the other
way that we could increase

the size of a tablespace
is to increase

the size of the data files
that the tablespace contains.

So here, we see
that this data file

is this many bytes, which is 50
meg, as we indicated up here.

And so we can change that
data file to a different size.

We can either increase
it or decrease it

with certain limits.

We cannot decrease it more
than the data that's within it

or the data that has
ever been within it.

The data within
a data file forms

what's called a high watermark.

And we can never decrease
the size below that.

However, we can increase it.

And so that one of
the easiest ways

to do this, since we're going
to use a data file command,

if you will, is to
use the file ID.

Rather than typing
out the entire path,

we just refer to this
data file by its file ID.

So I'll show you what I mean.

alter database datafile 6,
which refers to this data file,

resize 100 meg.

And now in DBA_DATA_FILES, we
see that our data files are now

both 100 meg.

Whenever we wish to
drop a tablespace,

we use the drop tablespace
command with the name.

And we use the phrase "including
contents and data files."

And now when we look
at DBA_TABLESPACES,

our example is no longer there.