high water wark function table
- From: "Juan Carlos Reyes Pacheco" <jreyes@xxxxxxxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 28 Jul 2004 17:21:10 -0400
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
- » high water wark function table
