orcle表空间的扩充

1.查看具体某个表空间大小、以及归属的目录

 

 select file_name,tablespace_name,bytes/1024/1024 as MB ,autoextensible,

maxbytes/1024/1024/1024 from dba_data_files;

 

或者查看数据文件

select * from v$datafile;

 

2.查看表空间使用情况

SELECT a.tablespace_name "表空间名",

       total / 1024 / 1024 "表空间大小",

       free / 1024 / 1024 "表空间剩余大小",

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

       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

 

3.扩充表空间

alter tablespace SXIT add datafile '/oracle/oradata/orcl/SXIT11.dbf' size 8G;

alter tablespace SXIT add datafile '/oracle/oradata/orcl/SXIT12.dbf' size 8G;

alter tablespace SXIT add datafile '/oracle/oradata/orcl/SXIT13.dbf' size 8G;

alter tablespace SXIT add datafile '/oracle/oradata/orcl/SXIT14.dbf' size 8G;

alter tablespace SXIT add datafile '/oracle/oradata/orcl/SXIT15.dbf' size 8G;

alter tablespace SXIT add datafile '/oracle/oradata/orcl/SXIT16.dbf' size 8G;

alter tablespace SXIT add datafile '/oracle/oradata/orcl/SXIT17.dbf' size 8G;

alter tablespace SXIT add datafile '/oracle/oradata/orcl/SXIT18.dbf' size 8G;

alter tablespace SXIT add datafile '/oracle/oradata/orcl/SXIT19.dbf' size 8G;

alter tablespace SXIT add datafile '/oracle/oradata/orcl/SXIT20.dbf' size 8G;

 

 

4.查看某个表的大小

 

查询该用户下所有表大小

select segment_name, ceil(sum(bytes) / 1024 / 1024 / 1024) || 'G' bytes

  from user_segments

 group by segment_name

 order by bytes desc;