I ran some CREATE, INSERT, REBUILD and MOVE commands from one session and monitored segments in that tablespace from another session concurrently. The timestamps in the outputs show how and when the "TEMPORARY" segments are created by Oracle. {Note : Take particular attention when using Parallel operations -- eg CREATE or REBUILD operations using Parallelism -- there are at least as many
Temporary Segments created as the number of Parallel Slaves as each slave creates it's own Segment (and extents) and these are merged by the Query Co-ordinator on
completion.}
This is my test session with the CREATE, INSERT, REBUILD and MOVE operations :
18:54:23 SQL>
18:54:23 SQL>
18:54:23 SQL> REM To demonstrate how and when Temporary segments NOT in the TEMP tablespace might get created
18:54:23 SQL>
18:54:23 SQL> rem Deliberately create the TBS with small 64K extents so that the operation is slow
18:54:23 SQL> rem and we can query and find the Temporary segments while existant for a short while
18:54:23 SQL>
18:54:23 SQL> create tablespace test_tbs
18:54:23 2 datafile 'F:\OR10G2DB\test_tbs_01.dbf' SIZE 200M autoextend on next 10M maxsize 2000M
18:54:23 3 extent management local uniform size 64K segment space management auto;
Tablespace created.
18:54:34 SQL>
18:54:34 SQL> alter session set workarea_size_policy='MANUAL';
Session altered.
18:54:34 SQL> alter session set sort_area_size=65536;
Session altered.
18:54:34 SQL>
18:54:34 SQL> pause press ENTER to proceed
press ENTER to proceed
18:54:43 SQL>
18:54:43 SQL> rem Create a largish table. Do NOT use NOLOGGING or APPEND -- just so that the operation is slower
18:54:43 SQL> pause press ENTER to proceed
press ENTER to proceed
18:54:45 SQL> create table test_table tablespace test_tbs as select * from dba_objects union all select * from dba_objects union all select * from dba_objects;
Table created.
18:54:50 SQL>
18:54:50 SQL>
18:54:50 SQL> rem Enlarge the table
18:54:50 SQL> pause press ENTER to proceed -- see if you can find TEMPORARY segments while the CREATE is running
press ENTER to proceed -- see if you can find TEMPORARY segments while the CREATE is running
18:54:54 SQL> insert into test_table select * from test_table union all select * from test_table;
310044 rows created.
18:55:06 SQL> pause press ENTER to proceed -- see if you can find TEMPORARY segments while the CREATE is running
press ENTER to proceed -- see if you can find TEMPORARY segments while the CREATE is running
18:55:08 SQL> create index test_table_ndx_1 on test_table(owner,object_name) tablespace test_tbs;
Index created.
18:55:47 SQL>
18:55:47 SQL> rem rebuild index and table
18:55:47 SQL> pause press ENTER to proceed -- see if you can find TEMPORARY segments while the REBUILD is running
press ENTER to proceed -- see if you can find TEMPORARY segments while the REBUILD is running
18:55:55 SQL> alter index test_table_ndx_1 rebuild;
Index altered.
18:56:11 SQL> pause press ENTER to proceed -- see if you can find TEMPORARY segments while the MOVE is running
press ENTER to proceed -- see if you can find TEMPORARY segments while the MOVE is running
18:56:16 SQL> alter table test_table move;
Table altered.
18:56:37 SQL> pause press ENTER to proceed -- see if you can find TEMPORARY segments while the REBUILD is running
press ENTER to proceed -- see if you can find TEMPORARY segments while the REBUILD is running
18:56:41 SQL> alter index test_table_ndx_1 rebuild;
Index altered.
18:57:23 SQL>
18:57:23 SQL>
And this is what I see in another session monitoring the first :
18:54:29 SQL> /
no rows selected
18:54:29 SQL> l
1 select segment_name,segment_type,header_file,header_block,extents,bytes/1024/1024 sz
2 from dba_segments
3 where segment_type = 'TEMPORARY'
4 and tablespace_name = 'TEST_TBS'
5 union
6 select segment_name,segment_type,header_file,header_block,extents,bytes/1024/1024 sz
7 from dba_segments
8 where segment_name in ('TEST_TABLE','TEST_TABLE_NDX_1')
9 and tablespace_name = 'TEST_TBS'
10* order by segment_name, header_file
18:54:31 SQL> /
no rows selected
18:54:32 SQL> /
no rows selected
18:54:41 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
2.11 TEMPORARY 2 11 143 8.94
18:54:48 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
TEST_TABLE TABLE 2 11 272 17.00
18:54:50 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
TEST_TABLE TABLE 2 11 375 23.44
18:54:56 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
TEST_TABLE TABLE 2 11 375 23.44
18:54:59 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
TEST_TABLE TABLE 2 11 714 44.63
18:55:05 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
TEST_TABLE TABLE 2 11 812 50.75
18:55:11 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
TEST_TABLE TABLE 2 11 812 50.75
18:55:13 SQL>
18:55:17 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
TEST_TABLE TABLE 2 11 812 50.75
18:55:17 SQL>
18:55:20 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
TEST_TABLE TABLE 2 11 812 50.75
18:55:21 SQL>
18:55:23 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
TEST_TABLE TABLE 2 11 812 50.75
18:55:24 SQL>
18:55:27 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
TEST_TABLE TABLE 2 11 812 50.75
18:55:28 SQL>
18:55:30 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
TEST_TABLE TABLE 2 11 812 50.75
18:55:31 SQL>
18:55:34 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
2.6507 TEMPORARY 2 6507 41 2.56
TEST_TABLE TABLE 2 11 812 50.75
18:55:35 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
2.6507 TEMPORARY 2 6507 114 7.13
TEST_TABLE TABLE 2 11 812 50.75
18:55:37 SQL>
18:55:44 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
2.6507 TEMPORARY 2 6507 271 16.94
TEST_TABLE TABLE 2 11 812 50.75
18:55:45 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
TEST_TABLE TABLE 2 11 812 50.75
TEST_TABLE_NDX_1 INDEX 2 6507 340 21.25
18:55:48 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
2.9227 TEMPORARY 2 9227 2 .13
TEST_TABLE TABLE 2 11 812 50.75
TEST_TABLE_NDX_1 INDEX 2 6507 340 21.25
18:55:57 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
2.9227 TEMPORARY 2 9227 62 3.88
TEST_TABLE TABLE 2 11 812 50.75
TEST_TABLE_NDX_1 INDEX 2 6507 340 21.25
18:56:00 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
2.9227 TEMPORARY 2 9227 259 16.19
TEST_TABLE TABLE 2 11 812 50.75
TEST_TABLE_NDX_1 INDEX 2 6507 340 21.25
18:56:05 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
2.9227 TEMPORARY 2 9227 278 17.38
TEST_TABLE TABLE 2 11 812 50.75
TEST_TABLE_NDX_1 INDEX 2 6507 340 21.25
18:56:08 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
TEST_TABLE TABLE 2 11 812 50.75
TEST_TABLE_NDX_1 INDEX 2 9227 340 21.25
18:56:12 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
2.6507 TEMPORARY 2 6507 175 10.94
TEST_TABLE TABLE 2 11 812 50.75
TEST_TABLE_NDX_1 INDEX 2 9227 340 21.25
18:56:19 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
2.6507 TEMPORARY 2 6507 407 25.44
TEST_TABLE TABLE 2 11 812 50.75
TEST_TABLE_NDX_1 INDEX 2 9227 340 21.25
18:56:25 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
2.6507 TEMPORARY 2 6507 639 39.94
TEST_TABLE TABLE 2 11 812 50.75
TEST_TABLE_NDX_1 INDEX 2 9227 340 21.25
18:56:30 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
TEST_TABLE TABLE 2 6507 814 50.88
TEST_TABLE_NDX_1 INDEX 2 9227 340 21.25
18:56:39 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
TEST_TABLE TABLE 2 6507 814 50.88
TEST_TABLE_NDX_1 INDEX 2 9227 340 21.25
18:56:44 SQL>
18:56:51 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
TEST_TABLE TABLE 2 6507 814 50.88
TEST_TABLE_NDX_1 INDEX 2 9227 340 21.25
18:56:52 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
TEST_TABLE TABLE 2 6507 814 50.88
TEST_TABLE_NDX_1 INDEX 2 9227 340 21.25
18:56:57 SQL>
18:57:02 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
TEST_TABLE TABLE 2 6507 814 50.88
TEST_TABLE_NDX_1 INDEX 2 9227 340 21.25
18:57:02 SQL>
18:57:07 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
2.11 TEMPORARY 2 11 56 3.50
TEST_TABLE TABLE 2 6507 814 50.88
TEST_TABLE_NDX_1 INDEX 2 9227 340 21.25
18:57:08 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
2.11 TEMPORARY 2 11 125 7.81
TEST_TABLE TABLE 2 6507 814 50.88
TEST_TABLE_NDX_1 INDEX 2 9227 340 21.25
18:57:12 SQL>
18:57:19 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
2.11 TEMPORARY 2 11 290 18.13
TEST_TABLE TABLE 2 6507 814 50.88
TEST_TABLE_NDX_1 INDEX 2 9227 340 21.25
18:57:20 SQL> /
Segment SegType File Hdr Blk Extents MB
------------------ ------------ ----- ------- -------- ----------
TEST_TABLE TABLE 2 6507 814 50.88
TEST_TABLE_NDX_1 INDEX 2 11 340 21.25
18:57:25 SQL>
Observations :
1. Temporary Segment names are named as 'filenumber.headerblocknumber'
2. The CREATE TABLE statement creates a TEMPORARY Segment "2.11"
which, on successful creation, is converted to a TABLE Segment.
(you can see how the filenumber and headerblocknumber are used)
3. When *extending* an already created Table (adding rows to it),
the new extents are added to the same Table segment -- they are
*NOT* a TEMPORARY Segment.
4. A CREATE INDEX also begins with a TEMPORARY Segment. However,
when creating an Index, you would see 2 such segments -- on in the TEMP
Tablespace where the SORT operations occur, and one in the _target_
Tablespace where the Index is supposed to be created. In this case, the
Index Segment starts with Block 6507.
5. An ALTER INDEX REBUILD creates a new TEMPORARY Segment
(Header Block 9227 in my case) and drops the old Index Segment only
after successful completion -- thus you see both the Block 6507 "TEST_TABLE_NDX_1"
and Block 9227 TEMPORARY Segment while the REBUILD is running
after which the Block 6507 is dropped.
6. An ALTER TABLE MOVE also creates it's own TEMPORARY Segment
(you can see that it reuses the Extents beginning with the one atBlock 6507
that were released by the "dropped" Index segment). On successful completion,
the "old" Table Segment (Block 11) is dropped and replaced by the new Segment
(Block 6507).
7. Another ALTER INDEX REBUILD again behaves in the same manner
(reusing the extents, beginning with the one at Block 11).
Why does Oracle use this manner of TEMPORARY Segments for CREATE
and REBUILD/MOVE ?
Consider what happens if the Tablespace doesn't have enough space.
Say the tablespace is a single datafile of 100MB and you begin running a
CREATE TABLE ... AS SELECT .... while will result in a final table size of
200MB (of, maybe, 1MB extents). When the CREATE begins, Oracle
does *not* know how large the target table will be. It begins with 1MB and
grows to 100MB and then fails to allocate another extent. Since this is
a CREATE table, it cannot allow an incomplete table of 100MB to continue
to exist. Oracle simply drops the TEMPORARY Segment it was using to
hold the table rows and returns a failure on the CREATE TABLE -- the
Table does NOT get created.
Exactly the same thing happens with a CREATE INDEX.
How about the REBUILD and MOVE ?
You might have an Index of 100MB Segment Size. However, you may have
deleted, say 90% of the rows from the Table. When you rebuild the Index,
the new Index might be only 10MB. Till such time as the new Index creation
completes, Oracle cannot drop the old index --- even if there is enough free
space for the 10MB segment to co-exist with the 100MB segment. What
would happen if the server were to fail or the database instance were to crash
because of some other bug while the REBUILD was running ? Had Oracle
dropped the original segment, you would end up with *NO* Index. Oracle
cannot allow that so it doesn't drop the original segment till the new one
is successfully built. The "switching" of the Segments is an "atomic" operation.
What if you hadn't deleted any rows from the table and yet were, simply,
rebuilding the Index. The new Index segment might require 100MB space.
However, when Oracle begins the REBUILD, it doesn't know precisely how
large the new segment *will* really be (it can't rely on the NUM_ROWS
statistics as those statistics might be outdated !). So, it has to first create the
new Index segment (and fail it if the Tablespace doesn't have the additional
100MB free space) before it can "drop" the old segment.
The same manner of behaviour has to occur with an ALTER TABLE ... MOVE.
Oracle cannot be sure, in advance, of how large the new Table segment will finally be.
It has to first create the Table segment successfully before it can "drop" the
old segment. Also, if any other server/instance failure occurred midway, this
method ensures that the "old" Table is still available 100% intact.
Note : Indexes get invalidated on a Table MOVE because the MOVE changes
ROWIDs causing the Indexes to point to incorrect rows.
Well, then, what about INSERT operations ? When you INSERT into a Table
that has already been created (even it was created as an empty table !), the
Table Segment already exists with 1 or more extents. The INSERT operation
simply needs to allocate Extents as necessary to "grow" the table. If there is
insuficient space mid-way, the INSERT is Rolled Back by running an UNDO
on it (the UNDO is "delete .. where rowid = ..." for each of the rows).
The extents that got allocated and the blocks that got "formatted" during the
INSERT remain allocated and formatted but are "cleared" of the rows.
Thus, this can mean that if a Table starts with 100 Extents (and 20,000 rows)
and undergoes an 80,000 row Insert that should have grown to 400 Extents
but fails after growing to 250 Extents (because the Tablespace datafiles are full),
then at the end of the Rollback the ROW COUNT reverts to the original count of
20,000 rows but the Table Segment's size *remains* at 250 Extents ! Those
Extents do not get deallocated. REMEMBER THAT the next time you attempt
a large INSERT !
11 comments:
Hemant,
Very nice explanation and very much appreciated. I am, however, still confused about extending an existing table when there is not enough space in the tablespace.
I have seen countless errors in my experience wherein the alert log the error clearly says something to the effect of: 'unable to extend temporary segment is tablespace USERS' or some other normal data tablespace (non-temporary tablespace).
In every case when I verified with the users, they explained they were doing something that added rows to an existing table.
If you can reply to me personally, I would appreciate it.
Thanks. Jim
wolffjw@yahoo.com
Can you regularly query
DBA_OBJECTS.LAST_DDL_TIME
to see if any Tables or
Indexes are undergoing
Moves or Rebuilds ?
Simple INSERTs can use errors on Table and Index
segments but not Temporary segments.
Very nice explaination, but i have a question regarding Temporary objects created while moving and compressing tables. I am compressing tables and moveing to new tablespace on Oracle 11g R1 and i see the temporary tables created and how do i drop these segments? I though they will be cleared after moving the tables..but they still exists. Can you please suggest.
JHR,
Temporary "tables" are not created, it is temporary "segments" that get created during a MOVE or REBUILD.
If you are seeing temporary "tables" they arise from some other operations. Maybe you are using some code or procedure and not the SQL command ALTER TABLE .. MOVE or ALTER INDEX .. REBUILD ? Such a code or procedure is using "temporary tables" ?
Hemant K Chitale
Hemant,
I'm running 2 threads of data loading programs ( it does query/using analytic functions/sorting etc) on Oracle 11g- each one targeting the different month partition but both the partitions are in same tablespace and I encountered the following error:
ORA-01652: unable to extend temp segment by 256 in tablespace TSPC2011
I'm failing to understand: if TSPC2011 is physical tablespace why error message says temp segment.
Shouldn't be it using temp space and not the storage space.
Does regular storage space also have associated temp segments ?
Harsha,
Check if your data loading programs are using direct path inserts (the APPEND Hint or PARALLEL Hint or ALTER SESSION FORCE PARALLEL DML). Such inserts require Oracle to build new index entries in a temporary segment which is merged with the real index segment at the end of the insert. So, you would have a TEMPORARY Segment for a short while.
Have you queried DBA_SEGMENTS for SEGMENT_TYPE='TEMPORARY' while the data load is running ?
Hemant
Thanks Hemant for your reply.
I re-ran my program once againg and ran queries to find out if it's using temp segment and
I got the answer :).
The segment names were numrical(I guess blocks and file).
-Program runs in parallel dml/query environment
-Uses Append hint
-Exchanges partition with temp tables
-and compresses after data load.
I was kind of perplexed to see temp segment refering to physical storage when I got error message
I read your post once more and things are clear, Thanks :)
Harsha,
The two parts of the TEMPORARY segment name indicate the Header File and Header Block (since the segment can be very large spanning multiple files). See the example "2.11" in my listing.
Hemant
Hemant,
Very good information on temporary segments.
I tried rebuilding indexes using parallel degree of 8, after table move.
The index size was 7 GB, but utilized around 20 GB of temp space in index tablespace.
What could be the estimate for the temp space requirement in such scenario?
Girish
Very nice explanation. Is there a way to tie the temporary segments to the sid or sql_id that is creating them? Suppose you have multiple long running ctas or index build or alter operations and you need to monitor the progress of each, is there a way to associate those back to the driving query? That way you could sum the bytes for the temporary segments to know how each was progressing. Thanks!
I just ran a test where I created a table of 64 extents, 6272 blocks and found 1 sample in V$ACTIVE_SESSION_HISTORY where CURRENT_FILE# and CURRENT_BLOCK# pointed to one of the table blocks. (You can get USER_ID, SQL_ID and other information from V$ACTIVE_SESSION_HISTORY --- use this view only if you have the Diagnostic Pack Licence).
This test, of course, is not conclusive -- only 1 block of the 6272 blocks was reported in V$ACTIVE_SESSION_HISTORY. And I queried the view after the CREATE TABLE had completed because I could identify all the blocks (querying DBA_EXTENTS) that were allocated to that new table.
Post a Comment