Set - 4

Question 1 :

What is the use of FILE option in IMP command ?

Answer :

The name of the file from which import should be performed.

Question 2 :

What is a Shared SQL pool?

Answer :

The data dictionary cache is stored in an area in SGA called the Shared SQL Pool. This will allow sharing of parsed SQL statements among concurrent users.

Question 3 :

What is hot backup and how it can be taken?

Answer :

Taking backup of archive log files when database is open. For this the ARCHIVELOG mode should be enabled. The following files need to be backed up. All data files. All Archive log, redo log files. All control files.

Question 4 :

List the Optional Flexible Architecture (OFA) of Oracle database? or How can we organize the tablespaces in Oracle database to have maximum performance ?

Answer :

SYSTEM - Data dictionary tables.
DATA - Standard operational tables.
DATA2- Static tables used for standard operations
INDEXES - Indexes for Standard operational tables.
INDEXES1 - Indexes of static tables used for standard operations.
TOOLS - Tools table.
TOOLS1 - Indexes for tools table.
RBS - Standard Operations Rollback Segments,
RBS1,RBS2 - Additional/Special Rollback segments.
TEMP - Temporary purpose tablespace
TEMP_USER - Temporary tablespace for users.
USERS - User tablespace.

Question 5 :

How to implement the multiple control files for an existing database ?

Answer :

Shutdown the database Copy one of the existing control file to new location Edit Config ora file by adding new control file. name Restart the database.

Question 6 :

What is advantage of having disk shadowing/ Mirroring ?

Answer :

Shadow set of disks save as a backup in the event of disk failure. In most Operating System if any disk failure occurs it automatically switchover to place of failed disk. Improved performance because most OS support volume shadowing can direct file I/O request to use the shadow set of files instead of the main set of files. This reduces I/O load on the main set of disks.

Question 7 :

How will you force database to use particular rollback segment ?

Answer :


Question 8 :

Why query fails sometimes ?

Answer :

Rollback segment dynamically extent to handle larger transactions entry loads. A single transaction may wipeout all available free space in the Rollback Segment Tablespace. This prevents other user using Rollback segments.

Question 9 :

What is the use of RECORD LENGTH option in EXP command ?

Answer :

Record length in bytes.

Question 10 :

How will you monitor rollback segment status ?

Answer :

Querying the DBA_ROLLBACK_SEGS view
IN USE - Rollback Segment is on-line.
AVAILABLE - Rollback Segment available but not on-line.
OFF-LINE - Rollback Segment off-line
INVALID - Rollback Segment Dropped.
NEEDS RECOVERY - Contains data but need recovery or corupted.
PARTLY AVAILABLE - Contains data from an unresolved transaction involving a distributed database.

Question 11 :

What is meant by Redo Log file mirroring ? How it can be achieved?

Answer :

Process of having a copy of redo log files is called mirroring. This can be achieved by creating group of log files together, so that LGWR will automatically writes them to all the members of the current on-line redo log group. If any one group fails then database automatically switch over to next group. It degrades performance.

Question 12 :

Which parameter in Storage clause will reduce no. of rows per block?

Answer :

PCTFREE parameter
Row size also reduces no of rows per block.

Question 13 :

What is meant by recursive hints ?

Answer :

Number of times processes repeatedly query the dictionary table is called recursive hints. It is due to the data dictionary cache is too small. By increasing the SHARED_POOL_SIZE parameter we can optimize the size of Data Dictionary Cache.

Question 14 :

What is the use of PARFILE option in EXP command ?

Answer :

Name of the parameter file to be passed for export.

Question 15 :

What is the difference between locks, latches, enqueues and semaphores? (for DBA)

Answer :

A latch is an internal Oracle mechanism used to protect data structures in the SGA from simultaneous access. Atomic hardware instructions like TEST-AND-SET is used to implement latches. Latches are more restrictive than locks in that they are always exclusive. Latches are never queued, but will spin or sleep until they obtain a resource, or time out.
Enqueues and locks are different names for the same thing. Both support queuing and concurrency. They are queued and serviced in a first-in-first-out (FIFO) order.
Semaphores are an operating system facility used to control waiting. Semaphores are controlled by the following Unix parameters: semmni, semmns and semmsl. Typical settings are:
semmns = sum of the "processes" parameter for each instance
(see init.ora for each instance)
semmni = number of instances running simultaneously;
semmsl = semmns

