常见Oracle问题及解决方案

连接数设置、字符集、端口监听、日期格式 折叠源码

1. 用sys登录sql plus:
usename: sys as sysdba
password: ****
2. 查看最大连接数： select value from V$parameter where name='processes';
3. 查看当前连接数： select count (*) from V$process;
4. 把最大连接数改成300：alter system set processes=300 scope=spfile;
5. 重启DB: shutdown immediate;
startup;
 
端口监听
修改文件：%oracle_home%\NETWORK\ADMIN\listener.ora
新增host ： 主机名、ip
(ADDRESS = (PROTOCOL = TCP)(HOST = leiax)(PORT = 1521))
cmd中：
lsnrctl
然后可以进行监听的开启start，结束stop，状态查询status
修改文件后需要对监听进行重启，且需要一段时间才会生效

服务的创建：
sc delete OracleMTSRecoveryService
sc create OracleMTSRecoveryService binPath= "D:\javaSoft\oracle1\soft\bin\omtsreco.exe"

日期格式修改
查看日期格式：select sysdate from dual;
修改日期格式：alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
重启DB: shutdown immediate;
        startup;

oracle数据块损坏 折叠源码

检测方式：
    管理员登录sqlplus： 输入：show parameter db_block_check
         
        db_block_checking       逻辑一致性检查 默认不开启
        db_block_checksum       数据库对块的物理一致性检查    默认开启

oracle 将坏块分为物理和逻辑损坏两种，那么当oracle发现物理或者逻辑损坏之后是如何标记数据块从而使之后的操作知道该块是损坏块的那？
对于物理损坏，oracle不会进行任何的处理，在进行后续处理时oracle会重新计算checksum，只要发现checksum不一致则认为该块时物理损坏，并抛出01578错误。
对于逻辑损坏，当oracle第一次对数据块进行逻辑检测时，会抛出ora 600等错误，并修改数据块中的标记位，当下次访问该数据块时，oracle检测标志位，如果发现标志位以置为逻辑损坏，则抛出ora 01578错误。当使用DBMS_REPAIR对坏块进行修改时，如果时物理损坏不作任何处理，如果时逻辑损坏，修改数据块的标志位。
dbv工具检测文件：
cmd下执行 ：  dbv file=D:\javaSoft\oracle\oradata\orcl\SYSAUX01.DBF
 
rman工具：
cmd中输入： RMAN                    进入rman
rman连接数据库： connect target system/passwd
备份文件： backup validate datafile 6;
   
问题：
2017-09-11 21:22:51,208 INFO  [org.jboss.resource.adapter.jdbc.local.LocalConnection] Refactored SQLException:ORACLE:ORA-01578: ORACLE 数据块损坏 (文件号 66, 块号 362498)
ORA-01110: 数据文件 66: '/home/emsuep/aaa/XXXXXX.DBF'
;72000;1578;java.sql.SQLException: ORA-01578: ORACLE 数据块损坏 (文件号 66, 块号 362498)
ORA-01110: 数据文件 66: '/home/emsuep/aaa/XXXXXX.DBF'

错误：在 exp 时出现以下错误：
EXP-00056: 遇到 ORACLE 错误 1578
ORA-01578: ORACLE 数据块损坏（文件号4，块号65）
ORA-01110: 数据文件 4: ’E:\ORACLE\ORADATA\USERS.DBF’
 
措施：
-- 1. 检查损坏的对象
SELECT tablespace_name, segment_type, owner, segment_name
            FROM dba_extents
           WHERE file_id = 4
             and 65 between block_id AND block_id + blocks - 1;
-- 2. 设置内部事件，使exp跳过损坏的block
ALTER SYSTEM SET EVENTS=’10231 trace name context forever,level 10’ ; 
-- 3. 导出表
exp user1/passwd1 file=t1.dmp tables=t1
-- 4. 删除有坏块的表
drop table t1 purge;
-- 5. 导入表
imp user1/passwd1 file=t1.dmp tables=t1
-- 6. 清除跟踪事件
ALTER SYSTEM SET EVENTS=’10231 trace name context off’ ;
 
缺点：坏块处的数据会丢失

oracle表空间容量查询

1、查看所有表空间大小、剩余量：
select dbf.tablespace_name,
dbf.totalspace "总量(M)",
dbf.totalblocks as 总块数,
dfs.freespace "剩余总量(M)",
dfs.freeblocks "剩余块数",
(dfs.freespace / dbf.totalspace) * 100 "空闲比例"
from (select t.tablespace_name,
sum(t.bytes) / 1024 / 1024 totalspace,
sum(t.blocks) totalblocks
from dba_data_files t
group by t.tablespace_name) dbf,
(select tt.tablespace_name,
sum(tt.bytes) / 1024 / 1024 freespace,
sum(tt.blocks) freeblocks
from dba_free_space tt
group by tt.tablespace_name) dfs
where trim(dbf.tablespace_name) = trim(dfs.tablespace_name);
 
