ORA-01653: unable to extend table xxx by 8192 in tablespace xxx 表空间满

最新推荐文章于 2024-09-24 08:36:22 发布

allen_swj

最新推荐文章于 2024-09-24 08:36:22 发布

阅读量1.3w

点赞数 3

分类专栏：数据库 Oracle 文章标签： ORA-01653 表空间满

本文链接：https://blog.csdn.net/qq_35257875/article/details/90295272

版权

数据库同时被 2 个专栏收录

25 篇文章 0 订阅

订阅专栏

Oracle

3 篇文章 0 订阅

订阅专栏

解决：

----查询表空间使用情况---     
   
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",     
D.TOT_GROOTTE_MB "表空间大小(M)",     
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",     
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') "使用比",     
F.TOTAL_BYTES "空闲空间(M)",     
F.MAX_BYTES "最大块(M)"    
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 4 DESC;

--查看表空间是否具有自动扩展的能力     
SELECT T.TABLESPACE_NAME,D.FILE_NAME,     
D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS     
FROM DBA_TABLESPACES T,DBA_DATA_FILES D     
WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME     
 ORDER BY TABLESPACE_NAME,FILE_NAME;

给文件赋予增长权限
alter database datafile 'D:/Program Files/ORACLE/orcl/USERS01.DBF' autoextend on maxsize unlimited;

新增db文件
Alter tablespace USERS add datafile 'D:/Program Files/ORACLE/orcl/USERS02.DBF' size 1024M autoextend on next 1024M Maxsize UNLIMITED;

原因及过程

用普通用户连接数据库失败：系统表空间满，不能为审计表AUD$分配空间

一、提示错误：

SQL> conn user/password

ERROR:

ORA-00604: erroroccurred at recursive SQL level 1

ORA-01653: unable toextend table SYS.AUD$ by 8192 in tablespace SYSTEM

ORA-02002: errorwhile writing to audit trail

ORA-00604: erroroccurred at recursive SQL level 1

ORA-01653: unable toextend table SYS.AUD$ by 8192 in tablespace SYSTEM

看字面意思：在system表空间中不能扩充表SYS.AUD$

解决办法：此时用管理员登录扩充表空间：

方法一：给表空间增加数据文件，并允许自动增长：

ALTER TABLESPACE SYSTEM ADD DATAFILE '/userdata/app/Administrators/oradata/ORCL/system03.dbf' SIZE5000M AUTOEXTEND ON NEXT 5000M MAXSIZE 20000M;

方法二：允许已存在的数据文件自动增长：

alter database datafile '/userdata/app/Administrators/oradata/ORCL/system01.dbf' autoextend on next 5000M maxsize unlimited;

方法三：手工改变已存在数据文件大小：

alter database datafile '/userdata/app/Administrators/oradata/ORCL/system01.dbf' resize 40000M;

oracle可管理的最大数据块为2的22次方个，而根据单个数据块大小大小的不同，其最大容量也是不同的。对于OLTP应用，数据块的大小通常为8K，这样，算下来，单个数据文件的大小最大为(2^22)*8K=32G.

既使指定“不限制”，其最大空间也是不能超过可管理最大空间的上限的。

修改表空间数据文件大小为不限制的语句为：

alter databasedatafile '/oradata/orcl/demo01.dbf' autoextend on maxsize unlimited;

创建表空间数据文件大小为不限制的语句为：

create tablespacedemo2 datafile '/oradata/orcl/demo201.dbf' size 10M autoextend on maxsizeunlimited;

分析原因：

系统表空间正常情况下只存放了数据字典之类的东西，所以占用的空间一般在500M以下。如果你的系统表空间占用比较多的空间，可能有以下几方面的原因：

1）没有为用户明确指定默认表空间，导致system系统表空间作为用户默认表空间

2）开启了审计,请检查此表的大小AUD$

你可以运行以下查询来检查一下系统表空间哪些表比较大：

SQL> select * from (selectSEGMENT_NAME,sum(bytes)/1024/1024 sx from dba_segments

where tablespace_name='SYSTEM'group by segment_name)

where sx>100 order by sxdesc;

通过查询结果可以看出AUD$表以达到32G

用sys登录数据库查看该表纪录数

select count(*) sum from AUD$;

可以查询到AUD$占用空间几乎达到32GB与上述数据文件最大限制复合，所以无法新增

解决方法大概可分为以下几种:

一、为system表空间另外新增一个数据文件。

alter tablespace system add datafile 'D:\oracleXE\oradata\XE\system_01.dbf' resize 1024M;

二、更改system表空间的数据文件SYSTEM.dbf分配空间。

alter database datafile 'D:\oracleXE\oradata\XE\system_01.dbf' autoextend on;

alter database datafile 'D:\oracleXE\oradata\XE\system_01.dbf' resize 1024M;

三、truncate掉AUD$表并关闭审计功能：

SQL> alter system set audit_trail=none scope=spfile;

SQL>shutdown immediate;

SQL>startup;

四、将AUD$默认表空间由system移出。

数据库oracle11g默认是关闭审计的，为了跟踪监视用户所执行的数据库操作，审计记录可存在数据字典表（称为审计记录:存储在system表空间中的SYS.AUD$表中，可通过视图dba_audit_trail查看）或操作系统审计记录中(默认位置为$ORACLE_BASE/admin/$ORACLE_SID/adump/).。默认情况下审计是没有开启的。详见下面的《关于审计部分》

如果要开启最好建立别的表空间专门存放审计，这样也不会影响system表空间性能

转移步骤：

1、新增表空间：

create tablespace SHENJI datafile '/userdata/app/Administrators/oradata/ORCL/shenji01.dbf' size5000MAutoextend onNext 5000M Maxsize unlimited;

2、迁移审计表和表上的索引：select index_name,index_type from user_indexes where table_name='AUD$'/'AUDIT$'/'AUDIT_AC'

查看表索引：

SQL>alter table aud$ move tablespace shenji;

Tablealtered.

SQL>alter table audit$ move tablespace shenji;

Tablealtered.

SQL>alter index i_audit rebuild online tablespace shenji;

Indexaltered.

SQL>alter table audit_actions move tablespace shenji;

Tablealtered.

SQL>alter index i_audit_actions rebuild online tablespace shenji;

Indexaltered.

查询表空间、数据文件、文件号：

selectfile_name,file_id,tablespace_name from dba_data_files;

查询数据库有哪些表空间：

查询现有的表空间大小：

selecttablespace_name,sum(bytes)/1024/1024 from dba_data_files group bytablespace_name;

查询表空间上有哪些用户：

select distinct s.owner from dba_segments s wheres.tablespace_name ='SYSTEM';

查询表空间实际使用大小：

selectb.file_id id, b.tablespace_name tbsname,b.bytes/1024/1024||'M'byte,(b.bytes-sum(nvl(a.bytes,0)))/1024/1024||'M' used,sum(nvl(a.bytes,0))/1024/1024||'M' lest,100-sum(nvl(a.bytes,0))/(b.bytes)*100percent from dba_free_space a,dba_data_files b where a.file_id=b.file_id groupby b.tablespace_name,b.file_id,b.bytes order by b.file_id;

转：https://blog.csdn.net/hufeng719/article/details/76602240
https://blog.csdn.net/naruto0025/article/details/78622374
https://blog.csdn.net/xindiai/article/details/79025959