Question 16 :

What is a logical backup?

Answer :

Logical backup involves reading a set of database records and writing them into a file. Export utility is used for taking backup and Import utility is used to recover from backup.

Question 17 :

Where can one get a list of all hidden Oracle parameters? (for DBA)

Answer :

Oracle initialization or INIT.ORA parameters with an underscore in front are hidden or unsupported parameters. One can get a list of all hidden parameters by executing this query:
select *
where substr(KSPPINM,1,1) = '_';
The following query displays parameter names with their current value:
select a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value"
from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and substr(ksppinm,1,1)='_'
order by a.ksppinm;
Remember: Thou shall not play with undocumented parameters!

Question 18 :

What is a database EVENT and how does one set it? (for DBA)

Answer :

Oracle trace events are useful for debugging the Oracle database server. The following two examples are simply to demonstrate syntax. Refer to later notes on this page for an explanation of what these particular events do.
Either adding them to the INIT.ORA parameter file can activate events. E.g.
event='1401 trace name errorstack, level 12'
... or, by issuing an ALTER SESSION SET EVENTS command: E.g.
alter session set events '10046 trace name context forever, level 4';
The alter session method only affects the user's current session, whereas changes to the INIT.ORA file will affect all sessions once the database has been restarted.

Question 19 :

What is a Rollback segment entry ?

Answer :

It is the set of before image data blocks that contain rows that are modified by a transaction. Each Rollback Segment entry must be completed within one rollback segment. A single rollback segment can have multiple rollback segment entries.

Question 20 :

What database events can be set? (for DBA)

Answer :

The following events are frequently used by DBAs and Oracle Support to diagnose problems:
" 10046 trace name context forever, level 4 Trace SQL statements and show bind variables in trace output.
" 10046 trace name context forever, level 8 This shows wait events in the SQL trace files
" 10046 trace name context forever, level 12 This shows both bind variable names and wait events in the SQL trace files
" 1401 trace name errorstack, level 12 1401 trace name errorstack, level 4 1401 trace name processstate Dumps out trace information if an ORA-1401 "inserted value too large for column" error occurs. The 1401 can be replaced by any other Oracle Server error code that you want to trace.
" 60 trace name errorstack level 10 Show where in the code Oracle gets a deadlock (ORA-60), and may help to diagnose the problem.
The following lists of events are examples only. They might be version specific, so please call Oracle before using them:
" 10210 trace name context forever, level 10 10211 trace name context forever, level 10 10231 trace name context forever, level 10 These events prevent database block corruptions
" 10049 trace name context forever, level 2 Memory protect cursor
" 10210 trace name context forever, level 2 Data block check
" 10211 trace name context forever, level 2 Index block check
" 10235 trace name context forever, level 1 Memory heap check
" 10262 trace name context forever, level 300 Allow 300 bytes memory leak for connections
Note: You can use the Unix oerr command to get the description of an event. On Unix, you can type "oerr ora 10053" from the command prompt to get event details.

Question 21 :

How can one dump internal database structures? (for DBA)

Answer :

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'

Question 22 :

What are the different kind of export backups?

Answer :

Full back - Complete database
Incremental - Only affected tables from last incremental date/full backup date.
Cumulative backup - Only affected table from the last cumulative date/full backup date.

Question 23 :

How free extents are managed in Ver 6.0 and Ver 7.0 ?

Answer :

Free extents cannot be merged together in Ver 6.0.
Free extents are periodically coalesces with the neighboring free extent in Ver 7.0

Question 24 :

What is the use of RECORD option in EXP command?

Answer :

For Incremental exports, the flag indirects whether a record will be stores data dictionary tables recording the export.

Question 25 :

What is the use of ROWS option in EXP command ?

Answer :

