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_MANAGEMENTFROM DBA_SEGMENTS SEG, DBA_TABLESPACES TSWHERE SEG.SEGMENT_NAME = :P_SEGNAMEAND (:P_PARTITION IS NULL ORSEG.PARTITION_NAME = :P_PARTITION)AND SEG.OWNER = :P_OWNERAND 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;
转载于:https://www.cnblogs.com/iyoume2008/p/4690653.html
