advertisements
_____________________________________________________________________________________________________________________
How to create Global Temporary table?
Temporary tables are the specific to sessions and the data in table is private to the session which they created. There are two types of temporary tables and those are
1. Transaction specific: The data in the table will be preserved until the transaction ends and the syntax to create the table is
CREATE GLOBAL TEMPORARY TABLE Trans_temp ( Trans_ID NUMBER,
Trans_amt NUMBER) ON COMMIT DELETE ROWS;
Trans_amt NUMBER) ON COMMIT DELETE ROWS;
2. Session Specific: The data in the table will be preserved until the session ends.
CREATE GLOBAL TEMPORARY TABLE Trans_temp ( Trans_ID NUMBER,
Trans_amt NUMBER) ON COMMIT PRESERVE ROWS;
Trans_amt NUMBER) ON COMMIT PRESERVE ROWS;
Privileges required for temporary table data population
· To create a global temporary table, the CREATE TABLE system privilege is required
· To populate a global temporary table that does not belong to you, the object privilege INSERT on this table is required:
Either granted by the owner of the table
Or by a user having the grant any object privilege (GAOP).
Features of Global Temporary Tables
o Can create triggers on the temporary tables
o Temporary table metadata (table definition) can be exported/imported using exp/imp utilities. But table rows cannot be exported.
o Database views can be created on temporary tables and it can be combined view with a permanent table as well.
o Session specific index can be created on temporary tables and it is valid only on the session.
o The data in the temporary tables are purged automatically at the end of the database session even if it is an abnormal end.
o Global temporary tables can store the data from multiple users. If you issue TRUNCATE command on a global temporary table, it deletes only the data belongs to that particular session. There is no affect on the data of other sessions.
How to take export of Global temporary tables
How to take export of Global temporary tables
SQL> create global temporary table gt_emp as select * from emp ;
Table created.
SQL> select table_name, TEMPORARY from dba_tables where table_name='GT_EMP';
TABLE_NAME T
------------------------------ -
GT_EMP Y
$ exp file=gt_table_bkup.dmp log=gt_table_dump.log tables=scott.gt_emp
Export: Release 11.1.0.7.0 - Production on Tue Jun 21 20:29:27 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
Current user changed to SCOTT
. . exporting table GT_EMP
Export terminated successfully without warnings.
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment