Oracle Interview Questions – Part 22
Q.What is PL/SQL?
PL/SQL is a procedural language that has both interactive SQL and procedural programming language constructs such as iteration, conditional branching.
Q.What are the components of OEM? (for DBA)
Oracle Enterprise Manager (OEM) has the following components:
. Management Server (OMS): Middle tier server that handles communication with the intelligent agents. The OEM Console connects to the management server to monitor and configure the Oracle enterprise.
. Console: This is a graphical interface from where one can schedule jobs, events, and monitor the database. The console can be opened from a Windows workstation, Unix XTerm (oemapp command) or Web browser session (oem_webstage).
. Intelligent Agent (OIA): The OIA runs on the target database and takes care of the execution of jobs and events scheduled through the Console.
Q.Write the order of precedence for validation of a column in a table ?
I. done using Database triggers.
ii. done using Integarity Constraints.
I & ii.
Q.Give the structure of the function ?
FUNCTION name (argument list …..) Return datatype is
local variable declarations
Q.What third party tools can be used with Oracle EBU/ RMAN? (for DBA)
The following Media Management Software Vendors have integrated their media management software packages with Oracle Recovery Manager and Oracle7 Enterprise Backup Utility. The Media Management Vendors will provide first line technical support for the integrated backup/recover solutions.
EMC Data Manager (EDM)
HP OMNIBack II
IBM’s Tivoli Storage Manager – formerly ADSM
ManageIT Backup and Recovery
Sterling Software’s SAMS:Alexandria – formerly from Spectralogic
Sun Solstice Backup
Q.Why and when should one tune? (for DBA)
One of the biggest responsibilities of a DBA is to ensure that the Oracle database is tuned properly. The Oracle RDBMS is highly tunable and allows the database to be monitored and adjusted to increase its performance. One should do performance tuning for the following reasons:
The speed of computing might be wasting valuable human time (users waiting for response); Enable your system to keep-up with the speed business is conducted; and Optimize hardware usage to save money (companies are spending millions on hardware). Although this FAQ is not overly concerned with hardware issues, one needs to remember than you cannot tune a Buick into a Ferrari.
Q.How can a square be drawn in the layout editor of the report writer?
By using the rectangle tool while pressing the (Constraint) key.
Q.How can a text file be attached to a report while creating in the report writer?
By using the link file property in the layout boiler plate property sheet.
Q.How can I message to passed to the user from reports?
By using SRW.MESSAGE function.
Q.What is bind reference and how can it be created?
Bind reference are used to replace the single value in sql, pl/sql statements a bind reference can be created using a (:) before a column or a parameter name.
Q.How can one improve Import/ Export performance? (for DBA)
. Set the BUFFER parameter to a high value (e.g. 2M)
. Set the RECORDLENGTH parameter to a high value (e.g. 64K)
. Stop unnecessary applications to free-up resources for your job.
. If you run multiple export sessions, ensure they write to different physical disks.
. DO NOT export to an NFS mounted filesystem. It will take forever.
. Create an indexfile so that you can create indexes AFTER you have imported data. Do this by setting INDEXFILE to a filename and then import. No data will be imported but a file containing index definitions will be created. You must edit this file afterwards and supply the passwords for the schemas on all CONNECT statements.
. Place the file to be imported on a separate physical disk from the oracle data files
. Increase DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to 9i) considerably in the init$SID.ora file
. Set the LOG_BUFFER to a big value and restart oracle.
. Stop redo log archiving if it is running (ALTER DATABASE NOARCHIVELOG;)
. Create a BIG tablespace with a BIG rollback segment inside. Set all other rollback segments offline (except the SYSTEM rollback segment of course). The rollback segment must be as big as your biggest table (I think?)
. Use COMMIT=N in the import parameter file if you can afford it
. Use ANALYZE=N in the import parameter file to avoid time consuming ANALYZE statements
. Remember to run the indexfile previously created
Q.At what point of report execution is the before Report trigger fired?
After the query is executed but before the report is executed and the records are displayed.
Q.What are the built -ins used for Modifying a groups structure?
Q.What is an user exit used for?
A way in which to pass control (and possibly arguments ) form Oracle report to another Oracle products of 3 GL and then return control ( and ) back to Oracle reports.
Q.I’ve lost my REDOLOG files, how can I get my DB back? (for DBA)
The following INIT.ORA parameter may be required if your current redo logs are corrupted or blown away. Caution is advised when enabling this parameter as you might end-up losing your entire database. Please contact Oracle Support before using it. _allow_resetlogs_corruption = true
Q.What is a property clause?
A property clause is a named object that contains a list of properties and their settings. Once you create a property clause you can base other object on it. An object based on a property can inherit the setting of any property in the clause that makes sense for that object.
Q.What is a physical page ? & What is a logical page ?
A physical page is a size of a page. That is output by the printer. The logical page is the size of one page of the actual report as seen in the Previewer.
Q.What are the two panes that Appear in the design time pl/sql interpreter?
1. Source pane.
2. Interpreter pane
Q.What are the two ways by which data can be generated for a parameters list of values?
1. Using static values.
2. Writing select statement.
Q.What are the various methods of performing a calculation in a report ?
1. Perform the calculation in the SQL statements itself.
2. Use a calculated / summary column in the data model.
Q.What are the default extensions of the files created by menu module?
Q.What are the default extensions of the files created by forms modules?
.fmb – form module binary
.fmx – form module executable
Q.What is the use of FILE option in IMP command ?
The name of the file from which import should be performed.
Q.What is a Shared SQL pool?
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.
Q.What is hot backup and how it can be taken?
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.
Q.How will you force database to use particular rollback segment ?
SET TRANSACTION USE ROLLBACK SEGMENT rbs_name.
Q.Why query fails sometimes ?
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.
Q.What is meant by Redo Log file mirroring ? How it can be achieved?
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.
Q.Which parameter in Storage clause will reduce no. of rows per block?
Row size also reduces no of rows per block.
Q.What is meant by recursive hints ?
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.
Q.What is a database EVENT and how does one set it? (for DBA)
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.
Q.What is a Rollback segment entry ?
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.
Q.What database events can be set? (for DBA)
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.
Q.How does Space allocation table place within a block ?
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)
Q.What are the factors causing the reparsing of SQL statements in SGA?
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.
Q.What is dictionary cache ?
Dictionary cache is information about the databse objects stored in a data dictionary table.
Q.What is a Control file ?
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.
Q.What is the use of ANALYSE ( Ver 7) option in EXP command ?
A flag to indicate whether statistical information about the exported objects should be written to export dump file.
Q.What is the use of ROWS option in IMP command ?
A flag to indicate whether rows should be imported. If this is set to ‘N’ then only DDL for database objects will be executed.
Q.What is the use of INDEXES option in EXP command ?
A flag to indicate whether indexes on tables will be exported.