Oracle 表空间管理和优化

5ol.cc

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;
　　
让普通用户能执行SYS.SHOW_SPACE
　　
SYS@zcs11G>  drop user zcs1 CASCADE;

　　
create user zcs>　　
grant connect,resource,dba to zcs;
　　
grant execute on SYS.SHOW_SPACE TO zcs;
　　
connect zcs/zcs
　　
drop table t1 purge;
　　
create table t1 (id int,name varchar2(19)) segment creation IMMEDIATE tablespace users;
　　
set serverout on;
　　
exec sys.show_space('T1');