Would a Temporary table be dropped automatically in Oracle?

user_6471991 0
Viewed 517

Forgive me to ask a silly question.

Would a temporary table be dropped automatically in Oracle (12c)?

Yesterday I have executed the following DDL to create a temporary table:

Create global temporary table my_1st_t_table on commit preserve rows as
   select
      *
   from
      other_table
   where
      selected_col = 'T';

After that I have executed following statements:

commit;

select count(*) from my_1st_t_table;

Yesterday, the last select statement returned 2000 rows.

After that I disconnected my VPN and also switched off my client laptop.

Today I rerun the last select statement after restarted my computer and reconnected to the VPN.

It returned 0 rows. So this means the table was still there but just all rows being deleted after my session.

However, may I ask when will my temporary table be dropped?

Thanks in advance!

temp-tables oracle
1 Answers

A temporary table in Oracle is much different than a temp table in other database platforms such as MS SQL Server, and the "temporary" nomenclature invariably leads to confusion.

In Oracle, a temporary table just like other tables, and does not get "dropped". However, the rows in the table only exist within the context of the session that inserted the rows. Once the session is terminated, assuming the session did not delete the rows, Oracle will delete the rows in the table for that session.

So bottom line, the data is temporary, the table structure is permanent, until the table is dropped.

user_17455440
Related