ITPub博客

封锁测试[@more@]10:16:39 scott@bus> select * from v$version10:16:54 2 ;BANNER================================================================Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionPL/SQL Release 9.2.0.4.0 - ProductionCORE 9.2.0.3.0 ProductionTNS for 32-bit Windows: Version 9.2.0.4.0 - ProductionNLSRTL Version 9.2.0.4.0 - Production已用时间: 00: 00: 00.00一、TX事务锁1) 在一个session中执行update操作，sid=920:59:29 scott@bus# update dept set deptno = deptno + 10;已更新4行。已用

rman技术速查手册[@more@]发表人:yaanzy | 发表时间: 2005年四月05日, 14:431、配置catalog恢复目录create tablespace ts_rman datafile='d:oratestrman.dbf' size=100M;create user rman identified by rman default tablespace ts_rman;grant connect ,resource , RECOVERY_CATALOG_OWNER to rman;连接到目标数据库和CATALOG数据库rman target / catalog rman/rman@catalog_tnsname创建CATALOG用户的表rman>create catalog登记目标数据库：rman>register database解除登记数据库(在恢复目录数据库上执行)SQL> select db_key,db

得到索引名称列表以及对应的表名和列名,该脚本稍加修改就可以得到索引相关的脚本，供参考[@more@]create or replace function fn_getindexcols(iv_indexname varchar2,iv_tablename varchar2)return varchar2 isresult varchar2(1000); beginfor rec in (select column_name,descend,column_positionfrom user_ind_columnswhere index_name = upper(iv_indexname)and table_name = upper(iv_tablename)order by column_position asc)loopif rec.column_position = 1 thenresult := lower(rec.column_name);elseresult := result || ',' ||

绑定变量[@more@]刷新共享池alter system flush shared_pool;使用常量编码declaretype rc is ref cursor;l_rc rc;l_dummy all_objects.object_name%type;l_start number default dbms_utility.get_time;beginfor i in 1 .. 1000loopopen l_rc for 'select object_name from all_objects where object_id='||i;fetch l_rc into l_dummy;close l_rc;end loop;dbms_output.put_line(round((dbms_utility.get_time-l_start)/100,2)||'seconds...');end;/使用绑定变量declaretype rc is ref cursor;l_rc rc;l_dummy all_objects.object_name%ty

oracle trace文件解析[@more@]*** 2006-02-23 22:11:45.000=====================PARSING IN CURSOR #1 len=68 dep=0 uid=59 oct=42 lid=59 tim=12047812267 hv=570864185 ad='66bd0bc4'alter session set events '10046 trace name context forever,level 12'END OF STMTEXEC #1:c=0,e=89,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=12047812255WAIT #1: nam='SQL*Net message to client' ela= 5 p1=1111838976 p2=1 p3=0WAIT #1: nam='SQL*Net message from client' ela= 3350384 p1=1111838976 p2=1 p3=0=====================PARSING IN

ORACLE 动态语句的笔记[@more@]-- 动态SQL例子create or replace procedure DynEmpProc(p_job in varchar2)astype refcursor is ref cursor;-- We must allocate our own host-- variables and resources using dynamic sql.l_cursor refcursor;l_ename emp.ename%type;begin-- We start by parsing the queryopen l_cursor for 'select ename from emp where job = :x' using in p_job;loop-- and explicitly FETCHING from the cursor.fetch l_cursor into l_ename;-- We have to do all error handling-- and processing logic ourselv

关闭数据库的备份与恢复[@more@]一致数据库备份通常指冷备份。4.1 还原整个数据库1、备份关闭的数据库1) 创建备份脚本--closed_backup.sqlRemark Set SQL*Plus variables to manipulate outputset feedback off heading off verify off trimspool offset pagesize 0 linesize 200Remark Set SQL*Plus user variables used in this scriptdefine dir = 'd:databakpractice_new'define fil = 'e:caiskyora-srtclosed_backup_commands.sql'prompt *** Spooling to &filRemark Create a command file with file backup commandsspool &amp

其它恢复案例[@more@]第五章 其它恢复案例5.1 损坏联机日志的恢复方法5.1.1 损坏非当前联机日志大家都清楚，联机日志分为当前联机日志和非当前联机日志，非当前联机日志的损坏是比较简单的，一般通过clear命令就可以解决问题。1、启动数据库，遇到ORA-00312 or ORA-00313错误，如ORA-00313: open failed for members of log group 1 of thread 1ORA-00312: online log 1 thread 1: 'D:ORACLEORADATATESTREDO01.LOG'从这里我们知道日志组1的数据文件损坏了从报警文件可以看到更详细的信息2、查看V$log视图SQL> select group#,sequence#,

以前用的自动备份脚本[@more@]-- 得到创建exp参数文件的脚本set pagesize 0set feedback offset head offset echo offspool d:partition_backupexp_tables_partitions.sqlselect 'set head off' || chr(10) ||'spool d:partition_backupexp_' || lower(segment_name) || '_' || lower(partition_name) || '.parfile' || chr(10) || 'select ''' ||'userid=usr/pwd@sid' || chr(10) || 'buffer=31457280' || chr(10) ||'tables=' || lower(owner) || '.' || lower(segment_name) || ':' || lower(partition_name) || chr(10) ||'file=d:par