global temporary table 조회 방법?

다른 내용도 좋지만, 오라클 forum에서 발견한 자료네요.

global temporary table에 대한 내용 입니다. 참고 하세요...

=====================================================

 

No. 11818
TEMPORARY TABLE IN ORACLE8I
===========================
Oracle8.1에서는 session내에서 임시로 사용할 data들을 영구적인 segment형태가
아닌 temporary structure에서 관리할 수 있다.
이러한 Temporary Table들은 그 생성문장에 의해서 definition이 dictionary에
저장되고, 각 session에서 해당 table을 사용할 때마다 definition을 이용하여
memory에 table 구조를 생성하게 된다.

CHARACTERISTICS
---------------
1. data는 session private하다. (특정 session에서 사용하는 temporary table
   data는 다른 session에서 access할 수 없다.)
2. CREATE GLOBAL TEMPORARY TABLE ...
   ON COMMIT [DELETE|PRESERVE] ROWS ; 문을 이용하여 생성한다.
3. data의 유지기간은 transaction단위 또는 session단위이다.
   'ON COMMIT DELETE ROWS'로 생성되었다면 transaction단위이며 이때 data는
   commit이 되는 시점에 자동으로 제거된다.
   'ON COMMIT PRESERVE ROWS'로 생성되었다면 session단위이며 이때 data는
   해당 session이 종료되면서 사라진다.
   default는 'ON COMMIT DELETE ROWS'이다.
4. table의 definition은 dictionary에 permanently 저장된다.
   *_tables의 TEMPORARY, DURATION column이 temporary table과 관련이 있다.
   TEMPORARY - 'Y' : temporary type table
               'N' : permanent type table
   DURATION  - 'SYS$SESSION'     : data의 유지기간이 session단위
               'SYS$TRANSACTION' : data의 유지기간이 transaction단위
               NULL              : 해당 table은 temporary type이 아님
5. session간에 data에 대한 contention이 발생되지 않기 때문에 DML문에 대한
   lock이 필요하지 않다.
6. 임시적으로만 관리되는 data이므로 DML문에 대해서 redo log를 발생시키지
   않는다.
7. index, view, trigger를 생성하여 사용할 수 있다. Temporary Table의
   column에 생성되는 index도 temporary type이다.
8. table의 definition은 export utility를 이용하여 export할 수 있다.
   그러나 그 row들은 export의 대상이 될 수 없다.

RESTRICTIONS
------------
1. partitioned, index-organized, clustered table로 생성할 수 없다.
2. foreign key constraint를 설정할 수 없다.
3. nested table이나 varray type의 column은 포함할 수 없다.
4. 다음과 같은 LOB_storage_clause들은 지정할 수 없다. : TABLESPACE,
   storage_clause, LOGGING 또는 NOLOGGING, MONITORING 또는 NOMONITORING,
   또는 LOB_index_clause.
5. parallel DML이나 parallel query는 지원되지 않는다. (parallel hint는
   무시될 것이며, table생성시 parallel clause를 지정하면 error를
   return한다.)
6. storage나 tablespace는 지정할 수 없다.
7. 분산 transaction은 지원되지 않는다.

SAMPLE
------
----------------------------------------------------------------------
-- temporary type table을 생성하되 duration은 transaction단위로 한다.
----------------------------------------------------------------------
SQL> create global temporary table temp_tab
  2  (col1 number, col2 char(10))
  3  on commit delete rows ;

-----------------------------------------------------------------------
-- 생성된 table에 대한 정보를 조회한다. *_tables의 TEMPORARY, DURATION
-- column이 temporary type table과 관련이 있다.
-----------------------------------------------------------------------
SQL> select temporary, duration
  2  from user_tables
  3  where table_name = 'TEMP_TAB' ;
T DURATION
- ---------------
Y SYS$TRANSACTION

-----------------------------------------------------------------------
-- temporary table에 row insert
-----------------------------------------------------------------------
SQL> insert into temp_tab values (1, 'wookpark') ;
SQL> select * from temp_tab ;
     COL1 COL2
--------- ----------
        1 wookpark

-----------------------------------------------------------------------
-- transction을 commit한다.
-----------------------------------------------------------------------
SQL> commit ;
Commit complete.

-----------------------------------------------------------------------
-- duration이 transaction단위이기 때문에 commit을 수행하면 모든 data가
-- 사라진다.
-----------------------------------------------------------------------
SQL> select * from temp_tab ;
no rows selected
    
