ITPub博客

sqlplus

REGEXP_LIKEREGEXP_SUBSTRREGEXP_INSTRREGEXP_REPLACE[@more@]====================REGEXP_LIKE====================SQL> select * from t1; COL1 COL2---------- ---------- 1 fly 2 flying 3 swim 4 Fly 5 Flies SQL> select * from t1 where regexp_like(col2, 'fl(y(ing)?|(ies))'); COL1 COL2---------- ---------- 1 fly 2 flying--'i' => 不区分大小写 SQL> select * from t1 where regexp_like(col2, 'fl(y(ing)?|(ies))','i')

数据库hang住的时候，应用自然是挂了，连sysdba都无法登陆。只能启用sqlplus的-prelim参数。[@more@]sqlplus -prelim "/as sysdba"oradebug setmypidoradebug unlimitoradebug hanganalyze 3--90 secoradebug hanganalyze 3exitsqlplus -prelim "/as sysdba"oradebug setmypidoradebug unlimitoradebug dump systemstate 10--wait 90 secondsoradebug dump systemstate 10--wait 90 secondsoradebug dump systemstate 10exit找到hanganalyze的trace文件，有如下信息：Found 12 objects waiting for <cnode/sid/ses

最近经常碰到这个等待事件，贴个metalink上的有用文档上来。Doc ID: 163424.1[@more@]Goal How to identify blocks which cause latch contention on the 'cache buffers chains' latch. How to identify a hot block within the database buffer cache. Solution Possible hot blocks in the buffer cache normally can be identified by a high or rapid increasing wait count on the CACHE BUFFERS CHAINS latch. This latch is acquired when searching for data blocks cached in the buffer cache. Since the Buffer cach

HANGANALYZE事件在分析系统挂住的时候很有用，尤其是会话(session)因为锁的原因挂住的时候。 用法： SQL>alter session set events 'immediate trace name HANGANALYZE level 4';[@more@]http://zhyuh.itpub.net/resource/334/29544

oradebug setospid 11774 oradebug event 10046 trace name context forever, level 12 oradebug event 10046 trace name context off[@more@]

包括隐含参数[@more@]SQL> col KSPPINM for a40SQL> col KSPPSTVL for a40SQL> set lines 100SQL> set pages 200SQL> list 1 Select a.ksppinm, b.ksppstvl 2 from x$ksppi a, x$ksppsv b 3 where a.indx = b.indx 4 order by 1比如 _kghdsidx_countSQL> Select a.ksppinm, b.ksppstvlfrom x$ksppi a, x$ksppsv bwhere a.indx = b.indxand a.ksppinm = '_kghdsidx_count'; 2 3 4KSPPINM-------------------------------------------------------------------------KSPPSTVL-----------------------

Oracle instance异常终止的情况下，如crash, shutdown abort等， 有时候memory,semaphores等资源并不能及时释放，这会影响下一次数据库的启动。[@more@]参见http://zhyuh.itpub.net/resource/334/26709

alter session set timed_statistics=true /alter session set max_dump_file_size=unlimited /alter session set tracefile_identifier='Identifier'/alter session set events '10046 trace name context forever, level 12' /SQL/alter session set events '10046 trace name context off'/[@more@]see http://zhyuh.itpub.net/resource/334/26661

关于min(), max()函数访问索引的方法。[@more@]参见http://zhyuh.itpub.net/resource/334/26409

When a database has severe performance problems, the cause of the problem may be a HANG. Using trace files produced by HANGANALYZE, you will be able to quickly determine if two or more processes are deadlocked and how many other processes may be affected. By using HANGANALYZE, you may also determine if a performance analysis will be required to diagnose the real problem when a HANG is taking place.[@more@]Steps to generate HANGANALYZE trace files -- Metalink 175006.1Interpreting HANGANALYZE trac

