Global temporary Table

Oracle allows us to create temporary tables to hold data just for the duration of a session or for a particular transaction.



ON COMMIT DELETE ROWS [The ON COMMIT DELETE ROWS Clause  indicates that the data should be deleted at the end of the transaction ,or end of the session]
======================
Example :

CREATE GLOBAL TEMPORARY TABLE my_test_tab
(
ID NUMBER,
description varchar2(10)
)
on commit delete rows;

SQL> insert into my_test_tab
  2  values (1,'one');

1 row created.

SQL> select count(*) from my_test_tab;

  COUNT(*)
----------
         1

SQL> commit;

Commit complete.

SQL> select count(*) from my_test_tab;

  COUNT(*)
----------
         0

ON COMMIT PRESERVE ROWS [this clause indicates that rows should persist beyond the end of the transaction.they will be removed at the end of the transaction]
========================

CREATE GLOBAL TEMPORARY TABLE test_tab
(
ID NUMBER,
description varchar2(10)
)on commit preserve rows;

SQL> insert into test_tab values(1,'one');

1 row created.


SQL> select count(*) from test_tab;

  COUNT(*)
----------
         1

SQL> commit;

Commit complete.

SQL> select count(*) from test_tab;

  COUNT(*)
----------
         1

SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$ sqlplus rajiv/rajiv@testdb


[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Mar 31 18:00:14 2016

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn rajiv/rajiv
Connected.
SQL> select count(*) from test_tab;

  COUNT(*)
----------
         0

Getting Disk I/O information

select d.name,t.name,f.phyrds,f.phywrts,f.readtim,f.writetim from
v$datafile d,
v$filestat f,
v$tablespace t
where f.file#=d.file#
and d.ts#=t.ts#

TABLESPACE SIZE DETAILS

BREAK ON REPORT
COMPUTE SUM OF tbsp_size ON REPORT
compute SUM OF used ON REPORT
compute SUM OF free ON REPORT
COL tbspname FORMAT a20 HEADING 'Tablespace Name'
COL tbsp_size FORMAT 999,999 HEADING 'SIZE|(MB)'
COL used FORMAT 999,999 HEADING 'Used|(MB)'
COL free FORMAT 999,999 HEADING 'Free|(MB)'
COL pct_used FORMAT 999 HEADING '%Used'
select df.tablespace_name tbspname,
sum(df.bytes)/1024/1024 tbsp_size,
nvl(sum(e.used_bytes)/1024/1024,0) used,
nvl(sum(f.free_bytes)/1024/1024,0) free,
nvl((sum(e.used_bytes)*100)/sum(df.bytes),0) pct_used
from dba_data_files df,
(select file_id ,sum (nvl(bytes,0)) used_bytes
from dba_extents group by file_id) e,
(select max(bytes) free_bytes,file_id
from dba_free_space group by file_id) f
where e.file_id(+)=df.file_id and
df.file_id=f.file_id(+)
group by df.tablespace_name
order by 5 DESC;


===================================================
select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
from
(select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
from dba_free_space group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as tbs_size
from dba_data_files group by tablespace_name
UNION
select tablespace_name, sum(bytes)/1024/1024 tbs_size
from dba_temp_files
group by tablespace_name ) b
where a.tablespace_name(+)=b.tablespace_name;