Oracle Interview Questions – Part 44

Q.What is a shared pool? 
A.It is the area in SGA that allows sharing of parsed SQL statements among concurrent users.

Q.What is snapshot in oracle? 
A.It is a read only copy of a table or a subset of a table.A recent copy of a table or a subset of rows or cols of a table is called as snapshot in oracle. A snapshot is more useful in distributed computing environment. We can create snapshot in oracle using command create snapshot. We can specify refresh interval while creating snapshot.It is the read-only copy of a master table on a remote node which is refreshed periodically refreshed to reflect changes made to the master table.

Q.What are LOB datatypes? 
A.The LOB datatypes such as BLOB, CLOB, NCLOB and BFile can store large blocks of unstructured data such as graphics, image, video clips etc. They provide random and piece-wise access to the data.

Q.What is a cursor? What are its types? 
A.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.
The main types of Cursors are:
Implicit cursors (predefined cursor): – The oracle engine implicitly opens a cursor on the server to process each SQL statement. Implicit cursors are managed by the Oracle engine itself.
Explicit Cursors (user defined cursor): – Explicit Cursors are the cursors created by the user inside a PL/SQL block. The declare section of a PL/SQL block and used within its executable Section.

Q.What is implicit cursor in Oracle? 
A.An implicit cursor is a cursor which is internally created by Oracle.It is created by Oracle for each individual SQL.A session contains a single implicit cursor which is defined automatically by PL/SQL.The cursor gets assigned to represent the execution of a statement whenever it is executed.

Q.Can you pass a parameter to a cursor? Explain with an explain
A.Yes, explicit cursors can take parameters

Q.What are the rules of writing package?
A.Packeges are PL/SQl constructs that allow related data to be stored together. A package has two parts: specification and a body.
The syntax of writing a package is:
procedure_specification |
function_specification |
variable_declaration |
type_definition |
exception_declaration |
END [pkg_name];
Thus the rules of writing a package would be:
•A package should have a name,
•The elements within the package should be the same as they are in the declarative section of an anonymous block.
•The same syntax rules apply to the package header as the declarative section, except for the procedures and the functions declarations.

Q.What are the parameters to be provided while executing Export and Import commands?
A.The parameters that need to be provided while executing IMPORT or EXPORT commands are: The file name
Write access
The commit count number
The nickname
For example:

Q.What is configure command and recovery catalog?
A.A recovery catalog is a schema stored in a database. It tracks backups and stores scripts for use in RMAN backup and recovery situations.
Configure command used in RMAN,is used to configure the parameters for the RMAN database.
It is used while making backups and recoveries.
What are different types of backup? (Hot, Cold, logical, Physical) Explain in detail.
Cold Backup
In this type of backup, after the database is shut down, DBA exits the SVRMGR utility and copies the log files, data files and control files onto a backup media.Once the backup has been completed the DBA can restart the database.
Physical Backup
The operating system saves the database files onto tape or some other media. This is useful to restire the system to an earlier point whenever needed.
Logical Backup
In logical backup technique, the IMPORT/EXPORT utilities are used to create the backup of the database. A logical backup backs-up the contents of the database. A logical backup can be used to restore the database to the last backup. However, unlike physical back, it should not be used to create an OS back up copy because restoring using this approach would make it possible to correct the damaged datafiles. Therefoe in these situations physical backups should be preferred.
Hot backup
A few systems that need to support continuous operation, it is difficult to bring down the database without interrupting the service provided to the users. In such cases, hot backup approach should be used.
There are two modes in which the hot backup works:
The database operations are suspended until archiving has been completed if the on-line redo log files are filled before they can be archived.
It is important that the directory containing the archived log files doesnt become full because if it does the ARCH thread wouldnt be able to archive the redo log files. The DBA has to continuously monitor the used-space percentage in the archive directory.
The database can be placed into ARCHIVELOG mode at the creation time or after the database has been created.
However, the database is shutdown before connecting as the user INTERNAL.
It was originally created in NOARCHIVELOG mode, this technique can be used to place the database in ARCHIVELOG mode.

Q.How are the indexes updated?
A.Whenever a table partition is modified using a DDL command, the associated index partitions are placed in the default tablespace or in the same tablespace as the data segments. This has been an issue with the previous releases of the Oracle databases.
With the Oracle 10g release, it has become possible to modify the partitioned table, using the UPDATE INDEXES clause. It can rebuild the associated index segments automatically. It helps to reduce the level of management needed and also enhances the availability of the data in the table.
You can create and Alter the table and update indexes to reflect the changes.
Latches vs Enqueues
Enqueues permit several concurrent processes to have varying degree of sharing of resources. Any object which can be concurrently used, can be protected with enqueues. The enqueue is obtained using an OS specific locking mechanism. An enqueue allows the user to store a value in the lock.
In latches there is no ordered queue of waiters like in enqueues. Latch waiters may use wakeup timers and retry or spin (only in multiprocessors). Since the processes keep trying concurrently, any process can get the latch. The first process to try might be the last one to get.

