首页 / ORACLE / Oracle常见故障问题
Oracle常见故障问题
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Oracle常见故障问题,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含4535字,纯文字阅读大概需要7分钟。
内容图文
1. ORA-27102: out of memory
创建pfile文件:
create pfile from spfile;
修改pfile文件
修改文件/home/oracle/app/oracle/product/10g/dbs/initradius.ora
pga,sga 大小
使用pfile来启动数据库
startup pfile=‘/home/oracle/app/oracle/product/10g/dbs/initradius.ora‘;
从spfile同步pfile文件,
create spfile from pfile;
然后切换到root用户重启数据库
2.无法使用上下左右按键
yum -y install readline-devel ncurses-devel libtool*
下载解压安装
http://down1.chinaunix.net/distfiles/rlwrap-0.24.tar.gz
./configure make && make install
vim /etc/profile
aliase sqlplus=‘rlwrap sqlplus‘
3.打包
tar -jvcf oracle_mydradius.tar.bz2 /etc/ora* /etc/sysctl.conf /etc/security/limits.conf /etc/pam.d/login /etc/passwd /etc/shadow /etc/group /etc/profile /home/oracle/ /etc/init.d/ora10g /ora /etc/hosts /usr/local/rlwrap
4.CPU使用率高的查询方法
pid 735
SQL> select s.sid from v$process p,v$session s where s.paddr=p.addr and p.spid = 735;
SID
----------
149
SELECT SQL_TEXT from V$SQLTEXT_WITH_NEWLINES where HASH_VALUE = (select sql_hash_value from v$session where SID = 149) ;
SQL> SELECT SQL_TEXT from V$SQLTEXT_WITH_NEWLINES where HASH_VALUE = (select sql_hash_value from v$session where SID = 149) ;
SQL_TEXT
----------------------------------------------------------------
TimeByUserAgent(o.scalledno) = -1
IS NOT NULL AND dExpireDate <= SYSDATE) )) AND pkg_util.hitFree
SYSDATE AND (iBillType = 0 OR iBillType = 7) ) OR (dExpireDate
ain, iCurrFlow) = 0) OR (dOverDate IS NOT NULL AND dOverDate <=
er.sp_flowUserFlowRemain(sUserName, iBillType, iIspID, fMoneyRem
pID, iCurrFlow) = 0) OR (iBillType = 2 AND iType !=3 AND pkg_us
pkg_user.sp_userFlowRemain(sUserName, dFirstDate, iBillType, iIs
ctets + o.fOutputMOctets ) OR (iBillType = 0 AND iType !=3 AND
OR (iBillType = 5 AND iType !=3 AND fMoneyRemain <= o.fInputMO
Users WHERE ( (iStatus <= 0 AND iStatus != -4)
lineRecord o WHERE sUserName IN ( SELECT sUserName FROM tbl_
SQL_TEXT
----------------------------------------------------------------
medIP, pkg_util.iip_to_aip(iFramedIP) AS aFramedIP FROM tbl_On
iNasIP) AS cNasIP, pkg_util.iip_to_cip(iFramedIP) AS cFra
SELECT iNasPort, sUserName, sSessionID, pkg_util.iip_to_cip(
14 rows selected.
----------------
可以直接使用
SELECT sql_text FROM v$sqltext a WHERE (a.hash_value, a.address) IN (SELECT decode(sql_hash_value, 0, prev_hash_value, sql_hash_value), decode(sql_hash_value, 0, prev_sql_addr, sql_address) FROM v$session b WHERE b.paddr = (SELECT addr FROM v$process c WHERE c.spid = ‘3127‘)) ORDER BY piece ASC;
----------------
5.11G密码有效期问题
Oracle 11G需要修改
设置密码永不过期
Oracle11g R2数据库提示ORA-28002: the password will expire within 5 days,
SELECT * FROM dba_profiles s WHERE s.profile=‘DEFAULT‘ AND resource_name=‘PASSWORD_LIFE_TIME‘; //查看有效期
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED; //设置密码永不失效
alter user joffice identified by joffice; //修改密码
6.无法删除一个用户
1.无法删除一个用户
ORA-24170: MYDRADIUS.PRIMARY_QUEUE_R is created by AQ, cannot be dropped
方法:
ALTER SESSION SET EVENTS = ‘25475 TRACE NAME CONTEXT FOREVER, LEVEL 2‘;
drop user mydradius cascade;
ORA-00604: error occurred at recursive SQL level 1
方法:
alter session set events‘10851 trace name context forever,level 1‘ ;
7.修改字符集方法
查看oracle的字符集
select userenv(‘language‘) from dual;
结果:AMERICAN _ AMERICA. ZHS16GBK
修改字符集:
shutdown immediate;
startup mount;
alter system enable restricted session;
alter system set job_queue_processes=0;
alter database open;
alter database character set internal_use US7ASCII;
alter database character set internal_use zhs16gbk;
shutdown immediate;
startup;
8.查看utf-8出现乱码
NLS_LANG="SIMPLIFIED CHINESE_CHINA.utf8"; export NLS_LANG
添加次环境变量
9.查看连接数和会话数并修改
select count(*),program, machine,osuser from v$session group by program,machine,osuser having count(*) >1;
各个客户端当前连接数
select count(0) from v$process;
当前连接数
select count(*) from v$session;
当前会话数
select value from v$parameter where name=‘processes‘;
show parameter process
系统设置的最大连接数
show parameter session
系统设置的最大会话数
alter system set processes=500 scope=spfile;
修改最大连接数
alter system set sessions=500 scope=spfile;
修改最大会话数
连接和会话修改后必须重启才能生效,他们之间的关系 sessions=(1.1 * processes + 5)
10.修改可使用内存SGA和链接使用内存PGA
SQL> show parameter sga_ //查看内存分配
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 1120M
sga_target big integer 1120M
alter system set sga_max_size=1024M scope=spfile;
alter system set sga_target=1024M scope=spfile; 无法动态修改,需要重启oracle
11.Oracle使用sql文件导入数据的乱码
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
加入环境变量 /etc/profile
原文:http://www.cnblogs.com/djoker/p/6373239.html
内容总结
以上是互联网集市为您收集整理的Oracle常见故障问题全部内容,希望文章能够帮你解决Oracle常见故障问题所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。