ITPub博客

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

看到很多人在论坛上问如何清空*.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

今天用ADDM生成的报告发现有几个表存在较严重的segment fragment，检查发现是统计信息(statistics collection)太旧.进一步检查发现是对象收集统计信息被锁,无法收集统计信息.优化过程如下：SQL> SELECT a.num_rows,a.avg_row_len FROM dba_tables a WHERE a.table_name='ZDRYJCXX';NUM_ROWS AVG_ROW_LEN ---------- ----------- 3015344 396 --统计信息行数 SQL> select count(*) num_rows from zdry.zdryjcxx;NUM_ROWS ---------- 909548 --实际行数 SQL> exec dbms_stats.gather_table_stats(ZDRY,'ZDRYJCXX');BEGIN dbms_stat

select q'# I'm a big man !#' from dual;[@more@]qu

结合闪回版本查询和闪回事务查询完成对某个事务修改数据的恢复.例子，SQL> insert into zdry.test values(8,'uestc');已创建 1 行。SQL> commit;提交完成。SQL> select versions_xid from zdry.test versions between timestamp sysdate-2/1440 and sysdate;VERSIONS_XID ----------------