ITPub博客

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 ----

转自：http://www.cnblogs.com/tracy/archive/2011/09/02/2163462.htmlIndex Full Scan reads the whole index, in an ordered manner (i.e. it "walks" the index from the lowest value to the highest value). (Index Fast Full Scan reads the whole index, without bothering about ordering, it just reads all the index leaf blocks using multiblock reads -- similar to a Table Full Scan). Index Range Scan reads (or expects to read) a limited set of entries from the index. It starts from a certain point

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@]

expdp/impdp工具使用总结1.impdp使用NETWORK_LINK时,不支持LONG类型的表2.一般的database object导出时快，导入时慢.3.含BLOB字段的表导出时慢,导入时非常快，能达到1G/MIN4.查询expdp/impdp的视图dba_datapump_jobs、dbs_datapump_sessions5.跳过当前正在运行的作业.假如你在导入时发现等了很长时间有个表都没能导入,而这个表不影响业务,可以等应用程序运行后，再来处理.那么可以采用这种方式.先进入交互模式，停掉JOB，再加上参数start_job=skip_current,即可跳过当前JOB运行的作业,继续运行剩下的。6.AIX命令prtcon显示系统的配置.smtc

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:

最近公司数据库alert log总是报 checkpoint not completed.不包括硬件问题，解决这个问题需要从三个方面入手：1.checkpoint是什么？2.产生此错误的机制？3.解决方法 1.checkpoint是什么？ checkpoint负责：通知DBWn进程；update数据文件头；update控制文件。 每隔3秒钟，CKPT进程就会将有关数据存储在control file中，来标志、验证当recovery开始时online redo log file对应的位置。checkpoint 的目的是为了保证在之前的时间点database buffer cache中的缓冲数据已经全部被写入datafiles。这个checkpoint 也是在实例

ADDM、ASMM、ASM、ASSM、AUM、oracle联机重定义技术[@more@]

错误原因:使用了分布式事务，造成这个问题的原因很多时候都是由于网络问题造成的解决办法:1.找到TRAN_IDSelect 'Rollback force '''||LOCAL_TRAN_ID||'''' from sys.pending_trans$;2.將其COMMIT或ROLLBACK: rollback force LOCAL_TRAN_ID; commit force LOCAL_TRAN_ID; 3.如果执行失败,则强行从资料字典中删除该交易记录: set transaction use rollback segment system; delete from dba_2pc_pending where local_tran_id = LOCAL_TRAN_ID; delete from pending_sessions$ where local_tran_id = LOCAL_TRAN_ID; delete fr

1. 查看物理CPU的个数#cat /proc/cpuinfo |grep "physical id"|sort |uniq|wc -l 2. 查看逻辑CPU的个数#cat /proc/cpuinfo |grep "processor"|wc -l 3. 查看CPU是几核#cat /proc/cpuinfo |grep "cores"|uniq 4. 查看CPU的主频#cat /proc/cpuinfo |grep MHz|uniq[@more@]

今天网络中断大概10分钟恢复后，数据库连接不了。报错达到了最大进程数据，查看lsnrctl services有60多万专用连接。关掉listener重启，一会儿又报同样错误。尝试用sys登录修改processes参数，无法登录。kill 掉一些进程，重新登录，修改processes参数重启后，错误依然。查看alert log发现J000 die错误，查资料，修改 job_queue_processes=100后正常运行。后来思考发现了错误的根源。网络中断后，一些JOB无法运行，网络恢复后JOB都开始运行，导致JOB过多，而默认JOB_QUEUE_PROCESSES参数只有10个，JOB就失败。而JOB调用的procedure/package

Process J000 died, see its trace file Mon Sep 7 16:26:07 2009 kkjcre1p: unable to spawn jobq slave process Mon Sep 7 16:26:07 2009 Errors in file /d01/proddb/10.2.0/admin/ora10g/bdump/prod_cjq0_27156.trc: Process J000 died, see its trace file Mon Sep 7 16:26:13 2009 kkjcre1p: unable to spawn jobq slave process Mon Sep 7 16:26:13 2009 Errors in file /d01/proddb/10.2.0/admin/ora10g/bdump/prod_cjq0_27156.trc: Mon Sep 7 16:26:44 2009 Process J000 died, see its trace file Mon Sep 7 16

Dropping, recreating or compiling a package is failing with: ORA-600 [PSDTYFND_WITH_SUFFIX] The package includes pipelined functions.(See Note.136909.1 PL/SQL Pipelined and Parallel Table Functions in Oracle9i) CauseThe cause of this issue is not known. It is believed that importing into a database where these pipelined functions already exist may be the cause, but testing has failed to rep SolutionWhen a pipelined function is created a number of system generated objects are also created. These

ora-12631:用户名检索失败解决方案：These errors are associated with remote authentication on NT under NTS (NT Transport Services). Essentially, the following parameter is set in the "sqlnet.ora": SQLNET.AUTHENTICATION_SERVICES = (NTS) As a result, if you are logged on as a domain user, and you don't have a network connection (network cable temporary removed or laptop booted standalone), you will not be able to connect to the database because the authentication will try to reach

当PACKAGE中含有PIPELINED FUNCTION时，有时会报ORA-600错误。因为编译PACKAGE时会自动生成N个TYPE，而有的TYPE会duplicate，所以当compile or recreate or drop PACKAGE会报ORA-600。解决方法是删除 duplicate的TYPE，一般TYPE名称为“%_OBJECT_ID_2_%“，可以通过PKG DEFINITION的OBJECT_ID ,查找出相应的TYPE并DELETE。有空再添加实例说明。[@more@]

例：以A，B，C字段建立索引，且A、B、C均为字符。1. select * from tab_name where a in (1,4);2.select * from tab_name where a in ('1','4');则语句1不走索引，而语句2走索引，即语句2更优。[@more@]

[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