ITPub博客

有个数据库，奇怪的是一建表就hang了，oracle也不报错，alert日志也没有任何告警和ora-的错误产生!非常奇怪，重启数据库很多次，都是这种现象，搞得很郁闷！跟踪了建表的session，发现处于死循环状态；等待事件是"control file seq... wait";搞得人相当的郁闷！然后我查看表空间是否充足，结果看不到建表所在的表空间的大小！非常奇怪！数据库正常启动，该表空间的数据也能正常查询！我查了下dba_data_files，发现该表空间的数据文件都存在，但是发现一处，有个数据文件的状态处于recover状态（后问了下，原来该数据文件曾经坏过

我这几天每隔5分钟去完全刷新一下物化视图,竟然发现每3个小时就产生3G的归档日志,搞得我得经常去删除日志,后改成快速刷新后,日志大大减少![@more@]

SQL> CREATE MATERIALIZED VIEW mv_vcard_last_hlrlist refresh fast asselect vcard.username,vcard.nick_name,vcard.pcode,hlrlist.region,vcard.birth_year, vcard.icon, vcard.descr, last.state,vcard.HLR_CITY, vcard.BIRTH_MONTH, vcard.BIRTH_DAYfrom hlrlist ,last ,vcard where vcard.username = last.username and vcard.hlr_city = hlrlist.hlr_idand vcard.status = 0 and last.state='Online' ;2 3 4 5 6 7 8 and vcard.status = 0 and last.state='Online'*ERROR at line 8:ORA-12052: cannot fast refresh materialize

今天在B库里发现统计的数据不对,每天注册人数才21人,以前都是基本稳定在5000人左右,结果查询是数据没有刷新过来 ,想了想是昨天我对基表用shrink回收了空间,结果造成了这个问题,那以后要很小心了,要不回出大问题!![@more@]http://blog.163.com/suntao1982@126/blog/static/6084445820080145103800/

今天刷新物化视图出现了问题:CREATE MATERIALIZED VIEW LOG ON users;CREATE MATERIALIZED VIEW users REFRESH FAST START WITH SYSDATE NEXT trunc(SYSDATE + 1) WITH PRIMARY KEY AS SELECT * FROM users@test.com;SQL> exec dbms_mview.refresh('USERS','FAST');begin dbms_mview.refresh('USERS','FAST'); end;ORA-12008: error in materialized view refresh pathORA-01008: not all variables boundORA-02063: preceding line from CNCYYORA-06512: at "SYS.DBMS_SNAPSHOT", line 2254ORA-06512: at "SYS.DBMS_SN

昨天一个朋友给我讲他的DB一直报ora-3136，带来的后果就是客户不能登陆，发现他安装了10g 10.2.0.1 for linux，没有用连接池，连接数也不是很多，process是200，并没有报连接溢出错误，但进程数一直在200以上，查metalink：*Note: 345197.1 1. set INBOUND_CONNECT_TIMEOUT_<listener name> = 0 in listener.ora2. set SQLNET.INBOUND_CONNECT_TIMEOUT = 0 in sqlnet.ora of server.3. stop and start both listener and data base.4. Re-run the Workflow Configuration Assistant看了一下，因为是10g，在network/admin/目录下什么

启动实例hang的一例的诊断数据库被人无意断开电源。1、启动数据库时HANG住：ALERT日志停在以下的行。RECO started with pid=7CJQ0 started with pid=8QMN0 started with pid=9Sun May 27 11:22:28 2007starting up 1 shared server(s) ...starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...Sun May 27 11:22:28 2007ALTER DATABASE MOUNTSun May 27 11:22:35 2007Successful mount of redo thread 1, with mount id 2423313207.Sun May 27 11:22:35 2007Database mounted in Exclusive Mode.Co

又发现一个不走索引的语句,一个长的sql条件很简单type=4但是开发人员在写的时候没有看这个type在表里的类型是char(1),结果该语句在sp报表的SQL ordered by Reads里就有所表现: CPU Elapsd Old Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value--------------- ------------ -------------- ------ -------- --------- ---------- 1,308,056 7 186,865.1 91.5 190.04 1222.70 1623835478让开发改为type='4'就好

通过sqlplus "/as sysdba"登录信息我们可以知道，sqlplus认为oracle数据库并没有关闭。oracle的进程都不存在了，但sqlplus为什么认为oracle没有关闭呢？它是根据什么来判断的？4、查看此时的共享内存段情况[oracle@rep ~]$ ipcs -as------ Shared Memory Segments --------key shmid owner perms bytes nattch status 0x91c11414 491531 oracle 640 142606336 10 ------ Semaphore Arrays --------key semid owner perms nsems 0xeddca300 360448 oracle 640 154 ------ Message Queues --------key msqid owner perms used-byt

一次调整优化过程总结 平台：windows 2000 server sp3数据库：Oracle 9.0.1.1.11. 在高峰时段，系统高负载的情况下用statspack一个小时做一次系统的快照。对statspack产生的报告注意其中的Top 5 Wait Events。具体可以参考eygle写的statspace使用指南。本例的等待事件主要是db file scattered read、buffer busy waits和db file sequential read。2. 对于db file scattered read主要是由于全表扫描造成的，db file sequential read说明当前系统负载很高或者cbo使用了不合适的索引。通过定时（2天一次在晚上12点执行dbms_stats.gather_sche

