Master Oracle 19c: Test Your Knowledge with a Top 10 DBA Quiz

Language: Oracle SQL

1. How do you create a new user in Oracle 19c's multitenant architecture?

In the multitenant architecture introduced in Oracle 12c and refined in 19c, user creation has a new dimension. How do you correctly create a user in a pluggable database (PDB)?

A) CREATE USER scott IDENTIFIED BY tiger;

B) ALTER SESSION SET CONTAINER = PDB1; CREATE USER c##scott IDENTIFIED BY tiger;

C) ALTER SESSION SET CONTAINER = PDB1; CREATE USER scott IDENTIFIED BY tiger;

D) CREATE USER c##scott IDENTIFIED BY tiger CONTAINER = ALL;

Correct Answer: C

Explanation: In Oracle's multitenant environment, you must first connect to the specific pluggable database (PDB) where you want to create the user. This is accomplished by using the ALTER SESSION SET CONTAINER command. Common users, which can be used across the container database (CDB) and all PDBs, must be prefixed with C##. For a local user within a PDB, no prefix is needed.

Code:

-- First, connect to the desired pluggable database
ALTER SESSION SET CONTAINER = PDB1;

-- Then, create the user within that PDB
CREATE USER scott IDENTIFIED BY tiger;

 

2. What is the primary difference between DELETE and TRUNCATE?

Both DELETE and TRUNCATE can remove all rows from a table, but they function very differently. Which statement accurately describes their core distinction?

A) DELETE is a DDL command, while TRUNCATE is a DML command.

B) TRUNCATE can be rolled back, but DELETE cannot.

C) DELETE removes rows one by one and generates undo data, while TRUNCATE is a faster, non-recoverable operation that deallocates the table's storage.

D) TRUNCATE fires triggers, while DELETE does not.

Correct Answer: C

Explanation: DELETE is a Data Manipulation Language (DML) command that removes rows based on a WHERE clause (or all rows if no clause is specified). Each row deletion is logged in the undo tablespace, which allows the operation to be rolled back. TRUNCATE is a Data Definition Language (DDL) command that removes all rows from a table by deallocating the data pages. It is significantly faster and generates minimal undo data, making it a non-recoverable operation in the traditional sense (though some advanced recovery techniques might be possible).

Code (DELETE):

-- Removes all employees from the 'sales' department
DELETE FROM employees WHERE department = 'sales';
ROLLBACK; -- This would undo the deletion

Code (TRUNCATE):

-- Removes all rows from the 'employees' table
TRUNCATE TABLE employees;
-- A ROLLBACK command would have no effect on the truncated table

 

3. How can you identify the top 5 most CPU-intensive SQL statements?

Performance tuning is a critical DBA task. Which dynamic performance view would you query to find the SQL statements consuming the most CPU time?

A) V$SESSION

B) V$SQL

C) V$SYSSTAT

D) V$PROCESS

Correct Answer: B

Explanation: The V$SQL view contains detailed statistics for each SQL statement that has been executed. By ordering the results by CPU_TIME in descending order, you can quickly identify the queries that are placing the heaviest load on the processor.

Code:

SELECT
  sql_text,
  sql_id,
  cpu_time / 1000000 AS cpu_time_seconds,
  executions,
  elapsed_time / 1000000 AS elapsed_time_seconds
FROM
  v$sql
ORDER BY
  cpu_time DESC
FETCH FIRST 5 ROWS ONLY;

 

4. What is the purpose of the SYSTEM and SYSAUX tablespaces?

Every Oracle database has these two essential tablespaces. What are their primary functions?

A) SYSTEM stores user data, and SYSAUX stores temporary data.

B) SYSTEM contains the data dictionary, and SYSAUX is an auxiliary tablespace for additional database components.

C) SYSTEM holds undo information, and SYSAUX stores application indexes.

D) SYSTEM is for system-level triggers, and SYSAUX is for user-defined functions.

Correct Answer: B

Explanation: The SYSTEM tablespace is the most critical tablespace in an Oracle database. It houses the data dictionary, which is the metadata about the database itself (table definitions, user information, etc.). The SYSAUX tablespace was introduced in Oracle 10g as an auxiliary tablespace to offload some of the components that were previously stored in the SYSTEM tablespace, such as the Automatic Workload Repository (AWR), Oracle Enterprise Manager (OEM) repository, and other database features. This separation helps to reduce the contention on the SYSTEM tablespace.

 

5. How do you perform a cold backup of a database?

A cold backup is a fundamental backup and recovery concept. What is the correct procedure for taking a consistent, offline backup?

A) Put the database in ARCHIVELOG mode and then copy the datafiles.

