All language subtitles for [SubtitleTools.com] Monitoring Space Usage - Learning Oracle 12c [Video]

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.