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'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.
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.