ORACLE數據庫除了可以保存永久表外
還可以建立臨時表temporary tables
這些臨時表用來保存一個會話SESSION的數據
或者保存在一個事務中需要的數據
當會話退出或者用戶提交commit和回滾rollback事務的時候
臨時表的數據自動清空(truncate)
但是臨時表的結構以及元數據還存儲在用戶的數據字典中
簡介 ORACLE數據庫除了可以保存永久表外
還可以建立臨時表temporary tables
這些臨時表用來保存一個會話SESSION的數據
或者保存在一個事務中需要的數據
當會話退出或者用戶提交commit和回滾rollback事務的時候
臨時表的數據自動清空(truncate)
但是臨時表的結構以及元數據還存儲在用戶的數據字典中
In addition to permanent tables
Oracle can create temporary tables to hold session
private data that exists only for the duration of a transaction or session
Temporary tables are supported by Oracle
i and Oracle
i
詳細介紹 Oracle臨時表分為 會話級臨時表 和 事務級臨時表
會話級臨時表是指臨時表中的數據只在會話生命周期之中存在
當用戶退出會話結束的時候
Oracle自動清除臨時表中數據
事務級臨時表是指臨時表中的數據只在事務生命周期中存在
當一個事務結束(commit or rollback)
Oracle自動清除臨時表中數據
臨時表中的數據只對當前Session有效
每個Session都有自己的臨時數據
並且不能訪問其它Session的臨時表中的數據
因此
臨時表不需要DML鎖
The CREATE GLOBAL TEMPORARY TABLE statement creates a temporary table that can be transaction
specific or session
specific
For transaction
specific temporary tables
data exists for the duration of the transaction
For session
specific temporary tables
data exists for the duration of the session
Data in a temporary table is private to the session
Each session can only see and modify its own data
DML locks are not acquired on the data of the temporary tables
The LOCK statement has no effect on a temporary table
because each session has its own private data
DML操作的臨時表不產生redo log重作日志
但會產生回滾日志Undo log
Undo的產生(rollback segment)會產生Redo log
DML statements on temporary tables do not generate redo logs for the data changes
However
undo logs for the data and redo logs for the undo logs are generated
當一個會話結束(用戶正常退出 用戶不正常退出 ORACLE實例崩潰)或者一個事務結束的時候
Oracle對這個會話的表執行 TRUNCATE 語句清空臨時表數據
但不會清空其它會話臨時表中的數據
A TRUNCATE statement issued on a session
specific temporary table truncates data in its own session
It does not truncate the data of other sessions that are using the same table
DML statements on temporary tables do not generate redo logs for the data changes
However
undo logs for the data and redo logs for the undo logs are generated
Data from the temporary table is automatically dropped in the case of session termination
either when the user logs off or when the session terminates abnormally such as during a session or instance failure
你可以索引臨時表和在臨時表基礎上建立視圖
同樣
建立在臨時表上的索引也是臨時的
也是只對當前會話或者事務有效
臨時表可以擁有觸發器
You can create indexes for temporary tables using the CREATE INDEX statement
Indexes created on temporary tables are also temporary
and the data in the index has the same session or transaction scope as the data in the temporary table
You can create views that access both temporary and permanent tables
You can also create triggers on temporary tables
空間分配Segment Allocation(v$sort_usage)
Temporary tables use temporary segments
Unlike permanent tables
temporary tables and their indexes do not automatically allocate a segment when they are created
Instead
segments are allocated when the first INSERT (or CREATE TABLE AS SELECT) is performed
This means that if a SELECT
UPDATE
or DELETE is performed before the first INSERT
then the table appears to be empty
Temporary segments are deallocated at the end of the transaction for transaction
specific temporary tables and at the end of the session for session
specific temporary tables
臨時表在一些版本中存在BUG可能產生過多的REDO LOG
eygle 的站點
建立臨時表 臨時表的定義對所有會話SESSION都是可見的
但是表中的數據只對當前的會話或者事務有效
建立方法:
) ON COMMIT DELETE ROWS 定義了建立事務級臨時表的方法
CREATE GLOBAL TEMPORARY TABLE admin_work_area
(startdate DATE
enddate DATE
class CHAR(
))
ON COMMIT DELETE ROWS;
EXAMPLE:
SQL> CREATE GLOBAL TEMPORARY TABLE admin_work_area
(startdate DATE
enddate DATE
class CHAR(
))
ON COMMIT DELETE ROWS;
SQL> create table permernate( a number);
SQL> insert into admin_work_area values(sysdate
sysdate
temperary table
);
SQL> insert into permernate values(
);
SQL> commit;
SQL> select * from admin_work_area;
SQL> select * from permernate;
A
)ON COMMIT PRESERVE ROWS 定義了創建會話級臨時表的方法
CREATE GLOBAL TEMPORARY TABLE admin_work_area
(startdate DATE
enddate DATE
class CHAR(
))
ON COMMIT PRESERVE ROWS;
EXAMPLE:
會話
:
SQL> drop table admin_work_area;
SQL> CREATE GLOBAL TEMPORARY TABLE admin_work_area
(startdate DATE
enddate DATE
class CHAR(
))
ON COMMIT PRESERVE ROWS;
SQL> insert into permernate values(
);
SQL> insert into admin_work_area values(sysdate
sysdate
session temperary
);
SQL> commit;
SQL> select * from permernate;
A
SQL> select * from admin_work_area;
STARTDATE ENDDATE CLASS
??
??
session temperary
會話
:
SQL> select * from permernate;
A
SQL> select * from admin_work_area;
未選擇行
會話
看不見會話
中臨時表的數據
temporary tables
but they have to support
a) read consistency
b) rollback
c) rollback to savepoint
_P
_DISPLAYID
F
_P
_CRITERIA:
they definitely
totally definitely
generate UNDO
consider:
ops$tkyte@ORA
IUTF> create global temporary table t ( x int ) on commit preserve
rows;
Table created
ops$tkyte@ORA
IUTF> insert into t values (
);
row created
ops$tkyte@ORA
IUTF> variable x refcursor
ops$tkyte@ORA
IUTF> exec open :x for select * from t;
PL/SQL procedure successfully completed
that is (as always) a read consitent result set
it is pre
ordained
we
haven
t fetched a single row of data yet
NO IO HAS BEEN performed
the result
set is not
copied
somewhere
just like any other query
ops$tkyte@ORA
IUTF> savepoint foo;
Savepoint created
ops$tkyte@ORA
IUTF> insert into t values (
);
row created
ops$tkyte@ORA
IUTF> select * from t;
X
that shows we can see two rows
but
ops$tkyte@ORA
IUTF> print x
X
that query used UNDO to rol
From:http://tw.wingwit.com/Article/program/Oracle/201311/18639.html