Maclean’s Oracle Database Tech Blog Archives

Text:   Stop SMON from cleaning up obj$ ——————————————————————————- NOTE: Events should NEVER be set by customers unless advised to do so by Oracle Support Services. You must read <Note:75713.1> before setting ANY event on a system. Usage: ~~~~~~ To disable cleanup of OBJ$ by SMON set this event in the init.ora file thus: EVENT=”10052 trace…

create table SPACE_TEMP ( TABLESPACE_NAME CHAR(30), CONTIGUOUS_BYTES NUMBER) / declare cursor query is select * from dba_free_space order by tablespace_name, block_id; this_row query%rowtype; previous_row query%rowtype; total number; begin open query; fetch query into this_row; previous_row := this_row; total := previous_row.bytes; loop fetch query into this_row; exit when query%notfound; if this_row.block_id = previous_row.block_id + previous_row.blocks then…

Overview ——– This script is intended to provide an easy method to provide information necessary to troubleshoot Data Guard issues. Script Notes ————- This script is intended to be run via sqlplus as the SYS or Internal user. Script ——- – – – – – – – – – – – – – – -…

imp数据导入时往往大多数的时间都消耗在了索引建立上，我们可以通过为导入会话设置一系列session级别的参数来加速索引的建立: begin dbms_system.set_int_param_in_session(&sid, &serial, ‘db_file_multiblock_read_count’, 64); dbms_system.set_int_param_in_session(&sid, &serial, ‘sort_area_size’, 209715200); dbms_system.set_int_param_in_session(&sid, &serial, ‘_sort_multiblock_read_count’, 64); end; 注意在PGA自动管理模式下(即当workarea_size_policy=AUTO时)，自行指定的sort_area_size参数将无法生效。MOS上Bug 8939043叙述了目前dbms_system包的功能仅能修改session级别的布尔和数值类型参数，而无法修改字符串类型参数的问题;所以目前也还无法动态修改其他会话中的workarea_size_policy参数(虽然这个参数在session/system级别是可以动态修改的)。

New Background Processes In 11g *  ACMS (atomic controlfile to memory service) per-instance process is an agent that contributes to ensuring a distributed SGA memory update is either globally committed on success or globally aborted in the event of a failure in an Oracle RAC environment. *  DBRM (database resource manager) process is responsible for…

Two purposes of latches were:Serialize Execution,Serialize Access Match the characteristics with latches and enqueues. Several modes of access                   Enqueue FIFO queue acquisition                    Enqueue Mostly exclusive access                   Latch Non-deterministic acquisition      Latch No atomicity                              Enqueue Atomicity                    …

We can examine X$BH table to obtain buffer header information,The BH stands for buffer header. Structures that maintain a list of data buffer headers are called:Hash buckets. Hash buckets are grouped by relative DBA and class number. Hash chains list data buffer headers in one hash bucket. Cache Recycle Pool For randomly accessed large tables…

Reports index fragmentation statistics: ========== Script #1: ========== SET ECHO off REM NAME: TFSIFRAG.SQL REM USAGE:”@path/tfsifrag schema_name index_name” REM ———————————————————————— REM REQUIREMENTS: REM SELECT on INDEX_STATS REM ———————————————————————— REM PURPOSE: REM Reports index fragmentation statistics REM ———————————————————————— REM EXAMPLE: REM Index Fragmentation Statistic REM REM index name S_EMP_USERID_UK REM leaf rows deleted 0 REM leaf…

Stored objects And Transient objects are stored in the library cache, neither Temporary objects nor Permanent objects. The library cache is structured as a Hash table .But Library cache objects are composed of data heaps. Oracle access hash tables through hash buckets. SHARED_POOL_SIZE does the Oracle server determine the number of hash tables. When the…

X$BH Fixed Table Buffer Cache Diagram Column Type Description ~~~~~~ ~~~~~ ~~~~~~~~~~~ ADDR RAW(4) Hex address of the Buffer Header. INDX NUMBER Buffer Header number BUF# NUMBER HLADDR RAW(4) Hash Chain Latch Address See . ADDR LRU_FLAG NUMBER 8.1+ LRU flag KCBBHLDF 0x01 8.1 LRU Dump Flag used in debug print routine KCBBHLMT 0x02 8.1…