Last month a question was asked on OTN forum regarding table name where the user had dropped a 450Gb table and while the drop was in progress the user could see a table segment with a weird name “234.16632” of the same size as the original table that being dropped.
Link:- http://forums.oracle.com/forums/thread.jspa?forumID=61&threadID=1000550
After a few searches found that usually when an index is created,rebuild or a table is moved within the same tablespace or different a ‘TEMPORARY’ segment gets created in a normal permanent tablespaces.
The interesting thing to notice is the temporary segment name.Its numeric in value.Question is what do these “numeric values represent”.
The numeric segment names are named as “FILENUMBER.HEADERBLOCKNUMBER”.
Time to test now:-
session 1:-
17:22:26 ANAND @ oracle >create tablespace temp_test datafile 'D:\ORACLE\PRODUCT\10.2.0\ORACLE\temp_test_01.dbf' size 100M autoextend on next 5M maxsize 1024M 17:24:51 2 extent management local 17:25:04 3 segment space management auto; Tablespace created. Elapsed: 00:00:04.15 17:39:45 ANAND @ oracle >create table dummy as select * from dba_objects union select * from dba_objects union select * from dba_objects union select * from dba_objects 17:40:27 2 union select * from dba_objects; Table created. Elapsed: 00:00:03.71
session 2:-
17:39:46 ANAND @ oracle > select segment_name,segment_type,header_file,header_block,extents,(bytes/1024/1024)MB from dba_segments
17:40:22 2 where segment_type = 'TEMPORARY' and tablespace_name = 'TEMP_TEST'
17:40:22 3 union
17:40:22 4 select segment_name,segment_type,header_file,header_block,extents,(bytes/1024/1024)MB from dba_segments
17:40:22 5 where segment_name in ('DUMMY','DUMMY_INDX') and tablespace_name = 'TEMP_TEST' order by segment_name, header_file
17:40:22 6 /
no rows selected
Elapsed: 00:00:00.48
17:40:28 ANAND @ oracle >/
SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK EXTENTS MB
----------------------------- ------------------ ----------- ------------ ---------- ----------
6.9 TEMPORARY 6 9 21 6
Elapsed: 00:00:00.17
17:47:57 ANAND @ oracle >/
SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK EXTENTS MB
----------------------------- ------------------ ----------- ------------ ---------- ----------
DUMMY TABLE 6 9 21 6
session 1:-
17:50:16 ANAND @ oracle >insert into dummy select * from dummy union all select * from dummy union all select * from dummy union all select * from dummy; 202440 rows created. Elapsed: 00:00:07.84 17:50:32 ANAND @ oracle >commit; Commit complete. Elapsed: 00:00:00.06
session 2:-
17:50:28 ANAND @ oracle >/ SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK EXTENTS MB ------------------------------- ------------------ ----------- ------------ ---------- ---------- DUMMY TABLE 6 9 24 9 Elapsed: 00:00:00.12 17:50:29 ANAND @ oracle >/ SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK EXTENTS MB ------------------------------- ------------------ ----------- ------------ ---------- ---------- DUMMY TABLE 6 9 26 11 Elapsed: 00:00:00.37 17:50:30 ANAND @ oracle >/ SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK EXTENTS MB -------------------------------- ------------------ ----------- ------------ ---------- ---------- DUMMY TABLE 6 9 29 14 Elapsed: 00:00:00.09 17:50:31 ANAND @ oracle >/ SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK EXTENTS MB -------------------------------- ------------------ ----------- ------------ ---------- ---------- DUMMY TABLE 6 9 37 22 Elapsed: 00:00:00.29
For inserts statements no temporary segments are created as the table is already created and extents get allocated to fit the data.
session 1:-
17:50:30 ANAND @ oracle >create index dummy_indx on dummy(owner,object_name); Index created. Elapsed: 00:00:02.37 17:51:51 ANAND @ oracle >alter index dummy_indx rebuild online; Index altered. Elapsed: 00:00:02.46 17:52:49 ANAND @ oracle >alter table dummy move; Table altered. Elapsed: 00:00:08.46
session 2:-
17:50:31 ANAND @ oracle >/ (create index command) SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK EXTENTS MB -------------------------------- ------------------ ----------- ------------ ---------- ---------- 6.3593 TEMPORARY 6 3593 26 11 DUMMY TABLE 6 9 43 28 Elapsed: 00:00:00.40 17:51:50 ANAND @ oracle >/ (after create index completed) SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK EXTENTS MB --------------------------------- ------------------ ----------- ------------ ---------- ---------- DUMMY TABLE 6 9 43 28 DUMMY_INDX INDEX 6 3593 27 12 Elapsed: 00:00:00.29 17:51:52 ANAND @ oracle >/ SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK EXTENTS MB --------------------------------- ------------------ ----------- ------------ ---------- ---------- 6.5137 TEMPORARY 6 5137 24 9 DUMMY TABLE 6 9 43 28 DUMMY_INDX INDEX 6 3593 27 12 Elapsed: 00:00:00.65 17:53:53 ANAND @ oracle >/ (table move command) SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK EXTENTS MB --------------------------------- ------------------ ----------- ------------ ---------- ---------- 6.9 TEMPORARY 6 9 43 28 DUMMY TABLE 6 5137 43 28 DUMMY_INDX INDEX 6 5129 27 12 Elapsed: 00:00:00.70 17:53:55 ANAND @ oracle >/ SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK EXTENTS MB ---------------------------------- ------------------ ----------- ------------ ---------- ---------- DUMMY TABLE 6 5137 43 28 DUMMY_INDX INDEX 6 5129 27 12 Elapsed: 00:00:00.32
As, we can can see temporary segments which on successful creation is converted to permanent segment are created in the temp_test tablespace which is a permanent tablespace while the index are created or rebuild or the table is moved, but still i am not able to get the the temporary segment for the “drop table” command.
It was a new learning for me so thought to blog it :). Thanks to Hemant Sir http://hemantoracledba.blogspot.com/2008/05/temporary-segments-in-dataindex.html
SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK EXTENTS MB
——————————————————————————— —————— ———– ———— ———- ———-
6.9 TEMPORARY 6 9 21 6
Elapsed: 00:00:00.17
17:47:57 ANAND @ oracle >/
SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK EXTENTS MB
——————————————————————————— —————— ———– ———— ———- ———-
DUMMY TABLE 6 9 21 6
4 thoughts on “Segment name – Numeric”
to get a temporary segment for drop table/index, you can test it as follows.
1. create the object.
create table james_test tablespace temp_test as
select a.* from dba_objects a,dba_objects b where rownum <= 200000;
create index james_test_oid_oname_idx on james_test (object_id,object_name) tablespace temp_test;
check the file#,header_block of the table/index segment.
2. alter tablespace read only
alter tablespace temp_test read only;
3. drop the table / index
drop table james_test;
drop index james_test_oid_oname_idx ;
4. check the segment_type/segment_name of the related file#/header_block
select segment_name,segment_type,header_file,header_block
from user_segments
where segment_type = 'TEMPORARY';