oracle导入数据之前，删除用户时候报错“ORA-01940: cannot drop a user that is currently connected”，原因是因为用户正在连接使用。
1
2
3
4
5
| SQL> drop user air_forecast cascade;
drop user air_forecast cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected
|
查询一下当前用户连接
1
2
3
4
5
| SQL> select sid,serial# from v$session where username='AIR_FORECAST';
SID SERIAL#
---------- ----------
237 2083
252 3564
|
杀掉占用进程
1
2
3
4
5
6
7
| SQL> alter system kill session '237,2083';
System altered.
SQL> alter system kill session '252,3564';
System altered.
|
删除用户
1
2
3
4
| SQL> drop user air_forecast cascade;
User dropped.
|
PS：上述语句中没有状态列，所以看到删除的进程还在，其实是已经标记为KILLED状态了(status 为要删除用户的session状态，如果还为inactive，说明没有被kill掉，如果状态为killed，说明已kill)，删除进程后使用如下语句查询.
1
| select saddr,sid,serial#,paddr,username,status from v$session where username is not null;
|
导入用户数据
1
2
| SQL> select * from dba_directories; #查询存储文件目录，将备份文件拷贝到此目录下
shell> impdp userid="'sys/password@sx21 as sysdba'" schemas=air_forecast directory=DATA_PUMP_DIR dumpfile=AIR_FORECAST.DMP
|