ITPub博客

直接上图SQL> select * from test_str; ID STR---------- -------------------------------------------------------------------------------- 1 0201,0202,0203,0204 2 0101,0102,0103,0104SQL> SQL> select distinct t.id, regexp_substr(t.str, '[^,]+', 1, level) 2 from test_str t 3 connect by level <= length(t.str) - length(replace(t.str, ',', '')) + 1 4 order by regexp_substr(t.str, '[^,]+', 1, level); ID REGEXP_SUBSTR(T.STR,'[^,]+',1,---------- -----------

SQL> connect SYS/password as SYSDBA SQL> spool text_install.txt SQL> @?/ctx/admin/catctx.sql CTXSYS SYSAUX TEMP NOLOCK Where: CTXSYS - is the ctxsys user password SYSAUX - is the default tablespace for ctxsys TEMP - is the temporary tablespace for ctxsys LOCK|NOLOCK - ctxsys user account will be locked or not SQL> connect CTXSYS/password@tns_alias SQL> @?/ctx/admin/defaults/drdefus.sql SQL> spool off connect SYS/password as SYSDBA ------------------- cut here ----

from :http://www.gplivna.eu/papers/v$session_longops.htmLong running Operations in Oracle (entries in v$session_longops)What are long running operations and how to monitor them?. 1Built in types of long operations. 4Table scan. 4Work behind the scenes. 410 000 database block threshold. 4Index Fast Full Scan. 51 000 database block threshold. 5Hash join. 6Even 20 block hash join may show up in v$session_longops. 6Hash joins can either fly or crawl 6Linear prediction may be wrong. 7Summary. 8Refere

1.禁用触发器再导对应表的数据（提高导入速度）2.数据导完后在索引表空间，重建索引（数据和索引分开存放,提高性能）3.重新设置表对应的最大sequence，否则可能出现主键或外键冲突（导入数据后sequence增大了）4.收集统计信息（获得表最新的信息，提高性能)[@more@]

--归档模式下使用SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN='YES';SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_END='YES';--使用一般文件存储数据字典,必须在归档模式下execute dbms_logmnr_d.build('logmnr_dict','/home/oracle');--加入日志文件EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/db/ora10g/oradata/test/redo03.log',options=>dbms_logmnr.NEW);EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/db/ora10g/oradata/test/redo04.log',options=>dbms_logmnr.ADDFILE);EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/db/ora10g/orada

[oracle@RAC2 ~]$ xclockXlib: connection to ":0.0" refused by serverXlib: No protocol specifiedError: Can't open display: :0.0[root@RAC2 ~]# export DISPLAY=:0.0[root@RAC2 ~]# xhost +access control disabled, clients can connect from any host[root@RAC2 ~]# su - oracle[oracle@RAC2 ~]$ xclock[oracle@RAC2 ~]$ dbca[@more@]

Oracle9i中全文检索的创建与使用Oracle从7.3开始支持全文检索，即用户可以使用Oracle服务器的上下文（ConText）选项完成基于文本的查询。具体可以采用通配符查找、模糊匹配、相关分类、近似查找、条件加权和词意扩充等方法。在Oracle8.0.x中称为ConText ；在Oracle8i中称为interMedia Text ； Oracle9i中称为Oracle Text。本篇主要介绍Oracle Text的基本结构和简单应用。Oracle Text是9i标准版和企业版的一部分。Oracle9i将全文检索功能做为内置功能提供给用户，使得用户在创建数据库实例时自动安装全文检索。Oracle Text的应用领域有很多

--purview是一个非分区表SQL> select table_name,partitioned from ALL_TABLES t where t.table_name='PURVIEW' ; TABLE_NAME PARTITIONED------------------------------ -----------PURVIEW NO--创建分区索引前,查看explain planSQL> explain plan for select * from purview where id<400; Explained SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value:

[oracle@jerry ~]$ cat .bash_profile# .bash_profile# Get the aliases and functionsif [ -f ~/.bashrc ]; then . ~/.bashrcfi# User specific environment and startup programsORACLE_HOSTNAME=jerry.comORACLE_BASE=/opt/oracle/appORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1ORACLE_SID=ocpPATH=$PATH:$HOME/bin:$ORACLE_HOME/bin. /usr/local/bin/oraenv --设置环境变量,登录oracle会提示设置ORACLE_SID以免实例太多误操作ORAENV_ASK=YESexport ORAENV_ASKumask 022export PATH ORACLE_HOSTNAME ORACLE_HOME ORACLE_BASE

