在创建大的索引，或者Rebuild大的索引以及使用CTAS创建大表，move大表的时候，我们经常可以看到一些纯粹数字表示(如”xxxx.xxxxx”)的类型为“TEMPORARY”的segment.之前我一直不清楚里面的两个数字是什么意思,曾经怀疑其与将要创建的object_id有某种关系, 前两天在 http://aprakash.wordpress.com/看到一个blog,说里面的数字是”filenumber.header_HEADERBLOCKNUMBER”,并且提供了比较详细的例子. 经过在我在测试环境的测试, 我觉得其更应该被表述为”header_file_number.header_block_number”.
在此特别感谢Anand与Hemant. 这篇文章的主要思想都是来自两位..
1. 在Session 1中创建一个相对较大的表temp_obj. 同时在session 2中监控相应信息.
--Session 1> 09:30:52 SQL> 09:30:52 SQL> create table temp_obj tablespace users as 09:31:04 2 select a.* 09:31:04 3 from dba_objects a,dba_objects b 09:31:04 4 where rownum <= 2000000; Table created. Elapsed: 00:00:31.68 09:31:42 SQL>
--Session 2
09:30:56 SQL> l
1 select owner,segment_name,segment_type,header_file,header_block,blocks
2 from DBA_segments
3 where segment_name in ('TEMP_OBJ_IND','TEMP_OBJ')
4* or segment_type = 'TEMPORARY'
09:31:08 SQL> /
header header
OWNER SEGMENT_NAME SEGMENT_TYPE file block BLOCKS
---------- --------------- ------------------ ---------- ---------- ----------
JAMES 4.59 TEMPORARY 4 59 768
09:31:14 SQL> /
header header
OWNER SEGMENT_NAME SEGMENT_TYPE file block BLOCKS
---------- --------------- ------------------ ---------- ---------- ----------
JAMES 4.59 TEMPORARY 4 59 2816
09:31:20 SQL> /
header header
OWNER SEGMENT_NAME SEGMENT_TYPE file block BLOCKS
---------- --------------- ------------------ ---------- ---------- ----------
JAMES 4.59 TEMPORARY 4 59 8064
09:31:25 SQL> /
header header
OWNER SEGMENT_NAME SEGMENT_TYPE file block BLOCKS
---------- --------------- ------------------ ---------- ---------- ----------
JAMES 4.59 TEMPORARY 4 59 12288
09:31:32 SQL> /
header header
OWNER SEGMENT_NAME SEGMENT_TYPE file block BLOCKS
---------- --------------- ------------------ ---------- ---------- ----------
JAMES 4.59 TEMPORARY 4 59 18432
09:31:40 SQL> /
header header
OWNER SEGMENT_NAME SEGMENT_TYPE file block BLOCKS
---------- --------------- ------------------ ---------- ---------- ----------
JAMES TEMP_OBJ TABLE 4 59 23552
09:31:44 SQL>
2. 接着再在Session 1中创建一个基于表temp_obj的索引temp_obj_ind. 同时在session 2中监控相应信息.
--Session 1 09:31:42 SQL> create index temp_obj_ind on temp_obj(object_id,object_name,object_type) tablespace users; Index created. Elapsed: 00:00:33.14 09:36:48 SQL>
--Session 2
09:36:01 SQL> l
1 select owner,segment_name,segment_type,header_file,header_block,blocks
2 from DBA_segments
3 where segment_name in ('TEMP_OBJ_IND','TEMP_OBJ')
4* or segment_type = 'TEMPORARY'
09:36:05 SQL> /
header header
OWNER SEGMENT_NAME SEGMENT_TYPE file block BLOCKS
---------- --------------- ------------------ ---------- ---------- ----------
JAMES TEMP_OBJ TABLE 4 59 23552
09:36:23 SQL> /
header header
OWNER SEGMENT_NAME SEGMENT_TYPE file block BLOCKS
---------- --------------- ------------------ ---------- ---------- ----------
JAMES TEMP_OBJ TABLE 4 59 23552
09:36:25 SQL> /
header header
OWNER SEGMENT_NAME SEGMENT_TYPE file block BLOCKS
---------- --------------- ------------------ ---------- ---------- ----------
JAMES TEMP_OBJ TABLE 4 59 23552
09:36:27 SQL> /
header header
OWNER SEGMENT_NAME SEGMENT_TYPE file block BLOCKS
---------- --------------- ------------------ ---------- ---------- ----------
JAMES TEMP_OBJ TABLE 4 59 23552
JAMES 4.2539 TEMPORARY 4 2539 640
09:36:33 SQL> /
header header
OWNER SEGMENT_NAME SEGMENT_TYPE file block BLOCKS
---------- --------------- ------------------ ---------- ---------- ----------
JAMES TEMP_OBJ TABLE 4 59 23552
JAMES 4.2539 TEMPORARY 4 2539 1408
09:36:38 SQL> /
header header
OWNER SEGMENT_NAME SEGMENT_TYPE file block BLOCKS
---------- --------------- ------------------ ---------- ---------- ----------
JAMES TEMP_OBJ TABLE 4 59 23552
JAMES 4.2539 TEMPORARY 4 2539 4992
09:36:44 SQL> /
header header
OWNER SEGMENT_NAME SEGMENT_TYPE file block BLOCKS
---------- --------------- ------------------ ---------- ---------- ----------
JAMES TEMP_OBJ TABLE 4 59 23552
JAMES 4.2539 TEMPORARY 4 2539 7552
09:36:46 SQL> /
header header
OWNER SEGMENT_NAME SEGMENT_TYPE file block BLOCKS
---------- --------------- ------------------ ---------- ---------- ----------
JAMES TEMP_OBJ_IND INDEX 4 2539 8064
JAMES TEMP_OBJ TABLE 4 59 23552
09:36:49 SQL>
3. 处理alter table move操作.
--Session 1 12:55:32 SQL> alter table temp_obj move; Table altered. Elapsed: 00:00:45.57 12:56:27 SQL>
--Session 2
12:55:23 SQL> l
1 select owner,segment_name,segment_type,header_file,header_block,blocks
2 from DBA_segments
3 where segment_name in ('TEMP_OBJ_IND','TEMP_OBJ')
4* or segment_type = 'TEMPORARY'
12:55:24 SQL>
12:55:43 SQL> /
header header
OWNER SEGMENT_NAME SEGMENT_TYPE file block BLOCKS
---------- --------------- ------------------ ---------- ---------- ----------
JAMES TEMP_OBJ_IND INDEX 4 2539 8064
JAMES TEMP_OBJ TABLE 4 59 23552
JAMES 4.6395 TEMPORARY 4 6395 1152
12:56:01 SQL> /
header header
OWNER SEGMENT_NAME SEGMENT_TYPE file block BLOCKS
---------- --------------- ------------------ ---------- ---------- ----------
JAMES TEMP_OBJ_IND INDEX 4 2539 8064
JAMES TEMP_OBJ TABLE 4 59 23552
JAMES 4.6395 TEMPORARY 4 6395 12288
12:56:16 SQL> /
header header
OWNER SEGMENT_NAME SEGMENT_TYPE file block BLOCKS
---------- --------------- ------------------ ---------- ---------- ----------
JAMES TEMP_OBJ_IND INDEX 4 2539 8064
JAMES TEMP_OBJ TABLE 4 59 23552
JAMES 4.6395 TEMPORARY 4 6395 19456
12:56:21 SQL> /
header header
OWNER SEGMENT_NAME SEGMENT_TYPE file block BLOCKS
---------- --------------- ------------------ ---------- ---------- ----------
JAMES TEMP_OBJ TABLE 4 6395 23552
JAMES TEMP_OBJ_IND INDEX 4 2539 8064
JAMES 4.59 TEMPORARY 4 59 23552
12:56:27 SQL> /
header header
OWNER SEGMENT_NAME SEGMENT_TYPE file block BLOCKS
---------- --------------- ------------------ ---------- ---------- ----------
JAMES TEMP_OBJ TABLE 4 6395 23552
JAMES TEMP_OBJ_IND INDEX 4 2539 8064
12:56:31 SQL>
4. alter index rebuild 操作.
--Session 1 12:56:27 SQL> alter index temp_obj_ind rebuild; Index altered. Elapsed: 00:00:31.92 13:00:08 SQL>
--Session 2
12:59:26 SQL> l
1 select owner,segment_name,segment_type,header_file,header_block,blocks
2 from DBA_segments
3 where segment_name in ('TEMP_OBJ_IND','TEMP_OBJ')
4* or segment_type = 'TEMPORARY'
12:59:27 SQL> /
header header
OWNER SEGMENT_NAME SEGMENT_TYPE file block BLOCKS
---------- --------------- ------------------ ---------- ---------- ----------
JAMES TEMP_OBJ TABLE 4 6395 23552
JAMES TEMP_OBJ_IND INDEX 4 2539 8064
12:59:44 SQL> /
header header
OWNER SEGMENT_NAME SEGMENT_TYPE file block BLOCKS
---------- --------------- ------------------ ---------- ---------- ----------
JAMES TEMP_OBJ TABLE 4 6395 23552
JAMES TEMP_OBJ_IND INDEX 4 2539 8064
JAMES 4.59 TEMPORARY 4 59 512
12:59:57 SQL> /
header header
OWNER SEGMENT_NAME SEGMENT_TYPE file block BLOCKS
---------- --------------- ------------------ ---------- ---------- ----------
JAMES TEMP_OBJ TABLE 4 6395 23552
JAMES TEMP_OBJ_IND INDEX 4 2539 8064
JAMES 4.59 TEMPORARY 4 59 4736
13:00:03 SQL> /
header header
OWNER SEGMENT_NAME SEGMENT_TYPE file block BLOCKS
---------- --------------- ------------------ ---------- ---------- ----------
JAMES TEMP_OBJ TABLE 4 6395 23552
JAMES TEMP_OBJ_IND INDEX 4 59 8064
13:00:08 SQL>
5. 在表空间为Read Only的时候,,如果删除表空间内部的对象,,对应的segment_type会变成TEMPORARY.
13:13:50 SQL> create table temp_obj tablespace test as 13:14:05 2 select a.* 13:14:05 3 from dba_objects a,dba_objects b 13:14:05 4 where rownum <= 1000000; Table created. Elapsed: 00:00:13.64 13:14:18 SQL> alter tablespace test read only; Tablespace altered. Elapsed: 00:00:00.57 13:14:35 SQL> select segment_name,segment_type,header_file,header_block 13:14:44 2 from dba_segments 13:14:44 3 where segment_name = 'TEMP_OBJ' or segment_type = 'TEMPORARY'; SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK -------------------- ------------------ ----------- ------------ TEMP_OBJ TABLE 6 11 Elapsed: 00:00:00.28 13:14:46 SQL> drop table temp_obj purge; Table dropped. Elapsed: 00:00:00.06 13:14:55 SQL> select segment_name,segment_type,header_file,header_block 13:14:57 2 from dba_segments 13:14:58 3 where segment_name = 'TEMP_OBJ' or segment_type = 'TEMPORARY'; SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK -------------------- ------------------ ----------- ------------ 6.11 TEMPORARY 6 11 Elapsed: 00:00:00.09
No related posts.
最近评论