-
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
-
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
-
THANKS A LOT SAMEER
IT IS VERY USEFUL FOR ME
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
-
Execute
ALTER TABLESPACE tablespace_name coalesce ;
Sameer
-
I HAVE EXECUTED IT BUT IT IS STILL SHOWING ME THE 100% USED.
-
the table is not in that tablespace then
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|