Oracle表实际大小分析脚本

CREATE OR REPLACE FUNCTION REAL_SIZE(P_SEGNAME IN VARCHAR2,
P_OWNER IN VARCHAR2 DEFAULT USER,
P_TYPE IN VARCHAR2 DEFAULT ‘TABLE’)
RETURN NUMBER AUTHID CURRENT_USER AS
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_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;
T_TOTAL_BYTES NUMBER;
T_FS_BYTES NUMBER;
P_PART_NAME VARCHAR2(30);
BEGIN
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_PART_NAME);
DBMS_SPACE.UNUSED_SPACE(P_OWNER,
P_SEGNAME,
P_TYPE,
L_TOTAL_BLOCKS,
L_TOTAL_BYTES,
L_UNUSED_BLOCKS,
L_UNUSED_BYTES,
L_LASTUSEDEXTFILEID,
L_LASTUSEDEXTBLOCKID,
L_LAST_USED_BLOCK,
P_PART_NAME);
T_FS_BYTES := L_FS1_BYTES * 0.25 / 2 + L_FS2_BYTES * (0.5 + 0.25) / 2 +
L_FS3_BYTES * (0.75 + 0.5) / 2 +
L_FS4_BYTES * (1 + 0.75) / 2 + L_UNUSED_BYTES;
T_TOTAL_BYTES := L_TOTAL_BYTES;
RETURN T_TOTAL_BYTES – T_FS_BYTES;
EXCEPTION
WHEN OTHERS THEN
RETURN – 1;
END;

Trackback

no comment untill now

Add your comment now

切换到手机版