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 be
talking about monitoring space usage
and managing it to some degree.
So monitoring space is one of and has always
been one of the DBA's eternal toiling jobs that they must do,
because you have, always, a limited amount of space,
and yet the needs of more and.
More data seem to press on the DBA.
And so you have to make sure that you're monitoring space
so that space is not filling up and causing errors.
And some companies have some pretty strict rules about that
as well, so it's important to know how to monitor space.
And just from a practical perspective,
monitoring space is normally done
a couple of different ways.
Sometimes it can be done on a daily basis, where a DBA would
connect into different databases and run one of the queries
that I'm going to show you, or use a tool
and just check and see how much space is available.
And if something is running out of space,
then the DBA can choose to go in and add space.
Another way that we can do monitoring of space is through
some kind of automatic system-- so some kind of monitoring
system like Oracle Enterprise Manager--
that's constantly looking at your space usage
and would report a problem if thresholds are crossed.
Let's take a couple of looks at different ways
that we can monitor space.
So what I want to show you is a new view in SQL Developer.
This is a view that allows you to do some more
administrative tasks--
so beyond just writing the SQL.
So we're going to open that view and click View and go down
to DBA.
And it opens the DBA view.
This feature isn't present in every version of SQL Developer.
Some of the later ones are the only ones that have it.
However, the version that comes with Oracle 12c
does have this feature.
So we need to add a connection to this view.
So we click Add.
And fortunately, we don't have to completely add
new information.
We just choose one of the existing ones
that we already have.
And if we're going to monitor space usage,
we're going to need to choose a privileged account.
And so here we're going to choose our system account,
and open this.
And then there are a number of different operations
here that we can do.
This version of SQL Developer supports
things like container databases and the new features of 12c,
but it also has a Storage section.
So underneath Storage, we have archive logs and control files
and various things.
But one of the things we're interested in for the purposes
of this lesson are Tablespaces.
So we click that, and it gives us some tablespace information.
So it'll tell us that the users tablespace has
5 megs allocated, 3 megs free, 2 megs used,
percent free, and percent used-- all of those types of things.
So this is a good, quickie way to come and look
at our tablespaces.
We can also click Data Files down here,
and we can actually get a graph here
that shows us how much of each one of our data
files is available.
So that's certainly one way it can be done.
Another way is just to leverage the data dictionary.
So let's do a couple of queries, here.
Let's start out with select * from dba_data_files,
which gives us quite a bit of information.
So let's modify this just a little bit to see what we want.
So
This is going to give us data files and their size.
So when we do bytes divided by 1024 divided by 1024,
we're taking it from the bytes to megabytes.
So the list of the files, the tablespaces
they belong with, and then their bytes.
Now, Oracle also has a data dictionary view
called dba_free_space.
That'll give us free space in each one of our table spaces.
So to leverage that, select tablespace_name.
So the information in dba_free_space
is actually based on extent.
So it's going to be a lot of rows in dba_free_space.
What we want to do is aggregate that together to give us,
actually, the amount of space that's free in our tablespaces.
So for each tablespace, this is the amount of megabytes
that's free-- again, bytes divided by 1024 twice--
and gives us that free space.
So DBAs are very big on writing scripts
that can do the monitoring of space usage,
and everybody has their own that they've refined over the years.
For the last example, I wanted to bring out one here--
demonstrate this a little bit.
So what this is going to do is actually
use a couple of subqueries on dba_data_files
and dba_free_space to bring that all together with these columns
that they label as tablespace, used meg, free meg,
and total meg, as well as percent free.
If we execute this, notice that we get a fairly nice display
of tablespace and how much is used and free,
and the percentage is free.
So that's a little bit about monitoring space usage.
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.