ORA-01653: 表 xxxx 无法通过 8192 (在表空间 USERS 中) 扩展

来源：互联网发布：神奈川有什么大学知乎编辑：程序博客网时间：2024/06/06 05:03

Oracle中增加表空间大小的四种方法
1：给表空间增加数据文件
ALTER TABLESPACE app_data ADD DATAFILE
‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF’ SIZE 50M;
2：新增数据文件，并且允许数据文件自动增长
ALTER TABLESPACE app_data ADD DATAFILE
‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP04.DBF’ SIZE 50M
AUTOEXTEND ON NEXT 5M MAXSIZE 100M;
3：允许已存在的数据文件自动增长
ALTER DATABASE DATAFILE ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF’
AUTOEXTEND ON NEXT 5M MAXSIZE 100M;
4：手工改变已存在数据文件的大小
ALTER DATABASE DATAFILE ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP02.DBF’
RESIZE 100M;
扩展表空间的3种方法
1手动增加数据文件大小
alter database datafile ‘/home/oracle/ts01.dbf’ resize 100m;
2把表空间设置为自动扩展
alter database datafile ‘/home/oracle/ts01.dbf’ autoextend on next 5m maxsize unlimited;
3 往表空间增加数据文件
alter tablespace ts01 add datafile ‘/home/oracle/ts02.dbf’ size 2m;

 SQL> 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;TABLESPACE_NAME------------------------------------------------------------FILE_NAME--------------------------------------------------------------------------------AUTOEX      BYTES   MAXBYTES STATUS------ ---------- ---------- ------------------SYSAUX/data/app/oracle/oradata/orcl11g/sysaux01.dbfYES    1017118720 3.4360E+10 AVAILABLESYSTEM/data/app/oracle/oradata/orcl11g/system01.dbfYES     786432000 3.4360E+10 AVAILABLETABLESPACE_NAME------------------------------------------------------------FILE_NAME--------------------------------------------------------------------------------AUTOEX      BYTES   MAXBYTES STATUS------ ---------- ---------- ------------------UNDOTBS1/data/app/oracle/oradata/orcl11g/undotbs01.dbfYES    3523215360 3.4360E+10 AVAILABLEUSERS/data/app/oracle/oradata/orcl11g/users01.dbfTABLESPACE_NAME------------------------------------------------------------FILE_NAME--------------------------------------------------------------------------------AUTOEX      BYTES   MAXBYTES STATUS------ ---------- ---------- ------------------YES    3.4353E+10 3.4360E+10 AVAILABLE-- 修改自增长方式SQL> alter database datafile '/data/app/oracle/oradata/orcl11g/users01.dbf' autoextend on next 500m maxsize unlimited;数据库已更改。SQL> alter database datafile '/data/app/oracle/oradata/orcl11g/sysaux01.dbf' autoextend on next 500m maxsize unlimited;数据库已更改。SQL> alter database datafile '/data/app/oracle/oradata/orcl11g/system01.dbf' autoextend on next 500m maxsize unlimited;数据库已更改。SQL> alter database datafile '/data/app/oracle/oradata/orcl11g/undotbs01.dbf' autoextend on next 50m maxsize unlimited;数据库已更改。-----------查看表空间是否具有自动扩展的能力SQL> 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;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;  SQL> Select Tablespace_Name,Sum(bytes)/1024/1024 From Dba_Segments Group By Tablespace_Name;TABLESPACE_NAME------------------------------------------------------------SUM(BYTES)/1024/1024--------------------SYSAUX            839.6875UNDOTBS1              2731.5USERS            32755.25TABLESPACE_NAME------------------------------------------------------------SUM(BYTES)/1024/1024--------------------SYSTEM             748.625SQL> SELECT tablespace_name 表空间,sum(blocks*8192/1000000) 剩余空间M FROM dba_free_space GROUP BY tablespace_name;表空间                                                        剩余空间M------------------------------------------------------------ ----------SYSAUX                                                       135.593984UNDOTBS1                                                      657.98144USERS                                                           5.24288SYSTEM                                                          .393216SQL> select b.name,sum(a.bytes/1000000)总空间 from v$datafile a,v$tablespace b where a.ts#=b.ts# group by b.name;NAME                                                             总空间------------------------------------------------------------ ----------SYSAUX                                                       1017.11872UNDOTBS1                                                     3523.21536USERS                                                        34352.6605SYSTEM                                                          786.432SQL> SELECT SUM（BYTES） FROM DBA_EXTENTS WHERE SEGMENT_NAME = 'WECENJOY';SUM（BYTES）------------  2013265920

PS:oracle可管理的最大数据块为2的22次方个，而根据单个数据块大小大小的不同，其最大容量也是不同的。对于OLTP应用，数据块的大小通常为8K，这样，算下来，单个数据文件的大小最大为(2^22)*8K=32G.
既使指定“不限制”，其最大空间也是不能超过可管理最大空间的上限的。

修改表空间数据文件大小为不限制的语句为：
alter database datafile ‘/oradata/orcl/demo01.dbf’ autoextend on maxsize unlimited;

创建表空间数据文件大小为不限制的语句为：
create tablespace demo2 datafile ‘/oradata/orcl/demo201.dbf’ size 10M autoextend on maxsize unlimited;

所以只能
增加表空间
新增文件

SQL> alter tablespace SYSTEM add datafile '/data/app/oracle/oradata/orcl11g/system02.dbf' size 30000M autoextend on next 50M maxsize unlimited;SQL> alter tablespace USERS add datafile '/data/app/oracle/oradata/orcl11g/users02.dbf' size 30000M autoextend on next 50M maxsize unlimited;

注意路径正确有权限写入

再查表空间已经新增了

SQL>  SELECT tablespace_name 表空间,sum(blocks*8192/1000000) 剩余空间M FROM dba_free_space GROUP BY tablespace_name;表空间                                                        剩余空间M------------------------------------------------------------ ----------SYSAUX                                                       135.069696UNDOTBS1                                                      657.98144USERS                                                        33648.0174SYSTEM                                                       31456.6246

0 0