EVENT:10211 check index block integrity

Event:10211                  See <Note:32969.1> for similar events / parameters
~~~~~~~~~~~                    

Version/Use:
  7.0 - 8.1.7  Check data block integrity after each modification
  9.0+         This event is no longer valid. Use <Parameter:DB_BLOCK_CHECKING>
                instead.

7.0 - 8.1.7 "Check data block integrity after each modification"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  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:
  This event should be set at instance level:
    EVENT="10211 trace name context forever, level 10"

Levels:
  This event is either ON (level 1 or higher) or OFF (not set)

  Note that in 8.1.6 onwards you should use the <Parameter:DB_BLOCK_CHECKING>
  instead of this event.

Description/Steps:
  Event:10211 enables index block integrity checking.
  The causes the index layer to perform a number of checks on the contents
  of a index block. If the block is found to be corrupt in some way it is
  marked as SOFTWARE CORRUPT.

  NB: Blocks are ONLY checked/corrupted when CHANGED. They are NOT checked
      when accessed for read.                                   

  Take care with this as if there are blocks in the database which
  are being accessed happily but have a minor corruption this will
  mark the block as corrupt.

  Once a block in an index is marked SOFTWARE CORRUPT it is best to
  rebuild the entire index.

*** WARNING:
        Releases with <Bug:792610> are best to keep this event disabled
        as an index corruption can lead to rollback segment corruption
        which is written to the redo stream.

  See <Note:32969.1> for more detail of various block checking features.

Example Output / Interpreting Output:
  This event will cause an ORA-600 error to be signalled if a corruption
  is detected after a data block has been modified. The exact content
  of the trace file depends on the ORA-600 and the action at the time
  of the corruption being noticed. Refer to the relevant ORA-600 article
  for the error which is signalled.

Related:
  Database block checking features                          <Note:32969.1>
  Table and Cluster block equivalents          <Event:10210> <Event:10212>

EVENT: 10060 dump predicates in optimizer (kko)

Event:10060
Text:   dump predicates in optimizer (kko)
-------------------------------------------------------------------------------
Explanation:
        This is a special EVENT code.
        It should *NOT* be used unless explicitly requested by ST support.
        This event can be useful in conjunction with an explain plan
        to confirm which operations occur where in the execution plan.
        It dumps the predicates into a special table.
Usage:
        - Available in 7.1.3 onwards when using CBO
        - NOTE: This does NOT do anything under RULE
        - After 7.1.3 you must create a special table called
          "kkoipt_table" before you set the event. This table should be
          in the schema of the person running under event 10060.
    
            create table kkoipt_table (
                    c1 integer, c2 varchar2(80)
            );            
        - To enable tracing:
            ALTER session set events '10060 trace name context forever';
        - Run the statement to be traced
        - Set formatting:
          set pages 999
          column c1 format 990
          column c2 format a75
        - select * from KKOIPT_TABLE;
        - Best used in conjunction with <Event:10053> trace and an
          execution plan.
KKOIPT_TABLE.C2 output information
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
fptconst                      - Folding constants
fptrnum                       - Remove rownum predicates
fptwhr                        - Remove all where predicates except remaining
                                rownum predicates
frofkks (rowid lookup)        - Rowid Lookup
frofkks[i] (and-equal lookup) - Start Key (And-Equal)
frofkke[i] (and-equal lookup) - End Key   (And-Equal)
froiand                       - Index only predicate
frofkksm[i] (sort-merge)      - Sort-Merg Key
frosand (sort-merge)          - Sort-Merge Predicates
frojand (sort-merge)          - Join Predicates
frofkks[i] (index start key)  - Index Start Key
frofkke[i] (index stop key)   - Index End Key
frofand (hash part)           - Table Predicate (Hash)
froiand (index only filter)   - Index Only Predicate
frofand                       - Table Predicate
froutand                      - Outer Join Predicates
Example:
~~~~~~~
select a.* from memp a, memp b
where a.sal > 5050 and rownum < 4 and 1=1
  and a.empno (+) = b.empno;
Output: (from kkoipt_table)
         1 fptrnum
         2  4>ROWNUM
         3 Table:
         4 MEMP
         5 frofand
         6  "A"."SAL">5050
         7 Table:
         8 MEMP
         9 frofand
        10  "A"."EMPNO"="B"."EMPNO"

EVENT:10218 dump uba of applied undo

@23-JAN-01, rpowell.uk, Distribution justification: "Should not be used by
@ customers unless told to by support so making noncustomer-viewable." - dbither.us
Event: 10218
Text:  dump uba of applied undo
-------------------------------------------------------------------------------
Note:
   Events should NEVER be set by customers unless advised to do so by
   Oracle Support Services.  See <Note:75713.1> for more details.
