Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: locally managed tablespaces
This maybe why: It seems that when you truncate the table, the space is not reclaimed.
Oracle NOTE: 77635.1
PURPOSE
This article describes how to find out how many blocks are really being
used within a table ie. are not empty.
SCOPE & APPLICATION
For DBA's needing to determine how many blocks within a table are
empty blocks.
How many blocks contain data (are not empty)
block_number.row.file
If the table is stored in a tablespace which has one datafile, all we have to do is to get DISTINCT number of block_number from ROWID column of this table.
But if the table is stored in a tablespace with more than one datafile then you can have the same block_number but in different datafiles so we have to get DISTINCT number of block_number+file from ROWID.
The SELECT statements which give us the number of "really used" blocks is below. They are different for ORACLE 7 and ORACLE 8 because of different structure of ROWID column in these versions.
For ORACLE 7:
SELECT COUNT(DISTINCT SUBSTR(rowid,15,4)||
SUBSTR(rowid,1,8)) "Used" FROM schema.table;
For ORACLE 8+:
SELECT COUNT (DISTINCT
DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)|| DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) "Used"FROM schema.table;
SELECT COUNT (DISTINCT SUBSTR(rowid,1,15)) "Used" FROM schema.table;
You could ask why the above information could not be determined by using the ANALYZE TABLE command. The ANALYZE TABLE command only identifies the number of 'ever' used blocks or the high water mark for the table.
What is the High Water Mark?
Data files do not have a high water mark; only segments do have them.
How to determine the high water mark
ANALYZE TABLE <tablename> ESTIMATE/COMPUTE STATISTICS;
This will update the table statistics. After generating the statistics, to determine the high water mark:
SELECT blocks, empty_blocks, num_rows
FROM user_tables
WHERE table_name = <tablename>;
BLOCKS represents the number of blocks 'ever' used by the segment.
EMPTY_BLOCKS represents only the number of blocks above the 'HIGH WATER MARK'
.
Deleting records doesn't lower the high water mark. Therefore, deleting
records doesn't raise the EMPTY_BLOCKS figure.
Let us take the following example based on table BIG_EMP1 which has 28672 rows (Oracle 8.0.6):
SQL> connect system/manager
Connected.
SQL> SELECT segment_name,segment_type,blocks 2> FROM dba_segments 3> WHERE segment_name='BIG_EMP1'; SEGMENT_NAME SEGMENT_TYPE BLOCKS EXTENTS1 row selected.
----------------------------- ----------------- ---------- -------
BIG_EMP1 TABLE 1024 2
SQL> connect scott/tiger
SQL> ANALYZE TABLE big_emp1 ESTIMATE STATISTICS; Statement processed.
SQL> SELECT table_name,num_rows,blocks,empty_blocks 2> FROM user_tables 3> WHERE table_name='BIG_EMP1'; TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS1 row selected.
------------------------------ ---------- ---------- ------------
BIG_EMP1 28672 700 323
Note: BLOCKS + EMPTY_BLOCKS (700+323=1023) is one block less than DBA_SEGMENTS.BLOCKS. This is because one block is reserved for the segment header. DBA_SEGMENTS.BLOCKS holds the total number of blocks allocated to the table. USER_TABLES.BLOCKS holds the total number of blocks allocated for data.
SQL> SELECT COUNT (DISTINCT 2> DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)|| 3> DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) "Used" 4> FROM big_emp1; Used
----------
700
SQL> DELETE from big_emp1;
28672 rows processed.
SQL> commit;
Statement processed.
SQL> ANALYZE TABLE big_emp1 ESTIMATE STATISTICS; Statement processed.
SQL> SELECT table_name,num_rows,blocks,empty_blocks 2> FROM user_tables 3> WHERE table_name='BIG_EMP1'; TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS1 row selected.
------------------------------ ---------- ---------- ------------
BIG_EMP1 0 700 323
SQL> SELECT COUNT (DISTINCT 2> DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)|| 3> DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) "Used" 4> FROM big_emp1; Used
----------
0
SQL> TRUNCATE TABLE big_emp1;
Statement processed.
SQL> ANALYZE TABLE big_emp1 ESIMATE STATISTICS; Statement processed.
SQL> SELECT table_name,num_rows,blocks,empty_blocks 2> FROM user_tables 3> WHERE table_name='BIG_EMP1'; TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS1 row selected.
------------------------------ ---------- ---------- ------------
BIG_EMP1 0 0 511
SQL> connect system/manager
Connected.
SQL> SELECT segment_name,segment_type,blocks 2> FROM dba_segments 3> WHERE segment_name='BIG_EMP1'; SEGMENT_NAME SEGMENT_TYPE BLOCKS EXTENTS1 row selected.
----------------------------- ----------------- ---------- -------
BIG_EMP1 TABLE 512 1
Note: TRUNCATE has also deallocated the space from the deleted rows. To retain the space from the deleted rows allocated to the table use:
TRUNCATE TABLE big_emp1 REUSE STORAGE . Received on Tue Jun 26 2001 - 22:13:14 CDT