I am an Oracle Database Specialist in Singapore.
Please note that this site uses cookies.
07 May, 2008
DBAs working long hours
A forums thread on why and how DBAs should handle the need to work long hours.
03 May, 2008
One Thing Leads to Another ....
I had written my post on Row Sizes and SORTs following the same forums posting that Jonathan Lewis used to build his test case in his blog post called "Manual Optimisation".
On reading the first version of Manual Optimisation (hey, did you notice that he is British ?!), I was then going to update my own post and reference his method. Quite cool and, at the same time, very complicated. However, I held back on that (and wrote another post on something else).
A few days later, I come back to his blog to read up on new comments, and I find a reference to Tom Kyte's response. Yes, I agree with Tom Kyte and do feel that depending on what could be a side-effect of behaviour isn't strictly usable in most scenarios -- but, maybe, Jonathan Lewis has found an exception. (Will I use it ? Probably not , I wouldn't be able to explain it to others !).
So, all things going around, what is THIS posting about ? Well actually, I found the example of the Employee Names and Telephone Numbers lookup that Tom Kyte has quoted as "Optimize to Your Most Frequently Asked Questions" quite intriguing. I've seen a similar application in Lotus Notes and wondered how it could be done in Oracle. Now, I know how it can be done !
So, from a forum posting, to my blog, to Jonathan Lewis's posting to Tom Kyte's response and to an example which gives me an insight into a solution for problem not strictly related to the first one (although there is a good link between the two -- the fact that the rowsize can be an issue in fetching data !).
On reading the first version of Manual Optimisation (hey, did you notice that he is British ?!), I was then going to update my own post and reference his method. Quite cool and, at the same time, very complicated. However, I held back on that (and wrote another post on something else).
A few days later, I come back to his blog to read up on new comments, and I find a reference to Tom Kyte's response. Yes, I agree with Tom Kyte and do feel that depending on what could be a side-effect of behaviour isn't strictly usable in most scenarios -- but, maybe, Jonathan Lewis has found an exception. (Will I use it ? Probably not , I wouldn't be able to explain it to others !).
So, all things going around, what is THIS posting about ? Well actually, I found the example of the Employee Names and Telephone Numbers lookup that Tom Kyte has quoted as "Optimize to Your Most Frequently Asked Questions" quite intriguing. I've seen a similar application in Lotus Notes and wondered how it could be done in Oracle. Now, I know how it can be done !
So, from a forum posting, to my blog, to Jonathan Lewis's posting to Tom Kyte's response and to an example which gives me an insight into a solution for problem not strictly related to the first one (although there is a good link between the two -- the fact that the rowsize can be an issue in fetching data !).
01 May, 2008
TEMPORARY Segments in Data/Index Tablespaces
Some misconceptions about Temporary Segments in a forums thread led me to run this demo to show how and when 'TEMPORARY' segments may be created in a "normal" tablespace -- one other than the TEMP tablespace.
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 :
And this is what I see in another session monitoring the first :
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 !
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 !
Subscribe to:
Posts (Atom)