Flag to indicate whether table rows should be exported. If 'N' only DDL statements for the database objects will be created.

Question 26 :

What is the use of COMPRESS option in EXP command ?

Answer :

Flag to indicate whether export should compress fragmented segments into single extents.

Question 27 :

How will you swap objects into a different table space for an existing database ?

Answer :

Export the user
Perform import using the command imp system/manager file=export.dmp indexfile=newrite.sql.
This will create all definitions into newfile.sql. Drop necessary objects.
Run the script newfile.sql after altering the tablespaces.
Import from the backup for the necessary objects.

Question 28 :

How does Space allocation table place within a block ?

Answer :

Each block contains entries as follows
Fixed block header
Variable block header
Row Header,row date (multiple rows may exists)
PCTEREE (% of free space for row updation in future)

Question 29 :

What are the factors causing the reparsing of SQL statements in SGA?

Answer :

Due to insufficient Shared SQL pool size. Monitor the ratio of the reloads takes place while executing SQL statements. If the ratio is greater than 1 then increase the SHARED_POOL_SIZE. LOGICAL & PHYSICAL ARCHITECTURE OF DATABASE.

Question 30 :

What is dictionary cache ?

Answer :

Dictionary cache is information about the databse objects stored in a data dictionary table.

Question 31 :

What is a Control file ?

Answer :

Database overall physical architecture is maintained in a file called control file. It will be used to maintain internal consistency and guide recovery operations. Multiple copies of control files are advisable.

Question 32 :

What is Database Buffers ?

Answer :

Database buffers are cache in the SGA used to hold the data blocks that are read from the data segments in the database such as tables, indexes and clusters DB_BLOCK_BUFFERS parameter in INIT.ORA decides the size.

Question 33 :

How will you create multiple rollback segments in a database ?

Answer :

Create a database which implicitly creates a SYSTEM Rollback Segment in a SYSTEM tablespace. Create a Second Rollback Segment name R0 in the SYSTEM tablespace. Make new rollback segment available (After shutdown, modify init.ora file and Start database) Create other tablespaces (RBS) for rollback segments. Deactivate Rollback Segment R0 and activate the newly created rollback segments.

Question 34 :

What is cold backup? What are the elements of it?

Answer :

Cold backup is taking backup of all physical files after normal shutdown of database. We need to take.
- All Data files.
- All Control files.
- All on-line redo log files.
- The init.ora file (Optional)

Question 35 :

What is meant by redo log buffer ?

Answer :

Changes made to entries are written to the on-line redo log files. So that they can be used in roll forward operations during database recoveries. Before writing them into the redo log files, they will first brought to redo log buffers in SGA and LGWR will write into files frequently. LOG_BUFFER parameter will decide the size.

Question 36 :

How will you estimate the space required by a non-clustered tables?

Answer :

Calculate the total header size
Calculate the available dataspace per data block
Calculate the combined column lengths of the average row
Calculate the total average row size.
Calculate the average number rows that can fit in a block
Calculate the number of blocks and bytes required for the table.
After arriving the calculation, add 10 % additional space to calculate the initial extent size for a working table.

Question 37 :

How will you monitor the space allocation ?

Answer :

By querying DBA_SEGMENT table/view.

Question 38 :

What is meant by free extent ?

Answer :

A free extent is a collection of continuous free blocks in tablespace. When a segment is dropped its extents are reallocated and are marked as free.

Question 39 :

What is the use of IGNORE option in IMP command ?

Answer :

A flag to indicate whether the import should ignore errors encounter when issuing CREATE commands.

Question 40 :

What is the use of ANALYSE ( Ver 7) option in EXP command ?

Answer :

A flag to indicate whether statistical information about the exported objects should be written to export dump file.

Question 41 :

What is the use of ROWS option in IMP command ?

Answer :

A flag to indicate whether rows should be imported. If this is set to 'N' then only DDL for database objects will be executed.

Question 42 :

What is the use of INDEXES option in EXP command ?

Answer :

A flag to indicate whether indexes on tables will be exported.

Question 43 :

What is the use of INDEXES option in IMP command ?