Q.What is nested table? Explain the purpose of nested table.
A.A nested table is an unordered set of data elements. These data elements are all of the same datatype. It has a single column whose type is either built in or an object type. It is a table stored within the structure of another table.
NESTED TABLE employee STORE AS employee_table
Can load the entire nested table into the database as column values. This means that we can store and retrieve nonatomic data in a single column.
The answers to following questions will be made available soon. Keep visiting.

Q.What is large object in oracle? Explain its purposes.
A.Large objects (LOB’s) are exclusively used to hold large amounts of data. It can hold data in tetra bytes. Different types of LOBs include internal, external, persistent and temporary. Binary LOB’s are typically used to store graphics, video, or audio data.
•Enables you to access and manipulate the data efficiently in your application
•Is optimized for large amounts of data
•Provides a uniform way of accessing data stored within the database or outside the database

Q.Explain types of large objects in oracle, i.e. BLOB, LLOB,NCLOB and BFILE.
Internal LOBs:
BLOBs, CLOBs, and NCLOBs are Internal LOBs stored inside database tablespaces to optimize space and efficient access. Changes to internal LOBs can be committed or rolled back.
They use copy semantics and participate in the transactional model of the server and can be recovered on media failure.
The ACIDFoot 1 properties are applicable to internal LOBs too.
The BLOB data type stores binary large objects. BLOB can store up to 4 gigabytes of binary data.
The CBLOB data type stores character large objects. CLOB can store up to 4 gigabytes of character data.
The NCBLOB data type stores character large objects in multibyte national character set. NCLOB can store up to 4 gigabytes of character data.
External LOBs:
BFILES are External LOBs stored in operating system files outside database tablespaces. These files use reference semantics.
The BFILE datatype allows read-only byte stream access to large files on the file system of the database server. The maximum file size supported is 4 gigabytes.
Define read-only replication and its uses.
Read only replication creates local copy of table data originating from one or more remote master tables. An application can query the data in a read-only table snapshot, but cannot insert, update, or delete rows in the snapshot of the data.
•Can be treated as back ups of original.
•For faster query performance, read only replication is used.
Read-only replication and snapshots
Replication is the process of copying database into one or more databases to make a system distributed.
Changes made to one database are forwarded and applied to the databases at the rest of the locations.
Advantages of Replication:
•Provides user with fast, local access to shared data,
•Protects availability of applications
•Even if one site becomes unavailable, users can continue to query or even update the remaining locations.
Read-only snapshots are used for query only. Changes made to the primary database are replicated to the snapshot by the Mobile Client.

Q.What are the different Levels of Auditing?
A.Statement Auditing
Privilege Auditing
and Object Auditing.

Q.What is implicit cursor in Oracle?
A.An implicit cursor is a cursor which is internally created by Oracle.
It is created by Oracle for each individual SQL.

Q.Explain the attributes of explicit cursor.
A.There are four attributes of explicit cursor in oracle.
this evaluates TRUE when cursor is open else FALSE.
this evaluates TRUE if last fetch succeeded.
Evaluates TRUE if last fetch failed.
This returns number of record fetched from active set.

Q.Describe ref cursor in Oracle.
A.Cursor is a reference type in oracle. We can allocate different storage locations to the cursor when the program runs.
Return_type – Name of new reference type.
Return Type – This represents select list type that will be returned by the query.

Q.What is a package cursor?
A.In a package cursor, the SQL statement for the cursor is attached dynamically at runtime from calling procedures

Q.Explain why cursor variables are easier to use than cursors.
A.They are easier to define as there is no need to specify a query statement.The query can also be specified dynamically at the opening time.
Cursor variables are easier to open.Cursor variables can be passed into procedures or functions.

