ITPub博客

There are several new other background processes in 10g/11g,as following：MMON performs various manageability-related background tasks, for example:*Issuing alerts whenever a given metrics violates its threshold value*Taking snapshots by spawning additional process (MMON slaves)*Capturing statistics value for SQL objects which have been recently modifiedMMNL stands for manageability monitor light which is responsible to flush the information to the repository and performs frequent and light-weig

今天一部门开发人员通过公司BQQ问我是否可以在脚本中获取到update语句执行后的更新行数，并想知道返回值。我做了一个小试验，如下的内容很适合他借以利用。这个案例的关键字有sql%notfound、sql%rowcount和return，适合在脚本或应用中加以使用。[@more@]SQL> create table test_return 2 ( 3 name varchar2(24) 4 );Table createdSQL> insert into test_return values('111');1 row insertedSQL> insert into test_return values('222');1 row insertedSQL> commit;Commit completeSQL> SQL> set serveroutput o

The ORACLE_DATAPUMP access driver unloads data from an Oracle database to a flat file (DPAPI format) using the external table method. The external table must be created using the CREATE TABLE ... AS SELECT ... (CTAS) method. You can specify the PARALLEL clause when creatingthe table; ORACLE_DATAPUMP access driver unloads data into multiple flat files at the same time. One parallel execution server will write to only one file at a time. Unloading data in the context of external table means creati

1、Client-Side Query CacheThis feature enables caching of query result sets in client memory. The cached result set data is transparently kept consistent with any changes done on the server side.Applications leveraging this feature see improved performance for queries which have a cache hit. Additionally, a query serviced by the cache avoids round trips to the server for sending the query and fetching the results. It also reduces the server CPU that would have been consumed for processing the qu

Oracle Database Standard Edition One Oracle Database Standard Edition One delivers unprecedented ease of use, power, and performance for workgroup,department-level, and Web applications. From single-server environments for small business to highly distributed branch environments, Oracle Database Standard EditionOne includes all the facilities necessary to build business-critical applications.[@more@]Oracle Database Standard Edition Oracle Database Standard Edition delivers the unprecedented ease

新登陆的客户端无法登陆上数据库，抛了一堆堆栈和memory的异常。赶紧上os主机上，看看是否有告警信息。查了一下告警日志很正常，没有特别的异样信息出现。查询后台进程信息，如下：[oracle@linux3200 log]$ ps -ef |grep ora_[@more@]oracle 7704 1 0 Nov07 ? 00:00:44 ora_pmon_db3200oracle 7706 1 0 Nov07 ? 00:00:06 ora_psp0_db3200oracle 7708 1 0 Nov07 ? 00:00:04 ora_mman_db3200oracle 7710 1 0 Nov07 ? 00:00:19 ora_dbw0_db3200oracle 7712 1 0 Nov07 ? 00:03:44 ora_lgwr_db3200oracle 7714 1 0 Nov07 ? 00:00:32 ora_ckpt_d

DBNEWID is a database utility that can change the internal, unique database identifier (DBID) and the database name (DBNAME) for an operational database. The DBNEWID utility lets you change any of the following:[@more@]Only the DBID of a databaseOnly the DBNAME of a databaseBoth the DBNAME and DBID of a databaseTherefore, you can manually create a copy of a database and give it a new DBNAME and DBID by re-creating the control file, and you can register a seed database and a manually copied datab

This document assumes that you are doing the Backup and Recovery basics- Running in Archivelog mode- multiplexing the controlfile- Taking regular backups- Periodically doing a complete restore to test your procedures. [@more@]Questions and Answers1. Turn on block checking.REASON: The aim is to detect, very early the presence of corrupt blocks in the database.This has a slight performance overhead, but Checksums allow Oracle to detect earlycorruption caused by underlying disk, storage system, or

This short article describes several features of Oracle which may be used on new databases to help detect problems as close to when they occur as possible. Some of these features are publicly documented and some are not. All should be used with extreme care.[@more@]DBVERIFY~~~~~~~~ DBVERIFY (also known as DBV) performs similar checks to the block checking described above but it is a stand alone tool which can be run against an Oracle datafile. Most Unix platform

In Oracle databases, corruption in its broadest sense is incorrect information - information that Oracle did not expect to find. This condition can exist in memory, in data blocks, or in the data dictionary, giving rise to the five forms of corruption:Memory (HEAP) corruptionBlock corruptionDictionary corruptionUndo corruptionRedo corruption[@more@]HEAP corruption occurs when a piece of memory is overwritten and this can be caused by an Oracle Bug, an O/S overwrite, or by an application. The cod