看到很多人在论坛上问如何清空*.log文件.这里总结下个人使用的方法以清空listener.log为例.1.直接rm，当下次listener重启时会自动生成。也可进入lsnrctl设置set log_file new_listener.log2.使用echo >listener.log.相于清空log3.最好的方法是先设置set log_file new_listener.log到新的文件,然后旧的log文件确认不需要时再rm掉当然方法很多,比如清空alert log就比较简单直接cp到其他地方就OK，系统会自动生成.[@more@]

今天看到书上说如果利用flashback_transaction_query来完成集群表或者具有行链接的表闪回操作需要启用补充日志(alter database add supplemental log data),但做实验并不需要启用(只做了集群表实验).不知实验是否哪儿做错了,请高人指点？SQL> show userUSER is "SCOTT"SQL> create cluster emp_dept(dept no number(3));create cluster emp_dept(dept no number(3)) *ERROR at line 1:ORA-02335: invalid datatype for cluster column SQL> desc dept Name

如果一张表非常重要需要审计失败的操作.自治事务可以帮我们完成.我们可通过自治事务建立两个before/after trigger，如果操作成功before/alter是配对的.不成功则只有before。SQL> l 1 create or replace trigger trg_aud_bef_emp 2 before insert on emp for each row 3 declare 4 pragma autonomous_transaction; 5 begin 6 insert into audit_emp values(:new.ename,'before insert',sysdate); 7 commit; 8 end; 9*SQL> /Trigger created.SQL> desc emp Name Null?

sys schema : SQL> conn / as sysdba Connected. SQL> select current_scn from v$database; CURRENT_SCN ----------- 527262 SQL> alter session set current_schema=SCOTT; Session altered. SQL> insert into dept values(60,'DMS','CHINA'); 1 row created. SQL> commit; Commit complete. SQL> select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 50 DBA CHN 60 DMS CHINA 10

并行查询(一般在OLAP中使用)条件：多个CPU并且数据跨多个磁盘驱动器存储.SQL> explain plan for select /*+parallel(c,5)*/ * from sh.customers c order by cust_last_name;已解释。已用时间: 00: 00: 00.00SQL> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 2648474980--------------------------------------------------------------------------------------------------------------------------| Id | Ope

ORACLE 10Gshrink space1. enable row movement2.shrink spacealter table test enable row movement;alter table test shrink space compact; -- shrink HWMalter table shrink space cascade; --not shrink HWMquery error desciption infofor example:[oracle@jerry ~]$ oerr ora 155501555, 00000, "snapshot too old: rollback segment number %s with name "%s" too small"// *Cause: rollback records needed by a reader for consistent read are// overwritten by other writers// *Action: If in Automatic

SQL> select job,deptno,sum(sal) total_sal from scott.emp group by rollup(job,deptno);JOB DEPTNO TOTAL_SAL--------- ------ ----------CLERK 10 1300CLERK 20 1900CLERK 30 950CLERK 4150ANALYST 20 6000ANALYST 6000MANAGER 10 2450MANAGER 20 2975MANAGER 30 2850MANAGER 8275SALESMAN 30 5600SALESMAN 5600PRESIDENT 10 5000PRESIDENT 50002902515 rows selectedgroup by rollup(job,deptno)从右到左开始计算(job,deptno)->(job)-> (*)SQL> select job,deptno,sum(sal) total_sal from scott.emp group by job,deptn

用SQL*PLUS不能启动到mount阶段，因为没PFILE或者SPFILE。使用RMAN可以，因为它会使用INIT.ORA文件启动。背景：用RMAN备份backup database plus archivelog;模拟丢失对象scott.ojects记录下时间alter system set nls_date_format='yyyy-mm-dd hh24:mi:ss' scope=spfile;SQL> select sysdate from dual;SYSDATE-------------------2011-01-25 11:32:28恢复流程如下：RMAN:rman target / restore spfile from <>; --恢复SPFILEshutdown immediate --关闭DBstartup nomount; --使用恢复的SPFILE重新启动restore controlfile from &l