1.臨時(shí)表不支持物化視圖 1)環(huán)境準(zhǔn)備 (1)創(chuàng)建基于會(huì)話的臨時(shí)表 sec@ora10g> create global temporary table t_temp_session (x int) on commit preserve rows;
Table created.
sec@ora10g> col TABLE_NAME for a30 sec@ora10g> col TEMPORARY for a10 sec@ora10g> select TABLE_NAME,TEMPORARY from user_tables where table_name = 'T_TEMP_SESSION';
TABLE_NAME TEMPORARY ------------------------------ ---------- T_TEMP_SESSION Y
(2)初始化兩條數(shù)據(jù) sec@ora10g> insert into t_temp_session values (1);
1 row created.
sec@ora10g> insert into t_temp_session values (2);
1 row created.
sec@ora10g> commit;
Commit complete.
sec@ora10g> select * from t_temp_session;
X ---------- 1 2
(3)在臨時(shí)表T_TEMP_SESSION上添加主鍵 sec@ora10g> alter table T_TEMP_SESSION add constraint PK_T_TEMP_SESSION primary key(x);
Table altered.
2)在臨時(shí)表T_TEMP_SESSION上創(chuàng)建物化視圖 (1)創(chuàng)建物化視圖日志日志 sec@ora10g> create materialized view log on T_TEMP_SESSION with sequence, rowid (x) including new values; create materialized view log on T_TEMP_SESSION with sequence, rowid (x) including new values * ERROR at line 1: ORA-14451: unsupported feature with temporary table
(2)創(chuàng)建物化視圖 sec@ora10g> create materialized view mv_T_TEMP_SESSION build immediate refresh fast on commit enable query rewrite as select * from T_TEMP_SESSION; create materialized view mv_T_TEMP_SESSION build immediate refresh fast on commit enable query rewrite as select * from T_TEMP_SESSION * ERROR at line 1: ORA-23413: table "SEC"."T_TEMP_SESSION" does not have a materialized view log
由于物化視圖日志沒有創(chuàng)建成功,因此顯然物化視圖亦無(wú)法創(chuàng)建。
2.在臨時(shí)表上創(chuàng)建索引 sec@ora10g> create index i_t_temp_session on t_temp_session (x);
Index created.
臨時(shí)表上索引創(chuàng)建成功。
3.基于臨時(shí)表創(chuàng)建視圖 sec@ora10g> create view v_t_temp_session as select * from t_temp_session where x<100;
Export: Release 10.2.0.1.0 - Production on Wed Jun 29 22:06:43 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ... . . exporting table T_TEMP_SESSION Export terminated successfully without warnings.
Import: Release 10.2.0.1.0 - Production on Wed Jun 29 22:06:57 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set . importing SEC's objects into SEC . importing SEC's objects into SEC "CREATE GLOBAL TEMPORARY TABLE "T_TEMP_SESSION" ("X" NUMBER(*,0)) ON COMMIT " "PRESERVE ROWS " "CREATE INDEX "I_T_TEMP_SESSION" ON "T_TEMP_SESSION" ("X" ) " Import terminated successfully without warnings.
Import: Release 10.2.0.1.0 - Production on Wed Jun 29 22:07:16 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set . importing SEC's objects into SEC . importing SEC's objects into SEC Import terminated successfully without warnings.
依然顯示沒有記錄被導(dǎo)入。
5.查看臨時(shí)表空間的使用情況 可以通過查詢V$SORT_USAGE視圖獲得相關(guān)信息。 sec@ora10g> select username,tablespace,session_num sid,sqladdr,sqlhash,segtype,extents,blocks from v$sort_usage;
7.臨時(shí)表數(shù)據(jù)將不會(huì)上DML(Data Manipulation Language)鎖 1)在新session中查看SEC用戶下鎖信息 sec@ora10g> col username for a8 sec@ora10g> select 2 b.username, 3 a.sid, 4 b.serial#, 5 a.type "lock type", 6 a.id1, 7 a.id2, 8 a.lmode 9 from v$lock a, v$session b 10 where a.sid=b.sid and b.username = 'SEC' 11 order by username,a.sid,serial#,a.type;
no rows selected
不存在任何鎖信息。
2)向臨時(shí)表中插入數(shù)據(jù),查看鎖信息 (1)插入數(shù)據(jù) sec@ora10g> insert into t_temp_session values (1);
1 row created.
(2)查看鎖信息 sec@ora10g> select 2 b.username, 3 a.sid, 4 b.serial#, 5 a.type "lock type", 6 a.id1, 7 a.id2, 8 a.lmode 9 from v$lock a, v$session b 10 where a.sid=b.sid and b.username = 'SEC' 11 order by username,a.sid,serial#,a.type;
sec@ora10g> select 2 b.username, 3 a.sid, 4 b.serial#, 5 a.type "lock type", 6 a.id1, 7 a.id2, 8 a.lmode 9 from v$lock a, v$session b 10 where a.sid=b.sid and b.username = 'SEC' 11 order by username,a.sid,serial#,a.type;