DBMS_SPACE package

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(user,'T1','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:

Sql> @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

  from dba_tables

  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;

RELATED DOCUMENTS

-----------------

This clause is documented in the ALTER TABLE, ALTER INDEX, and ALTER CLUSTER commands of the manuals:

Oracle8i Server SQL Release 8.1.5 Reference Guide A67795-1

Oracle9i Server SQL Release 9.0.1 Reference Guide Volume 2 A90127-01

Oracle Database SQL Reference 10g Release 2 (10.2) B14200-02

Oracle Database SQL Language Reference 11g Release 1 (11.1) B28286-03

See also:

Note:1025869.6 How to Increase or Decrease Datafile Size

Note: 242090.1 SEGMENT SHRINK and details

原文地址：https://blog.csdn.net/jnrjian/article/details/138013062

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！