oracle表空间不足 ORA-00604 unable to extend table SYS.AUD by 8192

版权声明：本文为CSDN博主「wangjz2008」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。

原文链接：https://blog.csdn.net/wangjz2008/article/details/114081758

 

 

先检查磁盘空间是否正常，可以参考oracle各种trace清理https://blog.csdn.net/wangjz2008/article/details/114081633

1、登陆

root -- 登陆linux

docker exec -it oracle /bin/bash -- 进入docker

su - oracle

sqlplus /nolog

conn / as sysdba

 

2、设置显示样式

a)当前连接生效

设置sqlplus模式显示总行数/行宽度（当前连接生效）：

show pagesize; --查看目前的pagesize

show linesize; --查看当前的linesize宽度

set pagesize 300;

set linesize 2000;

b)永久生效

修改安装目录glogin.sql文件（永久生效）：

set pagesize 300;

set linesize 1000;

 

2、查看表空间

SELECT * FROM dba_data_files;

SELECT * FROM dba_free_space;

col Tablespace_Name format a20;

col total format a15;

col used format a15;

col unUsed format a15;

col rate format a15;

col file_name format a100;

SELECT a.FILE_ID,a.Tablespace_Name,a.total||'M' total ,(a.total-b.unUsed) ||'M' used,b.unUsed ||'M' unUsed ,Round((a.total-b.unUsed)/a.total*100,2)||'%' rate,a.file_name

FROM (Select FILE_ID,Tablespace_Name,Round(bytes/1024/1024,2) total,file_name From dba_data_files ) a

LEFT JOIN (SELECT FILE_ID ,Round(sum(blocks*8192/1020/1024),2) unUsed FROM dba_free_space GROUP BY FILE_ID) b ON a.FILE_ID=b.FILE_ID

ORDER BY ((a.total-b.unUsed)/a.total) desc;

 

3、调整表空间和自动增长大小

alter database datafile '/dbdata/bossdb/data/web_db001.dbf' resize 15000m;

alter database datafile '/ora/oradata/radius/undo.dbf' resize 1024m;

alter database datafile 'c:\smartdb01.ora' autoextend on;//打开自动增长

alter database datafile 'c:\smartdb01.ora' autoextend on next 200m ;//每次自动增长200m

alter database datafile 'c:\smartdb01.ora' autoextend on next 200m maxsize 1024m;//每次自动增长200m，数据表最大不超过1g

 

4、查看单表占用情况

select segment_name,Round(bytes/1024/1024,2)||'M' used from dba_segments where owner = USER order by bytes asc