high water wark function table

From: Juan Carlos Reyes Pacheco <jreyes_at_dazasoftware.com>
Date: Wed, 28 Jul 2004 17:21:10 -0400
Message-Id: <410818C6.00000D.01764@CACHITOSS>

Hi list
I had done a simple function table to get free space only in tables for 9.2  I'll improve some day.
hope be usefull to you.

grant analyze any to daz;
DROP TYPE DAZ.typ_space ;
CREATE OR REPLACE TYPE DAZ.tyo_space AS OBJECT( Segment VARCHAR2(200), Owner VARCHAR2(200), Type VARCHAR2(200),Partition VARCHAR2(200),
FreeBlks number, SizeBlks number, SizeMB number, UnusedBlks number, UnusedMB number, LastUsedExtFileId number, LastUsedExtBlockId number, LasUseBlock number )
/

CREATE OR REPLACE TYPE DAZ.typ_space AS TABLE OF tyo_space;
/
 

CREATE OR REPLACE
FUNCTION DAZ.fut_table_space( cOwner VARCHAR2 DEFAULT '%', cSegment VARCHAR2 DEFAULT '%')
return typ_space
as
l_data typ_space := typ_space();
nTotBlks NUMBER;
nTodBytes NUMBER;
nUnusedBlks NUMBER;
nUnusedBytes NUMBER;
nLastUsedExtFileId NUMBER;
nLastUsedExtBlockId NUMBER;
nLasUseBlock NUMBER;
nFreeBlks NUMBER;
BEGIN
FOR A IN
( SELECT DISTINCT OWNER,TABLE_NAME SEGMENT_NAME,'TABLE' SEGMENT_TYPE,NULL PARTITION_NAME
FROM DBA_TABLES
WHERE TABLE_NAME NOT LIKE 'DR$%' AND IOT_TYPE IS NULL AND TEMPORARY = 'N' AND NOT TABLESPACE_NAME = 'SYSTEM'
AND OWNER LIKE cOwner
AND TABLE_NAME LIKE cSegment
) LOOP
nFreeBlks := NULL;
nTotBlks := -1;
nTodBytes := -1;
nUnusedBlks := -1;
nUnusedBytes := -1;
nLastUsedExtFileId := -1;
nLastUsedExtBlockId := -1;
nLasUseBlock := NULL;  

BEGIN
DBMS_SPACE.UNUSED_SPACE (

segment_owner => A.OWNER,
segment_name => A.SEGMENT_NAME,
segment_type =>A.SEGMENT_TYPE,

total_blocks => nTotBlks,
total_bytes => nTodBytes,
unused_blocks => nUnusedBlks,
unused_bytes => nUnusedBytes,

last_used_extent_file_id => nLastUsedExtFileId,
last_used_extent_block_id => nLastUsedExtBlockId,
last_used_block => nLasUseBlock,

partition_name => A.PARTITION_NAME);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM||'-'||cOwner||'-'||cSegment); END;
nTodBytes := ROUND(nTodBytes/1024/1024,2) ; nUnusedBytes := ROUND(nUnusedBytes/1024/1024,2) ;  

l_data.extend;
l_data(l_data.count) :=
tyo_space( A.SEGMENT_NAME, A.OWNER, A.SEGMENT_TYPE,A.PARTITION_NAME, nFreeBlks , nTotBlks , nTodBytes ,
nUnusedBlks , nUnusedBytes , nLastUsedExtFileId , nLastUsedExtBlockId , nLasUseBlock );
end loop;
return l_data;
end;
/

select * from
the ( select cast( DAZ.fut_table_space('DAZ','%') as DAZ.typ_space ) from dual )
order by unusedmb desc;  

Juan Carlos Reyes Pacheco
OCP

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed Jul 28 2004 - 16:22:31 CDT