sqlldr userid=lgone/tiger control=a.ctl LOAD DATA INFILE 't.dat' // 要导入的文件 // INFILE 'tt.date' // 导入多个文件 // INFILE * // 要导入的内容就在control文件里 下面的BEGINDATA后面就是导入的内容 INTO TABLE table_name // 指定装入的表 BADFILE 'c:ad.txt' // 指定坏文件地址 ************* 以下是4种装入表的方式 APPEND // 原先的表有数据 就加在后面 // INSERT // 装载空表 如果原先的表有数据 sqlloader会停止 默认值 // REPLACE // 原先的表有数据 原先的数据会全部删除 // TRUNCATE // 指定的内容和replace的相同 会

With the Partitioning, OLAP and Data Mining optionsExport done in UTF8 character set and UTF8 NCHAR character set. exporting pre-schema procedural objects and actions. exporting foreign function library names for user ORCL . exporting PUBLIC type synonyms. exporting private type synonyms. exporting object type definitions for user ORCL About to export ORCL's objects .... exporting database links. exporting sequence numbers. exporting cluster definitionsEXP-00056: ORACLE error 4021 encounteredORA

今天查询一个运营库的user_jobs,发现job都停止了,未执行,修改了一些这个参数,job又可以执行了!SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 0; 等2~3分钟，重新设置SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 5; 此时PMON会重起CJQ0进程ref: http://www.eygle.com/archives/2004/11/job_can_not_execute_auto.html但是uptime看1:38pm up 361 day(s)这个服务器启动才361天,未到eygle说的497天,但是job进程却挺了,有点奇怪呀![@more@]

最初由 howard_zhang 发布现在一个节点是这个错误RAC1Wed Aug 8 13:49:19 2007WARNING: inbound connection timed out (ORA-3136)Wed Aug 8 13:49:19 2007WARNING: inbound connection timed out (ORA-3136)Wed Aug 8 13:49:19 2007WARNING: inbound connection timed out (ORA-3136)另外一个CONNECT不允许登陆的节点错误如下:RAC2ARC1: Failed to archive thread 2 sequence 200 (19504)Wed Aug 8 11:43:10 2007ARC2: Failed to archive thread 2 sequence 200 (19504)Wed Aug 8 11:43:10 2007ARC3: Failed to archive thread 2 sequence

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK ref: http://www.itpub.net/347746.html[@more@]

接上:我的逻辑standby配置:http://wayw009.spaces.live.com/blog/cns!73fba3701ddca1f5!322.entryotn上logical standby: http://blog.oracle.com.cn/html/11/t-26311.html 1. 验证是否存在表没有主键 select owner,table_name from dba_logstdby_not_unique where (owner,table_name) not in (select distinct owner,table_name from dba_logstdby_unsupported) and bad_column='Y'; 2. 验证 SELECT SUPPLEMENTAL_LOG_DATA_PK AS PK_LOG,SUPPLEMENTAL_LOG_DATA_UI AS UI_LOGFROM V$DATABASE; PK_ UI_--- ---NO

今天在10.2.0.1 for linux上exp时出现下面错误:EXP-00091: Exporting questionable statistics.这个问题可以在EXP的参数中加参数STATISTICS=none就可以解决[@more@]

今天运行一条sql语句,20多分钟了还是没有返回,数据也没有什么特殊的地方,于是我把这个用户下的各个表重新分析了一下,再执行该语句,发现很快获得了结果!!在这里记录一下dbms_utility.analyze_schema(schema=>'SCOTT',method=>'ESTIMATE',estimate_percent=>10,method_opt=>'FOR ALL INDEXES');[@more@]

Oracle中试图对一个子查询进行更新时可能会出现ORA-01779错误。该错误的内容为：ORA-01779: cannot modify a column which maps to a non-key-preserved table例如，使用以下的更新查询就会出现该错误。CREATE TABLE test1 ( id integer primary key, num integer ); INSERT INTO test1 VALUES (1,0); INSERT INTO test1 VALUES (2,0); INSERT INTO test1 VALUES (3,0); INSERT INTO test1 VALUES (4,0); CREATE TABLE test2 ( id integer, num integer, upd integer ); INSERT INTO test2 VALUES (1,10, 0); INSERT INTO test2 VALUES (2

环境：AIX5L+HACMP+ORACLE9I分析alert，没有发现错误：Fri Apr 27 17:08:05 2007Thread 1 advanced to log sequence 612Current log# 2 seq# 612 mem# 0: /oracle2/oracle/oradata/mssdb2/redo02.logFri Apr 27 17:08:05 2007ARC0: Evaluating archive log 1 thread 1 sequence 611ARC0: Beginning to archive log 1 thread 1 sequence 611Creating archive destination LOG_ARCHIVE_DEST_1: '/oracle2/oracle/oradata/oraclearchivelog/1_611.dbf'ARC0: Completed archiving log 1 thread 1 sequence 611Fri Apr 27 17:18:52 2007Shuttin