oracle表空间信息查询

–1.查看表空间使用情况

SELECT A.TABLESPACE_NAME "表空间名",
       A.BYTES / 1024 / 1024 "表空间大小(M)",
       (A.BYTES - B.BYTES) / 1024 / 1024 "已使用空间(M)",
       B.BYTES / 1024 / 1024 "空闲空间(M)",
       ROUND(((A.BYTES - B.BYTES) / A.BYTES) * 100, 2) "使用比"
  FROM (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES
          FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME) A,
       (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES, MAX(BYTES) LARGEST
          FROM DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) B
 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
 ORDER BY ((A.BYTES - B.BYTES) / A.BYTES) DESC;
 

---

------------ 查看表空间信息

select tablespace_name,sum(obj_cnt) object_count, sum(ini_ext) init_ext, sum(nex_ext) next_ext, sum(byte)/1048576 bytesize, (sum(byte)/1048576- sum(fbyte)/1048576) byteused, sum(fbyte)/1048576 bytefree, sum(frags), sum(largest)/1048576 avail, (sum(fbyte)/sum(byte))*100 free 
from ( select tablespace_name,0 obj_cnt,0 ini_ext,0 nex_ext, 0 byte, sum(bytes) fbyte, count(*) frags, max(bytes) largest 
from dba_free_space group by tablespace_name
union
select tablespace_name,0, 0, 0, sum(bytes), 0, 0, 0 from dba_data_files group by tablespace_name 
union select tablespace_name, 0, initial_extent/1024 ini_ext, 
next_extent/1024 nex_ext, 0,0,0,0 from dba_tablespaces 
union select tablespace_name, count(*) obj_cnt, 0, 0, 0, 0, 0, 0 from dba_segments 
group by tablespace_name)
having sum(fbyte) <> 0 group by tablespace_name

查看表空间路径
select * from dba_data_files

– 2.查看数据文件路径

 SELECT FILE_ID, FILE_NAME, TABLESPACE_NAME, AUTOEXTENSIBLE, INCREMENT_BY
  FROM DBA_DATA_FILES
 WHERE TABLESPACE_NAME = '表空间名称';

– 3.扩充表空间（四种方法）
– (1)给表空间增加数据文件（数据文件路径=前缀/新数据文件名称）

alter tablespace 表空间名 add datafile '数据文件路径' size 32768M;

– (2)给表空间增加数据文件,并允许数据文件自动增长（数据文件路径=前缀/新数据文件名称）

alter tablespace 表空间名 add datafile '数据文件路径' size 100M autoextend on next 100M maxsize 32768M;

– (3)手工改变已存在数据文件大小

alter database datafile '已存在数据文件路径及名称' resize 32768M

– 使已存在数据文件自动增长

alter database datafile '已存在数据文件路径及名称' autoextend on next 100M maxsize 32768M;