Answer :

A flag to indicate whether import should import index on tables or not.

Question 44 :

What is the use of GRANT option in EXP command?

Answer :

A flag to indicate whether grants on databse objects will be exported or not. Value is 'Y' or 'N'.

Question 45 :

What is the use of GRANT option in IMP command ?

Answer :

A flag to indicate whether grants on database objects will be imported.

Question 46 :

What is the use of FULL option in EXP command ?

Answer :

A flag to indicate whether full databse export should be performed.

Question 47 :

What is the use of SHOW option in IMP command ?

Answer :

A flag to indicate whether file content should be displayed or not.

Question 48 :

What is the use of CONSTRAINTS option in EXP command ?

Answer :

A flag to indicate whether constraints on table need to be exported.

Question 49 :

What is the use of CONSISTENT (Ver 7) option in EXP command ?

Answer :

A flag to indicate whether a read consistent version of all the exported objects should be maintained.

Question 50 :

What are the different methods of backing up oracle database ?

Answer :

- Logical Backups
- Cold Backups
- Hot Backups (Archive log)

Question 51 :

What is the difference between ON-VALIDATE-FIELD trigger and a POST-CHANGE trigger ?

Answer :

When you changes the Existing value to null, the On-validate field trigger will fire post change trigger will not fire. At the time of execute-query post-change trigger will fire, on-validate field trigger will not fire.

Question 52 :

When is PRE-QUERY trigger executed ?

Answer :

When Execute-query or count-query Package procedures are invoked.

Question 53 :

How do you trap the error in forms 3.0 ?

Answer :

using On-Message or On-Error triggers.

Question 54 :

How many pages you can in a single form ?

Answer :


Question 55 :

While specifying master/detail relationship between two blocks specifying the join condition is a must ?
True or False. ?

Answer :


Question 56 :

EXIT_FORM is a restricted package procedure ?
a. True b. False

Answer :


Question 57 :

What is the usage of an ON-INSERT,ON-DELETE and ON-UPDATE TRIGGERS ?

Answer :

These triggers are executes when inserting, deleting and updating operations are performed and can be used to change the default function of insert, delete or update respectively. For Eg, instead of inserting a row in a table an existing row can be updated in the same table.

Question 58 :

What are the types of Pop-up window ?

Answer :

the pop-up field editor
pop-up list of values
pop-up pages.
Alert :

Question 59 :

What is an SQL *FORMS ?

Answer :

SQL *forms is 4GL tool for developing and executing; Oracle based interactive application.

Question 60 :

How do you control the constraints in forms ?

Answer :

Select the use constraint property is ON Block definition screen.

Question 61 :

What is the difference between restricted and unrestricted package procedure ?

Answer :

Restricted package procedure that affects the basic functions of SQL * Forms. It cannot used in all triggers except key triggers. Unrestricted package procedure that does not interfere with the basic functions of SQL * Forms it can be used in any triggers.

Question 62 :

A query fetched 10 records How many times does a PRE-QUERY Trigger and POST-QUERY Trigger will get executed ?

Answer :

PRE-QUERY fires once.
POST-QUERY fires 10 times.

Question 63 :

Give the sequence in which triggers fired during insert operations, when the following 3 triggers are defined at the same block level ?

Answer :


Question 64 :

State the order in which these triggers are executed ?

Answer :


Question 65 :

What the PAUSE package procedure does ?

Answer :

Pause suspends processing until the operator presses a function key

Question 66 :

What do you mean by a page ?

Answer :

Pages are collection of display information, such as constant text and graphics

Question 67 :

What are the type of User Exits ?

Answer :

ORACLE Precompliers user exits
OCI (ORACLE Call Interface)
Non-ORACEL user exits.
Page :

Question 68 :

What is the difference between an ON-VALIDATE-FIELD trigger and a trigger ?

Answer :

On-validate-field trigger fires, when the field Validation status New or changed. Post-field-trigger whenever the control leaving form the field, it will fire.

Question 69 :

Can we use a restricted package procedure in ON-VALIDATE-FIELD Trigger ?

