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

In this lesson,
we're going to take

a look at bigfile tablespaces.

Now, bigfile tablespaces
are a relatively new feature

of Oracle, around the
10g, 11g time period.

And bigfile tablespaces
were created

to address the needs of VLDBs,
or Very Large Databases.

So in this scenario, what if we
say our database is truly huge,

I mean very big?

What if it is so
big that it requires

more than the maximum database
size of eight petabytes?

So with smallfile tablespaces,
our maximum size of a database

is eight petabytes.

What if our database needs
to be larger than that?

Or what if we have a
table in our database

that is so big that it uses
up too many data files?

For instance, let's say
we have a table that's

along the four terabyte size.

Now, a four terabyte table will
be contained in a tablespace.

And that tablespace
will contain data files.

But since the maximum
size of a data file

is 32 gigabytes for
an 8K block size,

that means we'd have hundreds
and hundreds of data files.

So what if we have a
very large database

or what if we have
large tables, and we

need to increase our capacity
and our manageability?

Well, that's where bigfile
tablespaces come in.

And one of the features
of bigfile tablespaces

is that because of
the increase in size

that it allows for
a data file, we only

have one data file per
bigfile tablespace.

So it becomes a one-to-one
relationship between tablespace

and data file.

So for bigfile
tablespaces we don't

have multiple data
files contained

by the name of a tablespace.

So let's look at the
numbers here a little bit.

So the maximum size of a data
file in a bigfile tablespace

is going to be the block
size times roughly 4 billion.

Now, smallfile
tablespaces' max size

are the block size
times 4 million.

So this is an order
of magnitude larger.

And again, the result
is, say, a 32k block,

which is the largest
block size in a database,

times roughly 4
billion gives us a max

size on a data file in a bigfile
tablespace for 128 terabytes.

So that's an order
of magnitude larger

than 128 gigabytes with
smallfile tablespaces.

So if we apply that number,
that maximum data file number,

times the maximum
number of data files,

we see that the maximum
size of a database

with bigfile tablespaces
is eight exabytes.

So again, an order of magnitude
larger than that eight petabyte

limit with smallfile
tablespaces.

So let's do some operations
with bigfile tablespaces

and look at some general
commands that apply

to all tablespaces as well.

So let's create a
bigfile tablespace.

And in order to do
that, it's always

good to know where the
data files are located,

so we'll do a select
star from dba_data_files.

Now we create
bigfile tablespace.

Give it a name, datafile.

I'm just copying and
pasting the location in.

I'll say big_ts01.

size, 100 meg.

And now if we select star
from dba_tablespaces,

we see BIG_TS for Tablespace.

We see its block size,
initial and next extent.

And way over to the right,
we see in the BIGFILE column

it says YES.

All these others are smallfile
tablespaces, so they say NO.

And let's look at
dba_data_files to see our data

file belonging to the
tablespace BIG_TS.

Now that we have
bigfile tablespaces

and we have less to worry
about as far as reaching

the maximum size, we
can talk about a subject

called autoextend.

Now, this applies both to data
files in bigfile or smallfile

tablespaces.

But it's especially useful
with a bigfile tablespace.

Since we have this large
limit and we will not

be adding other data files
to a bigfile tablespace,

we can do something
called autoextend.

And with autoextend,
the tablespace

will grow automatically
when it is filled.

So when that data file
belonging to the tablespace

reaches its maximum size, let's
say in our example 100 meg,

then it will automatically
extend, add more extents,

and continue to grow without
worrying about errors that

might come saying,
you're out of space

and the DBA gets
up in the middle

of the night, the on-call,
and fixes the tablespace,

so on and so forth.

So in order to
turn autoextend on,

let's do an alter
database datafile command,

referring to file ID number 5.

autoextend on.

Now if we look at
dba_data_files,

we have this
AUTOEXTENSIBLE column.

And it says YES, that
it's autoextensible.

Notice that the other ones
are as well by default.

Tablespaces can be taken
online and offline.

That is to say, they
are made unavailable

when they're taken offline.

So to take our bigfile
tablespace offline,

we say alter tablespace
big_ts offline.

And so now that
tablespace is offline.

So if we were to say create
table test, we'd get the error,

tablespace 'BIG_TS' is offline,
cannot allocate space in it.

And so to bring it back online,
alter tablespace big_ts online.

And now it is online again.

Tablespaces can also be
put into read-only mode.

So when a read-only
tablespace is created,

or a tablespace is put
into read-only mode,

there's obviously no
rights that can occur.

So let's take a look at this.

alter tablespace
big_ts read only.

And the tablespace is altered.

So let's try to create this
table in the tablespace.

Again, 'BIG_TS' is read-only,
cannot allocate space in it.

So if we want to put a
read-only tablespace back

into read/write mode,
we say alter tablespace,

tablespace name, read write.

And now the tablespace is
back online and available

for writes.