Re: How to get user tablespace information?

From: Bricklen <bricklen_at_shaw.ca>
Date: Mon, 11 Feb 2002 03:02:40 GMT
Message-ID: <3C6733FC.5E88D46C@shaw.ca>

so user_tablespaces isn't what you are after?

How about something like this:

set pagesize 999 feedback off verify on linesize 600 column tablespace_name heading 'Tablespace' format a20 column object_count heading 'Objects|(#)' format 999990 column mb heading 'Mb' format 9990
column sum(frags) heading 'Frags|(#)' format 9999 column avail heading 'Max|(Mb)' format 9999.99 column free heading 'Free|(%)' format 999.9

column bytesize heading 'Size|(Mb)' format 99999
column byteused heading 'Used|(Mb)' format 99990
column bytefree heading 'Free|(Mb)' format 9999
column init_ext heading 'Initial|(K)' format 999999999
column next_ext heading 'Next|(K)' format 999999

spool tablespace_statistics.sql
select tablespace_name, sum(obj_cnt) object_count, sum(ini_ext) init_ext, sum(nex_ext) next_ext, sum(byte)/1048576 bytesize,

round((sum(byte)/1048576)- (sum(fbyte)/1048576),2) byteused,
round(sum(fbyte)/1048576,2) bytefree,  sum(frags) "Frags", 
round(sum(largest)/1048576,2) avail,
round((sum(fbyte)/sum(byte))*100,2) free 

from
(select tablespace_name, 0 obj_cnt, 0 ini_ext, 0 nex_ext, 0 byte, sum(bytes) fbyte, count(*) frags, max(bytes) largest from dba_free_space
group by tablespace_name
union
select tablespace_name, 0, 0, 0, sum(bytes), 0, 0, 0 from dba_data_files
group by tablespace_name
union
select tablespace_name, 0, initial_extent/1024 ini_ext, next_extent/1024 nex_ext, 0,0,0,0
from dba_tablespaces
union
select tablespace_name, count(*) obj_cnt, 0, 0, 0, 0, 0, 0 from dba_segments
group by tablespace_name)
group by tablespace_name;
spool off
clear columns

or, for the high water mark (courtesy of Tim Hall):

/*
-- File Name hw_mark.sql

• Author DR Timothy S Hall
• http://www.tshcomputing.com/Index.asp
• Description The high water mark of all tables not belonging to SYS or SYSTEM
• Requirements Access to the DBMS_SPACE package */ SET SERVEROUTPUT ON SET VERIFY OFF DECLARE CURSOR cu_tables IS SELECT a.owner, a.table_name FROM all_tables a WHERE partitioned <> UPPER('YES') AND owner IN (select user from dual); op1 NUMBER; op2 NUMBER; op3 NUMBER; op4 NUMBER; op5 NUMBER; op6 NUMBER; op7 NUMBER; BEGIN Dbms_Output.Disable; Dbms_Output.Enable(1000000); Dbms_Output.Put_Line('TABLE UNUSED BLOCKS TOTAL BLOCKS HIGH WATER MARK'); Dbms_Output.Put_Line('------------------------------ --------------- --------------- ---------------'); FOR cur_rec IN cu_tables LOOP

Dbms_Space.Unused_Space(cur_rec.owner,cur_rec.table_name,'TABLE',op1,op2,op3,op4,op5,op6,op7);

    Dbms_Output.Put_Line(RPad(cur_rec.table_name,30,' ') ||
                         LPad(op3,15,' ')                ||
                         LPad(op1,15,' ')                ||
                         LPad(Trunc(op1-op3-1),15,' ')); 

  END LOOP;
END;
/
SET VERIFY ON Received on Sun Feb 10 2002 - 21:02:40 CST