Answer :


Question 70 :

Is a Key startup trigger fires as result of a operator pressing a key explicitly ?

Answer :


Question 71 :

Can we use GO-BLOCK package in a pre-field trigger ?

Answer :


Question 72 :

Can we create two blocks with the same name in form 3.0 ?

Answer :


Question 73 :

What does an on-clear-block Trigger fire?

Answer :

It fires just before SQL * forms the current block.

Question 74 :

Name the two files that are created when you generate the form give the filex extension ?

Answer :

INP (Source File)
FRM (Executable File)

Question 75 :

What package procedure used for invoke sql *plus from sql *forms ?

Answer :

Host (E.g. Host (sqlplus))

Question 76 :

What is the significance of PAGE 0 in forms 3.0 ?

Answer :

Hide the fields for internal calculation.

Question 77 :

What are the different types of key triggers ?

Answer :

Function Key

Question 78 :

What is the difference between a Function Key Trigger and Key Function Trigger ?

Answer :

Function key triggers are associated with individual SQL*FORMS function keys You can attach Key function triggers to 10 keys or key sequences that normally do not perform any SQL * FORMS operations. These keys referred as key F0 through key F9.

Question 79 :

Committed block sometimes refer to a BASE TABLE ?

Answer :


Question 80 :

Error_Code is a package proecdure ?
a. True b. false

Answer :


Question 81 :

When is cost based optimization triggered? (for DBA)

Answer :

It's important to have statistics on all tables for the CBO (Cost Based Optimizer) to work correctly. If one table involved in a statement does not have statistics, Oracle has to revert to rule-based optimization for that statement. So you really want for all tables to have statistics right away; it won't help much to just have the larger tables analyzed.
Generally, the CBO can change the execution plan when you:
1. Change statistics of objects by doing an ANALYZE;
2. Change some initialization parameters (for example: hash_join_enabled, sort_area_size, db_file_multiblock_read_count).

Question 82 :

How can one optimize %XYZ% queries? (for DBA)

Answer :

It is possible to improve %XYZ% queries by forcing the optimizer to scan all the entries from the index instead of the table. This can be done by specifying hints. If the index is physically smaller than the table (which is usually the case) it will take less time to scan the entire index than to scan the entire table.

Question 83 :

What Enter package procedure does ?

Answer :

Enter Validate-data in the current validation unit.

Question 84 :

Where can one find I/O statistics per table? (for DBA)

Answer :

The UTLESTAT report shows I/O per tablespace but one cannot see what tables in the tablespace has the most I/O. The $ORACLE_HOME/rdbms/admin/catio.sql script creates a sample_io procedure and table to gather the required information. After executing the procedure, one can do a simple SELECT * FROM io_per_object; to extract the required information. For more details, look at the header comments in the $ORACLE_HOME/rdbms/admin/catio.sql script.

Question 85 :

My query was fine last week and now it is slow. Why? (for DBA)

Answer :

The likely cause of this is because the execution plan has changed. Generate a current explain plan of the offending query and compare it to a previous one that was taken when the query was performing well. Usually the previous plan is not available.
Some factors that can cause a plan to change are:
. Which tables are currently analyzed? Were they previously analyzed? (ie. Was the query using RBO and now CBO?)
. Has OPTIMIZER_MODE been changed in INIT.ORA?
. Has the DEGREE of parallelism been defined/changed on any table?
. Have the tables been re-analyzed? Were the tables analyzed using estimate or compute? If estimate, what percentage was used?
. Have the statistics changed?
. Has the INIT.ORA parameter DB_FILE_MULTIBLOCK_READ_COUNT been changed?
. Has the INIT.ORA parameter SORT_AREA_SIZE been changed?
. Have any other INIT.ORA parameters been changed?
. What do you think the plan should be? Run the query with hints to see if this produces the required performance.

Question 86 :

Why is Oracle not using the damn index? (for DBA)

Answer :

