Oracle DBA Interview Questions - Part 1

Question 01: When Oracle database implemented PL/SQL Language?
Answer: The first version of the PL/SQL language is introduced in Oracle version 6. In oracle Oracle7, released in 1992, introduced PL/SQL stored procedures and triggers.

Question 02: In which version of Oracle RAC is implemented?
Answer: Oracle 9i database introduced Oracle RAC in year 2001. Later Oracle Database 10g introduced grid computing in 2003.


Question 03: What is Oracle database instance?
Answer: An Oracle instance is a set of memory structures that manage different database files. It consists of Shared/System Global Area (SGA) and a set of background processes.

Question 04: Name the Oracle schema object types.
Tables, Indexes, Partitions, Views, Sequences, Dimensions, Synonyms, PL/SQL subprograms and packages.

Question 05: What are the characteristics of Materialized Views?
i) Just like table materialized views contain data and consume storage space.
ii) They can be refreshed when the data in their master tables changes.
iii) They can improve performance of SQL execution when used for query rewrite operations.
iv) The existence of materialize view is transparent to SQL applications and users. So if optimizer sees use of materialize view is more efficient than accessing original table then for that query materialize view will be automatically used.

Question 06: Name the different types of Constraints
NOT NULL, Unique, Primary, Foreign, Check.

Question 07: What are the DDL statements?
DDL statements do structural changes or drop schema objects. For example: CREATE, ALTER, DROP, TRUNCATE, GRANT, REVOKE, AUDIT, NOAUDIT, COMMENT.

Question 08: What are the DML statements?
Data manipulation language (DML) statements query or manipulate data in existing schema objects. For example: SELECT, INSERT, UPDATE, MERGE, DELETE, EXPLAIN PLAN, LOCK TABLE.

Question 09: What are the TC statements?
Transaction control statements manage the changes made by DML statements and group DML statements into transactions. For example: COMMIT, ROLLBACK, ROLLBACK TO SAVEPOINT, SAVEPOINT, SET TRANSACTION.

Question 10: What are the Session Control statements?
Session Control Statements dynamically manage the properties of a user session. For example: ALTER SESSION, SET ROLE.

Question 11: What are the System Control statements?
System control statements change the properties of the database instance. The only system control statement is ALTER SYSTEM.

Question 12: What are the Embedded SQL statements?
Embedded SQL statements incorporate DDL, DML, and transaction control statements within a procedural language program.
For example:
- Define, allocate, and release cursors (DECLARE CURSOR, OPEN, CLOSE).
- Specify a database and connect to it (DECLARE DATABASE, CONNECT).
- Assign variable names (DECLARE STATEMENT).
- Initialize descriptors (DESCRIBE).
- Specify how error and warning conditions are handled (WHENEVER).
- Parse and run SQL statements (PREPARE, EXECUTE, EXECUTE IMMEDIATE).
- Retrieve data from the database (FETCH).