SQL
文章平均质量分 54
launch_225
与电脑良好沟通~~~
展开
-
构造绑定含有绑定变量的JAVA的SQL语句执行计划
情景:11G R2,有条SQL执行计划有异常,内存中并没有好的现存的执行计划(baseline用不了),经确认可通过sql profile 绑定固定;----------------------------------------------------SQL的样式:select * from *where a=:1 and b=:2and c=:3原创 2014-02-07 09:20:10 · 1646 阅读 · 0 评论 -
使用systemstate dump的方法
当数据库hang住的时候,如果能够使用sqlplus登录数据库,那么debug也许是你的不错的选择。而systemstate dump、 hang analyze也是我们解决问题的有效途径下面就是systemstate dump的使用方法when database met problems or malfunction, systemstate dump is necessary for转载 2012-07-18 18:07:44 · 2349 阅读 · 0 评论 -
PLS-00428: an INTO clause is expected in this SELECT statement
解决:select 后面跟intoSQL> begin 2 v_xml clob; 3 for i in (select keyid from tb_intf_rcgdxx_text2 where rownum 4 select XML from tb_intf_rcgdxx_text2 tws where tws.keyid=i.keyid for up原创 2012-07-18 18:06:07 · 14262 阅读 · 1 评论 -
查询数据库阻塞锁的SQL
select f.LAST_CALL_ET,f.sql_id,f.USERNAME,f.status,f.PROGRAM,f.machine,f.CLIENT_INFO, f.SID,f.BLOCKING_SESSION, 'kill -9 ' || t.SPID from v$process t, v$session f where t.addr = f.PADDR and原创 2012-07-17 17:28:54 · 1155 阅读 · 0 评论 -
ORA-01436: 用户数据中的 CONNECT BY 循环
2009-09-09 11:41SELECT r1.region_id,parent_id FROM cnl_region r1 WHERE r1.region_id =1 START WITH r1.region_id = 1 CONNECT BY PRIOR r1.region_id = r1.parent_i转载 2012-07-17 11:00:12 · 52536 阅读 · 0 评论 -
简单trigger 对一个表行操作时备份其老数据
create or replace trigger kfc_triafter update on kfc for each rowbegininsert into kfc2(object_id,object_name,flag)values(:old.object_id,:old.object_name,:old.flag);end;原创 2012-06-25 15:55:09 · 1122 阅读 · 0 评论 -
ORACLE PL/SQL编程之八:把触发器说透
大家一定要评论呀,感谢!光发表就花了我将近一个下午。本篇主要内容如下:8.1 触发器类型8.1.1 DML触发器8.1.2 替代触发器8.1.3 系统触发器8.2 创建触发器8.2.1 触发器触发次序8.2.2 创建DML触发器8.2.3 创建替代(INSTEAD OF)触发器8.2.3 创建系统事件触发器8.2.4 系统触发转载 2012-06-25 15:20:09 · 584 阅读 · 0 评论 -
未使用绑定变量检查【摘抄ASKTOM】
1.create table t1 as select sql_text from v$sqlarea;2.alter table t1 add sql_text_wo_constants varchar2(1000);3.create function create or replace function remove_constants(p_query in varchar2原创 2012-06-25 09:24:30 · 702 阅读 · 0 评论 -
查找全表扫描比较严重的SQL方法1
分为两步,这样查询比较快些1.首先定位SQL_idselect SQL_ID from v$sql_plan v where v.operation = 'TABLE ACCESS' and v.OPTIONS = 'FULL' and length(object_owner)=4 and object_owner like 'XX%'原创 2012-06-21 17:08:25 · 1731 阅读 · 0 评论 -
sqlplus定义绑变量执行(简单例子)
SQL> variable v_dept numberSQL> exec :v_dept := 10SQL> select * from employees where department_id = :v_dept;EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL ...--------------- -------------------- ----原创 2012-05-25 11:48:26 · 827 阅读 · 0 评论 -
PUSH_PRED HINT
1.未加hint前SQL> EXPLAIN PLAN FOR 2 SELECT C.CORRESPONDORGID ORGID, 3 A.TYPENAME, 4 B.PORTCODE RMNAME, 5 B.PORTCODE RMENTITYCODE, 6 A.OC原创 2012-05-25 10:01:09 · 1677 阅读 · 0 评论 -
系统HANG住了,怎么办?
如果系统HANG住了,这个时候做一个SYSTEM STATE DUMP,对于分析HANG的原因十分重要。但是很多情况下,系统HANG住了就无法登录,那么如何进行分析呢?METALINK DOC :359536.1提供了一些好的办法,在这里和大家共享:支持的数据库版本:9.2.0.1-10.2平台(部分是UNIX平台,部分适用于所有平台)1、10G以前,使用DBX,GDB之类的工具:转载 2012-07-19 10:16:58 · 3331 阅读 · 0 评论 -
oracle中 merge:
从oracle 9i开始支持merge用法,10g有了完善 create table a (id_ integer,count_ integer);insert into a values(1,3);insert into a values(3,6);create table b (id_ integer,count_ integer);insert into b转载 2012-08-06 09:29:00 · 694 阅读 · 0 评论 -
统计用户下的表的大小排行(包含大字段统计)
select * from (select owner, segment_name, sum(GB) from (select OWNER, SEGMENT_NAME, BYTES / 1024 / 1024 / 1024 GB from dba_segments where owner = 'U原创 2012-11-30 10:31:34 · 740 阅读 · 0 评论 -
instr和substr对于分隔符号过滤使用
select * from kfc2 1923.168.2551.取第一个小数点前面的数据select substr(a,1,instr(a,'.',1)-1) from kfc2:19232.取最后一个小数点后的数据select substr(a,instr(a,'.',-1)+1,length(a)) from kfc2:255原创 2012-10-11 15:54:50 · 1554 阅读 · 0 评论 -
sql profiles和dbms_sqltune(sta)
1.先通过v$SQL查找SQL_IDvar tuning_task varchar2(100); DECLARE l_sql_id v$session.sql_id%TYPE; l_tuning_task VARCHAR2(30); BEGIN l_sql_id:='4zbqykx89yc8v'; --SQL_IDl_tuning_task := dbms_原创 2012-09-24 17:50:25 · 2690 阅读 · 0 评论 -
SQL/PLSQL性能优化思路和工具使用【不断完善】
1.组合索引顺序(a,b,c)->(b,c,a)2.执行计划改变【通过dbms_stats.gather_table_stats(user,'t1',cascade=>true)和dbms_stats.gather.index_stats对表和索引进行搜集分析】3.没有建立索引,索引抑制(数据类型字段='8')4.减小聚簇因子5.加hint影响执行计划6.表柱状图分析7.总原创 2012-09-06 14:45:30 · 1117 阅读 · 0 评论 -
in and exists
SQL> select count(1) from t1; COUNT(1)---------- 2337184SQL> select count(2) from t2; COUNT(2)---------- 100SQL> desc t1 Name原创 2012-09-03 22:24:28 · 562 阅读 · 0 评论 -
批量编译指定的无效对象【JOB】
declare Vs_SQL varchar2(2000);begin for R in (select owner, object_name,decode(object_type,'PACKAGE BODY','PACKAGE',object_type) object_type from dba_objects原创 2012-08-17 18:48:24 · 658 阅读 · 0 评论 -
merge 绑定变量批量DML
一.SQL CODEbeginfor ii in (select a,c,nn from tt22) loopmerge into tt2using(select 1 from dual)on (a=ii.a)when matched thenupdate set n=ii.nn, c=ii.cwhen not matched thenin原创 2012-08-17 11:49:31 · 776 阅读 · 0 评论 -
ORA-01776: cannot modify 和instead of 触发器
1.SQL> select * from tt1; A N-------- ----------a 1b 2 SQL> select * from tt2; A C-------- --------a cd c原创 2012-08-16 11:06:09 · 1999 阅读 · 0 评论 -
trigger updating函数带参数用法;
1.表SQL> desc aName Type Nullable Default Comments ------ ------- -------- ------- -------- ID_ INTEGER Y COUNT_ INTEGER Y 2.建立触原创 2012-08-14 10:13:38 · 5330 阅读 · 1 评论 -
merge ora-38101
解决:在inset values(后面加上要插入表的前缀即可);merge into bak22using bak2on (bak22.keyid=bak2.keyid)when matched then update set planno=1+bak2.plannowhen not matched then insert values(bak2.KEY原创 2012-08-06 10:36:14 · 8104 阅读 · 3 评论 -
查询当前SQL执行计划
SQL> explain plan for 2 select * from dual;Explained.SQL> select * from dual;D-XSQL> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT----------------------------原创 2012-05-23 18:24:18 · 789 阅读 · 0 评论 -
《循序渐进Oracle》第二版一书相关代码
《循序渐进Oracle》第二版一书相关代码作者:eygle |English Version 【转载时请以超链接形式标明文章出处和作者信息及本声明】链接:http://www.eygle.com/archives/2011/08/sbsoracle_v2_script.html站内相关文章|Related Articles 《Oracle DBA手记3》一书样章下载转载 2012-05-23 14:16:56 · 1056 阅读 · 0 评论 -
正则表达式函数待完善
Regular Expression Function SyntaxThe four regular expression functions have the following syntax. You can specify regular expressions in their pattern argument.• REGEXP_LIKE(text, pattern[, optio转载 2012-05-17 14:55:48 · 409 阅读 · 0 评论 -
查询包含有%的结果
SQL> select * from tt2;A B-------- ----------aaa2% 111bbb3% 222ccc0% 333cccab 556dd3f 8898aaks 88996 r原创 2012-05-17 11:16:05 · 1729 阅读 · 0 评论 -
较有意思的表定义
create table employees( empno NUMBER(4) constraint E_PK primary keyconstraint E_EMPNO_CHK check (empno > 7000), ename VARCHAR2(8) constraint E_NAME_NN not null, init VARCHAR2(5) constraint E_I转载 2012-05-18 10:01:07 · 525 阅读 · 0 评论 -
Alternative DELETE Command, Using a Subquery
select *from salgrades;GRADE LOWERLIMIT UPPERLIMIT BONUS----- ---------- ---------- ----------1 700 1200 02 1201 1400 503 1401 2000 1004 2001 3000 2005 3001 9999 500delete from (sele转载 2012-05-18 08:33:42 · 380 阅读 · 0 评论 -
DEFERRED IMMEDIATE
INITIALLY Clause The INITIALLY clause establishes the default checking behaviorfor constraints that are DEFERRABLE. The INITIALLY setting can be overridden by aSET CONSTRAINT(S) statement in a sub转载 2012-05-18 10:16:38 · 710 阅读 · 0 评论 -
When a Subquery Returns Too Many Values(in(
select attendeefrom registrationswhere (course, begindate) in(select course, begindatefrom offeringswhere location = 'CHICAGO');转载 2012-05-17 12:55:20 · 405 阅读 · 0 评论 -
case usage
select attendee, begindate, case evaluationwhen 1 then 'bad'when 2 then 'mediocre'when 3 then 'ok'when 4 then 'good'when 5 then 'excellent'else 'not filled in'endfrom registrations转载 2012-05-17 11:49:38 · 471 阅读 · 0 评论 -
NOT USAGE
Just as in mathematics, you can eliminate parentheses from SQL expressions. The following two queries are logically equivalent:select * from employees where NOT (ename = 'BLAKE' AND init = 'R')sel原创 2012-05-17 10:48:13 · 453 阅读 · 0 评论 -
查看执行计划set
set autot on traceonlyset autot traceony explainSET AUTOTRACE ON EXPLAINSET AUTOTRACE ONSTATISTICSSET AUTOTRACE TRACEONLY转载 2012-06-02 09:06:15 · 639 阅读 · 0 评论 -
list how many blocks contain data
select count(distinct (dbms_rowid.rowid_block_number(rowid))) block_ct from t2 ;原创 2012-05-31 08:33:17 · 568 阅读 · 0 评论 -
merge用法
select * from master_tab;PID SALES STATUS-------- -------- ------1 12 CURR2 13 NEW3 15 CURRselect * from delta_tab;PID SALES STATUS-------- -------- ------2 24 CURR3 0 OBS4 42转载 2012-05-18 08:55:38 · 570 阅读 · 0 评论 -
GROUP BY CUBE
select department_id, job_id , count(department_id) headcount from employees group by cube(department_id, job_id);SQL> select department_id, job_id 2 , count(department_id) he原创 2012-05-20 10:08:13 · 578 阅读 · 0 评论 -
ORA-01480: STR 绑定值的结尾 Null 字符缺失
问题解决了,原来是报表中sql定义的最大长度是4000,在sql中如果字节个数大于4000的话,一个字符占两个字节,也就是 汉字*2+字母数 不能大于2000,否则将会报那样的错误解决办法:把每一行都在plsql中左对齐,这样可以去掉很多空格转载 2012-05-23 09:06:52 · 101088 阅读 · 6 评论 -
简单运用动态SQL定JOB整理表碎片SQL
declaresql_1 varchar2(200);sql_2 varchar2(200);beginsql_1:='alter table test enable row movement';sql_2:='alter table test shrink space';execute immediate sql_1;execute immediate sql_2;原创 2012-05-22 17:17:29 · 953 阅读 · 0 评论 -
获得本机的IP,机器名,客户端,用户名
SELECT MACHINE, SYS_CONTEXT('USERENV', 'IP_ADDRESS'), PROGRAM, OSUSER FROM SYS.V_$SESSION T WHERE AUDSID = USERENV('SESSIONID')原创 2012-06-12 10:52:07 · 1218 阅读 · 0 评论