Free Space

[shailendra]

HI FRIENDS

I WANT TO KNOW THE FREE EXTENTS IN MY DATABASE HOW DO I FOUND?

AS WELL AS HOW MUCH SPACE IS AVAILIABLE?

THANKS

SHAILENDRA

[Sameer]

You can use view DBA_FREE_SPACE

lists % free and used for tablespaces.. Connect as SYSTEM or SYS user

set linesize 128
set pagesize 40
set heading off
set colsep '|'
column bytes format 999,999,999,999 heading "Bytes Grabbed"
column used format 999,999,999,999 heading "Bytes Grabbed"
column free format 999,999,999,999 heading "Free"

select to_char(sysdate,'DD-MON-YY HH24:MI:SS')
|| ' Tablespace Storage Allocation and Percent of space used '
from sys.dual
;
set heading on
clear computes
compute sum of bytes used free on report
break on report
SELECT d.tablespace_name "Name",
a.bytes bytes,
a.bytes - DECODE(f.bytes, NULL, 0, f.bytes) used ,
DECODE(f.bytes, NULL, 0, f.bytes) free ,
to_char(100 - (DECODE(f.bytes, NULL, 0, f.bytes)/(a.bytes)*100), '999.999') " Used %"
FROM sys.dba_tablespaces d, sys.sm$ts_avail a, sys.sm$ts_free f
WHERE d.tablespace_name = a.tablespace_name AND f.tablespace_name (+) = d.tablespace_name;

HTH

Sameer

[shailendra]

THANKS A LOT SAMEER

IT IS VERY USEFUL FOR ME

SHAILENDRA

[shailendra]

I HAVE STILL ONE QUERY

I HAVE CREATED ONE TABLE AND INSERT A LOT OF DATA IN THAT TABLE AND BECAUSE OF THAT THE WHOLE TABLESPACE IS FULL AND IT IS SHOWING 100% USED AS PER YOUR SCRIPT NOW THE PROBLEM IS THAT WHEN I DROP THE TABLE IT SHOULD SHOW ME SOME % FREE BUT IT IS STILL SHOWING ME THE 100% FULL

CAN YOU TELL ME WHY IT IS STILL SHOWING ME 100% USED.

THANKS

SHAILENDRA

[Sameer]

Execute

ALTER TABLESPACE tablespace_name coalesce ;

Sameer

[shailendra]

I HAVE EXECUTED IT BUT IT IS STILL SHOWING ME THE 100% USED.

[pando]

the table is not in that tablespace then