文章目录
一、解决ORA-00020:maximum number of processes (150) exceeded 错误
杀死所有oracle进程
su - oracleps -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 sysdbaSQL> 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 sysdbaSQL> 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; #查看
