1.下面的脚本为表空间使用情况查询脚本
select upper(f.tablespace_name) "ts-name",
d.tot_grootte_mb "ts-bytes(m)",
d.tot_grootte_mb - f.total_bytes "ts-used (m)",
f.total_bytes "ts-free(m)",
to_char(round((d.tot_grootte_mb - f.total_bytes) / d.tot_grootte_mb * 100,
2),
'990.99') "ts-per"
from (select tablespace_name,
round(sum(bytes) / (1024 * 1024), 2) total_bytes,
round(max(bytes) / (1024 * 1024), 2) max_bytes
from sys.dba_free_space
group by tablespace_name) f,
(select dd.tablespace_name,
round(sum(dd.bytes) / (1024 * 1024), 2) tot_grootte_mb
from sys.dba_data_files dd
group by dd.tablespace_name) d
where d.tablespace_name = f.tablespace_name
order by 1 desc;
ts-name ts-bytes(m) ts-used (m) ts-free(m) ts-per
------------------------------ ----------- ----------- ---------- -------
USERS 5 3.06 1.94 61.20
UNDOTBS1 35 33.62 1.38 96.06
SYSTEM 480 477.62 2.38 99.50
SYSAUX 240 239.5 .5 99.79
EXAMPLE 100 68.25 31.75 68.25
这里system,sysaux表空间使用率都超过99%,这样会影响系统运行速度,因此需要将这两个表空间扩容
2.可以使用下面语句进行扩容
首先需要查询数据文件的号
SQL> select file_name,file_id from dba_data_files;
FILE_NAME FILE_ID
---------------------------------------------------------------------------------------------------- ----------
/u01/app/oracle/oradata/orcl/example01.dbf 5
/u01/app/oracle/oradata/orcl/users01.dbf 4
/u01/app/oracle/oradata/orcl/sysaux01.dbf 3
/u01/app/oracle/oradata/orcl/undotbs01.dbf 2
/u01/app/oracle/oradata/orcl/system01.dbf 1
也可以使用下面语句进行查询
SQL> select a.file#,a.ts#,a.name,b.ts#,b.name from v$datafile a,v$tablespace b where a.ts#=b.ts#;
FILE# TS# NAME TS# NAME
---------- ---------- -------------------------------------------------- ---------- --------------------
1 0 /u01/app/oracle/oradata/orcl/system01.dbf 0 SYSTEM
2 1 /u01/app/oracle/oradata/orcl/undotbs01.dbf 1 UNDOTBS1
3 2 /u01/app/oracle/oradata/orcl/sysaux01.dbf 2 SYSAUX
4 4 /u01/app/oracle/oradata/orcl/users01.dbf 4 USERS
5 6 /u01/app/oracle/oradata/orcl/example01.dbf 6 EXAMPLE
这里可以看到表空间对应的file_id是1,使用下面语句进行数据文件的扩容
(1)将原来的数据文件进行扩容
SQL> alter database datafile 1 resize 550m;
Database altered.
(2)增加数据文件方式
SQL> alter tablespace users add datafile '/u01/app/oracle/oradata/orcl/users02.dbf' size 100m autoextend on next 10m maxsize 200m;
Tablespace altered
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25794484/viewspace-732056/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25794484/viewspace-732056/