符合中小企业对网站设计、功能常规化式的企业展示型网站建设
本套餐主要针对企业品牌型网站、中高端设计、前端互动体验...
商城网站建设因基本功能的需求不同费用上面也有很大的差别...
手机微信网站开发、微信官网、微信商城网站...
new_show_space.sql.zip
瀍河网站建设公司创新互联建站,瀍河网站设计制作,有大型网站制作公司丰富经验。已为瀍河千余家提供企业网站建设服务。企业网站搭建\外贸网站建设要多少钱,请找那个售后服务好的瀍河做网站的公司定做!
REM REM based on previous show_space script, now it can REM identify all partition_name for table or index- REM automatically without specifying partition_name. REM REM Usage: REM exec show_space('TABLE','OWNER','TABLE_NAME'); REM or REM exec show_space('TABLE PARTITION','TEST','P_TAB','PART1'); REM REM exec show_space('INDEX','TEST','IDX_TAB'); REM or REM exec show_space('INDEX PARTITION','TEST','IDX_TAB','IDX_PART1'); REM REM Edited by mx at 2020/03/27 REM -- based on previous procedure show_space from Internet. set serveroutput on CREATE OR REPLACE PROCEDURE show_space ( v_segment_type IN VARCHAR2 DEFAULT 'TABLE', v_segment_owner IN VARCHAR2 DEFAULT USER, v_segment_name IN VARCHAR2, v_partition_name IN VARCHAR2 DEFAULT NULL, v_space IN VARCHAR2 DEFAULT 'AUTO', v_analyzed IN VARCHAR2 DEFAULT 'Y' ) AS p_segment_type VARCHAR2 ( 30 ); p_segment_owner VARCHAR2 ( 30 ); p_segment_name VARCHAR2 ( 50 ); p_partition_name VARCHAR2 ( 30 ); p_partitioned VARCHAR2 ( 5 ); 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_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; PROCEDURE print ( p_label IN VARCHAR2, p_num IN NUMBER ) IS BEGIN dbms_output.put_line ( rpad( p_label, 40, '.' ) || p_num ); END; PROCEDURE analyze_space ( f_segment_type IN VARCHAR2, f_segment_owner IN VARCHAR2, f_segment_name IN VARCHAR2, f_partition_name IN VARCHAR2 ) IS BEGIN dbms_space.unused_space ( segment_owner => f_segment_owner, segment_name => f_segment_name, segment_type => f_segment_type, 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, partition_name => f_partition_name ); IF v_space = 'MANUAL' OR ( v_space <> 'auto' AND v_space <> 'AUTO' ) THEN dbms_space.free_blocks ( segment_owner => f_segment_owner, segment_name => f_segment_name, segment_type => f_segment_type, freelist_group_id => 0, free_blks => l_free_blks, partition_name => f_partition_name ); print ( 'Free Blocks', l_free_blks ); END IF; IF ( f_partition_name IS NULL ) THEN dbms_output.put_line ( '--' || rpad( f_segment_owner || '.' || f_segment_name, 45, '-' ) ); ELSE dbms_output.put_line ( '--' || rpad( f_segment_owner || '.' || f_segment_name || '.' || f_partition_name, 45, '-' ) ); END IF; print ( 'Total Blocks', l_total_blocks ); print ( 'Total Bytes', l_total_bytes ); print ( 'Unused Blocks', l_unused_blocks ); print ( 'Unused Bytes', l_unused_bytes ); print ( 'Last Used Ext FileId', l_LastUsedExtFileId ); print ( 'Last Used Ext BlockId', l_LastUsedExtBlockId ); print ( 'Last Used Block', l_LAST_USED_BLOCK ); /*IF the segment is analyzed */ IF v_analyzed = 'Y' THEN dbms_space.space_usage ( segment_owner => f_segment_owner, segment_name => f_segment_name, segment_type => f_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 => f_partition_name ); dbms_output.put_line ( 'The segment is analyzed.' ); print ( '0% -- 25% free space blocks', l_fs1_blocks ); print ( '0% -- 25% free space bytes', l_fs1_bytes ); print ( '25% -- 50% free space blocks', l_fs2_blocks ); print ( '25% -- 50% free space bytes', l_fs2_bytes ); print ( '50% -- 75% free space blocks', l_fs3_blocks ); print ( '50% -- 75% free space bytes', l_fs3_bytes ); print ( '75% -- 100% free space blocks', l_fs4_blocks ); print ( '75% -- 100% free space bytes', l_fs4_bytes ); print ( 'Unused Blocks', l_unformatted_blocks ); print ( 'Unused Bytes', l_unformatted_bytes ); print ( 'Total Blocks', l_full_blocks ); print ( 'Total bytes', l_full_bytes ); dbms_output.put_line ( rpad( ' ', 48, '-' ) ); END IF; END; BEGIN 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 ); IF ( v_segment_type = 'i' OR v_segment_type = 'I' ) THEN p_segment_type := 'INDEX'; END IF; IF ( v_segment_type = 't' OR v_segment_type = 'T' ) THEN p_segment_type := 'TABLE'; END IF; IF ( v_segment_type = 'c' OR v_segment_type = 'C' ) THEN p_segment_type := 'CLUSTER'; END IF; SELECT partitioned INTO p_partitioned FROM ( SELECT partitioned FROM all_tables WHERE owner = p_segment_owner AND table_name = p_segment_name UNION SELECT partitioned FROM all_indexes WHERE owner = p_segment_owner AND index_name = p_segment_name ); IF ( p_segment_type = 'TABLE' AND p_partitioned = 'YES' ) THEN p_segment_type := 'TABLE PARTITION'; FOR t IN ( SELECT partition_name FROM all_tab_partitions WHERE table_owner = p_segment_owner AND table_name = p_segment_name ORDER BY to_number( regexp_substr( partition_name, '[0-9]*[0-9]', 1 ) ) ) loop analyze_space ( p_segment_type, p_segment_owner, p_segment_name, t.partition_name ); END loop; ELSIF ( p_segment_type = 'INDEX' AND p_partitioned = 'YES' ) THEN p_segment_type := 'INDEX PARTITION'; FOR i IN ( SELECT partition_name FROM all_tab_partitions WHERE table_owner = p_segment_owner AND table_name = p_segment_name ORDER BY to_number( regexp_substr( partition_name, '[0-9]*[0-9]', 1 ) ) ) loop analyze_space ( p_segment_type, p_segment_owner, p_segment_name, i.partition_name ); END loop; ELSE analyze_space ( p_segment_type, p_segment_owner, p_segment_name, p_partition_name ); END IF; EXCEPTION WHEN others THEN dbms_output.put_line('Usage:'); dbms_output.put_line('- exec show_space(''table'',''owner'',''table_name'');'); dbms_output.put_line('- exec show_space(''table partition'',''owner'',''table_name'',''partition_name'');'); dbms_output.put_line('- exec show_space(''index'',''owner'',''index_name'');'); dbms_output.put_line('- exec show_space(''index partition'',''owner'',''index_name'',''partition_name'');'); END; /