1Z0-006 Database Administration and Security 5 — Questions and Answers
Question 1: Which initialization parameter file stores the database configuration parameters and can be modified without restarting the instance?
- PFILE (parameter file / init.ora)
- SPFILE (server parameter file) (Correct answer)
- CONTROLFILE
- TNSNAMES.ORA
Correct answer: SPFILE (server parameter file)
The SPFILE (Server Parameter File) is a binary file that allows parameters to be changed dynamically using ALTER SYSTEM and persist across instance restarts.
Question 2: What is the role of the Oracle SMON background process?
- Monitoring session processes and cleaning up failed sessions
- Performing instance recovery at startup and coalescing free space in tablespaces (Correct answer)
- Writing redo log entries from the log buffer to redo log files
- Detecting and resolving distributed transaction failures
Correct answer: Performing instance recovery at startup and coalescing free space in tablespaces
SMON (System Monitor) performs automatic instance recovery at database startup and reclaims unused temporary space in tablespaces.
Question 3: A user receives ORA-01950: no privileges on tablespace error when trying to insert data. What is the most likely fix?
- Grant CREATE TABLE privilege to the user
- Grant a quota on the tablespace to the user using ALTER USER (Correct answer)
- Add the user to the DBA role
- Restart the database instance to reset quotas
Correct answer: Grant a quota on the tablespace to the user using ALTER USER
ORA-01950 occurs when a user has no quota assigned to a tablespace; the fix is ALTER USER username QUOTA amount ON tablespace_name.
Question 4: What is the purpose of Oracle's UNDO tablespace?
- Storing temporary sort results for large queries
- Storing before-images of data to support transaction rollback and read consistency (Correct answer)
- Storing archived redo log files for media recovery
- Storing Oracle system catalog tables and views
Correct answer: Storing before-images of data to support transaction rollback and read consistency
The UNDO tablespace stores before-images (undo records) of changed data, enabling transaction rollback, read consistency, and Flashback features.
Question 5: Which Oracle feature automatically manages the sizes of various SGA components like the buffer cache and shared pool?
- Automatic Storage Management (ASM)
- Automatic Memory Management (AMM) / Automatic Shared Memory Management (ASMM) (Correct answer)
- Oracle Managed Files (OMF)
- Automatic Workload Repository (AWR)
Correct answer: Automatic Memory Management (AMM) / Automatic Shared Memory Management (ASMM)
Automatic Memory Management (AMM) or Automatic Shared Memory Management (ASMM) dynamically redistributes memory between SGA components based on workload needs.
Question 6: In Oracle, which dictionary view would you query to see the privileges granted directly to a specific user (not through roles)?
- DBA_ROLE_PRIVS
- DBA_SYS_PRIVS (Correct answer)
- SESSION_PRIVS
- USER_TAB_PRIVS
Correct answer: DBA_SYS_PRIVS
DBA_SYS_PRIVS shows system privileges granted directly to users and roles, allowing a DBA to see which system privileges a user holds.
Question 7: What is the correct sequence of steps when Oracle performs a CHECKPOINT?
- LGWR writes log buffer → DBWR writes dirty blocks → control file and data file headers updated (Correct answer)
- DBWR writes dirty blocks → LGWR writes redo → control file and data file headers updated
- Control file updated → DBWR writes dirty blocks → LGWR flushes log buffer
- PMON cleans processes → SMON coalesces space → DBWR writes dirty blocks
Correct answer: LGWR writes log buffer → DBWR writes dirty blocks → control file and data file headers updated
During a checkpoint, LGWR first ensures redo is written, DBWR then writes dirty buffer cache blocks to data files, and finally control files and data file headers are updated with the checkpoint SCN.
1Z0-006 Database Administration and Security 5
Which initialization parameter file stores the database configuration parameters and can be modified without restarting the instance?