Description:
   This event simply shows the undo block addresses for the undo
   applied during recovery.  This can be useful to see the progress of
   the rolling out of transactions, and to help identify where a rollback
   has gotten stuck.
Example output:
         uba: 80000ca.0d.12
         uba: 80000ca.0d.11
         uba: 80000ca.0d.10
         uba: 80000ca.0d.0f
         uba: 80000ca.0d.0e
         uba: 80000ca.0d.0d
         uba: 80000ca.0d.0c
         uba: 80000ca.0d.0b

EVENT:10226 trace CR applications of undo for data

Error:  ORA 10226
Text:   trace CR applications of undo for data operations
-------------------------------------------------------------------------------
Explanation:
This is NOT an error but is a special EVENT code. It can be used
to signal Oracle to perform SQL_TRACE actions.
This event causes application of UNDO for producing CR copies of
blocks. It can produce a lot of output (in the form of REDO
application dump to rewind the block)

EVENT:10061 disable SMON from cleaning temp segment

Error:  ORA 10061
Text:   disable SMON from cleaning temp segment
-------------------------------------------------------------------------------
Explanation:
        This is NOT an error but is a special EVENT code.
        It should *NOT* be used unless explicitly requested by RD support.
        It allows you to prevent SMON from cleaning up temporary segments.
        If set in:
         7.0.X:
            This will prevent SMON performing cleanup of TEMP segments
            and from coalsecing extents. The cleanup action was performed
            every 3 minutes in 7.0.X releases.
        7.1.X:
            The event behaviour is modified in that TEMP segment cleanup
            is skipped on TIMEOUTS but will be performed if SMON is
            posted. The cleanup only occurs every 12 hours or when posted
            in 7.1.X releases.
            You can POST SMON by performing a failed CREATE TABLE command.
            A new event 10269 is for coalescing extents.
Usage:  
        event="10061 trace name context forever, level 10"
Articles:
@Archived Managing SMON Activity on multiple OPS instances   <Note:45856.1>

EVENT:10212 check cluster integrity

Error:  ORA 10212
Text:   check cluster integrity
-------------------------------------------------------------------------------
Explanation:
This is NOT an error but is a special EVENT code.
It should *NOT* be used unless explicitly requested by RD support.
Event 10212 enables cluster block integrity checking.
The causes a number of checks on the contents of a block.
If the block is found to be corrupt in some way it is marked as
SOFTWARE CORRUPT.
NB: Blocks are ONLY checked/corrupted when CHANGED. They are NOT checked
when accessed for read.
Take care with this as if there are blocks in the database which
are being accessed happily but have a minor corruption this will
mark the block as corrupt.
Once a block in an cluster is marked SOFTWARE CORRUPT it is best to
rebuild the entire cluster.
Usage:  event="10212 trace name context forever, level 10"
Articles:
Data and Index block equivalents  <Oerr:10210> <Oerr:10211>

EVENT:10228 trace application of redo by kcocbk

Error:  ORA 10228
Text:   trace application of redo by kcocbk
-------------------------------------------------------------------------------
Explanation:
This is NOT an error but is a special EVENT code.
It should *NOT* be used unless explicitly requested by RD support.
Event 10228 is potentially useful in recovery situations since it
traces the application of redo. Therefore, if cache recovery
appears to hang or corrupt blocks then this can help track the dba
and operations upon it.
This event is available in Oracle 6 and 7.
The code just calls the same routines used to dump a redo log using
'alter system dump logfile' so has the same output.
The benefit is that it'll stop when it hits the corruption.
Articles:
Interpreting DUMP LOGFILE Output <Note:29726.1>

EVENT:10210 check data block integrity

Event:10210                  See  for similar events / parameters
~~~~~~~~~~~
Version/Use:
7.0 - 8.1.7  Check data block integrity after each modification
9.0+         This event is no longer valid. Use <Parameter:DB_BLOCK_CHECKING>
instead.
7.0 - 8.1.7 "Check data block integrity after each modification"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
NOTE: Events should NEVER be set by customers unless advised to do so by
Oracle Support Services. Read  before setting any event.
Summary Syntax:
This event should be set at instance level:
EVENT="10210 trace name context forever, level 10"
Levels:
This event is either ON (level 1 or higher) or OFF (not set)
Note that in 8.1.6 onwards you should use the
instead of this event.
Description/Steps:
Event 10210 enables data block integrity checking.
This causes the data layer to perform a number of checks on the
contents of a data block. If the block is found to be corrupt
in some way it is marked as SOFTWARE CORRUPT.
NB: Blocks are ONLY checked/corrupted when CHANGED. They are NOT
checked when accessed for read.
Take care with this, as if there are blocks in the database which
are being accessed happily but have a minor corruption such as
an incorrect free space count, this event will mark the block as
corrupt. Once marked SOFTWARE CORRUPT the data in the block
is not accessible to Oracle.
See  for more detail of various block checking features.
Example Output / Interpreting Output:
This event will cause an ORA-600 error to be signalled if a corruption
is detected after a data block has been modified. The exact content
of the trace file depends on the ORA-600 and the action at the time
of the corruption being noticed. Refer to the relevant ORA-600 article
for the error which is signalled.
Related:
Database block checking features
Index and Cluster block equivalents
See event 10231 which allows SOFTWARE CORRUPT blocks
to be skipped on full table scans

