第一篇：Oracle臨時(shí)表總結(jié)
Oracle臨時(shí)表總結(jié)
臨時(shí)表概念
臨時(shí)表就是用來(lái)暫時(shí)保存臨時(shí)數(shù)據(jù)(亦或叫中間數(shù)據(jù))的一個(gè)數(shù)據(jù)庫(kù)對(duì)象，它和普通表有些類似，然而又有很大區(qū)別。它只能存儲(chǔ)在臨時(shí)表空間，而非用戶的表空間。ORACLE臨時(shí)表是會(huì)話或事務(wù)級(jí)別的，只對(duì)當(dāng)前會(huì)話或事務(wù)可見(jiàn)。每個(gè)會(huì)話只能查看和修改自己的數(shù)據(jù)。
臨時(shí)表語(yǔ)法
臨時(shí)表分類
ORACLE臨時(shí)表有兩種類型：會(huì)話級(jí)的臨時(shí)表和事務(wù)級(jí)的臨時(shí)表。
1）ON COMMIT DELETE ROWS
它是臨時(shí)表的默認(rèn)參數(shù)，表示臨時(shí)表中的數(shù)據(jù)僅在事物過(guò)程(Transaction)中有效，當(dāng)事物提交(COMMIT)后，臨時(shí)表的暫時(shí)段將被自動(dòng)截?cái)?TRUNCATE)，但是臨時(shí)表的結(jié)構(gòu) 以及元數(shù)據(jù)還存儲(chǔ)在用戶的數(shù)據(jù)字典中。如果臨時(shí)表完成它的使命后，最好刪除臨時(shí)表，否則數(shù)據(jù)庫(kù)會(huì)殘留很多臨時(shí)表的表結(jié)構(gòu)和元數(shù)據(jù)。
2）ON COMMIT PRESERVE ROWS
它表示臨時(shí)表的內(nèi)容可以跨事物而存在，不過(guò)，當(dāng)該會(huì)話結(jié)束時(shí)，臨時(shí)表的暫時(shí)段將隨著會(huì)話的結(jié)束而被丟棄，臨時(shí)表中的數(shù)據(jù)自然也就隨之丟棄。但是臨時(shí)表的結(jié)構(gòu)以及元數(shù)據(jù)還存儲(chǔ)在用戶的數(shù)據(jù)字典中。如果臨時(shí)表完成它的使命后，最好刪除臨時(shí)表，否則數(shù)據(jù)庫(kù)會(huì)殘留很多臨時(shí)表的表結(jié)構(gòu)和元數(shù)據(jù)。
1:會(huì)話級(jí)的臨時(shí)表的數(shù)據(jù)和你當(dāng)前會(huì)話有關(guān)系，當(dāng)前SESSION不退出的情況下，臨時(shí)表中的數(shù)據(jù)就還存在，臨時(shí)表的數(shù)據(jù)只有當(dāng)你退出當(dāng)前SESSION的時(shí)候才被截?cái)?TRUNCATE TABLE)，如下所示：
會(huì)話級(jí)別的臨時(shí)表創(chuàng)建：
CREATE GLOBAL TEMPORARY TABLE TMP_TEST
（ID NUMBER ，NAME VARCHAR2(32))ON COMMIT PRESERVE ROWS;
或
CREATE GLOBAL TEMPORARY TABLE TMP_TEST ON COMMIT PRESERVE ROWS
AS
SELECT * FROM TEST;
操作示例：
SQL> CREATE GLOBAL TEMPORARY TABLE TMP_TEST
（ID NUMBER ，NAME VARCHAR2(32))ON COMMIT PRESERVE ROWS;
Table created
SQL> INSERT INTO TMP_TEST
SELECT 1, 'kerry' FROM DUAL;row inserted
SQL> COMMIT;
Commit complete
SQL> SELECT * FROM TMP_TEST;
ID
NAME
--------------------------
kerry
SQL> INSERT INTO TMP_TEST
SELECT 2, 'rouce' FROM DUAL;row inserted
SQL> ROLLBACK;
Rollback complete
SQL> SELECT * FROM TMP_TEST;
ID
NAME
--------------------------------
kerry
SQL> 2:事務(wù)級(jí)的臨時(shí)表(默認(rèn))，這種類型的臨時(shí)表與事務(wù)有關(guān)，當(dāng)進(jìn)行事務(wù)提交或者事務(wù)回滾的時(shí)候，臨時(shí)表的數(shù)據(jù)將自行截?cái)?，即?dāng)COMMIT或ROLLBACK時(shí)，數(shù)據(jù)就會(huì)被TRUNCATE掉，其它的特性和會(huì)話級(jí)的臨時(shí)表一致。
事務(wù)級(jí)臨時(shí)表的創(chuàng)建方法：
CREATE GLOBAL TEMPORARY TABLE TMP_TEST
（ID NUMBER ，NAME VARCHAR2(32))ON COMMIT DELETE ROWS;
或
CREATE GLOBAL TEMPORARY TABLE TMP_TEST ON COMMIT DELETE AS SELECT * FROM TEST;
SQL> CREATE GLOBAL TEMPORARY TABLE TMP_TEST
（ID NUMBER ，NAME VARCHAR2(32))ON COMMIT DELETE ROWS;
Table created
SQL> INSERT INTO TMP_TEST
SELECT 1, 'kerry' FROM DUAL;row inserted
SQL> SELECT * FROM TMP_TEST;
ID
NAME
--------------------------------
kerry
SQL> COMMIT;
Commit complete
SQL> SELECT * FROM TMP_TEST;
ID
NAME
----------------------------------
SQL>
3:關(guān)于臨時(shí)表只對(duì)當(dāng)前會(huì)話或事務(wù)可見(jiàn)。每個(gè)會(huì)話只能查看和修改自己的數(shù)據(jù)。
用DM用戶登錄數(shù)據(jù)庫(kù)，打開(kāi)SESSION 1后，創(chuàng)建臨時(shí)表TMP_TEST
CREATE GLOBAL TEMPORARY TABLE TMP_TEST
（ID NUMBER ，NAME VARCHAR2(32))ON COMMIT DELETE ROWS;
或
CREATE GLOBAL TEMPORARY TABLE TMP_TEST ON COMMIT DELETE AS SELECT * FROM TEST;
SQL> CREATE GLOBAL TEMPORARY TABLE TMP_TEST
（ID NUMBER ，NAME VARCHAR2(32))ON COMMIT DELETE ROWS;
Table created
SQL> INSERT INTO TMP_TEST
SELECT 1, 'kerry' FROM DUAL;row inserted
SQL> SELECT * FROM TMP_TEST;
ID
NAME
-------------------------------kerry
SQL> COMMIT;
Commit complete
SQL> SELECT * FROM TMP_TEST;
ID
NAME
---------------------------------
SQL>
用sys用戶登錄數(shù)據(jù)庫(kù)，打開(kāi)SESSION 2
SELECT * FROM DBA_TABLES WHERE TABLE_NAME='TMP_TEST'--時(shí)表數(shù)據(jù)
可以查到臨
SELECT * FROM DM.TMP_TEST;--查不到數(shù)據(jù)，即使TMP_TEST臨時(shí)表存在數(shù)據(jù)。
臨時(shí)表與永久表區(qū)別
SQL> SELECT TABLE_NAME, TABLESPACE_NAME,“LOGGING”，“TEMPORARY”, DURATION, “MONITORING”
FROM DBA_TABLES WHERE TABLE_NAME IN('TMP_TEST', 'TEST');
TABLE_NAME TABLESPACE_NAME LOGGING TEMPORARY DURATION MONITORING
---------------------------------------------------------------
TEST TBS_EDS_DATA YES N YES
TMP_TEST NO Y SYS$SESSION NO
如上所示，臨時(shí)表是存儲(chǔ)在臨時(shí)表空間里面的，但是上面腳本可以看出，臨時(shí)表在數(shù)據(jù)字典中沒(méi)有指定其表空間，臨時(shí)表是NOLOGGING，DURATION為SYS$SESSION
臨時(shí)表的DML操作速度比較快，但同樣也是要產(chǎn)生 Redo Log，只是同樣的DML語(yǔ)句，比對(duì) PERMANENT 的DML 產(chǎn)生的Redo Log 少其實(shí)在應(yīng)用中，往往會(huì)創(chuàng)建一個(gè)NOLOGGING的永久表(中間表)來(lái)保存中間數(shù)據(jù)，從而代替臨時(shí)表，至于這這兩者有啥優(yōu)劣，真是很難說(shuō)清道明(歡迎大家探討)。
臨時(shí)表用途
什么時(shí)候使用臨時(shí)表?用臨時(shí)表和用中間表有啥區(qū)別呢?
我覺(jué)得是在需要的時(shí)候應(yīng)用，下面是David Dai關(guān)于臨時(shí)表的一個(gè)應(yīng)用說(shuō)明，我覺(jué)得非常形象的說(shuō)明了臨時(shí)表的應(yīng)用場(chǎng)景：對(duì)于一個(gè)電子商務(wù)類網(wǎng)站，不同消費(fèi)者在網(wǎng)站上購(gòu)物，就是一個(gè)獨(dú)立的 SESSION，選購(gòu)商品放進(jìn)購(gòu)物車中，最后將購(gòu)物車中的商品進(jìn)行結(jié)算。也就是說(shuō)，必須在整個(gè)SESSION期間保存購(gòu)物車中的信息。同時(shí)，還存在有些消費(fèi)者，往往最終結(jié)賬時(shí)放棄購(gòu)買商品。如果，直接將消費(fèi)者選購(gòu)信息存放在最終表(PERMANENT)中，必然對(duì)最終表造成非常大的壓力。因此，對(duì)于這種案例，就可以采用創(chuàng)建臨時(shí)表(ON COMMIT PRESERVE ROWS)的方法來(lái)解決。數(shù)據(jù)只在 SESSION 期間有效，對(duì)于結(jié)算成功的有效數(shù)據(jù)，轉(zhuǎn)移到最終表中后，ORACLE自動(dòng)TRUNCATE 臨時(shí)數(shù)據(jù);對(duì)于放棄結(jié)算的數(shù)據(jù)，ORACLE 同樣自動(dòng)進(jìn)行 TRUNCATE，而無(wú)須編碼控制，并且最終表只處理有效訂單，減輕了頻繁的DML操作的壓力。
1：當(dāng)處理某一批臨時(shí)數(shù)據(jù)，需要多次DML操作時(shí)(插入、更新等)，建議使用臨時(shí)表。
2：當(dāng)某些表在查詢里面，需要多次用來(lái)做連接時(shí)。(為了獲取目標(biāo)數(shù)據(jù)需要關(guān)聯(lián)A、B、C，同時(shí)為了獲取另外一個(gè)目標(biāo)數(shù)據(jù)，需要關(guān)聯(lián)D、B、C....)
關(guān)于臨時(shí)表和中間表(NOLOGGING，保存中間數(shù)據(jù)，使用完后刪除)那個(gè)更適合用來(lái)存儲(chǔ)中間數(shù)據(jù)，我個(gè)人更傾向于使用臨時(shí)表，而不建議使用中間表。
注意事項(xiàng))不支持 lob 對(duì)象，這也許是設(shè)計(jì)者基于運(yùn)行效率的考慮，但實(shí)際應(yīng)用中確實(shí)需要此功能時(shí)就無(wú)法使用臨時(shí)表了。這點(diǎn)網(wǎng)上很多資料都這么說(shuō)，我沒(méi)有追查到底是那個(gè)版本不支持lob對(duì)象，至少在ORACLE 10g這個(gè)版本中，臨時(shí)表是支持lob對(duì)象的.SQL> CREATE GLOBAL TEMPORARY TABLE TMP_TEST(ID NUMBER , NAME CLOB)ON COMMIT PRESERVE ROWS;
Table created
SQL>
SQL> INSERT INTO TMP_TEST SELECT 1, 'ADF' FROM DUAL;row inserted
SQL> SELECT * FROM V$VERSION;
BANNER
--Oracle Database 10g Enterprise Edition Release 10.2.0.1.0Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0Production)不支持主外鍵關(guān)系）臨時(shí)表不能永久的保存數(shù)據(jù)。）臨時(shí)表的數(shù)據(jù)不會(huì)備份，恢復(fù)，對(duì)其的修改也不會(huì)有任何日志信息
5）臨時(shí)表不會(huì)有DML 鎖
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.）盡管對(duì)臨時(shí)表的DML操作速度比較快，但同樣也是要產(chǎn)生 Redo Log，只是同樣的DML語(yǔ)句，比對(duì) PERMANENT 的DML 產(chǎn)生的Redo Log 少。請(qǐng)見(jiàn)官方文檔：
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.）臨時(shí)表可以創(chuàng)建臨時(shí)的索引、視圖、觸發(fā)器。)如果要DROP會(huì)話級(jí)別臨時(shí)表，并且其中包含數(shù)據(jù)時(shí)，必須先截?cái)嗥渲械臄?shù)據(jù)。否則會(huì)報(bào)錯(cuò)。
SQL> DROP TABLE TMP_TEST PURGE;
DROP TABLE TMP_TEST PURGE
ORA-14452: 試圖創(chuàng)建, 更改或刪除正在使用的臨時(shí)表中的索引
SQL> TRUNCATE TABLE TMP_TEST;
Table truncated
SQL> DROP TABLE TMP_TEST PURGE;Table dropped
第二篇：Oracle 臨時(shí)表總結(jié)===
Oracle 臨時(shí)表總結(jié)
什么是臨時(shí)表？
臨時(shí)表只存在于某個(gè)會(huì)話或事務(wù)，不產(chǎn)生redo。
臨時(shí)表主要用于一些需要緩存結(jié)果的應(yīng)用中。例如，在一個(gè)學(xué)生選課系統(tǒng)中，學(xué)生暫時(shí)的選課表信息保存到一個(gè)臨時(shí)表中，此時(shí)的選課信息只對(duì)學(xué)生自己可見(jiàn)，當(dāng)學(xué)生確定選課內(nèi)容后，系統(tǒng)會(huì)將臨時(shí)表中的信息轉(zhuǎn)存到普通表，然后自動(dòng)清除臨時(shí)表中的內(nèi)容。
與普通表一樣，臨時(shí)表的結(jié)構(gòu)信息也是保存到數(shù)據(jù)字典中，當(dāng)?shù)谝淮蜗蚺R時(shí)表中插入數(shù)據(jù)后，系統(tǒng)機(jī)會(huì)分配對(duì)應(yīng)的臨時(shí)段，當(dāng)事務(wù)或者session結(jié)束后，會(huì)釋放臨時(shí)段空間。
默認(rèn)情況下，臨時(shí)表中的信息是保存到用戶的默認(rèn)臨時(shí)表空間中，也可以在創(chuàng)建臨時(shí)表的時(shí)候指定具體的表空間
臨時(shí)表中的內(nèi)容只有session內(nèi)可見(jiàn)，但是臨時(shí)表的定義信息是全局可見(jiàn)的，可以通過(guò)以下語(yǔ)句查詢一個(gè)表是否為臨時(shí)表 查看是否為臨時(shí)表以及臨時(shí)表的周期(transaction delete on commit, session preserve on commit)
select table_name,LOGGING,TEMPORARY,DURATION from user_tables where TEMPORARY='Y';
MY_TEMP_TABLE
NO Y SYS$TRANSACTION PARTITION_TEST
N T_COMPRESS
YES N EMP
YES N ORIGINAL_INDEX_TAB
YES N T_UNCOMPRESS
YES N
如何創(chuàng)建一個(gè)臨時(shí)表？
創(chuàng)建臨時(shí)表的語(yǔ)句如下：
CREATE GLOBAL TEMPORARY TABLE admin_work_area
(startdate DATE, enddate DATE, class CHAR(20))
ON COMMIT DELETE ROWS;
創(chuàng)建臨時(shí)表有兩種：delete rows, preserve rows
delete rows: 表示一個(gè)事務(wù)結(jié)束后，臨時(shí)表中的數(shù)據(jù)將會(huì)被自動(dòng)清除
preserve rows: 表示事務(wù)結(jié)束后，依然保留臨時(shí)表中的內(nèi)容，直到session結(jié)束后，自動(dòng)清除臨時(shí)表中的內(nèi)容。
delete rows 臨時(shí)表示例：
SQL> CREATE GLOBAL TEMPORARY TABLE admin_work_area 2
(startdate DATE, 3
enddate DATE, 4
class CHAR(20))5 ON COMMIT DELETE ROWS;
Table created.SQL> insert into ADMIN_WORK_AREA values(sysdate,sysdate+100,'Math');row created.SQL> select count(*)from ADMIN_WORK_AREA;
COUNT(*)----------
SQL> commit;
Commit complete.SQL> select count(*)from ADMIN_WORK_AREA;
COUNT(*)----------
0
preserve rows 示例
SQL> CREATE GLOBAL TEMPORARY TABLE admin_work_area_preserve 2(startdate DATE, 3 enddate DATE, 4 class CHAR(20))5 ON COMMIT preserve ROWS;
SQL> insert into ADMIN_WORK_AREA_preserve values(sysdate,sysdate+100,'Math');1 row created.SQL> select count(*)from ADMIN_WORK_AREA_preserve;COUNT(*)----------1 SQL> commit;Commit complete.SQL> select count(*)from ADMIN_WORK_AREA_preserve;COUNT(*)----------1 SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.064bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> show user USER is “USER01” SQL> select count(*)from ADMIN_WORK_AREA_preserve;COUNT(*)----------0
通過(guò)如下語(yǔ)句可以查詢臨時(shí)表的屬性信息：
SQL> select table_name,LOGGING,TEMPORARY,DURATION from user_tables where TEMPORARY='Y';TABLE_NAME LOG T DURATION-------------------------------------------------MY_TEMP_TABLE NO Y SYS$TRANSACTION ADMIN_WORK_AREA NO Y SYS$TRANSACTION ADMIN_WORK_AREA_PRESERVE NO Y SYS$SESSION
臨時(shí)表空間上也可以創(chuàng)建索引，周期與對(duì)應(yīng)的臨時(shí)表相同。創(chuàng)建臨時(shí)表的索引時(shí)，確保臨時(shí)表不在使用中
SQL> create index idx_temp_admin on ADMIN_WORK_AREA_preserve(startdate);create index idx_temp_admin on ADMIN_WORK_AREA_preserve(startdate)* ERROR at line 1: ORA-14452: attempt to create, alter or drop an index on temporary table already in use SQL> commit;Commit complete.SQL> insert into ADMIN_WORK_AREA_preserve values(sysdate,sysdate+100,'Math');1 row created.
第三篇：oracle創(chuàng)建臨時(shí)表
Oracle數(shù)據(jù)庫(kù)臨時(shí)表管理心得
我們?cè)趧?chuàng)建數(shù)據(jù)表的時(shí)候，若沒(méi)有特殊的指明，那么我們創(chuàng)建的表是一個(gè)永久的關(guān)系型表格，也就是說(shuō)，這個(gè)表格中對(duì)應(yīng)的數(shù)據(jù)，除非是我們顯示的刪除的話，表中的數(shù)據(jù)是永遠(yuǎn)都存在的。相對(duì)應(yīng)的，在Oracle數(shù)據(jù)庫(kù)中還有一種類型的表，叫做臨時(shí)表。這個(gè)臨時(shí)表跟永久表最大的區(qū)別就是表中的數(shù)據(jù)不會(huì)永遠(yuǎn)的存在。當(dāng)一個(gè)會(huì)話結(jié)束或者事務(wù)結(jié)束的時(shí)候，這個(gè)臨時(shí)表中的數(shù)據(jù)，不用用戶自己刪除，數(shù)據(jù)庫(kù)自己會(huì)自動(dòng)清
除。
1、事務(wù)臨時(shí)表的管理。
(1)事務(wù)臨時(shí)表的創(chuàng)建。
Oracle數(shù)據(jù)庫(kù)根據(jù)臨時(shí)表的性質(zhì)不同，可以分為事務(wù)臨時(shí)表與會(huì)話臨時(shí)表。事務(wù)臨時(shí)表是指數(shù)據(jù)只有在當(dāng)前事務(wù)內(nèi)有效。一般情況下，如果在創(chuàng)建數(shù)據(jù)表的時(shí)候，沒(méi)有特殊指明這表是會(huì)話臨時(shí)表的話，則該表默認(rèn)為事務(wù)臨時(shí)表。
我們可以以下面的語(yǔ)句創(chuàng)建事務(wù)臨時(shí)表。
Create global temporary table Temp_user
(ID NUMBER(12)Primary key,name varchar2(10));
筆者建議：
這個(gè)創(chuàng)建臨時(shí)表的語(yǔ)句中，雖然沒(méi)有顯性的指明該表是事務(wù)臨時(shí)表，但是，默認(rèn)的情況下，若沒(méi)有指明是什么臨時(shí)表的話，系統(tǒng)默認(rèn)是事務(wù)臨時(shí)表。我們要?jiǎng)?chuàng)建事務(wù)臨時(shí)表時(shí)，可以不指定關(guān)鍵字。但是，這查看起來(lái)比較麻煩。我建議，無(wú)論在建立什么臨時(shí)表，都要利用具體的關(guān)鍵字來(lái)顯形的指明，這大家看起來(lái)都方便。一般可以利用ON COMMIT DELETE ROWS關(guān)鍵字來(lái)說(shuō)明該表就是事務(wù)性的臨時(shí)表，而不是會(huì)話性質(zhì)的臨時(shí)表。
(2)事務(wù)臨時(shí)表數(shù)據(jù)的變化分析。
事務(wù)臨時(shí)表的話，當(dāng)事務(wù)結(jié)束的時(shí)候，就會(huì)清空這個(gè)事務(wù)臨時(shí)表。所以，當(dāng)我們?cè)跀?shù)據(jù)庫(kù)臨時(shí)表中插入數(shù)據(jù)后，只要事務(wù)沒(méi)有提交的話，該表中的數(shù)據(jù)就會(huì)存在。但是，當(dāng)事務(wù)提交以后，該表中的數(shù)據(jù)就會(huì)被刪除。而且，這個(gè)變化不會(huì)在重做日志中
顯示。
具體事務(wù)臨時(shí)表與會(huì)話臨時(shí)表有什么區(qū)別，我們?cè)诮榻B完會(huì)話臨時(shí)表后會(huì)詳細(xì)介
紹。
2、會(huì)話臨時(shí)表的管理。
會(huì)話臨時(shí)表，顧名思義，是指數(shù)據(jù)只在當(dāng)前會(huì)話內(nèi)有效的臨時(shí)表。關(guān)閉當(dāng)前會(huì)話或者進(jìn)行新的連接之后，數(shù)據(jù)表中的內(nèi)容就會(huì)被清除。那會(huì)話臨時(shí)表跟事務(wù)臨時(shí)表到底有什么區(qū)別呢?我們以一個(gè)實(shí)例來(lái)看其中的區(qū)別。
(1)首先，創(chuàng)建一個(gè)會(huì)話臨時(shí)表。
CREATE GLOBAL TEMPOPARY TABLE TEMP_USER
(ID NUMBER(12)Primary key,name varchar2(10))
ON COMMIT PRESERVE ROWS;
也就是說(shuō)，會(huì)話臨時(shí)表跟事務(wù)臨時(shí)表的創(chuàng)建語(yǔ)法大致相同，只有最后的關(guān)鍵字有區(qū)別。不過(guò)兩個(gè)表雖然類似，但是其內(nèi)部的處理機(jī)制還是有比較大的區(qū)別。
(2)往該表中插入數(shù)據(jù)。
Insert into TEMP_USER values(1001,’victor’);
往數(shù)據(jù)庫(kù)臨時(shí)表中插入數(shù)據(jù)的方法，跟往普通表中插入數(shù)據(jù)的方法是一樣的，都利用insert into語(yǔ)句進(jìn)行操作。該臨時(shí)表的數(shù)據(jù)在會(huì)話結(jié)束之前都是存在這個(gè)表格
中的。
(3)提交該事務(wù)并查詢相關(guān)記錄。
我們利用COMMIT的語(yǔ)句把該事務(wù)提交以后，再用SELECT查詢語(yǔ)句進(jìn)行查詢。我們知道，若該表是事務(wù)臨時(shí)表的話，則當(dāng)該事務(wù)結(jié)束以后，該表中的內(nèi)容就會(huì)被刪除。但是，這是會(huì)話臨時(shí)表，所以即使該事務(wù)提交了，但是，利用SELECT語(yǔ)句進(jìn)行查詢
時(shí)，仍然可以查到該條員工記錄。
(4)結(jié)束當(dāng)前會(huì)話，并重新連接數(shù)據(jù)庫(kù)。
關(guān)閉當(dāng)前會(huì)話，從新連接到數(shù)據(jù)庫(kù)后，再利用SELECT語(yǔ)句查詢時(shí)，會(huì)有什么結(jié)果呢?此時(shí)，就查不到我們剛才插入的數(shù)據(jù)。這也就是說(shuō)，在關(guān)閉對(duì)話的時(shí)候，數(shù)據(jù)庫(kù)系統(tǒng)已經(jīng)把原有的數(shù)據(jù)刪除了。從以上的分析我們可以看中，會(huì)話臨時(shí)表與事務(wù)臨時(shí)表主要的差異就在于刪除數(shù)據(jù)時(shí)機(jī)的不同。事務(wù)性臨時(shí)表是在事務(wù)提交的時(shí)候清除數(shù)據(jù)，而會(huì)話性臨時(shí)表則是在關(guān)閉當(dāng)前會(huì)話的時(shí)候清除臨時(shí)表。只要當(dāng)前會(huì)話沒(méi)有關(guān)閉，即使事務(wù)完成了，會(huì)話臨時(shí)表中的數(shù)據(jù)仍然存在，不會(huì)被清除。
3、臨時(shí)表管理需要注意的地方。
臨時(shí)表相對(duì)與其他表來(lái)說(shuō)，是一種比較特殊的表結(jié)構(gòu)，但是，作用又比較大，Oracle數(shù)據(jù)庫(kù)若沒(méi)有這種表的話，還真是不行。為了管理好這種特殊的表，我們需要
注意幾個(gè)細(xì)節(jié)。
一是要注意臨時(shí)表不能永久的保存數(shù)據(jù)。只所以稱為臨時(shí)表，就是因?yàn)樵摫碇械膬?nèi)容只是臨時(shí)存在的。當(dāng)一個(gè)會(huì)話或者事務(wù)結(jié)束時(shí)，該表中的內(nèi)容就會(huì)被自動(dòng)清空。所以，在臨時(shí)表中，一般不要保存永久數(shù)據(jù)。在實(shí)務(wù)中，有個(gè)不好的操作習(xí)慣，就是有些人在測(cè)試數(shù)據(jù)庫(kù)的時(shí)候，喜歡把測(cè)試的數(shù)據(jù)放在臨時(shí)數(shù)據(jù)表中。其實(shí)，這是對(duì)Oralce臨時(shí)數(shù)據(jù)表認(rèn)識(shí)的錯(cuò)誤。若我們?cè)跀?shù)據(jù)庫(kù)中，把要測(cè)試的數(shù)據(jù)，如銷售定單的內(nèi)容放在數(shù)據(jù)庫(kù)的臨時(shí)表中的話，則在其他功能中，如要測(cè)試銷售定單日?qǐng)?bào)表的功能時(shí)，就會(huì)找不到相關(guān)的定單內(nèi)容。因?yàn)殡x開(kāi)特定的會(huì)話或者事務(wù)的話，臨時(shí)表中的內(nèi)容就會(huì)不存在了。所以，Oralce數(shù)據(jù)庫(kù)中所講的臨時(shí)表不是給我們來(lái)存儲(chǔ)測(cè)試數(shù)據(jù)的。
二是臨時(shí)表中的數(shù)據(jù)不會(huì)備份、恢復(fù)，對(duì)其的修改也不會(huì)有任何的日志信息。若我們?cè)诓僮鲾?shù)據(jù)庫(kù)的時(shí)候，往數(shù)據(jù)庫(kù)的臨時(shí)表中存入了一些信息。此時(shí)突然服務(wù)器出現(xiàn)當(dāng)機(jī)。此時(shí)，我們想通過(guò)數(shù)據(jù)庫(kù)備份文件恢復(fù)數(shù)據(jù)庫(kù)臨時(shí)表中的內(nèi)容，或者查看臨時(shí)表的日志信息，都是無(wú)法實(shí)現(xiàn)的。也就是說(shuō)，當(dāng)服務(wù)器以外死機(jī)重新啟動(dòng)后，臨時(shí)表中的內(nèi)容就會(huì)被清空。在數(shù)據(jù)庫(kù)的任何地方，如數(shù)據(jù)庫(kù)備份文件或者日志信息中，都查不到在重新啟動(dòng)之前數(shù)據(jù)庫(kù)臨時(shí)表中保存了哪些內(nèi)容，就好象根本沒(méi)有對(duì)臨時(shí)表
進(jìn)行操作一樣。
三是臨時(shí)表表空間的管理。臨時(shí)表在Oracle數(shù)據(jù)庫(kù)中，也是表的一種，其也有對(duì)應(yīng)的表空間。在創(chuàng)建臨時(shí)表的時(shí)候，若我們不指定表空間的話，默認(rèn)的表空間是SYSTEM。對(duì)于臨時(shí)表的表空間管理的話，我們需要注意一個(gè)小的細(xì)節(jié)。若我們把臨時(shí)表的表空間歸屬為SYSTEM的話，也就是說(shuō)，在創(chuàng)建臨時(shí)表的時(shí)候不具體指定具體的表空間，則這個(gè)默認(rèn)的表空間是不能被刪除的。而若我們?cè)趧?chuàng)建臨時(shí)表表空間的時(shí)候，指定為SYSTEM以外的表空間的話，則在不需要這表空間的時(shí)候，我們可以刪除。所以，為了后續(xù)管理的方便，筆者還是建議大家在創(chuàng)建臨時(shí)表的時(shí)候，要指定表空間。
四是要注意一個(gè)問(wèn)題，臨時(shí)表只是數(shù)據(jù)是臨時(shí)的，而表仍然是永久的。也就是說(shuō)，當(dāng)一個(gè)會(huì)話結(jié)束或者一個(gè)事務(wù)完成時(shí)，其臨時(shí)表中的數(shù)據(jù)雖然刪除了，但是，臨時(shí)表本身仍然是存在的。也就是說(shuō)。Oracle數(shù)據(jù)庫(kù)中的臨時(shí)表表是全局的，只是數(shù)據(jù)是臨時(shí)的。這跟SQL Server數(shù)據(jù)庫(kù)系統(tǒng)具有比較大的區(qū)別。其實(shí)，這兩個(gè)數(shù)據(jù)庫(kù)在臨時(shí)表的處理上有很大的不同，各有各的特色。在以后的文章中，我會(huì)專門敘述這兩種數(shù)據(jù)庫(kù)在臨時(shí)表管理機(jī)制上的不同，歡迎大家關(guān)注。
五是要注意Oracle數(shù)據(jù)庫(kù)在給臨時(shí)表填入數(shù)據(jù)的時(shí)候，不會(huì)對(duì)相應(yīng)的記錄加鎖。也就是說(shuō)，當(dāng)在臨時(shí)表上執(zhí)行DML語(yǔ)句的操作時(shí)，不會(huì)給記錄加鎖，也不會(huì)將數(shù)據(jù)的變化內(nèi)容寫到重做(REDO)日志中。所以不能用臨時(shí)表保存永久的數(shù)據(jù)，也不能對(duì)臨時(shí)表進(jìn)行共同的操作。這是新手在管理數(shù)據(jù)庫(kù)臨時(shí)表經(jīng)常會(huì)碰到的問(wèn)題。
六是臨時(shí)表與普通表之間不能相互轉(zhuǎn)換。在一般情況下，臨時(shí)表建立后，該表就不能被轉(zhuǎn)換成永久表。所以，這也說(shuō)明一個(gè)道理，利用臨時(shí)表作為數(shù)據(jù)庫(kù)設(shè)計(jì)時(shí)候的測(cè)試表不合適。這個(gè)臨時(shí)表可能跟我們按字面意思理解的臨時(shí)表有誤，不是我們所認(rèn)為的為了測(cè)試表結(jié)構(gòu)而建立的臨時(shí)表。這一點(diǎn)是我們?cè)趧傞_(kāi)始接觸ORACLE數(shù)據(jù)庫(kù)時(shí)，經(jīng)常會(huì)犯的錯(cuò)誤。
第四篇：Oracle臨時(shí)表用法的經(jīng)驗(yàn)心得
Oracle臨時(shí)表用法的經(jīng)驗(yàn)心得
1.案例
前段時(shí)間報(bào)表中心有一存儲(chǔ)過(guò)程執(zhí)行速度過(guò)慢，由于另外一個(gè)存儲(chǔ)過(guò)程中用到了那個(gè)存儲(chǔ)過(guò)程中的中間表，因此如果前面的一個(gè)過(guò)程沒(méi)有執(zhí)行完而執(zhí)行后面的那個(gè)過(guò)程，后面的過(guò)程執(zhí)行完之后還是沒(méi)有數(shù)據(jù)。四月份那個(gè)過(guò)程執(zhí)行大約需要兩個(gè)小時(shí)的時(shí)間，本以為是過(guò)程里面的業(yè)務(wù)太復(fù)雜導(dǎo)致。可前段時(shí)間執(zhí)行的時(shí)間超過(guò)了十小時(shí)。后來(lái)才發(fā)現(xiàn)是中間表中數(shù)據(jù)量越來(lái)越多導(dǎo)致，大約有五百多萬(wàn)數(shù)據(jù)，而且每天會(huì)增加兩萬(wàn)左右的數(shù)據(jù)，慢的地方主要是在中間表中插入當(dāng)天的數(shù)據(jù)之后需要更新某些字段，由于中間表中數(shù)據(jù)量過(guò)大，更新這些字段是速度才很慢（已經(jīng)建了索引）。
解決方案：創(chuàng)建一個(gè)和中間表一摸一樣的臨時(shí)表，先把數(shù)據(jù)插入到臨時(shí)表中，在臨時(shí)表中更新需要更新的字段，更新完后再把臨時(shí)表中所有的數(shù)據(jù)插入到中間表中，然后再使用中間表中的數(shù)據(jù)匯總出報(bào)表。
2.如何創(chuàng)建臨時(shí)表
創(chuàng)建Oracle 臨時(shí)表，可以有兩種類型的臨時(shí)表：會(huì)話級(jí)的臨時(shí)表，事務(wù)級(jí)的臨時(shí)表。2.1會(huì)話級(jí)臨時(shí)表
因?yàn)檫@個(gè)臨時(shí)表中的數(shù)據(jù)和你的當(dāng)前會(huì)話有關(guān)系，當(dāng)你當(dāng)前SESSION 不退出的情況下，臨時(shí)表中的數(shù)據(jù)就還存在，而當(dāng)你退出當(dāng)前SESSION 的時(shí)候，臨時(shí)表中的數(shù)據(jù)就全部沒(méi)有了，當(dāng)然這個(gè)時(shí)候你如果以另外一個(gè)SESSION 登陸的時(shí)候是看不到另外一個(gè)SESSION 中插入到臨時(shí)表中的數(shù)據(jù)的。
即兩個(gè)不同的SESSION 所插入的數(shù)據(jù)是互不相干的。當(dāng)某一個(gè)SESSION 退出之后臨時(shí)表中的數(shù)據(jù)就被截?cái)啵╰runcate table，即數(shù)據(jù)清空）了。會(huì)話級(jí)的臨時(shí)表創(chuàng)建方法：
Create Global Temporary Table Table_Name(Col1 Type1,Col2 Type2...)On Commit Preserve Rows ； 舉例：
create global temporary table Student(Stu_id Number(5), Class_id Number(5), Stu_Name Varchar2(8), Stu_Memo varchar2(200))on Commit Preserve Rows ； 2.2 事務(wù)級(jí)臨時(shí)表
事務(wù)級(jí)臨時(shí)表是指該臨時(shí)表與事務(wù)相關(guān)，當(dāng)進(jìn)行事務(wù)提交或者事務(wù)回滾的時(shí)候，Oracle臨時(shí)表中的數(shù)據(jù)將自行被截?cái)啵渌膬?nèi)容和會(huì)話級(jí)的臨時(shí)表的一致（包括退出SESSION 的時(shí)候，事務(wù)級(jí)的臨時(shí)表也會(huì)被自動(dòng)截?cái)啵?。事?wù)級(jí)臨時(shí)表的創(chuàng)建方法：
Create Global Temporary Table Table_Name(Col1 Type1,Col2 Type2...)On Commit Delete Rows ； 舉例：
create global temporary table Classes(Class_id Number(5), Class_Name Varchar2(8), Class_Memo varchar2(200))on Commit delete Rows;2.3 兩種類型臨時(shí)表的區(qū)別
會(huì)話級(jí)臨時(shí)表采用 on commit preserve rows ；而事務(wù)級(jí)則采用 on commit delete rows ；用法上，會(huì)話級(jí)別只有當(dāng)會(huì)話結(jié)束臨時(shí)表中的數(shù)據(jù)才會(huì)被截?cái)?，而且事?wù)級(jí)臨時(shí)表則不管是 commit、rollback 或者是會(huì)話結(jié)束，Oracle臨時(shí)表中的數(shù)據(jù)都將被截?cái)?2.4臨時(shí)表的不足之處
1）不支持 lob 對(duì)象，這也許是設(shè)計(jì)者基于運(yùn)行效率的考慮，但實(shí)際應(yīng)用中確實(shí)需要此功能時(shí)就無(wú)法使用臨時(shí)表了。）不支持主外鍵關(guān)系
3.示例
1.會(huì)話級(jí)臨時(shí)表
create global temporary table emp_temp_preserve on commit preserve rows
as select * from emp where 1=2;
insert into emp_temp_preserve select * from emp;commit;
select * from emp_temp_preserve;
在同一個(gè)會(huì)話中查詢有數(shù)據(jù)，不在同一個(gè)會(huì)話中查詢沒(méi)數(shù)據(jù)
在不同會(huì)話中查詢：
2.事務(wù)級(jí)臨時(shí)表
create global temporary table emp_temp_delete on commit delete rows
as select * from emp where 1=2;
insert into emp_temp_delete select * from emp;commit;提交之后再同一個(gè)會(huì)話中查詢，沒(méi)有數(shù)據(jù)，說(shuō)明提交之后臨時(shí)表中的數(shù)據(jù)已清空，提交之前查詢時(shí)是有數(shù)據(jù)的，select * from emp_temp_delete;
第五篇：Oracle建表空間各種語(yǔ)句
在創(chuàng)建用戶之前，先要?jiǎng)?chuàng)建表空間：
其格式為：格式: create tablespace 表間名 datafile '數(shù)據(jù)文件名' size 表空間大小;如：
SQL> create tablespace news_tablespace datafile 'F:oracleproduct10.1.0oradatanewsnews_data.dbf' size 500M;其中'news_tablespace'是你自定義的表空間名稱，可以任意取名；'F:oracleproduct10.1.0oradatanewsnews_data.dbf'是數(shù)據(jù)文件的存放位置，'news_data.dbf'文件名也是任意取；'size 500M'是指定該數(shù)據(jù)文件的大小，也就是表空間的大小。
現(xiàn)在建好了名為'news_tablespace'的表空間，下面就可以創(chuàng)建用戶了：
其格式為：格式: create user 用戶名 identified by 密碼 default tablespace 表空間表;如：
SQL> create user news identified by news default tablespace news_tablespace;默認(rèn)表空間'default tablespace'使用上面創(chuàng)建的表空間。
接著授權(quán)給新建的用戶：
SQL> grant connect,resource to news;--表示把 connect,resource權(quán)限授予news用戶
SQL> grant dba to news;--表示把 dba權(quán)限授予給news用戶
授權(quán)成功。
ok!數(shù)據(jù)庫(kù)用戶創(chuàng)建完成，現(xiàn)在你就可以使用該用戶創(chuàng)建數(shù)據(jù)表了！1．建表空間
create tablespace hoteldata datafile 'D:javaOracleproduct10.1.0oradatazznorclhoteldata.dbf'size 200m autoextend on next 10m maxsize unlimited;2．建用戶 create user hotel identified by hotel default tablespace hoteldata account unlock;//identified by 后面的是密碼，前面的是用戶名 3．用戶授權(quán)
grant resource,connect,RECOVERY_CATALOG_OWNER to hotel;grant create table to hotel;alter user hotel quota unlimited ON OSDB;alter user hotel default tablespace hoteldata;4．刪除表空間
DROP TABLESPACE hoteldata INCLUDING CONTENTS AND DATAFILES;5．刪除用戶
DROP USER hotel CASCADE 6．刪除表的注意事項(xiàng)
在刪除一個(gè)表中的全部數(shù)據(jù)時(shí)，須使用TRUNCATE TABLE 表名;因?yàn)橛肈ROP TABLE，DELETE * FROM 表名時(shí)，TABLESPACE表空間該表的占用空間并未釋放，反復(fù)幾次DROP，DELETE操作后，該TABLESPACE上百兆的空間就被耗光了。oracle sqlplus腳本建庫(kù)總結(jié)(原創(chuàng))******************************************************************/--查詢表空間參數(shù)
select tablespace_name,min_extents,max_extents,pct_increase,status from dba_tablespaces;--查詢數(shù)據(jù)文件信息
--autoextensible數(shù)據(jù)庫(kù)已滿后是否自動(dòng)擴(kuò)展
select tablespace_name,bytes,autoextensible,file_name from dba_data_files;/******************************************************************/--創(chuàng)建表空間
--一般信息
--DATAFILE：數(shù)據(jù)文件目錄--存儲(chǔ)
--AUTOEXTEND：數(shù)據(jù)文件滿后自動(dòng)擴(kuò)展--ON NEXT：增量
--MAXSIZE UNLIMITED：最大容量無(wú)限制--SIZE：文件大小--存儲(chǔ)
--啟用事件記錄：LOGGING為生成從做日志并可恢復(fù)，NOLOGGING為快速更新不生成日志且不可恢復(fù)
--MANAGEMENT LOCAL：本地管理--缺?。鹤詣?dòng)分配
--UNIFORM SIZE：統(tǒng)一分配
--MANAGEMENT DICTIONARY：在字典中管理--DEFAULT STORAGE：覆蓋默認(rèn)區(qū)值--INITIAL：初始大小--NEXT ：下一個(gè)大小--MINEXTENTS：最小數(shù)量
--MAXEXTENTS UNLIMITED ：最大數(shù)量不限制--PCTINCREASE：增量，單位“%”--MINIMUM EXTENT：最小區(qū)大小 CREATE TABLESPACE “TEST” NOLOGGING DATAFILE 'G:ORACLEORADATAMYORACLETEST.ora' SIZE 5M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 12K;--最好寫成相對(duì)路徑，免得出錯(cuò) CREATE TABLESPACE “TEST” NOLOGGING DATAFILE '../DATABASE/TEST.ora' SIZE 5M REUSE--建議用'../oradata/TEST.ora' AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 12K;CREATE TABLESPACE “TEST” LOGGING DATAFILE 'G:ORACLEORADATAMYORACLETEST.ora' SIZE 5M EXTENT MANAGEMENT DICTIONARY DEFAULT STORAGE(INITIAL 1K NEXT 2K MINEXTENTS 5 MAXEXTENTS 67 PCTINCREASE 4)MINIMUM EXTENT 3K;/******************************************************************/--增加表空間, 注意這里test.ora1不能與原表空間文件名稱相同--添加一個(gè)新的大小為5M數(shù)據(jù)庫(kù)文件test.ora1 alter tablespace mytesttablespace add datafile 'c:testtest.ora1' size 5M;/******************************************************************/--修改表空間數(shù)據(jù)庫(kù)文件屬性，myoracle為sid--將test.ora1數(shù)據(jù)庫(kù)文件改為3M，其中resize可以是ONLINE, OFFLINE, RESIZE, AUTOEXTEND 或 END/DROP alter database myoracle datafile 'c:testtest.ora1' resize 3M;alter database myoracle datafile '$ORACLE_HOME/oradata/undo102.dbf' autoextend on next 10m maxsize 500M;/******************************************************************/ /*刪除表空間
語(yǔ)法：drop tablespace tablespace_name including contents and datafiles;刪除表空間時(shí)要系統(tǒng)不會(huì)刪除表空間數(shù)據(jù)庫(kù)文件，要徹底刪除要手動(dòng)刪除 */ drop tablespace mytesttablespace including contents and datafiles;/******************************************************************/--創(chuàng)建用戶--命令：
--CREATE USER 名稱 IDENTIFIED BY 口令 DEFAULT TABLESPACE “默認(rèn)表空間名” TEMPORARY TABLESPACE “TEMP” ACCOUNT UNLOCK--一般屬性
--DEFAULT TABLESPACE ：默認(rèn)表空間名
--TEMPORARY TABLESPACE ：臨時(shí)表空間名，默認(rèn)TEMP--ACCOUNT ：用戶狀態(tài)，默認(rèn)UNLOCK 未鎖定；LOCK 鎖定 CREATE USER “TEST” PROFILE “DEFAULT” IDENTIFIED BY “test” DEFAULT TABLESPACE “TEST” TEMPORARY TABLESPACE “TEMP” ACCOUNT UNLOCK;--可以寫成
CREATE USER “TEST” PROFILE “DEFAULT” IDENTIFIED BY “test” DEFAULT TABLESPACE “TEST”;/******************************************************************/--用戶授權(quán)
--grant “connect,resource,dba” to “someuser” with admin option;--WITH ADMIN OPTION ：管理選項(xiàng)--授予usertest DBA權(quán)限
grant dba to “usertest” with admin option;--取消授權(quán)
--REVOKE “RESOURCE” FROM “SCOTT”;/******************************************************************/--建表
--在usertest方案下建表,注意表名不能用關(guān)鍵字，否則報(bào)錯(cuò)ORA-00903: 表名無(wú)效
--語(yǔ)法：
--create table [schema.]
--data type：表示字段的數(shù)據(jù)類型
--default
CREATE TABLE “TEST”.“TEST”(“ID” NUMBER(10)DEFAULT 0 PRIMARY KEY, “NAME” VARCHAR2(20)NOT NULL, “INFO” VARCHAR2(1000))TABLESPACE “TEST”;/******************************************************************/--插入數(shù)據(jù)--語(yǔ)法：
--INSERT INTO tablename(column1,column2,…)VALUES(expression1,expression2,…);
INSERT INTO “TEST”.“TEST”(“ID” ,“NAME” ,“INFO”)VALUES(1 ,'testname' ,'');--也可以寫成
INSERT INTO “TEST”.“TEST” VALUES(1 ,'testname' ,'');/******************************************************************/
點(diǎn)擊咨詢 