ITPub博客

http://blog.itpub.net/post/860/39132[@more@]

绑定变量[@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