正文
oracle怎么找临时表,oracle使用临时表
小程序：扫一扫查出行【扫一扫了解最新限行尾号】
复制小程序
oracle 怎么在存储过程中创建一个临时表，在里面插入数据，再查找这个临时表的所有数据，最后drop这个表。
Oracle
的临时表的
处理机制，
和
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
复制代码