Q.Explain types of tables in oracle.
A.In Oracle, tables are the basic storage units. There are several types of database tables. Different types of tables have different characteristics.
1.Heap Organized Table:
The default table type in Oracle is heap table. If you use “create table” clause normally, you create a standard database table. Exp:
create table A
x char
2.Object Table
This type of table is created based on an object type and not collection of columns. It is usually used in special cases. The logic depends on object-oriented programming. “create table ” statement for object tables is:
create table test of x_type;
create or replace type atype
as object
a varchar2(30),
b date
Create table p of atype;
3.Index Organized Tables
Index Organized Tables have their primary key and non-key column data stored within the same B-Tree structure.The data is stored within the primary key index.IOTs make accessing data via the primary key quicker as the key and the data reside in the same structure.Since there is no need to read an index, the table data is read in a separate structure.Less duplication of the key columns in an index and table mean low storage requirements.
4.Hash clustered tables
Hash clusters provide a good facility for storage and retrieval.In the case of a heap table, the rows are not sorted in a user-controlled manner.Oracle hash cluster tables can improve random row access speed by foour times as the hash can get the row location far faster than index access.Multiple table hash clusters store logically-related rows on a single data block due to which you can access a whole unit of data in a single physical I/O.
5.Nested tables
Nested table can be used to link the subordinate data items to the base table using the object ID (OID).Oracle objects can be directly referenced by using pointers as opposed joining relational.Objects in standard relational databases need to be reassembled every time it is used.
6.Index cluster tables
An index cluster uses an index to maintain row sequence.A table cluster is a group of tables that share the same data blocks, since they share common columns and are often used together.All rows for each table are stored in the same data blocks, When you create cluster tables are created.The cluster key value is the value of the cluster key columns for a particular row.
Index cluster tables can be either multi-table or single-table.
7.Global temporary tables
The maintenance and management of temporary tables can be delegated to the server by using Global Temporary Tables.
The data in a global temporary table is private.

Q.Describe Oracle architecture in brief.
A.The Oracle database has:
1.Logical layer: The components of the logical layer map the data to these physical components
2.Physical layer: The physical layer consists of the files that reside on the disk
Tablespaces and Datafiles
The database is divided into one or more logical pieces known as tablespaces. A tablespace is used to logically group data together
The Database Schema: A collection of logical-structure objects, known as schema objects.
These schema objects consist of structures such as:
5.stored procedures,
6.database triggers, and
Segments, Extents, and Data Blocks
Within Oracle, the space used to store data is controlled by the use of logical structures. These structures consist of the following:
1.Data blocks-A block is the smallest unit of storage in an Oracle database. (contains header information concerning the block itself as well as the data)
2.Extents-Extents consist of data blocks.
3.Segments-A segment is a set of extents used to store a particular type of data.
The Oracle Instance has:
1.User processes
2.Oracle background processes
3.The shared memory
The Oracle Memory Structure contains:
1.The System Global Area (SGA)
2.The Data-Dictionary Cache
3.The Program Global Area (PGA)

Q.Define referential integrity.
A.Referential integrity is the rules that governs the relationships between primary keys and foreign keys of the tables and ensure data consistency. It ensures the value of foreign key be matched by the value of a primary key in another table.

Q.Define Statement Auditing, Privilege Auditing and Object Auditing in oracle.
1.Statement auditing is the auditing of the powerful system privileges without regard to specifically named objects.
2.Privilege auditing is the auditing of the use of powerful system privileges without regard to specifically named objects.
3.Object auditing is the auditing of accesses to specific schema objects without regard to user.

Q.Advantages of PL/SQL.
A.Support SQL data manipulation.
Provide facilities like conditional checking, branching and looping.
Provide fast code execution since it sends SQL statement as a block to the oracle engine.

Q.What is WebDB?
A.WebDB is a tool written in PL/SQL, used to develop HTML based application that can easily interact with Oracle data. It uses HTML components and follows HTML standards which makes it compatible to many browsers in the market.QNested Table is a table inside a table. It can have several rows for each row of its parent table.

Q.What is SQL*Plus?
A.SQL*Plus is an interactive and batch query tool.It gets installed with every Oracle Database Server or Client installation.It has a command-line user interface, a Windows Graphical User Interface (GUI) and the iSQL*Plus web-based user interface.SQL*Plus has its own commands and environment, and it provides access to the Oracle Database.
Following types of commands can be entered and executed:
Operating system commands
With the above commands, following actions can be performed:
Develop and run batch scripts
Perform database administration
Format, perform calculations on, store, and print from query results
Examine table and object definitions
SQL*Plus can be used to:
Generate reports interactively
Generate reports as batch processes
Output the results to text file, to screen, or to HTML file for browsing on the Internet.
Generate reports dynamically using the HTML output facility of SQL*Plus, or using the dynamic reporting capability of iSQL*Plus to run a script from a web page.

