Re: How to get user tablespace information?

Hi, Bricklen,
Thank you!
I just got headache. It is so complex in oracle, isn't it? Thanks for the help!

Bricklen wrote:

> 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:07:18 CST