Re: How to get user tablespace information?

"Daniel A. Morgan" wrote:

> Not complex at all. Bricklen just handed you the entire thing. All you have to do is run it at the
> SQL> prompt.
>
> If that is a problem ... Oracle is not for you. Perhaps MS Access would be a better choice.

Thanks for your suggestion. I will consider it.

>
>
> Daniel Morgan
>
> 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:30:12 CST