30 Oracle Interview Questions and Answers to Help You Study

oracleinterviewquestionsandanswersOracle is an object-relational database management system, also referred to as the Oracle RDBMS. It’s a commonly used database system and an alternative to MySQL. Most companies maintain some form of informational database, making database administrator and engineer positions stable jobs in the IT field.

You’ll have a lot of competition during the interview process. By studying this list of Oracle interview questions and answers, you’ll be forced to think critically about what you already know, learn and study new concepts, and be prepared to show the interviewer your knowledge.

If you’re still learning the ins and outs of database maintenance, check out this handy beginner’s guide on becoming an Oracle database administrator. Or, study more with this course on the architecture, processes, and roles of an Oracle DBA.

Questions and Answers

1. Explain the Oracle version numbering system.

We’ll use the release number 10.1.0.3.2 as an example.

  • 10 is the major database release number.
  • 1 is the database maintenance release number.
  • 0 is the application server release number.
  • 3 is the component-specific release number.
  • 2 is the platform-specific release number.

2. What file types compose the physical structure of an Oracle database?

At least one data file, redo log file, and control file.

3. Define a synonym, how many types there are, and explain the difference between them.

A synonym is another term for a table, sequence, or view. There are private synonyms and public synonyms. Only the owner of a private synonym has access to it, while public synonyms can be accessed by any of the database’s users.

4. Where are errors and other corruptions stored for easy access?

The alert log.

5. What’s inside a control file?

A database’s physical components, including the database name, a time stamp of its creation, and the name and locations of its files.

6. Define a rollback segment.

A rollback segment is an object in the database used to temporarily store before images.

7. How would you figure out which time zone a database was using?

SELECT dbtimezone FROM DUAL;

8. What would the syntax to find the current time and date look like?

SELECT TO_CHAR (SYSDATE, 'YYYY-MM-DD HH24:MI:SS') "Current_Date"   FROM DUAL

9. Convert a string to a date.

to_date('2013/12/10', 'yyyy/mm/dd') 

December 10, 2013

9. Define a schema, and give five examples.

A schema is a collection of logical database objects owned by, and with the same name of, the database user. Schema objects can include tables, indexes, clusters, sequences, and views.

10. Explain the auditing mechanism.

Auditing monitors and records the actions of select database users.

11. What is a snapshot?

A snapshot, or a materialized view, replicates a target master table at specified times.

12. What are the two types of snapshots? What’s the difference?

Simple: Simple snapshot queries exclude CONNECT BY and GROUP BY clauses, JOINs, snapshots of operations, and sub-queries.
Complex: Complex snapshots will contain at least one of the aforementioned.

13. How can you refresh a snapshot?

FORCE: The server that the snapshot is on will try to perform a fast refresh, and then opt for a complete refresh if that doesn’t work.
COMPLETE: The server will execute the snapshot query, replacing the snapshot with the query’s result set.
FAST: The latest changes applied to the master table since the last refresh are applied to the snapshot.

14. What’s the advantage of using an index?

Indexes provide quicker access to data blocks.

15. How do you rebuild an index?

ALTER INDEX index_name REBUILD;

16. How many join types are used to write subqueries? What are they?

Three types: self join, equi-join, and outer join.

17. How many types of export backups are there? What are they?

Three types: full back, incremental, and cumulative back-up.

18. What is a dictionary cache?

A dictionary cache uses a data dictionary table to store information about database objects.

19. How many types of pop-up windows are there? What are they?

Three types: pop-up pages, pop-up values list, and pop-up field editor.

20. Which of the following are restricted prodecures, and which of the following are unrestricted procedures?

Class: unrestricted.
Call-query: unrestricted.
Execute-query: restricted.
User-exit: unrestricted.
Exit-form: restricted.
Up: restricted.
Message: restricted.
Post: restricted.
Break: unrestricted.

21. Explain the size limits of a form.

A form can be a maximum of 255 characters wide and 255 characters long.

22. What do the following stand for? PLL, FMB, and MMB?

PLL is PL/SQL library module binary.
FMB is form module binary.
MMB is menu module binary.

23. What is a cluster?

A cluster is one or more tables physically grouped together based on shared columns.

24. What is the benefit to using a hash cluster?

It makes data retrieval quicker and more efficient, requiring less I/Os to be performed, making it preferable to using indexed tables. This is so because it hashes the cluster key value, which will directly locate the specified rows stored on the disk.

25. Explain the four cursor attributes.

%ROWCOUNT:

  • If cursor is open, returns TRUE. If cursor is closed, returns FALSE.

%NOTFOUND:

  • If cursor is closed, or declared, but not open, returns INVALID_CURSOR.
  • If a row was not returned, returns TRUE.
  • If a fetch was successful, returns FALSE.
  • If cursor is open, but fetch was not successfully executed, returns NULL.

%FOUND:

  • If cursor is closed, or declared, but not open, returns INVALID_CURSOR.
  • If a row was not returned, returns FALSE.
  • If a fetch was successful, returns TRUE.
  • If cursor is open, but fetch was not successfully executed, returns NULL.

%ISOPEN:

  • If cursor is closed, or declared, but not open, returns INVALID_CURSOR.
  • Returns amount of rows that were fetched.

26. What is the minimum and maximum amount of columns a table can have?

Between 1 and 254.

27. How many types of segments are there? What are they?

Four types: data segment, index segment, temporary segment, and rollback segment.

28. What are V$SESSION and V$SESSION_WAIT used for?

They are used to determine connected sessions, and which resources those sessions are waiting for.

29. What would you use to force a log switch?

ALTER SYSTEM SWITCH LOGFILE;

30. What does it mean to overload a procedure?

Overloading a procedure is very similar to overloading functions in general programming. The process includes the creation of numerous procedures within the same package, and all with the same name, but with different arguments.


You might also want to read: