Technical Sample Questions : C |  C++ |  Oracle |  Java | Unix |  Operating Systems |  Data Structure

Oracle Sample Questions ›› Oracle Database Administration

Technical Sample Questions

Oracle Sample Questions : Oracle Database Administration

Following are Oracle sample questions on Oracle Database Administration.

1. How can I dump internal database structures?

   Answer: 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'

   /

2. What database events can be set?

   Answer: # 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"

3. Is there any undocumented commands in Oracle?

   Answer: Yes there is

   • In Server Manager from Oracle7.3: ORADEBUG HELP

     It looks like one can change memory locations with the ORADEBUG POKE command, anyone brave enough to test this one for us?

   • SQL*Plus: ALTER SESSION SET CURRENT_SCHEMA = SYS;

4. How can I prevent tablespace fragmentation?

   Answer: Always set PCTINCREASE to 0 or 100.

5. What can I do about ORA-600 Space Leaks?

   Answer: 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"

6. What database block size should I use?

   Answer: 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).

7. Can one rename a database user (schema)?

   Answer: No

8. Can one rename a tablespace's name?

   Answer: No

9. Can one manually resize a datafile?

   Answer: You can manually increase or decrease the size of a datafile in Oracle 7.2 using the

   ALTER DATABASE DATAFILE 'filename2' RESIZE 100M;

   command.

   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.

10. How does one create a standby database?

    Answer: 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;