64 lines
2.1 KiB
SQL
64 lines
2.1 KiB
SQL
@@header
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display Unused space in a segment
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 27-Nov-07 Vishal Gupta First Draft
|
|
*/
|
|
|
|
set serveroutput on
|
|
set numf 999,999,999,999
|
|
set verify off
|
|
|
|
define SEGMENT_OWNER="&&1"
|
|
define SEGMENT_NAME="&&2"
|
|
define SEGMENT_TYPE="&&3"
|
|
--define PARTITION_NAME="&&4"
|
|
|
|
DECLARE
|
|
l_total_blocks NUMBER;
|
|
l_total_bytes NUMBER;
|
|
l_unused_blocks NUMBER;
|
|
l_unused_bytes NUMBER;
|
|
l_last_used_extent_file_id NUMBER;
|
|
l_last_used_extent_block_id NUMBER;
|
|
l_last_used_block NUMBER;
|
|
BEGIN
|
|
DBMS_SPACE.UNUSED_SPACE (
|
|
'&&SEGMENT_OWNER'
|
|
,'&&SEGMENT_NAME'
|
|
,'&&SEGMENT_TYPE'
|
|
,l_total_blocks
|
|
,l_total_bytes
|
|
,l_unused_blocks
|
|
,l_unused_bytes
|
|
,l_last_used_extent_file_id
|
|
,l_last_used_extent_block_id
|
|
,l_last_used_block
|
|
,'&&PARTITION_NAME'
|
|
);
|
|
DBMS_OUTPUT.PUT_line(' ' );
|
|
DBMS_OUTPUT.PUT_line(' ' );
|
|
DBMS_OUTPUT.PUT_line('Segment Owner : ' || '&&SEGMENT_OWNER' );
|
|
DBMS_OUTPUT.PUT_line('Segment Name : ' || '&&SEGMENT_NAME' );
|
|
DBMS_OUTPUT.PUT_line('Segment Type : ' || '&&SEGMENT_TYPE' );
|
|
DBMS_OUTPUT.PUT_line('Total Blocks : ' || TO_CHAR(l_total_blocks,'999,999,999,999,999') );
|
|
DBMS_OUTPUT.PUT_line('Total Bytes : ' || TO_CHAR(l_total_bytes,'999,999,999,999,999'));
|
|
DBMS_OUTPUT.PUT_line('Total unused blocks : ' || TO_CHAR(l_unused_blocks,'999,999,999,999,999'));
|
|
DBMS_OUTPUT.PUT_line('Total unused bytes : ' || TO_CHAR(l_unused_bytes,'999,999,999,999,999'));
|
|
DBMS_OUTPUT.PUT_line('Last used extent file id : ' || TO_CHAR(l_last_used_extent_file_id,'999,999,999,999,999'));
|
|
DBMS_OUTPUT.PUT_line('Last used extend Block id : ' || TO_CHAR(l_last_used_extent_block_id,'999,999,999,999,999'));
|
|
DBMS_OUTPUT.PUT_line('Last used extend Block : ' || TO_CHAR(l_last_used_block,'999,999,999,999,999'));
|
|
END;
|
|
/
|
|
|
|
undefine SEGMENT_OWNER
|
|
undefine SEGMENT_NAME
|
|
undefine SEGMENT_TYPE
|
|
|
|
@@footer |