[font=Helvetica]--1查看表空间已经使用的百分比

[font=Monaco]Sql代码
[color=rgb(127,20,85)][backcolor=rgb(250,250,250)][font=Monaco]select[backcolor=rgb(250,250,250)][font=Monaco] a.tablespace_name,a.bytes/1024/1024 [color=rgb(4,51,255)][backcolor=rgb(250,250,250)][font=Monaco]"Sum MB"[backcolor=rgb(250,250,250)][font=Monaco],(a.bytes-b.bytes)/1024/1024 [color=rgb(4,51,255)][backcolor=rgb(250,250,250)][font=Monaco]"used MB"[backcolor=rgb(250,250,250)][font=Monaco],b.bytes/1024/1024 [color=rgb(4,51,255)][backcolor=rgb(250,250,250)][font=Monaco]"free MB"[backcolor=rgb(250,250,250)][font=Monaco],round(((a.bytes-b.bytes)/a.bytes)*100,2) [color=rgb(4,51,255)][backcolor=rgb(250,250,250)][font=Monaco]"percent_used"[backcolor=rgb(250,250,250)][font=Monaco]
[color=rgb(127,20,85)][backcolor=rgb(250,250,250)][font=Monaco]from[backcolor=rgb(250,250,250)][font=Monaco]
[backcolor=rgb(250,250,250)][font=Monaco]([color=rgb(127,20,85)][backcolor=rgb(250,250,250)][font=Monaco]select[backcolor=rgb(250,250,250)][font=Monaco] tablespace_name,[color=rgb(255,47,147)][backcolor=rgb(250,250,250)][font=Monaco]sum[backcolor=rgb(250,250,250)][font=Monaco](bytes) bytes [color=rgb(127,20,85)][backcolor=rgb(250,250,250)][font=Monaco]from[backcolor=rgb(250,250,250)][font=Monaco] dba_data_files [color=rgb(127,20,85)][backcolor=rgb(250,250,250)][font=Monaco]group[backcolor=rgb(250,250,250)][font=Monaco] [color=rgb(127,20,85)][backcolor=rgb(250,250,250)][font=Monaco]by[backcolor=rgb(250,250,250)][font=Monaco] tablespace_name) a,
[backcolor=rgb(250,250,250)][font=Monaco]([color=rgb(127,20,85)][backcolor=rgb(250,250,250)][font=Monaco]select[backcolor=rgb(250,250,250)][font=Monaco] tablespace_name,[color=rgb(255,47,147)][backcolor=rgb(250,250,250)][font=Monaco]sum[backcolor=rgb(250,250,250)][font=Monaco](bytes) bytes,[color=rgb(127,20,85)][backcolor=rgb(250,250,250)][font=Monaco]max[backcolor=rgb(250,250,250)][font=Monaco](bytes) largest [color=rgb(127,20,85)][backcolor=rgb(250,250,250)][font=Monaco]from[backcolor=rgb(250,250,250)][font=Monaco] dba_free_space [color=rgb(127,20,85)][backcolor=rgb(250,250,250)][font=Monaco]group[backcolor=rgb(250,250,250)][font=Monaco] [color=rgb(127,20,85)][backcolor=rgb(250,250,250)][font=Monaco]by[backcolor=rgb(250,250,250)][font=Monaco] tablespace_name) b
[color=rgb(127,20,85)][backcolor=rgb(250,250,250)][font=Monaco]where[backcolor=rgb(250,250,250)][font=Monaco] a.tablespace_name=b.tablespace_name
[color=rgb(127,20,85)][backcolor=rgb(250,250,250)][font=Monaco]order[backcolor=rgb(250,250,250)][font=Monaco] [color=rgb(127,20,85)][backcolor=rgb(250,250,250)][font=Monaco]by[backcolor=rgb(250,250,250)][font=Monaco] ((a.bytes-b.bytes)/a.bytes) [color=rgb(127,20,85)][backcolor=rgb(250,250,250)][font=Monaco]desc[backcolor=rgb(250,250,250)][font=Monaco]

[size=13px]select a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB",round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used" 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;

[font=Helvetica]“Sum MB”表示表空间所有的数据文件总共在操作系统占用磁盘空间的大小
[font=Helvetica]比如：test表空间有2个数据文件，datafile1为300MB，datafile2为400MB，那么test表空间的“Sum MB”就是700MB “userd MB”表示表空间已经使用了多少 “free MB”表示表空间剩余多少 “percent_user”表示已经使用的百分比

