解决ORA-00060: Deadlock detected小例 数据库版本：?12345678SQL > select * from v$version;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64biPL/SQL Release 10.2.0.5.0 - ProductionCORE 10.2.0.5.0 ProductionTNS for Linux: Version 10.2.0.5.0 - ProductionNLSRTL Version 10.2.0.5.0 - Production 事件：数据库产生死锁： ORA-00060: Deadlock detected alert  日志如下：?123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13794.trc.Wed Jul 10 12:39:00 CST 2013ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13792.trc.Wed Jul 10 12:40:02 CST 2013ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13794.trc.Wed Jul 10 12:41:56 CST 2013ORA-00060: Deadlock detected....

ORA-19527与ORA-00312和db_recovery_file_dest_size of 2048 MB is 0.00% usedErrors in file d:\oracle\product\10.2.0\admin\billing\bdump\billing_mrp0_2216.trc:ORA-19527: 必须重命名物理备用重做日志ORA-00312: 联机日志 1 线程 1: 'E:\CZ\BILLING\REDO01.LOG'Clearing online redo logfile 1 completeMedia Recovery Waiting for thread 1 sequence 13364Tue Aug 21 13:41:52  2012Completed: alter database recover managed standby database disconnect from sessionTue Aug 21 13:55:58  2012db_recovery_file_dest_size of 2048 MB is 0.00% used. This is auser-specified limit on the amount of space that will be used by thisdatabase for recovery-related files, and does not reflect the amount ofspace available in the underlying filesystem or ASM diskgroup.这是10g以后，oracle为了加快swtichover的速度，在can become a primary之前就去clear the online logfiles了，而如果没有设置log_file_name_convert，这个时候oracle可能就不认识哪怕是你copy过来的一模一样的logfile了   这个说法也在taobao dba team的blog中得到...

oracle10G/11G官方下载地址集合 直接迅雷下载 Oracle Database 11g Release 2 (11.2.0.1.0) for Microsoft Windows (64-bit)http://download.oracle.com/otn/nt/oracle11g/112010/win64_11gR2_database_1of2.ziphttp://download.oracle.com/otn/nt/oracle11g/112010/win64_11gR2_database_2of2.ziphttp://download.oracle.com/otn/nt/oracle11g/112010/win64_11gR2_client.ziphttp://download.oracle.com/otn/nt/oracle11g/112010/win64_11gR2_grid.zipOracle Database 11g Release 2 (11.2.0.1.0) for Microsoft Windows (32-bit)http://download.oracle.com/otn/nt/oracle11g/112010/win32_11gR2_database_1of2.ziphttp://download.oracle.com/otn/nt/oracle11g/112010/win32_11gR2_database_2of2.ziphttp://download.oracle.com/otn/nt/oracle11g/112010/win32_11gR2_client.zipOracle Database 11g Release 2 (11.2.0.1.0) for Linux x86http://download.oracle.com/otn/linux/oracle11g/R2/linux_11gR2_database_1of2.ziphttp://download.oracle.com/otn/linux/oracle11g/R2/linux_11gR2_database_2of2.ziphttp://download.orac...

数据库在非归档模式下丢失数据文件恢复  在非归档模式下，保证归档没有切换过就可以恢复具体如下：SQL> create tablespace test datafile '/app/oracle/oradata/ORCL/test01.dbf' size 50M;Tablespace created.SQL> archive log list;Database log mode              No Archive ModeAutomatic archival             DisabledArchive destination            /app/oracle/arch1Oldest online log sequence     9Current log sequence           11SQL> select * from v$version;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProdPL/SQL Release 10.2.0.1.0 - ProductionCORE    10.2.0.1.0      ProductionTNS for Linux: Version 10.2.0.1.0 - Productio...

