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;