ITPub博客

Remark Set SQL*Plus variables to manipulate outputset feedback off heading off verify off trimspool offset pagesize 0 linesize 200set sqlterminator '/'Remark Set SQL*Plus user variables used in this scriptdefine movetablespace = 'movetbs.sql'define usrlist = '''SCOTT'',''ESYS'''prompt *** Spooling to &movetablespace.Remark 创建移动表空间的脚本spool &movetablespaceSELECT 'ALTER TABLE ' || A.OWNER || '.' || A.TABLE_NAME ||' MOVE TABLESPACE ' || B.DEFAULT_TABLESPACE || ';' AS CMDFROM (SELEC

oracle数据库是当前应用最广泛的大型数据库之一，而其性优化直接关系到系统的运行效率。本文以数据库性能优化的基本原则为出发点，阐述了在数据库设计阶段如何避免竞争和如何优化数据访问，在数据库运行阶段如何从操作系统和数据库实例级别上调整内存和i/o来达到数据库性能优化的各种技术。 1、引言 随着网络应用和电子商务的不断发展，各个站点的访问量越来越大，数据库规模也随之不断的扩大，数据库系统的性能问题就越来越突出，因此，如何对数据库进行调优至关重要：如何使用有限的计算机系统资源为更多的用户服务？如何保证用户

如何发现行迁移问题？方法一：1、@%ORACLE_HOME%/rdbms/admin/utlchain.sql2、ANALYZE TABLE TABLE_NAME LIST CHAINED ROWS;3、SELECT * FROM CHAINED_ROWS WHERE TABLE_NAME='TABLE_NAME';方法二：从视图V$SYSSTAT中，table fetch contined row也可以检查行迁移问题解决：为了避免行迁移现象，可以通过设置PCTFREE（为更新一个块所保留的空间）和数据库大小来解决。[@more@]

经常有人问temp表空间暴涨的问题,以及如何回收临时表空间,由于版本的不同, 方法显然也多种多样,但这些方法显示是治标不治本的办法,只有深刻理解temp 表空间快速增加的原因,才能从根本上解决temp ts的问题。 是什么操作在使用temp ts? - 索引创建或重创建. - ORDER BY or GROUP BY - DISTINCT 操作. - UNION & INTERSECT & MINUS - Sort-Merge joins. - Analyze 操作 - 有些异常将会引起temp暴涨 [@more@]所以,在处理以上操作时,dba需要加倍关注temp的使用情况,v$sort_segment字典可以记载temp的比较详细的使用情况,而v$sort_usage

通过以下例子可以明白在为什么显式游标好像比隐式游标运行得更快了。当使用显式游标进行测试的时候，只取一次数据X＝1，为了找到答案，查询只需要扫描非常少的块（很少的一致的读次数）。然而，只要使显式游标来进行隐式游标的工作，检查确保没有其他记录满足同一条件，你就会看到显式游标检查表中的每一个块。现在，接着说隐式游标，通过使用ROWNUM=1看看它是否也会在找到第一条符合条件的记录时停下来，它和显式游标做相同的工作量。当它检查表中的第二行是否符合条件时，你会看到它同显式游标一样进行相同次数的一致读；它也不得不对表

测试脚本在SCOTT用户下运行[@more@]set serveroutput ondrop table emp_bak;create table emp_bak(empno number(4),ename varchar2(10),note varchar2(30));drop procedure bulktest;create or replace procedure bulktest is type NumTab is table of emp.empno%type; type NameTab is table of emp.ename%type; --type NumTab is table of emp.empno%type index by binary_integer; --type NameTab is table of emp.ename%type index by binary_integer; enums NumTab; names NameTab; cursor c is select empno,ename from emp;

CREATE OR REPLACE PROCEDURE p_test(a IN NUMBER,b IN NUMBER) IS PROCEDURE p_sub(c IN NUMBER) IS BEGIN DBMS_OUTPUT.PUT_LINE('a='||a); DBMS_OUTPUT.PUT_LINE('b='||b); DBMS_OUTPUT.PUT_LINE('c='||c); END;BEGIN p_sub(3);END p_test;/SET SERVEROUTPUT ONEXEC p_test(1,2);DROP PROCEDURE p_test;[@more@]

SET SERVEROUTPUT ONDECLARE TYPE T_ARRAY IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER; T T_ARRAY;BEGIN T(1) := 'A'; T(2) := 'B'; T(3) := 'C'; FOR I IN 1..T.COUNT LOOP DBMS_OUTPUT.PUT_LINE(T(I)); END LOOP;END;/这是实现数组的test，还可以结合type record来实现多维数组。[@more@]

Logminer是每个Dba都应熟悉的工具，当一天由于用户的误操作你需要做不完全的恢复时，由于你无法确定这个操作是哪个时间做的，所以这对你的恢复就带来的很大的难度，丢失的数据也不能完全恢复回来。而LogMiner就可以帮你确定这个误操作的准确时间。我的测试环境是Aix4.3.3的操作系统，Oracle9.2.0.3的测试库。[@more@]一、LogMiner的几点说明1、LogMiner可以帮你确定在某段时间所发的各种DML，DDL操作的具体时间和SCN号，它所依据的是归档日志文件及联机日志文件。2、它只能在Oracle8i及以后的版本中使用，不过它可以分析Oracle8的日志。3

SET SERVEROUTPUT ONDECLARE TYPE TCUR IS REF CURSOR; CUR TCUR; AC_WHERE VARCHAR2(100); AC VARCHAR2(100); BEGIN AC_WHERE := '(31044,31048)'; OPEN CUR FOR 'SELECT OBJECT_ID FROM USER_OBJECTS WHERE OBJECT_ID IN '|| AC_WHERE; LOOP FETCH CUR INTO AC; EXIT WHEN CUR%NOTFOUND; DBMS_OUTPUT.PUT_LINE(AC); END LOOP; CLOSE CUR;END;/[@more@]

从系统视图中生成存储过程的单元测试脚本（供参考）[@more@]create or replace function f_get_proc_param(obj_name in varchar2) return varchar2 is res varchar2(4000); cnt number default 0;begin res := res || 'PROMPT **********************************************' || chr(10); res := res || 'PROMPT Test ' || obj_name || chr(10); res := res || 'PROMPT **********************************************' || chr(10); res := res || 'PROMPT 模拟数据语句块，请在此处添加SQL' || chr(10); res := res || 'PROMP

树的层次体现的sql方案[@more@]SQL> select * from test; ID PARENT_ID---------- ---------- 1 0 2 1 3 2 11 0 22 11 33 22 4 2 44 228 rows selected要得到如下的结果： ID PARENT_ID 层次---------- ---------- ---- 1 0 0 2 1 1 3 2 2 4 2 2 11 0 0 22

bitand(x,y)通过位与操作可以推出以下操作：位或操作＝(x + y) - bitand(x,y)位异或操作＝(x + y) - bitand(x,y) * 2[@more@]

稍加整理的比较常用的sql列表[@more@]--监控索引是否使用alter index &index_name monitoring usage;alter index &index_name nomonitoring usage;select * from v$object_usage where index_name = &index_name;--求数据文件的I/O分布select df.name,phyrds,phywrts,phyblkrd,phyblkwrt,singleblkrds,readtim,writetim from v$filestat fs,v$dbfile df where fs.file#=df.file# order by df.name;--求某个隐藏参数的值col ksppinm format a54col ksppstvl format a54select ksppinm, ksppstvl from x$ksppi pi, x$ksppcv cv

适用于windows操作系统，如果是unix系统需修改。[@more@]set feedback off set linesize 300set pagesize 3000set serveroutput onspool d:deletefile.cmddeclarefilepath varchar2(100) ;beginfilepath := '&1' ;for rec in (select thread#,sequence# from v$log_history where trunc(first_time) = trunc(sysdate - 1)and sequence# > 991)loopdbms_output.put_line('del ' || filepath || '' || rec.thread# || '_' || rec.sequence# || '.dbf') ; end loop ;end ;/spool offset feedback onset linesize 80set pagesize 14$d:

得到对表操作的脚本（可作为参考）[@more@]--使约束失效的脚本set feedback offset pagesize 0spool d:disableconstraint.sqlselect 'alter table ' || table_name || chr(10) || ' disable constraint ' ||constraint_name || ';' objfrom user_constraintswhere constraint_type in ('R');spool off--启用约束的脚本spool d:enableconstraint.sqlselect 'alter table ' || table_name || chr(10) || ' enable constraint ' ||constraint_name || ';' objfrom user_constraintswhere constraint_type in ('R');spool off--清空表的脚本sp

-- createoutln.sql的详细脚本[@more@]set serveroutput on declare user_exists exception;outln_user number;outln_tables number;extra_outln_tables number;ddl_cursor integer;beginselect count(*) into outln_user from user$ where name = 'OUTLN';select count(*)into outln_tablesfrom obj$where name in ('OL$', 'OL$HINTS') andowner# = (select user# from user$ where name = 'OUTLN');select count(*)into extra_outln_tablesfrom obj$where name not in ('OL$', 'OL$HINTS') and type# = 2 andowner# = (select user# fro

一个测试数组用法的例子[@more@]create or replace package pk_test istype array_test is varray(3) of integer;end pk_test;/create or replace procedure test_array(aa in pk_test.array_test)isbeginfor i in 1..3loopdbms_output.put_line(aa(i)); end loop; end;/set serveroutput ondeclarea pk_test.array_test;begina := pk_test.array_test();a.extend(3);a(1) := 1;a(2) := 2;a(3) := 3; test_array(a);end;/ SQL> 123PL/SQL procedure successfully completed

sqlplus-help[@more@]SQL> helpHELP----Accesses this command line help system. Enter HELP INDEX for a listof topics.In iSQL*Plus, click the Help button to display iSQL*Plus help.HELP [topic]SQL> help indexEnter Help [topic] for help.@ COPY PAUSE SHUTDOWN@@ DEFINE PRINT SPOOL/ DEL PROMPT SQLPLUSACCEPT DESCRIBE QUIT STARTAPPEND DISCONNECT RECOVER STARTUPARCHIVE LOG EDIT REMARK STOREATTRIBUTE EXECUTE REPFOOTER TIMINGBREAK EXIT REPHEADER TTITLEBTITLE GET RESERVED WORDS (SQL) UNDEFINECHANGE HELP

一直用8i，最近接触9i。好多新东西都不是很了解。nvl2(a,b,c)当a为空时返回c，否则返回b。SQL> select nvl2(1,'a','aa') from dual;NVL2(1,'A','AA')----------------aSQL> select nvl2(null,'a','aa') from dual;NVL2(NULL,'A','AA')-------------------aa好，现在可以不用把decode和nvl结合起来用了。呵呵[@more@]