[font=Helvetica]--2比如从1中查看到MLOG_NORM_SPACE表空间已使用百分比达到90%以上，
[font=Helvetica]可以查看该表空间总共有几个数
[font=Helvetica]据文件，每个数据文件是否自动扩展，可以自动扩展的最大值。
[font=Monaco]Sql代码
[color=rgb(127,20,85)][backcolor=rgb(250,250,250)][font=Monaco]select[backcolor=rgb(250,250,250)][font=Monaco] file_name,tablespace_name,bytes/1024/1024 [color=rgb(4,51,255)][backcolor=rgb(250,250,250)][font=Monaco]"bytes MB"[backcolor=rgb(250,250,250)][font=Monaco],maxbytes/1024/1024 [color=rgb(4,51,255)][backcolor=rgb(250,250,250)][font=Monaco]"maxbytes MB"[backcolor=rgb(250,250,250)][font=Monaco] [color=rgb(127,20,85)][backcolor=rgb(250,250,250)][font=Monaco]from[backcolor=rgb(250,250,250)][font=Monaco] dba_data_files
[backcolor=rgb(250,250,250)][font=Monaco] [color=rgb(127,20,85)][backcolor=rgb(250,250,250)][font=Monaco]where[backcolor=rgb(250,250,250)][font=Monaco] tablespace_name=[color=rgb(4,51,255)][backcolor=rgb(250,250,250)][font=Monaco]'MLOG_NORM_SPACE'[backcolor=rgb(250,250,250)][font=Monaco];
[backcolor=#fafafa]
[font=Helvetica]--2.1 查看 xxx 表空间是否为自动扩展
[font=Helvetica]
[font=Monaco]Sql代码
[color=rgb(127,20,85)][backcolor=rgb(250,250,250)][font=Monaco]select[backcolor=rgb(250,250,250)][font=Monaco] file_id,file_name,tablespace_name,autoextensible,increment_by [color=rgb(127,20,85)][backcolor=rgb(250,250,250)][font=Monaco]from[backcolor=rgb(250,250,250)][font=Monaco] dba_data_files [color=rgb(127,20,85)][backcolor=rgb(250,250,250)][font=Monaco]order[backcolor=rgb(250,250,250)][font=Monaco] [color=rgb(127,20,85)][backcolor=rgb(250,250,250)][font=Monaco]by[backcolor=rgb(250,250,250)][font=Monaco] file_id [color=rgb(127,20,85)][backcolor=rgb(250,250,250)][font=Monaco]desc[backcolor=rgb(250,250,250)][font=Monaco]; [color=rgb(44,145,175)][font=Helvetica]

[font=Helvetica]--3比如MLOG_NORM_SPACE表空间
[font=Helvetica]目前的大小为19GB，但最大每个数据文件只能为20GB，数据文[font=Helvetica]件快要写满，可以增加表空间的数据文件 用操作系统UNIX、Linux中的df -g命令(查看下可以使用的磁盘空间大小) 获取创建表空间的语句：
[font=Monaco]Sql代码
[color=rgb(127,20,85)][backcolor=rgb(250,250,250)][font=Monaco]select[backcolor=rgb(250,250,250)][font=Monaco] dbms_metadata.get_ddl([color=rgb(4,51,255)][backcolor=rgb(250,250,250)][font=Monaco]'TABLESPACE'[backcolor=rgb(250,250,250)][font=Monaco],[color=rgb(4,51,255)][backcolor=rgb(250,250,250)][font=Monaco]'MLOG_NORM_SPACE'[backcolor=rgb(250,250,250)][font=Monaco]) [color=rgb(127,20,85)][backcolor=rgb(250,250,250)][font=Monaco]from[backcolor=rgb(250,250,250)][font=Monaco] dual;
[backcolor=#fafafa]
[font=Helvetica]--4确认磁盘空间足够，增加一个数据文件
[font=Monaco]Sql代码
[color=rgb(127,20,85)][backcolor=rgb(250,250,250)][font=Monaco]alter[backcolor=rgb(250,250,250)][font=Monaco] tablespace MLOG_NORM_SPACE
[color=rgb(127,20,85)][backcolor=rgb(250,250,250)][font=Monaco]add[backcolor=rgb(250,250,250)][font=Monaco] datafile [color=rgb(4,51,255)][backcolor=rgb(250,250,250)][font=Monaco]'/oracle/oms/oradata/mlog/Mlog_Norm_data001.dbf'[backcolor=rgb(250,250,250)][font=Monaco]
[color=rgb(127,20,85)][backcolor=rgb(250,250,250)][font=Monaco]size[backcolor=rgb(250,250,250)][font=Monaco] 10M autoextend [color=rgb(127,20,85)][backcolor=rgb(250,250,250)][font=Monaco]on[backcolor=rgb(250,250,250)][font=Monaco] maxsize 20G

[backcolor=#fafafa]
[font=Helvetica]--5验证已经增加的数据文件
[font=Monaco]Sql代码
[color=rgb(127,20,85)][backcolor=rgb(250,250,250)][font=Monaco]select[backcolor=rgb(250,250,250)][font=Monaco] file_name,file_id,tablespace_name [color=rgb(127,20,85)][backcolor=rgb(250,250,250)][font=Monaco]from[backcolor=rgb(250,250,250)][font=Monaco] dba_data_files
[color=rgb(127,20,85)][backcolor=rgb(250,250,250)][font=Monaco]where[backcolor=rgb(250,250,250)][font=Monaco] tablespace_name=[color=rgb(4,51,255)][backcolor=rgb(250,250,250)][font=Monaco]'MLOG_NORM_SPACE'[backcolor=rgb(250,250,250)][font=Monaco]

[font=Helvetica]--6如果删除表空间数据文件，如下：
[font=Monaco]Sql代码
[color=rgb(127,20,85)][backcolor=rgb(250,250,250)][font=Monaco]alter[backcolor=rgb(250,250,250)][font=Monaco] tablespace MLOG_NORM_SPACE
[color=rgb(127,20,85)][backcolor=rgb(250,250,250)][font=Monaco]drop[backcolor=rgb(250,250,250)][font=Monaco] datafile [color=rgb(4,51,255)][backcolor=rgb(250,250,250)][font=Monaco]'/oracle/oms/oradata/mlog/Mlog_Norm_data001.dbf'[backcolor=rgb(250,250,250)][font=Monaco]