TOM大师的show_space

最新推荐文章于 2022-01-04 18:36:39 发布

转载最新推荐文章于 2022-01-04 18:36:39 发布 · 556 阅读

oracle 专栏收录该内容

96 篇文章

订阅专栏

本文介绍了一个用于查询Oracle数据库中表或分区表空间使用情况的实用程序。该程序能够显示总块数、已用和未用空间等详细信息，并演示了如何通过插入数据和截断表来观察空间变化。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

create or replace procedure SHOW_SPACE(P_SEGNAME   IN VARCHAR2,
                                       P_OWNER     IN VARCHAR2 DEFAULT USER,
                                       P_TYPE      IN VARCHAR2 DEFAULT 'TABLE',
                                       P_PARTITION IN VARCHAR2 DEFAULT NULL)
-- THIS PROCEDURE USES AUTHID CURRENT USER SO IT CAN QUERY DBA_*
-- VIEWS USING PRIVILEGES FROM A ROLE AND SO IT CAN BE INSTALLED
-- ONCE PER DATABASE, INSTEAD OF ONCE PER USER WHO WANTED TO USE IT.
AUTHID CURRENT_USER AS
L_FREE_BLKS          NUMBER;
L_TOTAL_BLOCKS       NUMBER;
L_TOTAL_BYTES        NUMBER;
L_UNUSED_BLOCKS      NUMBER;
L_UNUSED_BYTES       NUMBER;
L_LASTUSEDEXTFILEID NUMBER;
L_LASTUSEDEXTBLOCKID NUMBER;
L_LAST_USED_BLOCK    NUMBER;
L_SEGMENT_SPACE_MGMT VARCHAR2(255);
L_UNFORMATTED_BLOCKS NUMBER;
L_UNFORMATTED_BYTES NUMBER;
L_FS1_BLOCKS         NUMBER;
L_FS1_BYTES          NUMBER;
L_FS2_BLOCKS         NUMBER;
L_FS2_BYTES          NUMBER;
L_FS3_BLOCKS         NUMBER;
L_FS3_BYTES          NUMBER;
L_FS4_BLOCKS         NUMBER;
L_FS4_BYTES          NUMBER;
L_FULL_BLOCKS        NUMBER;
L_FULL_BYTES         NUMBER;

-- INLINE PROCEDURE TO PRINT OUT NUMBERS NICELY FORMATTED
-- WITH A SIMPLE LABEL.
PROCEDURE P(P_LABEL IN VARCHAR2, P_NUM IN NUMBER) IS
BEGIN
    DBMS_OUTPUT.PUT_LINE(RPAD(P_LABEL, 40, '.') ||
                         TO_CHAR(P_NUM, '999,999,999,999'));
END;
BEGIN
-- THIS QUERY IS EXECUTED DYNAMICALLY IN ORDER TO ALLOW THIS PROCEDURE
-- TO BE CREATED BY A USER WHO HAS ACCESS TO DBA_SEGMENTS/TABLESPACES
-- VIA A ROLE AS IS CUSTOMARY.
-- NOTE: AT RUNTIME, THE INVOKER MUST HAVE ACCESS TO THESE TWO
-- VIEWS!
-- THIS QUERY DETERMINES IF THE OBJECT IS AN ASSM OBJECT OR NOT.
BEGIN
    EXECUTE IMMEDIATE 'SELECT TS.SEGMENT_SPACE_MANAGEMENT
FROM DBA_SEGMENTS SEG, DBA_TABLESPACES TS
WHERE SEG.SEGMENT_NAME = :P_SEGNAME
AND (:P_PARTITION IS NULL OR
SEG.PARTITION_NAME = :P_PARTITION)
AND SEG.OWNER = :P_OWNER
AND SEG.TABLESPACE_NAME = TS.TABLESPACE_NAME'
      INTO L_SEGMENT_SPACE_MGMT
      USING P_SEGNAME, P_PARTITION, P_PARTITION, P_OWNER;
EXCEPTION
    WHEN TOO_MANY_ROWS THEN
      DBMS_OUTPUT.PUT_LINE('THIS MUST BE A PARTITIONED TABLE, USE P_PARTITION => ');
      RETURN;
END;

-- IF THE OBJECT IS IN AN ASSM TABLESPACE, WE MUST USE THIS API
-- CALL TO GET SPACE INFORMATION; ELSE WE USE THE FREE_BLOCKS
-- API FOR THE USER MANAGED SEGMENTS.
IF L_SEGMENT_SPACE_MGMT = 'AUTO' THEN
    DBMS_SPACE.SPACE_USAGE(P_OWNER,
                           P_SEGNAME,
                           P_TYPE,
                           L_UNFORMATTED_BLOCKS,
                           L_UNFORMATTED_BYTES,
                           L_FS1_BLOCKS,
                           L_FS1_BYTES,
                           L_FS2_BLOCKS,
                           L_FS2_BYTES,
                           L_FS3_BLOCKS,
                           L_FS3_BYTES,
                           L_FS4_BLOCKS,
                           L_FS4_BYTES,
                           L_FULL_BLOCKS,
                           L_FULL_BYTES,
                           P_PARTITION);

    P('UNFORMATTED BLOCKS ', L_UNFORMATTED_BLOCKS);
    P('FS1 BLOCKS (0-25) ', L_FS1_BLOCKS);
    P('FS2 BLOCKS (25-50) ', L_FS2_BLOCKS);
    P('FS3 BLOCKS (50-75) ', L_FS3_BLOCKS);
    P('FS4 BLOCKS (75-100)', L_FS4_BLOCKS);
    P('FULL BLOCKS ', L_FULL_BLOCKS);
