oracle问题解决集锦

---

一、解决ORA-00020:maximum number of processes (150) exceeded 错误

杀死所有oracle进程

su - oracle
ps -ef |grep $ORACLE_SID|grep -v grep|awk '{print $2}' | xargs kill -9
ipcs -m | grep oracle | awk '{print $2}' | xargs ipcrm shm
lsnrctl stop
lsnrctl start

查看当前最大连接数sessions和最大线程数processes

sqlplus / as sysdba
SQL> startup;
SQL> show parameter sessions;

SQL> show parameter processes;

查看已经使用的连接数sessions和线程数processes

select count(*) from v$session;
select count(*) from v$process;

修改最大连接数sessions和最大线程数processes

alter system set sessions=2272 scope=spfile;
alter system set processes=1500 scope=spfile;
shutdown immediate;
startup;

修改成功

二、连接不上实例，lsnrctl status提示The listener supports no services 问题解决方法

解决方法

修改监听文件/deploy/oracle/product/11.2.0.4/network/admin/listener.ora
监听文件，通过lsnrctl status能查看到

增加如下内容：

SID_LIST_LISTENER =
(SID_LIST =
  (SID_DESC =
  (GLOBAL_DBNAME = orcl)
  (SID_NAME = orcl)
  )
)

lsnrctl stop
lsnrctl start
sqlplus / as sysdba
SQL> startup

三、ORA-01940: cannot drop a user that is currently connected

su - oracle
sqlplus / as sysdba

查找用户下哪些连接在运行

SQL> SELECT SID,SERIAL# FROM V$SESSION WHERE USERNAME='用户名(大写)';   

删除用户进程

SQL> ALTER  SYSTEM  KILL SESSION '22,53461';
SQL> ALTER  SYSTEM  KILL SESSION '221,62305';

重新查看用户连接，并确认无连接再删除

SQL> drop user 用户名 cascade;

四、ORA-28000: the account is locked

用户被锁了

解决

su - oracle
sqlplus / as sysdba
SQL> alter user 用户名 account unlock;

五、 备份数据库时报ORA-01555: snapshot too old

解决

truncate T_LOG_EXCEPTION_STACK_202203

六、删除表报错ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

当前用户登录

SQL> select l.session_id,o.owner,o.object_name from v$locked_object l,dba_objects o where l.object_id=o.object_id;

得到SID

根据SID查看更详细的信息

SQL> SELECT sid, serial#, username, oSUSEr, terminal,program ,action, prev_exec_start FROM v$session where sid = 2149;

发现有人用工具连接了该库

alter system kill session '2149,5834';		#执行删除

七、密码即将过期：ORA-28002: the password will expire within 7 days

查看默认过期时间为180天

select * from dba_profiles s where s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';

修改为永不过期

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
select * from dba_profiles s where s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';

修改过期用户的密码

alter user system identified by 123456;

重新登录不会再报密码过期
如果还想使用旧的密码，再将密码改成旧的即可

八、java启动失败，ORA-12519拒绝错误

参考第一个问题的解决方法，增大进程数
参考文章：ORA-12519错误解决方案 oracle

九、创建表空间报错“ORA-00059: maximum number of DB_FILES exceeded”

原因

db_files默认值为200，已经满了

sqlplus / as sysdba
SQL> select count(1) from dba_data_files;	#统计当前db_files的使用数量

解决

增大db_files的值

SQL> alter system set db_files=400 scope=spfile;
SQL> shutdown immediate;
SQL> startup;
SQL> show parameter db_files;	#查看