Oracle Interview Questions – Part 26
Q.What are the different types of Delete details we can establish in Master-Details?
Q.What is relation between the window and canvas views?
Canvas views are the back ground objects on which you place the interface items (Text items), check boxes, radio groups etc.,) and boilerplate objects (boxes, lines, images etc.,) that operators interact with us they run your form . Each canvas views displayed in a window.
Q.What is a Static Record Group?
A static record group is not associated with a query, rather, you define its structure and row values at design time, and they remain fixed at runtime.
Q.I’ve lost some Rollback Segments, how can I get my DB back? (for DBA)
Re-start your database with the following INIT.ORA parameter if one of your rollback segments is corrupted. You can then drop the corrupted rollback segments and create it from scratch.
Caution is advised when enabling this parameter, as uncommitted transactions will be marked as committed. One can very well end up with lost or inconsistent data!!! Please contact Oracle Support before using it. _Corrupted_rollback_segments = (rbs01, rbs01, rbs03, rbs04)
Q.What is the significance of having storage clause ?
We can plan the storage for a table as how much initial extents are required, how much can be extended next, how much % should leave free for managing row updations etc.,
Q.What is the use of INCTYPE option in EXP command ?
Type export should be performed COMPLETE,CUMULATIVE,INCREMENTAL. List the sequence of events when a large transaction that exceeds beyond its optimal value when an entry wraps and causes the rollback segment toexpand into anotion Completes. e. will be written.
Q.What is the difference between locks, latches, enqueues and semaphores? (for DBA)
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<instance>.ora for each instance)
semmni = number of instances running simultaneously;
semmsl = semmns
Q.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 😡 := dbms_utility.make_data_block_address(1,12);
alter session set events ‘immediate trace name blockdump level 50360894’
Q.How will you monitor the space allocation ?
By querying DBA_SEGMENT table/view.
Q.What is the use of INDEXES option in IMP command ?
A flag to indicate whether import should import index on tables or not.
Q.How do you control the constraints in forms ?
Select the use constraint property is ON Block definition screen.
Q.What is the difference between restricted and unrestricted package procedure ?
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.
Q.Error_Code is a package procedure ?
a. True b. false
Q.When is cost based optimization triggered? (for DBA)
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).
Q.Classify the restricted and unrestricted procedure from the following.
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.
Q.What is an User Exits ?
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.
Q.Is it possible to attach same library to more than one form?
Q.Can you attach an lov to a field at design time?
Q.What is a Layout Editor?
The Layout Editor is a graphical design facility for creating and arranging items and boilerplate text and graphics objects in your application’s interface.
Q.List the Types of Items?
User Area item.
Q.What are the triggers associated with a check box?
Only When-checkbox-activated Trigger associated with a Check box.
Q.What is a list item?
It is a list of text elements.
Q.What buil-in routines are used to display editor dynamically?
Q.Give built-in routine related to a record groups?
Q.What is the MultiOrg and what is it used for? (for DBA)
MultiOrg or Multiple Organizations Architecture allows multiple operating units and their relationships to be defined within a single installation of Oracle Applications. This keeps each operating unit’s transaction data separate and secure.
Use the following query to determine if MuliOrg is intalled:
select multi_org_flag from fnd_product_groups;
Q.What is the difference between Fields and FlexFields? (for DBA)
A field is a position on a form that one uses to enter, view, update, or delete information. A field prompt describes each field by telling what kind of information appears in the field, or alternatively, what kind of information should be entered in the field.
A flexfield is an Oracle Applications field made up of segments. Each segment has an assigned name and a set of valid values. Oracle Applications uses flexfields to capture information about your organization. There are two types of flexfields: key flexfields and descriptive flexfields.
Q.What is a record Group?
A record group is an internal oracle forms data structure that has a similar column/row frame work to a database table
Q.What is a Navigable item?
A navigable item is one that operators can navigate to with the keyboard during default navigation, or that Oracle forms can navigate to by executing a navigational built-in procedure.
Q.What are the components of a PL/SQL Block ?
Declarative part, Executable part and Exception part.
Q.What is a database trigger ? Name some usages of database trigger ?
Database trigger is stored PL/SQL program unit associated with a specific database table. Usages are Audit data modifications, Log events transparently, Enforce complex business rules Derive column values automatically, Implement complex security authorizations. Maintain replicate tables.