查看用户默认表空间
 
SELECT USERNAME,DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME='DB_USER_NAME';
 
  查看表空间文件路径、大小、已使用、使用率
 
SELECT
  B.FILE_NAME 物理文件名,
  B.TABLESPACE_NAME 表空间名称,
  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 数据文件,TABLESPACE_NAME AS 表空间名称,AUTOEXTENSIBLE AS 自动扩展,STATUS AS 状态,MAXBYTES AS 可扩展最大值,USER_BYTES AS 已使用大小,INCREMENT_BY AS 自动扩展增量 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
 
 

1.   查看表空间使用率
 
--查询表空间的总容量
 
select tablespace_name,sum(bytes) /1024/1024 as MB from dba_data_files group by tablespace_name;
 
 
 
 
 
--查询表空间使用率
 
select total.tablespace_name,
        round(total.MB, 2) as Total_MB,
        round(total.MB - free.MB, 2) as Used_MB,
        round((1 - free.MB / total.MB) * 100, 2) || '%' as Used_Pct from
        (select tablespace_name,sum(bytes) / 1024 / 1024 as MB from dba_free_space group by tablespace_name) free,
        (select tablespace_name,sum(bytes) / 1024 / 1024 as MB from dba_data_files group by tablespace_name) total
        where free.tablespace_name = total.tablespace_name order by used_pct desc;
 
 
 
--对象类型：表   分区表   索引   分区索引共四种，我们统计一下不同种类对象各多少size
 
 select segment_type,sum(bytes)/1024/1024 total_size from dba_segments where wner='CCARE' group by segment_type;
 
 
 
--SQL计算出某个用户下所有对象的大小
 
select owner,segment_name,segment_type,partition_name,bytes from dba_segments where wner='CCARE';
 
 
 
--我们经常使用的LEO1表空间使用情况已经perfect显示，现在我们需要统计的是LEO1表空间包含对象的大小，由于记录数较多，只把SQL语句写在这里了
 
 
 
 select o.object_name,o.object_type,o.owner,s.tablespace_name,s.segment_name,s.bytes from dba_objects o,dba_segments s where s.tablespace_name='LEO1' and o.owner=s.owner and o.owner='LEO1' and o.object_type=s.segment_type;

oracle 查看表空间使用率（包括临时表）
 

select * from (
Select a.tablespace_name,
to_char(a.bytes/1024/1024,'99,999.999') total_bytes,
to_char(b.bytes/1024/1024,'99,999.999') free_bytes,
to_char(a.bytes/1024/1024 - b.bytes/1024/1024,'99,999.999') use_bytes,
to_char((1 - b.bytes/a.bytes)*100,'99.99') || '%' use
from (select tablespace_name,
sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name,
sum(bytes) bytes
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
union all
select c.tablespace_name,
to_char(c.bytes/1024/1024,'99,999.999') total_bytes,
to_char( (c.bytes-d.bytes_used)/1024/1024,'99,999.999') free_bytes,
to_char(d.bytes_used/1024/1024,'99,999.999') use_bytes,
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.tablespace_name "表空间名", 
 total "表空间大小", 

 free "表空间剩余大小", 

 (total - free) "表空间使用大小", 

 total / (1024 * 1024 * 1024) "表空间大小(G)", 

 free / (1024 * 1024 * 1024) "表空间剩余大小(G)", 

 (total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)", 

 round((total - free) / total, 4) * 100 "使用率 %" 

 FROM (SELECT tablespace_name, SUM(bytes) free 

 FROM dba_free_space 

 GROUP BY tablespace_name) a, 

 (SELECT tablespace_name, SUM(bytes) total 

 FROM dba_data_files 

 GROUP BY tablespace_name) b 

 
WHERE a.tablespace_name = b.tablespace_name 
 

 
 
//查看路径
 
 select * from dba_data_files;
 
 
 //追加表空间
 alter tablespace housedata add datafile '/oracle/oradata/house/HEDATA01.dbf' size 1024M autoextend on next 200M maxsize unlimited;
 
 

 
 
//查看临时表空间
 
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS, 
 USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
 NVL(FREE_SPACE,0) "FREE_SPACE(M)"
 FROM 
 (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
 FROM DBA_TEMP_FILES
 GROUP BY TABLESPACE_NAME) D,
 (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,
 ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE
 FROM V$TEMP_SPACE_HEADER
 GROUP BY TABLESPACE_NAME) F
 WHERE  D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)