Overview of Temporary Tables
Introduction
~~~~~~~~~~~~
This is an overview of TEMPORARY TABLES introduced in Oracle8i.  This 
new feature allows temporary tables to be created automatically in a 
users temporary tablespace.
 
Syntax
~~~~~~
  CREATE GLOBAL TEMPORARY TABLE tablename ( columns )
        [ ON COMMIT PRESERVE | DELETE ROWS ]
 
  The default option is to delete rows on commit.
 
What Happens
~~~~~~~~~~~~
  When you create a GLOBAL TEMPORARY table a dictionary definition of
  the table is created. As soon as the table gets populated (on the first
  INSERT or at creation time for CTAS operations) a temporary segment is
  created in the users default TEMPORARY tablespace location. This temporary
  segments contents are just like a normal table.
  Point --> Users Default Temporary tablespace 일반적인 temporary tablespace
            혹은 temp 에 해당 임시 Segment 가 생성 된다.
 
  Different sessions using the same GLOBAL TEMPORARY table get allocated
  different temporary segments. The temporary segments are cleaned up
  automatically at session end or transaction end depending on the specified
  duration (ON COMMIT PRESERVE ROWS or ON COMMIT DELETE ROWS).
 
  Apart from the data visibility temporary tables can be used like ordinary
  tables for most operations.
 
 
Characteristics
~~~~~~~~~~~~~~~
 
1. Data exists only for the duration of either the session or 
   transaction.
 
   This can be specified in the create table command.
   For example:
 
       SQL> Create global temporary table emp_temp(eno number) 
              on commit delete rows;
 
     - OR -
 
       SQL> Create global temporary table emp_temp(eno number) 
              on commit preserve rows;
 
 
   ON COMMIT DELETE ROWS indicates a transaction level duration and
   PRESERVE indicates a session level duration.  
  
2. Data is visible only at session or transaction level.  Multiple 
   users using the same temporary table can see the definition
   of the table and their own data segment and nothing else.
 
3. Indexes, triggers and views can be created on these tables.
 
4. If an Index is created on temporary tables then it MUST be created 
   when the table is empty - ie: When there are NO temporary segments for 
   incarnations of the table. Indexes are implemented as separate 
   temporary segments.
 
5. No redo is generated for operations on the temporary table itself BUT
   undo is generated. Redo *IS* generated for the undo so temporary tables
   do indirectly generate redo.
 
6. The keyword GLOBAL indicates the table definition can be viewed
   by anybody with sufficient privileges - ie:using the same rules
   that apply to normal user tables. Currently only GLOBAL TEMPORARY
   tables are supported.
 
7. TRUNCATE operations truncate only the current session's incarnation
   of the table.
 
8. You can only export or import the definition not the data.
  
   Global Temporary table 을 export 후 결과물인 XXX.dmp 화일을
   vi 혹은 notepad 로 오픈 해보면
   create global temporary table .....
   및
   grant ... 의 문장을 볼수 있다. 
 
 
9. Segments get created only on the first insert (or CTAS) operation.
 
 
Drawbacks
~~~~~~~~~
 
1. The table definition is not dropped automatically. 
 
2. Only GLOBAL tables are supported right now, not local ones.   
 
3. Can perform DDL only when no session is bound to it. 
 
4. There is no underlying support for STATISTICS on GLOBAL 
   TEMPORARY tables so CBO (Cost Based Optimizer) has no statistical
   information to help determine an execution plan.
   NB: "ANALYZE TABLE COMPUTE/ESTIMATE STATISTICS" returns success 
       even though no statistics are gathered in 8i.   9i and higher 
       provides support for STATISTICS.
  
 
Constraints
~~~~~~~~~~~
 
Constraints can be implemented on the table either at the session or 
transaction level depending on the scope of the temporary table and
are not for the entire table even though the constraint is defined 
for the entire table.
==> Constraints 는 생성 가능 하나, Session 혹은 Transaction Scope 내에 존재 !!
 
If there is a primary key or unique key constraint, it is applicable only at
either session or transaction leve  i.e. two users can enter the same values
into the table from different sessions even though you have a primary / unique
key in place for the entire table (if the scope is the session )
 
In the case of a transaction level temporary table, the same values 
can be entered from different transactions.

 

 

### global temporary table 인지 체크 하는 방법은

