首先将要查询碎片的对象信息放在基础表里，然后执行EXEC_SHOW_SPACE，结果保存在结果表里（取决于segment的管理方式）
[php]
— Create table 基础数据表
create table SHOW_SPACE_TABLE
(
owner VARCHAR2(30 BYTE),
segment_name VARCHAR2(81),
segment_type VARCHAR2(18),
partition_name VARCHAR2(30 BYTE)
);
— Create table 存放结果表
create table SHOW_SPACE_TABLE_REPORT
(
segment_name VARCHAR2(300),
segment_type VARCHAR2(300),
unformatted_blocks NUMBER,
unformatted_bytes NUMBER,
fs1_blocks NUMBER,
fs1_bytes NUMBER,
fs2_blocks NUMBER,
fs2_bytes NUMBER,
fs3_blocks NUMBER,
fs3_bytes NUMBER,
fs4_blocks NUMBER,
fs4_bytes NUMBER,
full_blocks NUMBER,
full_bytes NUMBER,
partition_name VARCHAR2(300),
owner VARCHAR2(300),
chk_date DATE,
id NUMBER(8)
);
— Create table 存放结果表
create table SHOW_FREE_BLOCKS_REPORT
(
segment_owner VARCHAR2(30),
segment_name VARCHAR2(30),
segment_type VARCHAR2(30),
freelist_group_id NUMBER,
free_blks NUMBER,
partition_name VARCHAR2(30),
chk_date DATE,
id NUMBER(8)
);
— Create table 存放错误信息
create table BI_ERROR_RECORD_LSQ
(
error_id NUMBER(16) not null,
error_code NUMBER(10),
error_msg VARCHAR2(2000 BYTE),
error_date DATE,
error_program VARCHAR2(200 BYTE)
);
–序列
create sequence SEQ_SHOW_FREE_BLOCKS_REPORT
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;
create sequence SEQ_SHOW_SPACE_TABLE_REPORT
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;
create sequence SEQ_BI_ERROR_RECORD
minvalue 1
maxvalue 9999999999999999
start with 1
increment by 1
cache 20;
–存储过程
CREATE OR REPLACE PROCEDURE SAVE_ERROR_INFO(IN_SQLCODE BI_ERROR_RECORD.ERROR_CODE%TYPE,
IN_MSG BI_ERROR_RECORD.ERROR_MSG%TYPE,
IN_PROGRAM BI_ERROR_RECORD.ERROR_PROGRAM%TYPE) AS
BEGIN
/* 保存错误信息 */
INSERT INTO BI_ERROR_RECORD_LSQ
(ERROR_ID, ERROR_CODE, ERROR_MSG, ERROR_DATE, ERROR_PROGRAM)
VALUES
(SEQ_BI_ERROR_RECORD.NEXTVAL, IN_SQLCODE, IN_MSG, SYSDATE, IN_PROGRAM);
END SAVE_ERROR_INFO;
CREATE OR REPLACE PROCEDURE SHOW_SPACE(V_SEGMENT_OWNER IN VARCHAR2,
V_SEGMENT_NAME IN VARCHAR2,
V_SEGMENT_TYPE IN VARCHAR2,
V_PARTITION_NAME IN VARCHAR2 DEFAULT NULL)
AUTHID CURRENT_USER AS
P_SEGMENT_NAME VARCHAR2(30);
P_SEGMENT_OWNER VARCHAR2(30);
P_SEGMENT_TYPE VARCHAR2(30);
P_PARTITION_NAME VARCHAR2(30);
P_SPACE VARCHAR2(30);
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;
L_FREE_BLKS NUMBER;
L_TOTAL_BYTES NUMBER;
L_UNUSED_BLOCKS NUMBER;
L_UNUSED_BYTES NUMBER;
L_LASTUSEDEXTFILEID NUMBER;
L_LASTUSEDEXTBLOCKID NUMBER;
L_LAST_USED_BLOCK NUMBER;
V_PROGRAM VARCHAR2(30) := ‘SHOW_SPACE_TABLE’;
BEGIN
/* 调用系统DBMS */
P_SEGMENT_NAME := UPPER(V_SEGMENT_NAME);
P_SEGMENT_OWNER := UPPER(V_SEGMENT_OWNER);
P_SEGMENT_TYPE := UPPER(V_SEGMENT_TYPE);
P_PARTITION_NAME := UPPER(V_PARTITION_NAME);
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 P_SPACE
USING P_SEGMENT_NAME, P_PARTITION_NAME, P_PARTITION_NAME, P_SEGMENT_OWNER;
IF P_SPACE = ‘AUTO’ THEN
DBMS_SPACE.SPACE_USAGE(SEGMENT_OWNER => P_SEGMENT_OWNER,
SEGMENT_NAME => P_SEGMENT_NAME,
SEGMENT_TYPE => P_SEGMENT_TYPE,
UNFORMATTED_BLOCKS => L_UNFORMATTED_BLOCKS,
UNFORMATTED_BYTES => L_UNFORMATTED_BYTES,
FS1_BLOCKS => L_FS1_BLOCKS,
FS1_BYTES => L_FS1_BYTES,
FS2_BLOCKS => L_FS2_BLOCKS,
FS2_BYTES => L_FS2_BYTES,
FS3_BLOCKS => L_FS3_BLOCKS,
FS3_BYTES => L_FS3_BYTES,
FS4_BLOCKS => L_FS4_BLOCKS,
FS4_BYTES => L_FS4_BYTES,
FULL_BLOCKS => L_FULL_BLOCKS,
FULL_BYTES => L_FULL_BYTES,
PARTITION_NAME => P_PARTITION_NAME);
INSERT INTO SHOW_SPACE_TABLE_REPORT
(ID,
OWNER,
SEGMENT_NAME,
SEGMENT_TYPE,
UNFORMATTED_BLOCKS,
UNFORMATTED_BYTES,
FS1_BLOCKS,
FS1_BYTES,
FS2_BLOCKS,
FS2_BYTES,
FS3_BLOCKS,
FS3_BYTES,
FS4_BLOCKS,
FS4_BYTES,
FULL_BLOCKS,
FULL_BYTES,
PARTITION_NAME,
CHK_DATE)
VALUES
(SEQ_SHOW_SPACE_TABLE_REPORT.NEXTVAL,
P_SEGMENT_OWNER,
P_SEGMENT_NAME,
P_SEGMENT_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_NAME,
SYSDATE);
COMMIT;
ELSE
DBMS_SPACE.FREE_BLOCKS(SEGMENT_OWNER => P_SEGMENT_OWNER,
SEGMENT_NAME => P_SEGMENT_NAME,
SEGMENT_TYPE => P_SEGMENT_TYPE,
FREELIST_GROUP_ID => 0,
PARTITION_NAME => P_PARTITION_NAME,
FREE_BLKS => L_FREE_BLKS);
/*P(‘Free Blocks’, L_FREE_BLKS);*/
INSERT INTO SHOW_FREE_BLOCKS_REPORT
(ID,
SEGMENT_OWNER,
SEGMENT_NAME,
SEGMENT_TYPE,
FREELIST_GROUP_ID,
FREE_BLKS,
PARTITION_NAME,
CHK_DATE)
VALUES
(SEQ_SHOW_FREE_BLOCKS_REPORT.NEXTVAL,
P_SEGMENT_OWNER,
P_SEGMENT_NAME,
P_SEGMENT_TYPE,
‘0’,
L_FREE_BLKS,
P_PARTITION_NAME,
SYSDATE);
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
SAVE_ERROR_INFO(SQLCODE,
‘碎片状态:’ || P_SEGMENT_NAME || P_PARTITION_NAME ||
‘,错误信息:’ || SQLERRM,
V_PROGRAM);
COMMIT;
END;
CREATE OR REPLACE PROCEDURE EXEC_SHOW_SPACE
AUTHID CURRENT_USER AS
BEGIN
/* 循环调用存储过程 */
FOR C1 IN (SELECT EE.OWNER,
EE.SEGMENT_NAME,
EE.SEGMENT_TYPE,
EE.PARTITION_NAME
FROM SHOW_SPACE_TABLE EE) LOOP
SHOW_SPACE(C1.OWNER,
C1.SEGMENT_NAME,
C1.SEGMENT_TYPE,
C1.PARTITION_NAME);
END LOOP;
END;
[/php]