PURPOSE
-------
This bulletin explains how to interpret the DBA_TABLES.EMPTY_BLOCKS value and
DBMS_SPACE.UNUSED_SPACE information regarding FREELIST and BMB (Bit Mapped Block) segments in 9i.
SCOPE & APPLICATION
-------------------
The following examples should help the DBAs not to misunderstand the values
returned by different sources of information regarding the space used within
blocks.
*** *****************
*** Freelist segments
*** *****************
NOTE: LOB PARTITION size information is not supported in the DBMS_SPACE
package until 10g.
Also, note that passing a LOB partition to dbms_space() without
specifying a partition name yields an internal error (ORA-600).
To get the size of LOB PARTITIONS use dba_segments when the database version is
9i, instead of using DBMS_SPACE package. If the database version is 10g or higher then DBMS_SPACE can be used.
When DBMS_SPACE.UNUSED_SPACE relates 3 blocks unused, DBA_TABLES.EMPTY_BLOCKS
displays the same value 3.
This means that there are 3 blocks unused that could be deallocated if all
restrictions were met. (Refer Note:115586.1)
Example
-------
SQL> create table FREELIST_T (C varchar2(10))
2 tablespace FREELIST_TBS;
Table created.
SQL> begin
2 for b in 1..19 loop
3 for i in 1..324 loop
4 insert into FREELIST_T values (i);
5 end loop;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> analyze table FREELIST_T compute statistics;
Table analyzed.
SQL> select blocks, empty_blocks from dba_tables
2 where table_name='FREELIST_T';
BLOCKS EMPTY_BLOCKS
---------- ------------
10 2
SQL> set serveroutput on
SQL>
SQL> declare
2 TOTAL_BLOCKS number;
3 TOTAL_BYTES number;
4 UNUSED_BLOCKS number;
5 UNUSED_BYTES number;
6 LAST_USED_EXTENT_FILE_ID number;
7 LAST_USED_EXTENT_BLOCK_ID number;
8 LAST_USED_BLOCK number;
9
10 begin dbms_space.unused_space('SYSTEM', 'FREELIST_T', 'TABLE',
11 TOTAL_BLOCKS, TOTAL_BYTES, UNUSED_BLOCKS, UNUSED_BYTES,
12 LAST_USED_EXTENT_FILE_ID, LAST_USED_EXTENT_BLOCK_ID,
13 LAST_USED_BLOCK);
14
15 dbms_output.put_line('OBJECT_NAME = FREELIST_T');
16 dbms_output.put_line('-----------------------------------');
17 dbms_output.put_line('TOTAL_BLOCKS = '||TOTAL_BLOCKS);
18 dbms_output.put_line('UNUSED_BLOCKS = '||UNUSED_BLOCKS);
19 end;
20 /
OBJECT_NAME = FREELIST_T
-----------------------------------
TOTAL_BLOCKS = 13
UNUSED_BLOCKS = 2
PL/SQL procedure successfully completed.
Conclusion
----------
DBA_TABLES DBMS_SPACE.UNUSED_SPACE
------------------------ ------------------------------
BLOCKS | EMPTY_BLOCKS TOTAL_BLOCKS | UNUSED_BLOCKS
------------------------ ------------------------------
10 2 13 2
10 blocks + 2 empty blocks + 1 header segment block = 13 Total blocks
The information between both sources is consistent.
*** **************
*** BMB segments
*** **************
When DBMS_SPACE.UNUSED_SPACE relates 0 block unused, DBA_TABLES.EMPTY_BLOCKS
displays another value:
Example
-------
SQL> create table TEST (C varchar2(10))
2 tablespace BMB_TBS ;
Table created.
SQL> begin
2 for b in 1..19 loop
3 for i in 1..324 loop
4 insert into TEST values (i);
5 end loop;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> analyze table TEST compute statistics;
Table analyzed.
SQL> select blocks, empty_blocks from dba_tables
2 where table_name='TEST';
BLOCKS EMPTY_BLOCKS
---------- ------------
10 3
SQL> set serveroutput on
SQL>
SQL> declare
2 TOTAL_BLOCKS number;
3 TOTAL_BYTES number;
4 UNUSED_BLOCKS number;
5 UNUSED_BYTES number;
6 LAST_USED_EXTENT_FILE_ID number;
7 LAST_USED_EXTENT_BLOCK_ID number;
8 LAST_USED_BLOCK number;
9
10 begin dbms_space.unused_space('SYSTEM', 'TEST', 'TABLE',
11 TOTAL_BLOCKS, TOTAL_BYTES, UNUSED_BLOCKS, UNUSED_BYTES,
12 LAST_USED_EXTENT_FILE_ID, LAST_USED_EXTENT_BLOCK_ID,
13 LAST_USED_BLOCK);
14
15 dbms_output.put_line('OBJECT_NAME = TEST');
16 dbms_output.put_line('-----------------------------------');
17 dbms_output.put_line('TOTAL_BLOCKS = '||TOTAL_BLOCKS);
18 dbms_output.put_line('UNUSED_BLOCKS = '||UNUSED_BLOCKS);
19 end;
20 /
OBJECT_NAME = TEST
-----------------------------------
TOTAL_BLOCKS = 13
UNUSED_BLOCKS = 0
PL/SQL procedure successfully completed.
Conclusion 1
------------
DBA_TABLES DBMS_SPACE.UNUSED_SPACE
------------------------ ------------------------------
BLOCKS | EMPTY_BLOCKS TOTAL_BLOCKS | UNUSED_BLOCKS
------------------------ ------------------------------
10 3 13 0
One source of information says there are 3 blocks empty and the other 0.
New procedure DBMS_SPACE.SPACE_USAGE for BMB segments
-----------------------------------------------------
This new procedure provides the space usage ratio within each block. This
procedure replaces the DBMS_SPACE.FREE_BLOCKS procedure for BMB segments.
(refer Note:116565.1)
SQL> declare
2 v_unformatted_blocks number;
3 v_unformatted_bytes number;
4 v_fs1_blocks number;
5 v_fs1_bytes number;
6 v_fs2_blocks number;
7 v_fs2_bytes number;
8 v_fs3_blocks number;
9 v_fs3_bytes number;
10 v_fs4_blocks number;
11 v_fs4_bytes number;
12 v_full_blocks number;
13 v_full_bytes number;
14 begin
15 dbms_space.space_usage ('SYSTEM', 'TEST', 'TABLE', v_unformatted_blocks,
16 v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
17 v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
18 dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
19 dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);
20 dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);
21 dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);
22 dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);
23 dbms_output.put_line('Full Blocks = '||v_full_blocks);
24 end;
25 /
Unformatted Blocks = 0
FS1 Blocks = 0
FS2 Blocks = 0
FS3 Blocks = 0
FS4 Blocks = 1
Full Blocks = 9
PL/SQL procedure successfully completed.
where
FS1 means 0-25% free space within a block
FS2 means 25-50% free space within a block
FS3 means 50-75% free space within a block
FS4 means 75-100% free space within a block
Conclusion 2
------------
DBA_TABLES DBMS_SPACE.UNUSED_SPACE DBMS_SPACE.SPACE_USAGE
--------------------- ---------------------------- ------------------------
BLOCKS | EMPTY_BLOCKS TOTAL_BLOCKS | UNUSED_BLOCKS FS4 Blocks | Full Blocks
--------------------- ---------------------------- ------------------------
10 3 13 0 1 9
1 FS4 (75-100% free) + 9 Full blocks = 10 blocks used
What are the 3 extra blocks related to EMPTY_BLOCKS though mentioned as
UNUSED by DBMS_SPACE.UNUSED_SPACE procedure ?
Dump extra blocks
-----------------
If you dump the blocks of the segment, the 3 blocks are the Bitmap Blocks used
to track space used in the segment.
Bitmap blocks of 3 different levels:
1. Extent Control Header contains the block addresses of other Bitmap blocks
Last Level 1 BMB: 0x00800009 => means one L1 bitmap block
Last Level II BMB: 0x0080000a => means one L2 bitmap block
Last Level III BMB: 0x00000000 => means no L3 bitmap block
and the extent map of the segment:
--------------------------------------------------
Extent 0 : L1 dba: 0x00800009 Data dba: 0x0080000c => means 1 extent with
-------------------------------------------------- one L1 bitmap block
2. First Level Bitmap Block contains the freeness of the blocks for the extent:
0:Metadata 1:Metadata 2:Metadata 3:FULL
4:FULL 5:75-100% free 6:FULL 7:FULL
8:FULL 9:FULL 10:FULL 11:FULL
12:FULL
This shows that the first 3 blocks (0,1,2) are used for metadata, and they
cannot contain any data. This is explicitely written in this Level1 bitmap
block itself:
first useful block: 3
Blocks 3 to 12 are full of data.
3. Second Level Bitmap Block contains the addresses of the Bitmap blocks of
Level 1:
L1 Ranges :
--------------------------------------------------------
0x00800009 Free: 5 Inst: 1
Another example of bitmap blocks of a segment with 3 extents
------------------------------------------------------------
1. Extent Control Header contains the block addresses of other Bitmap blocks
Last Level 1 BMB: 0x00800023
Last Level II BMB: 0x0080000a
Last Level III BMB: 0x00000000
and the extent map of the segment:
--------------------------------------------------------
Extent 0 : L1 dba: 0x00800009 Data dba: 0x0080000c -------------------
Extent 1 : L1 dba: 0x00800016 Data dba: 0x00800017 -------------- |
Extent 2 : L1 dba: 0x00800023 Data dba: 0x00800024 -------- | |
-------------------------------------------------------- | | |
| | |
1st Extent | | |
| | |
0:Metadata 1:Metadata 2:Metadata 3:FULL | | |
4:FULL 5:FULL 6:FULL 7:FULL | | <---
8:FULL 9:FULL 10:FULL 11:FULL | |
12:FULL | |
| |
2nd Extent | |
| |
0:Metadata 1:75-100% free 2:75-100% free 3:75-100% free | |
4:75-100% free 5:75-100% free 6:75-100% free 7:75-100% free| <---
8:75-100% free 9:75-100% free 10:75-100% free 11:75-100% free|
12:75-100% free |
|
3rd Extent |
|
0:Metadata 1:unformatted 2:unformatted 3:unformatted |
4:unformatted 5:unformatted 6:unformatted 7:unformatted <--
8:unformatted 9:unformatted 10:unformatted 11:unformatted
12:unformatted
* Second Level Bitmap Block contains the addresses of the Bitmap blocks of
Level 1:
L1 Ranges :
--------------------------------------------------------
0x00800009 Free: 1 Inst: 1
0x00800016 Free: 5 Inst: 1
0x00800023 Free: 5 Inst: 1
CONCLUSION
----------
DBA_TABLES.EMPTY_BLOCKS column displays the right number of unused blocks for
Freelist segments, but not for BMB segments.
For BMB segments, use the DBMS_SPACE.UNUSED_SPACE procedure to get the right
number of unused blocks returned by the parameter UNUSED_BLOCKS.
© 2009, www.oracledatabase12g.com. 版权所有.文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.
相关文章 | Related posts:
- DBMS_SQL Package versus Native Dynamic SQL
- Rollback Segment Utilization:Extent, Wrap and Shrink
- How to Format Corrupted Block Not Part of Any Segment
- EVENT: 10015 "Write Trace for Undo Segment Recovery"
- Know about Oracle High Water Mark
- Script:when transaction will finish rollback
- EVENT:10061 disable SMON from cleaning temp segment
Automatic Space Segment Management in RAC Environments
PURPOSE
——-
Discussion of segment space management in Oracle 9i RAC environments.
SCOPE & APPLICATION
——————-
For DBAs (customers and support technical staff) seeking more
information regarding automatic segment-space management (bitmap managed
segments) in an Real Application Clusters (RAC) environment.
Automatic Segment Space Management
———————————-
Database space management has always been an important part of any database
administrator’s job. Administrators spend a significant amount of their time
planning and monitoring the space utilization in order to ensure uninterrupted
database operations.
New features introduced in Oracle9i simplify the space administration tasks,
enforce best practices, and eliminate much of the space management related
performance tuning. The Automatic Segment Space Management is one such feature
which simplifies the management of free space within an object such as tables
or indexes, improves space utilization, and provides significantly better out
of box performance and scalability.
In versions of the database prior to Oracle9i, data structures called the
FREELISTS keep track of blocks within an object which have enough free space
to allow insertion of a new row. Administrators can define the number of
FREELISTS and FREELIST GROUPS when creating an object. The value of the
parameter PCTUSED is used to place a block in and out of the FREELIST.
The new mechanism makes the space management within an object completely
transparent by using bitmaps to track the space utilization of each data block
allocated to the object. The state of the bitmap indicates how much free space
exists in a given data block (i.e. > 75%, between 50 and 75%, between 25 to 50%
or < 25%) as well as whether it is formatted or not.
The new implementation eliminates the necessity to tune space management related
controls (such as FREELISTS, FREELIST GROUPS and PCTUSED) thereby freeing
database administrators from manually managing the space within a database
object. At the same time, it improves the space utilization since the database
now has a more accurate knowledge of how free a data block is. This enables
better reuse of the available free space especially for objects with rows of
highly varying size.
Additionally, the Automatic Segment Space Management feature improves the
performance of concurrent DML operations significantly since different parts of
the bitmap can be used simultaneously eliminating serialization for free space
lookups.
RAC Related Advantages
The performance and manageability gains provided by the Automatic Segment Space
Management feature are particularly noticeable in a Real Application Cluster
environment. It removes the need to alter the number of FREELISTS and FREELIST
GROUPS when new instances are brought online thereby saving the downtime
associated with such table reorganizations.
An Oracle internal benchmark comparing the performance of automatic and manual
segment space management, conducted on a two node (6 x 336 MHz CPU, 4 GB RAM
per node) Real Application Cluster database by inserting about 3 million rows
in a table showed that Automatic Segment Space Management provided over 35%
performance gain over an optimally tuned segment (8 FREELIST GROUPS, 20
FREELISTS) using the manual mode.
How to Signal Automatic Segment Space Management
The Automatic Segment Space Management feature is available only with locally
managed tablespaces. A new clause SEGMENT SPACE MANAGEMENT in the CREATE
TABLESPACE command allows administrators to choose between automatic and manual
modes. A tablespace created with MANUAL segment space management continues to
use FREELISTS for managing free space within the objects located in it. The
following example illustrates how to create a tablespace with Automatic Segment
Space Management.
CREATE TABLESPACE data DATAFILE ‘/u02/oracle/data/data01.dbf’ SIZE 50M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
All objects created in the above tablespace will use the automatic segment space
management feature to manage their free space. Any specification of PCTUSED,
FREELISTS and FREELIST GROUPS parameters for objects created in this tablespace
will be ignored. A new column called SEGMENT_SPACE_MANAGEMENT has been added to
the DBA_TABLESPACES view to indicate the segment space management mode used by
a tablespace. For information on how to interpret the DBA_TABLES.EMPTY_BLOCKS
value and DBMS_SPACE.UNUSED_SPACE information regarding FREELIST and BMB
segments in 9i, see the following note:
Note 149516.1
BMB versus Freelist Segment: DBMS_SPACE.UNUSED_SPACE and
DBA_TABLES.EMPTY_BLOCKS
The compatible parameter must be set to 9.0.0 or higher in order to use this
feature.
How to Deallocate Unused Space from a Table, Index or Cluster.
NOTE
—-
For versions 10G+, and using ASSM:
Segment shrink is an alternative way to release space.
Refer to Note 242090.1 SEGMENT SHRINK and details.
PURPOSE
——-
This document explains the purpose and use of the DEALLOCATE UNUSED KEEP
clause of the ALTER TABLE, ALTER INDEX, and ALTER CLUSTER commands.
SCOPE & APPLICATION
——————-
The article will be useful for Oracle Dba’s, developers and Support Analysts.
How to Deallocate Unused Space from a Table, Index or Cluster
————————————————————-
Times when the DBA finds that he has overallocated the space required for
a table, index, cluster or the table, index, or cluster has shrunk
significantly, then the space can be freed up if certain restrictions can
be met.
The unused space can be reclaimed using the DEALLOCATE clause in the
ALTER TABLE, ALTER INDEX, and ALTER CLUSTER commands.
The freed space is then available for re-use by other objects in the
tablespace. In addition, the user who releases the space is credited for
the freed space in his available space quota.
The syntax is:
DEALLOCATE UNUSED;
and
DEALLOCATE UNUSED KEEP nn; in bytes
DEALLOCATE UNUSED KEEP nnK; in kilobytes
DEALLOCATE UNUSED KEEP nnM; in megabytes
The KEEP parameter specifies the number of bytes above the high water mark
the object needs to retain, even if there are no rows in that space.
Examples:
ALTER TABLE ExampleTable DEALLOCATE UNUSED;
ALTER INDEX ExampleIndex DEALLOCATE UNUSED KEEP 100K;
ALTER CLUSTER ExampleCluster DEALLOCATE UNUSED KEEP 10M;
Restrictions:
1. You can reclaim only the space above the high water mark in a segment.
2. The high water mark represents the highest buffer that has been formated
to receive data. The space above this buffer in which data has never
been stored is the only space that can be released.
The only way the high water mark can be moved downward is to truncate the
segment or drop and recreate the segment. This of course removes all the
data from the segment, but the high water mark is reset.
The exact amount of space that is deallocated depends on the values of the
INITIAL, MINEXTENTS, and NEXT parameters.
If you do not use the KEEP option then:
If an extent is entirely above the high water mark the whole extent is
deallocated.
If the extent is partly above the high water mark, the part above is
deallocated, and the part below becomes the extent.
If the high water mark is above the size of INITIAL and MINEXTENTS, then all
unused space above the high water mark is deallocated.
If the high water mark is less then the size of INITIAL or MINEXTENTS, then
all unused space above MINEXTENTS is deallocated.
The NEXT parameter is set to the size of the last extent that was deallocated.
If you do use the KEEP option then:
If an extent is above the high water mark, and above the KEEP area then the
entire extent is deallocated.
If the extent is partially above the high water mark and KEEP area, then the
space above is deallocated, and the space below becomes the extent.
If the remaining number of extents if less than MINEXTENTS, then MINEXTENTS
is set to the new number of extents.
If the initial extent in the object becomes smaller then the INITIAL
parameter, then the parameter INITIAL is set to the size of the inital extent.
The NEXT parameter is set to the size of the last extent that was deallocated.
EXAMPLES
——–
In this example assume that you are a user SCOTT, and you have a table EMP,
and you want to deallocate the spare space.
You can first determine the space available to be deallocated by using the
DBMS_SPACE package as follows:
set serveroutput on
declare
TOTAL_BLOCKS number;
TOTAL_BYTES number;
UNUSED_BLOCKS number;
UNUSED_BYTES number;
LAST_USED_EXTENT_FILE_ID number;
LAST_USED_EXTENT_BLOCK_ID number;
LAST_USED_BLOCK number;
begin
dbms_space.unused_space(‘SCOTT’,'EMP’,'TABLE’,
TOTAL_BLOCKS, TOTAL_BYTES, UNUSED_BLOCKS, UNUSED_BYTES,
LAST_USED_EXTENT_FILE_ID, LAST_USED_EXTENT_BLOCK_ID,
LAST_USED_BLOCK);
dbms_output.put_line(‘OBJECT_NAME = EMP’);
dbms_output.put_line(‘———————————–’);
dbms_output.put_line(‘TOTAL_BLOCKS = ‘||TOTAL_BLOCKS);
dbms_output.put_line(‘TOTAL_BYTES = ‘||TOTAL_BYTES);
dbms_output.put_line(‘UNUSED_BLOCKS = ‘||UNUSED_BLOCKS);
dbms_output.put_line(‘UNUSED BYTES = ‘||UNUSED_BYTES);
dbms_output.put_line(‘LAST_USED_EXTENT_FILE_ID = ‘||LAST_USED_EXTENT_FILE_ID);
dbms_output.put_line(‘LAST_USED_EXTENT_BLOCK_ID = ‘||LAST_USED_EXTENT_BLOCK_ID);
dbms_output.put_line(‘LAST_USED_BLOCK = ‘||LAST_USED_BLOCK);
end;
/
The output is:
SVRMGR> @space1
Server Output ON
Statement processed.
OBJECT_NAME = EMP
———————————–
TOTAL_BLOCKS = 5
TOTAL_BYTES = 10240
UNUSED_BLOCKS = 3
UNUSED BYTES = 6144
LAST_USED_EXTENT_FILE_ID = 1
LAST_USED_EXTENT_BLOCK_ID = 17625
LAST_USED_BLOCK = 2
SVRMGR>
Or you can determine the number of empty blocks and unused bytes by using
(in this example db_block_size has been assumed to be 4096):
SQL> analyze table scott.emp compute statistics;
Table analyzed.
SQL> select empty_blocks, empty_blocks*4096 unused_bytes
2 from dba_tables
3 where table_name=’EMP’ and owner=’SCOTT’;
EMPTY_BLOCKS
————
2
The high water mark of the table in bytes is the difference between the
TOTAL_BYTES value and the UNUSED_BYTES value, which in this example is 4096.
So this means that the first 4096 bytes of the table have data stored in them,
and the next 6144 bytes are available to be deallocated.
This can be accomplished by using the command:
ALTER TABLE scott.emp DEALLOCATE UNUSED;
When to use DBMS_SPACE.UNUSED_SPACE or DBMS_SPACE.FREE_BLOCKS Procedures
PURPOSE
——-
DBMS_SPACE.UNUSED_SPACE and DBMS_SPACE.FREE_BLOCKS procedure
names seem to provide redundant information about segments
free space.
The document explains the purpose and the difference
between the two procedures.
SCOPE & APPLICATION
——————-
The information provided by one or the other procedure
leads the DBAs towards different actions regarding reallocation
and storage issues.
*** ——————————-
*** DBMS_SPACE.UNUSED_SPACE meaning
*** ——————————-
The UNUSED_SPACE procedure refers to unused space above the high
water mark in a segment.
The high water mark represents the frontier between the blocks
that store (inserted rows) or have stored rows (deleted rows),
and those that never stored any rows. These blocks are considered
unused as long as they were never useful for the segment and
therefore can be released.
The only way the high water mark can be moved downward
is to truncate the segment or drop/recreate the segment.
*** ——————————-
*** DBMS_SPACE.UNUSED_SPACE purpose
*** ——————————-
If you need to retrieve unused space from the segments that
are allocated superfluous blocks:
1. Use UNUSED_SPACE procedure to retrieve the list of those
segments that can restitute space for other or new segments.
Refer to note: 115586.1 to follow the exact method of execution
of the procedure.
2. Use ALTER TABLE/INDEX/CLUSTER DEALLOCATE UNUSED statement
to deallocate the unused space and get it back.
*** ——————————
*** DBMS_SPACE.FREE_BLOCKS meaning
*** ——————————
The FREE_BLOCKS procedure refers to the blocks in a segment
below the high water mark whose number of rows falls below
the PCTUSED attribute, and therefore are candidate for new
inserted rows.
Monitor free blocks in order to avoid out of space errors
and new extent allocations.
Note: in a OPS environment, a given oracle process gets free
blocks from only one freelist of a group based on its process
id and has no access to the free blocks in other freelists of
the same group; it may get an out of space error or cause a new
extent to be allocated even though there are free blocks in the
group.
*** ——————————
*** DBMS_SPACE.FREE_BLOCKS purpose
*** ——————————
DBMS_SPACE.UNUSED_SPACE relates 0 blocks unused (you can get
the same information from DBA_TABLES.EMPTY_BLOCKS after table
analysis).
You lack space and therefore cannot allocate new extents,
but need to insert new rows into tables.
1. Use DBMS_SPACE.FREE_BLOCKS procedure to retrieve the list
of tables for which the number of free blocks is too low.
2. If AVG_SPACE is high towards AVG_ROW_LEN and PCTUSED low,
recreate the object with a higher PCTUSED so as to optimize
the number of rows inserted in used blocks.
*** ——–
*** Examples
*** ——–
a/ Table T1 created with a low PCTUSED (10) and a MAXEXTENTS
of 1 to alert us of the necessity of extent allocation
SQL> drop table scott.t1;
Table dropped.
SQL> create table scott.t1 (c char(579))
2 storage (initial 8k maxextents 1)
3 pctused 10;
Table created.
b/ 9 rows are inserted to fill up the blocks =>
AVG_SPACE displays 197 bytes free in each block
DBMS_SPACE.FREE_BLOCKS displays 1 free block
SQL> insert into scott.t1 values (‘A’);
SQL> insert into scott.t1 values (‘B’);
SQL> insert into scott.t1 values (‘C’);
SQL> insert into scott.t1 values (‘D’);
SQL> insert into scott.t1 values (‘E’);
SQL> insert into scott.t1 values (‘F’);
SQL> insert into scott.t1 values (‘G’);
SQL> insert into scott.t1 values (‘H’);
SQL> insert into scott.t1 values (‘I’);
SQL> commit;
Commit complete.
SQL> select rowid,substr(c,1,10) from scott.t1;
ROWID SUBSTR(C,1
—————— ———-
AAAH2gAAFAAAbtmAAA A
AAAH2gAAFAAAbtmAAB B
AAAH2gAAFAAAbtmAAC C
AAAH2gAAFAAAbtnAAA D
AAAH2gAAFAAAbtnAAB E
AAAH2gAAFAAAbtnAAC F
AAAH2gAAFAAAbtoAAA G
AAAH2gAAFAAAbtoAAB H
AAAH2gAAFAAAbtoAAC I
9 rows selected.
SQL> analyze table scott.t1 compute statistics;
Table analyzed.
SQL> select avg_row_len, empty_blocks, avg_space
2 from dba_tables where table_name=’T1′ and owner=’SCOTT’;
AVG_ROW_LEN EMPTY_BLOCKS AVG_SPACE
———– ———— ———-
585 0 197
SQL> declare
2 free_blocks number;
3 begin
4 DBMS_SPACE.FREE_BLOCKS(‘SCOTT’, ‘T1′,’TABLE’,0,free_blocks);
5 dbms_output.put_line(‘Nb of free blocks = ‘||free_blocks);
6 end;
7 /
Nb of free blocks = 1
PL/SQL procedure successfully completed.
*** Test1
c/ 3 rows are deleted from the same block and therefore
release the block for new inserts =>
AVG_SPACE displays 782 bytes free in each block
DBMS_SPACE.FREE_BLOCKS displays 2 free blocks
SQL> delete from scott.t1
2 where c =’A’ or c=’B’ or c=’C';
3 rows deleted.
SQL> commit;
Commit complete.
SQL> analyze table scott.t1 compute statistics;
Table analyzed.
SQL> select avg_row_len, empty_blocks, avg_space
2 from dba_tables where table_name=’T1′ and owner=’SCOTT’;
AVG_ROW_LEN EMPTY_BLOCKS AVG_SPACE
———– ———— ———-
585 0 782
SQL> declare
2 free_blocks number;
3 begin
4 DBMS_SPACE.FREE_BLOCKS(‘SCOTT’, ‘T1′,’TABLE’,0,free_blocks);
5 dbms_output.put_line(‘Nb of free blocks = ‘||free_blocks);
6 end;
7 /
Nb of free blocks = 2
PL/SQL procedure successfully completed.
d/ Rows can be inserted
SQL> insert into scott.t1 values (‘J’);
1 row created.
SQL> insert into scott.t1 values (‘K’);
1 row created.
SQL> commit;
Commit complete.
*** Test2
c/ 3 rows are deleted from 3 blocks and therefore
do not release any block for new inserts =>
AVG_SPACE displays 782 bytes free in each block
DBMS_SPACE.FREE_BLOCKS still displays 1 free block
SQL> delete from scott.t1
2 where c =’A’ or c=’D’ or c=’G';
3 rows deleted.
SQL> commit;
Commit complete.
SQL> analyze table scott.t1 compute statistics;
Table analyzed.
SQL> select avg_row_len, empty_blocks, avg_space
2 from dba_tables where table_name=’T1′ and owner=’SCOTT’;
AVG_ROW_LEN EMPTY_BLOCKS AVG_SPACE
———– ———— ———-
585 0 782
SQL> declare
2 free_blocks number;
3 begin
4 DBMS_SPACE.FREE_BLOCKS(‘SCOTT’, ‘T1′,’TABLE’,0,free_blocks);
5 dbms_output.put_line(‘Nb of free blocks = ‘||free_blocks);
6 end;
7 /
Nb of free blocks = 1
PL/SQL procedure successfully completed.
d/ Rows cannot be inserted => new extent allocation required
SQL> insert into scott.t1 values (‘J’);
1 row created.
SQL> insert into scott.t1 values (‘K’);
insert into scott.t1 values (‘K’)
*
ERROR at line 1:
ORA-01631: max # extents (1) reached in table SCOTT.T1
In such cases:
1. Export the table data
2. Recreate the table with a higher PCTUSED
3. Import the table data
*** Test2 is now successful with a higher PCTUSED
SQL> create table scott.t1 (c char(579))
2 storage (initial 8k maxextents 1)
3 pctused 70;
Table created.
SQL> insert into scott.t1 values (‘A’);
SQL> insert into scott.t1 values (‘B’);
SQL> insert into scott.t1 values (‘C’);
SQL> insert into scott.t1 values (‘D’);
SQL> insert into scott.t1 values (‘E’);
SQL> insert into scott.t1 values (‘F’);
SQL> insert into scott.t1 values (‘G’);
SQL> insert into scott.t1 values (‘H’);
SQL> insert into scott.t1 values (‘I’);
SQL> commit;
Commit complete.
SQL> select rowid,substr(c,1,10) from scott.t1;
ROWID SUBSTR(C,1
—————— ———-
AAAH2gAAFAAAbtmAAA A
AAAH2gAAFAAAbtmAAB B
AAAH2gAAFAAAbtmAAC C
AAAH2gAAFAAAbtnAAA D
AAAH2gAAFAAAbtnAAB E
AAAH2gAAFAAAbtnAAC F
AAAH2gAAFAAAbtoAAA G
AAAH2gAAFAAAbtoAAB H
AAAH2gAAFAAAbtoAAC I
9 rows selected.
SQL> analyze table scott.t1 compute statistics;
Table analyzed.
SQL> select avg_row_len, empty_blocks, avg_space
2 from dba_tables where table_name=’T1′ and owner=’SCOTT’;
AVG_ROW_LEN EMPTY_BLOCKS AVG_SPACE
———– ———— ———-
585 0 197
SQL> declare
2 free_blocks number;
3 begin
4 DBMS_SPACE.FREE_BLOCKS(‘SCOTT’, ‘T1′,’TABLE’,0,free_blocks);
5 dbms_output.put_line(‘Nb of free blocks = ‘||free_blocks);
6 end;
7 /
Nb of free blocks = 1
PL/SQL procedure successfully completed.
SQL> delete from scott.t1
2 where c =’A’ or c=’D’ or c=’G';
3 rows deleted.
SQL> commit;
Commit complete.
SQL> analyze table scott.t1 compute statistics;
Table analyzed.
SQL> select avg_row_len, empty_blocks, avg_space
2 from dba_tables where table_name=’T1′ and owner=’SCOTT’;
AVG_ROW_LEN EMPTY_BLOCKS AVG_SPACE
———– ———— ———-
585 0 782
SQL> declare
2 free_blocks number;
3 begin
4 DBMS_SPACE.FREE_BLOCKS(‘SCOTT’, ‘T1′,’TABLE’,0,free_blocks);
5 dbms_output.put_line(‘Nb of free blocks = ‘||free_blocks);
6 end;
7 /
Nb of free blocks = 3
PL/SQL procedure successfully completed.
SQL> insert into scott.t1 values (‘J’);
1 row created.
SQL> insert into scott.t1 values (‘K’);
1 row created.
SQL> commit;
Commit complete.