oracle怎么找臨時表,oracle使用臨時表
oracle 怎么在存儲過程中創建一個臨時表，在里面插入數據，再查找這個臨時表的所有數據，最后drop這個表。
Oracle
崇川網站制作公司哪家好，找創新互聯！從網頁設計、網站建設、微信開發、APP開發、成都響應式網站建設公司等網站項目制作，到程序開發，運營維護。創新互聯從2013年開始到現在10年的時間，我們擁有了豐富的建站經驗和運維經驗，來保證我們的工作的順利進行。專注于網站建設就選創新互聯。
的臨時表的
處理機制，
和
SQL
Server
的不一樣。
假如你的臨時表的結構不是每次執行都發生變化的話
那么就是事先
通過
CREATE
GLOBAL
TEMPORARY
TABLE
語句，把臨時表建立好。
存儲過程里面，就根據需要，
執行
INSERT
/
SELECT
之類的操作就可以了
oracle存儲中創建并查詢臨時表
Oracle中的臨時表用法匯總
說明:下文中的一些說明和示例代碼摘自CSDN,恕不一一指明出處,在此一并對相關作者表示感謝! 如果作者有異議,請來信說明
1 語法 在Oracle中，可以創建以下兩種臨時表：
1) 會話特有的臨時表
CREATE GLOBAL TEMPORARY TABLE_NAME (column specification )
ON COMMIT PRESERVE ROWS;
2) 事務特有的臨時表
CREATE GLOBAL TEMPORARY TABLE_NAME (column specification )
ON COMMIT DELETE ROWS；
CREATE GLOBAL TEMPORARY TABLE MyTempTable
所建的臨時表雖然是存在的，但是如果insert 一條記錄然后用別的連接登上去select，記錄是空的。
--ON COMMIT DELETE ROWS 說明臨時表是事務指定，每次提交后ORACLE將截斷表（刪除全部行）
--ON COMMIT PRESERVE ROWS 說明臨時表是會話指定，當中斷會話時ORACLE將截斷表。
2 動態創建
create or replace procedure pro_temp(v_col1 varchar2,v_col2 varchar2) as
v_num number;
begin
select count(*) into v_num from user_tables where table_name='T_TEMP';
--create temporary table
if v_num1 then
execute immediate 'CREATE GLOBAL TEMPORARY TABLE T_TEMP (
COL1 VARCHAR2(10),
COL2 VARCHAR2(10)
) ON COMMIT delete ROWS';
end if;
--insert data
execute immediate 'insert into t_temp values('''||v_col1||''','''||v_col2||''')';
execute immediate 'select col1 from t_temp' into v_num;
dbms_output.put_line(v_num);
execute immediate 'delete from t_temp';
commit;
execute immediate 'drop table t_temp';
end pro_temp;
測試:
15:23:54 SQL set serveroutput on
15:24:01 SQL exec pro_temp('11','22');
11
PL/SQL 過程已成功完成。
已用時間: 00: 00: 00.79
15:24:08 SQL desc t_temp;
ERROR：
ORA-04043: 對象 t_temp 不存在
3 特性和性能(與普通表和視圖的比較) 臨時表只在當前連接內有效臨時表不建立索引,
所以如果數據量比較大或進行多次查詢時,不推薦使用數據處理比較復雜的時候時表快,
反之視圖快點在僅僅查詢數據的時候建議用游標: open cursor for 'sql clause';
臨時表的創建
創建Oracle臨時表，可以有兩種類型的臨時表：會話級的臨時表和事務級的臨時表。
1）會話級的臨時表因為這這個臨時表中的數據和你的當前會話有關系，當你當前SESSION不退出的情況下，臨時表中的數據就還存在，而當你退出當前SESSION的時候，臨時表中的數據就全部沒有了，當然這個時候你如果以另外一個SESSION登陸的時候是看不到另外一個SESSION中插入到臨時表中的數據的。即兩個不同的SESSION所插入的數據是互不相干的。當某一個SESSION退出之后臨時表中的數據就被截斷（truncate table，即數據清空）了。會話級的臨時表創建方法：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）事務級臨時表是指該臨時表與事務相關，當進行事務提交或者事務回滾的時候，臨時表中的數據將自行被截斷，其他的內容和會話級的臨時表的一致（包括退出SESSION的時候，事務級的臨時表也會被自動截斷）。事務級臨時表的創建方法：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 ;
3）、兩種不通類型的臨時表的區別：語法上，會話級臨時表采用on commit preserve rows而事務級則采用on commit delete rows；用法上，會話級別只有當會話結束臨時表中的數據才會被截斷，而且事務級臨時表則不管是commit、rollback或者是會話結束，臨時表中的數據都將被截斷。
例子：
1）、會話級（Session關閉掉之后數據就沒有了，當Commit的時候則數據還在，當Rollback的時候則數據也是一樣被回滾）：
insert into student(stu_id,class_id,stu_name,stu_memo) values(1,1,'張三','福建');
insert into student(stu_id,class_id,stu_name,stu_memo) values(2,1,'劉德華','福州');
insert into student(stu_id,class_id,stu_name,stu_memo) values(3,2,'S.H.E','廈門');
SQL select *from student ;
STU_ID CLASS_ID STU_NAME STU_MEMO
------ -------- -------- --------------------------------------------------------------------------------
1 1 張三 福建
2 1 劉德華 福州
3 2 S.H.E 廈門
4 2 張惠妹 廈門
SQL commit;
Commit complete
SQL select * from student ;
STU_ID CLASS_ID STU_NAME STU_MEMO
------ -------- -------- --------------------------------------------------------------------------------
1 1 張三 福建
2 1 劉德華 福州
3 2 S.H.E 廈門
4 2 張惠妹 廈門
SQLinsert into student(stu_id,class_id,stu_name,stu_memo) values(4,2,'張惠妹','廈門');
1 row inserted
SQL select * from student ;
STU_ID CLASS_ID STU_NAME STU_MEMO
------ -------- -------- --------------------------------------------------------------------------------
1 1 張三 福建
2 1 劉德華 福州
3 2 S.H.E 廈門
4 2 張惠妹 廈門
4 2 張惠妹 廈門
SQL rollback ;
Rollback complete
SQL select * from student ;
STU_ID CLASS_ID STU_NAME STU_MEMO
------ -------- -------- --------------------------------------------------------------------------------
1 1 張三 福建
2 1 劉德華 福州
3 2 S.H.E 廈門
4 2 張惠妹 廈門
SQL
2）、事務級（Commit之后就刪除數據）：本例子將采用以下的數據：
insert into classes(Class_id,Class_Name,Class_Memo) values(1,'計算機','9608');
insert into classes(Class_id,Class_Name,Class_Memo) values(2,'經濟信息','9602');
insert into classes(Class_id,Class_Name,Class_Memo) values(3,'經濟信息','9603');
在一個SESSION中（比如SQLPLUS登陸）插入上面3條記錄，然后再以另外一個SESSION（用SQLPLUS再登陸一次）登陸，當你select * from classes;的時候，classes表是空的，而你再第一次登陸的SQLPLUS中select的時候可以查詢到，這個時候你沒有進行commit或者rollback之前你可以對剛才插入的3條記錄進行update、delete等操作，當你進行commit或者rollback的時候，這個時候由于你的表是事務級的臨時表，那么在插入數據的session也看不到數據了，這個時候數據就已經被截斷了。
運行結果如下：
SQL insert into classes(Class_id,Class_Name,Class_Memo) values(1,'計算機','9608');
1 row inserted
SQL insert into classes(Class_id,Class_Name,Class_Memo) values(2,'經濟信息','9602');
1 row inserted
SQL insert into classes(Class_id,Class_Name,Class_Memo) values(3,'經濟信息','9603');
1 row inserted
SQL update classes set class_memo ='' where class_id=3 ;
1 row updated
SQL select * from classes ;
CLASS_ID CLASS_NAME CLASS_MEMO
-------- ---------- --------------------------------------------------------------------------------
1 計算機 9608
2 經濟信息 9602
3 經濟信息
SQL delete from classes where class_id=3 ;
1 row deleted
SQL select * from classes ;
CLASS_ID CLASS_NAME CLASS_MEMO
-------- ---------- --------------------------------------------------------------------------------
1 計算機 9608
2 經濟信息 9602
SQL commit;
Commit complete
SQL select *from classes ;
CLASS_ID CLASS_NAME CLASS_MEMO
-------- ---------- --------------------------------------------------------------------------------
SQL
再重復插入一次，然后rollback。
SQL Rollback ;
Rollback complete
SQL select * from classes ;
CLASS_ID CLASS_NAME CLASS_MEMO
-------- ---------- --------------------------------------------------------------------------------
SQL
臨時表的應用
1）、當某一個SQL語句關聯的表在2張及以上，并且和一些小表關聯。可以采用將大表進行分拆并且得到比較小的結果集合存放在臨時表中。
2）、程序執行過程中可能需要存放一些臨時的數據，這些數據在整個程序的會話過程中都需要用的等等。
5、注意事項：
1）、臨時表的索引以及對表的修改、刪除等和正常的表是一致的。
2）、Oracle的臨時表是Oracle8i才支持的功能特性，如果你的Oracle版本比較低的話，那么就可能沒有辦法用到了，如果你的Oracle版本是8i的話，你還需要把$ORACLE_HOME/admin/${ORACLE_SID}/pfile目錄下的initORACLE_SID.ora初始參數配置文件的compatible修改為compatible = "8.1.0"，我的服務器上就是這樣子配置的。當然也可以修改為compatible = "8.1.6"
Oracle臨時表
有兩種類型的臨時表：
會話級的臨時表
事務級的臨時表
1)
會話級的臨時表因為這這個臨時表中的數據和你的當前會話有關系，當你當前SESSION
不退出的情況下，臨時表中的數據就還存在，而當你退出當前SESSION
的時候，臨時表中的數據就全部沒有了，當然這個時候你如果以另外一個SESSION 登陸的時候是看不到另外一個SESSION
中插入到臨時表中的數據的。即兩個不同的SESSION 所插入的數據是互不相干的。當某一個SESSION
退出之后臨時表中的數據就被截斷(truncate table ，即數據清空)了。會話級的臨時表創建方法：
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) 事務級臨時表是指該臨時表與事務相關，當進行事務提交或者事務回滾的時候，臨時表中的數據將自行被截斷，其他的內容和會話級的臨時表的一致(包括退出SESSION 的時候，事務級的臨時表也會被自動截斷)。事務級臨時表的創建方法：
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 ;
3) 兩中類型臨時表的區別
會話級臨時表采用
on commit preserve rows ;而事務級則采用 on commit delete rows
;用法上，會話級別只有當會話結束臨時表中的數據才會被截斷，而且事務級臨時表則不管是 commit 、 rollback
或者是會話結束，臨時表中的數據都將被截斷
4 )什么時候使用臨時表
1 )、當某一個 SQL 語句關聯的表在 2 張及以上，并且和一些小表關聯。可以采用將大表進行分拆并且得到比較小的結果集合存放在臨時表中
2 )、程序執行過程中可能需要存放一些臨時的數據，這些數據在整個程序的會話過程中都需要用的等等。
3 .臨時表的不足之處
1 )不支持 lob 對象，這也許是設計者基于運行效率的考慮，但實際應用中確實需要此功能時就無法使用臨時表了。
2 )不支持主外鍵關系
oracle怎么查看臨時表空間具體存放的數據
1、查看臨時表空間 （dba_temp_files視圖）（v_$tempfile視圖）
select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;
select status,enabled, name, bytes/1024/1024 file_size from v_$tempfile;--sys用戶查看
2、縮小臨時表空間大小
alter database tempfile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TELEMT\TEMP01.DBF' resize 100M;
3、擴展臨時表空間：
方法一、增大臨時文件大小：
SQL alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp01.dbf’ resize 100m;
方法二、將臨時數據文件設為自動擴展：
SQL alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp01.dbf’ autoextend on next 5m maxsize unlimited;
方法三、向臨時表空間中添加數據文件：
SQL alter tablespace temp add tempfile ‘/u01/app/oracle/oradata/orcl/temp02.dbf’ size 100m;
4、創建臨時表空間：
SQL create temporary tablespace temp1 tempfile ‘/u01/app/oracle/oradata/orcl/temp11.dbf’ size 10M;
5、更改系統的默認臨時表空間：
--查詢默認臨時表空間
select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
--修改默認臨時表空間
alter database default temporary tablespace temp1;
所有用戶的默認臨時表空間都將切換為新的臨時表空間：
select username,temporary_tablespace,default_ from dba_users;
--更改某一用戶的臨時表空間：
alter user scott temporary tablespace temp;
6、刪除臨時表空間
刪除臨時表空間的一個數據文件：
SQL alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp02.dbf’ drop;
刪除臨時表空間(徹底刪除)：
SQL drop tablespace temp1 including contents and datafiles cascade constraints;
7、查看臨時表空間的使用情況（GV_$TEMP_SPACE_HEADER視圖必須在sys用戶下才能查詢）
GV_$TEMP_SPACE_HEADER視圖記錄了臨時表空間的使用大小與未使用的大小
dba_temp_files視圖的bytes字段記錄的是臨時表空間的總大小
SELECT temp_used.tablespace_name,
total - used as "Free",
total as "Total",
round(nvl(total - used, 0) * 100 / total, 3) "Free percent"
FROM (SELECT tablespace_name, SUM(bytes_used) / 1024 / 1024 used
FROM GV_$TEMP_SPACE_HEADER
GROUP BY tablespace_name) temp_used,
(SELECT tablespace_name, SUM(bytes) / 1024 / 1024 total
FROM dba_temp_files
GROUP BY tablespace_name) temp_total
WHERE temp_used.tablespace_name = temp_total.tablespace_name
ORDER BY B.TABLESPACE, B.SEGFILE#, B.SEGBLK#, B.BLOCKS;
希望能幫到您！
oracle如何查找用戶默認的表空間
1、用PL/SQL登錄到sys用戶，執行命令：select default_tablespace from user_users;
2、可以看到sys用戶默認的永久表空間為SYSTEM。
3、用PL/SQL登錄到我們要操作的用戶，比如lottery用戶。執行命令：select default_tablespace from user_users;
4、可以看到當前用戶的默認表空間為TS_LOTTERY_DATA。
5、上面查詢到的TS_LOTTERY_DATA是用戶的永久表空間，執行命令select * from user_users。
6、還可以查詢到當前用戶的臨時表空間及賬戶狀態等。
oracle中的臨時表
臨時表就是用來暫時保存臨時數據（亦或叫中間數據）的一個數據庫對象，它和普通表有些類似，然而又有很大區別。它只能存儲在臨時表空間，而非用戶的表空間。ORACLE臨時表是會話或事務級別的，只對當前會話或事務可見。每個會話只能查看和修改自己的數據。
ORACLE臨時表有兩種類型：會話級的臨時表和事務級的臨時表。
1）ON COMMIT DELETE ROWS
它是臨時表的默認參數，表示臨時表中的數據僅在事物過程（Transaction）中有效，當事物提交（COMMIT）后，臨時表的暫時段將被自動截斷（TRUNCATE），但是臨時表的結構 以及元數據還存儲在用戶的數據字典中。如果臨時表完成它的使命后，最好刪除臨時表，否則數據庫會殘留很多臨時表的表結構和元數據。
2）ON COMMIT PRESERVE ROWS
它表示臨時表的內容可以跨事物而存在，不過，當該會話結束時，臨時表的暫時段將隨著會話的結束而被丟棄，臨時表中的數據自然也就隨之丟棄。但是臨時表的結構以及元數據還存儲在用戶的數據字典中。如果臨時表完成它的使命后，最好刪除臨時表，否則數據庫會殘留很多臨時表的表結構和元數據。
1:會話級的臨時表的數據和你當前會話有關系，當前SESSION不退出的情況下，臨時表中的數據就還存在，臨時表的數據只有當你退出當前SESSION的時候才被截斷（TRUNCATE TABLE），如下所示：
會話級別的臨時表創建：
復制代碼
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;
1 row inserted
SQL COMMIT;
Commit complete
SQL SELECT * FROM TMP_TEST;
ID NAME
---------- ----------------
1 kerry
SQL INSERT INTO TMP_TEST
SELECT 2, 'rouce' FROM DUAL;
1 row inserted
SQL ROLLBACK;
Rollback complete
SQL SELECT * FROM TMP_TEST;
ID NAME
---------- ----------------------
1 kerry
SQL
復制代碼
2:事務級的臨時表（默認），這種類型的臨時表與事務有關，當進行事務提交或者事務回滾的時候，臨時表的數據將自行截斷，即當COMMIT或ROLLBACK時，數據就會被TRUNCATE掉，其它的特性和會話級的臨時表一致。
事務級臨時表的創建方法：
復制代碼
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;
1 row inserted
SQL SELECT * FROM TMP_TEST;
ID NAME
---------- ----------------------
1 kerry
SQL COMMIT;
Commit complete
SQL SELECT * FROM TMP_TEST;
ID NAME
---------- ------------------------
SQL
復制代碼
3:關于臨時表只對當前會話或事務可見。每個會話只能查看和修改自己的數據。
用DM用戶登錄數據庫，打開SESSION 1后，創建臨時表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;
1 row inserted
SQL SELECT * FROM TMP_TEST;
ID NAME
---------- ---------------------
1 kerry
SQL COMMIT;
Commit complete
SQL SELECT * FROM TMP_TEST;
ID NAME
---------- -----------------------
SQL
復制代碼
用sys用戶登錄數據庫，打開SESSION 2
SELECT * FROM DBA_TABLES WHERE TABLE_NAME='TMP_TEST' --可以查到臨時表數據
SELECT * FROM DM.TMP_TEST; --查不到數據，即使TMP_TEST臨時表存在數據。
臨時表與永久表區別
復制代碼
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
復制代碼
網站標題：oracle怎么找臨時表,oracle使用臨時表
轉載來源：http://vcdvsql.cn/article20/hsphco.html
成都網站建設公司_創新互聯，為您提供手機網站建設、營銷型網站建設、動態網站、網站營銷、微信小程序、搜索引擎優化
聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創新互聯
- 什么叫云服務器，云服務器關鍵用以哪一方面？ 2016-11-14
- 云服務器托管需要注意哪些問題？ 2022-10-09
- 云服務器的機房在哪 2021-02-08
- 虛擬主機和云服務器有哪些區別 2017-06-09
- 云服務器租用究竟有哪些優勢呢？ 2022-10-07
- 云服務器與傳統服務器的區別 2022-10-05
- 云服務器哪家比較好呢？怎么選擇好的云服務器提供商？ 2022-10-07
- 免費云服務器弊端有哪些 2022-10-02
- 虛擬主機和云服務器哪個更易于網站SEO優化？ 2014-01-11
- 為何云服務器如此受歡迎？ 2022-10-04
- 虛擬主機與云服務器 2021-02-14
- 如何選擇便宜穩定的香港云服務器？ 2022-10-06