SELECT USERNAME,DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME='DB_USER_NAME';

SELECT
  B.FILE_NAME 物理文件名,
  B.TABLESPACE_NAME 表空間名稱(chēng),
  B.BYTES/1024/1024 大小M,
  (B.BYTES-SUM(NVL(A.BYTES,0)))/1024/1024 已使用M,
  SUBSTR((B.BYTES-SUM(NVL(A.BYTES,0)))/(B.BYTES)*100,1,5) 使用率
FROM DBA_FREE_SPACE A,DBA_DATA_FILES B
WHERE A.FILE_ID=B.FILE_ID
GROUP BY B.TABLESPACE_NAME,B.FILE_NAME,B.BYTES
ORDER BY B.TABLESPACE_NAME;

SELECT FILE_NAME AS 數(shù)據(jù)文件,TABLESPACE_NAME AS 表空間名稱(chēng),AUTOEXTENSIBLE AS 自動(dòng)擴(kuò)展,STATUS AS 狀態(tài),MAXBYTES AS 可擴(kuò)展最大值,USER_BYTES AS 已使用大小,INCREMENT_BY AS 自動(dòng)擴(kuò)展增量 FROM DBA_DATA_FILES;

CREATE SMALLFILE TABLESPACE TABLE_SPACE_NAME DATAFILE 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\TEST.DBF' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED NOLOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO DEFAULT NOCOMPRESS;

ALTER DATABASE DATAFILE 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\TEST.DBF' RESIZE 200M;

DROP TABLESPACE TABLE_SPACE_NAME INCLUDING CONTENTS AND DATAFILES;

SELECT * FROM DBA_TEMP_FILES

select c.tablespace_name,
       to_char(c.bytes / 1024 / 1024 / 1024, '99,999.999') total_gb,
       to_char((c.bytes - d.bytes_used) / 1024 / 1024 / 1024, '99,999.999') free_gb,
       to_char(d.bytes_used / 1024 / 1024 / 1024, '99,999.999') use_gb,
       to_char(d.bytes_used * 100 / c.bytes, '99.99') || '%' use
  from (select tablespace_name, sum(bytes) bytes
          from dba_temp_files
         GROUP by tablespace_name) c,
       (select tablespace_name, sum(bytes_cached) bytes_used
          from v$temp_extent_pool
         GROUP by tablespace_name) d
 where c.tablespace_name = d.tablespace_name;

select a.username,
       a.sql_id,
       a.SEGTYPE,
       b.BYTES_USED/1024/1024/1024||'G',
       b.BYTES_FREE/1024/1024/1024  
from V$TEMPSEG_USAGE a join V$TEMP_SPACE_HEADER b on a.TABLESPACE=b.tablespace_name;

select * ?from V$TEMPORARY_LOBS;