2012年12月_duqiangatom

原创 Linux核心参数Shmmax,shmall,shmni

Linux 下核心参数调整kernel.shmmaxshmmax是核心参数中最重要的参数之一，用于定义单个共享内存段的最大值，shmmax设置应足够大，能在一个共享内存段下容纳下整个的SGA，设置的过低可能会导致需要创建多个共享内存段，可能导致系统性能的下降。Oralce建议一个大的共享内存段能容纳整个SGA，这样在任何时候都不会有性能下降的隐患。Oracle安装文档建议，32位

2012-12-31 10:49:24 1606

原创统计信息的收集

1、STATISTICS：优化统计信息是为了更详细的描述数据库及数据库对象而收集的数据，这些信息被用于为sql语句选择最优的执行计划。优化统计信息包括的项有：　　Table statistics（表统计信息）：Number of rows、Number of blocks、Average row length；　　Column statistics（列统计信息）：Numb

2012-12-25 21:50:21 709

原创表连接方式及使用场合

NESTED LOOP 对于被连接的数据子集较小的情况，nested loop连接是个较好的选择。nested loop就是扫描一个表，每读到一条记录，就根据索引去另一个表里面查找，没有索引一般就不会是 nested loops。一般在nested loop中，驱动表满足条件结果集不大，被驱动表的连接字段要有索引，这样就走nstedloop。如果驱动表返回记录太多，就不适合nest

2012-12-25 21:47:59 390

原创使用stored_outline优化、稳定执行计划

1、打开一个session，设置自动创建sql的stored_outline alter session set create_stored_outlines = demo;2、抓取性能不好的sql，例如（假设nest loop 比较优）：SELECT /*+ use_hash(a,b)*/* FROM game_draw_stats_site_sale a ,game_dr

2012-12-25 21:47:00 394

原创查看执行计划3种方式

--1 生成并查看planSELECT * FROM PLAN_TABLE;DELETE FROM plan_table;EXPLAIN PLAN FOR SELECT * FROM game_draw WHERE draw_id = 33;select * FROM TABLE(dbms_xplan.display);--2 from libary cache v$sql_p

2012-12-25 21:45:02 381

原创 sql tuning一个例子

1、sqlplus中 set autotrace traceonly statistics2、查看一致读、物理读排序等谢谢（执行2遍）如下：方案1：HELIOS @ db-1:1521/cslyn >SELECT/*+ use_hash (c b)*/ post_balance + nvl(deal_amount, 0) 2 FROM (SELECT d.site_

2012-12-25 21:43:51 380

原创触发器用法总结

触发器是特定事件出现的时候，自动执行的代码块。类似于存储过程，但是用户不能直接调用他们。功能： 1、允许/限制对表的修改 2、自动生成派生列，比如自增字段 3、强制数据一致性 4、提供审计和日志记录 5、防止无效的事务处理 6、启用复杂的业务逻辑开始 create trigger biufer_employees_depa

2012-12-25 21:26:11 643

原创 decode函数用法

decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)该函数的含义如下：IF 条件=值1 THEN　　　　RETURN(翻译值1)ELSIF 条件=值2 THEN　　　　RETURN(翻译值2)　　　　......ELSIF 条件=值n THEN　　　　RETURN(翻译值n)ELSE　　　　RETURN(缺省值)EN

2012-12-25 21:21:49 3463

原创 rank/row_number() OVER ()使用

row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的).　　与rownum的区别在于：使用rownum进行排序的时候是先对结果集加入伪列rownum然后再进行排序，而此函数在包含排序从句后是先排序再计算行号码．　　row_n

2012-12-25 21:20:00 579

原创 pl/sql 编程方面积累

1.自治事务:8i以上版本，不影响主事务。在存储过程的is\as后面声明PRAGMA AUTONOMOUS_TRANSACTION; 自治事务防止嵌套提交，使事务在自己的事务区内提交或回滚不会影响其他的事务。2、包包说明(package specification)，包头,存放关于包的内容的信息，定义包的用户可见的过程、函数，数据类型和变量 crea

2012-12-25 21:17:16 369

原创 sql-plus+pl/sql-dev制造报表技巧

1、sql-plus: 1）1.sql内容如下：set linesize 100 set term off verify off feedback off pagesize 999 set markup html on entmap ON spool on preformat offspool 1.xls(HTM) REPLACE/APPEND/CREATE se

2012-12-25 21:12:34 455

原创 oralce集合的function总结

集合方法pl/sql预定义了在varray 和嵌套表实例上进行调用的方法。这些方法能在集合上执行一定的功能。EXISTS该函数返回集合中第一个元素的索引，如果集合为空，返回NULLCollection.EXISTS(index)COUNT该函数集合元素的数目Collection.

2012-12-25 21:10:45 401

原创绑定变量(bind variables)、替换变量(substitution variables)

1、替换变量（主要用来 sql、plsql与用户的交互，可以运行时输入、也可提前输入）注意：只替换变量，如果是字符，请放在单引号内，如‘&1’； a 、使用环境 sql语句中：如 select * from site where site_no='&1'; pl/sql块中使用，如： DEC

