Oracle 19C学习 - 20. 临时表

临时表是什么

临时表是用来暂时保存临时数据的一个数据库对象。
它只能保存在临时表空间，而无法保存在用户表空间。

Oracle中，临时表分为SESSION会话和TRANSACTION事务两种。

1. SESSION级的临时表整个SESSION都存在，直到会话结束。
2. TRANSACTION级的临时表在TRANSACTION结束后消失，即COMMIT/ROLLBACK或者会话断开。

临时表的特点

1. 不支持主外键关系。
2. 不能永久保存数据。
3. 临时表的数据不会备份、恢复，对其修改会产生少量日志信息。
4. 临时表不会有DML锁。
5. 临时表可以创建临时的索引和视图。
6. 如果要DROP会话级别的临时表，如果其中有数据，需要先截断其中数据，否则会报错。
7. ORACLE 12C以后专门为临时表准备了临时UNDO，以再次减少REDO和UNDO产生的量。

创建session临时表

SQLPLUS中

CREATE GLOBAL TEMPORARY TABLE TMP_DEPTS
(
    department_id NUMBER,
    department_name VARCHAR2(40)
) ON COMMIT PRESERVE ROWS;

SQL> INSERT INTO tmp_depts SELECT 1, 'IT' FROM dual;

SQL> INSERT INTO tmp_depts SELECT 2, 'EO' FROM dual;

SQL> COMMIT;

Commit complete.

无法删除此表
SQL> DROP TABLE tmp_depts PURGE;
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already
in use


手工TRUNCATE,在删除就可以。
SQL> TRUNCATE TABLE tmp_depts;

Table truncated.

SQL> DROP TABLE tmp_depts PURGE;

Table dropped.

用户退出的时候，临时表还会在，只是数据没有了。

为临时表创建视图

CREATE VIEW 视图名 AS 临时表查询

SQL> CREATE VIEW v_tmp_depts AS SELECT department_name FROM tmp_depts;
View created.

为临时表创建索引

创建临时表索引，必须保证临时表是没有数据的。

SQL> CREATE INDEX TMP_DEPTS_DEPARTMENT_ID_IDX ON tmp_depts(department_id);
CREATE INDEX TMP_DEPTS_DEPARTMENT_ID_IDX ON tmp_depts(department_id)
                                            *
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already
in use

SQL> TRUNCATE TABLE tmp_depts;
Table truncated.

SQL> CREATE INDEX TMP_DEPTS_DEPARTMENT_ID_IDX ON tmp_depts(department_id);
Index created.

创建TRANSACTION级临时表

SQLPLUS中

SQL> CREATE GLOBAL TEMPORARY TABLE tmp_emp
  2  (
  3     employee_id number,
  4     employee_name varchar2(40)
  5  ) ON COMMIT DELETE ROWS;

Table created.

SQL> INSERT INTO tmp_emp VALUES (1, 'IT');
1 row created.

SQL> INSERT INTO tmp_emp VALUES (2, 'EO');
1 row created.

SQL> commit;
Commit complete.

SQL> SELECT * FROM tmp_emp;
no rows selected