Q.Explain how to change SQL*Plus system settings.
A.The SET command can be used to change the settings in the SQl*PLUS environment.
1.SET AUTOCOMMIT OFF: Turns off the auto-commit feature
2.SET FEEDBACK OFF: Stops displaying the “27 rows selected.” message at the end of the query output
3.SET HEADING OFF: Stops displaying the header line of the query output
4.SET LINESIZE 256: Sets the number of characters per line when displaying the query output
5.SET NEWPAGE 2: Sets 2 blank lines to be displayed on each page of the query output
6.SET NEWPAGE NONE: Sets for no blank lines to be displayed on each page of the query output
7.SET NULL ‘null’: Asks SQL*Plus to display ‘null’ for columns that have null values in the query output
8.SET PAGESIZE 60: Sets the number of lines per page when displaying the query output
9.SET TIMING ON: Asks SQL*Plus to display the command execution timing data
10.SET WRAP OFF: Turns off the wrapping feature when displaying query output.

Q.What is Oracle Server Autotrace?
A.The Autotrace feature of Oracle server generates two statement execution reports which are useful for performance tuning. They are:
1.Statement execution path
Displays execution loop logic of a DML statement.
2.Statement execution statistics
Displays various execution statistics of a DML statement.
Steps to turn the autotrace feature ON:
Create table PLAN_TABLE.
Create a special security role called PLUSTRACE.
Grant PLUSTRACE role your user account.

Q.What is Output Spooling in SQL*Plus?
A.The spooling feature facilitates copying of all the contents of the command line SQL*Plus to a specified file. This feature is called Spooling.
SPOOL filename: Turns on output spooling with the specified file.
SPOOL OFF: Turns off output spooling and close the spool file

Q.What are SQL*Plus Environment variables?
A.The behaviour of SQL PLUS depends on some environmental variables predefined in the OS: ORACLE_HOME: This variable stores the home directory where the Oracle client.

Q.What are the original Export and Import Utilities?
A.The import and export utilities of oracle provide a very simple way to transfer data objects between Oracle databases. These may reside on heterogeneous software and hardware platforms.The exported data objects are extracted and written to an export dump file.
The Import utility reads the object definitions and table data from the dump file.An export file is an Oracle binary-format dump file located on disk or tape which can be transferred by using FTP or physically transported. They can then be used with the Import utility to transfer data between databases on a single system. The files can also be used as backups in addition to normal backup procedures.Export and Import utilities are now being replaced by Data Pump Export and Import utilities in Oracle 10g.

Q.What is SQL loader? What are the files used by SQL Loader? Explain the method of loading data.
A.SQL Loader loads data from external files into tables in oracle. SQL loader uses following files
1.Method for loading data
2.Connectional path load
3.Direct Path load

Q.What is a SQL*Loader Control File?
A.A SQL*Loader control file contains the following specification:
1.Location of the input data file.
2.The format of the input date file.
3.The target table where the data should be loaded.
4.The way input data fields should be mapped to target table columns.
5.Select criteria to select input records for loading.
6.Location where the errors should be reported.

Q.What is locking, advantages of locking and types of locking in oracle?
A.Locking protect table when several users are accessing the same table. Locking is a concurrency control technique in oracle. It helps in data integrity while allowing maximum concurrency access to data. Oracle offers automatic locking whenever situation requires. This is called implicit locking.
Types of locking
1.Shared Lock
This type is placed on a record when the record is being viewed.
2.Exclusive lock
This is placed when Insert, Update or Delete command is performed. There can be only one exclusive lock on a record at a time.

Q.What are transaction isolation levels supported by Oracle?
If row locks are obtained by a certain transaction, then any other transaction that contains DML needs to wait until the row locks have been released by that particular transaction.
If a serializable transaction contains DML that updates a resource that has been updated in another transaction but which has not committed yet, then the DML statement fails.

Q.Differences between CHAR and NCHAR in Oracle.
A.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. (NCHAR stores characters in Unicode).A character could be 1 byte to 4 bytes long depending on the character set used.

Q.Explain the characteristics of function object in Oracle
A.A function can allow one or more or no parameter.A function must have explicit return statement.The data type of the return value must be declared in the function’s header.

Q.Explain an ORA-01555
A.You get this error when you get a snapshot too old within rollback. It can usually be solved by increasing the undo retention or increasing the size of rollbacks. You should also look at the logic involved in the application getting the error message.

Q.Explain the difference between $ORACLE_HOME and $ORACLE_BASE.
A.ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is where the oracle products reside.

Q.What command would you use to create a backup control file?
A.Alter database backup control file to trace.

Q.Give the stages of instance startup to a usable state where normal users may access it.
A.STARTUP NOMOUNT – Instance startup. STARTUP MOUNT – The database is mounted. STARTUP OPEN – The database is opened


This site uses Akismet to reduce spam. Learn how your comment data is processed.