RE: Free Space per tablespace....

From: Smith, Ron L. <rlsmith_at_kmg.com>
Date: Tue, 5 Dec 2000 10:05:59 -0600
Message-Id: <10701.123669@fatcity.com>

Try this one:

spool ts_size2.txt
set verify off
set pagesize 55
set linesize 132
col T_space format a12
col fname format a37
col Use_Pct format 999.0 heading Used%

col Total    format 999,999,999
col Used_Mg  format 999,999,999.0
col Free_Mg  format 999,999,999.0

define Blk_size=4096                    /* Set Oracle Block Size */

set heading on

compute sum of Total on report
compute sum of Used_Mg on report
compute sum of Free_Mg on report
break on report

select decode(x.online$,1,x.name,

              substr(rpad(x.name,14),1,14)||' OFF') T_Space,
       round(sum(distinct (f.blocks+(f.file#/1000))*&Blk_Size)


               / (1024*1024) ) Total,

       round(sum(s.length*&Blk_Size)/(1024*1024),1) Used_Mg,
       round(sum(distinct (f.blocks+(f.file#/1000))*&Blk_Size)


               / (1024*1024)

           - nvl(sum(s.length*&Blk_Size)/(1024*1024),0), 1) Free_Mg,
       round(((sum(s.length*&Blk_Size)/(1024*1024) )
          / (sum(distinct (f.blocks+(f.file#/1000))*&Blk_Size)


               / (1024*1024) )) * 100, 1) Use_Pct

from   sys.uet$ s, sys.file$ f, sys.ts$ x
where  x.ts#     = f.ts#
and    x.name    NOT LIKE 'R%'
and    x.online$ in (1,2)                                       /* Online !!
*/
and    f.status$ = 2                                    /* Online !! */
and    f.ts#     = s.ts# (+)

and f.file# = s.file# (+)
group by x.name, x.online$
order by Use_Pct DESC
/
clear breaks
tti off
set verify on
spool off

-----Original Message-----
From: Rahul Dandekar [mailto:orcldba_at_hotmail.com] Sent: Tuesday, December 05, 2000 9:51 AM To: Multiple recipients of list ORACLE-L Subject: Free Space per tablespace....

Jeeeeeeeeeee this is going into my head now. Please help..... Cannot find my script and too bad that I am making mistakes in rebuiding....

I want to find out Space in Tablespace and Free Space. I have two simplest of individual queries.

Group by in first query helps grouping for each datafie in the tablespace

Group by in second helps grouping free chunks in the tablespace

column tablespace_name format a30
column tot_size format 9,999,999.90
column fr_size format 9,999,999.90

select tablespace_name, sum(bytes)/1048576 tot_size from dba_data_files
group by tablespace_name
order by tot_size;

select tablespace_name, sum(bytes)/1048576 fr_size from dba_free_space
group by tablespace_name
order by fr_size;

Output should be...

TABLESPACE_NAME TOT_SIZE FR_SIZE

------------------------ ------------ ---------------

TIA, -Rahul

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rahul Dandekar
  INET: orcldba_at_hotmail.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may

Received on Tue Dec 05 2000 - 10:05:59 CST