Following are Oracle sample questions on Oracle Database Administration.
- How can I dump internal database structures?
The following undocumented SQL statements can be used to obtain information about internal database structures:
-- Dump control file
alter session set events 'immediate trace name CONTROLF level 10'
-- Dump file headers
alter session set events 'immediate trace name FILE_HDRS level 10'
-- Dump redo log headers
alter session set events 'immediate trace name REDOHDR level 10'
-- Dump the system state
alter session set events 'immediate trace name SYSTEMSTATE level 10'
-- Dump optimizer statistics whenever a SQL statement is parsed
alter session set events '10053 trace name context forever'
- What database events can be set?
# prevent block corruptions
event = "10210 trace name context forever, level 10"
event = "10211 trace name context forever, level 10"
event = "10231 trace name context forever, level 10"
# performance monitoring
event = "10046 trace name context forever, level 12"
# memory protect cursor
event = "10049 trace name context forever, level 2"
# data block check
event = "10210 trace name context forever, level 2"
# index block check
event = "10211 trace name context forever, level 2"
# memory heap check
event = "10235 trace name context forever, level 1"
# allow 300 bytes memory leak for connections
event = "10262 trace name context forever, level 300"
- Is there any undocumented commands in Oracle?
Yes there is
- How can I prevent tablespace fragmentation?
Always set PCTINCREASE to 0 or 100.
- What can I do about ORA-600 Space Leaks?
You can prevent ORA-600 space leak messages during database shutdown by telling the kernel not to check for memory leakages.This undocumented feature was introduced with Oracle 7.1.6 and can be prevented by setting:
event = "10262 trace name context forever, level 1024"
in the INIT.ORA file or by executing the following SQL command:
SQL> ALTER SESSION SET EVENTS "10262 trace name context forever, level 1024"
- What database block size should I use?
Oracle recommends that your database block size match, or be multiples of your operating system block size.One can go smaller, but the performance cost is significant.Your choice should depend on the type of application you are running.If you have lots of small transaction like with OLTP, use a small block size.With fewer but larger transactions, like with a DSS application, use a large block size.
If you are using a volume manager, consider your "operating system block size" to be 8K.This is because volume manager products use 8K blocks (and this is not configurable).
- Can one rename a database user (schema)?
- Can one rename a tablespace's name?
- Can one manually resize a datafile?
You can manually increase or decrease the size of a datafile in Oracle 7.2 using the
ALTER DATABASE DATAFILE 'filename2' RESIZE 100M;
Because you can change the sizes of datafiles, you can add more space to your database without adding more datafiles.This is beneficial if you are concerned about reaching the maximum number of datafiles allowed in your database.
Manually reducing the sizes of datafiles allows you to reclaim unused space in the database.This is useful for correcting errors in estimates of space requirements.
- How does one create a standby database?
While your production database is running, take an ON-LINE backup and restore it on duplicate hardware.
On your standby database, issue the following commands:
ALTER DATABASE CREATE STANDBY CONTROLFILE AS...>br>
ALTER DATABASE MOUNT STANDBY DATABASE;
RECOVER STANDBY DATABASE;
Write a job to copy archived redo log files from your primary database to the standby system, and apply the redo log files to the standby database (pipe it).Remember the database is recovering and will prompt you for the next log file to apply.
When you need the standby database stop the recovery process and activate it:
ALTER DATABASE ACTIVATE STANDBY DATABASE;