Frequently Asked Oracle Interview Questions

Jump To Right Section Show

Oracle is a secured database that’s broadly utilized in multinational corporations. On this article, we’ll give attention to a number of important Oracle Interview questions that you could be be requested throughout an Android Developer job interview. Let’s see 45 very regularly requested oracle interview questions & solutions as under:

1) What are the elements of the bodily database construction of the Oracle database?

Elements of the physical database construction are given under.

A number of information records data.
Two or extra redo log files.
A number of management records data.

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

Elements of the logical database construction.

Tablespaces
Database’s schema objects

3) What’s a tablespace?

A database incorporates a Logical Storage Unit known as tablespaces. A tablespace is a set of associated logical buildings. Really, a tablespace teams associated logical buildings collectively.

4) What’s a SYSTEM tablespace and when it’s created?

When the database is created in the Oracle database system, it robotically generates a SYSTEM named SYSTEM tablespace. The SYSTEM tablespace contains data dictionary tables for your complete database.

5) What’s an Oracle desk?

A desk is the fundamental unit of data storage in an Oracle database. A desk incorporates all of the accessible data of a person in rows and columns.

6) Within the Oracle model 9.3.0.5.0, what does every quantity reveals?

Oracle model quantity refers:

9 – Main database launch quantity
3 – Database upkeep launch quantity
0 – Software server release number
5 – Part Specific release quantity
0 – Platform Specific release quantity

7) What’s a 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 surely doesn’t copy the construction of the identical information. The primary benefit of BCP is the quick mechanism for copying information and you can even take the backup of information simply.

8) What’s the relationship between database, tablespace, and information file?

An Oracle database incorporates a number of logical storage items known as tablespaces. These tablespaces collectively retailer the entire information of databases and every tablespace within the Oracle database consists of a number of recordsdata known as datafiles. These information files are the physical structures that confirm the operating system wherein Oracle is operating.

9) What’s a snapshot within the Oracle database?

A snapshot is a reproduction of a goal grasp desk from a single time limit. In easy phrases, you possibly can say, a snapshot is a copy of a table on a remote database.

10) What’s the distinction between a sizzling backup and a chilly backup in Oracle? Inform about their advantages additionally.

Scorching backup (On-line Backup): A sizzling backup is also called a web based backup as a result of it’s performed whereas the database is lively. Some websites can’t shut down their database while making a backup copy, they’re used 24 hours a day, 7 days per week.

Chilly backup (Offline Backup): A chilly backup is also called an offline backup as a result of it’s performed whereas the database has been shutdown using the SHUTDOWN normal command. If the database is all of a sudden shutdown with an unsure situation it must be restarted with RESTRICT mode after which shutdown with the NORMAL choice. For an entire chilly backup, the next recordsdata have to be backed up. All information recordsdata, All management recordsdata, All on-line redo log recordsdata(non-compulsory), and the init.ora file (you possibly can recreate it manually).

11) What number of reminiscence layers are within the Oracle shared pool?

Oracle shared swimming pools incorporates two layers:

library cache
data dictionary cache

12) What’s the save level within the Oracle database?

Save factors are used to divide a transaction into smaller components. It permits the rolling again of a transaction. A most of 5 save factors are allowed. It’s used to avoid wasting our information, everytime you encounter an error you possibly can roll again from the purpose the place you save your SAVEPOINT.

13) What’s the hash cluster in Oracle?

Hash cluster is a way to retailer information within the hash desk and enhance the efficiency of information retrieval. The hash function is applied to the table row’s cluster key worth and retailer within the hash cluster.

14) What are the varied Oracle database objects?

Tables: It is a set of parts organized in a vertical and horizontal vogue.

Tablespaces: It is a logical storage unit in Oracle.

Views: It’s a digital desk derived from a number of tables.

Indexes: It is a efficiency tuning methodology to course of the information.

Synonyms: It is a name for tables.

15) What’s the distinction between pre-select and pre-query?

A pre-query set off fireplace earlier than the question executes and fireplace as soon as when you attempt to question. With the assistance of this set off, you possibly can modify the the place clause half dynamically. Pre-select question fires in the course of the execute question and depend question processing after Oracle types assemble the choose assertion to be issued, however earlier than the assertion is definitely issued. Pre-query set off fires earlier than Pre-select set off.

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

Following are the completely different modules in Oracle types:

Kind module
Menu module
Pl/SQL Library module
Object Library module

17) What’s the utilization of ANALYZE command in Oracle?

ANALYZE command is used to carry out varied capabilities on index, desk, or cluster. The next checklist specifies the utilization of ANALYZE command in Oracle:

It’s used to determine migrated and chained rows of the table or cluster.
It’s used to validate the construction of the item.
It helps in accumulating the statistics about objects utilized by the optimizer. They’re then saved within the information dictionary.
It helps in deleting statistics utilized by objects from the information dictionary.

18) Are you able to create a synonym with out having a desk?