ELSE
    DBMS_SPACE.FREE_BLOCKS(SEGMENT_OWNER     => P_OWNER,
                           SEGMENT_NAME      => P_SEGNAME,
                           SEGMENT_TYPE      => P_TYPE,
                           FREELIST_GROUP_ID => 0,
                           FREE_BLKS         => L_FREE_BLKS);

    P('FREE BLOCKS', L_FREE_BLKS);
END IF;

-- AND THEN THE UNUSED SPACE API CALL TO GET THE REST OF THE
-- INFORMATION.
DBMS_SPACE.UNUSED_SPACE(SEGMENT_OWNER             => P_OWNER,
                          SEGMENT_NAME              => P_SEGNAME,
                          SEGMENT_TYPE              => P_TYPE,
                          PARTITION_NAME            => P_PARTITION,
                          TOTAL_BLOCKS              => L_TOTAL_BLOCKS,
                          TOTAL_BYTES               => L_TOTAL_BYTES,
                          UNUSED_BLOCKS             => L_UNUSED_BLOCKS,
                          UNUSED_BYTES              => L_UNUSED_BYTES,
                          LAST_USED_EXTENT_FILE_ID => L_LASTUSEDEXTFILEID,
                          LAST_USED_EXTENT_BLOCK_ID => L_LASTUSEDEXTBLOCKID,
                          LAST_USED_BLOCK           => L_LAST_USED_BLOCK);

P('TOTAL BLOCKS', L_TOTAL_BLOCKS);
P('TOTAL BYTES', L_TOTAL_BYTES);
P('TOTAL MBYTES', TRUNC(L_TOTAL_BYTES / 1024 / 1024));
P('UNUSED BLOCKS', L_UNUSED_BLOCKS);
P('UNUSED BYTES', L_UNUSED_BYTES);
P('LAST USED EXT FILEID', L_LASTUSEDEXTFILEID);
P('LAST USED EXT BLOCKID', L_LASTUSEDEXTBLOCKID);
P('LAST USED BLOCK', L_LAST_USED_BLOCK);
END;

===============================================

分区表中的使用:

SQL> set serverout on
SQL> exec show_space('TABLE_NAME,'OWNER','TABLE PARTITION','PARTITION_NAME');

eg:

SQL> create table test0008 as select * from dba_objects;

Table created.

SQL> exec show_space('TEST0008','SYS','TABLE');
FREE BLOCKS.............................               0
TOTAL BLOCKS............................             768
TOTAL BYTES.............................       6,291,456
TOTAL MBYTES............................               6
UNUSED BLOCKS...........................              81
UNUSED BYTES............................         663,552
LAST USED EXT FILEID....................               1
LAST USED EXT BLOCKID...................          65,161
LAST USED BLOCK.........................              47

PL/SQL procedure successfully completed.

SQL> insert into test0008 select * from test0008;

49942 rows created.

SQL> commit;

Commit complete.

SQL> exec show_space('TEST0008','SYS','TABLE');
FREE BLOCKS.............................               5
TOTAL BLOCKS............................           1,408
TOTAL BYTES.............................      11,534,336
TOTAL MBYTES............................              11
UNUSED BLOCKS...........................              33
UNUSED BYTES............................         270,336
LAST USED EXT FILEID....................               1
LAST USED EXT BLOCKID...................          65,801
LAST USED BLOCK.........................              95

PL/SQL procedure successfully completed.

SQL> insert into test0008 select * from test0008;

99884 rows created.

SQL> commit;

Commit complete.

SQL> exec show_space('TEST0008','SYS','TABLE');
FREE BLOCKS.............................               1
TOTAL BLOCKS............................           2,816
TOTAL BYTES.............................      23,068,672
TOTAL MBYTES............................              22
UNUSED BLOCKS...........................              78
UNUSED BYTES............................         638,976
LAST USED EXT FILEID....................               1
LAST USED EXT BLOCKID...................          67,209
LAST USED BLOCK.........................              50

PL/SQL procedure successfully completed.

SQL> truncate table test0008;

Table truncated.

SQL> exec show_space('TEST0008','SYS','TABLE');
FREE BLOCKS.............................               0
TOTAL BLOCKS............................               8
TOTAL BYTES.............................          65,536
TOTAL MBYTES............................               0
UNUSED BLOCKS...........................               7
UNUSED BYTES............................          57,344
LAST USED EXT FILEID....................               1
LAST USED EXT BLOCKID...................          63,825
LAST USED BLOCK.........................               1

PL/SQL procedure successfully completed.

SQL>

绿色为高水位块