Alter system set events

Format: Alter system | session set events '[eventnumber | immediate] Trace name eventname [forever] [, level levelnumber]:... '
Through the symbol, you can set multiple events consecutively, or you can use alter session set events consecutively to set multiple events.

Format description: eventnumber refers to the event number that triggers dump. The event number can be an oracle error number (the specified event is tracked when an error occurs) or an internal oralce event number, the internal event number is between 10000 and 10999, and cannot be used with the immediate keyword.

The immediate keyword indicates that the specified structure is immediately dumped to the trace file after the command is issued. This keyword is only used in the alter session Statement and cannot be used with the eventnumber or forever keyword.

Trace name is a keyword
Eventname refers to the event name (see the following section), that is, the actual structure name for dump. If eventname is context, internal event numbers are used for tracking.
The forever keyword indicates that the event remains valid during the instance or session period and cannot be used with immediate.
Level is the event-level keyword. But the error stack (errorstack) does not have a level.
Levelnumber indicates the event level number. Generally, from 1 to 10, 1 indicates only the dump structure header information, and 10 indicates all the information of the dump structure.

1. buffers event: DB Buffer Structure in the dump SGA Buffer
Alter session set events 'immediate trace name buffers level 1';-indicates the dump buffer header.

2. blockdump event: dump data file, index file, and rollback segment file structure
Alter session set events 'immediate trace name blockdump level 100';-indicates that the dump block address is 66666.
After Oracle 8, the command has been changed:
Alter system dump datafile 11 Block 9;-indicates that the dump data file number is the 9th data blocks in column 11.

3. controlf event: Dump control file structure
Alter session set events 'immediate trace name controlf level 10';-indicates all contents of the dump control file.

4. Locks event: the lock information of the dump LCK Process
Alter session set events 'immediate trace name locks level 5 ′;

5. redohdr event: Dump redo log header information
Alter session set events 'immediate trace name redohdr level 1';-indicates the control file entry in the dump redo log header.
Alter session set events 'immediate trace name redohdr Level 2';-indicates the general file header of the dump redo log.
Alter session set events 'immediate trace name redohdr level 10';-indicates the full file header of the dump redo log.
Note: dump of redo logs can use the following statement:
Alter system dump logfile 'logfilename ';

6. loghist event: log history items in the dump Control File
Alter session set events 'immediate trace name loghist level 1';-indicates that only the earliest and latest log history items are dumped.
When levelnumber is greater than or equal to 2, it indicates the levelnumber of 2 to the power of log history.
Alter session set events 'immediate trace name loghist level 4';-Indicates 16 log history items in dump.

7. file_hdrs event: Dump header information of all data files
Alter session set events 'immediate trace name file_hdrs level 1';-indicates the control file items in the dump header of all data files.
Alter session set events 'immediate trace name file_hdrs Level 2';-indicates the general file header of all data files in dump.
Alter session set events 'immediate trace name file_hdrs level 10';-indicates the full file header of all data files in dump.

8. errorstack event: Dump error stack information. Generally, when an oracle error occurs, the foreground process will get an error message, but in some cases the error message is not obtained. You can use this method to get an oracle error.
Alter session set events '1970 trace name errorstack forever ';-Indicates dump error stack and process stack when a 604 error occurs.

9. systemstate event: dump all system statuses and process statuses
Alter session set events 'immediate trace name systemstate level 10';-indicates all system and process statuses of dump.

10. coalesec event: Dump specifies the free interval in the tablespace
When levelnumber is expressed in hexadecimal notation, two high bytes represent the number of free intervals, and two low bytes represent the tablespace number. For example, 0 × 00050000 represents the five free intervals in the dump system tablespace, to convert to decimal format is 327680, that is:
Alter session set events 'immediate trace name coalesec level 327680 ′;

11. processsate event: Dump Process status
Alter session set events 'immediate trace name processsate level 10 ′;

12. library_cache event: Dump library Cache Information
Alter session set events 'immediate trace name library_cache level 10 ′;

13. heapdump event: Information in dump PGA, SGA, and UGA
Alter session set events 'immediate trace name heapdump level 1 ′;

14. row_cache event: Information in the dump Data Dictionary Buffer
Alter session set events 'immediate trace name row_cache level 1 ′;

 

Common Oracle dump commands, record for future reference.
1. Memory dumps

1). Global Area
Alter session set events 'immediate trace name global_area level n ';
1 Including PGA
2 contains SGA
4 include UGA
8 contains indrect memory

