Oracle Interview Questions – Part 25
Oracle Financials products provide organizations with solutions to a wide range of long- and short-term accounting system issues. Regardless of the size of the business, Oracle Financials can meet accounting management demands with:
Oracle Assets: Ensures that an organization’s property and equipment investment is accurate and that the correct asset tax accounting strategies are chosen.
Oracle General Ledger: Offers a complete solution to journal entry, budgeting, allocations, consolidation, and financial reporting needs.
Oracle Inventory: Helps an organization make better inventory decisions by minimizing stock and maximizing cash flow.
Oracle Order Entry: Provides organizations with a sophisticated order entry system for managing customer commitments.
Oracle Payables: Lets an organization process more invoices with fewer staff members and tighter controls. Helps save money through maximum discounts, bank float, and prevention of duplicate payment.
Oracle Personnel: Improves the management of employee- related issues by retaining and making available every form of personnel data.
Oracle Purchasing: Improves buying power, helps negotiate bigger discounts, eliminates paper flow, increases financial controls, and increases productivity.
Oracle Receivables:. Improves cash flow by letting an organization process more payments faster, without off-line research. Helps correctly account for cash, reduce outstanding receivables, and improve collection effectiveness.
Oracle Revenue Accounting Gives an organization timely and accurate revenue and flexible commissions reporting.
Oracle Sales Analysis: Allows for better forecasting, planning. and reporting of sales information.
Q.What are the design facilities available in forms 4.0?
Default Block facility.
Tables Columns Browser.
Q.Write the Abbreviation for the following File Extension 1. FMB 2. MMB 3. PLL?
FMB —– Form Module Binary.
MMB —– Menu Module Binary.
PLL —— PL/SQL Library Module Binary.
Q.List the built-in routine for controlling window during run-time?
Q.Can you attach an alert to a field?
Q.Can a root window be made modal?
Q.What built-in is used for showing the alert during run-time?
Q.What built-in is used for changing the properties of the window dynamically?
Q.What is a visual attribute?
Visual Attributes are the font, color and pattern characteristics of objects that operators see and intract with in our application.
Q.How many maximum number of radio buttons can you assign to a radio group?
Unlimited no of radio buttons can be assigned to a radio group
Q.What is a Package Procedure?
A Package procedure is built in PL/SQL procedure.
Q.List the system variables related in Block and Field?
Q.Where can one find I/O statistics per table? (for DBA)
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.
My query was fine last week and now it is slow. Why? (for DBA)
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.
Q.Why is Oracle not using the damn index? (for DBA)
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.
Q.What is the significance of PAGE 0 in forms 3.0?
Hide the fields for internal calculation.
Q.What are the different types of key triggers?
Q.Can we use a restricted package procedure in ON-VALIDATE-FIELD Trigger?
Q.Is a Key startup trigger fires as result of a operator pressing a key explicitly?
Q.What is the use of CONSTRAINTS option in EXP command? A flag to indicate whether constraints on table need to be exported.
Q.What is the use of CONSISTENT (Ver 7) option in EXP command?
A flag to indicate whether a read consistent version of all the exported objects should be maintained.
Q.How will you create multiple rollback segments in a database?
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.
Q.What is cold backup? What are the elements of it?
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)
Q.How will you monitor rollback segment status?
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.
Q.What is the use of PARFILE option in EXP command?
Name of the parameter file to be passed for export.
Q.What is a record group?
A record group is an internal Oracle Forms that structure that has a column/row framework similar to a database table. However, unlike database tables, record groups are separate objects that belong to the form module which they are defined.
Q.My database is down and I cannot restore. What now? (for DBA )
Recovery without any backup is normally not supported, however, Oracle Consulting can sometimes extract data from an offline database using a utility called DUL (Disk UnLoad). This utility reads data in the data files and unloads it into SQL*Loader or export dump files. DUL does not care about rollback segments, corrupted blocks, etc, and can thus not guarantee that the data is not logically corrupt. It is intended as an absolute last resort and will most likely cost your company a lot of money!!!
Q.How is it possible to select generate a select set for the query in the query property sheet?
By using the tables/columns button and then specifying the table and the column names.
Q.How can values be passed bet. precompiler exits & Oracle call interface?
By using the statement EXECIAFGET & EXECIAFPUT.
Q.Where is a procedure return in an external pl/sql library executed at the client or at the server?
At the client.
Q.What is coordination Event?
Any event that makes a different record in the master block the current record is a coordination causing event.