ITPub博客

variable jobno number; begin dbms_job.submit(:jobno, 'pro_test;', sysdate,'sysdate 1/24/12'); commit; end; / --注意存储过程名不要参数，直接加分号;[@more@]

--D:oracleproduct10.2.0log--重启生效alter system set utl_file_dir='D:/oracle/product/10.2.0/log' scope=spfile;show parameter utl;--创建LOGMNR包--不要在plsql dev下面执行，直接在cmd下plsql下执行@D:oracleproduct10.2.0db_3RDBMSADMINdbmslmd.sql@D:oracleproduct10.2.0db_3RDBMSADMINdbmslm.sql@D:oracleproduct10.2.0db_3RDBMSADMINdbmslms.sql@D:oracleproduct10.2.0db_3RDBMSADMINprvtlm.plb[@more@]--生成字典begin sys.dbms_logmnr_d.build(dictionary_filename=>'myora100201.lgmr',dictionary_location=>'D:ora

摘自http://hi.baidu.com/nixsql/blog/item/d8b4dbd80723ab7ad1164e1a.htmlDBA_HIST_ACTIVE_SESS_HISTORY displays the history of the contents of the in-memory active session history of recent system activity. This view contains snapshots of V$ACTIVE_SESSION_HISTORY. See for further interpretation details for many of these columns (except SNAP_ID, DBID, and INSTANCE_NUMBER). [@more@] Column Datatype NULL Description SNAP_ID NUMBER Unique snapshot ID DBID NUMBER Da

--查询v$librarycache: select sum(pins) "Executions", sum(pinhits) "Hits", ((sum(pinhits) / sum(pins)) * 100) "PinHitRatio", sum(reloads) "Misses", ((sum(pins) / (sum(pins) + sum(reloads))) * 100) "RelHitRatio" from v$librarycache; --查询 v$sql_bind_capture，看看 average binds 是否大于15 (issue): select sql_id, count(*) bind_count from v$sql_bind_capture where child_number = 0 group by sql_id having count(*) > 20 order by count(*); --查找有问

最近一次查询oracle性能时，很不稳定。通过查询dba_jobs发现一个运行很频繁的job: emd_maintenance.execute_em_dbms_job_procs 此job每分钟运行一次。用下列语句将系统job停掉。 exec sysman.emd_maintenance.remove_em_dbms_jobs后系统运行稳定。[@more@]

select * from dba_tablespaces--查询系统包含哪些tablespaceselect dbms_metadata.get_ddl('TABLESPACE','USERS');--获取表空间USERS的ddl语法[@more@]

package com.azhu.test;import java.rmi.server.UID;; public class Uid { //private UID uid=new UID(); public static void main(String[] args){ System.out.println("test"); for(int i=0;i<10;i++){ System.out.println(getUid()); } } public static String getUid(){ return new UID().toString(); }}[@more@] ORACLE 数据库java:create or replace and compile java source named javaUtils asimport java.rmi.server.UID;public class JavaUtils{ public static String getUID() { return

使用过oracle的人都知道，not in 不能有效利用索引，所以效率一直是开发人员所担心的。那如何来优化not in呢？(not exists,except 及左关联)其实在oracle中可以用minus,在db2里用except实现这个需求。 具体实现思路是 1 假设一个sql是 select * from taba a where a.cola not in ( select cola from tabb ) 已在taba与tabb上建立索引。 2 select distinct a.* from taba a, ( select cola from taba minus --在db2中用except替换 select cola from tabb ) b --构造一个不在tabb中的列集合 where a.cola=b.cola 3 minus与except的实现由集合

connect by的妙用 1 构造任意个数的集合(比如100个) select rownum rn from dual connect by rownum <=1002 构造全年日期select to_date('2005-01-01','yyyy-mm-dd')+rownum-1from dualconnect by rownum<=365[@more@]db2中有无类似的函数呢,可也用如下变通:1with temptb(level) as( select 1 level from sysibm.sysdummy1 union all select b.level+1 from temptb b )select * from temptb fetch first 100 rows only ;2(这个语句需要源表的行数大于366,其实可以用上面的方法替换)select date('2005-01-01')+(rn -1 ) daysfrom (

Select A.COLUMN_NAME COLUMN_NAME, B.COMMENTS, decode(A.DATA_TYPE,'DATE',A.DATA_TYPE, 'NUMBER',decode(A.DATA_SCALE,0,A.DATA_TYPE||'('||a.DATA_PRECISION||')', A.DATA_TYPE||'('||a.DATA_PRECISION||','||A.DATA_SCALE||')'), A.DATA_TYPE||'('||a.DATA_LENGTH||')' ) DATA_TYPE From USER_TAB_COLS A , ( Select * From (Select a.*,row_number() over(partition By column_name order by comments) rn From( Select Distinct COLUMN_NAME,COMMENTS From USER_COL_COMMENTS Order By COLUMN_NAME DESC,COMMENTS Nulls Last ) a )

--查询对象是否被锁 SELECT /*+ rule */ s.username, decode(l.type,'TM','TABLE LOCK', 'TX','ROW LOCK', NULL) LOCK_LEVEL, o.owner,o.object_name,o.object_type, s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser FROM v$session s,v$lock l,dba_objects o WHERE l.sid = s.sid AND l.id1 = o.object_id(+) AND s.username is NOT Null [@more@]

Select * From v$sqlarea Order By cpu_time Desc --最耗资源 SELECT SQL_TEXT,hash_value, executions, buffer_gets, disk_reads, parse_calls FROM V$SQLAREA WHERE buffer_gets > 10000000 OR disk_reads > 1000000 ORDER BY buffer_gets + 100 * disk_reads DESC; --其中包括 Database Wait Time Ratio（数据库等待时间比） --和 Database CPU Time Ratio（数据库 CPU 时间比）这两个时间度量 select METRIC_NAME, VALUE from SYS.V_$SYSMETRIC where METRIC_NAME IN ('Database CPU Time Ratio', 'Database Wait Time Ratio')

--注意表名用相应表名去替换下面的表名都为oper_log --fb/vo 属性 column properties format a256; select 'private String '||LOWER(A.COLUMN_NAME)||';//'||B.COMMENTS properties FROM user_tab_cols A,user_col_comments B WHERE A.TABLE_NAME=B.TABLE_NAME AND LOWER(A.TABLE_NAME)='oper_log' and a.COLUMN_NAME=b.COLUMN_NAME ; --生成sql语句 --注意Sys_Connect_By_Path参数的字段中有','时不能用','去分隔，改为用'@@',然后再替换为',' --用Sys_Connect_By_Path(COLUMN_NAME, ',')时将行转换成列时，每一行都加'

plsql/devloper进不了调试状态 oracle环境： Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP and Data Mining Scoring Engine options 利用plsql/devloper6调试时，对test窗口总是进不了调试状态，按f9也一样。 解决方法:在要调试的存储过程中添加断点后编译，再调试。 对rac环境下调试完后，最好用命令再编译一次。 alter package packagename compile; alter package packagename compile body;[@mor

plsql自动生成java源码[@more@]--注意表名用相应表名去替换下面的表名都为oper_log --fb/vo 属性 column properties format a256; select 'private String '||LOWER(A.COLUMN_NAME)||';//'||B.COMMENTS properties FROM user_tab_cols A,user_col_comments B WHERE A.TABLE_NAME=B.TABLE_NAME AND LOWER(A.TABLE_NAME)='oper_log' and a.COLUMN_NAME=b.COLUMN_NAME ; --生成sql语句 --注意Sys_Connect_By_Path参数的字段中有','时不能用','去分隔，改为用'@@',然后再替换为',' --用Sys_Connect_By_Path(COLUMN_NAME, ',')

类型长度大于最大值at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162)at oracle.jdbc.driver.DatabaseError.check_error(DatabaseError.java:885)at oracle.jdbc.driver.T4CMAREngine.buffer2Value(T4CMAREngine.java:2231)at oracle.jdbc.driver.T4CMAREngine.unmarshalUB2(T4CMAREngine.java:1048)at oracle.jdbc.driver.T4CTTIdcb.receiveCommon(T4CTTIdcb.java:112)at oracle.jdbc.driver.T4CTTIdcb.receiveFromRefCurs

http://www.itpub.net/thread-1052750-1-1.htmloracle 长数据类型的问题：oracle 长数据类型的问题：比如 数据：123456789012.1234 插入oracle 中就变成了 123456789012.1230 如果数据是：123456789012.5678 插入数据库中就变成了：123456789012.5680 当小数点的数据长度小于12位的情况时，就不会出现上述的问题。数据库的类型原为NUMBER(20,4)，改为NUMBER(22,5)问题依然。哪位仁兄遇到到类似的问题，是怎么解决的？用函数to_char进行转换.[@more@]往往在java开发过程中,习惯了编写类似select col1,col2,... ,colnfrom table1where ...

plsql逐行累加 Select eba002,buydate,2 buynum,outnum,nvl(Sum(buynum) over (partition by eba002 order by buydate),0)sumbuy,nvl(Sum(outnum)over (partition by eba002 order by buydate),0) sunout3 From zhugyt1[@more@]plsql逐行累加 EBA002 BUYDATE BUYNUM OUTNUM---------- ---------- ---------- -----------------91001 2008-1-1 200 91001 2008-1-2 300 5091001 2008-1-3 10091002 2008-1-1 100 91002 2008-1-2 20 91002 2008-1-3 50需要依据eba002计算出总的购入数与销出数,结果如下:EBA002 BUYDATE BUYNUM OUTNUM SUMBUY S

sql优化 sql类似:select col1,col2,...colnfrom v_view1where col1='...'and col...其中v_view1又是类似于select v1col1,v1col2,...v1colnfrom v_view2where ...而v_view2类似于select v2col1,v2col2,...,v2colnfrom table1where v2col1!=...and not ...这样的sql我搞了半天,对不等于,对not这种都不好优化.而sql的执行计划,竟然对一个数据量比较大的表进行了6次全表扫描.而这个表每天数据都在疯狂的增长.在plsql/devloper下执行一次要花时10秒左右.是不是优化没法进行了?从理论上来说,sql的优化就是尽量减少数据库的读与写.而这个sql有6次