Tablespaces with SEGMENT_SPACE_MANAGEMENT=MANUAL manage their space via free lists, so the call to dbms_space.space_usage will result to an error: "ORA-10614: Operation not allowed on this segment".
Check this post for SEGMENT_SPACE_MANAGEMENT=MANUAL.
Summary
In this method we will use the procedure dbms_space.space_usage.
Its description from Oracle9i Supplied PL/SQL Packages and Types Reference states:
This procedure shows the space usage of data blocks under the segment High Water Mark. The bitmap blocks, segment header, and extent map blocks are not accounted for by this procedure. This procedure can only be used on tablespaces that are created with auto segment space management.It is clear that system blocks used by Oracle to manage segment space allocation will not be returned from this procedure call.
So, do not expect the segment's total blocks returned by dbms_space.unused_space to be equal to the sum of full, free, unused and unformatted blocks.
First, create a table to hold the segments we want to analyze (e.g. Schema's PERFSTAT segments):
CREATE TABLE HELPDESK.SEGS
TABLESPACE HELPDESK
AS
SELECT *
FROM DBA_SEGMENTS
WHERE OWNER = 'PERFSTAT'
AND TABLESPACE_NAME IS NOT NULL
AND SEGMENT_TYPE IN ('TABLE', 'INDEX', 'CLUSTER');We create a table to save the information we need:
CREATE TABLE HELPDESK.FRAG_SEGS
(
OWNER VARCHAR2 (30 BYTE),
SEG_NAME VARCHAR2 (30 BYTE),
SEG_TYPE VARCHAR2 (30 BYTE),
DT DATE,
TOTAL_BLCKS NUMBER,
TOTAL_BYTES NUMBER,
UNUSED_BLCKS NUMBER,
UNUSED_BYTES NUMBER,
FREE_BLCKS NUMBER,
FREE_BYTES NUMBER,
FULL_BLCKS NUMBER,
FULL_BYTES NUMBER,
UNFORM_BLCKS NUMBER,
UNFORM_BYTES NUMBER
)
TABLESPACE HELPDESK;We run the following procedure, which uses dbms_space.unused_space and dbms_space.space_usage procedures to populate helpdesk.frag_segs table.
DECLARE
V_TBLOCKS NUMBER;
V_TBYTES NUMBER;
V_UBLOCKS NUMBER;
V_UBYTES NUMBER;
V_LUE_FILE_ID NUMBER;
V_LUE_BLOCK_ID NUMBER;
V_LU_BLOCK NUMBER;
V_UNFORMATTED_BLOCKS NUMBER;
V_UNFORMATTED_BYTES NUMBER;
V_FS1_BLOCKS NUMBER;
V_FS1_BYTES NUMBER;
V_FS2_BLOCKS NUMBER;
V_FS2_BYTES NUMBER;
V_FS3_BLOCKS NUMBER;
V_FS3_BYTES NUMBER;
V_FS4_BLOCKS NUMBER;
V_FS4_BYTES NUMBER;
V_FULL_BLOCKS NUMBER;
V_FULL_BYTES NUMBER;
CURSOR C1
IS
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE FROM HELPDESK.SEGS;
BEGIN
FOR LINE IN C1
LOOP
DBMS_SPACE.UNUSED_SPACE (SEGMENT_OWNER => LINE.OWNER,
SEGMENT_NAME => LINE.SEGMENT_NAME,
SEGMENT_TYPE => LINE.SEGMENT_TYPE,
TOTAL_BLOCKS => V_TBLOCKS,
TOTAL_BYTES => V_TBYTES,
UNUSED_BLOCKS => V_UBLOCKS,
UNUSED_BYTES => V_UBYTES,
LAST_USED_EXTENT_FILE_ID => V_LUE_FILE_ID,
LAST_USED_EXTENT_BLOCK_ID => V_LUE_BLOCK_ID,
LAST_USED_BLOCK => V_LU_BLOCK);
DBMS_SPACE.SPACE_USAGE (SEGMENT_OWNER => LINE.OWNER,
SEGMENT_NAME => LINE.SEGMENT_NAME,
SEGMENT_TYPE => LINE.SEGMENT_TYPE,
UNFORMATTED_BLOCKS => V_UNFORMATTED_BLOCKS,
UNFORMATTED_BYTES => V_UNFORMATTED_BYTES,
FS1_BLOCKS => V_FS1_BLOCKS,
FS1_BYTES => V_FS1_BYTES,
FS2_BLOCKS => V_FS2_BLOCKS,
FS2_BYTES => V_FS2_BYTES,
FS3_BLOCKS => V_FS3_BLOCKS,
FS3_BYTES => V_FS3_BYTES,
FS4_BLOCKS => V_FS4_BLOCKS,
FS4_BYTES => V_FS4_BYTES,
FULL_BLOCKS => V_FULL_BLOCKS,
FULL_BYTES => V_FULL_BYTES);
INSERT INTO HELPDESK.FRAG_SEGS (OWNER,
SEG_NAME,
SEG_TYPE,
DT,
TOTAL_BLCKS,
TOTAL_BYTES,
UNUSED_BLCKS,
UNUSED_BYTES,
FREE_BLCKS,
FREE_BYTES,
FULL_BLCKS,
FULL_BYTES,
UNFORM_BLCKS,
UNFORM_BYTES)
VALUES (LINE.OWNER,
LINE.SEGMENT_NAME,
LINE.SEGMENT_TYPE,
SYSDATE,
V_TBLOCKS,
V_TBYTES,
V_UBLOCKS,
V_UBYTES,
V_FS1_BLOCKS + V_FS2_BLOCKS + V_FS3_BLOCKS + V_FS4_BLOCKS,
V_FS1_BYTES + V_FS2_BYTES + V_FS3_BYTES + V_FS4_BYTES,
V_FULL_BLOCKS,
V_FULL_BYTES,
V_UNFORMATTED_BLOCKS,
V_UNFORMATTED_BYTES);
IF MOD (C1%ROWCOUNT, 100) = 0
THEN
COMMIT;
END IF;
END LOOP;
COMMIT;
END;
/Finally, we run this query to derive some conclusions about which segments need rebuild or shrink:
SELECT SEG_NAME,
SEG_TYPE,
ROUND (TOTAL_BYTES / 1024 / 1024, 2) "TOTAL SPACE MB",
ROUND (FULL_BYTES / 1024 / 1024, 2) "FULL SPACE MB",
ROUND (UNUSED_BYTES / 1024 / 1024, 2) "UNUSED SPACE MB",
ROUND (FREE_BYTES / 1024 / 1024, 2) "FREE SPACE MB",
ROUND (UNFORM_BYTES / 1024 / 1024, 2) "UNFORM. SPACE MB",
ROUND ( ( (FULL_BYTES * 100) / TOTAL_BYTES), 2) "FULL SPACE %",
ROUND ( ( (UNUSED_BYTES * 100) / TOTAL_BYTES), 2) "UNUSED SPACE %",
ROUND ( ( (FREE_BYTES * 100) / TOTAL_BYTES), 2) "FREE SPACE %",
ROUND ( ( (UNFORM_BYTES * 100) / TOTAL_BYTES), 2) "UNFORM. SPACE %"
FROM HELPDESK.FRAG_SEGS
WHERE TOTAL_BLCKS > 2
ORDER BY "FREE SPACE MB" DESC;
No comments:
Post a Comment