Maclean’s Oracle Database Tech Blog Archives

there’s no HWM for datafiles, it’s just a virtual term to describe the last block containing data in the data file, which is the minimum size allowed for sizing down the data file. This article intends to provide an SQL script to find tables which are fragmented (i.e Data is much lower then High Water…

pga_aggregate_target is a target, as opposed to a hard limit – so it isn’t unusual to go above that. 13G above that, now that’s unusual though! There IS an enhancement request in, to make a hard-limit setting, but that does not currently exist. There is a known bug in 10203 with certain statements burning up…

What are the default parameter values ? select dbms_stats.get_param(‘cascade’) from dual; select dbms_stats.get_param(‘degree’) from dual; select dbms_stats.get_param(‘estimate_percent’) from dual; select dbms_stats.get_param(‘method_opt’) from dual; select dbms_stats.get_param(‘no_invalidate’) from dual; select dbms_stats.get_param(‘granularity’) from dual; DEFAULT PARAMETER DBMS_STATS.AUTO_CASCADE NULL DBMS_STATS.AUTO_SAMPLE_SIZE FOR ALL COLUMNS SIZE AUTO DBMS_STATS.AUTO_INVALIDATE AUTO

事件 说明 例子 Event 10013 – Monitor Transaction Recovery 在Startup时跟踪事务恢复 ALTER SESSION SET EVENTS ‘10013 trace name context forever, level 1’; Event 10015 – Dump Undo Segment Headers- 在事务恢复后做Dump回退段头信息 ALTER SESSION SET EVENTS ‘10015 trace name context forever, level 1’; Event 10032 – Dump Sort Statistics Dump排序的统计信息 ALTER SESSION SET EVENTS ‘10032 trace name context…

以下脚本用于列出Oracle中数据文件的状况: REM Datafile Report set linesize 120 pagesize 1400; SELECT t.tablespace_name, ‘Datafile’ file_type, t.status tablespace_status, d.status file_status, ROUND((d.bytes – NVL(f.sum_bytes, 0)) / 1048576) used_mb, ROUND(NVL(f.sum_bytes, 0) / 1048576) free_mb, t.initial_extent, t.next_extent, t.min_extents, t.max_extents, t.pct_increase, d.file_name, d.file_id, d.autoextensible, d.maxblocks, d.maxbytes, nvl(d.increment_by, 0) increment_by, t.block_size FROM (SELECT tablespace_name, file_id, SUM(bytes) sum_bytes FROM DBA_FREE_SPACE GROUP BY tablespace_name, file_id)…

Value Name(s) Table method (TM lock) 0 No lock n/a 1 Null lock (NL) Used during some parallel DML operations (e.g. update) by the pX slaves while the QC is holding an exclusive lock. 2 Sub-share (SS) Until 9.2.0.5/6 “select for update” Row-share (RS) Since 9.2.0.1/2 used at opposite end of RI during DML Lock…

From Vinod Haval‘s <Inside Overview of ASM Metadata> These Views helps in understanding the following metrics Physical Mapping Provides Undocumented Information 18 X$ Tables (May be more) TABLE NAME DESCRIPTION X$KFALS This table gives the details about aliases  created in ASM X$KFCBH This is similar to x$kfbh and have same number  of rows as x$kfbh…

View: X$KCCRS [K]ernel [C]ache [C]ontrolfile management controlfile [R]ecord [S]ection directory Column Type Description ——– —- ———– ADDR RAW(4) address of this row/entry in the SGA INDX NUMBER control file record type The following are the non-circular-reuse record types: KCCDEDBI 0 DataBase Info record KCCDECKP 1 Checkpoint progress KCCDERTH 2 Redo THread record KCCDELOG 3 LOgFile…

Error:  ORA 10051 Text:   trace OPI calls ——————————————————————————- Explanation: This is NOT an error but is a special EVENT code. It should *NOT* be used unless explicitly requested by RD support. Event 10051 allows you to track OPI calls on the server side. This can be useful to home in on what sequence of events…

Event:10235 ~~~~~~~~~~~ Version/Use: 7.0 – 10.1.X Check memory manager internal structures. 7.0 – 10.1.X “Check memory manager internal structures” ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ NOTE: Events should NEVER be set by customers unless advised to do so by Oracle Support Services. Read [NOTE:75713.1] before setting any event. Summary Syntax: ~~~~~~~~~~~~~~~ EVENT=”10235 trace name context forever, level LL” (Always comment…