Oracle Architecture Interview Questions & Answers
=======
Explain the methods provided by SQL Loader.
What is the use of Redo Log Information ?
SGA definition files
Explain different types of segment. Data segment, Index segment, Rollback segment and temporary segment.
Describe the different type of Integrity Constraints supported by ORACLE ?
What does a Control file Contain ?
What is the diff b/w BTREE INDEX and BITMAP INDEX
What is SQL Loader? Explain the files used by SQL Loader to load file. i.e Loader control file, Input datafile, Log File, Bad File, Discard file
Explain SGA memory structures: Shared Pool, Database buffer Cache, Redo log Cache, Large Pool Java Pool.
What is the diff b/w BTREE INDEX and BITMAP INDEX
What is the function of SMON?
Explain the areas of memory used by oracle, i.e. Software code area, system global area (SGA), program global area(PGA), sort area.
What are the Large object types supported by Oracle?
what is the difference between SID and Global Database Name ?
Explain the types of data files used by the oracle RDBMS.
What is the physical and logical structure of oracle?
What is SYSTEM tablespace and when is it Created?
What are the Large object types supported by Oracle?
What is an Oracle Data Dictionary?
What are the Characteristics of Data Files
How to define Data Block size in Oracle?
What are the Back ground processes in Oracle?
What is the differnece between materialized view and snapshot
What is the function of Redo Log ?
What is a Data Dictionary ?
Describe Oracle architecture in brief.
Can a View based on another View in Oracle?
Can an Integrity Constraint be enforced on a table if some existing table data does not satisfy the constraint?
Describe the different type of Integrity Constraints supported by ORACLE?
What is an oracle database schema?
Can a Tablespace hold objects from different Schemes in Oracle?
what is the differnece between materialized view and snapshot
Explain in brief oracle database objects.
What is an Oracle SYSTEM tablespace and when is it Created?
Explain the categories of oracle processes i.e. user, data writing processes, logging processes and monitoring processes.
Physical database structure
What are Oracle Schema Objects?
What is an Oracle Data Segment?
What is the use of Oracle Control File?
What is an Integrity Constrains in Oracle?
What are the uses of Oracle Rollback Segment?
Can objects of the same Schema reside in different tablespaces?
What is an Oracle Rollback Segment?
What is an Oracle Data Block?
What is the function of Redo Log in Oracle?
--------------------------------------------------------------------------------------------
Concepts and Architecture Interview Questions & Answers
=======
What is the maximum number of CHECK constraints that can be defined on a column ?
Can objects of the same Schema reside in different tablespaces.?
What is self-referential integrity constraint ?
Can an Integrity Constraint be enforced on a table if some existing table data does not satisfy the constraint ?
Explain the relationship among Database, Tablespace and Data file.
What are Clusters ?
What does a Control file Contain ?
What is cluster Key ?
Do View contain Data ?
How to define Data Block size ?
When can Hash Cluster used ?
What are the Characteristics of Data Files ?
What is Network Database link ?
What is SYSTEM tablespace and when is it Created?
What are the Referential actions supported by FOREIGN KEY integrity constraint ?
What is concept in architecture? is concept useful? do users understand the concept of the building? does concept affect the user?
What are the Limitations of a CHECK Constraint ?
What are the components of Logical database structure of ORACLE database?
What is an Extent ?
What are the different type of Segments ?
What are Schema Objects ?
What is the function of Redo Log ?
What is the use of Redo Log Information ?
What is difference between UNIQUE constraint and PRIMARY KEY constraint ?
What is Table ?
What is Index Cluster ?
Describe Referential Integrity ?
Can a Tablespace hold objects from different Schemes ?
What is a Data Dictionary ?
What is Data Block ?
How are Indexes Update ?
What is a Segment ?
What are synonyms used for ?
What are the uses of Rollback Segment ?
What is a Private Synonyms ?
What is Rollback Segment ?
What is a View ?
What is a Data File ?
What is Private Database Link ?
Can a View based on another View ?
What are the types of Database Links ?
What is a Tablespace?
What is a Synonym ?
What is Row Chaining ?
What is a Data Segment ?
Describe the different type of Integrity Constraints supported by ORACLE ?
What is a Sequence ?
What is a Temporary Segment ?
What are the advantages of Views ?
What is an Index ?
What are the components of Physical database structure of Oracle Database?
What is an Index Segment ?
What are the type of Synonyms?
What is Public Database Link ?
What is the use of Control File ?
What is a Redo Log ?
What is schema?
What is Hash Cluster ?
What is a Public Synonyms ?
What is Database Link ?
What is an Integrity Constrains ?
==============================================
Backup and Recovery Interview Questions & Answers
===============================================
What are the steps involved in Instance Recovery ?
What is Log Switch ?
What is Partial Backup ?
What are the steps involved in Database Startup ?
Can Full Backup be performed when the database is open ?
What is the steps for recovery of missing data file? and the steps for physical stand by database creation?
What is the steps for recovery with missing online redo logs?
What is Archived Redo Log ?
What are the different modes of mounting a Database with the Parallel Server ?
What is Restricted Mode of Instance Startup ?
What are the steps involved in Database Shutdown ?
What is Mirrored on-line Redo Log ?
What are the advantages of operating a database in ARCHIVELOG mode over operating it in NO ARCHIVELOG mode ?
What are the advantages of operating a database in ARCHIVE LOG mode over operating it in NO ARCHIVE LOG mode ?
What is On-line Redo Log?
What is Full Backup ?
What is the difference between oracle media recovery and crash recovery?
Why more redos are generated when the oracle database is in begin backup mode?
What is the disk migration? what is the steps involved in oracle disk migration?
What is an Oracle database Partial Backup?
What is db_recovery_file_dest in oracle? When do you need to set this value? Give me the steps to perform the point in time recovery with a backup which is taken before the reset logs of the db? Tell me about the steps required to enable the RMAN backup for a target database?
What are the different tools available for hot backups in Oracle? Is it preferable to take it manually all the time or it depends on the size of the database?
Can we create procedures to fetch more than one record?
What is an Oracle database Full Backup?
What is Restricted Mode of Instance Startup in Oracle?
What is the difference between recovery and restoring of the oracle database?
What do you mean by Oracle MEDIA RECOVERY?
What are the advantages of operating a database in ARCHIVELOG mode over operating it in NO ARCHIVELOG mode in Oracle?
===========================================
Data Access Interview Questions & Answers
===========================================
Define Transaction ?
What does COMMIT do ?
What is Read-Only Transaction ?
What does ROLLBACK 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 ?
What is COST-based approach to optimization ?
What are the values that can be specified for OPTIMIZER MODE Parameter ?
What are the different approaches used by Optimizer in choosing an execution plan ?
What are the factors that affect OPTIMIZER in choosing an Optimization approach ?
What is Execution Plan ?
What is the effect of setting the value "CHOOSE" for OPTIMIZER_GOAL, parameter of the ALTER SESSION Command ?
When does a Transaction end ?
What is SAVE POINT ?
What is the effect of setting the value "CHOOSE" for OPTIMIZER_GOAL, parameter of the ALTER SESSION Command ?
Can we delete tuples in a table based on date column? And how?
There is a field of type character in the table, but we are storing numeric values like (Roll number) in this field, so if we want to sort the field (in java) according to numbers.How?
What is the effect of setting the value "ALL_ROWS" for OPTIMIZER_GOAL parameter of the ALTER SESSION command ?
What are cursors?
What is the function of Optimizer ?
What is difference between Rename and Alias?
Explain What is SAVE POINT?
What is COST-based approach to optimization?
What is the function of Optimizer ?
Explain What is Execution Plan?
What does COMMIT do ?
When does a Transaction end ?
What is COST-based approach to optimization?
What is SAVE POINT ?
What is Read-Only Transaction ?
Can you tell the difference between DELETE & TRUNCATE commands?
What does ROLLBACK do ?
What is COST-based approach to optimization ?
Explain Can we delete tuples in a table based on date column? And how?
Explain What is Read-Only Transaction?
Define Transaction ?
How you will avoid your query from using indexes?
What are the values that can be specified for OPTIMIZER MODE Parameter ?
Will the Optimizer always use COST-based approach if OPTIMIZER_MODE is set to "Cost'?
Explain What are the factors that affect OPTIMIZER in choosing an Optimization approach?
Can we use Truncate command on a table which is referenced by FOREIGN KEY?
Explain There is a field of type character in the table, but we are storing numeric values like (Roll number) in this field, so if we want to sort the field (in java) according to numbers. How?
Explain What are the factors that affect OPTIMIZER in choosing an Optimization approach?
Explain What are the values that can be specified for OPTIMIZER MODE Parameter?
What is a OUTER JOIN?
What is the effect of setting the value "ALL_ROWS" for OPTIMIZER_GOAL parameter of the ALTER SESSION command ?
What is the basic difference between clustered and a non-clustered index?
What is difference between UNIQUE and PRIMARY KEY constraints?
What is a Stored Procedure
Explain When does a Transaction end?
What is the effect of setting the value of OPTIMIZER_MODE to 'RULE' ?
What is RULE-based approach to optimization ?
What is an Index?
Explain What are the different approaches used by Optimizer in choosing an execution plan ?
What is a trigger?
Explain What does ROLLBACK do?
Explain What are the values that can be specified for OPTIMIZER_GOAL parameter of the ALTER SESSION Command?
What is Execution Plan ?
What are the different approaches used by Optimizer in choosing an execution plan ?
What are the values that can be specified for OPTIMIZER_GOAL parameter of the ALTER SESSION Command ?
What is the effect of setting the 'RULE' for OPTIMIER_GOAL parameter of the ALTER SESSION Command ?
Explain What are the values that can be specified for OPTIMIZER_GOAL parameter of the ALTER SESSION Command?
What are the factors that affect OPTIMIZER in choosing an Optimization approach ?
What is a view?
Explain What is the effect of setting the value "ALL_ROWS" for OPTIMIZER_GOAL parameter of the ALTER SESSION command?
=================================
DBA ADMINISTRATION
=================================
What is a deadlock ? Explain .
What is ORA-1555?
Can objects of the same schema reside in different tablespaces
Can we decrease the Datafile size?
Whats the difference between indexes,views,synonyms?
Do you need a commit after DDL statements?
What is the use of control file
What are the basic element of base configuration of an Oracle database
How do you handle exceptions / errors in stored procedures?
What are Schema Objects
What does a control file contains
What are the different types of segments
What are the types of synonyms
What rule optimizations are possible in SQL query tuning?
What are the components of physical database structure of Oracle database
What is a cursor? Why do you need them? What are the different kinds of cursor?
Is it possible to disable the connection sys as sysdba. If yes, does violates oracle 9i Any attempts would be appreciated
Different types of table join.
What is the difference between DATABASE ADMINISTRATION & ORACLE?
What are the uses of rollback segment
What is a synonym
What are the components of logical database structure of Oracle database
What is Parallel Server ?
How can you find all the tables created by an user?
What is network database link
What is private database link
What is an index segment
Name some built-in functions that can be used in SQL queries?
Explain the relationship among database, tablespace and data file.What is schema
What is public database link
What is row chaining
What are synonyms used for
What is a database instance and Explain
What is an extent
What Is a Initialization Parameter File in Oracle?
If you have an open cursor on a table, modify the same tableand commit, what would happen?
Do a view contain data
What Is a Dynamic Performance View in Oracle?
What is clusters ?
What is a lookup table in database? where can we use these type of table?
What is Program Global Area (PGA) in Oracle?
What is a cluster key
What is a datafile
What is an index and How it is implemented in Oracle database
What are the types of database links
Is it advisable to force an index when you want to select mostof the entries in the table?
Whenever a DBA creates a user in which datafile that will be get created ?
What is an Oracle sequence
How are the index updates
What is a Database instance ? Explain
What is the use of redo log information
What are clusters
What is index cluster
1)What ll be in the redolog file after deleting the table ,will it stores the table or any thing else? 2)Can we resize the redolog files?
What is System Global Area (SGA) in Oracle?
What is an Oracle view
What is Oracle table
What is a Schema ?
What is the function of redo log
What is a cluster Key ?
What is a deadlock and Explain
When can hash cluster used
What is Parallel Server
What is an Index ? How it is implemented in Oracle Database ?
What is a schema
Can a view based on another view
Can a tablespace hold objects from different schemes
What is a temporary segment
How is locking implemented?
What is a segment
How would you pass hints to the SQL processor?
What is a tablespace
What is the relation between free list and buffer busy wait?
What is hash cluster
How do you sort a table?
What is SYSTEM tablespace and when is it created
What is a public synonym
What is data block
What is database link
What are the advantages of views
What are clusters
What is DDL, DML?
What is a private synonym
What is a Database Schema in Oracle?
What is a data segment
What is rollback segment
An Oracle data file is a big unit of physical storage in the OS file system. One or many Oracle data files are organized together to provide physical storage to a single Oracle tablespace.
How to define data block size
What are the characteristics of data files
What are the basic element of Base configuration of an oracle Database ?
What is an Oracle index
What is cluster key
What Is an Oracle Data File?
What is a redo log
What Is SQL*Plus?
Explain What is an Oracle sequence?
What Is a Recycle Bin in Oracle?
What Is an Oracle Tablespace?
Explain When can hash cluster used?
What Is the Relation of a User Account and a Schema in Oracle?
What Is a Database Table in Oracle?
Explain Different types of table join?
Explain What is a cursor? Why do you need them? What are the different kinds of cursor?
Explain What is a lookup table in database? where can we use these type of table?
Explain What is hash cluster?
Explain What is index cluster?
Explain database clusters?
Explain What is a tablespace?
What is the difference between indexes,views,synonyms?
What Is a User Role in Oracle?
What Is a Table Index in Oracle?
Explain Do a view contain data?
What Is an Oracle Instance?
Explain What is ORA-1555?
Explain How do we handle exceptions / errors in stored procedures?
Explain What are the basic element of base configuration of an Oracle database?
What Is Oracle?
What Is a Server Parameter File in Oracle?
What Is an Oracle Database?
Explain What is an extent?
Tell us Do you need a commit after DDL statements?
What Is a User Account in Oracle?
What Is a Parameter File in Oracle?
============================================
Database Security Interview Questions & Answers
============================================
What is default tablespace ?
How can you enable automatic archiving ?
What are the responsibilities of a Database Administrator ?
What is Auditing ?
What are the steps to creating a password authentication file?
What is Object Auditing ?
How we can secure our data in local area Network
What is Tablespace Quota ?
Explain the security features in Oracle.
Describe the advantages and disadvantages of DBMS-provided and application-provided security.
Explain the difference between an explicit and an implicit lock.
Explain the difference between a database administrator and a data administrator.
What is user Account in Oracle database?
Define rollback and roll forward.
What are roles? How can we implement roles ?
In general, how should the boundaries of a transaction be defined?
How can we specify the Archived log file name format and destination?
What are Tablespace Quota and default tablespace?
if we have database,then how can we tell the data in the "database is secured" . 2)what is ment by "data validation"?
Why is it important to write to the log before changing the database values?
What are the system resources that can be controlled through Profile ?
What is a trace file and how is it created ?
What are the minimum parameters should exist in the parameter file (init.ora) ?
What are Roles ?
What is a profile ?
What is the use of ANALYZE command ?
What are the database administrators utilities avaliable ?
Explain the purpose and scope of database security?
What is default tablespace ?
What is Statement Auditing ?
What are the different Levels of Auditing ?
What is a trace file and how is it created?
What is user Account in Oracle database?
What are the roles and user accounts created automatically with the database?
What are the dictionary tables used to monitor a database spaces ?
What are the use of Roles ?
Explain how a database could be recovered via reprocessing. Why is this generally not feasible?
What is Privilege Auditing ?
Explain How can you specify the Archived log file name format and destination?
Explain Statement Auditing, Privilege Auditing, And Object Auditing.
Question
Rating
Explain What is Tablespace Quota?
What are roles? How can we implement roles?
How will you enforce security using stored procedures?
Explain the meaning of the expression ACID transaction.
Explain What is an Object Auditing?
Explain What is default tablespace?
What are the dictionary tables used to monitor a database spaces?
What is Auditing? What are the different Levels of Auditing?
Explain What are the responsibilities of a Database Administrator?
How to protect a database publicly accessible on the net ?
What is lock granularity?
Name three Database Privileges.
Explain What are the roles and user accounts created automatically with the database?
What is the use of ANALYZE command?
What is a trace file and how is it created?
Explain What are the system resources that can be controlled through Profile?
Explain How you can secure our data in local area Network?
Explain What is a trace file and how is it created?
Explain What is an Auditing?
Explain What are the steps to creating a password authentication file?
Explain What are Roles?
Explain What is Auditing? What are the different Levels of Auditing?
Explain What is Statement Auditing?
Explain What is an Auditing?
Explain What are Roles?
Explain What is Auditing? What are the different Levels of Auditing?
What are roles? How can we implement roles?
What are the security features in Oracle?
What are the steps to switch a database's archiving mode between NO ARCHIVELOG and ARCHIVELOG mode?
Explain What is user Account in Oracle database?
Explain What is Privilege Auditing?
Explain What is a user Account in Oracle database?
What are the minimum parameters should exist in the parameter file (init.ora)?
Explain What is Statement Auditing?
What are the roles and user accounts created automatically with the database?
Explain What is user Account in Oracle database?
How can you enable automatic archiving?
How to implement database security ?
How can we specify the Archived log file name format and destination?
Explain What are the steps to creating a password authentication file?
Explain What are the system resources that can be controlled through Profile?
Explain What are the database administrators utilities avaliable?
What are the database administrator?s utilities avaliable?
Explain What are roles? How can you implement roles?
Explain How you can secure our data in local area Network?
Explain What are the database administrators utilities avaliable?
Explain What is a trace file and how is it created?
What is user Account in Oracle database?
Explain How can we enable automatic archiving?
Explain if you have database,then how can you tell the data in the "database is secured". And what is ment by "data validation"?
Explain What is an Object Auditing?
How will you enforce security using stored procedures?
Explain What is Tablespace Quota?
Explain What is Privilege Auditing?
Explain What is Auditing? What are the different Levels of Auditing?
What is a database security profile?
===========================================
Distributed Processing Interview Questions & Answers
===========================================
What is Distributed database ?
What is Oracle Distributed database?
What is the mechanism provided by ORACLE for table replication ?
Differentiate simple and complex, snapshots ?
How can you Enforce Referential Integrity in oracle snapshots?
What are the benefits of distributed options in databases?
What is a SNAPSHOT LOG ?
Describe two phases of Two-phase commit ?
What are the options available to refresh snapshots ?
What is Two-Phase Commit ?
How can you Enforce Referential Integrity in snapshots ?
What are the advantages of DCE?
What is snapshot log ?
How can we reduce the network traffic in Distributed Processing?
What is a SNAPSHOT ?
What is a SQL * NET?
What is DCE?
What is an oracle snapshot log?
What is snapshots?
When will the data in the snapshot log be used?
How can we reduce the network traffic?
What dynamic data replication?
What is Oracle Two-Phase Commit?
Differentiate simple and complex, oracle snapshots?
What is Oracle Distributed database?
What are the various type of snapshots?
Can a DCE client import multiple interfaces?
What is an oracle snapshot log?
What is Oracle Two-Phase Commit?
SHOULD ALL MY OBJECTS BE DISTRIBUTED?
What is Oracle Two-Phase Commit?
Describe two phases of Oracle Two-phase commit?
Differentiate simple and complex, oracle snapshots?
When will the data in the oracle snapshot log be used?
What are the options available to refresh Oracle snapshots?
What are the various type of snapshots in Oracle?
What is an Oracle SQL * NET?
What is an oracle SNAPSHOT?
WHAT IS A DISTRIBUTED OBJECT?
Is DCE an official standard?
Describe two phases of Oracle Two-phase commit?
What platforms support DCE?
What products use DCE?
HOW ARE DISTRIBUTED OBJECTS ACTUALLY IMPLEMENTED?
What is an Oracle SQL * NET?
What are the various type of snapshots in Oracle?
What are the options available to refresh Oracle snapshots?
When will the data in the oracle snapshot log be used?
What is an oracle SNAPSHOT?
How can you Enforce Referential Integrity in oracle snapshots?
================================
General Oracle Interview Questions & Answers
=============================================
Difference between DBMS & RDBMS?
How can I get 5th highest sal. from employee tab?
POPULATE_GROUP(function) POPULATE_GROUP_WITH_QUERY(function) SET_GROUP_CHAR_CELL(procedure) SET_GROUP_DATE_CELL(procedure) SET_GROUP_NUMBER_CELL(procedure)
Use the Add_group_row procedure to add a row to a static record group 1. true or false?
What are the important difference between property clause and visual attributes?
what are difference between post database commit and post-form commit?
Use the ADD_GROUP_COLUMN function to add a column to a record group that was created at design time.I) TRUE II)FALSE
What is a difference between pre-select and pre-query?
What is the Maximum allowed length of Record group Column?
What is the "LOV of Validation" Property of an item? What is the use of it?
What are the difference between lov & list item?
What is forms_DDL?
Why is it preferable to create a fewer no. of queries in the data model?
What are the three types of user exits available ?
How do you create a new session while open a new form?
What is a Query Record Group?
What are the triggers available in the reports?
What is a Text_io Package?
What are the Coordination Properties in a Master-Detail relationship?
How is it possible to select generate a select set for the query in the query property sheet?
What is the difference between two null values in a column?
What are the different display styles of list items?
Can a formula column referred to columns in higher group?
What is the difference between SHOW_EDITOR and EDIT_TEXTITEM?
What are the default parameter that appear at run time in the parameter screen?
What is new_form built-in?
Explain about horizontal, Vertical tool bar canvas views?
What are the two ways by which data can be generated for a parameters list of values?
In Oracle version 9.2.0.4.0 what does each number refers to?
What is trigger associated with the timer?
What is a record group?
What is the difference between OLE Server & Ole Container?
What are the different default triggers created when Master Deletes Property is set to isolated?
What are three panes that appear in the run time pl/sql interpreter?
What are the vbx controls?
How to create receipts automatically from bill receivables transactions?
What is the use of transactional triggers?
What is system.coordination_operation?
What are the different file extensions that are created by oracle reports?
What is the diff. when confine mode is on and when it is off?
State any three mouse events system variables?
To display the page no. for each page on a report what would be the source & logical page no. or & of physical page no.?
What is the use of image_zoom built-in?
What are the possible clauses included in the CREATE TABLE command?
Is the After report trigger fired if the report execution fails?
Does a grouping done for objects in the layout editor affect the grouping done in the data model editor?
What are the two types of views available in the object navigator(specific to report 2.5)?
What is the diff. when Flex mode is mode on and when it is off?
Can a formula column be obtained through a select statement?
The join defined by the default data link is an outer join yes or no?
What are the ways to monitor the performance of the report?
At what point of report execution is the before Report trigger fired?
Is it possible to split the print reviewer into more than one region?
What are the different windows events activated at runtimes?
What is the remove on exit property?
What are Most Common types of Complex master-detail relationships?
What are the different default triggers created when Master Deletes Property is set to Non-isolated?
What is the difference between boiler plat images and image items?
What are the sql clauses supported in the link property sheet?
What are modalless windows?
Name of the functions used to get/set canvas properties?
What are different types of modules available in oracle form?
What are the built-ins used for finding object ID functions?
What is a combo box?
Use the add_group_column function to add a column to record group that was created at a design time?
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?
What is a physical page ? & What is a logical page ?
What are the built-ins used for processing rows?
What are the built-ins that are used to Attach an LOV programmatically to an item?
What are the two repeating frame always associated with matrix object?
What are the built-ins that are used for setting the LOV properties at runtime?
What is the difference between $$DATE$$ & $$DBDATE$$$$DBDATE$$ retrieves the current database date$$date$$ retrieves the current operating systemdate.
What is pop list?
How do you reference a parameter indirectly?
What is the use of place holder column?
Which of the two views should objects according to possession?
What is difference between open_form and call_form?
What is bind reference and how can it be created?
What does the term panel refer to with regard to pages?
Can a property clause itself be based on a property clause?
What is the frame & repeating frame?
What is lexical reference? How can it be created?
What is an LOV?
What are the various methods of performing a calculation in a report ?
What are the Built-ins to display the user-named editor?
When do you use data parameter type?
Is it possible to center an object horizontally in a repeating frame that has a variable horizontal size?
If a break order is set on a column would it affect columns which are under the column?
Is it possible to disable the parameter from while running the report?
What are different types of images?
How do you call other Oracle Products from Oracle Forms?
If you increase the size of the database naturally the memory has to be increased for better performance.What is the proportion in which the memory has to be added for the corresponding increase in the disk space?
What are the built-ins used for Creating and deleting groups?
What is a name space and how it differs from a schema?
How is link tool operation different bet. reports 2 & 2.5?
What is strip sources generate options?
What are the types of calculated columns available?
What is the advantage of the library?
What are the built_ins used the display the LOV?
When a form is invoked with call_form, Does oracle forms issues a save point?
If the maximum record retrieved property of the query is set to 10 then a summary value will be calculated?
How do you reference a Parameter?
How do you display console on a window ?
What is a Static Record Group?
What are the two panes that Appear in the design time pl/sql interpreter?
Is it possible to link two groups inside a cross products after the cross products group has been created?
Can you have more than one content canvas view attached with a window?
What is a timer?
What are built-ins used for Processing rows?
What are the Built-ins used for sending Parameters to forms?
How can a square be drawn in the layout editor of the report writer?
Where is a procedure return in an external pl/sql library executed at the client or at the server?
What are visual attributes?
What are the built-ins used for Getting cell values?
Atleast how many set of data must a data model have before a data model can be base on it?
What are the different types of Record Groups?
What is synchronize?
What is the diff. bet. setting up of parameters in reports 2.0 reports2.5?
How many windows in a form can have console?
What is the difference between object embedding & linking in Oracle forms?
Question
Rating
What are the various sub events a mouse double click event involves?
What is a master detail relationship?
What are the different types of Delete details we can establish in Master-Details?
What are the different modals of windows?
What are the different objects that you cannot copy or reference in object groups?
How can I message to passed to the user from reports?
Which parameter can be used to set read level consistency across multiple queries?
What are the built-ins used for finding Object ID function?
What use of command line parameter cmd file?
What are parameters?
What is the User-Named Editor?
What is use of term?
Can a repeating frame be created without a data group as a base?
What is term?
If a parameter is used in a query without being previously defined, what diff. exist betw. report 2.0 and 2.5 when the query is applied?
What are modal windows?
Can you pass data parameters to forms?
What are the default extensions of the files created by library module?
What is an anchoring object & what is its use?
What are the different Parameter types?
What are the different styles of activation of ole Objects?
What are the built-in used for getting cell values?
What are the different types of Coordinations of the Master with the Detail block?
Can a field be used in a report without it appearing in any data group?
What is an object group?
What are the default extensions of the files created by menu module?
What are the trigger associated with image items?
What is a User_exit?
What is a library?
Where is the external query executed at the client or the server?
If yes, how?
Do user parameters appear in the data modal editor in 2.5?
What is relation between the window and canvas views?
How is possible to restrict the user to a list of values while entering values for parameters?
What are the two phases of block coordination?
What is the use of break group?
How many number of columns a record group can have?
What are the different default triggers created when Master Deletes Property is set to Cascade?
How can a text file be attached to a report while creating in the report writer?
To execute row from being displayed that still use column in the row which property can be used?
Sort By : Latest First | Oldest First | By Rating
Question
Rating
Is it possible to modify an external query in a report which contains it?
From which designation is it preferred to send the output to the printed?
With which function of summary item is the compute at options required?
What are the built -ins used for Modifying a groups structure?
How can a cross product be created?
What is the use of hidden column?
What is coordination Event?
How can a group in a cross products be visually distinguished from a group that does not form a cross product?
Is it possible to have a link from a group that is inside a cross product to a group outside ?
What is an user exit used for?
Explain about stacked canvas views?
What is the purpose of the product order option in the column property sheet?
What is a property clause?
What is a text list?
What is the main diff. bet. Reports 2.0 & Reports 2.5?
Is it possible to insert comments into sql statements return in the data model editor?
An open form can not be execute the call_form procedure if you chain of called forms has been initiated by another open form?
What is WHEN-Database-record trigger?
Does a Before form trigger fire when the parameter form is suppressed?
What is the maximum no of chars the parameter can store?
Any attempt to navigate programmatically to disabled form in a call_form stack is allowed?
What are built-ins associated with timers?
How can values be passed bet. precompiler exits & Oracle call interface?
What is an OLE?
What are different types of canvas views?
Is it possible to set a filter condition in a cross product group in matrix reports?
Give the sequence of execution of the various report triggers?
How can a break order be created on a column in an existing group?
What is the basic data structure that is required for creating an LOV?
Explain about content canvas views?
What are the master-detail triggers?
What are display items?
Which of the above methods is the faster method?
What are the default extensions of the files created by forms modules?
Why is a Where clause faster than a group filter or a format trigger?
How can a button be used in a report to give a drill down facility?
If two groups are not linked in the data model editor, What is the hierarchy between them?
What is the diffrence between Join query using where condition and join query using on clause?
===============================================================
Memory Management Interview Questions & Answers
===============================================================
What is the advantage of Base-Limit Registers in comparison to PIC and Relocation?
What is advantage of having disk shadowing / mirroring
What is the use of RECORD LENGTH option in EXP command ?
How can we organize the tablespaces in Oracle database to have maximum performance
What is meant by recursive hints ?
How does memory compaction work?
How can reply if interviewer say thats why you choose B.Tech after passing the exam of Bsc.
What is meant by a dirty page?
What are the three tricks used to resolve absolute addresses?
What is SGA
What is a shared pool
What is redo log file mirroring
Which parameter in Storage clause will reduce number of rows per block
What is meant by recursive hints
What is use of rollback segments in Oracle database
What is hit ratio ?
Why is a memory map used?
How will you force database to use particular rollback segment
What is meant by redo log buffer ?
What is contained within a page table entry?
What is the use of COMPRESS option in EXP command ?
What is the OPTIMAL parameter?
What is the use of OWNER option in EXP command ?
What are the four main functions of a memory manager?
How the space utilization takes place within rollback segments ?
What is hit ratio
How will you swap objects into a different table space for an existing database
What is Database Buffers ?
What is the use of PARFILE option in EXP command ?
What is a logical backup?
What are the factors causing the reparsing of SQL statements in SGA
What is dictionary cache
How will you monitor the space allocation
How to implement the multiple control files for an existing database
What is the use of INDEXES option in EXP command ?
What is the use of GRANT option in IMP command ?
How can we plan storage for very large tables ?
What is the use of CONSTRAINTS option in EXP command ?
List the Optional Flexible Architecture (OFA) of Oracle database? or How can we organize the tablespaces in Oracle database to have maximum performance ?
List the factors that can affect the accuracy of the estima
What is the use of INDEXES option in IMP command ?
What is the role of PCTFREE parameter is storage clause
How will you monitor rollback segment status
How can the problem of TLB during context switching be avoided?
What is Database Buffers
When will be a segment released ?
What is dictionary cache ?
What is the use of ROWS option in IMP command ?
What is a data segment?
What are disadvantages of having raw devices
What is cold backup? What are the elements of it?
How will you monitor the space allocation ?
What is the use of SHOW option in IMP command ?
What is a Rollback segment entry ?
What is an overlay?
What is mean by Program Global Area (PGA) ?
What is redo log buffer
What is the use of FILE option in IMP command ?
What is mean by Program Global Area (PGA)
Why query fails sometimes
How will you create multiple rollback segments in a database ?
How will you estimate the space required by a non-clustered tables?
What is a Control file
Why are page sizes powers of two? Why is this necessary?
When will be a segment released
What is a data segment
How can we plan storage for very large tables
What is the use of GRANT option in EXP command?
What are the factors causing the reparsing of SQL statements in SGA?
What is hot backup and how it can be taken?
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 another extend.
What is the OPTIMAL parameter
What is the significance of having storage clause ?
It is possible to use raw devices as data files and what are the advantages over file system files
List the factors that can affect the accuracy of the estimations?
What is meant by free extent ?
How redo logs can be achieved
What is a trick that a process can use to bypass BL limit restrictions? How is the system safe from this trick?
What is the functionality of SYSTEM table space
What is the method of relocation?
How free extents are managed in Ver 6.0 and Ver 7.0 ?
How will you force database to use particular rollback segment ?
What is a Shared SQL pool?
How does Space allocation table place within a block
What are the different kind of export backups?
What is a Control file ?
What is use of Rollback Segments In Database ?
What is the use of RECORD option in EXP command?
Which parameter in Storage clause will reduce number of rows per block?
How redo logs can be achieved?
What is a downfall of memory compaction?
How will you swap objects into a different table space for an existing database ?
What is the functionality of SYSTEM table space ?
What are the problems that arise with absolute addresses in terms of swapping?
What is the use of IGNORE option in IMP command ?
Which parameter in Storage clause will reduce no. of rows per block?
Why would an overlay be used?
What is meant by free extent
What is a rollback segment entry
What is the significance of having storage clause
Sort By : Latest First | Oldest First | By Rating
Question
Rating
What is the use of TABLES option in EXP command ?
How the space utilization takes place within rollback segments
What are disadvantages of having raw devices ?
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 another extend.
What is advantage of having disk shadowing/ Mirroring ?
How to implement the multiple control files for an existing database ?
How will you monitor rollback segment status ?
What is the use of ANALYSE ( Ver 7) option in EXP command ?
How does Space allocation table place within a block ?
How will you estimate the space required by a non-clustered tables
What are the different methods of backing up oracle database ?
What is the use of PARFILE option in EXP command ?
What is SGA? How it is different from Ver 6.0 and Ver 7.0?
It is possible to use raw devices as data files and what is the advantages over file. system files ?
How does Space allocation table place within a block?
What is the role of PCTFREE parameter is Storage clause ?
What is the use of FILE option in EXP command?
How can we organize the tablespaces in Oracle database to have maximum performance?
What is use of LOG (Ver 7) option in EXP command ?
What is a logical backup?
List the Optional Flexible Architecture (OFA) of Oracle database
What is the use of ROWS option in EXP command ?
What is meant by Redo Log file mirroring ? How it can be achieved?
List the factors that can affect the accuracy of the estimations?
How will you create multiple rollback segments in a database
What is the use of FULL option in EXP command ?
What is the use of CONSISTENT (Ver 7) option in EXP command ?
How can we plan storage for very large tables?
What is a Shared SQL pool?
What is the use of INCTYPE option in EXP command ?
Why query fails sometimes ?
What is the use of ROWS option in IMP command?
What is redo log buffer?
How will you monitor the space allocation?
How the space utilization takes place within rollback segments?
What is the role of PCTFREE parameter is storage clause?
What is hot backup and how it can be taken?
What is redo log file mirroring?
How will you monitor rollback segment status?
What is the use of GRANT option in EXP command?
What is use of LOG (Ver 7) option in EXP command?
How will you swap objects into a different table space for an existing database?
What is meant by recursive hints?
What is the use of FILE option in IMP command?
How to modify ganged and unganged memory modes in intel processors?
Why query fails sometimes?
How will you force database to use particular rollback segment?
What is use of rollback segments in Oracle database?
What is the use of PARFILE option in EXP command?
What is the use of RECORD LENGTH option in EXP command?
How to implement the multiple control files for an existing database?
What is mean by Program Global Area (PGA)?
What is advantage of having disk shadowing / mirroring?
What is the use of FULL option in EXP command?
What is dictionary cache?
What is meant by recursive hints?
What is cold backup? What are the elements of it?
What is the use of GRANT option in IMP command?
What is the use of SHOW option in IMP command?
What is meant by free extent?
It is possible to use raw devices as data files and what are the advantages over file system files?
What is a Control file?
What is a rollback segment entry?
What is hit ratio?
What is the use of INDEXES option in IMP command?
What are the factors causing the reparsing of SQL statements in SGA?
What is the use of FILE option in EXP command?
What is the use of ROWS option in EXP command?
What is the use of TABLES option in EXP command?
What is the use of INCTYPE option in EXP command?
What is the use of RECORD option in EXP command?
What is the significance of having storage clause?
What is the OPTIMAL parameter?
What is a shared pool?
What is a data segment?
How will you create multiple rollback segments in a database?
What is SGA? How it is different from Ver 6.0 and Ver 7.0?
What are the different kind of export backups?
What is the use of INDEXES option in EXP command?
What is the use of ANALYSE ( Ver 7) option in EXP command?
What is the use of OWNER option in EXP command?
What is the functionality of SYSTEM table space?
What is a shared pool?
What is mean by Program Global Area (PGA)?
What is Database Buffers?
How will you estimate the space required by a non-clustered tables?
What are disadvantages of having raw devices?
=================================================
Scenarios Interview Questions & Answers
=================================================
How to do the scheduled task/jobs in Unix platform
What is Testing Scenario ? What is scenario based testing? can u explain with an example ?
If the SQL * Plus hangs for a long time, what is the reason
What are the differences between database designing and database modeling
If the entire disk is corrupted how will you and what are the steps to recover the database
All the users are complaining that their application is hanging. How you will resolve this situation in OLTP
Tell me the total process of eim?
How do you increase the performance of %LIKE operator
Schema A has some objects and created one procedure and granted to Schema B. Schema B has the same objects like schema A. Schema B executed the procedure like inserting some records. In this case where the data will be stored whether in Schema A or Schema B
Dual table explain. Is any data internally storing in dual table. 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
Shall we create procedures to fetch more than one record
What are the differences you have seen while installing Oracle on NT and Unix platform
What is mutated trigger, is it the problem of locks. In single user mode we got mutated error, as a DBA how you will resolve it
How can you see the source code of the package
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 is bulk SQL
What is spooling
If the large table contains thousands of records and the application is accessing 35% of the table which method to use: index searching or full table scan
You are regularly changing the package body part. How will you create or what will you do before creating that package
Explain the total process of eim?
Question
Rating
Explain What is spooling?
Explain How do we increase the performance of %LIKE operator?
Explain What are the differences you have seen while installing Oracle on NT and Unix platform?
In which situation whether peak time or off peak time you will execute the ANALYZE TABLE command. Why
Explain How to do the scheduled task/jobs in Unix platform?
Explain the Dual table. Is any data internally storing in dual table. 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 and Why?
Explain about the oracle disaster recovery scenarios?
Explain If the entire disk is corrupted how will you and what are the steps to recover the database?
Explain What is Testing Scenario? What is scenario based testing? can u explain with an example?
Explain If the large table contains thousands of records and the application is accessing 35% of the table which method to use: index searching or full table scan?
Explain If the SQL * Plus hangs for a long time, what is the reason?
Explain about the oracle disaster recovery scenarios?
Explain 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?
Explain If the entire disk is corrupted how will you and what are the steps to recover the database?
Explain What is mutated trigger, is it the problem of locks. In single user mode we got mutated error, as a DBA how you will resolve it?
We are regularly changing the package body part. How will we create or what will we do before creating that package?
Explain the Dual table. Is any data internally storing in dual table. 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 and Why?
Explain What is Testing Scenario? What is scenario based testing? can u explain with an example?
Explain What is spooling?
If All the users are complaining that their application is hanging. How we will resolve this situation in OLTP?
Schema A has some objects and created one procedure and granted to Schema B. Schema B has the same objects like schema A. Schema B executed the procedure like inserting some records. In this case where the data will be stored whether in Schema A or Schema B?
Tell us In which situation whether peak time or off peak time we will execute the ANALYZE TABLE command and Why?
Can we create procedures to fetch more than one record?
Explain What is bulk SQL?
Explain If the large table contains thousands of records and the application is accessing 35% of the table which method to use: index searching or full table scan?
Explain What is mutated trigger, is it the problem of locks. In single user mode we got mutated error, as a DBA how you will resolve it?
Explain What are the differences between database designing and database modeling?
Explain What is bulk SQL?
Explain How can we see the source code of the package?
We are regularly changing the package body part. How will we create or what will we do before creating that package?
Explain the total process of eim?
Explain 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?
Schema A has some objects and created one procedure and granted to Schema B. Schema B has the same objects like schema A. Schema B executed the procedure like inserting some records. In this case where the data will be stored whether in Schema A or Schema B?
Explain What are the differences between database designing and database modeling?
If All the users are complaining that their application is hanging. How we will resolve this situation in OLTP?
Tell us In which situation whether peak time or off peak time we will execute the ANALYZE TABLE command and Why?
Explain What are the differences you have seen while installing Oracle on NT and Unix platform?
Explain How can we see the source code of the package?
Explain How do we increase the performance of %LIKE operator?
Explain If the SQL * Plus hangs for a long time, what is the reason?
==============================================================
Oracle Architecture Interview Questions
==============================================================
What are the components of Physical database structure of Oracle Database?.
What is cluster Key ?
The related columns of the table in the cluster are called the cluster key
1. Explain: a.) Integrity Constraints
It can be called as a declarative way in order to define a business rule for a table's column
b.) Index
- It can be called as an optional structure which is associated with a table for direct access to the rows
- Index can be created for one or more columns in a table
c.) Extent
- It can be defined as a specific number of contiguous data blocks in single allocation.
- It is used to store a specific type of information.
2. What is ANALYZE command used for?
ANALYZE command is used to perform various functions on index, table, or cluster, as listed below:
- It helps in dentifying migrated and chained rows of the table or cluster.
- It helps in validating the structure of the object.
- It helps in collecting the statistics about object used by the optimizer. They are then stored in the data dictionary.
- It helps in deleting statistics used by object from the data dictionary.
Oracle 11g dba interview questions and answers - 50 questions
Test your Oracle skills:
Oracle interview test part 1 (40 questions)
Oracle DBA interview test (30 questions)
Oracle PLSQL interview test (30 questions)
Oracle Replication interview test (20 questions)
Oracle Architecture interview test (20 questions)
Oracle Transaction interview test (20 questions)
3. a.) List the types of joins used in writing SUBQUERIES.
- Self join
- Outer Join
- Equi-join
b.) List the various Oracle database objects.
- TABLES
- VIEWS
- INDEXES
- SYNONYMS
- SEQUENCES
- TABLESPACES
4. a.) Explain i.)Rename ii.)Alias.
- Rename - It is a permanent name provided to a table or column.
- Alias - It is a temporary name provided to a table or column which gets over after the execution of SQL statement.
b.) What is a view?
- It is virtual table which is defined as a stored procedure based on one or more tables.
5. a.) What are the varoius components of physical database structure of Oracle database?
Oracle database comprises of three kinds of files:
- Datafiles,
- Redo log files,
- Control files.
b.) List out the components of logical database structure of Oracle database.
- Tablespaces
- Database's schema objects.
6. a.) What do you mean by a tablespace?
- These are the Logical Storage Units into which a database is divided.
- It is used to group together the related logical structures.
b.) What is Control File used for?
Control File is used for:
- Database recovery.
- Whenever an instance of an ORACLE database begins, its control file is used to identify the database and redo log files that must be opened for database operation to go ahead.
7. a.) What is a synonym? What are its various types?
A synonym can be called as an alias for a table, view, sequence or program unit. It is basically of two types:
- Private - Only the owner can access it.
- Public - Can be accessed by any database user.
b.) What are the uses of synonyms?
- Mask the real name and owner of an object.
- Provide public access to an object
- Provide location transparency for tables, views or program units of a remote database.
- Simplify the SQL statements for database users.
8.) What do you mean by a deadlock?
- When two processes are waiting to update the rows of a table which are locked by another process, the situation is called a deadlock.
- The reasons for it to happen are:
- lack of proper row lock commands.
- Poor design of front-end application
- It reduces the performance of the server severely.
- These locks get automatically released automatically when a commit/rollback operation is performed or any process is killed externally.
9.) a.) What suggestions do you have to reduce the network traffic?
Following are some of the actions which can be taken to reduce the network traffic:
- Use snapshots to replicate data.
- Use remote procedure calls.
- Replicate data in distributed environment.
b.) What are the various types of snapshots ?
There are two types of snapshots:
- Simple snapshots - Based on a query that does not contain GROUP BY clauses, CONNECT BY clauses, JOINs, sub-query or snashot of operations.
- Complex snapshots- which contains atleast any one of the above.
10.) What is a sub query? What are its various types?
- Sub Query also termed as Nested Query or Inner Query is used to get data from multiple tables.
- A sub query is added in the where clause of the main query.
There can be two types of subqueries:
a.) Correlated sub query
- It can reference column in a table listed in the from list of the outer query but is not as independent as a query. .
b.) Non-Correlated sub query
- Results of this sub query are submitted to the main query or parent query.
- It is independent like a query
11.) Will you be able to store pictures in the database?Explain.
- Yes, pictures can be stored in the database using Long Raw Data type.
- This datatype is used to store binary data for 2 gigabytes of length.
- However, the table can have only one Long Raw data type.
12.) Explain: a.) Integrity constraint.
- It is a declaration defined in a business rule for a table column.
- Integrity constraint ensures the accuracy and consistency of data in a database.
- It is of three types - namely - Domain Integrity, Referential Integrity and Domain Integrity.
b.) COALESCE function
- This function is used to return the value which is set not to be null in the list.
- Incase all values in the list are null the coalesce function will return NULL.
- Its representation:
Coalesce(value1, value2, value3,…)
13.) Explain the following: a.) BLOB datatype.
- It is a data type with varying length binary string, used to store two gigabytes memory.
- For BLOB, the length needs to be specified in bytes.
b.) DML.
- DML - it is also termed as Data Manipulation Language (DML).
- It is used to access and manipulate data in the existing objects.
- DML statements are insert, select, update and delete.
14.) Differentiate between: a.) TRANSLATE and REPLACE.
- Translate is used to substitute character by character.
- Replace is used to substitute a single character with a word.
b.) What is Merge Statement used for?
- Merge statement is used to select rows from one or more data source to updating and insert into a table or a view.
15.) What are the various Oracle Database objects?
Various database objects are as follows:
- Tables – This is a set of elements organized in vertical and horizontal fashion.
- Tablespaces – This is a logical storage unit in Oracle.
- Views – It is virtual table derived from one or more tables.
- Indexes – This is a performance tuning method to process the records.
- Synonyms – This is a name for tables.
- Sequences.
16. What is the purpose of Save Points in Oracle database?
- Save Points are used to divide a transaction into smaller phases.
- It enables rolling back part of a transaction.
- Maximum 5 save points are allowed in Oracle Database.
- Whenever an error is encountered, it is possible to rollback from the point where the SAVEPOINT has been saved.
17. a.) What is an ALERT?
- It a window which appears on the screen overlaying a portion of the current display.
b.) Differentiate between post-database commit and post-form commit.
- The post-database commit trigger is fired after oracle forms issue the commit to finalized transactions.
- The post-form commit is fired during the post and commit transactions process, after the database commit occurs.
18. Differentiate between pre-select and pre-query.
- Once oracle forms construct the select statement to be issued Pre-select is fired during the execute query and count query processing. All this happens before the statement is actually issued.
- The pre-query trigger is fired just before oracle forms issue the select statement to the database.
19. What is hot backup and logical backup?
Hot backup
- Backing up the archive log files when database is open is called Hot backup.
- To do this, the ARCHIVELOG mode is enabled.
- Following files are backed up - All data files, Archive log, redo log files and control files.
Logical backup
- Logical back ip is reading a set of database records and writing them into a file.
- An Export utility is required to take the backup while an Import utility is required to recover from the backup.
20. What do you mean by Redo Log file mirroring ?
- The process of having a copy of redo log files is called mirroring.
- It is done by creating group of log files together. This ensures that LGWR automatically writes them to all the members of the current on-line redo log group.
- In case a group fails, the database automatically switches over to the next group. It diminishes the performance
1. Difference between varchar and varchar2 data types?
Varchar can store upto 2000 bytes and varchar2 can store upto 4000 bytes. Varchar will occupy space for NULL values and Varchar2 will not occupy any space. Both are differed with respect to space.
2. In which language Oracle has been developed?
Oracle has been developed using C Language.
3. What is RAW datatype?
RAW datatype is used to store values in binary data format. The maximum size for a raw in a table in 32767 bytes.
4. What is the use of NVL function?
The NVL function is used to replace NULL values with another or given value. Example is –
NVL(Value, replace value)
5. Whether any commands are used for Months calculation? If so, What are they?
In Oracle, months_between function is used to find number of months between the given dates. Example is –
Months_between(Date 1, Date 2)
6. What are nested tables?
Nested table is a data type in Oracle which is used to support columns containing multi valued attributes. It also hold entire sub table.
7. What is COALESCE function?
COALESCE function is used to return the value which is set to be not null in the list. If all values in the list are null, then the coalesce function will return NULL.
Coalesce(value1, value2,value3,…)
8. What is BLOB datatype?
A BLOB data type is a varying length binary string which is used to store two gigabytes memory. Length should be specified in Bytes for BLOB.
9. How do we represent comments in Oracle?
Comments in Oracle can be represented in two ways –
Two dashes(–) before beginning of the line – Single statement
/*—— */ is used to represent it as comments for block of statement
10. What is DML?
Data Manipulation Language (DML) is used to access and manipulate data in the existing objects. DML statements are insert, select, update and delete and it won’t implicitly commit the current transaction.
11. What is the difference between TRANSLATE and REPLACE?
Translate is used for character by character substitution and Replace is used substitute a single character with a word.
12. How do we display rows from the table without duplicates?
Duplicate rows can be removed by using the keyword DISTINCT in the select statement.
13. What is the usage of Merge Statement?
Merge statement is used to select rows from one or more data source for updating and insertion into a table or a view. It is used to combine multiple operations.
14. What is NULL value in oracle?
NULL value represents missing or unknown data. This is used as a place holder or represented it in as default entry to indicate that there is no actual data present.
15. What is USING Clause and give example?
The USING clause is used to specify with the column to test for equality when two tables are joined.
[sql]Select * from employee join salary using employee ID[/sql]
Employee tables join with the Salary tables with the Employee ID.
16. What is key preserved table?
A table is set to be key preserved table if every key of the table can also be the key of the result of the join. It guarantees to return only one copy of each row from the base table.
17. What is WITH CHECK OPTION?
The WITH CHECK option clause specifies check level to be done in DML statements. It is used to prevent changes to a view that would produce results that are not included in the sub query.
18. What is the use of Aggregate functions in Oracle?
Aggregate function is a function where values of multiple rows or records are joined together to get a single value output. Common aggregate functions are –
Average
Count
Sum
19. What do you mean by GROUP BY Clause?
A GROUP BY clause can be used in select statement where it will collect data across multiple records and group the results by one or more columns.
20. What is a sub query and what are the different types of subqueries?
Sub Query is also called as Nested Query or Inner Query which is used to get data from multiple tables. A sub query is added in the where clause of the main query.
There are two different types of subqueries:
Correlated sub query
A Correlated sub query cannot be as independent query but can reference column in a table listed in the from list of the outer query.
Non-Correlated subquery
This can be evaluated as if it were an independent query. Results of the sub query are submitted to the main query or parent query.
21. What is cross join?
Cross join is defined as the Cartesian product of records from the tables present in the join. Cross join will produce result which combines each row from the first table with the each row from the second table.
22. What are temporal data types in Oracle?
Oracle provides following temporal data types:
Date Data Type – Different formats of Dates
TimeStamp Data Type – Different formats of Time Stamp
Interval Data Type – Interval between dates and time
23. How do we create privileges in Oracle?
A privilege is nothing but right to execute an SQL query or to access another user object. Privilege can be given as system privilege or user privilege.
[sql]GRANT user1 TO user2 WITH MANAGER OPTION;[/sql]
24. What is VArray?
VArray is an oracle data type used to have columns containing multivalued attributes and it can hold bounded array of values.
25. How do we get field details of a table?
Describe <Table_Name> is used to get the field details of a specified table.
26. What is the difference between rename and alias?
Rename is a permanent name given to a table or a column whereas Alias is a temporary name given to a table or column. Rename is nothing but replacement of name and Alias is an alternate name of the table or column.
27. What is a View?
View is a logical table which based on one or more tables or views. The tables upon which the view is based are called Base Tables and it doesn’t contain data.
28. What is a cursor variable?
A cursor variable is associated with different statements which can hold different values at run time. A cursor variable is a kind of reference type.
29. What are cursor attributes?
Each cursor in Oracle has set of attributes which enables an application program to test the state of the cursor. The attributes can be used to check whether cursor is opened or closed, found or not found and also find row count.
30. What are SET operators?
SET operators are used with two or more queries and those operators are Union, Union All, Intersect and Minus.
31. How can we delete duplicate rows in a table?
Duplicate rows in the table can be deleted by using ROWID.
32. What are the attributes of Cursor?
Attributes of Cursor are
%FOUND
Returns NULL if cursor is open and fetch has not been executed
Returns TRUE if the fetch of cursor is executed successfully.
Returns False if no rows are returned.
%NOT FOUND
Returns NULL if cursor is open and fetch has not been executed
Returns False if fetch has been executed
Returns True if no row was returned
%ISOPEN
Returns true if the cursor is open
Returns false if the cursor is closed
%ROWCOUNT
Returns the number of rows fetched. It has to be iterated through entire cursor to give exact real count.
33. Can we store pictures in the database and if so, how it can be done?
Yes, we can store pictures in the database by Long Raw Data type. This datatype is used to store binary data for 2 gigabytes of length. But the table can have only on Long Raw data type.
34. What is an integrity constraint?
An integrity constraint is a declaration defined a business rule for a table column. Integrity constraints are used to ensure accuracy and consistency of data in a database. There are types – Domain Integrity, Referential Integrity and Domain Integrity.
35. What is an ALERT?
An alert is a window which appears in the center of the screen overlaying a portion of the current display.
36. What is hash cluster?
Hash Cluster is a technique used to store the table for faster retrieval. Apply hash value on the table to retrieve the rows from the table.
37. What are the various constraints used in Oracle?
Following are constraints used:
NULL – It is to indicate that particular column can contain NULL values
NOT NULL – It is to indicate that particular column cannot contain NULL values
CHECK – Validate that values in the given column to meet the specific criteria
DEFAULT – It is to indicate the value is assigned to default value
38. What is difference between SUBSTR and INSTR?
SUBSTR returns specific portion of a string and INSTR provides character position in which a pattern is found in a string.
SUBSTR returns string whereas INSTR returns numeric.
39. What is the parameter mode that can be passed to a procedure?
IN, OUT and INOUT are the modes of parameters that can be passed to a procedure.
40. What are the different Oracle Database objects?
There are different data objects in Oracle –
Tables – set of elements organized in vertical and horizontal
Views – Virtual table derived from one or more tables
Indexes – Performance tuning method for processing the records
Synonyms – Alias name for tables
Sequences – Multiple users generate unique numbers
Tablespaces – Logical storage unit in Oracle
41. What are the differences between LOV and List Item?
LOV is property whereas list items are considered as single item. List of items is set to be a collection of list of items. A list item can have only one column, LOV can have one or more columns.
42. What are privileges and Grants?
Privileges are the rights to execute SQL statements – means Right to connect and connect. Grants are given to the object so that objects can be accessed accordingly. Grants can be provided by the owner or creator of an object.
43. What is the difference between $ORACLE_BASE and $ORACLE_HOME?
Oracle base is the main or root directory of an oracle whereas ORACLE_HOME is located beneath base folder in which all oracle products reside.
44. What is the fastest query method to fetch data from the table?
Row can be fetched from table by using ROWID. Using ROW ID is the fastest query method to fetch data from the table.
45. What is the maximum number of triggers that can be applied to a single table?
12 is the maximum number of triggers that can be applied to a single table.
46. How to display row numbers with the records?
Display row numbers with the records numbers –
Select rownum, <fieldnames> from table;
1
Select rownum, <fieldnames> from table;
This query will display row numbers and the field values from the given table.
47. How can we view last record added to a table?
Last record can be added to a table and this can be done by –
Select * from (select * from employees order by rownum desc) where rownum<2;
1
Select * from (select * from employees order by rownum desc) where rownum<2;
48. What is the data type of DUAL table?
The DUAL table is a one-column table present in oracle database. The table has a single VARCHAR2(1) column called DUMMY which has a value of ‘X’.
49. What is difference between Cartesian Join and Cross Join?
There are no differences between the join. Cartesian and Cross joins are same. Cross join gives cartesian product of two tables – Rows from first table is multiplied with another table which is called cartesian product.
Cross join without where clause gives Cartesian product.
50. How to display employee records who gets more salary than the average salary in the department?
This can be done by this query –
Select * from employee where salary>(select avg(salary) from dept, employee where dept.deptno = employee.deptno;
1
Select * from employee where salary>(select avg(salary) from dept, employee where dept.deptno = employee.deptno;
1. What is DBMS?
A Database Management System (DBMS) is a program that controls creation, maintenance and use of a database. DBMS can be termed as File Manager that manages data in a database rather than saving it in file systems.
2. What is RDBMS?
RDBMS stands for Relational Database Management System. RDBMS store the data into the collection of tables, which is related by common fields between the columns of the table. It also provides relational operators to manipulate the data stored into the tables.
Example: SQL Server.
3. What is SQL?
SQL stands for Structured Query Language , and it is used to communicate with the Database. This is a standard language used to perform tasks such as retrieval, updation, insertion and deletion of data from a database.
Standard SQL Commands are Select.
4. What is a Database?
Database is nothing but an organized form of data for easy access, storing, retrieval and managing of data. This is also known as structured form of data which can be accessed in many ways.
Example: School Management Database, Bank Management Database.
5. What are tables and Fields?
A table is a set of data that are organized in a model with Columns and Rows. Columns can be categorized as vertical, and Rows are horizontal. A table has specified number of column called fields but can have any number of rows which is called record.
Example:.
Table: Employee.
Field: Emp ID, Emp Name, Date of Birth.
Data: 201456, David, 11/15/1960.
6. What is a primary key?
A primary key is a combination of fields which uniquely specify a row. This is a special kind of unique key, and it has implicit NOT NULL constraint. It means, Primary key values cannot be NULL.
7. What is a unique key?
A Unique key constraint uniquely identified each record in the database. This provides uniqueness for the column or set of columns.
A Primary key constraint has automatic unique constraint defined on it. But not, in the case of Unique Key.
There can be many unique constraint defined per table, but only one Primary key constraint defined per table.
8. What is a foreign key?
A foreign key is one table which can be related to the primary key of another table. Relationship needs to be created between two tables by referencing foreign key with the primary key of another table.
9. What is a join?
This is a keyword used to query data from more tables based on the relationship between the fields of the tables. Keys play a major role when JOINs are used.
10. What are the types of join and explain each?
There are various types of join which can be used to retrieve data and it depends on the relationship between tables.
Inner join.
Inner join return rows when there is at least one match of rows between the tables.
Right Join.
Right join return rows which are common between the tables and all rows of Right hand side table. Simply, it returns all the rows from the right hand side table even though there are no matches in the left hand side table.
Left Join.
Left join return rows which are common between the tables and all rows of Left hand side table. Simply, it returns all the rows from Left hand side table even though there are no matches in the Right hand side table.
Full Join.
Full join return rows when there are matching rows in any one of the tables. This means, it returns all the rows from the left hand side table and all the rows from the right hand side table.
11. What is normalization?
Normalization is the process of minimizing redundancy and dependency by organizing fields and table of a database. The main aim of Normalization is to add, delete or modify field that can be made in a single table.
12. What is Denormalization.
DeNormalization is a technique used to access the data from higher to lower normal forms of database. It is also process of introducing redundancy into a table by incorporating data from the related tables.
13. What are all the different normalizations?
The normal forms can be divided into 5 forms, and they are explained below -.
First Normal Form (1NF):.
This should remove all the duplicate columns from the table. Creation of tables for the related data and identification of unique columns.
Second Normal Form (2NF):.
Meeting all requirements of the first normal form. Placing the subsets of data in separate tables and Creation of relationships between the tables using primary keys.
Third Normal Form (3NF):.
This should meet all requirements of 2NF. Removing the columns which are not dependent on primary key constraints.
Fourth Normal Form (3NF):.
Meeting all the requirements of third normal form and it should not have multi- valued dependencies.
14. What is a View?
A view is a virtual table which consists of a subset of data contained in a table. Views are not virtually present, and it takes less space to store. View can have data of one or more tables combined, and it is depending on the relationship.
15. What is an Index?
An index is performance tuning method of allowing faster retrieval of records from the table. An index creates an entry for each value and it will be faster to retrieve data.
16. What are all the different types of indexes?
There are three types of indexes -.
Unique Index.
This indexing does not allow the field to have duplicate values if the column is unique indexed. Unique index can be applied automatically when primary key is defined.
Clustered Index.
This type of index reorders the physical order of the table and search based on the key values. Each table can have only one clustered index.
NonClustered Index.
NonClustered Index does not alter the physical order of the table and maintains logical order of data. Each table can have 999 nonclustered indexes.
17. What is a Cursor?
A database Cursor is a control which enables traversal over the rows or records in the table. This can be viewed as a pointer to one row in a set of rows. Cursor is very much useful for traversing such as retrieval, addition and removal of database records.
18. What is a relationship and what are they?
Database Relationship is defined as the connection between the tables in a database. There are various data basing relationships, and they are as follows:.
One to One Relationship.
One to Many Relationship.
Many to One Relationship.
Self-Referencing Relationship.
19. What is a query?
A DB query is a code written in order to get the information back from the database. Query can be designed in such a way that it matched with our expectation of the result set. Simply, a question to the Database.
20. What is subquery?
A subquery is a query within another query. The outer query is called as main query, and inner query is called subquery. SubQuery is always executed first, and the result of subquery is passed on to the main query.
21. What are the types of subquery?
There are two types of subquery – Correlated and Non-Correlated.
A correlated subquery cannot be considered as independent query, but it can refer the column in a table listed in the FROM the list of the main query.
A Non-Correlated sub query can be considered as independent query and the output of subquery are substituted in the main query.
22. What is a stored procedure?
Stored Procedure is a function consists of many SQL statement to access the database system. Several SQL statements are consolidated into a stored procedure and execute them whenever and wherever required.
23. What is a trigger?
A DB trigger is a code or programs that automatically execute with response to some event on a table or view in a database. Mainly, trigger helps to maintain the integrity of the database.
Example: When a new student is added to the student database, new records should be created in the related tables like Exam, Score and Attendance tables.
24. What is the difference between DELETE and TRUNCATE commands?
DELETE command is used to remove rows from the table, and WHERE clause can be used for conditional set of parameters. Commit and Rollback can be performed after delete statement.
TRUNCATE removes all rows from the table. Truncate operation cannot be rolled back.
25. What are local and global variables and their differences?
Local variables are the variables which can be used or exist inside the function. They are not known to the other functions and those variables cannot be referred or used. Variables can be created whenever that function is called.
Global variables are the variables which can be used or exist throughout the program. Same variable declared in global cannot be used in functions. Global variables cannot be created whenever that function is called.
26. What is a constraint?
Constraint can be used to specify the limit on the data type of table. Constraint can be specified while creating or altering the table statement. Sample of constraint are.
NOT NULL.
CHECK.
DEFAULT.
UNIQUE.
PRIMARY KEY.
FOREIGN KEY.
27. What is data Integrity?
Data Integrity defines the accuracy and consistency of data stored in a database. It can also define integrity constraints to enforce business rules on the data when it is entered into the application or database.
28. What is Auto Increment?
Auto increment keyword allows the user to create a unique number to be generated when a new record is inserted into the table. AUTO INCREMENT keyword can be used in Oracle and IDENTITY keyword can be used in SQL SERVER.
Mostly this keyword can be used whenever PRIMARY KEY is used.
29. What is the difference between Cluster and Non-Cluster Index?
Clustered index is used for easy retrieval of data from the database by altering the way that the records are stored. Database sorts out rows by the column which is set to be clustered index.
A nonclustered index does not alter the way it was stored but creates a complete separate object within the table. It point back to the original table rows after searching.
30. What is Datawarehouse?
Datawarehouse is a central repository of data from multiple sources of information. Those data are consolidated, transformed and made available for the mining and online processing. Warehouse data have a subset of data called Data Marts.
31. What is Self-Join?
Self-join is set to be query used to compare to itself. This is used to compare values in a column with other values in the same column in the same table. ALIAS ES can be used for the same table comparison.
32. What is Cross-Join?
Cross join defines as Cartesian product where number of rows in the first table multiplied by number of rows in the second table. If suppose, WHERE clause is used in cross join then the query will work like an INNER JOIN.
33. What is user defined functions?
User defined functions are the functions written to use that logic whenever required. It is not necessary to write the same logic several times. Instead, function can be called or executed whenever needed.
34. What are all types of user defined functions?
Three types of user defined functions are.
Scalar Functions.
Inline Table valued functions.
Multi statement valued functions.
Scalar returns unit, variant defined the return clause. Other two types return table as a return.
35. What is collation?
Collation is defined as set of rules that determine how character data can be sorted and compared. This can be used to compare A and, other language characters and also depends on the width of the characters.
ASCII value can be used to compare these character data.
36. What are all different types of collation sensitivity?
Following are different types of collation sensitivity -.
Case Sensitivity – A and a and B and b.
Accent Sensitivity.
Kana Sensitivity – Japanese Kana characters.
Width Sensitivity – Single byte character and double byte character.
37. Advantages and Disadvantages of Stored Procedure?
Stored procedure can be used as a modular programming – means create once, store and call for several times whenever required. This supports faster execution instead of executing multiple queries. This reduces network traffic and provides better security to the data.
Disadvantage is that it can be executed only in the Database and utilizes more memory in the database server.
38. What is Online Transaction Processing (OLTP)?
Online Transaction Processing or OLTP manages transaction based applications which can be used for data entry and easy retrieval processing of data. This processing makes like easier on simplicity and efficiency. It is faster, more accurate results and expenses with respect to OTLP.
Example – Bank Transactions on a daily basis.
39. What is CLAUSE?
SQL clause is defined to limit the result set by providing condition to the query. This usually filters some rows from the whole set of records.
Example – Query that has WHERE condition
Query that has HAVING condition.
40. What is recursive stored procedure?
A stored procedure which calls by itself until it reaches some boundary condition. This recursive function or procedure helps programmers to use the same set of code any number of times.
41. What is Union, minus and Interact commands?
UNION operator is used to combine the results of two tables, and it eliminates duplicate rows from the tables.
MINUS operator is used to return rows from the first query but not from the second query. Matching records of first and second query and other rows from the first query will be displayed as a result set.
INTERSECT operator is used to return rows returned by both the queries.
42. What is an ALIAS command?
ALIAS name can be given to a table or column. This alias name can be referred in WHERE clause to identify the table or column.
Example-.
Select st.StudentID, Ex.Result from student st, Exam as Ex where st.studentID = Ex. StudentID
1
Select st.StudentID, Ex.Result from student st, Exam as Ex where st.studentID = Ex. StudentID
Here, st refers to alias name for student table and Ex refers to alias name for exam table.
43. What is the difference between TRUNCATE and DROP statements?
TRUNCATE removes all the rows from the table, and it cannot be rolled back. DROP command removes a table from the database and operation cannot be rolled back.
44. What are aggregate and scalar functions?
Aggregate functions are used to evaluate mathematical calculation and return single values. This can be calculated from the columns in a table. Scalar functions return a single value based on the input value.
Example -.
Aggregate – max(), count – Calculated with respect to numeric.
Scalar – UCASE(), NOW() – Calculated with respect to strings.
45. How can you create an empty table from an existing table?
Example will be -.
Select * into studentcopy from student where 1=2
1
Select * into studentcopy from student where 1=2
Here, we are copying student table to another table with the same structure with no rows copied.
46. How to fetch common records from two tables?
Common records result set can be achieved by -.
Select studentID from student. <strong>INTERSECT </strong> Select StudentID from Exam
1
Select studentID from student. <strong>INTERSECT </strong> Select StudentID from Exam
47. How to fetch alternate records from a table?
Records can be fetched for both Odd and Even row numbers -.
To display even numbers-.
Select studentId from (Select rowno, studentId from student) where mod(rowno,2)=0
1
Select studentId from (Select rowno, studentId from student) where mod(rowno,2)=0
To display odd numbers-.
Select studentId from (Select rowno, studentId from student) where mod(rowno,2)=1
1
Select studentId from (Select rowno, studentId from student) where mod(rowno,2)=1
from (Select rowno, studentId from student) where mod(rowno,2)=1.[/sql]
48. How to select unique records from a table?
Select unique records from a table by using DISTINCT keyword.
Select DISTINCT StudentID, StudentName from Student.
1
Select DISTINCT StudentID, StudentName from Student.
49. What is the command used to fetch first 5 characters of the string?
There are many ways to fetch first 5 characters of the string -.
Select SUBSTRING(StudentName,1,5) as studentname from student
1
Select SUBSTRING(StudentName,1,5) as studentname from student
Select RIGHT(Studentname,5) as studentname from student
1
Select RIGHT(Studentname,5) as studentname from student
50. Which operator is used in query for pattern matching?
LIKE operator is used for pattern matching, and it can be used as -.
% – Matches zero or more characters.
_(Underscore) – Matching exactly one character.
Example -.
Select * from Student where studentname like ‘a%’
1
Select * from Student where studentname like ‘a%’
Select * from Student where studentname like ‘ami_’
1
Select * from Student where studentname like ‘ami_’