上个礼拜在帮忙一同事解决expdp时碰到三个异常错误，今天总结如下：首先遇到的异常如下：[oracle@linux3200 oracle]$ expdp system/wicp_annie@db3200 directory=dir_expdp dumpfile='wicp_all_071026.dmp';[@more@]Export: Release 10.2.0.1.0 - Production on Thursday, 25 October, 2007 20:56:51Copyright (c) 2003, 2005, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsORA-31626: job does not existORA-

最近在将一heap日志表调整为partition的日志表时，发现一个问题，那就是查询分区数据时无法直接按变量来进行查询。过程如下：insert into t_actionlog_backupselect * from t_actionlog partition(v_partition_name)[@more@]其中v_partition_name是我程序中传入的分区名称变量，结果无论我怎么调试都抛异常“ORA-02149: 指定的分区不存在”。我直接在sql提示符下执行都是没问题的。见如下的过程：declarev_partition_name varchar2(20);beginv_partition_name :='p_20071016';insert into t_actionlog_backupselect * from t_actionlog pa

三、查询热点块和热点对象statspack报告中latch的竞争比较突出，见如下：Latch Name Requests Misses Sleeps Sleeps 1->4-------------------------- -------------- ----------- ----------- ------------cache buffers chains 105,398,981 27,571 361 27232/317/22NoWait WaiterLatch Name Where Misses Sleeps Sleeps------------------------ -------------------------- ------- ---------- --------cache buffers chains kcbrls: kslbegin 0 290 7cache buffers chains kcbgtcr: fast path 0 57 43cache buffers chains kcbgtcr:

二、Parent and Child LatchesMost internal Oracle data structures that are protected by latches are protected by only one latch. However, in some cases more than one latch may be used. For example, there may be a number of library cache latches protecting different groups of objects in the library cache, and separate cache buffers chains latches are used to protect each of the database buffer cache hash chains.[@more@]Whenever a number of latches may be used to protect different parts of a struct

一、Latch and LockLatches are the more restrictive mechanism, because they do not allow multiple processes to inspect the protected data structure at the same time—they provide for exclusive access only.[1] Locks allow for better concurrency, because they may be held in a shared mode when the data structure is simply being inspected.[1] This is a simplification. The redo copy latches can be shared, but this is hardware dependent.[@more@]Another significant difference between locks and latches i

Waits are timed if and only if the TIMED_STATISTICS parameter is set to TRUE.Let me endorse what others have said before, that the overhead of timed statistics is negligible. If you need to convince yourself, use the SET TIMING ON command in SQL*Plus to measure the elapsed time of a benchmark query. Use an otherwise idle system and take ten or more measurements with and without timed statistics. You will be hard pressed to discern any significant difference.[@more@]Without timed statistics, Orac

Export: Release 9.2.0.1.0 - Production on 星期四 10月 11 11:01:11 2007Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.连接到: Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.4.0 - Production已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集[@more@]. 正在导出 pre-schema 过程对象和操作EXP-00008: 遇到 ORACLE 错误 4068ORA-04068: existing state of packages has been discardedORA-04063: package bod

公司测试部一数据库无法正常启动，找到了我帮忙解决！具体故障现象如下：SQL> startupORACLE instance started.Total System Global Area 353857616 bytesFixed Size 737360 bytesVariable Size 318767104 bytesDatabase Buffers 33554432 bytesRedo Buffers 798720 bytesDatabase mounted.ORA-00368: checksum error in redo log blockORA-00353: log corruption near block 74032 change 1121332681927 time 09/25/200710:47:51ORA-00312: online log 3 thread 1: '/oracle/oradata/ora9i/redo03.log'[@more@]提示在线日志文件遭到了

昨天在建一分区表时抛了异常“ORA-14039: 分区列必须构成 UNIQUE 索引的关键字列子集”，具体过程如下：[@more@]SQL> begin2 execute immediate3 'create table MSG_STORAGE_CACHE4 (5 NAME VARCHAR2(64) not null,6 HASHKEY VARCHAR2(100) not null,7 PACKAGE VARCHAR2(3000) not null,8 CREATEDATE DATE default SYSDATE9 )10 partition by range(CREATEDATE)11 (12 partition p_'||to_char(sysdate-6,'yyyymmdd')||' values less than(to_date('''||to_char(sysdate-5,'yyyymmdd')||''',''yyyymmdd'')),13 partition p_'||to_char(s

同事反映应用中获取到的sequence的nextval值存在问题，从获取时间上来看不是递增的。一表中列SN的取值来源于序列TB_C_DATA_ALTER_LOG_SEQ，14：50插数据时SN为4081，而15：11插数据时SN反而变小了，为4062。经分析得知该序列采用了cache 20的默认选项。[@more@] 单实例的数据库倒不存在这个问题，RAC中就会出现先取的值不一定是小的，后取的值不一定会大的问题。具体原因是由于rac两节点中若序列的cache为20的话，在节点一上cache了1－20个，然后节点二上cache了20－40。当从不同的节点来进行对sequence取值的时候，会