下面的sql，利用v$session视图里的 PREV_SQL_ADDR， PREV_HASH_VALUE列，确定某个SID或者spid对应的完整SQL[@more@]select /*+ ORDERED */ sql_text from v$sqltext awhere (a.hash_value,a.ADDRESS) in( select decode(sql_hash_value,0,PREV_HASH_VALUE,sql_hash_value),decode(sql_hash_value,0,PREV_SQL_ADDR,SQL_ADDRESS)from v$session bwhere b.sid=&SID)order by piece asc或者以操作系统的process ID为参数。select /*+ ORDERED */ sql_text from v$sqltext awhere (a.hash_value,a.ADDRESS) in( select decode(sql_hash_val

要写一个TRIGGER，把一张表的所有列，修改前后的值，都记录到另外一张修改历史表中。想从user_tab_columns里动态地获取列名，然后用循环自动获取每一列的修改前后值。于是写了一个trigger（仅以scott.emp表的sal列为例）[@more@]CREATE OR REPLACE TRIGGER Print_salary_changesBEFORE DELETE OR INSERT OR UPDATE ON EmpFOR EACH ROWWHEN (new.Empno > 0)DECLAREcursor cur_emp is select * from user_tab_columnswhere table_name='EMP';col_name varchar2(20);sal_new number;state1 varchar2(500);cursor_handle integer;execute_s

一台PC上搭建Data Guard，并做Switch-over测试[@more@]具体过程参见http://zhyuh.itpub.net/resource/334/16864中的文档Fail-over测试尚未开始

我见过最好的数据仓库入门文章，忍不住花些时间翻译了一下[@more@]http://zhyuh.itpub.net/resource/334/15824 原文请见 http://www.ciobriefings.com/whitepapers/StarSchema.asp

以scott用户下的表为例，其中的表纪录内容有所变动。[@more@]emp表有6条记录，其中Blake员工对应的部门号为空。SQL> col deptno format a10SQL> select empno,ename,nvl(to_char(deptno),'null') deptno from emp; SQL> col deptno format 999999SQL> select * from dept; 现在查询所有员工和所属部门信息，没有部门的员工也要列出来。使用outer join:SQL> list1 select nvl(to_char(e.deptno),'null') dept_no,nvl(d.dname,'null'),e.empno,e.ename2 from emp e, dept d3* where e.deptno=d.deptno(+)SQL> / 查看执行

发现一个好地方，里面有很多学习oracle的例子，比起光看文档来，看例子舒服多了。[@more@]http://www.oracle.com/technology/obe/start/index.html

一般设计数据库的时候，我们都考虑一个汉字占用两个字节。所以设计数据库的时候，如果认为某字段最长要存四个汉字，该字段都会定义为varchar2(8)。[@more@]SQL> create table t1 (col1 varchar2(8));Table created.但是测试插入三个汉字的时候就报错了。SQL> insert into t1 values('一二三');insert into t1 values('一二三') *ERROR at line 1:ORA-12899: value too large for column "NC31"."T1"."COL1" (actual: 9, maximum:8)检查相关参数及环境变量。SQL> select * fr

下面的存储过程，略加修改后，可以用来查看某个用户所有表的记录数，每个表占用的空间等[@more@]CREATE FUNCTION row_count (tab_name VARCHAR2) RETURN INTEGER AS rows INTEGER;BEGIN EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || tab_name INTO rows; RETURN rows;END;/SQL> declare 2 type TabCurTyp is ref cursor; 3 tab_cv TabCurTyp; 4 v_tname varchar2(20); 5 v_sql varchar2(200); 6 v_count number; 7 begin 8 open tab_cv for 9 'select tname from tab where tname in (''EMP''

Oracle10g中发送email方面，除了原先的utl_smtp包以外，还增加了utl_mail包[@more@]UTL_MAIL package:有cc,bcc,attachment功能， 安装此 package 需要运行utlmail.sql和prvtmail.plb脚本 适合小范围接受用户，附件32k以下，易操作，设置格式 UTL_SMTP package:适合附件32k以上，接收者众多的场合