Oracle常见故障问题
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
