Oracle Certification
61 practice questions with correct answers and detailed explanations. Use this guide to review concepts before taking the practice exam.
The Oracle Oracle Database Administration I (1Z0-082) certification validates professional expertise in Oracle technologies. This study guide covers all 61 practice questions from our 1Z0-082 practice test, complete with correct answers and explanations to help you understand each concept thoroughly.
Review each question and explanation below, then test yourself with the full interactive practice exam to measure your readiness.
Which parameter in the initialization file controls the maximum number of processes that can connect to an Oracle database instance?
The 'processes' parameter specifies the maximum number of operating system user processes that can simultaneously connect to Oracle. This is a fundamental initialization parameter for controlling database connections.
You are managing an Oracle database and need to ensure that archived redo logs are automatically deleted after they have been backed up. Which RMAN configuration command should you use?
The ARCHIVELOG DELETION POLICY configuration in RMAN specifies when archived logs can be deleted based on whether they have been applied to standby databases or backed up. This ensures logs are retained until they are safely backed up.
What is the primary purpose of the alert log in an Oracle database?
The alert log is a crucial diagnostic file that records critical database events, errors, warnings, and significant operational messages. It is essential for troubleshooting and monitoring database health.
You need to increase the size of a tablespace that is running out of space. The tablespace contains datafiles that cannot be extended further. What is the best approach?
When existing datafiles reach their maximum size limits, the best solution is to add a new datafile to the tablespace using the ALTER TABLESPACE command. This provides additional space without affecting existing data.
Which Oracle feature allows you to temporarily suspend I/O operations to specific datafiles without bringing down the database?
Placing a tablespace in offline mode temporarily makes it unavailable while the database continues running. This is useful for maintenance on specific datafiles without shutting down the entire instance.
You are configuring automatic undo management for your Oracle database. Which initialization parameter determines the duration that undo data must be retained?
UNDO_RETENTION specifies the minimum duration (in seconds) that undo data should be retained, even if the undo tablespace space needs to be reused. It ensures consistent read queries can complete successfully.
What happens when you execute the SHUTDOWN ABORT command on an Oracle database instance?
SHUTDOWN ABORT performs an immediate termination of the instance without completing checkpoints or transactions. This can leave the database in an inconsistent state and requires recovery on the next startup.
You need to move a datafile from one disk to another without downtime. Which method is most appropriate for an online datafile move?
To move a datafile online, place the tablespace in read-only mode, move the file at the OS level, then update Oracle with the new location and return the tablespace to read-write mode. This allows the database to remain operational.
Which view would you query to determine the current size and space usage of all datafiles in the database?
DBA_DATA_FILES (with underscore) contains information about all datafiles including their current size, location, and usage. This is the correct view name for datafile information in the data dictionary.
You are setting up Oracle Net configuration. Which file is primarily responsible for defining how client connections are routed to database services?
The tnsnames.ora file defines the client-side connection definitions (net service names) that specify how to route connections to the appropriate database services. Clients use this file to locate and connect to databases.
What is the primary advantage of using Automatic Shared Memory Management (ASMM) in Oracle 11g?
ASMM automatically adjusts SGA components (buffer cache, log buffer, shared pool) based on workload, reducing manual tuning effort. However, PGA still requires manual configuration unless using Automatic Memory Management.
You need to recover a dropped table from a backup without restoring the entire database. Which Oracle feature would you use?
Flashback Drop allows recovery of a dropped table from the recycle bin without restoring backups, provided the table hasn't been purged. This is the fastest and simplest method for recovering recently dropped tables.
When configuring backup and recovery settings, which parameter specifies the location where Oracle will automatically store backup files and archive logs?
DB_RECOVERY_FILE_DEST specifies the default location for RMAN backup files, archived logs, and other recovery-related files when using the flash recovery area. Setting this enables automatic backup management.
You are analyzing wait events in your database and notice high 'db file sequential read' wait events. What does this typically indicate?
The 'db file sequential read' wait event indicates single-block reads typically from index lookups. High occurrences suggest missing indexes, poor query optimization, or inefficient execution plans that require excessive I/O operations.
Which SQL statement would you use to add a new member to an existing redo log group that currently has only one member?
The correct syntax is ALTER DATABASE ADD LOGFILE MEMBER followed by the file path and TO GROUP clause. This adds a member to an existing redo log group for redundancy and enhanced write performance.
You need to implement a backup strategy that can recover from a datafile loss within the last 5 days. What retention policy should you configure in RMAN?
The RECOVERY WINDOW retention policy specifies the time period during which you can perform point-in-time recovery. Setting it to 5 DAYS ensures you can recover to any point within the last 5 days.
What is the minimum privilege level required for a user to view information about all database objects they do not own?
The SELECT ANY TABLE system privilege allows a user to query any table in the database regardless of ownership. This is the appropriate privilege for viewing data from all tables.
You are managing character set conversion for an Oracle database. Which view would you query to determine the current database character set?
The NLS_DATABASE_PARAMETERS view (or query to the database itself via NLS_LANG) shows the current character set. You can also check DBA_PROPERTIES or the database alert log during startup to confirm the character set.
When a tablespace is created with the AUTOEXTEND OFF clause, what happens when it reaches its maximum size?
With AUTOEXTEND OFF and no additional datafiles available, the tablespace cannot accommodate new space requests. DML operations will fail with 'ORA-01653: unable to extend table' type errors.
Which Oracle background process is responsible for writing dirty buffers from the buffer cache to datafiles?
The Database Writer (DBWR) process writes modified (dirty) data blocks from the buffer cache to the datafiles. LGWR writes redo logs, PMON cleans up failed processes, and CKPT updates file headers.
You need to estimate the size of a new table before creating it. Which factor is NOT typically considered in the calculation?
Query complexity does not affect table storage size. Size estimation depends on row length, row count, block overhead, compression, and growth. The complexity of queries accessing the table is a performance consideration, not a storage one.
When configuring log archiving, what is the result of setting LOG_ARCHIVE_DEST_1 with the LOCATION parameter instead of SERVICE?
Using the LOCATION parameter directs archived logs to a file system path (local or NFS). The SERVICE parameter is used for sending logs to a remote standby database, and LOCATION is the standard choice for local archiving.
You execute a backup of your database without specifying a retention policy. Which of the following best describes the default retention behavior?
If no explicit retention policy is configured in RMAN, backups are retained indefinitely. The CONFIGURE RETENTION POLICY command sets a specific retention strategy, but without it, no automatic deletion occurs.
Which initialization parameter controls the number of database writer processes in the Oracle instance?
The DB_WRITER_PROCESSES parameter specifies the number of DBWR processes that write dirty buffers to disk. This parameter is useful on systems with multiple CPUs to improve I/O performance.
You are investigating a performance issue and suspect that the shared pool is too small. Which dynamic performance view would you query to check the amount of free memory in the shared pool?
V$SGASTAT shows detailed SGA memory statistics including free space in the shared pool. Querying this view with POOL='shared pool' reveals how much free memory is available for allocations.
Which parameter in the initialization file controls the maximum number of processes that can connect to an Oracle database instance?
The PROCESSES parameter specifies the maximum number of operating system user processes that can simultaneously connect to Oracle. This is a fundamental instance initialization parameter.
When you create a new tablespace, which statement about extent allocation is correct?
When creating a tablespace, the EXTENT MANAGEMENT clause determines how extents are allocated. Locally managed tablespaces use bitmap allocation with specified extent sizes, while dictionary-managed tablespaces use the data dictionary.
You need to audit all SELECT statements issued by a specific user. Which audit option should you enable?
The syntax 'AUDIT SELECT BY username' audits all SELECT statements executed by the specified user. The BY clause identifies the user, while BY ACCESS creates individual audit records for each statement.
What is the primary advantage of using Automatic Storage Management (ASM) in Oracle Database?
ASM simplifies storage management by providing automatic load balancing and mirroring across disk groups, reducing administrative overhead and improving I/O performance distribution.
You are configuring backup retention for RMAN. Which setting determines how long backup files are kept before they can be deleted?
RMAN RETENTION POLICY determines when backups become obsolete and eligible for deletion. It can be configured by redundancy (number of copies) or by days of recovery window.
Which dynamic performance view would you query to determine the current undo tablespace usage and transaction slots available?
V$UNDOSTAT shows undo tablespace statistics including usage metrics, while V$TRANSACTION displays active transactions. These views together provide comprehensive undo segment monitoring information.
When you encounter an 'ORA-01555: snapshot too old' error, what is the root cause?
This error occurs when a query needs to read older versions of data blocks (for read consistency) but those undo records have been overwritten. Increasing undo tablespace size or undo retention resolves this.
Which initialization parameter controls whether the database automatically writes redo log files to the archive destination?
LOG_ARCHIVE_MODE, when set to ENABLE, activates automatic archiving of redo logs. LOG_ARCHIVE_DEST_n specifies where logs are archived, but the MODE parameter controls whether archiving is active.
You need to move a datafile to a different location due to storage reconfiguration. What is the correct sequence of operations?
The correct procedure involves taking the tablespace offline, moving the physical file, using ALTER DATABASE RENAME DATAFILE to update Oracle's metadata, then bringing the tablespace back online.
Which statement about Oracle's Transparent Data Encryption (TDE) is accurate?
TDE supports both column-level and tablespace-level encryption using keys stored in a database or external wallet. Encryption/decryption is transparent to users and has minimal performance impact.
You are implementing a recovery strategy and need to test if your backups can restore the database to a specific point in time. What RMAN feature should you use?
The RMAN DUPLICATE command creates a copy of the database from backups and can be used to test point-in-time recovery without affecting the production database.
What is the purpose of the DB_RECOVERY_FILE_DEST parameter?
DB_RECOVERY_FILE_DEST specifies the Fast Recovery Area (FRA), a centralized backup storage location where Oracle automatically manages backups, archive logs, and flashback logs.
In a Data Guard configuration, what is the primary function of the standby database when using maximum availability mode?
In maximum availability mode, redo is applied synchronously to ensure zero data loss. The standby can serve as a read-only system while protecting against primary database failure.
Which parameter determines the level of detail recorded in the database audit trail?
AUDIT_TRAIL parameter specifies where audit information is written (NONE, OS, DB, DB_EXTENDED, or XML). When set to DB or higher, detailed audit records are stored in the database.
You need to estimate the size required for a new tablespace. Which approach provides the most accurate calculation?
Accurate sizing requires analyzing table and index definitions, estimating row counts, calculating storage for indexes and overhead, and reserving space for future growth.
Which Oracle tool would you use to clone a database for testing purposes while maintaining complete independence from the source database?
RMAN DUPLICATE creates a new, independent database instance from source database backups, handling all necessary configuration changes and ensuring complete separation from the original.
What happens when the Fast Recovery Area becomes full?
When the FRA is full, new backups cannot be created. If archive log destination also uses the FRA, log switches can be delayed, potentially causing the database to stall.
Which statement correctly describes the relationship between control files and database backups?
Control files are essential metadata files that RMAN includes in all backups. They record database structure, datafile locations, and backup history and must be protected.
You are configuring password policies for database users. Which parameter controls the number of days a password remains valid before expiration?
PASSWORD_LIFE_TIME (specified in a resource profile) sets the maximum number of days a password is valid. PASSWORD_GRACE_TIME provides additional login attempts after expiration before the account is locked.
In the context of Oracle Database recovery, what is the difference between MEDIA RECOVERY and CRASH RECOVERY?
MEDIA RECOVERY restores datafiles from backups and applies archived redo logs for complete recovery. CRASH RECOVERY uses online redo logs to recover from instance failures without datafile restoration.
Which view would you query to identify which sessions are blocking other sessions in the database?
V$SESSION shows active sessions, V$LOCK displays lock information, and V$PROCESS shows operating system process details. Together they identify which sessions hold locks blocking others.
What is the correct syntax to create a locally managed tablespace with automatic segment space management?
The correct syntax uses EXTENT MANAGEMENT LOCAL for locally managed tablespaces and SEGMENT SPACE MANAGEMENT AUTO for automatic segment space management (ASSM).
You need to grant the ability to create new users to a role, but without granting full DBA privileges. Which system privilege should you include in the role?
The CREATE USER system privilege specifically grants the ability to create new user accounts. This is a granular privilege that can be granted independently without DBA role permissions.
Which initialization parameter should you set to enable the Automatic Database Diagnostic Monitor (ADDM)?
ADDM is automatically enabled when AWR is enabled (via STATISTICS_LEVEL) and DIAGNOSTIC_DEST is configured. ADDM requires AWR data to analyze performance issues.
When performing an RMAN backup, what is the significance of the SECTION SIZE parameter in the BACKUP command?
SECTION SIZE enables parallelization of large datafile backups by dividing files into sections that multiple RMAN channels can process simultaneously, reducing total backup time.
You want to ensure that expired audit records are automatically purged from the audit trail. Which approach is recommended?
DBMS_AUDIT_MGMT provides procedures to configure automated audit trail purging based on retention policies, managing audit records in a database-aware manner.
You need to configure automatic undo retention for your database. Which initialization parameter controls the minimum amount of time that undo data should be retained?
UNDO_RETENTION specifies the minimum amount of time (in seconds) that undo data should be retained. This parameter works with automatic undo management to ensure undo data is available for consistent reads.
Which type of database control file backup is automatically created and maintained by Oracle when using ARCHIVELOG mode?
Oracle automatically maintains multiplexed copies of the control file as specified in the CONTROL_FILES initialization parameter. These copies ensure redundancy and protection against control file loss.
You are configuring a new database and want to enable automatic Secure Sockets Layer (SSL) encryption for database connections. Which parameter should you configure in the sqlnet.ora file?
SQLNET.ENCRYPTION_CLIENT controls whether SSL encryption is requested or required for client connections. This parameter in sqlnet.ora enables encryption at the network protocol level for Oracle Net connections.
A user reports being unable to connect to the database during peak hours. You suspect that the number of database processes has reached its maximum. Which parameter would you check first?
The PROCESSES initialization parameter specifies the maximum number of operating system processes that can simultaneously connect to the database. Reaching this limit would prevent new connections.
You need to perform a recovery operation using a backup control file that is older than the current database incarnation. Which RMAN command sequence is most appropriate?
When recovering with a backup control file, you must restore it, issue RESET DATABASE to resynchronize it with the database files, and then perform recovery with the USING BACKUP CONTROLFILE clause.
You are managing a database with multiple tablespaces. Which statement accurately describes the relationship between a tablespace and its datafiles?
A tablespace is a logical storage unit that contains one or more physical datafiles. Each datafile belongs to exactly one tablespace, but a tablespace can span multiple datafiles.
Your database is experiencing severe I/O contention on the redo log files. You decide to add an additional redo log group. What is the correct syntax to accomplish this?
The correct syntax to add a new redo log group is ALTER DATABASE ADD LOGFILE GROUP <group_number> followed by one or more file specifications. The GROUP keyword explicitly identifies the group number.
You need to implement a strategy to prevent unauthorized access to sensitive data stored in specific tables. Which Oracle security feature would be most appropriate for this requirement?
Virtual Private Database (VPD) provides fine-grained access control at the row level, allowing you to transparently enforce security policies to restrict sensitive data access based on application context or user attributes.
During a database startup, you encounter an error indicating that the database cannot find one of the required redo log files. The file was accidentally deleted. What is the most appropriate recovery action?
When a redo log file is permanently lost and cannot be recovered, you should add a replacement redo log group and drop the damaged group. This approach preserves the database without requiring a full restoration.
You are tuning database performance and notice that many queries are performing full table scans instead of using indexes. Which dynamic performance view would you query to determine if indexes exist on the frequently scanned tables?
DBA_INDEXES displays all indexes in the database, and DBA_IND_COLUMNS shows the columns indexed. These data dictionary views help identify existing indexes and their composition on specific tables.
You've reviewed all 61 questions. Take the interactive practice exam to simulate the real test environment.
▶ Start Practice Exam — Free