2012-12-24 18:57:31 1278

原创 PL/SQL异常处理总结

一、break(即退出循环体)可用exit代替。 loop ... exit when ...; ... end loop; 二、continue（退出单次循环）用自定义异常代替。 loop begin ... raise my_ex; ... exception when others then null; end; end loop;

2012-12-24 18:54:59 814

原创 oracle cursor游标总结

游标（Cursor）：用来查询数据库，获取记录集合（结果集）的指针，可以让开发者一次访问一行结果集，在每条结果集上作操作。游标可分为：1.静态游标：分为显式（explicit）游标和隐式（implicit）游标。2.REF游标：是一种引用类型，类似于指针。 1、静态游标1.1显式游标定义格式： CURSOR 游标名 ( 参数 ) ISSelect 语句

2012-12-24 18:46:54 813

原创 oracle日期格式杂烩

注意（关于IW计算周数）：1、如果一年当中第52周别之后至当年的12月31日之间，还有大于或等于4天的话，则定为当年的第53周，否则剩余这些天数被归为下一年的第1周；2、如果在不足52周别的话，则以下一年的时间来补；3、每周固定的从周一开始作为本周的第1天，到周日作为本周的第7天；1、关于to_char格式'W','WW','IW' a 、SELECT TO_CHAR(

2012-12-24 18:39:15 405

原创定语复合分区表的2种语法

1、在每个分区中嵌套子分区：create table ptest(id int ,name varchar2(30),gender varchar2(1))partition by range(id)subpartition by list(gender)(partition p1 values less than(10)(subpartition p1_1 values

2012-12-24 18:37:40 393

原创 FORALL

DECLARE TYPE char_rec IS TABLE OF VARCHAR2(5) INDEX BY PLS_INTEGER; TYPE pls_rec IS TABLE OF NUMBER INDEX BY PLS_INTEGER; siteno_rec char_rec; pct_rec pls_rec;BEGIN FOR i IN 1 .. 20 L

2012-12-24 18:35:24 403

原创 2种Global临时表

1）会话级的临时表因为这这个临时表中的数据和你的当前会话有关系，当你当前SESSION不退出的情况下，临时表中的数据就还存在，而当你退出当前SESSION的时候，临时表中的数据就全部没有了，当然这个时候你如果以另外一个SESSION登陆的时候是看不到另外一个SESSION中插入到临时表中的数据的。即两个不同的SESSION所插入的数据是互不相干的。当某一个SESSION退出之后临时表中的数据就被

2012-12-24 18:34:24 555

原创 level、connect by、rownum使用

--1、start with + connect by -->一般用于构成树查询--eg:SELECT * FROM district WHERE del_flag = 0 AND (district_level_code IN (SELECT DISTINCT domain_key FROM domain_district_level) OR district_level_

2012-12-24 18:28:07 701

原创行列变换、PL/SQL游标加锁

A、行列变化 1、建立测试数据：SQL> create table t(name varchar2(10),type number,phone number);SQL> insert into t values('duqiang',1,09176...22); SQL> insert into t values('duqiang',2,0106....22);S

2012-12-24 18:26:54 364

原创三种办法将普通表改为分区表

Oracle的普通表没有办法通过修改属性的方式直接转化为分区表，必须通过重建的方式进行转变，下面介绍三种效率比较高的方法，并说明它们各自的特点。方法一：利用原表重建分区表。步骤： SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE); 表已创建。 SQL> INSERT INTO T SELECT ROWNUM,

2012-12-24 18:20:56 2194

原创游标常量+游标变量的使用

游标打开的瞬间（尤其是游标定义中含有变量时，只使用打开时的值），已经决定了游标的内容！！！fetch 取不到值时，不异常，只是使notfound值为false而已。游标变量如果有类型返回要求（return）时，不允许使用动态sql。因为它要验证类型。1、游标常量： declare cursor v_cur(v_name varchar2 default 'c

2012-12-24 18:18:17 670

原创避免selet into的no_data_found

declarev_rdc_id number;beginselect nvl(sum(rdc_id),0) into v_rdc_id from rdc t where 1=2; ---- 先聚合，再取nvl后。即使中间返回0条记录，也不会no_data_foundend;

2012-12-24 18:16:02 294

原创 SET TRANSACTION READ ONLY 只读事务

如果把事务设置成READ ONLY，后续查询就能看到事务开始之前提交的内容;只读事务对于运行那些涉及到一个或多个数据表的多查询来说，是很有用的；并且，在其他用户对表进行更新操作的时候，我们也可以对同样的表进行查询操作。在只读事务中，所有的查询都会引用同一个提供多表，多查询，读一致视图的数据库快照。其他用户可以像平时一样继续查询或更新数据。在下面的例子中，作为一个商店经理，我们可以使用一个只

2012-12-22 21:44:07 2289

原创游标加锁更新数据

1、加锁会锁住游标中的所有数据行，而不是只锁定取出的数据行 DECLARE CURSOR c1 IS SELECT empno, job, sal FROM emp FOR UPDATE; ...BEGIN OPEN c1; LOOP FETCH c1 INTO ... ..

2012-12-22 21:42:47 901

原创 oracle_loader类型外部表

1、创建目录（create any directory）： SQL> create user oracle identified by oracle;用户已创建。SQL> grant dba to oracle;授权成功。SQL> grant create any directory to oracle;授权成功。SQL> conn oracle/oracle

2012-12-22 21:26:04 546

原创 oracle_datapump类型外部表

1、卸载数据（可以并行） create table dp_user_objectsorganization external(type oralce_datapumpdefault directory user_data_dirlocation ('user_objects1.dmp','user_objects2.dmp'))parallel 2as s

2012-12-22 21:24:14 400

原创 sql和PL/SQL中绑定变量的区别

在oracle 中，对于一个提交的sql语句,存在两种可选的解析过程, 一种叫做硬解析,一种叫做软解析.一个硬解析需要经解析,制定执行路径,优化访问计划等许多的步骤.硬解释不仅仅耗费大量的cpu，更重要的是会占据重要的们闩（latch）资源，严重的影响系统的规模的扩大（即限制了系统的并发行），而且引起的问题不能通过增加内存条和cpu的数量来解决。之所以这样是因为门闩是为了顺序访问以及修改一些

2012-12-22 21:23:29 470

原创 Trigger example

1、一个例子表test1(id1 int,name varchar2(200); 表log(trigger_info) create or replace trigger audit_test1 after insert or update of name or delete on test1 --只有update能到列 fo

2012-12-22 21:22:00 296

原创 Merge

一、merge相关1、一些复杂的关联update更新数据，可以考虑用merge代替2、on中可以实现被插入表的单表数据过滤(using 数据源可以为空,此时when matched 和when not matched 返回null都不执行）如 merge into test d using （select 1 from dual）s

2012-12-22 21:19:59 293

原创 flashback_transaction_query

1、记录Undo中所有提交和未commit的事务记录2、例子：查询未提交事务 select * from v$transaction; 查询已经提交的事务ID(某个表test上的事务) a、delete from test where rownumb、select versions_xid,versions_operation,versions_start

2012-12-22 21:19:03 409

原创 Rman backup and recover

==========================================================================================================几个状态区别：expired ： crosscheck 后产生，物理文件与目录信息不一致，恢复物理文件后，可自动转为availableobsolete：基于设定的redun

2012-12-22 21:16:28 474

原创 expdp/EXP

expdp是 oracle 10g提供的一个代替exp的工具，不论从速度还是功能上来讲，相对于exp来说都是一个飞跃。1. 执行expdp之前要先创建directory对象，如： CONNECT system/managerCREATE OR REPLACE DIRECTORY expdir AS ‘d:\exp’;GRANT read,write ON DIRECTORY exp

2012-12-22 21:10:13 519

原创 FLASHBACK DATABASE 的方法

1、查看闪回日志信息 select * From v$flashback_database_log; select * from v$flashback_database_stat;2、mount下启动archive，设置DB_RECOVERY_AREA_DEST位置和大小，启动闪回3、mount下闪回，read only 打开检查，若不符合要求，撤销闪回用RECOVER

2012-12-22 21:03:57 279

原创如何停止一个JOB

1、标识job为broken select job from dba_jobs_running; 注意使用DBMS_JOB包来标识你的JOB为BROKEN。 SQL> EXEC DBMS_JOB.BROKEN(job#,TRUE); 注意：当执行完该命令你选择的这个JOB还是在运行着的。2、 Kill 对应的Oracle Session

2012-12-22 21:03:21 3116

原创 oracle死锁时，杀进程的方法

第一步：尝试在sqlplus中通过sql命令进行删除，如果能够删除成功，则万事大吉！但通常情况下，出现死锁时，想通过命令行或者通过oracle的管理工具删除有死锁的session，oracle只会将该session标记为killed，但无法清除掉，往往需要通过第二步在操作系统层级进行删除！Connected to Oracle9i Enterprise Edition Release 9.2.

2012-12-22 21:02:40 608

原创闪回查询、闪回表、闪回数据库(delete,update,drop及一切混合动作)

--===================情景1(inset、delete表)=================================select * from location t;delete from LOCATION where LOCATION_id in (23,123);commit;select * from LOCATION order by 1;

2012-12-22 21:01:29 604

原创表空间space使用查询

--表空间使用select * from SM$TS_FREE;select * from sm$ts_used; SQL>select textfrom dba_viewswhere view_name='SM$TS_FREE';TEXT----------------------------------------------------------------

2012-12-22 20:56:50 394

原创查询锁及并发锁等待

--锁等待信息（以上查询结果是一个树状结构，如果有子节点，则表示有等待发生) SELECT /*+ rule */ (SELECT '''' || TT.SID || ',' || SS.SERIAL# || ''' (' || SS.MACHINE || ')' FROM V$LOCK TT, V$SESSION SS WHERE TT.BLOCK

2012-12-22 19:14:37 342

空空如也

空空如也