\u9996\u5148\u5c06\u8981\u67e5\u8be2\u788e\u7247\u7684\u5bf9\u8c61\u4fe1\u606f\u653e\u5728\u57fa\u7840\u8868\u91cc\uff0c\u7136\u540e\u6267\u884cEXEC_SHOW_SPACE\uff0c\u7ed3\u679c\u4fdd\u5b58\u5728\u7ed3\u679c\u8868\u91cc\uff08\u53d6\u51b3\u4e8esegment\u7684\u7ba1\u7406\u65b9\u5f0f\uff09<\/p>\n
[php]
\n— Create table \u57fa\u7840\u6570\u636e\u8868
\ncreate table SHOW_SPACE_TABLE
\n(
\nowner VARCHAR2(30 BYTE),
\nsegment_name VARCHAR2(81),
\nsegment_type VARCHAR2(18),
\npartition_name VARCHAR2(30 BYTE)
\n);<\/p>\n
— Create table \u5b58\u653e\u7ed3\u679c\u8868
\ncreate table SHOW_SPACE_TABLE_REPORT
\n(
\nsegment_name VARCHAR2(300),
\nsegment_type VARCHAR2(300),
\nunformatted_blocks NUMBER,
\nunformatted_bytes NUMBER,
\nfs1_blocks NUMBER,
\nfs1_bytes NUMBER,
\nfs2_blocks NUMBER,
\nfs2_bytes NUMBER,
\nfs3_blocks NUMBER,
\nfs3_bytes NUMBER,
\nfs4_blocks NUMBER,
\nfs4_bytes NUMBER,
\nfull_blocks NUMBER,
\nfull_bytes NUMBER,
\npartition_name VARCHAR2(300),
\nowner VARCHAR2(300),
\nchk_date DATE,
\nid NUMBER(8)
\n);<\/p>\n
— Create table \u5b58\u653e\u7ed3\u679c\u8868
\ncreate table SHOW_FREE_BLOCKS_REPORT
\n(
\nsegment_owner VARCHAR2(30),
\nsegment_name VARCHAR2(30),
\nsegment_type VARCHAR2(30),
\nfreelist_group_id NUMBER,
\nfree_blks NUMBER,
\npartition_name VARCHAR2(30),
\nchk_date DATE,
\nid NUMBER(8)
\n);<\/p>\n
— Create table \u5b58\u653e\u9519\u8bef\u4fe1\u606f
\ncreate table BI_ERROR_RECORD_LSQ
\n(
\nerror_id NUMBER(16) not null,
\nerror_code NUMBER(10),
\nerror_msg VARCHAR2(2000 BYTE),
\nerror_date DATE,
\nerror_program VARCHAR2(200 BYTE)
\n);<\/p>\n
–\u5e8f\u5217
\ncreate sequence SEQ_SHOW_FREE_BLOCKS_REPORT
\nminvalue 1
\nmaxvalue 999999999999999999999999999
\nstart with 1
\nincrement by 1
\ncache 20;<\/p>\n
create sequence SEQ_SHOW_SPACE_TABLE_REPORT
\nminvalue 1
\nmaxvalue 999999999999999999999999999
\nstart with 1
\nincrement by 1
\ncache 20;<\/p>\n
create sequence SEQ_BI_ERROR_RECORD
\nminvalue 1
\nmaxvalue 9999999999999999
\nstart with 1
\nincrement by 1
\ncache 20;<\/p>\n
–\u5b58\u50a8\u8fc7\u7a0b
\nCREATE OR REPLACE PROCEDURE SAVE_ERROR_INFO(IN_SQLCODE BI_ERROR_RECORD.ERROR_CODE%TYPE,
\nIN_MSG BI_ERROR_RECORD.ERROR_MSG%TYPE,
\nIN_PROGRAM BI_ERROR_RECORD.ERROR_PROGRAM%TYPE) AS
\nBEGIN
\n\/* \u4fdd\u5b58\u9519\u8bef\u4fe1\u606f *\/
\nINSERT INTO BI_ERROR_RECORD_LSQ
\n(ERROR_ID, ERROR_CODE, ERROR_MSG, ERROR_DATE, ERROR_PROGRAM)
\nVALUES
\n(SEQ_BI_ERROR_RECORD.NEXTVAL, IN_SQLCODE, IN_MSG, SYSDATE, IN_PROGRAM);
\nEND SAVE_ERROR_INFO;<\/p>\n
CREATE OR REPLACE PROCEDURE SHOW_SPACE(V_SEGMENT_OWNER IN VARCHAR2,
\nV_SEGMENT_NAME IN VARCHAR2,
\nV_SEGMENT_TYPE IN VARCHAR2,
\nV_PARTITION_NAME IN VARCHAR2 DEFAULT NULL)
\nAUTHID CURRENT_USER AS
\nP_SEGMENT_NAME VARCHAR2(30);
\nP_SEGMENT_OWNER VARCHAR2(30);
\nP_SEGMENT_TYPE VARCHAR2(30);
\nP_PARTITION_NAME VARCHAR2(30);
\nP_SPACE VARCHAR2(30);<\/p>\n
L_UNFORMATTED_BLOCKS NUMBER;
\nL_UNFORMATTED_BYTES NUMBER;
\nL_FS1_BLOCKS NUMBER;
\nL_FS1_BYTES NUMBER;
\nL_FS2_BLOCKS NUMBER;
\nL_FS2_BYTES NUMBER;
\nL_FS3_BLOCKS NUMBER;
\nL_FS3_BYTES NUMBER;
\nL_FS4_BLOCKS NUMBER;
\nL_FS4_BYTES NUMBER;
\nL_FULL_BLOCKS NUMBER;
\nL_FULL_BYTES NUMBER;<\/p>\n
L_FREE_BLKS NUMBER;
\nL_TOTAL_BYTES NUMBER;
\nL_UNUSED_BLOCKS NUMBER;
\nL_UNUSED_BYTES NUMBER;
\nL_LASTUSEDEXTFILEID NUMBER;
\nL_LASTUSEDEXTBLOCKID NUMBER;
\nL_LAST_USED_BLOCK NUMBER;
\nV_PROGRAM VARCHAR2(30) := ‘SHOW_SPACE_TABLE’;<\/p>\n
BEGIN
\n\/* \u8c03\u7528\u7cfb\u7edfDBMS *\/
\nP_SEGMENT_NAME := UPPER(V_SEGMENT_NAME);
\nP_SEGMENT_OWNER := UPPER(V_SEGMENT_OWNER);
\nP_SEGMENT_TYPE := UPPER(V_SEGMENT_TYPE);
\nP_PARTITION_NAME := UPPER(V_PARTITION_NAME);<\/p>\n
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’
\nINTO P_SPACE
\nUSING P_SEGMENT_NAME, P_PARTITION_NAME, P_PARTITION_NAME, P_SEGMENT_OWNER;
\nIF P_SPACE = ‘AUTO’ THEN
\nDBMS_SPACE.SPACE_USAGE(SEGMENT_OWNER => P_SEGMENT_OWNER,
\nSEGMENT_NAME => P_SEGMENT_NAME,
\nSEGMENT_TYPE => P_SEGMENT_TYPE,
\nUNFORMATTED_BLOCKS => L_UNFORMATTED_BLOCKS,
\nUNFORMATTED_BYTES => L_UNFORMATTED_BYTES,
\nFS1_BLOCKS => L_FS1_BLOCKS,
\nFS1_BYTES => L_FS1_BYTES,
\nFS2_BLOCKS => L_FS2_BLOCKS,
\nFS2_BYTES => L_FS2_BYTES,
\nFS3_BLOCKS => L_FS3_BLOCKS,
\nFS3_BYTES => L_FS3_BYTES,
\nFS4_BLOCKS => L_FS4_BLOCKS,
\nFS4_BYTES => L_FS4_BYTES,
\nFULL_BLOCKS => L_FULL_BLOCKS,
\nFULL_BYTES => L_FULL_BYTES,
\nPARTITION_NAME => P_PARTITION_NAME);
\nINSERT INTO SHOW_SPACE_TABLE_REPORT
\n(ID,
\nOWNER,
\nSEGMENT_NAME,
\nSEGMENT_TYPE,
\nUNFORMATTED_BLOCKS,
\nUNFORMATTED_BYTES,
\nFS1_BLOCKS,
\nFS1_BYTES,
\nFS2_BLOCKS,
\nFS2_BYTES,
\nFS3_BLOCKS,
\nFS3_BYTES,
\nFS4_BLOCKS,
\nFS4_BYTES,
\nFULL_BLOCKS,
\nFULL_BYTES,
\nPARTITION_NAME,
\nCHK_DATE)
\nVALUES
\n(SEQ_SHOW_SPACE_TABLE_REPORT.NEXTVAL,
\nP_SEGMENT_OWNER,
\nP_SEGMENT_NAME,
\nP_SEGMENT_TYPE,
\nL_UNFORMATTED_BLOCKS,
\nL_UNFORMATTED_BYTES,
\nL_FS1_BLOCKS,
\nL_FS1_BYTES,
\nL_FS2_BLOCKS,
\nL_FS2_BYTES,
\nL_FS3_BLOCKS,
\nL_FS3_BYTES,
\nL_FS4_BLOCKS,
\nL_FS4_BYTES,
\nL_FULL_BLOCKS,
\nL_FULL_BYTES,
\nP_PARTITION_NAME,
\nSYSDATE);
\nCOMMIT;<\/p>\n
ELSE<\/p>\n
DBMS_SPACE.FREE_BLOCKS(SEGMENT_OWNER => P_SEGMENT_OWNER,
\nSEGMENT_NAME => P_SEGMENT_NAME,
\nSEGMENT_TYPE => P_SEGMENT_TYPE,
\nFREELIST_GROUP_ID => 0,
\nPARTITION_NAME => P_PARTITION_NAME,
\nFREE_BLKS => L_FREE_BLKS);
\n\/*P(‘Free Blocks’, L_FREE_BLKS);*\/
\nINSERT INTO SHOW_FREE_BLOCKS_REPORT
\n(ID,
\nSEGMENT_OWNER,
\nSEGMENT_NAME,
\nSEGMENT_TYPE,
\nFREELIST_GROUP_ID,
\nFREE_BLKS,
\nPARTITION_NAME,
\nCHK_DATE)
\nVALUES
\n(SEQ_SHOW_FREE_BLOCKS_REPORT.NEXTVAL,
\nP_SEGMENT_OWNER,
\nP_SEGMENT_NAME,
\nP_SEGMENT_TYPE,
\n‘0’,
\nL_FREE_BLKS,
\nP_PARTITION_NAME,
\nSYSDATE);
\nCOMMIT;
\nEND IF;<\/p>\n
EXCEPTION
\nWHEN OTHERS THEN
\nROLLBACK;
\nSAVE_ERROR_INFO(SQLCODE,
\n‘\u788e\u7247\u72b6\u6001:’ || P_SEGMENT_NAME || P_PARTITION_NAME ||
\n‘,\u9519\u8bef\u4fe1\u606f:’ || SQLERRM,
\nV_PROGRAM);
\nCOMMIT;<\/p>\n
END;<\/p>\n
CREATE OR REPLACE PROCEDURE EXEC_SHOW_SPACE
\n AUTHID CURRENT_USER AS<\/p>\n
BEGIN
\n\/* \u5faa\u73af\u8c03\u7528\u5b58\u50a8\u8fc7\u7a0b *\/
\n FOR C1 IN (SELECT EE.OWNER,
\n EE.SEGMENT_NAME,
\n EE.SEGMENT_TYPE,
\n EE.PARTITION_NAME
\n FROM SHOW_SPACE_TABLE EE) LOOP
\n SHOW_SPACE(C1.OWNER,
\n C1.SEGMENT_NAME,
\n C1.SEGMENT_TYPE,
\n C1.PARTITION_NAME);
\n END LOOP;<\/p>\n
END;
\n[\/php]<\/p>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
\u9996\u5148\u5c06\u8981\u67e5\u8be2\u788e\u7247\u7684\u5bf9\u8c61\u4fe1\u606f\u653e\u5728\u57fa\u7840\u8868\u91cc\uff0c\u7136\u540e\u6267\u884cEXEC_S …<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[4],"tags":[],"class_list":["post-35","post","type-post","status-publish","format-standard","hentry","category-4"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p7zRbQ-z","_links":{"self":[{"href":"https:\/\/www.lisq.net\/index.php?rest_route=\/wp\/v2\/posts\/35","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.lisq.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.lisq.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.lisq.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.lisq.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=35"}],"version-history":[{"count":4,"href":"https:\/\/www.lisq.net\/index.php?rest_route=\/wp\/v2\/posts\/35\/revisions"}],"predecessor-version":[{"id":40,"href":"https:\/\/www.lisq.net\/index.php?rest_route=\/wp\/v2\/posts\/35\/revisions\/40"}],"wp:attachment":[{"href":"https:\/\/www.lisq.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=35"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.lisq.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=35"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.lisq.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=35"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}