500 Oracle interview questions and answers

Ask a question
          

Oracle interview questions and answers


Part 2 | Part 3 | Part 4 | Part 5 | Part 6 | Part 7 | Part 8 | Part 9  

Oracle interview questions - April 17, 2013 at 01:16 PM by Kshipra Singh 

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. 

Download Oracle interview questions and answers pdf

Explain the difference between trigger and stored procedure.

Latest answer: Trigger in act which is performed automatically before or after a event occur. Stored procedure is a set of functionality which is executed when it is explicitly invoked.................
Read answer

Explain Row level and statement level trigger.

Latest answer: Row Level Trigger is fired each time row is affected by Insert, Update or Delete command. If statement doesn’t affect any row, no trigger action happens................
Read answer

Oracle interview questions - Jan 04, 2011 at 05:16 PM by Rahul

Write a PL/SQL program for a trigger.

Latest answer: PL/SQL program for tracking operation on a emp table
Create or Replace Trigger EmpTracking
Before Insert or Delete or Update on Emp..................
Read answer

Advantage of a stored procedure over a database trigger.

Latest answer: Stored procedure is a set of pre-compiled SQL statements, executed when it is called in the program. Triggers are similar to stored procedure except it is executed automatically when any operations are occurred on the table..................
Read answer

What are cascading triggers?

Latest answer: A Trigger that contains statements which cause invoking of other Triggers are known as cascading triggers. Here’s the order of execution of statements in case of cascading triggers:................
Read answer

Oracle interview questions and answers for freshers and experienced

Relational database

What is Rational database? Flat database vs. Rational database
Features of Rational database
Dr. Codd defined thirteen standards which must be met before a database can be considered to be a relational database. What was that?
Explain how data is arranged in a rational database with an example..............

Data manipulation language

Overview of Data Manipulation Language
Explain how to retrieve information using SELECT.
Add information using INSERT
Change information using UPDATE
Remove information using DELETE
Use Subqueries as your search condition............

Oracle displaying & aggregating data

What are aggregate functions in SQL? What are those functions?
Using ROLLUP to aggregate data in SQL
Define, syntax and an example with output for:SUM function, AVG (average) function, COUNT function, Max and Min function..............

SQL number functions

Define, syntax and an example with output for: ABS(number), CEIL(number), FLOOR(number), MOD(number, divisor), POWER(number, power), SIGN(number), ROUND(number, precision), SQRT(number), TRUNC(number, precision).....................

SQL single-row character

