Segment name – Numeric

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

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

About these ads
  1. 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';

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 463 other followers

%d bloggers like this: