select * from v$datafile;
select * from dba_data_files
select * from dba_tablespaces

---

 

***** 5 STAR *****
http://www.oracle-wiki.net/startsqldisplayspcusgdf

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
COLUMN "Tablespace Name" FORMAT A20
COLUMN "File Name" FORMAT A80

SELECT  Substr(df.tablespace_name,1,20) "Tablespace Name",
        Substr(df.file_name,1,80) "File Name",
        Round(df.bytes/1024/1024,0) "Size (M)",
        decode(e.used_bytes,NULL,0,Round(e.used_bytes/1024/1024,0)) "Used (M)",
        decode(f.free_bytes,NULL,0,Round(f.free_bytes/1024/1024,0)) "Free (M)",
        decode(e.used_bytes,NULL,0,Round((e.used_bytes/df.bytes)*100,0)) "% Used"
FROM    DBA_DATA_FILES DF,
       (SELECT file_id,
               sum(bytes) used_bytes
        FROM dba_extents
        GROUP by file_id) E,
       (SELECT Max(bytes) free_bytes,
               file_id
        FROM dba_free_space
        GROUP BY file_id) f
WHERE    e.file_id (+) = df.file_id
AND      df.file_id  = f.file_id (+)
ORDER BY df.tablespace_name,
         df.file_name
;

---

 

SQL> SELECT TABLESPACE_NAME, CONTENTS, STATUS  FROM DBA_TABLESPACES;

SQL> select substr(name,1,60),(Round(BYTES/1024/1024,0)) from v$tempfile;

SQL> SELECT TABLESPACE_NAME, ROUND(BYTES_USED/1024/1024) "USED (MB)",ROUND(BYTES_FREE/1024/1024) "FREE (MB)" from V$TEMP_SPACE_HEADER;

---

select b.tablespace_name,a.name,a.status,b.bytes/1024/1024 MB
from    v$datafile a
,       dba_data_files b
where a.file# = b.file_id

-- om uit te zoeken hoeveel ruimte er nog in de tablespace vrij is.

select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/datafile/datafile_prod01.dbf' RESIZE 12GM;