Oracle Placement Papers
Oracle Aptitude Questions and Answers - 28 Nov 2008
Oracle Technical Interview Questions - 26 Nov 2008
Oracle Interview Questions and Answers - 22 Nov 2008
Oracle Technical Paper
Oracle Reasoning paper
Oracle Arithmetic paper 2014
Oracle Reasoning Test - 2012
Oracle Technical Section - March 2011(Chennai)
Oracle Placement Paper - 15 Sep 2007
Oracle Verbal Ability - 2003
Oracle Arithmetic paper - 2002 (Warangal)
Oracle Technical Paper 03 Feb 2001
Oracle Database Questions 2009
Oracle Technical Questions - 2009
Placement Papers for All Companies
Amiti Software Technologies
Ashok Leyland Ltd
AT & T
AXA Technology Services
Bharti Airtel Ltd
Blue Star Infotech
Citicorp Overseas Software Ltd
CtrlS Datacenters Ltd
Ernst & Young
Harita - TVS
HTC Global Services
IBS Software Services
IndiaBulls Power Limited
Jindal Steel and Power Limited
L & T
L & T Infotech
Linde India Ltd
Lucas - TVS
Mahindra Engineering Services Ltd
Poornam Info Vision
PSI Data Systems Limited
SAP labs India
Sutherland Global Services
UTC Aerospace System
Oracle Interview Questions and Answers - 24 Nov 2008
Posted on :26-02-2016
Oracle Interview Questions and Answers - 24 Nov 2008:-
Q1. What is the frame & repeating frame?
A frame is a holder for a group of fields. A repeating frame is used to display a set of records when the number of records that are to displayed is not known before.
Q2. 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.
Q3. 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.
Q4. How free extents are managed in Ver 6.0 and Ver 7.0?
Free extents cannot be merged together in Ver 6.0.
Free extents are periodically coalesces with the neighboring free extent in Ver 7.0
Q5. What is dictionary cache?
Dictionary cache is information about the database objects stored in a data dictionary table.
Q6. 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)
Q7. What is meant by free extent?
A free extent is a collection of continuous free blocks in table space. When a segment is dropped its extents are reallocated and are marked as free.
Q8. How do you trap the error in forms 3.0?
Using On-Message or On-Error triggers.
Q9. What are the types of Pop-up window?
the pop-up field editor
pop-up list of values
Q10. What are parameters?
Parameters provide a simple mechanism for defining and setting the values of inputs that are required by a form at startup. Form parameters are variables of type char, number, date that you define at design time.
Parameters provide a simple mechanism for defining and setting the values of inputs that are required by a form at startup. Form parameters are variables of type char, number, date that you define at design time.
Q11. How does one add users to a password file?
One can select from the SYS.V_$PWFILE_USERS view to see which users are listed in the password file. New users can be added to the password file by granting them SYSDBA or SYSOPER privileges, or by using the orapwd utility. GRANT SYSDBA TO scott;
Q12. What are modal windows?
Modal windows are usually used as dialogs, and have restricted functionality compared to modelless windows. On some platforms for example operators cannot resize, scroll or iconify a modal window.
Q13. What is the advantage of the library?
Libraries provide a convenient means of storing client-side program units and sharing them among multiple applications. Once you create a library, you can attach it to any other form, menu, or library modules. When you can call library program units from triggers menu items commands and user named routine, you write in the modules to which you have attach the library. When a library attaches another library, program units in the first library can reference program units in the attached library. Library support dynamic loading-that is library program units are loaded into an application only when needed. This can significantly reduce the run-time memory requirements of applications.
Q14. What is synchronize?
It is a terminal screen with the internal state of the form. It updates the screen display to reflect the information that oracle forms has in its internal representation of the screen
Q15. From which designation is it preferred to send the output to the printed?
Q16. With which function of summary item is the compute at options required?
Percentage of total functions
Q17. What is an SQL *FORMS?
SQL *forms is 4GL tool for developing and executing; Oracle based interactive application.
Q18. How many pages you can in a single form?
Q19. How do you control the constraints in forms?
Select the use constraint property is ON Block definition screen.
Q20. What are the differences between post database commit and post-form commit?
Post-form commit fires once during the post and commit transactions process, after the database commit occurs. The post-form-commit trigger fires after inserts, updates and deletes have been posted to the database but before the transactions have been finalized in the issuing the command. The post-database-commit trigger fires after oracle forms issues the commit to finalized transactions
Q21. Which of the above methods is the faster method?
Performing the calculation in the query is faster.
Q22. How many windows in a form can have console?
Only one window in a form can display the console, and you cannot change the console assignment at runtime.
Q23. What are the different display styles of list items?
Q24. What are the three types of user exits available?
Oracle Precompiler exits, Oracle call interface, NonOracle user exits
Q25. What are the master-detail triggers?
Q26. What is the difference between setting up of parameters in reports 2.0 reports 2.5?
LOVs can be attached to parameters in the reports 2.5 parameter form.
Q27. What is system.coordination_operation?
It represents the coordination causing event that occur on the master block in master-detail relation.
Q28. What forms_DDL?
Issues dynamic Sql statements at run time, including server side pl/SQl and DDL.
Q29. How do you reference a Parameter?
In Pl/Sql, You can reference and set the values of form parameters using bind variables syntax. Ex. PARAMETER name = or :block.item = PARAMETER Parameter name
Q30. What is an administrative (privileged) user?
Oracle DBAs and operators typically use administrative accounts to manage the database and database instance. An administrative account is a user that is granted SYSOPER or SYSDBA privileges. SYSDBA and SYSOPER allow access to a database instance even if it is not running. Control of these privileges is managed outside of the database via password files and special operating system groups. This password file is created with the orapwd utility.
Q31. What are the different default triggers created when Master Deletes Property is set to Non-isolated?
Master Deletes Property Resulting Triggers
Non-Isolated(the default) On-Check-Delete-Master
Q32. What are the two repeating frame always associated with matrix object?
One down repeating frame below one across repeating frame.
Q33. EXIT_FORM is a restricted package procedure?
a. Trueb. False
Q34. While specifying master/detail relationship between two blocks specifying the join condition is a must? Say True or False?
Q35. What is the usage of an ON-INSERT, ON-DELETE and ON-UPDATE TRIGGERS?
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.
Q36. What is the difference between ON-VALIDATE-FIELD trigger and a POST-CHANGE trigger?
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.
Q37. When is PRE-QUERY trigger executed?
When Execute-query or count-query Package procedures are invoked.
Q38. What is the use of CONSTRAINTS option in EXP command?
A flag to indicate whether constraints on table need to be exported
Q39. What is the use of GRANT option in EXP command?
A flag to indicate whether grants on database objects will be exported or not. Value is Y or N.
Q40. What are the different methods of backing up oracle database?
- Logical Backups
- Cold Backups
- Hot Backups (Archive log)
Q41. What is the use of SHOW option in IMP command?
A flag to indicate whether file content should be displayed or not.
Q42. 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.
Q43. What is the use of GRANT option in IMP command?
A flag to indicate whether grants on database objects will be imported.
Q44. What is the use of INDEXES option in EXP command?
A flag to indicate whether indexes on tables will be exported.
Q45. What is the use of FULL option in EXP command?
A flag to indicate whether full database export should be performed
Q46. What is the use of INDEXES option in IMP command?
A flag to indicate whether import should import index on tables or not.
Q47. Is it possible to modify an external query in a report which contains it?
Q48. What are the different models of windows?
Q49. Does a grouping done for objects in the layout editor affect the grouping done in the data model editor?
Q50. How does one connect to an administrative user?
If an administrative user belongs to the DBA group on Unix, or the ORA_DBA (ORA_sid_DBA) group on NT, he/she can connect like this: connect / as sysdba.
No password is required. This is equivalent to the desupported connect internal method.
A password is required for non-secure administrative access. These passwords are stored in password files. Remote connections via Net8 are classified as non-secure. Look at this example: connect sys/password as sysdba
Q51. If a break order is set on a column would it affect columns which are under the column?
Q52. Why are OPS$ accounts a security risk in a client/server environment?
If you allow people to log in with OPS$ accounts from Windows Workstations, you cannot be sure who they really are. With terminals, you can rely on operating system passwords, with Windows, you cannot.
If you set REMOTE_OS_AUTHENT=TRUE in your init.ora file, Oracle assumes that the remote OS has authenticated the user. If REMOTE_OS_AUTHENT is set to FALSE (recommended), remote users will be unable to connect without a password. IDENTIFIED EXTERNALLY will only be in effect from the local host. Also, if you are using OPS$ as your prefix, you will be able to log on locally with or without a password, regardless of whether you have identified your ID with a password or defined it to be IDENTIFIED EXTERNALLY.
Q53. Do user parameters appear in the data modal editor in 2.5?
Q54. Can you pass data parameters to forms?
Q55. Is it possible to link two groups inside a cross products after the cross products group has been created?
Q56. What are the differences between lov & list item?
Lov is a property where as list item is an item. A list item can have only one column, lov can have one or more columns.
Q57. What is lexical reference? How can it be created?
Lexical reference is place_holder for text that can be embedded in a sql statements. A lexical reference can be created using & before the column or parameter name.
Q58. What is a Text_io Package?
It allows you to read and write information to a file in the file system
Q59. What use of command line parameter cmd file?
It is a command line argument that allows you to specify a file that contain a set of arguments for r20run.
Q60. What are the different styles of activation of ole Objects?
In place activation External activation.
Q61. How is link tool operation different bet. reports 2 & 2.5?
In Reports 2.0 the link tool has to be selected and then two fields to be linked are selected and the link is automatically created. In 2.5 the first field is selected and the link tool is then used to link the first field to the second field.
Q62. What is the difference between object embedding & linking in Oracle forms?
In Oracle forms, Embedded objects become part of the form module, and linked objects are references from a form module to a linked source file.
Q63. Name of the functions used to get/set canvas properties?
Q64. What are the built-ins that are used for setting the LOV properties at runtime?
Q65. What are the built-ins used for processing rows?
Q66. What are the built-in used for getting cell values?
Q67. Atleast how many set of data must a data model have before a data model can be base on it?
Q68. To execute row from being displayed that still use column in the row which property can be used?
ANS: Format trigger.
Q69. What are different types of modules available in oracle form?
Form module - a collection of objects and code routines Menu modules - a collection of menus and menu item commands that together make up an application menu library module - a collection of user named procedures, functions and packages that can be called from other modules in the application
Q70. What is the remove on exit property?
For a modelless window, it determines whether oracle forms hides the window automatically when the operators navigates to an item in the another window.
Q71. What is WHEN-Database-record trigger?
Fires when oracle forms first marks a record as an insert or an update. The trigger fires as soon as oracle forms determine through validation that the record should be processed by the next post or commit as an insert or update. C generally occurs only when the operators modifies the first item in the record, and after the operator attempts to navigate out of the item.
Q72. What is a difference between pre-select and pre-query?
Fires during the execute query and count query processing after oracle forms constructs the select statement to be issued, but before the statement is actually issued. The pre-query trigger fires just before oracle forms issues the select statement to the database after the operator as define the example records by entering the query criteria in enter query mode. Pre-query trigger fires before pre-select trigger.
Q73. What are built-ins associated with timers?
Q74. What are the built-ins used for finding Object ID function?
Q75. Any attempt to navigate programmatically to disabled form in a call_form stack is allowed?
Q76. Use the Add_group_row procedure to add a row to a static record group 1. Say true or false?
Q77. What third party tools can be used with Oracle EBU/ RMAN?
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
IBMs Tivoli Storage Manager - formerly ADSM
ManageIT Backup and Recovery
Sterling Softwares SAMS:Alexandria - formerly from Spectralogic
Sun Solstice Backup
Q78. Why and when should one tune?
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.
Q79. How can a break order be created on a column in an existing group? What are the various sub events a mouse double click event involves?
By dragging the column outside the group.
Q80. What is the use of place holder column? What are the various sub events a mouse double click event involves?
A placeholder column is used to hold calculated values at a specified place rather than allowing is to appear in the actual row where it has to appear.
Q81. What is the use of hidden column? What are the various sub events a mouse double click event involves?
A hidden column is used to when a column has to embed into boilerplate text.
Q82. What database aspects should be monitored?
One should implement a monitoring system to constantly monitor the following aspects of a database. Writing custom scripts, implementing Oracles Enterprise Manager, or buying a third-party monitoring product can achieve this. If an alarm is triggered, the system should automatically notify the DBA (e-mail, page, etc.) to take appropriate action.
. Is the database up and responding to requests
. Are the listeners up and responding to requests
. Are the Oracle Names and LDAP Servers up and responding to requests
. Are the Web Listeners up and responding to requests
Q83. What are the various sub events a mouse double click event involves? What are the various sub events a mouse double click event involves?
Double clicking the mouse consists of the mouse down, mouse up, mouse click, mouse down & mouse up events.
Q84. What are the default parameter that appear at run time in the parameter screen? What are the various sub events a mouse double click event involves?
Destype and Desname.
Q85. What are the built-ins used for Creating and deleting groups?
Q86. What are different types of canvas views?
Content canvas views
Stacked canvas views
Q87. What are the different types of Delete details we can establish in Master-Details?
Q88. 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.
Q89. What is a User_exit?
Calls the user exit named in the user_exit_string. Invokes a 3Gl program by name which has been properly linked into your current oracle forms executable.
Q90. 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.
Q91. How can values be passed bet. precompiler exits and Oracle call interface?
By using the statement EXECIAFGET and EXECIAFPUT.
Q92. How can a square be drawn in the layout editor of the report writer?
By using the rectangle tool while pressing the (Constraint) key.
Q93. 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.
Q94. How can I message to the user from reports?
By using SRW.MESSAGE function
Q95. Does one need to drop/ truncate objects before importing?
Before one import rows into already populated tables, one needs to truncate or drop these tables to get rid of the old data. If not, the new data will be appended to the existing tables. One must always DROP existing Sequences before re-importing. If the sequences are not dropped, they will generate numbers inconsistent with the rest of the database. Note: It is also advisable to drop indexes before importing to speed up the import process. Indexes can easily be recreated after the data was successfully imported
Q96. How can a button be used in a report to give a drill down facility?
By setting the action associated with button to Execute pl/sql option and using the SRW.Run_report function.
Q97. Can one import/export between different versions of Oracle?
Different versions of the import utility is upwards compatible. This means that one can take an export file created from an old export version, and import it using a later version of the import utility. This is quite an effective way of upgrading a database from one release of Oracle to the next.
Oracle also ships some previous catexpX.sql scripts that can be executed as user SYS enabling older imp/exp versions to work (for backwards compatibility). For example, one can run $ORACLE_HOME/rdbms/admin/catexp7.sql on an Oracle 8 database to allow the Oracle 7.3 exp/imp utilities to run against an Oracle 8 database.
Q98. 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.
Q99. Give the sequence of execution of the various report triggers?
Before form, After form, Before report, Between page, After report.
Q100. What is coordination Event?
Any event that makes a different record in the master block the current record is a coordination causing event.
Q101. What is an LOV?
An LOV is a scrollable popup window that provides the operator with either a single or multi column selection list.
Q102. 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.
Q103. 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.
Q104. Why is it preferable to create a fewer no. of queries in the data model?
Because for each query, report has to open a separate cursor and has to rebind, execute and fetch data.
Q105. Where is the external query executed at the client or the server?
At the server.
Q106. Where is a procedure return in an external pl/sql library executed at the client or at the server?
At the client.
Q107. What is an object group?
An object group is a container for a group of objects; you define an object group when you want to package related objects, so that you copy or reference them in other modules.
Q108. What is the difference between OLE Server & Ole Container?
An Ole server application creates ole Objects that are embedded or linked in ole Containers ex. Ole servers are ms_word & ms_excel. OLE containers provide a place to store, display and manipulate objects that are created by ole server applications. Ex. oracle forms is an example of an ole Container.
Q109. What are the built -ins used for Modifying a groups structure?
ADD_GROUP_COLUMN (function)ADD_GROUP_ROW (procedure)DELETE_GROUP_ROW(procedure)
Q110. What is the User-Named Editor?
A user named editor has the same text editing functionality as the default editor, but, because it is a named object, you can specify editor attributes such as windows display size, position, and title.
Q111. 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.
Q112. 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.
Q113. 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.
Q114. 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.
Q115. What are the differences between EBU and RMAN?
Enterprise Backup Utility (EBU) is a functionally rich, high performance interface for backing up Oracle7 databases. It is sometimes referred to as OEBU for Oracle Enterprise Backup Utility. The Oracle Recovery Manager (RMAN) utility that ships with Oracle8 and above is similar to Oracle7s EBU utility. However, there is no direct upgrade path from EBU to RMAN.
Q116. How can a group in a cross products be visually distinguished from a group that does not form a cross product?
A group that forms part of a cross product will have a thicker border.
Q117. What are three panes that appear in the run time pl/sql interpreter?
1. Source pane.
2. interpreter pane.
3. Navigator pane.
Q118. What is a combo box?
A combo box style list item combines the features found in list and text item. Unlike the pop list or the text list style list items, the combo box style list item will both display fixed values and accept one operator entered value.
Q119. What are the two panes that Appear in the design time pl/sql interpreter?
1. Source pane.
2. Interpreter pane
Q120. 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.
Q121. What are the default extensions of the files created by menu module?
Q122. What are the default extensions of the files created by forms modules?
.fmb - form module binary
.fmx - form module executable
Q123. To display the page number for each page on a report what would be the source: & logical page number (or) & physical page no?
& physical page no
Q124. It is possible to use raw devices as data files and what is the advantages over file. system files?
Yes. The advantages over file system files.
I/O will be improved because Oracle is bye-passing the kernnel which writing into disk. Disk Corruption will be very less.
Q125. What are disadvantages of having raw devices?
We should depend on export/import utility for backup/recovery (fully reliable) The tar command cannot be used for physical file backup, instead we can use dd command which is less flexible and has limited recoveries.
Q126. 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.,
Q127. 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 to expand into anotion Completes. e. will be written.
Q128. What is the use of FILE option in IMP command?
The name of the file from which import should be performed
Q129. 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.
Q130. How to implement the multiple control files for an existing database?
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.
Q131. How will you force database to use particular rollback segment?
SET TRANSACTION USE ROLLBACK SEGMENT rbs_name.
Q132. What is the use of RECORD LENGTH option in EXP command?
Record length in bytes.
Q133. What is advantage of having disk shadowing/ Mirroring?
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 switch over 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.
Q134. 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 Table space. This prevents other user using Rollback segments.
Q135. 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.
Q136. 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 corrupted.
PARTLY AVAILABLE - Contains data from an unresolved transaction involving a distributed database.
Q137. Which parameter in Storage clause will reduce number of rows per block?
Row size also reduces no of rows per block.
Q138. 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.
Q139. What is the use of PARFILE option in EXP command?
Name of the parameter file to be passed for export.
Q140. What is a logical backup?
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.
Q141. Where can one get a list of all hidden Oracle parameters?
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:
from SYS.X$KSPPIwhere 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 Valuefrom x$ksppi a, x$ksppcv b, x$ksppsv cwhere a.indx = b.indx and a.indx = c.indxand substr(ksppinm,1,1)=_order by a.ksppinm;
Q142. What are the different kinds of export backups?
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.
Q143. What is the use of RECORD option in EXP command?
For Incremental exports, the flag indirects whether a record will be stores data dictionary tables recording the export.
Q144. What is the use of ROWS option in EXP command?
Flag to indicate whether table rows should be exported. If N only DDL statements for the database objects will be created.
Q145. What is the use of COMPRESS option in EXP command?
Flag to indicate whether export should compress fragmented segments into single extents.
Q146. How will you swap objects into a different table space for an existing database?
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.
Q147. 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)
Q148. 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.
Q149. 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.
Q150. What is Database Buffers?
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.
Q151. How will you create multiple rollback segments in a database?
Create a database which implicitly creates a SYSTEM Rollback Segment in a SYSTEM table space. Create a Second Rollback Segment name R0 in the SYSTEM table space. Make new rollback segment available (After shutdown, modify init.ora file and Start database) Create other table spaces (RBS) for rollback segments. Deactivate Rollback Segment R0 and activate the newly created rollback segments.
Q152. What is meant by redo log buffer?
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
Q153. How will you estimate the space required by a non-clustered tables?
Calculate the total header size
Calculate the available data space 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.
Q154. How will you monitor the space allocation?
By querying DBA_SEGMENT table/view.
Q155. 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.
Q156. 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
Q157. How does one create a password file?
The Oracle Password File ($ORACLE_HOME/dbs/orapw or orapwSID) stores passwords for users with administrative privileges. One needs to create a password files before remote administrators (like OEM) will be allowed to connect.
Follow this procedure to create a new password file:
. Log in as the Oracle software owner. Runcommand: orapwd file = $ORACLE_HOME/ dbs/ orapw $ORACLE_SID password = mypasswd. Shutdown the database (SQLPLUS> SHUTDOWN IMMEDIATE). Edit the INIT.ORA file and ensure REMOTE_LOGIN_PASSWORDFILE = exclusive is set.. Startup the database (SQLPLUS> STARTUP)
NOTE: The orapwd utility presents a security risk in that it receives a password from the command line. This password is visible in the process table of many systems. Administrators needs to be aware of this!
Q158. What are the different default triggers created when Master Deletes Property is set to isolated?
Master Deletes Property Resulting Triggers
Q159. What are the different default triggers created when Master Deletes Property is set to Cascade?
Master Deletes Property Resulting Triggers
Q160. List the Optional Flexible Architecture (OFA) of Oracle database? or How can we organize the tablespaces in Oracle database to have maximum performance?
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.