1、登录数据库时报错
ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table SYS.AUD$ by 1024 in tablespace SYSTEM
ORA-02002: error while writing to audit trail
ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table SYS.AUD$ by 1024 in tablespace SYSTEM
使用管理员权限,查询表空间及其使用情况
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
file_id,
file_name AS "表空间所在路径",
tablespace_name,
autoextensible,
increment_by,
round(bytes / (1024 * 1024), 0) total_space,
round(maxbytes / (1024 * 1024), 0) "maxbytes MB"
FROM
dba_data_files
ORDER BY
file_id ;
扩大表空间又分三种做法:
- 设置表空间自动扩展
alter database datafile '数据文件位置' autoextend on next 自动扩展大小 maxsize 最大扩展大小;
举例
alter database datafile '/data/PROD/db/apps_st/data/a_summ01.dbf' autoextend on next 100M maxsize 10000M;
- 增大所需表空间大小
alter database datafile '数据文件位置' resize 新的尺寸;
举例
SQL> alter database datafile '/data/PROD/db/apps_st/data/test.dbf' resize 16000M;
SQL> alter database datafile '/data/PROD/db/apps_st/data/test.dbf' resize 16G;
- 增加数据文件个数
alter tablespace 表空间名称 add datafile '新的数据文件位置' size 数据文件大小;
举例
SQL> alter tablespace APPS_TS_TX_DATA add datafile '/data/PROD/db/apps_st/data/a_summ02.dbf' size 1000M;
删除表空间
alter tablespace 表空间名称 DROP datafile '/app/orcl/product/10.2.0/oradata/orcl/test.dbf';