Known Oracle Internal Stack Call Meaning

 ksedmp             # KSE: dump the process state
ksfdmp             # Call relevant dump routine
kgeasi             # Raise an error on an ASSERTION failure (IGNORE)
ktcrab             KTC: Kernel Transaction Control Real ABort - Abort a
transaction.
ktcsod             KTC: Transaction Control: STATE OBJECT PROCEDURE VECTOR
DEFINITION
kssdch_stage       
kssdch             KSS: delete children of state obj.
ksures             
ktmres             ktmres - KTM Resource cleanup routine.
ktmmon             KTM: TX Monitor: background timeout action
ksbrdp             KSB: run a detached (background) process
opirip             # Oracle Program Interface Run Independent Process
(IGNORE)
opidrv             # opidrv - ORACLE Program Interface DRiVer (IGNORE)
sou2o              # Main Oracle executable entry point
main               # Standard executable entry point

Function Based Indexes and Global Temporary Tables

A nonunique index can be used to enforce a primary key or unique constraint.

In Oracle8i indexes can be rebuilt without locking the table.

The DROP COLUMN option of the ALTER TABLE command is restartable.

The MOVE option of the ALTER TABLE command retains the constraints of the table.

Data rows in the global temporary table are always deleted when A user session is terminated

1.    As user Scott, create a table with three columns.  Create an index on all three columns in the order they appear in the table.  Then add a primary key constraint using the first two columns with the second column of the table appearing first.  Verify that only one index, the index being used to enforce the constraint, has been defined for the table.

Hint: if the columns in the table were labeled A, B, and C, the index would be on (A, B, C) while the constraint would be on columns (B, A).

Solution:

 

connect scott/tiger

 

 

CREATE TABLE acct

( acct_no       NUMBER(10),

customer_id           NUMBER(10),

acct_comment VARCHAR2(200),

CONSTRAINT pk_cid_aid  PRIMARY KEY(customer_id, acct_no) DISABLE

)

/

CREATE INDEX I_ANO_CNO_ACOMM

ON acct(acct_no, customer_id, acct_comment)

ONLINE

/

ALTER TABLE acct

ENABLE CONSTRAINT pk_cid_aid

/

 

select index_name, table_name from user_indexes

where table_name = ‘ACCT’

/

2. As user Scott create a table containing three columns.  Remove the third column using one of the new methods introduced in Oracle8i.  Verify that the column is no longer part of the table.

Solution:

connect scott/tiger

 

 

CREATE TABLE acct_col

( acct_col_no         NUMBER(10),

customer_id           NUMBER(10),

acct_col_comment    VARCHAR2(200)

)

/

ALTER TABLE acct_col

SET UNUSED COLUMN acct_col_comment

/

 

desc acct_col

 

SELECT * FROM user_unused_col_tabs

/

ALTER TABLE acct_col

DROP UNUSED COLUMNS

/

SELECT * FROM user_unused_col_tabs

/

 

3. As user SYS create a global temporary table containing three columns.  The inserted rows should remain available until explicitly deleted or the session ends.  Make the table available to anyone who wishes to use it.  The users should not have to know the table owner in order to make use of it.

Solution:

connect / as sysdba

 

CREATE GLOBAL TEMPORARY TABLE emp_temp_X

(eno NUMBER,

ename VARCHAR2(20),

sal NUMBER)

ON COMMIT PRESERVE ROWS;

 

connect / AS SYSDBA

 

CREATE PUBLIC SYNONYM emp_temp for emp_temp_x

/

GRANT ALL ON emp_temp TO PUBLIC

/

col object_name format a20

SELECT owner, object_name, object_type FROM dba_objects

WHERE object_name LIKE ‘%EMP_TEMP%’

/

connect scott/tiger

 

desc emp_temp

 

select * from emp_temp

/

 

 

沪ICP备14014813号

沪公网安备 31010802001379号