dba_extents 에서 해당 테이블이름을 segment_name 과 join 하여

해당 extent 가 존재 하지 않으면 global temporary table 로 추측 하는방법뿐인듯싶다.

 

# 샘플 테이블 생성
SCOTT@CRAFT.WORLD> create global temporary table temp_tab
                   ( col1 number, col2 char(10))
                  on commit delete rows
Table created.

# 샘플 테이블 조회
SCOTT@CRAFT.WORLD> select temporary , duration from user_tables
                   where table_name ='TEMP_TAB';
T DURATION
- ---------------
Y SYS$TRANSACTION

# 샘플 데이타 입력
SCOTT@CRAFT.WORLD>insert into temp_tab values ( 1, 'wookpark');
1 row created.

# 샘플 데이타 조회 [ 자기 세션 에서 - 보인다. ]
SCOTT@CRAFT.WORLD>select * from temp_tab ;

      COL1 COL2
---------- ----------
         1 wookpark

# 샘플 데이타 조회 [ 다른 세션 에서 - 안보인다. ]
SCOTT@CRAFT.WORLD>select * from temp_tab ;
no rows selected

SCOTT@CRAFT.WORLD>commit ;
Commit complete.

# 샘플 데이타 다시 입력
SCOTT@CRAFT.WORLD>insert into temp_tab values ( 2, 'TG') ;
1 row created.

# 다른 세션에서 alter table 을 통한 칼럼 추가 시도
SCOTT@CRAFT.WORLD>alter table temp_tab add ( col3 number ) ;
alter table temp_tab add ( col3 number )
*
ERROR at line 1:
ORA-14450: attempt to access a transactional temp table already in use

# 다른 세션에서 truncate table 후  alter table 을 통한 칼럼 추가 시도
SCOTT@CRAFT.WORLD>truncate table temp_tab ;

Table truncated.

SCOTT@CRAFT.WORLD>alter table temp_tab add ( col3 number ) ;
alter table temp_tab add ( col3 number )
*
ERROR at line 1:
ORA-14450: attempt to access a transactional temp table already in use

## 모두 Fail

# 샘플 데이타 입력 세션에서 다시 commit
SCOTT@CRAFT.WORLD>commit ;
Commit complete.

# 칼럼 추가 시도 - 성공
SCOTT@CRAFT.WORLD>alter table temp_tab add ( col3 number ) ;
Table altered.

# Global temporary table 은 세션별 ( create 시 option ) 테이블이라
  사용중이면 alter table 이 먹지 않는다.
  또한 다른 Session 에서 Truncate 해도 의미 없다.

 

## global temporary table 을 사용중인 세션 찾을 후 Kill 후

    alter table 을 진행 하면 될것으로 생각된다.

 

## 실제 Global Temporary table 을 사용중인 세션은 v$transaction 을 통해서 찾을수 있다.

 

## 세션 1

SCOTT@CRAFT.WORLD>insert into temp_tab values ( 2, 'TG',3 ) ;

1 row created.

## 세션 2

SCOTT@CRAFT.WORLD>insert into temp_tab values ( 2, 'TGG', 3 ) ;

1 row created.
이상태에서 아래의 Query 실행

SQL> select sid, serial#, username, taddr, used_ublk, used_urec
         from v$transaction t, v$session s
         where t.addr = s.taddr;

       SID    SERIAL# USERNAME                       TADDR     USED_UBLK  USED_UREC
---------- ---------- ------------------------------ -------- ---------- ----------
        13       5168 SCOTT                          471623AC          1          1
        18       8988 SCOTT                          47162728           1          1

 

해당 세션 에서 사용중인 sql 문장을 추출해야한다.

 

define sid_num=&sid
define serial_num=&serial

SELECT /*+ ordered use_hash(s t) */ sql_text, piece
FROM v$session s, v$sqltext_with_newlines t
WHERE s.sid = 13
AND s.serial# = &serial_num AND s.sql_address = t.address
AND s.sql_hash_value = t.hash_value AND s.sql_hash_value <> 0
UNION ALL SELECT /*+ ordered use_hash(s t) */ sql_text, piece
FROM v$session s, v$sqltext_with_newlines t
WHERE s.sid = 13 AND s.serial# = 5168
AND s.prev_sql_addr = t.address AND s.prev_hash_value = t.hash_value
AND s.sql_hash_value = 0 ORDER BY piece

undefine sid_num
undefine serial_num