向表中插入数据有很多办法,但是方法不同，性能差别很大.----1.原始语句 drop table t1 purge;create table t1 ( sid number, sname varchar2(20) ) tablespace test; create or replace procedure proc01 ( sname varchar2 ) as begin for i in 1..10000000 loop execute immediate 'insert into t1(sid,sname) values('||i||','''||sname||''')'; commit; end loop; end; / alter system flush shared_pool;set timing on;exec proc01('ocpyangtest');已用时间: 02: 02: 54.12  ----2.绑定变量 drop table t1 purge;create table t1 ( sid number, sname varchar2(20) ) tablespace test;create or replace procedure proc02 ( sname varchar2 ) as begin for i in 1..10000000 loop execute immediate 'insert into t1(sid,sname) values(:no'||','''||sname||''')' using i; commit; end loop; end; / alter system flush shared_pool;set timing on;exec proc02('ocpyangtest');已用时间: 00: 22: 59.79select count(*) from t1;----3.静态语句 drop table t1 purge;create table t1 ( sid number, sname varchar2(20) ) tablespace ...

故障现象：(1)问题开始时间是20130918 22:40 从20次每秒,增加加0919号的00:20分的2500次每秒。(2)应用程序插入SQL执行失败，程序缓冲池满了，由于插入失败，日志表中没有数据故障分析：（1）查看等待事件，看到SID 980和SID 1063 为阻塞源，阻塞了大量session，而1063的阻塞源又是980，所980是根源。        18-9月 -13 10.05.09.517 下午        980        p49fnjdb        fcfzp8zgxx4gx   Wait for shrink lock          oracle@p49fnjdb (J002)        UNKNOWN        18-9月 -13 10.11.20.224 下午        1063        njdmweb1        gs9qxx1pbvuw2        row cache lock   JDBC Thin Client        VALID        980（2）对应SQL_ID为如下语句alter index "CPE"."CPE_ACS_LOCALE" modify partition "P_CPE_ACTION_STATUS_2013...

说明：Oracle数据库服务器操作系统：CentOSIP：192.168.0.198端口：1521SID：orclOracle数据库版本：Oracle11gR2具体操作：1、root用户登录服务器mkdir -p /backup/oracledata  #新建Oracle数据库备份目录chown -R  oracle:oinstall /backup/oracledata -R #设置目录权限为oinstall用户组的oracle用户（用户oracle与用户组oinstall是在安装Oracle数据库时设置的）2、新建Oracle数据库备份脚本vi /backup/oracledata/ordatabak.sh  #新建文件，输入以下代码#!/bin/shexport ORACLE_BASE=/data/oracleexport ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1export ORACLE_SID=orclexport ORACLE_TERM=xtermexport PATH=$ORACLE_HOME/bin:/usr/sbin:$PATHexport LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/libexport LANG=Cexport NLS_LANG=AMERICAN_AMERICA.ZHS16GBK#以上代码为Oracle数据库运行账号oracle的系统环境变量设置，必须添加，否则crontab任务计划不能执行。# oracle用户的系统环境变量路径：/home/oracle/.bash_profiledate=date +%Y_%m_%d   #获取系统当前日期时间days=7  #设置删除7天之前的备份文件orsid=192.168.0.198:1521/or...

oracle 中 not 运算符使用方法

Oracle 10G 测试move与shrink 测试两个表 gps_ht_9612与 gps_ht_9613 两表并未建索引。SQL> select * from gps_sq where sq<11;        SQ TABLE_NAME---------- ------------------------------         1 GPS_HT_9610         2 GPS_HT_9611         3 GPS_HT_9612         4 GPS_HT_9613         5 GPS_HT_9614         6 GPS_HT_9615         7 GPS_HT_9616         8 GPS_HT_9617         9 GPS_HT_9618        10 GPS_HT_961910 rows selectedExecuted in 0.113 seconds清空两表内容SQL> truncate table gps_ht_9613;Table truncatedExecuted in 1.412 secondsSQL> truncate table gps_ht_9612...

查看处于被锁状态的表：v$locked_object dba_objects v$session all_objects v$sqlarea v$lock--1.查看处于被锁状态的表SELECT a.object_id,       a.session_id,       b.object_name  FROM v$locked_object a,       dba_objects     b WHERE a.object_id = b.object_id--2.查出锁定object的session的信息以及被锁定的object名SELECT l.session_id sid,       s.serial#,       l.locked_mode,       l.oracle_username,       l.os_user_name,       s.machine,       s.terminal,       o.object_name,       s.logon_time  FROM v$locked_object l,       all_objects     o,   &nb...