oracle interview questions answers

 oracle interview questions answers

1) What are the components of physical database structure of Oracle database

Components of the physical database structure are given below.

 One or more data files.

 Two or more redo log files.

 One or more control files.

2) What are the components of logical database structure in Oracle database

Components of a logical database structure.

 Tablespaces

 Database's schema objects

3) What is a tablespace

A database contains a Logical Storage Unit called tablespaces. A tablespace is a set of related logical structures. Actually a tablespace

groups related logical structures together.

4) What is a SYSTEM tablespace and when it is created

When the database is created in Oracle database system, it automatically generates a SYSTEM named SYSTEM tablespace. The

SYSTEM tablespace contains data dictionary tables for the entire database.

5) What is an Oracle table

A table is a basic unit of data storage in an Oracle database. A table contains all the accessible information of a user in rows and

columns.

6) In the Oracle version 9.3.0.5.0, what does each number shows

Oracle version number refers:

 9 - Major database release number

 3 - Database maintenance release number

 0 - Application server release number

 5 - Component Specific release number

 0 - Platform Specific release number

7) What is bulk copy or BCP in Oracle

Bulk copy or BCP in Oracle is used to import or export data from tables and views but it does not copy structure of the same data.

The main advantage of BCP is a fast mechanism for copying data and you can also take the backup of data easily.

8) What is the relationship among database, tablespace and data file

An Oracle database contains one or more logical storage units called tablespaces. These tablespaces collectively store whole data of

databases and each tablespace in Oracle database consists of one or more files called datafiles. These datafiles are physical

 a structure that confirms with the operating system in which Oracle is running.

9) What is a snapshot in Oracle database

A snapshot is a replica of a target master table from a single point in time. In simple words you can say, a snapshot is a copy of a

the table on a remote database.

10) What is the difference between hot backup and cold backup in Oracle Tell about their benefits also.

Hot backup (Online Backup): A hot backup is also known as an online backup because it is done while the database is active. Some

sites can not shut down their database while making a backup copy, they are used 24 hours a day, 7 days a week.

Cold backup (Offline Backup): A cold backup is also known as an offline backup because it is done while the database has been

shut down using the SHUTDOWN normal command. If the database is suddenly shut down with an uncertain condition it should be

restarted with RESTRICT mode and then shut down with the NORMAL option.

For a complete cold backup, the following files must be backed up.

All data files, All control files, All online redo log files(optional), and the init.ora file (you can recreate it manually).

11) How many memory layers are in the Oracle shared pool

Oracle shared pools contains two layers:

1. library cache

2. data dictionary cache

12) What is save point in Oracle database

Save points are used to divide a transaction into smaller parts. It allows the rolling back of a transaction. Maximum of five save points are

allowed. It is used to save our data, whenever you encounter an error you can roll back from the point where you save your

SAVEPOINT.

13) What is hash cluster in Oracle

Hash cluster is a technique to store a data in hash table and improve the performance of data retrieval. Hash function is applied on

table row's cluster key value and store in hash cluster.

14) What are the various Oracle database objects

Tables: This is a set of elements organized in a vertical and horizontal fashion.

Tablespaces: This is a logical storage unit in Oracle.

Views: It is a 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.

15) What is the difference between pre-select and pre-query

A pre-query trigger fire before the query executes and fire once while you try to query. With the help of this trigger, you can modify

the where clause part dynamically.

Pre-select query fires during the execute query and count query processing after Oracle forms construct the select statement to be

issued, but before the statement is actually issued.

Pre-query trigger fires before Pre-select trigger.

16) What are the different types of modules in Oracle forms

Following are the different modules in Oracle forms:

 Form module

 Menu module

 Pl/SQL Library module

 Object Library modules

17) What is the usage of ANALYZE command in Oracle

ANALYZE command is used to perform various functions on index, table, or cluster. The following list specifies the usage of ANALYZE

command in Oracle:

 It is used to identify migrated and chained rows of the table or cluster.

 It is used to validate the structure of the object.

 It helps in collecting the statistics about objects used by the optimizer. They are then stored in the data dictionary.

 It helps in deleting statistics used by objects from the data dictionary.

18) Can you create a synonym without having a table

Yes. We can create a synonym without having a base table.

19) What types of joins are used in writing SUBQUERIES

 Self-join

 Outer Join

 Equi-join