2、查看当前用户各表所占表空间
Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name
3.  使用如下语句查看一下谁在用临时段
SELECT se.username, se.SID, se.serial#, se.sql_address, se.machine, se.program, su.TABLESPACE,  su.segtype,  su.CONTENTS
FROM v$session se, v$sort_usage su
    WHERE se.saddr = su.session_addr;

连接数设置、字符集、端口监听、日期格式表被其他用户占用，处理方法

--用于查看数据库锁,诊断锁的来源及类型
select object_id,session_id,locked_mode from v$locked_object;
 
select b.owner,b.object_name,l.session_id,l.locked_mode from v$locked_object l, dba_objects b
    where b.object_id=l.object_id;
 
--找出数据库的serial#,以备杀死
select t2.username,t2.sid,t2.serial#,t2.logon_time from v$locked_object t1,v$session t2
    where t1.session_id=t2.sid order by t2.logon_time;
--杀死该session
alter system kill session 'sid,serial#';

触发器失效

Oracle数据库UserA用户权限丢失及触发器失效解决方法
 
停止EMS网管服务，保证EMS网管数据库没有被访问使用，然后执行如下步骤；
 
使用system用户登录数据库（使用sqlplus，PL/SQL Developer,SQL Developer等工具皆可），密码由现场人员输入，执行以下sql：
Select * from DBA_sys_privs where grantee='UserA';
查看UserA用户是否有如下第2步所示的所有权限，如果有则跳过第2步，若没有则继续第2步。
 
用system用户执行以下sql添加用户权限：
GRANT UNLIMITED TABLESPACE TO UserA;
GRANT CREATE SESSION TO UserA;
GRANT CREATE SEQUENCE TO UserA;
GRANT CREATE TRIGGER TO UserA;
GRANT CREATE CLUSTER TO UserA;
GRANT CREATE PROCEDURE TO UserA;
GRANT CREATE TYPE TO UserA;
GRANT CREATE OPERATOR TO UserA;
GRANT CREATE TABLE TO UserA;
GRANT CREATE INDEXTYPE TO UserA;
GRANT CREATE  VIEW TO UserA;
 
请用UserA/password登录数据库，执行以下查询：
Select OBJECT_NAME, STATUS
  From user_objects
 Where object_type = 'TRIGGER'
   and (object_name = 'AAA' OR object_name = 'BBB');
 
如果查询结果中，'AAA'或者object_name = 'BBB'为INVALID，则表示触发器已失效，继续执行以下检查步骤。否则忽略后续步骤。
 
仍旧用UserA/password执行以下查询：
SELECT sequence_name, LAST_NUMBER
FROM user_sequences
WHERE sequence_name = 'AAA'
or sequence_name = 'BBB';
如果查询结果没有'AAA'或者'BBB'，则表示序列已经丢失，触发器引用了序列，因此触发器也失效了，需要继续执行后续步骤；
如果查询结果中'AAA'和'BBB'均存在，则忽略后续步骤。
 
用UserA用户执行以下sql重建序列和触发器：
 CREATE SEQUENCE AAA
    INCREMENT BY 1
    START WITH 1
    MAXVALUE 99999 MINVALUE 1
    CYCLE
    CACHE 20
    ORDER;
CREATE OR REPLACE TRIGGER AAA BEFORE INSERT
    ON "AA"
    FOR EACH ROW
    DECLARE newID INTEGER;
BEGIN
    select AAA.nextval into newID
    from dual;
    :new.Id := newID;
END;
/
CREATE SEQUENCE BBB
    INCREMENT BY 1
    START WITH 1
    MAXVALUE 99999 MINVALUE 1
    CYCLE
    CACHE 20
    ORDER;
 
CREATE OR REPLACE TRIGGER BBB BEFORE INSERT
    ON "BB"
    FOR EACH ROW
    DECLARE newID INTEGER;
BEGIN
    select BBB.nextval into newID
    from dual;
    :new.Id := newID;
END;
/
重复步骤3，检查触发器状态是否已正常，如已经正常则执行下面的步骤，否则联系开发同事支持。
用UserA用户调用存储过程：
begin
ruleandboxidsequencematch;
end;
/
 
重复步骤4，记下序列的LAST_NUMBER值，再执行以下sql：
select max(id) from alarmrule;
select max(id) from alarmbox;
如果序列的当前值大于ID的最大值，就说明存储过程执行成功，至此问题解决。

oracle中查看bolb格式的字段,

pl/sql 中可以直接查询出结果
naviact的话: SELECT UTL_RAW.CAST_TO_VARCHAR2(field01) FROM AAA;
    或者 选中 blob字段，然后点击工具图标栏的 备注 按钮

oracel 数据库问题 ORA-12547 ORA-12571 解决记录

1. ORA-12547 ORA-12571
 