Sure. We are able to create a synonym with out having a base desk.

19) What varieties of joins are utilized in writing SUBQUERIES?

Self Be a part of
Outer Be a part of
Equi-join

20) What’s the utilization of the management file in Oracle?

In Oracle, the management file is used for database restoration. The management file can be used to determine the database and redo log recordsdata that have to be opened for database operation to go forward, at any time when an occasion of an ORACLE database begins.

21) What’s a synonym?

A synonym is also called an alias for a desk, view, sequence, or program unit.

22) What are the various kinds of synonyms?

There are two varieties of synonyms or alias:

Non-public: It could actually solely be accessed by the proprietor.

Public: It may be accessed by any database person.

23) What’s the utilization of synonyms?

A synonym can be utilized to cover the actual title and proprietor of an object.
It gives public entry to an object.
It additionally gives location transparency for tables, views or program items of a distant database.
It simplifies the SQL statements for database customers.

24) How do you retailer photos in a database?

Sure, you possibly can retailer photos in a database using the Long Raw Data type. This data type is used to retailer binary information for two gigabytes of size. Nevertheless, the table can have only one Long Raw data type.

25) What’s the BLOB information kind in Oracle?

The BLOB data type is a knowledge kind with various size binary string. It’s used to retailer two gigabytes of reminiscence. For BLOB data type, the size must be laid out in bytes.

26) What’s the distinction between TRANSLATE and REPLACE in Oracle?

Translate is used to substitute a personality by character whereas Substitute is used to substitute a single character with a phrase.

27) What are the various kinds of database objects?

A list of different types of database objects:

Tables: It is a set of parts organized in a vertical and horizontal vogue.
Tablespaces: It is a logical storage unit in Oracle.
Views: It’s a digital desk derived from a number of tables.
Indexes: It is a efficiency tuning methodology to course of the information.
Synonyms: It is a title for tables.

28) What’s the utilization of Save Factors within the Oracle database?

Save Factors are used to divide a transaction into smaller phases. It permits rolling again a part of a transaction. There’s a most of 5 save factors allowed in Oracle Database. Each time an error is encountered, it’s doable to roll again from the purpose the place the SAVEPOINT has been saved.

29) What’s the distinction between post-database commit and post-form commit?

The post-database commit set off is executed after Oracle types situation the decide to the finalized transaction whereas, the post-form commit is fired in the course of the publish and commit transactions course of after the database commit happens.

30) What’s Logical backup in Oracle?

Logical backup is used to learn a set of database information and writing them right into a file. An Export utility is used to take the backup whereas an Import utility is used to recuperate from the backup.

31) What do you perceive by Redo Log file mirroring?

Mirroring is the method of getting a duplicate of Redo log recordsdata. It’s performed by creating a group of log files collectively. This ensures that LGWR robotically writes them to all of the members of the current online redo log group. If the group fails, the database robotically switches over to the following group. It diminishes the efficiency.

32) What’s the that means of recursive hints in Oracle?

The variety of instances a dictionary desk is repeatedly known as by varied processes is called a recursive trace. The recursive trace is occurred due to the small measurement of the information dictionary cache.

33) What are the constraints of the CHECK constraint?

The primary limitation of the CHECK constraint is that the situation have to be a Boolean expression evaluated utilizing the values within the row being inserted or up to date and may’t include subqueries.

34) What’s using the GRANT choice within the IMP command?

GRANT is used to import object grants.

35) What’s using the ROWS choice within the IMP command?

The ROWS choice signifies whether or not the desk rows must be imported.

36) What’s using the INDEXES choice within the IMP command?

The INDEXES choice is used to find out whether or not indexes are imported.

37) What’s using the ignoring choice in IMP command?

The IGNORE choice is used to specify how object creation errors must be dealt with.

38) What’s using the SHOW choice within the IMP command?

The SHOW choice specifies when the worth of present=y, the DDL throughout the export file is displayed.

39) What’s using FILE param in IMP command?

FILE param is used to specify the title of the export file to import. A number of recordsdata may be listed, separated by commas.

40) The right way to convert a date to char in Oracle? Give one instance.

The to_char() perform is used to transform date to the character. You may as well specify the format wherein you need to output.

SELECT to_char ( to_date (’12-12-2012′, ‘DD-MM-YYYY’) , ‘YYYY-MM-DD’) FROM twin;

Or,

SELECT to_char ( to_date (’12-12-2012′, ‘DD-MM-YYYY’) , ‘DD-MM-YYYY’) FROM twin;

41) What are precise and formal parameters?

Precise Parameters: Precise parameters are the variables or expressions referenced within the parameter checklist of a subprogram. Let’s see a process name that lists two precise parameters named empno and amt:

raise_sal(empno, amt);

Formal Parameters: Formal parameters are variables declared in a subprogram specification and referenced within the subprogram physique. The next process declares two formal parameters named empid and amt: