Oracle Interview questions

Posted by tib on May 15th, 2019

Oracle is a secured database that's wide utilized in international companies. The commonly asked questions from oracle database are given below.

1) What are the elements of physical database structure of Oracle database?

Components of physical database structure are given below.

• One or a lot of information files.
• Two or a lot of redo log files.
• One or a lot of control files.

2) What are the elements of logical database structure in Oracle database?

Components of logical database structure are given below.

• Tablespaces
• Database's schema objects

3) What's a tablespace?

A database contains Logical Storage Unit known as tablespaces. A tablespace may be a set of Oracle Training in Marathahalli connected logical structures. Truly a tablespace teams connected logical structures along.

4) What's a SYSTEM tablespace and once it's created?

When the database is created in Oracle info system, it automatically generates a SYSTEM named SYSTEM tablespace. The SYSTEM tablespace contains information dictionary tables for the whole database.

5) What's an Oracle table?

A table is basic unit of information storage in Oracle database. A table contains all the accessible information in the form of rows and columns.

6) Within the Oracle version 9.3.0.5.0, what will every number shows?

Oracle version number refers:

• 9 - Major database release number
• 3 - database maintenance release number
• 0 - Application server release number
• 5 - element Specific release number
• 0 - Platform Specific release number

7) What's 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 doesn't copy structure of same data.

The main advantage of BCP is fast mechanism for coping data and you can conjointly take the backup of data easily.

8) What's the relationship among database, tablespace and data file?

An Oracle database contains one or more logical storage units known as tablespaces. These tablespaces jointly store whole data of databases and every tablespace in Oracle database consists of 1 or more files known as data files. These data files are physical structure that makes sure with the OS in which Oracle is running.

9) What's a snapshot in Oracle database?

A snapshot may be a duplicate of a target master table from a single point-in-time. In easy words you can Oracle Training in Marathahalli with Placement say; snapshot may be a copy of a table on a remote database.

10) What’s the difference between hot backup and cold backup in Oracle? Tell about their advantages also.

Hot backup (Online Backup): A hot backup is additionally called on-line backup because it's done whereas the info is active. Some sites cannot shut down their database while creating a backup copy; they're used for 24 hour a day, seven days a week.

Cold backup (Offline Backup): a cold backup is additionally called offline backup because it's done while the database has been shutdown using the shutdown normal command. If the database is suddenly shutdown with a unsure condition it should be restarted with limit mode and so shutdown with normal option.

11) How many memory layers are within the Oracle shared pool?

Oracle shared pools contains 2 layers:

1. library cache
2. data dictionary cache

12) What's save purpose in Oracle database?

Save points are used to divide a transaction into smaller elements. It permits rolling back of a transaction. Maximum 5 save points are allowed. It’s accustomed save our data, whenever you encounter an error you can roll back from the point where you save your SAVEPOINT.

13) What's hash cluster in Oracle?

Hash cluster may be a technique to store information in hash table and improve the performance of information retrieval.

14) What are the various Oracle database objects?

• Tables: this is often a group of components organized in vertical and horizontal fashion.
• Tablespaces: storage unit in Oracle.
• Views: its virtual table derived from one or a lot of tables.
• Indexes: tuning method to process the records.
• Synonyms: this is a name for tables.

15) What's the difference between pre-select and pre-query?

• A pre-query trigger fire before the query executes and fire once while you are attempting to query. With the assistance of this trigger you can modify the wherever clause part dynamically.
• Pre-query trigger fires before Pre-select trigger.

16) What are the various types of modules in Oracle forms?

Following are the different modules in Oracle forms:

• Form module
• Menu module
• Pl/SQL Library module
• Object Library module

17) What's the usage of ANALYZE command in Oracle?

ANALYZE command is used to perform varied functions on index, table, or cluster.

ANALYZE command in Oracle:

• Validate the structure of the object.
• It helps in collecting the statistics regarding object utilized by the optimizer. They’re then keeping within the data dictionary etc.

18) Can you create a synonym while not having a table?

Yes. We are able to create a synonym without having a base table.

19) What types of joins are used in writing SUBQUERIES?

• Self join
• Outer join
• Equi-join