dba_segments之segment_type='TEMPORARY'
TEMPORARY:臨時段。除了磁碟排序產生臨時段之外,臨時表也會有臨時段。另外,在CTAS過程中,如果SQL還沒有最終完成,這個時候的表對應的段為TEMPORARY表,只有在SQL執行的最後將TEMPORARY段改為TABLE段。比如:
SQL> create table t2 as select * from dba_objects;
在執行上面語句的同時,執行:
SQL> select segment_type,owner,segment_name from dba_segments where segment_type=’TEMPORARY’;
SEGMENT_TYPE OWNER SEGMENT_NAME
------------------ ------------------------------ --------------------------------------------
TEMPORARY SYS 1.84337
可以看到臨時段,在CTAS執行完之後,我們可以看到:
SQL> select segment_type,owner,segment_name from dba_segments where segment_type=’TEMPORARY’;
未選定行
SQL> select header_file,header_block from dba_segments where wner=USER and segment_name=’T2′;
HEADER_FILE HEADER_BLOCK
----------- ------------
1 84337
可以看到,之前的臨時段(其段名為一個特別的名字1.84337,段頭的檔案號和塊號),與CTAS後的表的段頭一致。
另外在表和索引的MOVE、REBUILD階段也會有臨時段。所以臨時段不一定是在臨時表中,在普通的表空間中也可能會存在。
注意在排序段和臨時表的段在並沒有在DBA_SEGMENTS檢視,而是在V$TEMPSEG_USAGE檢視中。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/9240380/viewspace-759630/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Allocation of Temporary Segments for Temporary Tables and Indexes (28)Index
- Dba_segments詳解
- oracle temporary tableOracle
- 查詢DBA_SEGMENTS,bytes=0
- 記憶體(memory)表和臨時(temporary)表之瞭解記憶體
- [Oracle Script] Temporary Sort UsageOracle
- Restrictions on Altering Temporary TablesREST
- Operations that Require Temporary Segments (26)UI
- Temporary tablespaces in RAC ? Oracle databas...Oracle
- 資料庫表--temporary table資料庫
- Find and Fix the Mismatch Between DBA_SEGMENTS and DBA_EXTENTS ViewsView
- dba_segments/dba_extents/dba_tables區別
- Oracle臨時表GLOBAL TEMPORARY TABLEOracle
- Oracle Temporary Tables(Oracle 臨時表)Oracle
- oracle的兩種global temporary table!Oracle
- Expert Tips on Drop Temporary Tablespace Hangs!!
- ftp_rawlist: Unable to create temporary file.FTP
- Sybase IQ 錯誤 : Temporary space limit exceededMIT
- 全域性臨時表 GLOBAL TEMPORARY TABLE
- dba_tables 和 dba_segments 表中 blocks 的區別BloC
- Ora-600 [15160] Joining Dba_objects and Dba_segmentsObject
- Temporary failure resolving ‘archive.ubuntu.com‘AIHiveUbuntu
- Unable to generate a temporary class (result=1)解決方法
- oracle的臨時表空間temporary tablespaceOracle
- 【案例分析】ORA-25153: Temporary Tablespace is Empty
- SpringBoot檔案上傳異常之提示The temporary upload location xxx is not validSpring Boot
- Oracle佇列鎖enq:TS,Temporary Segment (also TableSpace)Oracle佇列ENQ
- nginx的an upstream response is buffered to a temporary file報錯Nginx
- 【TEMPORARY TABLE】Oracle臨時表使用注意事項Oracle
- 臨時表空間temporary tablespace相關操作
- name server cannot be used, reason: Temporary failure in name resolutionServerAI
- ORA-25153: Temporary Tablespace is Empty 解決方法
- Redo內部解析-Global Temporary table insert(九)
- 深度解析dba_segments和sys.seg$中的細節差異(上)
- 深度解析dba_segments和sys.seg$中的細節差異(下)
- Resolving archive.cloudera.com... failed: Temporary failure in nameHiveCloudAI
- IP address could not be resolved: Temporary failure in name resolutionAI
- 【MySQL】ERROR 1878 (HY000): Temporary file write failure.MySqlErrorAI