This problem normally only arises when the query plan is being generated by the Cost Based Optimizer. The usual cause is because the CBO calculates that executing a Full Table Scan would be faster than accessing the table via the index.
Fundamental things that can be checked are:
. USER_TAB_COLUMNS.NUM_DISTINCT - This column defines the number of distinct values the column holds.
. USER_TABLES.NUM_ROWS - If NUM_DISTINCT = NUM_ROWS then using an index would be preferable to doing a FULL TABLE SCAN. As the NUM_DISTINCT decreases, the cost of using an index increase thereby is making the index less desirable.
. USER_INDEXES.CLUSTERING_FACTOR - This defines how ordered the rows are in the index. If CLUSTERING_FACTOR approaches the number of blocks in the table, the rows are ordered. If it approaches the number of rows in the table, the rows are randomly ordered. In such a case, it is unlikely that index entries in the same leaf block will point to rows in the same data blocks.
. Decrease the INIT.ORA parameter DB_FILE_MULTIBLOCK_READ_COUNT - A higher value will make the cost of a FULL TABLE SCAN cheaper.
. Remember that you MUST supply the leading column of an index, for the index to be used (unless you use a FAST FULL SCAN or SKIP SCANNING).
. There are many other factors that affect the cost, but sometimes the above can help to show why an index is not being used by the CBO. If from checking the above you still feel that the query should be using an index, try specifying an index hint. Obtain an explain plan of the query either using TKPROF with TIMED_STATISTICS, so that one can see the CPU utilization, or with AUTOTRACE to see the statistics. Compare this to the explain plan when not using an index.

Question 87 :

When should one rebuild an index? (for DBA)

Answer :

You can run the 'ANALYZE INDEX VALIDATE STRUCTURE' command on the affected indexes - each invocation of this command creates a single row in the INDEX_STATS view. This row is overwritten by the next ANALYZE INDEX command, so copy the contents of the view into a local table after each ANALYZE. The 'badness' of the index can then be judged by the ratio of 'DEL_LF_ROWS' to 'LF_ROWS'.

Question 88 :

What are the unrestricted procedures used to change the popup screen position during run time ?

Answer :

Resize -View

Question 89 :

What is an Alert ?

Answer :

An alert is window that appears in the middle of the screen overlaying a portion of the current display.

Question 90 :

Deleting a page removes information about all the fields in that page ?
a. True. b. False

Answer :

a. True.

Question 91 :

Two popup pages can appear on the screen at a time ?Two popup pages can appear on the screen at a time ?
a. True. b. False?

Answer :

a. True.

Question 92 :

Classify the restricted and unrestricted procedure from the following.
a. Call
b. User-Exit
c. Call-Query
d. Up
e. Execute-Query
f. Message
g. Exit-From
h. Post
i. Break?

Answer :

a. Call - unrestricted
b. User Exit - Unrestricted
c. Call_query - Unrestricted
d. Up - Restricted
e. Execute Query - Restricted
f. Message - Restricted
g. Exit_form - Restricted
h. Post - Restricted
i. Break - Unrestricted.

Question 93 :

What is an User Exits ?

Answer :

A user exit is a subroutine which are written in programming languages using pro*C pro *Cobol , etc., that link into the SQL * forms executable.

Question 94 :

What is a Trigger ?

Answer :

A piece of logic that is executed at or triggered by a SQL *forms event.

Question 95 :

What is a Package Procedure ?

Answer :

A Package procedure is built in PL/SQL procedure.

Question 96 :

What is the maximum size of a form ?

Answer :

255 character width and 255 characters Length.

Question 97 :

What is the difference between system.current_field and system.cursor_field ?

Answer :

1. System.current_field gives name of the field.
2. System.cursor_field gives name of the field with block name.

Question 98 :

List the system variables related in Block and Field?

Answer :

1. System.block_status
2. System.current_block
3. System.current_field
4. System.current_value
5. System.cursor_block
6. System.cursor_field
7. System.field_status.

Question 99 :

What are the different types of Package Procedure ?

Answer :

1. Restricted package procedure.
2. Unrestricted package procedure.

Question 100 :

What are the types of TRIGGERS ?

Answer :

1. Navigational Triggers.
2. Transaction Triggers.