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