B) Shut down the database cleanly and then copy all datafiles, control files, and redo log files.

C) Use RMAN to perform a backup while the database is open.

D) Export the entire database using Data Pump.

Correct Answer: B

Explanation: A cold backup, also known as an offline backup, is performed while the database is shut down. This ensures a consistent state of all database files. The process involves shutting down the database with a normal, immediate, or transactional option and then using operating system commands to copy all essential files to a backup location.

Steps:

  1. Shutdown the database:  
    SHUTDOWN IMMEDIATE;
  2. Copy files: Use operating system commands (e.g., cp in Linux/Unix, copy in Windows) to copy all datafiles, control files, and online redo log files to your backup destination.
  3. Startup the database:  
    STARTUP;

 

6. What is the role of the Listener in an Oracle environment?

The Listener is a key networking component. What is its main responsibility?

A) To execute SQL queries on behalf of the client.

B) To manage the allocation of memory in the SGA.

C) To receive incoming client connection requests and hand them off to a server process.

D) To write data from the database buffer cache to the datafiles.

Correct Answer: C

Explanation: The Oracle Listener is a server-side process that "listens" for incoming connection requests from client applications. When a request is received, the Listener establishes a connection and then hands off the communication to a dedicated or shared server process. The Listener itself does not handle the ongoing communication or SQL processing.

 

7. How would you enable Automatic Workload Repository (AWR) snapshots to be taken every 30 minutes with a retention of 15 days?

AWR is a vital performance tuning tool. How do you adjust its snapshot interval and retention period?

A) ALTER SYSTEM SET AWR_SNAPSHOT_INTERVAL = 30, AWR_RETENTION = 21600;

B) By editing the init.ora file.

C) EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval => 30, retention => 21600);

D) ALTER DATABASE SET AWR_SNAPSHOT_SETTINGS (INTERVAL => 30, RETENTION => 15);

Correct Answer: C

Explanation: The DBMS_WORKLOAD_REPOSITORY package provides procedures for managing the AWR. The MODIFY_SNAPSHOT_SETTINGS procedure allows you to change the snapshot collection interval and the retention period. The interval is specified in minutes, and the retention is also in minutes (15 days = 15 * 24 * 60 = 21600 minutes).

Code:

-- Set snapshot interval to 30 minutes and retention to 15 days (in minutes)
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval => 30, retention => 21600);

 

8. What is the most efficient way to get the total number of rows in a very large table?

You need a quick and accurate row count for a table with billions of rows. What is the best method?

A) SELECT COUNT(*) FROM large_table;

B) SELECT COUNT(1) FROM large_table;

C) Query the NUM_ROWS column in the DBA_TABLES view after gathering fresh statistics.

D) SELECT MAX(ROWID) FROM large_table;

Correct Answer: C

Explanation: Performing a COUNT(*) or COUNT(1) on a very large table can be extremely time-consuming as it requires a full table scan. A much more efficient approach is to query the NUM_ROWS column in the DBA_TABLES (or USER_TABLES, ALL_TABLES) view. This value is populated when table statistics are gathered. For an accurate count, ensure that statistics have been recently collected.

Code:

-- First, ensure table statistics are up-to-date
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'LARGE_TABLE');

-- Then, query the data dictionary
SELECT num_rows FROM dba_tables WHERE owner = 'SCHEMA_NAME' AND table_name = 'LARGE_TABLE';

 

9. You need to apply a security patch to your Oracle Home. What is the recommended utility for this task?

Oracle regularly releases patches to address security vulnerabilities and bugs. Which command-line utility is used to apply these patches?

A) sqlplus

B) opatch

C) dbca

D) rman

Correct Answer: B

Explanation: OPatch is the standard Oracle utility for applying and rolling back patches to Oracle software. It is a Java-based tool that is included with your Oracle installation. sqlplus is for interacting with the database, dbca is for creating and configuring databases, and rman is for backup and recovery.

 

10. What is the purpose of an index in an Oracle database?

Indexes are fundamental to database performance. What is their primary function?

A) To enforce data integrity through constraints.

B) To provide a faster access path to data in a table.

C) To store a backup copy of the table data.

D) To audit user access to a table.

Correct Answer: B

Explanation: An index is a data structure that is associated with a table and provides a quick lookup mechanism for finding rows with specific column values. By creating an index on one or more columns of a table, you can often dramatically improve the performance of SELECT statements with a WHERE clause, as the database can use the index to locate the data without having to scan the entire table.

Code to create an index:

-- Creates an index on the 'last_name' column of the 'employees' table
CREATE INDEX idx_emp_last_name ON employees(last_name);
Back to List