2). Library Cache
Alter session set events 'immediate trace name library_cache level n ';
1. Library cache statistics
2 contains hash table Histogram
3 include object handle
4 contains the object structure (heap 0)

3). Row Cache
Alter session set events 'immediate trace name row_cache level n ';
1 row cache statistics
2 contains hash table Histogram
8 contains the object structure

4). Buffers
Alter session set events 'immediate trace name buffers level n ';
1 buffer Header
2 level 1 + block Header
3 level 2 + Block Contents
4 level 1 + Hash Chain
5 level 2 + Hash Chain
6 level 3 + Hash Chain
8 level 4 + users/waiters
9 Level 5 + users/waiters
10 level 6 + users/waiters

5). Buffer
Alter session set events 'immediate trace name buffer level n ';
N is the rdba of a specified block. This command can be used to dump all versions of a block in the buffer.

6). Heap
Alter session set events 'immediate trace name heapdump level level ';
1 PGA Summary
2 SGA Abstract
4 UGA Summary
8 current call (CGA) Abstract
16 User call (CGA) Abstract
32 large call (LGA) Abstract
1025 PGA content
2050 SGA content
4100 UGA content
8200 current call content
16400 User call content
32800 large call content

7). Sub heap
Before Oracle 9.0.1
Alter session set events 'immediate trace name heapdump_addr level n ';
If n is the subheap address, the digest information of the subheap is dumped.
If n is the subheap address + 1, the dumped content is the subheap content.
Versions later than Oracle 9.2.0
Alter session set events 'immediate trace name heapdump_addr level N, ADDR m ';
M is the subheap address.
N is the digest of 1 dump subheap, n is the content of 2 dump subheap

8). process state
Alter session set events 'immediate trace name processstate level n ';

9). system state
Alter session set events 'immediate trace name systemstate level n ';

10). error state
Alter session set events 'immediate trace name errorstack level n ';
0 error Stack
1 level 0 + function call stack
2 level 1 + process state
3 level 2 + context Area
11). Hang Analysis
Alter session set events 'immediate trace name hanganalyze level n ';

12). Work Area
Alter session set events 'immediate trace name workareatab_dump level n ';
1. SGA Information
2. workarea table Summary
3. workarea table details

13). latches
Alter session set events 'immediate trace name latches level n ';
1. Latch Information
2. Statistics

14). Events
Alter session set events 'immediate trace name events level n ';
1 session
2 Process
3 System

15). Locks
Alter session set events 'immediate trace name locks level n ';

16). Shared Server process
Alter session set events 'immediate trace name shared_server_state level n ';
N is 1 ~ 14

17). Background messages
Alter session set events 'immediate trace name bg_messages level n ';
N is PID + 1

Ii. File dumps

1). Block
Before oracle 7
Alter session set events 'immediate trace name blockdump level n ';
Rdba whose N is Block
After oracle8
Alter system dump datafile file # block #;
Alter system dump datafile file #
Block min minimum_block #
Block Max maximum_block #;

2). Tree dump
Alter session set events 'immediate trace name treedump level n ';
N is object_id

3). Undo segment Header
Alter system dump undo_header 'segment _ name ';

4). Undo for a transaction
Alter system dump undo block 'segment _ name' Xid xidusn xidslot xidsqn;

5). File Header
Alter session set events 'immediate trace name file_hdrs level n ';
1. File Header Information in the control file
2 level 1 + file header information
3 level 2 + data file header information
10 level 3

6). Control File
Alter session set events 'immediate trace name controlf level n ';
1. File Header Information
2 level 1 + database information + checkpoint Information
3 level 2 + reusable section information
10 level 3

7). redo log Header
Alter session set events 'immediate trace name redohdr level n ';
1. redo log information in the control file
2 level 1 + file header information
3 level 2 + Log File Header Information
10 level 3

8). redo log
Alter system dump logfile 'filename ';
Alter system dump logfile 'filename'
SCN min minimumscn
SCN Max maximumscn
Time min minimumtime
Time Max maximumtime
Layer Layer
Opcode
DBA min filenumber. blocknumber
DBA Max filenumber. blocknumber
RBA min logfilesequencenumber. blocknumber
RBA Max logfilesequencenumber. blocknumber;
Where time = (yyyy-1988) * 12 + mm-1) * 31 + DD-1) * 24 + HH) * 60 + MI) * 60 + SS;

9). loghist
Alter session set events 'immediate trace name loghist level n ';
1. The earliest and latest log history items in the dump Control File
1 dump 2 ^ n log history items