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;

?