|
How can one dump internal database structures? (for DBA
ORACLE Interview Questions and Answers (Part 2)
(Continued from previous question...)
320. How can one dump internal database structures? (for DBA
The following (mostly undocumented) commands can be used to obtain information about internal database structures.
o Dump control file contents
alter session set events 'immediate trace name CONTROLF level 10'
/
o Dump file headers
alter session set events 'immediate trace name FILE_HDRS level 10'
/
o Dump redo log headers
alter session set events 'immediate trace name REDOHDR level 10'
/
o Dump the system state
NOTE: Take 3 successive SYSTEMSTATE dumps, with 10-minute intervals
alter session set events 'immediate trace name SYSTEMSTATE level 10'
/
o Dump the process state
alter session set events 'immediate trace name PROCESSSTATE level 10'
/
o Dump Library Cache details
alter session set events 'immediate trace name library cache level 10'
/
o Dump optimizer statistics whenever a SQL statement is parsed (hint: change statement or flush pool)
alter session set events '10053 trace name context forever, level 1'
/
o Dump a database block (File/ Block must be converted to DBA address)
Convert file and block number to a DBA (database block address).
Eg: variable x varchar2;
exec :x := dbms_utility.make_data_block_address(1,12);
print x
alter session set events 'immediate trace name blockdump level 50360894'
/
(Continued on next question...)
Other Interview Questions
| 