oracle删除表空间文件后无法登录数据库用户问题解决

背景：因为测试库需要导入较大的表数据，导入后需要删掉，并且释放磁盘空间。结果删除后无法登录普通用户了。

解决过程：参考https://blog.csdn.net/jccyww/article/details/50766603

[oracle@jie ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 29 16:05:31 2016

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> conn /as sysdba
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 4.2950E+11 bytes
Fixed Size                 30164616 bytes
Variable Size            2.9528E+10 bytes
Database Buffers         3.9943E+11 bytes
Redo Buffers              506703872 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 18 - see DBWR trace file
ORA-01110: data file 18: '/u01/app/19.3.0/oradata/IOMAPP/GXYD_IM_TAB_04.dbf'

SQL> alter database datafile 18 offline drop;  --这里的6是根据上面错误信息里的data file 18

Database altered.

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 19 - see DBWR trace file
ORA-01110: data file 19: '/u01/app/19.3.0/oradata/IOMAPP/GXYD_IM_TAB_05.dbf'

删除了多少个数据库文件重复操作就行。

SQL> alter database datafile 19 offline drop;

Database altered.

一直到alter database open;提示成功为止。

SQL>  alter database open;

Database altered.

然后重启数据库就行

SQL> exist

sqlplus /nolog

SQL> conn /as sysdba
Connected.

SQL> shutdown immediate
Database closed.
Database dismounted.

ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 4.2950E+11 bytes
Fixed Size                 30164616 bytes
Variable Size            2.9528E+10 bytes
Database Buffers         3.9943E+11 bytes
Redo Buffers              506703872 bytes
Database mounted.
Database opened.
SQL> exit

重启下监听

lsnrctl stop --停止监听

lsnrctl start --启动监听

lsnrctl reload --重启监听

lsnrctl status --查看监听状态