SELECT tablespace_name “Tablespace”,
d.STATUS “Status”,
TO_CHAR((a.bytes / 1048576),’99,999,990.900′) “Total MB”,
TO_CHAR(((DECODE(f.bytes, NULL, 0, f.bytes)) / 1048576),’99,999,990.900′) “Free MB”,
TO_CHAR(((a.bytes – DECODE(f.bytes, NULL, 0, f.bytes)) / 1048576),’99,999,990.900′) “Used MB”,
TO_CHAR((((a.bytes – DECODE(f.bytes, NULL, 0, f.bytes)) / 1048576)*100) / (a.bytes / 1048576),’999,999.9′) “% Used”
FROM
(sys.dba_tablespaces d JOIN sys.sm$ts_avail a USING (tablespace_name))
LEFT OUTER JOIN sys.sm$ts_free f USING (tablespace_name)
ORDER BY 6
Another usefull query
select
df.tablespace_name “Tablespace”,
block_size “Block Size”,
(df.totalspace – fs.freespace) “Used MB”,
fs.freespace “Free MB”,
df.totalspace “Total MB”,
round(100 * (fs.freespace / df.totalspace)) “Pct. Free”
from
dba_tablespaces ts,
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select tablespace_name,
round(sum(bytes) / 1048576) FreeSpace
from dba_free_space
group by tablespace_name) fs
where
ts.tablespace_name = fs.tablespace_name
and
df.tablespace_name = fs.tablespace_name(+)
TO CHECK SINGLE TABLESPACE AND ALL ITS DATAFILE LIST IN MB
select TABLESPACE_NAME,FILE_NAME,bytes/1024/1024 “MB” from dba_data_files where TABLESPACE_NAME=upper(‘&tsname’);
TO ADD NEW DATAFILE
alter tablespace STAGE_DATA add datafile ‘+KITTU_VG/INST/datafile/stage15.dbf’ size 4000m;WHO IS USING TEMP SPACE
SELECT S.sid || ‘,’ || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) sort_ops
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
S.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;