oracel 数据库问题 ORA-12547 ORA-12571 解决记录
Oracel 业务数据库近期频繁出现：ORA-12547：TNS：lost contact  和 ORA-12571：TNS：packet writer failure 错误
排除了端口及监听程序的问题，最终解决办法：
将Sqlnet.ora文件中的SQLNET.AUTHENTICATION_SERVICES= (NTS)修改为SQLNET.AUTHENTICATION_SERVICES= (NONE)
修改后上述错误解决，但又出现连接非常慢的问题，连接成功时间超过10分钟，
经过漫长的检查，发现是Oracle相关日志文件过大，导致后续日志信息写入时间过长导致，解决办法：
备份原日志文件，并清空日志文件，相关日志文件如下：
X:\app\Administrator\diag\tnslsnr\xx-datebase-1\listener\trace\listener.log
X:\app\Administrator\product\11.2.0\dbhome_2\oc4j\j2ee\OC4J_DBConsole_xx-datebase-1_orcl\log\em-application.log
其中xx-datebase-1代表数据库服务器名。
数据库问题的出现有很多原因，以上方法并不代表同样的问题在其他环境下有效。防止踩坑......
 
2. 连接oracle数据库出现oracle ORA-12526: TNS: 监听程序: 所有适用例程都处于受限
今天使用PL/SQL远程连接oracle数据库出现oracle ORA-12526: TNS: 监听程序: 所有适用例程都处于受限模式。
查了下原来之前改字符集时执行了：ALTER SYSTEM ENABLE RESTRICTED SESSION；
导致受限
解决办法：使用系统管理员身份运行以下一段代码
ALTER SYSTEM DISABLE RESTRICTED SESSION;

Oracle在更改机器名后服务无法启动的解决方法

Oracle改变机器名后会导致服务无法正常启动，可以通过下列操作解决：
1.oracle\product\10.2.0\db_3\network\ADMIN目录下，listener.ora文件中的LISTENER 改为你的新机器名，如果有域名的话加上域名；
tnsnames.ora中ORCL改为你的新机器名，如果有域名的话加上域名；
2.oracle\product\10.2.0\db_3\下有个以你”旧机器名_orcl"为文件名的文件夹，把旧机器名改为新的名字
3.oracle\product\10.2.0\db_3\oc4j\j2ee有个文件夹为OC4J_DBConsole_旧机器名_orcl，把旧机器名改掉
4.CMD下执行emca -reops recreate 重建EM资料库：
5.执行emca -config dbcontrol db
执行4,5时会让你填写：
SID:[即数据库名，默认orcl即可]
监听程序端口号：[默认填1521]
SYS,DBSNMP,SYSMAN的口令

oracle查询存储过程，视图，表的创建语句

查询存储过程：
select text from all_source where owner=user and name='SP_CREATEVIEW';
查询视图：
SET LONG 60000
select text from user_views where view_name = 'V_view_name';
在显示不完全的情况下添加：SET LONG 60000 语句

oracle删除

Windows 平台 
  因为oracle在Windows下的卸载颇有一些麻烦，如果不能完全卸载有可能影响将来的再次安装！常规卸载方法是运行Oracle的自带的卸载程序，可遗憾的是我在卸载时总不能完全卸载，当我再次安装Oracle时，就会出现莫名其妙的问题，并且这种卸载方式比较麻烦，比较慢，下面我介绍一种比较快并且能够彻底卸载Oracle的方法。     
1.关闭oracle所有的服务。可以在windows的服务管理器中关闭；
2.打开注册表：regedit 打开路径：  HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\  
删除该路径下的所有以oracle开始的服务名称，这个键是标识Oracle在windows下注册的各种服务！
3.打开注册表，找到路径：  HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE 
删除该oracle目录，该目录下注册着Oracle数据库的软件安装信息。
4.删除注册的oracle事件日志，打开注册表
 HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application  
删除注册表的以oracle开头的所有项目。 
5.删除环境变量path中关于oracle的内容。
鼠标右键右单击“我的电脑属性高级环境变量PATH 变量。   
删除Oracle在该值中的内容。注意:path中记录着一堆操作系统的目录，在windows中各个目录之间使用分号（; ）隔开的，删除时注意。
建议：删除PATH环境变量中关于Oracle的值时，将该值全部拷贝到文本器中，找到对应的Oracle的值，删除后，再拷贝修改的串，粘贴到PATH环境变量中，这样相对而言比较安全。 
 6.重新启动操作系统。 
以上1~5个步骤操作完毕后，重新启动操作系统。
7.重启操作系统后各种Oracle相关的进程都不会加载了。
这时删除Oracle_Home下的所有数据。（Oracle_Home指Oracle程序的安装目录） 
8.删除C:\Program Files下oracle目录。   （该目录视Oracle安装所在路径而定） 
9.删除开始菜单下oracle项，
如：  C:\Documents and Settings\All Users\「开始」菜单\程序\Oracle - Ora10g