RE: Free Space per tablespace....

From: Jeffery Stevenson <jeff_at_mpv.com>
Date: Tue, 5 Dec 2000 10:50:14 -0600
Message-Id: <10701.123678@fatcity.com>

--Total space allocated to tablespace

select * from sys.sm$ts_avail;

--Free space in tablespace

select * from sys.sm$ts_free;

--Used space in tablespace

select * from sys.sm$ts_used;

These are the tables that Storage Manager uses to calculate its pretty little graphic that shows the free space in a tablespace. :)

Jeffery Stevenson
Chief Databeast Tamer
Medical Present Value, Inc.
Austin, TX

-----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 also send the HELP command for other information (like subscribing). Received on Tue Dec 05 2000 - 10:50:14 CST