no need for the headache dude, just cut 'n paste that code (2 dif. files
though) and execute. no probs.
Y wrote:
>
> 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:15:25 CST
