Oracle cursor : Implicit & Explicit cursors

DATABASE Administrator (DBA) Interview Questions and Answers

(Continued from previous question...)

61. Oracle cursor : Implicit & Explicit cursors

Oracle uses work areas called private SQL areas to create SQL statements. PL/SQL construct to identify each and every work are used, is called as Cursor. For SQL queries returning a single row, PL/SQL declares all implicit cursors. For queries that returning more than one row, the cursor needs to be explicitly declared.

(Continued on next question...)

Other Interview Questions

• What is a DBA?
• What DBA activities did you to do today?
• What are the different modes of mounting a Database with the Parallel Server?
• What are the advantages of operating a database in ARCHIVELOG mode over operating it in NO ARCHIVELOG mode?
• Do you consider yourself a development DBA or a production DBA and why?
• What are the Large object types suported by Oracle?
• Diffrence between a “where” clause and a “having” claus
• Shall we create procedures to fetch more than one record?
• Do View contain Data?
• What are the Referential actions supported by FOREIGN KEY integrity constraint?
• What are the type of Synonyms?
• Where would you look for errors from the database engine?
• Compare and contrast TRUNCATE and DELETE for a table.
• Give the reasoning behind using an index.
• Give the two types of tables involved in producing a star schema and the type of data they hold.
• What type of index should you use on a fact table?
• What is a Segment?
• A table is classified as a parent table and you want to drop and re-create it. How would you do this without affecting the children tables?
• Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and disadvantages to each.
• What is a Sequence?
• Give the stages of instance startup to a usable state where normal users may access it.
• What is a Synonym?
• How would you go about generating an EXPLAIN plan?
• How would you go about increasing the buffer cache hit ratio?
• Explain an ORA-01555
• Explain the difference between $ORACLE_HOME and $ORACLE_BASE.
• How would you determine the time zone under which a database was operating?
• Explain the use of setting GLOBAL_NAMES equal to TRUE.
• What command would you use to encrypt a PL/SQL application?
• Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.
• Explain the use of table functions.
• Name three advisory statistics you can collect.
• Where in the Oracle directory tree structure are audit traces placed?
• Explain materialized views and how they are used.
• What does a Control file Contain?
• What is difference between UNIQUE constraint and PRIMARY KEY constraint?
• What is the effect of setting the value “ALL_ROWS” for OPTIMIZER_GOAL parameter of the ALTER SESSION command?
• Describe what redo logs are.
• How would you force a log switch?
• What are the different Parameter types?
• What does coalescing a tablespace do?
• What is the difference between a TEMPORARY tablespace and a PERMANENT tablespace?
• Name a tablespace automatically created when you create a database.
• When creating a user, what permissions must you grant to allow them to connect to the database?
• How do you add a data file to a tablespace?
• How do you resize a data file?
• What view would you use to look at the size of a data file?
• What view would you use to determine free space in a tablespace?
• How would you determine who has added a row to a table?
• How can you rebuild an index?
• Explain what partitioning is and what its benefit is.
• You have just compiled a PL/SQL package but got errors, how would you view the errors?
• How can you gather statistics on a table?
• How can you enable a trace for a session?
• What is the difference between the SQL*Loader and IMPORT utilities?
• What is a Schema?
• What is a cluster Key?
• What?s the command to change the SQL prompt name?
• What is Parallel Server?
• How do I eliminate the duplicate rows ?
• How do I display row number with records?
• Display the records between two range
• I know the nvl function only allows the same data type(ie. number or char or date Nvl(comm, 0)), if commission is null then the text ?Not Applicable? want to display, instead of blank space. How do I write the query?
• Oracle cursor : Implicit & Explicit cursors
• Explicit Cursor attributes
• Implicit Cursor attributes
• Find out nth highest salary from emp table
• To view installed Oracle version information
• Display the number value in Words
• Display Odd/ Even number of records
• Which date function returns number value?
• Any three PL/SQL Exceptions?
• What are PL/SQL Cursor Exceptions?
• Other way to replace query result null value with a text
• What are the more common pseudo-columns?
• What is the output of SIGN function?
• What is the maximum number of triggers, can apply to a single table?
• What is a database instance? Explain.
• What is Parallel Server?
• What is a schema?
• What are the options available to refresh snapshots?
• What is a SNAPSHOT LOG?
• What is Distributed database?
• What are the basic element of base configuration of an Oracle database?
• What is a deadlock? Explain.
• What is Privilege Auditing?
• What is Object Auditing?
• Physical DB Structure
• Logical DB Structure
• Tablespaces
• Online & Offline TS
• Schema
• Index Clusters
• Database Links
• Data Blocks
• For a field in a repeating frame, can the source come from the column which does not exist in the data group which forms the base for the frame?
• Can a field be used in a report without it appearing in any data group?
• The join defined by the default data link is an outer join yes or no?
• What UNIX command will control the default file permissions when files are created?
• Explain the read, write, and execute permissions on a UNIX directory.
• the difference between a soft link and a hard link?
• Give the command to display space usage on the UNIX file system.
• Explain iostat, vmstat and netstat.
• What is new_form built-in?
• What is the “LOV of Validation” Property of an item? - What is the use of it?
• What is the diff. when Flex mode is mode on and when it is off?
• What is ROWID?
• What is a correlated subquery?
• Explain UNION, MINUS, UNION ALL and INTERSECT?
• What are the types of SQL statement?
• What is a pseudo column. Give some examples?
• What is difference between CHAR and VARCHAR2?
• What do you know about subqueries?
• What is a database link?
• How to drop the index ?
• What are the different types of SQL statements?
• What are the uses of rollback segment?
• What is a deadlock and Explain?
• State any three mouse events system variables?
• What other parts of your organization do you interact with and how?
• How do you display console on a window?
• In exception handling we have some NOT_FOUND and OTHERS. In inner layer we have some NOT_FOUND and OTHERS. While executing which one whether outer layer or inner layer will check first?
• What are the different types of joins?
• How will you copy the structure of a table without copying the data?
• What is a VIEW? How to get script for a view?
• What is a “trigger”?
• Explain the difference between a hot backup and a cold backup and the benefits associated with each.
• What cursor type do you use to retrieve multiple recordsets?
• What is the difference among “dropping a table”, “truncating a table” and “deleting all records” from a table.
• Difference between “ORACLE” and “MICROSOFT ACCESS” databases.
• How to remove duplicate records from a table?
• Are you a nuts-n-bolts DBA or a tools-n-props DBA
• How to create a database link?
• What is SQL*Loader?
• How to run SQL script from a Unix Shell?
• Talk about “Exception Handling” in PL/SQL?
• Give some examples of Analytical functions
• What is Log Switch?
• What is On-line Redo Log?
• Which parameter specified in the DEFAULT STORAGE clause of CREATE TABLESPACE cannot be altered after creating the tablespace?
• What are the steps involved in Database Startup?
• What are the steps involved in Instance Recovery?
• Can Full Backup be performed when the database is open?
• You have just had to restore from backup and do not have any control files. How would you go about bringing up this database?
• What are the steps involved in Database Shutdown?
• What is Archived Redo Log?
• What is Restricted Mode of Instance Startup?
• What is Partial Backup?
• What is Mirrored on-line Redo Log?
• What is Full Backup?
• Can a View based on another View?
• Can a Tablespace hold objects from different Schemes?
• Can objects of the same Schema reside in different tablespaces?
• What is the use of Control File?
• What is your typical day like?
• How do you switch from an init.ora file to a spfile?
• Explain the difference between a data block, an extent and a segment.
• Give two examples of how you might determine the structure of the table DEPT.
• What is a Redo Log?
• What is an Index Segment?
• Explain the relationship among Database, Tablespace and Data file.?
• What are the different type of Segments?
• What are Clusters?
• What is an Integrity Constrains?
• What is an Index?
• What is an Extent?
• What is a View?
• What is Table?
• What column differentiates the V$ views to the GV$ views and how?
• What command would you use to create a backup control file?
• Give two examples of referential integrity constraints.
• What is schema?
• Describe Referential Integrity?
• What is Hash Cluster?
• What is a Private Synonyms?
• What is Database Link?
• What is a Tablespace?
• What is Rollback Segment?
• What are the Characteristics of Data Files?
• How to define Data Block size?
• When a user process fails, what background process cleans up after it?
• What background process refreshes materialized views?
• How would you determine what sessions are connected and what resources they are waiting for?
• What is the effect of setting the value “CHOOSE” for OPTIMIZER_GOAL, parameter of the ALTER SESSION Command?
• What is the function of Optimizer?
• What is Execution Plan?
• What are the different approaches used by Optimizer in choosing an execution plan?
• What does ROLLBACK do?
• What is SAVE POINT?
• What are the values that can be specified for OPTIMIZER MODE Parameter?
• What is COST-based approach to optimization?
• What does COMMIT do?
• What is RULE-based approach to optimization?
• What are the values that can be specified for OPTIMIZER_GOAL parameter of the ALTER SESSION Command?
• Define Transaction?
• What is Read-Only Transaction?
• What is a deadlock?
• Name two files used for network connection to a database.
• What?s the command to see the current user name?
• How do you switch to DOS prompt from SQL prompt?
• What are the basic element of Base configuration of an oracle Database?
• What is clusters?
• What is an Index? - How it is implemented in Oracle Database?
• What is a Database instance?
• What is the use of ANALYZE command?
• What is default tablespace?
• What are the system resources that can be controlled through Profile?
• What is Tablespace Quota?
• What are the different Levels of Auditing?
• What is Statement Auditing?
• What are the database administrators utilities available?
• How can you enable automatic archiving?
• What are roles?
• How can we implement roles?
• What are the use of Roles?
• Auditing
• Audit Trial
• What is Auditing?
• What are the responsibilities of a Database Administrator?
• What is a trace file and how is it created?
• What is a profile?
• How will you enforce security using stored procedures?
• What are the dictionary tables used to monitor a database spaces?
• What are the roles and user accounts created automatically with the database?
• What are the minimum parameters should exist in the parameter file (init.ora)?
• How can we specify the Archived log file name format and destination?
• What is user Account in Oracle database?
• What dynamic data replication?
• What is Two-Phase Commit?
• How can you Enforce Referential Integrity in snapshots?
• What is a SQL * NET?
• What is a SNAPSHOT?
• What is the mechanism provided by ORACLE for table replication?
• What is snapshots?
• What are the various type of snapshots?
• Describe two phases of Two-phase commit?
• What is snapshot log?
• What are the benefits of distributed options in databases?
• What is an index? How it is implemented in Oracle database?
• What are clusters?
• What is a cluster key?
• How can we reduce the network traffic?
• Differentiate simple and complex, snapshots?
• What are the Built-ins used for sending Parameters to forms?
• Can you have more than one content canvas view attached with a window?
• Is the After report trigger fired if the report execution fails?
• Does a Before form trigger fire when the parameter form is suppressed?
• Is it possible to split the print reviewer into more than one region?
• Is it possible to center an object horizontally in a repeating frame that has a variable horizontal size?
• How do you list the files in an UNIX directory while also showing hidden files?
• How do you execute a UNIX command in the background?
• Can a formula column referred to columns in higher group?
• Can a formula column be obtained through a select statement?
• If a parameter is used in a query without being previously defined, what diff. exist between report 2.0 and 2.5 when the query is applied?
• What are the SQL clauses supported in the link property sheet?
• What is trigger associated with the timer?
• What are the trigger associated with image items?
• What are the different windows events activated at runtimes?
• When do you use data parameter type?
• What is difference between open_form and call_form?
• How would you change all occurrences of a value using VI?
• Give two UNIX kernel parameters that effect an Oracle install
• Briefly, how do you install Oracle software on UNIX.
• What is the diff. when confine mode is on and when it is off?
• What are visual attributes?
• Which of the two views should objects according to possession?
• What are the two types of views available in the object navigator (specific to report 2.5)?
• What are the vbx controls?
• What is the use of transactional triggers?
• How do you create a new session while open a new form?
• What are the ways to monitor the performance of the report?
• If two groups are not linked in the data model editor, What is the hierarchy between them?
• An open form can not be execute the call_form procedure if you chain of called forms has been initiated by another open form?
• Explain about horizontal, Vertical tool bar canvas views?
• What is the purpose of the product order option in the column property sheet?
• What is the use of image_zoom built-in?
• How do you reference a parameter indirectly?
• Is it possible to insert comments into sql statements return in the data model editor?
• Is it possible to disable the parameter from while running the report?
• When a form is invoked with call_form, Does oracle forms issues a save point?
• Can a property clause itself be based on a property clause?
• What is a timer?
• What are the two phases of block coordination?
• What are Most Common types of Complex master-detail relationships?
• What is a text list?
• What is term?
• What is use of term?
• What is pop list?
• What is the maximum no of chars the parameter can store?
• What are the default extensions of the files created by library module?
• What are the Coordination Properties in a Master-Detail relationship?
• What is an index and How it is implemented in Oracle database?
• Lot of users are accessing select sysdate from dual and they getting some millisecond differences. If we execute SELECT SYSDATE FROM EMP; what error will we get. Why?
• Give two methods you could use to determine what DDL changes have been made.
• What are the types of calculated columns available?
• Explain about stacked canvas views?
• What are the built_ins used the display the LOV?
• What is the difference between SHOW_EDITOR and EDIT_TEXTITEM?
• What are the built-ins that are used to Attach an LOV programmatically to an item?
• How do you call other Oracle Products from Oracle Forms?
• What is the main diff. bet. Reports 2.0 & Reports 2.5?
• What are the different file extensions that are created by oracle reports?
• What is strip sources generate options?
• What is the basic data structure that is required for creating an LOV?
• What is the Maximum allowed length of Record group Column?
• Which parameter can be used to set read level consistency across multiple queries?
• What are the different types of Record Groups?
• From which designation is it preferred to send the output to the printed?
• what are difference between post database commit and post-form commit?
• What are the different display styles of list items?
• Which of the above methods is the faster method?
• With which function of summary item is the compute at options required?
• What are parameters?
• What are the three types of user exits available?
• How many windows in a form can have console?
• If the maximum record retrieved property of the query is set to 10 then a summary value will be calculated?
• What are the two repeating frame always associated with matrix object?
• What are the master-detail triggers?
• What are the different objects that you cannot copy or reference in object groups?
• What is an OLE?
• Is it possible to modify an external query in a report which contains it?
• Does a grouping done for objects in the layout editor affect the grouping done in the data model editor?
• Can a repeating frame be created without a data group as a base?
• If a break order is set on a column would it affect columns which are under the column?
• Is it possible to set a filter condition in a cross product group in matrix reports?
• Do user parameters appear in the data modal editor in 2.5?
• Can you pass data parameters to forms?
• Is it possible to link two groups inside a cross products after the cross products group has been created?
• What are the different modals of windows?
• What are modal windows?
• What are the different default triggers created when Master Deletes Property is set to Non-isolated?
• What are the different default triggers created when Master Deletes Property is set to isolated?
• What are the different default triggers created when Master Deletes Property is set to Cascade?
• What is the diff. bet. setting up of parameters in reports 2.0 reports2.5?
• What are the difference between lov & list item?
• What is the advantage of the library?
• What is lexical reference?
• What is system.coordination_operation?
• What is synchronize?
• What use of command line parameter cmd file?
• What is a Text_io Package?
• What is forms_DDL?
• How is link tool operation different bet. reports 2 & 2.5?
• What are the different styles of activation of ole Objects?
• How do you reference a Parameter?
• What is the difference between object embedding and linking in Oracle forms?
• Name of the functions used to get/set canvas properties?
• What are the built-ins that are used for setting the LOV properties at runtime?
• What are the built-ins used for processing rows?
• What are built-ins used for Processing rows?
• What are the built-in used for getting cell values?
• What are the built-ins used for Getting cell values?
• A tleast how many set of data must a data model have before a data model can be base on it?
• To execute row from being displayed that still use column in the row which property can be used?
• What are different types of modules available in oracle form?
• What is the remove on exit property?
• What is WHEN-Database-record trigger?
• What is a difference between pre-select and pre-query?
• What are built-ins associated with timers?
• What are the built-ins used for finding object ID functions?
• What are the built-ins used for finding Object ID function?
• Any attempt to navigate programmatically to disabled form in a call_form stack is allowed?
• Use the Add_group_row procedure to add a row to a static record group 1. true or false?
• Use the add_group_column function to add a column to record group that was created at a design time?
• What are the various sub events a mouse double click event involves?
• How can a break order be created on a column in an existing group?
• What is the use of place holder column?
• What is the use of hidden column?
• What is the use of break group?
• What is an anchoring object and what is its use?
• What are the various sub events a mouse double click event involves?
• What are the default parameter that appear at run time in the parameter screen?
• What are the built-ins used for Creating and deleting groups?
• What are different types of canvas views?
• What are the different types of Delete details we can establish in Master-Details?
• What is relation between the window and canvas views?
• What is a User_exit?
• How is it possible to select generate a select set for the query in the query property sheet?
• How can values be passed between precompiler exits & Oracle call interface?
• How can a square be drawn in the layout editor of the report writer?
• How can a text file be attached to a report while creating in the report writer?
• How can I message to passed to the user from reports?
• How is possible to restrict the user to a list of values while entering values for parameters?
• How can a button be used in a report to give a drill down facility?
• How can a cross product be created?
• What are different types of images?
• What is the difference between boiler plat images and image items?
• What is bind reference and how can it be created?
• What are the triggers available in the reports?
• Give the sequence of execution of the various report triggers?
• Why is a Where clause faster than a group filter or a format trigger?
• Why is it preferable to create a fewer no. of queries in the data model?
• Where is the external query executed at the client or the server?
• Where is a procedure return in an external pl/SQL library executed at the client or at the server?
• What is coordination Event?
• What is the difference between OLE Server & OLE Container?
• What is an object group?
• What is an LOV?
• At what point of report execution is the before Report trigger fired?
• What are the built -ins used for Modifying a groups structure?
• What is an user exit used for?
• What is the User-Named Editor?
• What are the Built-ins to display the user-named editor?
• What is a Static Record Group?
• What is a record group?
• How many number of columns a record group can have?
• What is a Query Record Group?
• What is a property clause?
• What is a physical page? What is a logical page?
• What does the term panel refer to with regarda to pages?
• What is a master detail relationship?
• What is a library?
• How can a group in a cross products be visually distinguished from a group that does not form a cross product?
• What is the frame & repeating frame?
• What is a combo box?
• What are three panes that appear in the run time pl/SQL interpreter?
• What are the two panes that Appear in the design time pl/SQL interpreter?
• What are the two ways by which data can be generated for a parameters list of values?
• What are the various methods of performing a calculation in a report?
• What are the default extensions of the files created by menu module?
• It is possible to use raw devices as data files and what is the advantages over file system files?
• What are disadvantages of having raw devices?
• What is the significance of having storage clause?
• What is the use of INCTYPE option in EXP command?
• What is the use of FILE option in IMP command?
• What is a Shared SQL pool?
• What is hot backup and how it can be taken?
• List the Optional Flexible Architecture (OFA) of Oracle database? How can we organize the tablespaces in Oracle database to have maximum performance?
• How to implement the multiple control files for an existing database?
• What is advantage of having disk shadowing/ Mirroring?
• How will you force database to use particular rollback segment?
• Give one method for transferring a table from one schema to another
• What is the purpose of the IMPORT option IGNORE? What is it?s default setting?
• You have a rollback segment in a version 7.2 database that has expanded beyond optimal, how can it be restored to optimal?
• If the DEFAULT and TEMPORARY tablespace clauses are left out of a CREATE USER command what happens? Is this bad or good? Why?
• What are some of the Oracle provided packages that DBAs should be aware of?
• What happens if the constraint name is left out of a constraint clause?
• What happens if a tablespace clause is left off of a primary key constraint clause?
• What is the proper method for disabling and re-enabling a primary key constraint?
• What happens if a primary key constraint is disabled and then enabled without fully specifying the index clause?
• (On UNIX) When should more than one DB writer process be used? How many should be used?
• You are using hot backup without being in archivelog mode, can you recover in the event of a failure? Why or why not?
• What causes the "snapshot too old" error? How can this be prevented or mitigated?
• How can you tell if a database object is invalid?
• A user is getting an ORA-00942 error yet you know you have granted them permission on the table, what else should you check?
• A developer is trying to create a view and the database won?t let him. He has the "DEVELOPER" role which has the "CREATE VIEW" system privilege and SELECT grants on the tables he is using, what is the problem?
• If you have an example table, what is the best way to get sizing data for the production table implementation?
• How can you find out how many users are currently logged into the database? How can you find their operating system id?
• A user selects from a sequence and gets back two values, his select is:
• How can you determine if an index needs to be dropped and rebuilt?
• A tablespace has a table with 30 extents in it. Is this bad? Why or why not.
• How do you set up tablespaces during an Oracle installation?
• You see multiple fragments in the SYSTEM tablespace, what should you check first?
• What are some indications that you need to increase the SHARED_POOL_SIZE parameter?
• What is the general guideline for sizing db_block_size and db_multi_block_read for an application that does many full table scans?
• What is the fastest query method for a table?
• Explain the use of TKPROF? What initialization parameter should be turned on to get full TKPROF output?
• When looking at v$sysstat you see that sorts (disk) is high. Is this bad or good? If bad -How do you correct it?
• When should you increase copy latches? What parameters control copy latches?
• Where can you get a list of all initialization parameters for your instance? How about an indication if they are default settings or have been changed?
• Describe hit ratio as it pertains to the database buffers. What is the difference between instantaneous and cumulative hit ratio and which should be used for tuning?
• Discuss row chaining, how does it happen? How can you reduce it? How do you correct it?
• When looking at the estat events report you see that you are getting busy buffer waits. Is this bad? How can you find what is causing it?
• If you see contention for library caches how can you fix it?
• If you see statistics that deal with "undo" what are they really talking about?
• If a tablespace has a default pctincrease of zero what will this cause (in relationship to the smon process)?
• If a tablespace shows excessive fragmentation what are some methods to defragment the tablespace? (7.1,7.2 and 7.3 only)
• How can you tell if a tablespace has excessive fragmentation?
• You see the following on a status report: redo log space requests 23; redo log space wait time 0; Is this something to worry about? What if redo log space wait time is high? How can you fix this?
• What can cause a high value for recursive calls? How can this be fixed?
• If you see a pin hit ratio of less than 0.8 in the estat library cache report is this a problem? If so, how do you fix it?
• If you see the value for reloads is high in the estat library cache report is this a matter for concern?
• You look at the dba_rollback_segs view and see that there is a large number of shrinks and they are of relatively small size, is this a problem? How can it be fixed if it is a problem?