create global temporary table table_name
(p1 number,
.......)
on commit delete/reserve rows;comments:
delete once the transaction is commit or rollback the table's contents will lost;
reserve only the user logout the table's contents will lost.
(p1 number,
.......)
on commit delete/reserve rows;comments:
delete once the transaction is commit or rollback the table's contents will lost;
reserve only the user logout the table's contents will lost.
解决方案 »
- emp表中如何统计每个员工的领导的年薪,并按年薪由高到低排列
- oracle10g的isqlplus无法启动,很有意思
- 求助~~~~~~~~~~
- oracle 9i + solaris 9里面的内存分配问题
- rowid 问题
- procedure错误问题 急在线等
- 在存储过程中传递空变量
- 如何让oracle自动维护索引,即插入一行时候不需要指明索引字段
- 如何查找TNS listener 所占用的端口号? (用网卡的tcp/ip 来过滤端口,所以必须得知道这些端口以便添加进去)
- 100分求教汇总统计方法
- 再问DBASTUDIO中备份导入导出数据库的问题?
- 值得学习的存储过程问题,大家都来看,我给高分!!!
那是不是可以这样:
create temporary table test
(
........
)在过程中使用此表。当commit或rollback后此表中数据被清空,而表仍然在?
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. The CREATE GLOBAL TEMPORARY TABLE command creates a temporary table which can be transaction specific or session specific. For transaction-specific temporary tables, data exists for the duration of the transaction while 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 command has no effect on a temporary table as each session has its own private data. 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 crash. You can create indexes for temporary tables using the CREATE INDEX command. 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. The EXPORT and IMPORT utilities can export and import the definition of a temporary table. However, no data rows are exported even if you use the ROWS option. Similarly, you can replicate the definition of a temporary table but you cannot replicate its data.
COL1 VARCHAR2(10),
COL2 NUMBER
) ON COMMIT PRESERVE(DELETE) ROWS ;
这种临时表不占用表空间,而且不同的SESSION之间互相看不到对方的数据
在会话结束后表中的数据自动清空,如果选了DELETE ROWS,则在提交的时候即清空数据,PRESERVE则一直到会话结束