All language subtitles for [SubtitleTools.com] Temporary Tables - Learning Oracle 12c [Video]

In this lesson, we're
looking at temporary tables.

So a temporary table, we could
call it a transient table.

It's a table that is
visible to all sessions,

but the data is not.

So a temporary table can be
very useful in situations

where we need to
perhaps combine data

from a number of
different tables,

or we need to execute some
kind of function on a table,

we need to kind of
gather data together

for the purpose of
resolving an issue,

but we don't need
to store the data.

So we combine it
for that action,

but then we don't
store the data.

And a temporary
table is actually

stored in a different place than
regular heap organized tables

would have their data stored.

So a temporary table
will be stored actually

in temporary segments in
the temporary tablespace.

So, when we create
a temporary table,

we use the syntax create
global temporary table, then

the definition of
the table, and then

we have a clause
that says on commit.

And, at that point,
we have two choices.

So we can say on
commit, delete rows,

and so that is a
temporary table that

is specific to a transaction,
or preserve rows,

which is specific to a session.

So when we do on commit
delete rows, anytime a commit

occurs in our session,
the rows will go away.

They'll disappear.

If we create our table
with preserve rows,

then the table data will
exist for the entire session,

at least until the user that
creates the temporary table

logs out.

So one is to delete
the rows on a commit

and the next is to
preserve those rows.

So let's do an example and look
at how this behavior works.

Create global temporary table.

Test temp 1.

Col1.

So, up to this point, a standard
way of defining a table.

And then we say on commit.

And, in this case,
we'll say delete rows.

Change the name here.

Right, so our test
temp 3 is created

now with on commit delete rows.

So let's look at
the behavior of it.

Do a select star
from test temp 3.

You see there's no data.

Insert into test temp 3 values.

We run that insert, and
we select from the table,

and we see the values there.

So we did this table with
on commit delete rows.

So let's execute a commit.

The commit occurs.

And we select the data.

And, again, since it's
on commit delete rows,

those rows are now gone.

If we want to create a temporary
table with on commit delete

rows, we have to have a lot of
control over our transactions.

We have to make sure
that a commit doesn't

occur at the wrong time.

So let's look at the behavior,
change a few things here.

On commit reserve rows.

So now we're going to create
another temporary table

with the behavior preserve
rows instead of delete rows.

And we'll look at the table.

There are no rows.

We'll insert some data.

We select from the table.

And we see our rows.

So now, since it's an
on commit preserve rows,

and we execute a
commit, we'll expect

the rows to still be there.

And, indeed, they are.

So a temporary table with
preserve rows on a commit

requires less discipline as far
as controlling the transaction

in order to use the data
that's in the table.