oracle 临时表

oracle   临时表

conn scott/tiger

--会话内保留行

drop table tmp1;
CREATE GLOBAL TEMPORARY TABLE tmp1
ON COMMIT PRESERVE ROWS 
AS SELECT * FROM emp;

SELECT TABLESPACE_NAME,CURRENT_USERS  FROM  V$SORT_SEGMENT;

SELECT * FROM TMP1;
INSERT INTO TMP1 SELECT * FROM EMP;
COMMIT;
SELECT * FROM TMP1;

CONN SCOTT/TIGER

SELECT * FROM TMP1;

DROP TABLE TMP1;

--事物内保存行

drop table tmp2;
CREATE GLOBAL TEMPORARY TABLE tmp2
AS SELECT * FROM emp;

SELECT * FROM TMP2;
INSERT INTO TMP2 SELECT * FROM EMP;
SELECT * FROM TMP2;
COMMIT;
SELECT * FROM TMP2;

--验证临时表空间的使用

SELECT * FROM V$TEMPSEG_USAGE; 
SELECT TABLE_NAME,TEMPORARY,DURATION FROM USER_TABLES;