ORA-01563表空间占满无法扩充解决办法

 

发现数据库无法连接，报错内容截图如下：

0  用sql plus 命令登录数据库。

 sqlplus / as sysdba;

1 调整Oracle表空间大小(调整system01.dbf为4G)

alter database datafile '/home/db/oracle/oradata/orcl/system01.dbf' resize 4096m;

2 设置表空间自动增长

ALTER DATABASE DATAFILE '/home/db/oracle/oradata/orcl/system01.dbf'  AUTOEXTEND ON;//打开自动增长

3  查询表空间占用率及是否自增

select dbf.tablespace_name, dbf.totalspace "总量(M)", dbf.totalblocks as 总块数, dfs.freespace "剩余总量(M)", dfs.freeblocks "剩余块数", (dfs.freespace / dbf.totalspace) * 100 "空闲比例" from (select t.tablespace_name, sum(t.bytes) / 1024 / 1024 totalspace, sum(t.blocks) totalblocks from dba_data_files t group by t.tablespace_name) dbf, (select tt.tablespace_name, sum(tt.bytes) / 1024 / 1024 freespace, sum(tt.blocks) freeblocks from dba_free_space tt group by tt.tablespace_name) dfs where trim(dbf.tablespace_name) = trim(dfs.tablespace_name);

4  查看表空间是否开启了自动扩展的功能 

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;