CONCAT(string1, string2), INITCAP(string), LENGTH(string), LPAD(string, #,padding_char), RPAD(string, #,padding_char), LTRIM(string,searchString), RTRIM(string,searchString), REPLACE(string,searchString,replacement), SUBSTR(string,start,length), UPPER(string), LOWER(string)...........

SQL single-row date functions

SYSDATE, ADD_MONTHS(Date, months_to_add), LAST_DAY(Date), MONTHS_BETWEEN(Date1, Date2)...........

Subqueries

Explain subqueries with an example - A subquery is a query within a query. The inner query is processed first. They are most commonly used to return data from multiple tables when the exact value is not known...............

SQL*Plus

What is SQL*Plus? Explain its features
Explain how to use the SQL*Plus utility.
What are the basic SQL*Plus commands?
What is AFIEDT.BUF?
Difference between ? and HELP.
Explain how to enable the SQL*Plus HELP facility.
Difference between @ and @@.
Difference between & and &&.
Difference between ! and HOST
How can one prevent SQL*Plus connection warning messages?............

Oracle database tables

What is a Database Table?
How many types of tables supported by Oracle? Explain them.
Can you explain how to create a aew table in your schema?
Explain with an example how to create a new table by selecting rows from another table.
Depict a sample script for renaming an existing table.
Sample script to drop an existing table.
How to Add a new column to an existing table?...........

Oracle constraints

Oracle constraints - overview. What are the types of constraints avaialable in oracle
Explain Oracle "Check" constraint.
Explain Not Null constraint.
Explain primary key constraint.
Explain oracle foreign key constraint.
Explain unique Constraint.
Explain with an example how to alter oracle table constraint..........

Restricting and sorting data in oracle

Explain how to limit the rows that are retrieved by a query.
Explain how to sort the rows that are retrieved by a query.
What are the comparison operators in oracle. List them with description...............

Working with multiple tables joins

What are joins? Explain its characteristic features
Types of joins. Define, write syntax and an example for each type............

Working with multiple tables

Define subquery. Explain with an example
What is correlated query? Explain its uses.
What are Set Operators? Explain union and union all with an example for each...........

PL/SQL

Define PL/SQL. Explain its purpose
Advantages of PL/SQL
Main Features of PL/SQL
PL/SQL Architecture
Basic Structure of PL/SQL
Variables and Types............

PL/SQL control structures

Conditional control: IF and CASE Statements.
Iterative Control: LOOP and EXIT Statements.
GOTO statement takes the control to the labeled statement...........

PL/SQL collections

What is a Collection?
Nested Tables vs. Associative Arrays
Nested Tables vs. Varrays
Explain the methods exist for collections. - EXISTS , COUNT , LIMIT , FIRST and LAST , PRIOR and NEXT , EXTEND, TRIM , DELETE..............

PL/SQL record data type

What is a PL/SQL Record data type?
Define and declare Records
Different Types of Records - Table-based, Cursor-based, Programmer-defined
Benefits of using Records
Guidelines for using Records...........

PL/SQL cursors

What are Cursors?
Define cursor attributes: %FOUND, %NOTFOUND, %ROWCOUNT, and %ISOPEN
What are cursor variables? Explain with an example
Significance of SELECT FOR UPDATE clause. Write syntax
Significance of WHERE CURRENT OF clause. Write syntax.........

PL/SQL error handling

Overview of PL/SQL Error Handling
Advantages of PL/SQL Exceptions
Explain some of the commonly used Predefined PL/SQL Exceptions.
What is user-defined exception? Explain it with an example.
Explain how PL/SQL exceptions are raised.........

PL/SQL subprograms

What are PL/SQL Subprograms?
Advantages of PL/SQL Subprograms.
PL/SQL has two types of subprograms called procedures and functions. Explain them.
How does subprogram pass information? What are actual and formal subprogram parameters? Explain with an example
Define three parameter modes, IN (the default), OUT, and IN OUT that can be used with any subprogram............

PL/SQL packages

What is a PL/SQL package?
Advantages of PL/SQL Packages
What are the types and layers of Packages? Explain them
PL/SQL Packages features
Guidelines for writing packages........

PL/SQL triggers

What is a Trigger? Syntax of Triggers
Types of PL/SQL Triggers
How to obtain information about any trigger?
What is CYCLIC CASCADING in a TRIGGER?............

PL/SQL sequences

Define PL/SQL sequences and write syntax for a sequence
How do we set the LASTVALUE value in an Oracle Sequence?
What does cache and no cache options mean while creating a sequence?.........

Oracle/PLSQL indexes

What is an Index? Explain how to create an Index.
What is Function-Based Index? Create a Function-Based Index........

Users in oracle

Types of users - a local user, an external user, or a global user
Explain how to create users through Sql Plus.
How to delete an Oracle applications user?...........

Oracle database performance tuning

Explain the purpose of tuning oracle database.
Could you explain the areas where tuning of database is required?
What are the tools provided by oracle to assist performace tuning? Explain them in brief
What is cost based optimization? When is it triggered?
Explain the factor that can cause the execution plan of a query to change
What is index monitoring feature in oracle? Explain how to use it for index optimization...........

Oracle advanced replication

What is replication? Explain its purpose.
Oracle Server supports two different forms of replication: basic and advanced replication. Explain difference between these.
What is the difference between a snapshot and a materialized view?
Describe how to implement basic snapshot replication.
What object types can and cannot be replicated in oracle?
Describe how to monitor replication.............

Oracle forms

What is oracle forms and what is it used for?
Explain how to iterate through items and records in a specified block in oracle form.
How to bypass the oracle login screen.........

Oracle HTTP Server (Apache)

What is the Oracle HTTP Server? How does it work?
Explain how to start and stop the Oracle HTTP Server.
Explain how to publish static HTML pages on the Oracle HTTP Server.
Explain how to execute standard CGI-BIN programs from the Oracle HTTP Server.
Explain how to password protect certain directories...........

Oracle export and import utilities

What is import/export? Explain its usage.
Explain how to use the import/export utilities.
Can we export a subset of a table? Explain how
Can we monitor how fast a table is imported? How?
Explain how to import tables to a different tablespace?
Explain how to improve Import/Export performance?
Describe the common Import/Export problems...........

Oracle ODBC connectivity

What is ODBC? Explain its purposes
Difference between ODBC and Net8.
What do I need to get ODBC to work?
Explain how to create a Data Source.
Explain how to trace ODBC calls.
What is ODBC SQLPASSTHROUGH option?
How to attach an Oracle table in MS-Access?
Explain how to get Oracle data into MS-Excel...........

Personal oracle lite (POL)

How compatible is Oracle Lite with the standard oracle server?
What is personal oracle lite?
Explain how to connect to a POL database
Explain how to write Java Stored Procedures
Explain how to replicate data using Oracle Lite.............

Oracle database backup and recovery

Why and when should I backup my database?
What strategies are available for backing-up an Oracle database?
Difference between online and offline backups.
Difference between restoring and recovering
Explain how to backup a database using the export utility
Explain how to put a database into ARCHIVELOG mode
Explain how to do off-line database backups..........

Oracle database

Explain how to create a new database
Explain how to rename a database
Explain how to resize tablespaces and data files
Explain how to find the overall database size
Explain how to find used/free space in a TEMPORARY tablespace
What is ORADEBUG? Explain its uses?
Explain how to dump internal database structures...........

Oracle parallel query (OPQ)

What is Oracle Parallel Query?
Difference between Parallel Query and Parallel Server
Explain how to invoke Parallel Query
Explain how to disable Parallel Query
Can single-processor systems benefit from Parallel Query? Explain..........

Oracle web cache

What is the Oracle Web Cache?
Difference between the Database and Web Cache
Explain how to install and configure the Web Cache
Explain how to stop and start the Web Cache..............

Oracle spatial

What is Oracle Spatial?
Difference between Oracle Spatial and Locator
Difference between the Relational and Object Spatial model
Explain how to install Oracle Spatial...........

Oracle real application clusters

What is RAC and how is it different from non RAC databases?
Can any application be deployed on RAC? Explain
Explain how to convert a single instance database to RAC
How do we stop and start RAC instances?
Can we test if a database is running in RAC mode? How?
How can we keep track of active instances?.........

Peoplesoft on oracle

What is a PeopleSoft database?
How does the PeopleSoft database interact with the Oracle database?
What is the order of installing PeopleSoft on Oracle?
How can the client workstation connect to a PeopleSoft database?
What is the sizing of a generic PeopleSoft HRMS database?..........

What is a JOIN? Explain types of JOIN in oracle.

Latest answer: A JOIN is used to match/equate different fields from 2 or more tables using primary/foreign keys. Output is based on type of Join and what is tobe queries......................
Read answer

What is a join, explain the types of joins?

Latest answer: A join is a query that extracts data from two or more tables, views or snapshots...........
Read answer

Explain the types of joins.

Latest answer: Types of joins are: Equijoins, Non-equijoins, self join, outer join. A Join is used to create query using two or more tables and views..........
Read answer

What is object data type in oracle?

Latest answer: Object data type is created using object type in oracle. The object data type can only be created using SQL data type such as Number, Varchar 2, Date etc.................
Read answer

What is composite data type?

Latest answer: Collections are usually referred to as Composite Data types. Composite type is one that has components in it. A variable of a composite type contains one or more scalar types................
Read answer

Differences between CHAR and NCHAR in Oracle.

Latest answer: CHAR and NCHAR are character data types which and have a fixed length. However, CHAR has a specified size in bytes by default and NCHAR has a size specified in characters by default................
Read answer

Differences between CHAR and VARCHAR2 in Oracle.

Latest answer: CHAR values have a fixed length. They are padded with space characters to match the specified length...........
Read answer

Differences between DATE and TIMESTAMP in Oracle

Latest answer: TIMESTAMP and DATE vary in formats as follows: DATE stores values as century, year, month, date, hour, minute, and second............
Read answer

Define CLOB and NCLOB datatypes.

Latest answer: Both CLOB and NCLOB are used to store huge character data in the database, CLOBs store single-byte character set data............
Read answer

What is the BFILE datatypes?

Latest answer: The BFILE datatype is used to store unstructured binary data outside the database. The column of BFILE type stores file locator that points the OS file which actually stores data...........
Read answer

What is Varrays?

Latest answer: Varrays, variable length array is quite similar to array of C++ or Java. It has fixed upper-bound size that has to be specified while it is declared..........
Read answer

What are LOB datatypes?

Latest answer: The LOB datatypes such as BLOB, CLOB, NCLOB and BFile can store large blocks of unstructured data such as graphics, image, video clips etc............
Read answer

What is a cursor? What are its types?

Latest answer: Oracle engine uses private working area to process queries. This work area is called as cursor. The data that is stored in the cursor is called as Active Data Set.............
Read answer

Explain the attributes of implicit cursor

Latest answer: There are four attributes of implicit cursor in oracle: SQL%IsOPEN - Implicit cursor always returns FALSE as it gets closed automatically...........
Read answer

Explain the attributes of explicit cursor.

Latest answer: There are four attributes of explicit cursor in oracle. %IsOPEN - This evaluates TRUE when cursor is open else FALSE..........
Read answer

What is the ref cursor in Oracle?

Latest answer: Cursor is a reference type in oracle. We can allocate different storage locations to the cursor when the program runs............
Read answer

What are the drawbacks of a cursor?

Latest answer: Implicit cursors are less efficient than explicit cursors, Implicit cursors are more vulnerable to data errors..................
Read answer

What is a cursor variable?

Latest answer: A cursor variable is capable to get associated with different SELECT statements at run time. It is a reference type which is quite similar to pointer in C. In order to use cursor variable...........
Read answer 

Business object interview questions

Latest answer: Differentiate between Data Mining and Data warehousing. What is Data purging? What are CUBES? What are OLAP and OLTP? What are the different problems that “Data mining” can solve? What are different stages of “Data mining”?..................... .
Read answer

OLAP interview questions

Latest answer: Explain the concepts and capabilities of OLAP. Explain the functionality of OLAP. What are MOLAP and ROLAP? Explain the role of bitmap indexes to solve aggregation problems. Explain the encoding technique used in bitmaps indexes. What is Binning? What is candidate check?..................
Read answer

Next>>     Part 1 | Part 2 | Part 3 | Part 4 | part 5 | part 6 | part 7 | part 8 | part 9    

Write your comment - Share Knowledge and Experience

Discussion Board
pls provide a sql get the below result.

ID_NO, MARKS, YR
1 30 2010
1 40 2013
1 50 2014
2 30 2009
2 40 2010

is the input table.

pls let me the know the sql to get the following o/p

id_no year previous yr
1 2014 2013
1 2013 2010
1 2010 null
2 2010 2009
2 2009 null

i should not get more rows than the no of rows input table . year 2014 has two previous years one 2013 and 2010 but i should get only 2013 in o/p

the following sql gives below o/p but its not giving expected result

select a.id_no,a.yr,b.yr from student a left outer join student b on a.id_no = b.id_no
and b.yr < a.yr order by a.id_no,a.yr desc

ID_NO YR YR
---------- ---------- ----------
1 2014 2010
1 2014 2013
1 2013 2010
1 2010
2 2010 2009
2 2009



prasanth 10-25-2014 01:47 PM

Oracle Interview questions and answers

What are triggers? What are the events a trigger includes?
The procedures are called as triggers in oracle and it run implicitly when an insert, update or delete statement is being performed on a table. They are same as store procedures but it can include SQL, PL/SQL or Java statements. Triggers are implicit to the application.
It includes the following events such as:
- DML statements for modification of data like INSERT, UPDATE or DELETE
- DDL statements
- System related messages like startup, shutdown, and error messages
- User events like logon or logoff


Rohit Sharma 12-7-2011 07:12 AM

Oracle interview questions and answers

Does the After report trigger get fired if report execution fails?
Yes.

Explain PGA.
PGA - Program Global Area - area in memory used by a single Oracle user process.

Ramakutty 12-6-2011 04:09 AM

Oracle interview questions and answers

List various levels of Auditing ?
- Statement Auditing,
- Privilege Auditing
- Object Auditing.

List the types of snapshots.
- Simple
- Complex



Ankur 12-6-2011 04:08 AM

Oracle interview questions and answers

List the characterstics of data files.
- Data file can be associated with only one database.
- Once it is created, it can't change size.
- One or more database files together form a table space.

When does a Transaction finish ?
- When it is committed
- OR When it is Rolled back.

What is the function of ROLLBACK?
- It retracts the changes occuring from the execution of SQL statements in the transaction.



Rajat 12-6-2011 04:08 AM

Oracle interview questions and answers

Explain tablespace.
Logical Storage Unit into which a database is divided.

What is Oracle View?
Oracle View - A virtual table which has a query attached to it.

Do views contain/store data?
No. Views do not contain/ store any data.


Sakshi 12-6-2011 04:07 AM

 

Test your oracle knowledge with our multiple choice questions!


 

Oracle interview questions

Hot backup vs. cold backup

A database backup while it is still up and running is a Hot backup and it must be in archive log mode.
A cold backup is a backup while it is shut down. The database does not require being in archive log mode in this mode.
The benefit of a hot backup is that the database is still available for use while the backup is occurring.
A cold backup is easier to administer the backup and recovery process.
Cold backups does not require being in archive log mode and thus slight performance gain as the database is not writing archive logs to disk.

What are actual and formal parameters?

Actual Parameters

The variables or expressions referenced in the parameter list of a subprogram call are actual parameters.
Following procedure call lists two actual parameters named empno and amt:

raise_sal(empno, amt);

Formal Parameters

The variables declared in a subprogram specification and referenced in the subprogram body are formal parameters.
Following procedure declares two formal parameters named empid and amt:

PROCEDURE raise_sal(empid INTEGER, amt REAL) IS current_salary REAL;

Oracle interview questions

Can we protect our PL/SQL source code?

Yes, we can protect. PL/SQL V2.2 implements a binary wrapper for PL/SQL programs to protect the source code.
The utility available in this version transforms the PL/SQL source code into portable binary object code and thus protect from exposing your proprietary algorithms and methods.

Explain an Exception and its types.

Exception is the error handling part of PL/SQL block.
Exception is of two types: Predefined and user defined.

Some of Predefined exceptions are

  • ZERO_DIVIDE
  • NO_DATA_FOUND
  • TOO_MANY_ROWS
  • LOGON_DENIED
  • CURSOR_ALREADY_OPEN
  • INVALID_NUMBER
  • NOT_LOGGED_ON
  • STORAGE_ERROR
  • PROGRAM-ERROR
  • TIMEOUT_ON_RESOURCE
  • VALUE_ERROR
  • DUP_VAL_ON_INDEX
  • INVALID_CURSOR.

More oracle interview questions

Oracle Question 1: How to retrieve 5th highest sal from emp table?
Oracle Question 2: What is $FLEX$ and $PROFILES$? Where are they used?
Oracle Question 3: Explain how to pass values from one table to another by using Bulk collect.
Oracle Question 4: How to call a trigger inside a stored procedure?
Oracle Question 5: What is WATER MARK IN oracle? Explain the significance of High water mark.
Oracle Question 6: What is an object groups?
Oracle Question 7: Difference between clustering and mirroring
Oracle Question 8: Difference between paging and fragmentation
Oracle Question 9: Can you explain how to insert an image in table in oracle?
Oracle Question 10: How to find out second largest value in the table?
Oracle Question 11: Disadvantage of user defined function in oracle.
Oracle Question 12: Explain the significance of cluster table or non cluster table.
Oracle Question 13: What is pragma restrict_reference in oracle 9i? When do we use it?
Oracle Question 14: Why can't we assign not null constraint as table level constraint in oracle?
Oracle Question 15: Explain the use of between page triggers in REPORTS.
Oracle Question 16: How to delete all duplicate records from a table using subquery?
Oracle Question 17: How many types of trigger can be used in a table at a time? What are they?
Oracle Question 18: What is partitioned table? What are its types? Explain its purpose and how to create.
Oracle Question 19: What is the role of Archiver [ARCn].
Oracle Question 20: Structural difference between bitmap and btree index in oracle.
Oracle Question 21: Can you explain how to convert oracle table data into excel sheet?
Oracle Question 22: When should we go for hash partitioning?
Oracle Question 23: What is Materialized view? What is a snapshot?
Oracle Question 24: What are the advantages of running a database in NO archive log mode?
Oracle Question 25: What are the advantages of running a database in archive log mode?
Oracle Question 26: What is dba_segment in oracle?
Oracle Question 27: Purpose of using Nextval while creating the sequence.
Oracle Question 28: What is forall Statement? Explain with an example
Oracle Question 29: What is flashback Query? Explain its uses with an example
Oracle Question 30: What is the tablespace in Oracle? Purpose and significance
Oracle Question 31: How to use sequence and what use of sequence cache?
Oracle Question 32: Difference between formula column and place holder.
Oracle Question 33: How to create placeholder columns in oracle?
Oracle Question 34: What is autonomous transaction?
Oracle Question 35: How to create LOV in Oracle forms?
Oracle Question 36: Which sql command to be used to get a print out from oracle?
Oracle Question 37: What is sql*loader parameters and where do we use it?
Oracle Question 38: Explain how to see the report output in excel sheet in oracle applications.
Oracle Question 39: Can we restore a Table that accidentally dropped? How?
Oracle Question 40: What is the use of Data Link in Reports?
Oracle Question 41: What are the types of triggers available in Oracle Reports?
Oracle Question 42: What are the triggers associated with image items? Explain them
Oracle Question 43: Explain how to view the status of the Rollback segment in oracle.
Oracle Question 44: What is the use of NOARCHIEVELOG parameter in oracle database?
Oracle Question 45: How the SMON process is used to write into LOG files?
Oracle Question 46: What are the types of calculated columns available? Explain them

Related Links

  • Fundamental ODP.NET Classes
    To retrieve data from an Oracle database using ODP.NET, we need to work with a few of the ODP.NET classes.....
  • Retrieving Data Using OracleDataReader
    OracleDataReader is simply a read-only and forward-only result set. It works only if the database connection is open and it makes sure that the connection is open while you are retrieving data. As the data that it retrieves is read-only, it is a bit faster than any other method to retrieve data from Oracle.......
  • Working with Data Tables and Data Sets
    The OracleDataAdapter class is mainly used to populate data sets or data tables for offline use. The OracleDataAdapter simply connects to the database, retrieves the information, populates that information into datasets or data tables, and finally disconnects the connection to the database.......
  • Working with DataTableReader
    DataTableReader is complementary to a DataTable object, and is mainly used as a type of Data Reader in the disconnected mode................
  • Presenting Master-Detail Information Using a Dataset
    A DataSet object can have its own relations between data tables existing in it. We can add these relations dynamically at the client side (within an application), to represent master-detail (or hierarchical) information......
  • About the OracleCommand Object
    OracleCommand is not simply meant for OracleDataReader. It has got a lot of functionality for itself. Let us see few of the most commonly used features of OracleCommand in this section.......
  • Techniques to Improve Performance while Retrieving Data
    Performance tuning is a great subject in Oracle. Volumes of books would not be enough to cover every aspect of performance tuning in Oracle.........
  • Oracle Web RowSet In this chapter we will use the XML document representation of a result set generated with an SQL query to modify a relational database table. .
 
Interview questions
 
Home | Login | About us | Sitemap | Contact us