96 free Oracle 1Z0-062 practice questions from the CertStash pack of 382, each with the correct answer and a full explanation. The rule is the same for every exam: ten free on the product page, 96 free here, all 382 in the pack, and each step includes the one before it.
Get all 382 questions (US$39) · Download the first 10 as a PDF
Question 1
A user establishes a connection to a database instance by using an Oracle Net connection. You want to ensure the following: 1. The user account must be locked after five unsuccessful login attempts. 2. Data read per session must be limited for the user. 3. The user cannot have more than three simultaneous sessions. 4. The user must have a maximum of 10 minutes session idle time before being logged off automatically. How would you accomplish this?
Show answer and explanation
Correct answer: D. by assigning a profile to the user
Every requirement maps to a profile limit. FAILED_LOGIN_ATTEMPTS 5 locks the account after five unsuccessful logins, LOGICAL_READS_PER_SESSION caps the data blocks read in a session, SESSIONS_PER_USER 3 limits concurrent sessions, and IDLE_TIME 10 disconnects a session after 10 minutes of inactivity. Creating a profile with these limits and assigning it to the user with ALTER USER ... PROFILE enforces all four requirements together. Resource limits in a profile are enforced when the resource_limit parameter is TRUE, while password limits such as FAILED_LOGIN_ATTEMPTS are always enforced.
Why the other options are wrong
- A. A secure application role only controls when a set of privileges is enabled; it cannot lock accounts or limit sessions, reads or idle time.
- B. Database Resource Manager allocates resources among consumer groups but cannot lock an account after failed login attempts.
- C. Oracle Label Security provides row-level access control based on data labels, not login or session resource limits.
Question 2
As a user of the ORCL database, you establish a database link to the remote HQ database such that all users in the ORCL database may access tables only from the SCOTT schema in the HQ database. SCOTT’s password is TIGER. The service name "HQ" is used to connect to the remote HQ database. Which command would you execute to create the database link?
Show answer and explanation
Correct answer: C. CREATE PUBLIC DATABASE LINK HQ CONNECT TO scott IDENTIFIED BY tiger USING 'HQ';
For every user in the ORCL database to use the link, it must be created as a PUBLIC database link; a link created without PUBLIC is private and available only to its owner. The CONNECT TO scott IDENTIFIED BY tiger clause makes it a fixed user link, so every session using it connects to the HQ database as SCOTT and can therefore access only the objects SCOTT can reach, such as the tables in the SCOTT schema. USING 'HQ' names the Oracle Net service used to reach the remote database. CREATE PUBLIC DATABASE LINK HQ CONNECT TO scott IDENTIFIED BY tiger USING 'HQ' meets every requirement.
Why the other options are wrong
- A. This creates a private connected user link, so only its owner can use it and each session connects as the local user rather than as SCOTT.
- B. A CURRENT_USER link is private here and connects as the current global user, not as SCOTT.
- D. Without the PUBLIC keyword the link is private and only the user who created it can use it.
Question 3
What happens if a maintenance window closes before a job that collects optimizer statistics completes?
Show answer and explanation
Correct answer: D. The job is terminated and statistics for the remaining objects are collected the next time the maintenance window opens.
The automatic optimizer statistics collection job runs inside the maintenance windows, and its stop_on_window_close attribute defaults to TRUE. When the window closes before the job finishes, the Scheduler stops the job. Statistics already gathered for the objects processed up to that point are kept, and the objects that were not yet processed are handled when the job runs in the next maintenance window. Because the job prioritizes the objects that most need new statistics, the most important objects are usually processed first.
Why the other options are wrong
- A. Statistics already gathered for completed objects are saved; only the remaining objects are left for the next window.
- B. Whether statistics are published depends on the PUBLISH preference, not on the window closing, and by default they are published.
- C. With the default stop_on_window_close setting of TRUE, the job is stopped when the window closes rather than running to completion.
Question 4
You plan to create a database by using the Database Configuration Assistant (DBCA), with the following specifications: Applications will connect to the database via a middle tier. The number of concurrent user connections will be high. The database will have mixed workload, with the execution of complex BI queries scheduled at night. Which DBCA option must you choose to create the database?
Show answer and explanation
Correct answer: C. a General Purpose database template, with the shared server mode option and Automatic Memory Management (AMM) enabled
The General Purpose (Transaction Processing) template suits a mixed workload of daytime transactions and scheduled BI queries, whereas the Data Warehouse template is tuned for workloads dominated by large queries. Because applications connect through a middle tier with a high number of concurrent connections, shared server mode lets a small pool of shared server processes serve many sessions and reduces process and memory overhead. Enabling Automatic Memory Management lets the instance move memory between the SGA and PGA as the workload shifts, for example toward the PGA for sorts and hash joins in the night-time BI queries.
Why the other options are wrong
- A. Default memory allocation with the default connection mode does not provide shared server support for the large number of concurrent middle-tier connections.
- B. The Data Warehouse template targets query-dominated workloads, and dedicated server mode scales poorly for a very high number of concurrent connections.
- D. A default configuration does not deliberately select shared server mode or AMM, so it does not address the stated requirements.
Question 5
Which two statements are true about the logical storage structure of an Oracle database? (Choose two.)
Show answer and explanation
Correct answer: D, E. It is possible to have tablespaces of different block sizes; A data block is the smallest unit of I/O in data files.
A tablespace can use a block size different from the database standard block size by specifying the BLOCKSIZE clause of CREATE TABLESPACE, provided the matching DB_nK_CACHE_SIZE parameter is set, so tablespaces of different block sizes can coexist in one database. The data block is the smallest logical unit of storage in an Oracle database, and it is the smallest unit the database reads from or writes to a data file, since Oracle requests I/O in whole data blocks rather than individual rows or bytes.
Why the other options are wrong
- A. An extent consists of logically contiguous data blocks, but those blocks need not be physically contiguous on disk.
- B. An extent belongs to exactly one segment; a segment is made up of one or more extents, not the other way around.
- C. A data block is a multiple of the operating system block size and can span several OS blocks.
Question 6
Which two statements correctly describe the relationship between data files and logical database structures? (Choose two.)
Show answer and explanation
Correct answer: B, C. A data file can belong to only one tablespace; An extent cannot span data files.
A data file is created for a specific tablespace and belongs to that one tablespace only, although a tablespace can contain many data files. An extent is a set of logically contiguous data blocks allocated within a single data file, so an extent can never span data files. When a segment grows, the database can allocate its additional extents in any data file of the tablespace, which is how a segment, unlike an extent, can be spread across several data files.
Why the other options are wrong
- A. A segment can have extents in different data files of the same tablespace, so it can span data files.
- D. The Oracle data block size is a multiple of the OS block size and is not expected to equal it; common settings such as 8 KB exceed typical OS block sizes.
Question 7
Which statement is true about the Log Writer process?
Show answer and explanation
Correct answer: D. It writes when a user commits a transaction.
When a user commits a transaction, LGWR writes the commit record and the remaining redo entries for that transaction from the redo log buffer to the current online redo log group, and the commit is not acknowledged until this write completes. This fast commit mechanism guarantees durability without having to write the changed data blocks immediately. LGWR also writes when the redo log buffer is one-third full, every three seconds, and before DBWn writes modified buffers whose redo has not yet been written.
Why the other options are wrong
- A. CKPT does not signal LGWR to write; CKPT updates the control file and data file headers and signals DBWn.
- B. LGWR writes concurrently only to the members of the current redo log group, not to all multiplexed groups.
- C. The order is the reverse: redo must be written by LGWR before DBWn writes the corresponding dirty buffers (write-ahead logging).
Question 8
The ORCL database is configured to support shared server mode. You want to ensure that a user connecting remotely to the database instance has a one-to-one ratio between client and server processes. Which connection method guarantees that this requirement is met?
Show answer and explanation
Correct answer: D. connecting by using the local naming method with the server = dedicated parameter set in the tnsnames.ora file for the net service
When shared server is configured, clients are routed to dispatchers by default. To request a dedicated server process, giving a one-to-one ratio between the client and its server process, the connect descriptor must contain (SERVER=DEDICATED). Defining a net service name in tnsnames.ora (local naming) with SERVER=DEDICATED in the CONNECT_DATA section guarantees that every connection made through that name is handled by its own dedicated server process, regardless of the shared server configuration.
Why the other options are wrong
- A. External naming only resolves the connect descriptor; without SERVER=DEDICATED the connection uses shared server.
- B. An easy connect string uses the default server type, which is shared server here, unless a dedicated server is explicitly requested.
- C. A service created with dbms_service.create_service does not by itself specify dedicated server connections.
- E. Directory naming only resolves the connect descriptor; it does not force a dedicated server unless SERVER=DEDICATED is included.
Question 9
Which two tasks can be performed on an external table? (Choose two.)
Show answer and explanation
Correct answer: D, E. creating a public synonym; creating a view
An external table is a read-only table whose data resides in operating system files outside the database. You can query it with SELECT, join it and sort it, and you can create views and synonyms, including public synonyms, on it just as you can for ordinary tables. However, no DML operations (UPDATE, INSERT or DELETE) can be performed on an external table, and no indexes can be created on it, because the database does not manage its underlying data.
Why the other options are wrong
- A. In Oracle Database 12c Release 1 an external table cannot be partitioned; it is defined as a single object over its external data files.
- B. No indexes of any kind, visible or invisible, can be created on an external table.
- C. External tables are read-only, so UPDATE, INSERT and DELETE statements are not allowed.
Question 10
Which three statements are true about using flashback database in a multitenant container database (CDB)? (Choose three.)
Show answer and explanation
Correct answer: A, B, D. The root container can be flashed back without flashing back the pluggable databases (PDBs); To enable flashback database, the CDB must be mounted; The DB_FLASHBACK_RETENTION_TARGET parameter must be set to enable flashback of the CDB.
Flashback Database is configured and run at the CDB level from the root container. The FLASHBACK DATABASE command is issued in the root and flashes back the root container itself, with no separate flashback operation performed on each individual PDB, which is why the root can be flashed back without flashing back the PDBs one by one. To enable the feature, the CDB is shut down and started in MOUNT mode before ALTER DATABASE FLASHBACK ON is issued. The db_flashback_retention_target parameter must have a value (1440 minutes by default) because it defines how far back the flashback logs allow the CDB to be rewound. The target can be a point in time, an SCN or a restore point.
Why the other options are wrong
- C. Flashback Database rewinds the CDB as a whole; a single PDB is returned to an earlier time with PDB point-in-time recovery instead.
- E. FLASHBACK DATABASE also accepts a restore point as its target, not only a time or an SCN.
Question 11
The user SCOTT owns the CUST table that is placed in the SALES tablespace. The user SCOTT opens a session and executes commands as follows: SQL> INSERT INTO cust VALUES(101, 'JACK'); 1 row created. SQL> INSERT INTO cust VALUES(102, 'SMITH'); 1 row created. As a DBA, you execute the following command from another session: ALTER TABLESPACE sales READ ONLY; Which statement is true regarding the effect of this command on the transaction in Scott's session?
Show answer and explanation
Correct answer: D. The command hangs until all transactions on the objects in the tablespace commit or rollback, and then the tablespace is placed in read-only mode.
ALTER TABLESPACE sales READ ONLY places the tablespace in a transitional read-only mode and waits for existing transactions to complete. Scott's uncommitted inserts into CUST are neither rolled back nor terminated; while the command waits, no further DML against the tablespace is allowed except rollback of those existing transactions. Once Scott commits or rolls back, and any other pending transactions finish, the statement completes and the tablespace becomes read-only.
Why the other options are wrong
- A. The command does not fail when transactions are pending; it waits for them to finish.
- B. Oracle never rolls back another session's transaction to make a tablespace read-only; it waits for the transaction to end.
- C. While the tablespace is in transitional read-only mode, Scott cannot run further DML on it; only commit or rollback of the existing work is possible.
Question 12
Which three resources might be prioritized between competing pluggable databases when creating a multitenant container database plan (CDB plan) using Oracle Database Resource Manager? (Choose three.)
Show answer and explanation
Correct answer: C, D, E. Parallel server limit; CPU; Exadata I/O
A CDB resource plan uses a directive for each PDB to allocate resources between competing PDBs. Each directive sets shares and a utilization limit that govern CPU and a parallel_server_limit that caps the parallel execution servers the PDB can use, and on Exadata the same shares also drive I/O allocation through I/O Resource Management. CPU, parallel servers and Exadata I/O are therefore the resources a CDB plan can prioritize among PDBs.
Why the other options are wrong
- A. Undo limits are consumer group directives within a PDB plan, not resources allocated between PDBs by a CDB plan.
- B. Idle time limits are set in consumer group directives, not in CDB plan directives for PDBs.
- F. Resource Manager does not manage I/O to a local file system; its I/O management applies to Exadata storage.
Question 13
Which two statements are true about Oracle Managed Files (OMF)? (Choose two.)
Show answer and explanation
Correct answer: D, E. OMF automatically creates unique file names for tablespaces and control files; OMF may affect the location of the redo log files and archived log files.
With Oracle Managed Files, you set parameters such as db_create_file_dest, db_create_online_log_dest_n and db_recovery_file_dest, and the database creates, names and deletes the files itself. It automatically generates unique names for the data files of tablespaces, for control files and for other file types. Because these parameters also determine where online redo log files are created and, through the fast recovery area, where archived redo logs are written, OMF can affect the location of redo log and archived log files.
Why the other options are wrong
- A. Oracle-managed files can coexist with user-named files in the same database.
- B. The directories named in OMF parameters must already exist; OMF does not create them.
- C. OMF works with file systems and ASM disk groups but not with raw devices.
Question 14
Which three activities are supported by the Data Recovery Advisor? (Choose three.)
Show answer and explanation
Correct answer: A, B, E. Advising on block checksum failures; Advising on inaccessible control files; Advising on invalid block header field values
Data Recovery Advisor diagnoses failures of persistent data and recommends repairs. It covers components that are not accessible, such as control files or data files that are missing, have incorrect permissions or have been taken offline, and physical corruptions such as block checksum failures and invalid block header field values. It also handles inconsistencies between files and I/O failures, and it presents manual or automatic repair options through RMAN commands such as ADVISE FAILURE and REPAIR FAILURE.
Why the other options are wrong
- C. The block change tracking file is not a target of Data Recovery Advisor; losing it only disables change tracking and does not affect recoverability.
- D. Password file problems fall outside the persistent data failures that Data Recovery Advisor diagnoses.
Question 15
Your multitenant container database (CDB) contains some pluggable databases (PDBs), you execute the following command in the root container: SQL> CREATE USER c##a_admin IDENTIFIED BY password DEFAULT TABLESPACE data_ts QUOTA 100M ON test_ts QUOTA 500K ON data_ts TEMPORARY TABLESPACE temp_ts PROFILE hr_profile; Which two statements are true? (Choose two.)
Show answer and explanation
Correct answer: C, D. The command will create a common user whose description is contained in the root and each PDB; The schema for the common user C##A_ADMIN can be different in each container.
The exhibit creates C##A_ADMIN from the root with DEFAULT TABLESPACE data_ts, quotas on test_ts and data_ts, TEMPORARY TABLESPACE temp_ts and PROFILE hr_profile. In the root, CONTAINER=ALL is the default, so the statement creates a common user whose description exists in the root and in every PDB. A common user has the same identity in every container, but its schema in each container is separate, so the objects it owns in one PDB can differ entirely from those it owns in another.
Why the other options are wrong
- A. Each container holds its own copy of the user's schema, so objects created in one PDB are not shared with other PDBs.
- B. temp_ts is assigned in every container, so the user can use it in each PDB, not only in the root.
- E. When connected to the root, CREATE USER defaults to CONTAINER=ALL, so the C## user is created in all containers.
Question 16
Which two statements are true when row archival management is enabled? (Choose two.)
Show answer and explanation
Correct answer: C, D. The ROW ARCHIVAL VISIBILITY session parameter defaults to active rows only; The ORA_ARCHIVE_STATE column is visible if referenced in the select list of a query.
Enabling ROW ARCHIVAL on a table adds the hidden column ORA_ARCHIVE_STATE, which is set to '0' for active rows. The ROW ARCHIVAL VISIBILITY session parameter defaults to ACTIVE, so by default queries return only active rows (state '0') until the session sets it to ALL. Because ORA_ARCHIVE_STATE is a hidden column, it does not appear in SELECT * output or in DESCRIBE, but it becomes visible when it is named explicitly in the select list of a query. The default of active rows only and the column appearing when referenced in the select list are therefore both true.
Why the other options are wrong
- A. ROW ARCHIVAL VISIBILITY controls which rows are returned, not whether the ORA_ARCHIVE_STATE column is displayed.
- B. Row archival itself does not use activity tracking columns, which belong to Heat Map and Automatic Data Optimization; a row's state is set by an ordinary UPDATE of ORA_ARCHIVE_STATE.
- E. Oracle never changes ORA_ARCHIVE_STATE automatically; archiving a row requires an explicit update.
Question 17
Identify two correct statements about multitenant architectures.
Show answer and explanation
Correct answer: B, E. Multiple pluggable databases (PDBs) share certain multitenant container database (CDB) resources; Patches are always applied at the CDB level.
All PDBs in a CDB share the resources of the container: a single instance with its SGA and background processes, the root's control files and online redo logs, and the same Oracle Database software, while Resource Manager can divide CPU and other resources between them. Because the PDBs run on the CDB's software, patches are applied at the CDB level, and one patching operation covers every PDB in the container. A single PDB is patched by moving it to a CDB that runs the patched software.
Why the other options are wrong
- A. The multitenant architecture works with single-instance databases as well as with Real Application Clusters.
- C. Resources are shared by the PDBs within a CDB, not by multiple CDBs.
- D. A PDB can be plugged into and opened by only one CDB at a time.
- F. In the Oracle Database 12c Release 1 multitenant architecture, undo is managed by the root, with one undo tablespace per instance shared by all PDBs.
Question 18
You want to reduce fragmentation and reclaim unused space for the SALES table but not its dependent objects. During this operation, you want to ensure the following: • Long-running queries are not affected. • No extra space is used. • Data manipulation language (DML) operations on the table succeed at all times throughout the process. • Unused space is reclaimed both above and below the high water mark. Which ALTER TABLE option would you recommend?
Show answer and explanation
Correct answer: C. SHRINK SPACE COMPACT
A shrink operation compacts rows toward the start of the segment in place, so it needs no extra space and works on free space below the high water mark as well as above it, unlike DEALLOCATE UNUSED. Omitting CASCADE limits it to the SALES table without touching dependent objects. The COMPACT option performs only the compaction phase and does not move the high water mark, so DML on the table succeeds throughout and long-running queries are not affected. The space can be released later with a plain SHRINK SPACE.
Why the other options are wrong
- A. DEALLOCATE UNUSED releases only space above the high water mark and does nothing about fragmentation below it.
- B. CASCADE also shrinks dependent objects such as indexes, which the requirement excludes.
- D. Enabling basic compression does not reclaim existing free space and compresses only data loaded afterward by direct path.
Question 19
You have altered a non-unique index to be invisible to determine if queries execute within an acceptable response time without using this index. Which two are possible if table updates are performed which affect the invisible index columns? (Choose two.)
Show answer and explanation
Correct answer: A, E. The index remains invisible; The index is updated by the DML on the table.
Making an index invisible only hides it from the optimizer; the index stays valid and remains invisible until ALTER INDEX ... VISIBLE is issued. Invisible indexes are maintained by DML like any other index, so inserts, updates and deletes that affect the indexed columns keep the index current. This allows the index to be made visible again immediately if queries do not perform acceptably without it.
Why the other options are wrong
- B. Unlike an unusable index, an invisible index is still updated by DML on the table.
- C. DML does not change index visibility; the index stays invisible until it is altered.
- D. Making an index invisible does not make it unusable, and DML keeps it maintained.
Question 20
You notice a performance change in your production Oracle database and you want to know which change has made this performance difference. You generate the Compare Period Automatic Database Diagnostic Monitor (ADDM) report for further investigation. Which three findings would you get from the report? (Choose three.)
Show answer and explanation
Correct answer: A, B, D. It detects any configuration change that caused a performance difference in both time periods; It identifies any workload change that caused a performance difference in both time periods; It shows the resource usage for CPU, memory, and I/O in both time periods.
Compare Period ADDM performs a full ADDM analysis across two AWR periods, a base period and a comparison period, to explain why performance differs between them. It identifies causes, namely configuration changes such as altered parameter settings and workload changes such as new or different SQL, and it measures effects, including the resource usage for CPU, memory and I/O in each period. It then correlates the causes with the effects and makes recommendations with their quantified impact.
Why the other options are wrong
- C. Listing the top wait events belongs to a standard AWR or ADDM report, not to the change-focused findings of Compare Period ADDM.
- E. Differences in memory pool sizes are shown in the AWR Compare Periods report, not among the Compare Period ADDM findings.
- F. Compare Period ADDM does not report on optimizer statistics collection in the two periods.
Question 21
Which three statements are true about the working of system privileges in a multitenant container database (CDB) that has pluggable databases (PDBs)? (Choose three.)
Show answer and explanation
Correct answer: A, C, E. System privileges apply only to the PDB in which they are used; The granter of system privileges must possess the set container privilege; System privileges with the WITH GRANT OPTION CONTAINER=ALL clause must be granted to a common user before the common user can grant privileges to other users.
In a CDB, a system privilege is exercised only in the container where the session is working, so even a commonly granted privilege lets a user act only in its current PDB. Granting privileges commonly requires the grantor to be a common user connected to the root that holds the SET CONTAINER privilege granted commonly. In addition, a common user can pass a system privilege on to other users commonly only after receiving that privilege itself with CONTAINER=ALL and the option to grant it.
Why the other options are wrong
- B. A local user holding a system privilege such as SELECT ANY TABLE can use it on a common user's objects within that PDB.
- D. Privileges are exercised only in the current container, so a common user must switch or connect to another PDB to use privileges there.
Question 22
Which two statements are true about the Oracle Direct Network File system (DNFS)? (Choose two.)
Show answer and explanation
Correct answer: C, E. Oracle Disk Manager can manage NFS on its own, without using the operating system kernel NFS driver; Direct NFS can load-balance I/O traffic across multiple network adapters.
Direct NFS Client is built into the database kernel and is enabled by linking in the Direct NFS Oracle Disk Manager (ODM) library. Through ODM, the database performs NFS I/O itself instead of going through the operating system kernel NFS driver. Direct NFS can also use multiple network paths to an NFS server, defined in the oranfstab file, and load-balance I/O traffic across several network adapters, which improves both throughput and availability.
Why the other options are wrong
- A. Direct NFS performs direct I/O that bypasses the operating system file system cache.
- B. The NFS file systems must still be mounted through a regular kernel NFS mount before Direct NFS Client can use them.
- D. Direct NFS is part of the database kernel and is available on all supported platforms, including Windows.
Question 23
A redaction policy was added to the SAL column of the SCOTT.EMP table: All users have their default set of system privileges. For which three situations will data not be redacted? (Choose three.)
Show answer and explanation
Correct answer: A, B, C. SYS sessions, regardless of the roles that are set in the session; SYSTEM sessions, regardless of the roles that are set in the session; SCOTT sessions, only if the MGR role is set in the session
The policy in the exhibit redacts the SAL column of SCOTT.EMP whenever SYS_CONTEXT('SYS_SESSION_ROLES', 'MGR') returns 'FALSE', that is, whenever the MGR role is not enabled in the current session. Oracle Data Redaction does not apply to SYS or SYSTEM, so their sessions always see the actual values, whatever roles are set. For SCOTT, the SYS_SESSION_ROLES namespace reports the roles currently enabled in the session, so SAL is returned unredacted only when the MGR role is set in the session.
Why the other options are wrong
- D. A grant alone is not enough: if MGR is not enabled in the session, for example because it is not a default role or was disabled with SET ROLE, SYS_SESSION_ROLES reports FALSE and SAL is redacted.
- E. Owning the table gives no exemption, because redaction is bypassed only by SYS, SYSTEM, SYSDBA sessions and users with the EXEMPT REDACTION POLICY privilege.
- F. SYSTEM sessions see unredacted data whether or not MGR is set, so the MGR condition does not apply to SYSTEM.
Question 24
Examine the structure of the sales table, which is stored in a locally managed tablespace with Automatic Segment Space Management (ASSM) enabled. You want to perform online segment shrink to reclaim fragmented free space below the high water mark. What should you ensure before the start of the operation?
Show answer and explanation
Correct answer: A. Row movement is enabled.
The SALES table (PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD and AMOUNT_SOLD) resides in a locally managed ASSM tablespace, so it is eligible for ALTER TABLE ... SHRINK SPACE. Shrinking compacts the segment by moving rows into free space lower in the segment, which changes their ROWIDs. For that reason row movement must be enabled first with ALTER TABLE sales ENABLE ROW MOVEMENT; otherwise the shrink statement fails.
Why the other options are wrong
- B. Referential integrity constraints do not need to be disabled for a segment shrink.
- C. Segment shrink is an online operation, so queries can continue to run against the table.
- D. Shrink compacts rows within the existing segment and does not need extra space equal to the segment size.
- E. DML can continue during most of the shrink operation, so pending transactions do not have to be absent beforehand.
Question 25
You execute the following piece of code with appropriate privileges: User SCOTT has been granted the CREATE SESSION privilege and the MGR role. Which two statements are true when a session logged in as SCOTT queries the SAL column in the view and the table? (Choose two.)
Show answer and explanation
Correct answer: A, C. Data is redacted for the EMP.SAL column only if the SCOTT session does not have the MGR role set; Data is never redacted for the EMP_V.SAL column.
The first policy, SCOTT_EMP, applies DBMS_REDACT.FULL to the SAL column of SCOTT.EMP with the expression SYS_CONTEXT('SYS_SESSION_ROLES','MGR') = 'FALSE', so salary values from the table are fully redacted only when the MGR role is not enabled in the session. The view EMP_V selects all columns from EMP and would normally carry the table's redaction, but the second policy, SCOTT_EMP_V, defines DBMS_REDACT.NONE on EMP_V.SAL. A policy created on the view takes precedence for that column, and NONE applies no redaction, so SAL queried through EMP_V is never redacted, whether or not MGR is set.
Why the other options are wrong
- B. The expression is true when MGR is not set, so table salaries are redacted when the role is absent, not when it is present.
- D. The NONE policy on EMP_V applies no redaction whether or not the MGR role is set.
- E. The NONE policy on EMP_V overrides the table's policy, so the view shows salaries even without the MGR role.
Question 26
Examine the parameters for a database instance: Your database has three undo tablespaces and the default undo tablespace is not autoextensible. Resumable space allocation is not enabled for any sessions in the database instance. What is the effect on new transactions when all undo space in the default undo tablespace is in use by active transactions?
Show answer and explanation
Correct answer: B. Transactions fail.
The exhibit shows undo_management AUTO, undo_tablespace UNDOTBS1, undo_retention 900 and temp_undo_enabled TRUE. An instance uses only its one active undo tablespace, so the other two undo tablespaces are not used. Because UNDOTBS1 is not autoextensible, all of its space is held by active transactions, and resumable space allocation is not enabled, new transactions that cannot allocate undo fail with an error such as ORA-30036. temp_undo_enabled affects only temporary tables and does not relieve permanent undo.
Why the other options are wrong
- A. The SYSTEM undo segment is used only for changes to objects in the SYSTEM tablespace, not as overflow for user transactions.
- C. Without resumable space allocation enabled, transactions do not wait for space and instead fail.
- D. Undo for permanent tables is never written to a temporary tablespace; temporary undo applies only to temporary tables.
Question 27
What is the result of executing a TRUNCATE TABLE command on a table that has Flashback Archiving enabled?
Show answer and explanation
Correct answer: C. The rows in the table are archived, and then truncated.
TRUNCATE TABLE is a supported DDL operation on a table that has Flashback Data Archive enabled. Before the rows are removed from the base table, they are recorded in the table's history in the flashback archive, so the historical data is not lost. The table is then empty, but a Flashback Query such as SELECT ... AS OF TIMESTAMP for a time before the truncate still returns the old rows from the archive, and they can be reinserted if required.
Why the other options are wrong
- A. TRUNCATE is permitted on history-tracked tables, so no invalid DDL error is raised.
- B. The truncated rows are preserved in the flashback archive, so they are archived before removal.
- D. The archived history is kept according to the flashback archive retention period and is not truncated along with the table.
Question 28
What should you do to ensure that a job stores minimal job metadata and runtime data on disk, and uses only existing PL/SQL programs?
Show answer and explanation
Correct answer: B. Create a lightweight job.
Lightweight jobs are designed for workloads that create many short jobs quickly. They store minimal metadata and runtime data on disk, do not create a full database object, and are faster to create and drop than regular jobs. A lightweight job must reference an existing program (a PL/SQL block or stored procedure program) rather than defining its own action inline, which matches the requirement to use only existing PL/SQL programs.
Why the other options are wrong
- A. An event-based job starts in response to an event but stores the same metadata on disk as a regular job.
- C. A job class groups jobs for resource allocation and logging and does not reduce stored job metadata.
- D. A job array is a way to submit many jobs in one call, not a job type that reduces stored metadata.
Question 29
Your database is open and the LISTENER listener is running. You stopped the wrong listener LISTENER by issuing the following command: lsnrctl > STOP What happens to the sessions that are presently connected to the database instance?
Show answer and explanation
Correct answer: B. They are not affected and continue to function normally.
The listener takes part only while a connection is being established: it receives the client's request and hands the connection off to a server process or dispatcher. Once the session exists, the client communicates directly with that server process, so stopping the listener has no effect on existing sessions, which continue to work normally. The only consequence is that new connections through that listener are refused until it is started again.
Why the other options are wrong
- A. Existing sessions are not limited to queries; they keep full functionality.
- C. Stopping the listener neither terminates existing sessions nor rolls back their transactions.
- D. Established sessions do not depend on the listener and keep working without interruption.
Question 30
You want to schedule a job to rebuild a table's indexes after a bulk insert, which must be scheduled as soon as a file containing data arrives on the system. What would you do to accomplish this?
Show answer and explanation
Correct answer: A. Create a file watcher and an event-based job for bulk insert and then create another job to rebuild indexes on the table.
A Scheduler file watcher monitors a directory and raises a file arrival event when the expected file appears. An event-based job subscribed to that event starts immediately and performs the bulk insert from the new file. A second job then rebuilds the table's indexes after the load, for example started by the job-completion event of the bulk insert job, so the rebuild always follows the insert.
Why the other options are wrong
- B. A file watcher only raises an event; without an event-based job consuming it, nothing performs the bulk insert.
- C. A job array only submits several jobs in one call and does not react to a file arriving.
- D. Without a file watcher, no file arrival event is ever raised for an event-based job to respond to.
Question 31
In order to exploit some new storage tiers that have been provisioned by a storage administrator, the partitions of a large heap table must be moved to other tablespaces in your Oracle 12c database. Both local and global partitioned B-tree indexes are defined on the table. A high volume of transactions access the table during the day and a medium volume of transactions access it at night and during weekends. Minimal disruption to availability is required. Which three statements are true about this requirement? (Choose three.)
Show answer and explanation
Correct answer: A, C, D. The partitions can be moved online to new tablespaces; The partitions can be compressed in the same tablespaces; The partitions can be compressed in the new tablespaces.
Oracle Database 12c supports ALTER TABLE ... MOVE PARTITION ... ONLINE, which relocates a partition to a new tablespace while DML on the table continues, meeting the minimal disruption requirement for a busy table. The MOVE PARTITION clause also accepts compression attributes, so a partition can be compressed while it is moved to a new tablespace, or compressed by moving it within its current tablespace. Using the UPDATE INDEXES clause, or the ONLINE clause, keeps both local and global indexes usable during the operation.
Why the other options are wrong
- B. Global indexes are maintained automatically with ONLINE or UPDATE INDEXES, so no manual rebuild is required.
- E. Local index partitions are maintained automatically with ONLINE or UPDATE INDEXES, so they do not have to be rebuilt manually.
Question 32
Which statement is true regarding the startup of a database instance?
Show answer and explanation
Correct answer: E. Instance recovery is not required if the database instance was shut down by using SHUTDOWN IMMEDIATE.
SHUTDOWN IMMEDIATE rolls back active transactions, disconnects users and performs a checkpoint that writes all dirty buffers to the data files before closing the database. The data files and control files are therefore consistent at shutdown, so the next startup opens the database without needing instance recovery. By contrast, SHUTDOWN ABORT leaves the database inconsistent, and SMON automatically performs instance recovery at the next startup.
Why the other options are wrong
- A. After SHUTDOWN ABORT, the next startup performs automatic instance recovery; no manual media recovery is needed.
- B. With SHUTDOWN IMMEDIATE, uncommitted transactions are rolled back during the shutdown, not during the next startup.
- C. Startup after an abort includes instance recovery (roll forward and rollback), while startup after an immediate shutdown does not.
- D. Media recovery is needed only after media failure or when restoring files, not after either shutdown mode.
Question 33
Which two statements are true concerning dropping a pluggable database (PDB)? (Choose two.)
Show answer and explanation
Correct answer: B, C. The PDB must be in mount state; The PDB must be unplugged.
A PDB cannot be dropped while it is open. DROP PLUGGABLE DATABASE requires the PDB to be closed, in the mounted state, or to have already been unplugged with ALTER PLUGGABLE DATABASE ... UNPLUG INTO, in which case dropping it removes it from the CDB while its XML file and data files remain available. By default the drop uses KEEP DATAFILES, and INCLUDING DATAFILES is specified only when the data files should also be deleted.
Why the other options are wrong
- A. A PDB must be closed (mounted) or unplugged to be dropped; it cannot be open in read-only mode.
- D. The data files are removed only with INCLUDING DATAFILES; the default KEEP DATAFILES leaves them on disk.
- E. A PDB unplugged before being dropped with KEEP DATAFILES can be plugged back into a CDB using its XML file.
Question 34
Which two are true concerning a multitenant container database with three pluggable databases? (Choose two.)
Show answer and explanation
Correct answer: C, D. The pluggable databases reduce administration effort; The pluggable databases are patched together.
Consolidating three databases as PDBs in one CDB means there is only one instance, one set of background processes and one Oracle home to manage, so tasks such as backups, memory configuration and monitoring can be performed once for the whole CDB, reducing administration effort. Because all PDBs run on the software of the CDB, patching or upgrading the CDB patches all of its PDBs together, which avoids patching each database separately.
Why the other options are wrong
- A. Many tasks, such as backup, patching and instance management, are performed once at the CDB level rather than per PDB.
- B. Patching the CDB once patches all PDBs, so the multitenant model reduces patching time.
- E. Besides consolidation, PDBs support rapid provisioning, cloning and patching or upgrading by unplug and plug.
Question 35
You executed a DROP USER CASCADE on an Oracle 11g release 1 database and immediately realized that you forgot to copy the OCA.EXAM_RESULTS table to the OCP schema. The RECYCLE_BIN was enabled before the DROP USER was executed and the OCP user has been granted the FLASHBACK ANY TABLE system privilege. What is the quickest way to recover the contents of the OCA.EXAM_RESULTS table to the OCP schema?
Show answer and explanation
Correct answer: C. Recover the table using Automated Tablespace Point In Time Recovery.
Dropping a user with DROP USER CASCADE purges all of that user's objects from the recycle bin, so OCA.EXAM_RESULTS cannot be restored with FLASHBACK TABLE ... TO BEFORE DROP even though the recycle bin was enabled. The table has to be recovered from backups, and the quickest method is fully automated tablespace point-in-time recovery, in which RMAN creates and manages the auxiliary instance itself, recovers the tablespace to a time just before the drop and brings it back without affecting the rest of the database. The recovered table can then be copied into the OCP schema.
Why the other options are wrong
- A. The dropped user's objects were purged from the recycle bin, so FLASHBACK TABLE has nothing to restore.
- B. Traditional TSPITR requires the DBA to prepare and manage the auxiliary instance manually, which takes longer than the automated method.
- D. Database point-in-time recovery rewinds the entire database and loses all later changes, which is far more disruptive than recovering one tablespace.
- E. The dropped user's objects were purged from the recycle bin, so FLASHBACK TABLE fails regardless of the FLASHBACK ANY TABLE privilege.
Question 36
Your database is open and the listener LISTENER is up. You issue the command: LSNRCTL> RELOAD What is the effect of reload on sessions that were originally established by listener?
Show answer and explanation
Correct answer: C. The sessions are not affected and continue to function normally.
The LSNRCTL RELOAD command makes the running listener reread listener.ora and apply new settings without stopping. The listener's role ends once it has handed a connection to a server process, so existing sessions communicate directly with their server processes and are not affected by a reload. They continue to function normally. Dynamically registered services are registered again by the database instance shortly after the reload, which affects only new connection requests.
Why the other options are wrong
- A. Existing sessions are independent of the listener, so none are disconnected, whether their services were registered statically or dynamically.
- B. Established sessions do not depend on the listener and are not blocked while services re-register.
- D. RELOAD does not terminate any sessions or roll back any transactions.
Question 37
You want to flash back a test database by five hours. You issue this command: SQL > FLASHBACK DATABASE TO TIMESTAMP (SYSDATE - 5/24); Which two statements are true about this flashback scenario? (Choose two.)
Show answer and explanation
Correct answer: B, E. The database must be MOUNTED for the flashback to succeed; The database must be opened with the RESETLOGS option after the flashback is complete.
FLASHBACK DATABASE can run only when the database is mounted and not open, because it rewinds the data files using flashback logs and then applies redo to reach the target time. After the flashback completes, the database must be opened with ALTER DATABASE OPEN RESETLOGS, which starts a new incarnation, before normal read/write use. Opening the database read-only beforehand to verify the result is optional.
Why the other options are wrong
- A. Flashback Database relies on flashback logs and redo, not on multiplexed online redo logs.
- C. Block change tracking speeds up incremental backups and plays no part in Flashback Database.
- D. The database must be mounted, not open in restricted mode, for FLASHBACK DATABASE to run.
- F. Opening read-only to check the SCN is an optional verification step, not a requirement.
Question 38
Which two statements are true about the use of the procedures listed in the v$sysaux_occupants.move_procedure column? (Choose two.)
Show answer and explanation
Correct answer: A, B. The procedure may be used for some components to relocate component data to the SYSAUX tablespace from its current tablespace; The procedure may be used for some components to relocate component data from the SYSAUX tablespace to another tablespace.
V$SYSAUX_OCCUPANTS lists each component that stores data in the SYSAUX tablespace, and its MOVE_PROCEDURE column names the procedure that relocates that component's data. For components that have such a procedure, it can move the data out of SYSAUX into another tablespace, and it can also be used to move the data back into SYSAUX from the tablespace where it currently resides. The column is null for components that have no move procedure, so relocation is available only for some occupants.
Why the other options are wrong
- C. Not every component can be moved, because some occupants have no move procedure listed in MOVE_PROCEDURE.
- D. Some occupants must remain in SYSAUX, since they have no move procedure to relocate them to another tablespace.
Question 39
You upgraded from a previous Oracle database version to Oracle Database 12c. Your database supports a mixed workload. During the day, lots of insert, update, and delete operations are performed. At night, Extract, Transform, Load (ETL) and batch reporting jobs are run. The ETL jobs perform certain database operations using two or more concurrent sessions. After the upgrade, you notice that the performance of ETL jobs has degraded. To ascertain the cause of performance degradation, you want to collect basic statistics such as the level of parallelism, total database time, and the number of I/O requests for the ETL jobs. How do you accomplish this?
Show answer and explanation
Correct answer: D. Enable real-time database operation monitoring using the DBMS_SQL_MONITOR.BEGIN_OPERATION function, and then use the DBMS_SQL_MONITOR.REPORT_SQL_MONITOR function to view the required information.
Real-time database operation monitoring in Oracle Database 12c tracks a composite business task, such as an ETL job made up of many SQL and PL/SQL calls across two or more concurrent sessions, as a single named operation. You mark the start with DBMS_SQL_MONITOR.BEGIN_OPERATION and the end with END_OPERATION, then produce a report with DBMS_SQL_MONITOR.REPORT_SQL_MONITOR. The report shows statistics for the whole operation, including the degree of parallelism, total database time and the number of I/O requests, which is exactly what is needed to diagnose the degraded ETL jobs.
Why the other options are wrong
- A. ASH samples active sessions for the whole instance and does not aggregate statistics for a defined multi-session job as one operation.
- B. SQL tracing produces detailed per-session trace files, which is heavy and does not give a consolidated view of the ETL job as a whole.
- C. Real-time SQL monitoring tracks individual SQL statements, not a composite operation that spans many statements and sessions.
Question 40
You have just completed a manual upgrade of an Oracle 11g Database to Oracle Database 12c. The Post-Upgrade Status Tool reports an INVALID status for some of the components after the upgrade. What must you do first in this situation to attempt to fix this problem?
Show answer and explanation
Correct answer: D. Run utlrp.sql to recompile stored PL/SQL and Java code and check the DBA_REGISTRY view.
When the Post-Upgrade Status Tool reports components as INVALID, the first step is to run utlrp.sql, which recompiles all invalid stored PL/SQL and Java code. Many components show INVALID only because their objects have not yet been recompiled after the upgrade. After utlrp.sql finishes, you check DBA_REGISTRY (or rerun the Post-Upgrade Status Tool) to confirm the components are VALID before investigating further.
Why the other options are wrong
- A. catuppst.sql performs remaining post-upgrade actions that do not require upgrade mode and is not the recompilation step that fixes INVALID components.
- B. utluiobj.sql only lists objects that became invalid during the upgrade and does not fix anything.
- C. preupgrd.sql is the pre-upgrade information tool and its fix-up scripts are meant to run before the upgrade.
Question 41
Which three features work together, to allow a SQL statement to have different cursors for the same statement based on different selectivity ranges? (Choose three.)
Show answer and explanation
Correct answer: A, C, D. Bind Variable Peeking; Adaptive Cursor Sharing; Bind variable used in a SQL statement
Adaptive cursor sharing lets one SQL statement that uses bind variables have multiple child cursors with different plans, each suited to a range of selectivity. It depends on bind variables in the statement, so that different values are passed to the same shared SQL text, and on bind variable peeking, which lets the optimizer see the actual values and their selectivity. When a bind-sensitive cursor shows significantly different selectivity, it becomes bind-aware and the database creates a new child cursor for that selectivity range.
Why the other options are wrong
- B. SQL plan baselines control which accepted plans the optimizer may use; they do not create cursors for different selectivity ranges.
- E. Statements with different literals are distinct SQL texts with separate parent cursors, so adaptive cursor sharing does not apply to them.
Question 42
You plan to migrate your database from a File system to Automatic Storage Management (ASM) on same platform. Which two methods or commands would you use to accomplish this task? (Choose two.)
Show answer and explanation
Correct answer: A, D. RMAN CONVERT command; The BACKUP AS COPY DATABASE . . . command of RMAN
Migrating a database from a file system to ASM on the same platform is done with RMAN, which copies the existing physical files into a disk group. BACKUP AS COPY DATABASE with a FORMAT naming the ASM disk group creates image copies of all data files in ASM, after which SWITCH DATABASE TO COPY makes the database use them. The RMAN CONVERT command, with an ASM disk group as the FORMAT destination, can likewise write copies of the data files into ASM. Both methods move the database files without unloading and reloading any data.
Why the other options are wrong
- B. Data Pump moves data logically into another database and does not relocate the existing database's files into ASM.
- C. Conventional export and import also move data logically and do not migrate the database files into ASM.
- E. Transportable tablespaces cannot move the SYSTEM, SYSAUX and undo tablespaces, so this method cannot migrate the whole database.
Question 43
Which three statements are true about adaptive SQL plan management? (Choose three.)
Show answer and explanation
Correct answer: A, D, E. It automatically performs verification or evolves non-accepted plans, in COMPREHENSIVE mode when they perform better than existing accepted plans; The non-accepted plans are automatically accepted and become usable by the optimizer if they perform better than the existing accepted plans; The non-accepted plans in a SQL plan baseline are automatically evolved, in COMPREHENSIVE mode, during the nightly maintenance window and a persistent verification report is generated.
With adaptive SQL plan management, the evolve process runs automatically: when automatic SQL tuning is in COMPREHENSIVE mode, the SYS_AUTO_SPM_EVOLVE_TASK verifies the non-accepted plans in SQL plan baselines during the nightly maintenance window. Any non-accepted plan that performs better than the existing accepted plans is automatically accepted and becomes usable by the optimizer, and a persistent report describing the verification is generated. The DBA therefore no longer needs to run the verification or evolve process manually.
Why the other options are wrong
- B. A fixed plan is preferred, but the optimizer cannot use it when the plan can no longer be reproduced, for example after an index it relies on is dropped.
- C. Automatically evolved plans are added as accepted plans, not as fixed plans; plans are marked fixed only manually.
Question 44
The persistent configuration settings for RMAN have default for all parameters. Identify four RMAN commands that produce a multi-section backup.
Show answer and explanation
Correct answer: A, B, E, G. BACKUP TABLESPACE SYSTEM SECTION SIZE 100M;; BACKUP AS COPY TABLESPACE SYSTEM SECTION SIZE 100M;; BACKUP TABLESPACE "UNDO" INCLUDE CURRENT CONTROLFILE SECTION SIZE 100M;; BACKUP INCREMENTAL LEVEL 0 TABLESPACE SYSAUX SECTION SIZE 100M;
A multisection backup splits a data file into sections of the size given by SECTION SIZE so that the sections can be processed in parallel. It applies to data files, and backup sets, image copies made with BACKUP AS COPY and incremental level 0 backups all support SECTION SIZE, so the SYSTEM, SYSAUX and UNDO data file backups are produced as multisection backups. Including the current control file does not change this, because the data files in that command are still divided into sections while the control file is backed up normally.
Why the other options are wrong
- C. Archived redo log files are not divided into sections, so this command produces an ordinary archived log backup.
- D. Temporary tablespaces use tempfiles, which RMAN never backs up.
- F. The server parameter file is not a data file, so SECTION SIZE does not produce a multisection backup of it.
Question 45
Your multitenant container database, CDB1, is running in ARCHIVELOG mode and has two pluggable databases, HR_PDB and ACCOUNTS_PDB. An RMAN backup exists for the database. You issue the command to open ACCOUNTS_PDB and find that the USERDATA.DBF data file for the default permanent tablespace USERDATA belonging to ACCOUNTS_PDB is corrupted. What should you do before executing the commands to restore and recover the data file in ACCOUNTS_PDB?
Show answer and explanation
Correct answer: D. Take the USERDATA tablespace offline in ACCOUNTS_PDB.
USERDATA is a non-system tablespace in ACCOUNTS_PDB, so its damaged data file can be recovered while the CDB, HR_PDB and the rest of ACCOUNTS_PDB stay available. Before restoring, you take the USERDATA tablespace offline in ACCOUNTS_PDB (for example with OFFLINE IMMEDIATE, which is permitted in ARCHIVELOG mode), then restore and recover it with RMAN and bring it back online. There is no need to mount the whole CDB or close other containers for a non-system data file.
Why the other options are wrong
- A. Mounting CDB1 closes every PDB and makes the whole database unavailable, which is unnecessary for a non-system data file.
- B. Mounting CDB1 already closes all PDBs, and taking the entire CDB down is far more disruptive than recovery requires.
- C. Opening the PDB in restricted mode only limits user access and does not take the damaged data file out of use for restoration.
Question 46
You execute the following command to create a password file in the database server: $ orapwd file = +DATA/PROD/orapwprod entries = 5 ignorecase = N format = 12 Which two statements are true about the password file? (Choose two.)
Show answer and explanation
Correct answer: C, E. It is used by Oracle to authenticate users for remote database administration; It supports the SYSBACKUP, SYSDG, and SYSKM system privileges.
The password file stores the credentials of users granted administrative privileges, and Oracle uses it to authenticate administrators who connect remotely with SYSDBA or another administrative privilege. Created with format=12, the file also supports the SYSBACKUP, SYSDG and SYSKM administrative privileges, which appear as columns in V$PWFILE_USERS.
Why the other options are wrong
- A. Granting the DBA role does not add a user to the password file; only administrative privileges such as SYSDBA do.
- B. Auditing has no connection with the entries in the password file.
- D. Membership of the OSDBA or OSOPER group is checked by operating system authentication and is not recorded in the password file.
Question 47
Which two statements are true about the process monitor (PMON) background process in Oracle Database 12c? (Choose two.)
Show answer and explanation
Correct answer: C, E. It kills sessions that exceed idle time; It frees resources held by abnormally terminated processes.
The process monitor (PMON) is responsible for cleanup. When a user process terminates abnormally, PMON frees the resources it held, releasing locks, resetting the transaction table and removing the process from the list of active processes. PMON also cleans up sessions that have been idle too long: when resource limits are enforced, sessions exceeding their profile IDLE_TIME are terminated, and PMON handles detached connections and transactions that exceed their idle timeout.
Why the other options are wrong
- A. Checkpoint information is recorded in the control file by the checkpoint process (CKPT), not PMON.
- B. Unused temporary segments are freed by the system monitor process (SMON).
- D. In Oracle Database 12c, services are registered with listeners by the listener registration process (LREG).
Question 48
Which three statements are true when the listener handles connection requests to an Oracle 12c database instance with multithreaded architecture enabled in UNIX? (Choose three.)
Show answer and explanation
Correct answer: B, D, E. The local listener may spawn a new process and have that new process create a thread; Each multithreaded Oracle process has an SCMN thread; The local listener may pass the request to an existing process which in turn will create a thread.
With threaded_execution set to TRUE on UNIX, most Oracle processes run as operating system threads inside a smaller number of processes. Each multithreaded Oracle process contains an SCMN thread, which accepts connection requests handed to that process and creates new threads within it. For a dedicated connection, the local listener may spawn a new process and have that process create the server thread, or, when DEDICATED_THROUGH_BROKER_listener_name is set to ON in listener.ora, it may pass the request to an existing process, which in turn creates the thread. No dispatcher is involved, because dispatchers belong to the shared server architecture.
Why the other options are wrong
- A. Dispatchers are part of the shared server architecture, and dedicated connections in the multithreaded model create threads without any dispatcher.
- C. Only multithreaded Oracle processes contain an SCMN thread; background processes that still run as single-threaded operating system processes, such as PMON and DBWn, do not.
Question 49
Which two statements are true concerning the Resource Manager plans for individual pluggable databases (PDB plans) in a multitenant container database (CDB)? (Choose two.)
Show answer and explanation
Correct answer: A, E. If no PDB plan is enabled for a pluggable database, then all sessions for that PDB are treated to an equal degree of the resource share of that PDB; If a PDB plan is enabled for a pluggable database, then resources are allocated to consumer groups based on the shares provided to the PDB in the CDB plan
In a CDB, the CDB resource plan allocates resources among PDBs with shares and utilization limits, and a PDB resource plan then distributes each PDB's portion among its consumer groups. If no PDB plan is enabled, all sessions in that PDB are treated equally within the resources the PDB receives. If a PDB plan is enabled, its consumer groups receive resources out of the share given to that PDB in the CDB plan. PDB plans are single-level, allow up to eight consumer groups and do not support subplans.
Why the other options are wrong
- B. PDB plans cannot use subplans at all; they are single-level plans with at most eight consumer groups.
- C. A PDB plan allocates resources only among the consumer groups of its own PDB, not across all PDBs.
- D. A PDB's share comes from its directive in the CDB plan, or the default directive, not from a dynamic calculation.
Question 50
You notice a performance change in your production Oracle 12c database. You want to know which change caused this performance difference. Which method or feature should you use?
Show answer and explanation
Correct answer: A. Compare Period ADDM report
Compare Period ADDM compares database performance between two time periods, such as a baseline period before the change and the current period, and returns a report that describes the performance differences and identifies the root cause of the change, for example increased concurrency waits or a changed SQL workload. Because it performs analysis rather than simply presenting statistics, it is the feature designed to show which change caused a performance difference on a production system.
Why the other options are wrong
- B. The AWR Compare Periods report shows differences in statistics between two periods but leaves the diagnosis of the cause to the DBA.
- C. An ASH report analyzes session activity in a single time window and does not compare periods to find what changed.
- D. Manually comparing snapshots only exposes raw statistic differences and does not identify the cause of the change.
Question 51
You want to prevent a group of users in your database from performing long-running transactions that consume huge amounts of space in the undo tablespace. If the quota for these users is exceeded during execution of a data manipulation language (DML) statement, the operation should abort and return an error. However, queries should still be allowed, even if users have exceeded the undo space limitation. How would you achieve this?
Show answer and explanation
Correct answer: D. Implement a Database Resource Manager plan.
Database Resource Manager can assign an undo pool to a consumer group through the UNDO_POOL directive in a resource plan. The pool limits the total undo that uncommitted transactions of the group can generate. When the limit is exceeded, the DML statement generating the undo is terminated with an error, and no member of the group can perform further DML until undo is freed. Queries generate no undo and continue to run normally, which matches the requirement.
Why the other options are wrong
- A. Tablespace quotas do not apply to undo tablespaces, so undo usage cannot be limited this way.
- B. ITL slots govern concurrent transactions within a block and do not limit undo space consumption.
- C. Profiles limit resources such as CPU, sessions and idle time, but they have no undo space limit.
Question 52
You plan to implement the distributed database system in your company. You invoke Database Configuration Assistant (DBCA) to create a database on the server. During the installation, DBCA prompts you to specify the Global Database Name. What must this name be made up of?
Show answer and explanation
Correct answer: A. It must be made up of a database name and a domain name.
A global database name uniquely identifies a database in a distributed environment and takes the form database_name.domain_name, for example sales.us.example.com. The database name part becomes the DB_NAME initialization parameter and the domain part becomes DB_DOMAIN. DBCA asks for this value on its database identification page and, by default, derives the SID from the database name portion.
Why the other options are wrong
- B. The host name is not part of the global database name, and the SID is a separate instance identifier.
- C. INSTANCE_NAME identifies an instance, and neither it nor the host name forms the global database name.
- D. ORACLE_SID and SERVICE_NAMES are instance and service settings, not components of the global database name.
Question 53
Oracle Grid Infrastructure for a standalone server is installed on your production host before installing the Oracle Database server. The database and listener are configured by using Oracle Restart. Examine the following command and its output: $ crsctl config has CRS-4622: Oracle High Availability Services auto start is enabled. What does this imply?
Show answer and explanation
Correct answer: B. When a database instance is started by using the SRVCTL utility and listener startup fails, the instance is still started.
The crsctl output confirms that Oracle High Availability Services starts automatically, so Oracle Restart manages the registered database and listener. Oracle Restart treats the link between a database instance and the listener as a weak dependency: when the instance is started with SRVCTL, Oracle Restart attempts to start the listener, and if the listener fails to start, the database instance is still started. A later listener failure likewise does not cause Oracle Restart to shut down or restart the instance.
Why the other options are wrong
- A. Dependent components are started automatically only when the instance is started through SRVCTL or Oracle Restart, not from SQL*Plus.
- C. A database created with SQL*Plus must be registered manually with srvctl add database; only DBCA adds it to Oracle Restart automatically.
- D. Services created through the SERVICE_NAMES parameter are not registered with Oracle Restart; they must be added with srvctl add service.
Question 54
You must track all transactions that modify certain tables in the sales schema for at least three years. Automatic undo management is enabled for the database with a retention of one day. Which two must you do to track the transactions? (Choose two.)
Show answer and explanation
Correct answer: D, E. Create a Flashback Data Archive in any suitable tablespace; Enable Flashback Data Archiving for the tables that require tracking.
Flashback Data Archive keeps the history of changes to tracked tables for a specified retention period, independent of undo retention. You create an archive with CREATE FLASHBACK ARCHIVE in any suitable tablespace and a retention of at least three years, for example RETENTION 3 YEAR. By default no table is tracked, so you then enable archiving for each required table in the sales schema with ALTER TABLE ... FLASHBACK ARCHIVE. Historical changes are then kept in the archive for the full retention period.
Why the other options are wrong
- A. Supplemental logging adds column data to the redo stream for LogMiner and replication; it does not provide three years of change history.
- B. Undo retention guarantee applies to the undo tablespace and a one-day retention cannot track changes for three years.
- C. The archive does not need to be in the tablespace where the tables are stored; any suitable tablespace can be used.
Question 55
In which two scenarios do you use SQL*Loader to load data? (Choose two.)
Show answer and explanation
Correct answer: C, D. Load data into multiple tables during the same load statement; Generate unique sequential key values in specified columns.
A SQL*Loader control file can contain several INTO TABLE clauses, so one load can distribute records from the same input into multiple tables, optionally using WHEN conditions to choose the target table. SQL*Loader can also generate unique sequential key values in a column with the SEQUENCE parameter, for example SEQUENCE(MAX,1), without needing a database sequence.
Why the other options are wrong
- A. Transforming data while it is loaded is the situation in which external tables are recommended over SQL*Loader.
- B. Transparent parallel loading without splitting the input data first is a benefit of external tables, not SQL*Loader.
Question 56
Which three are direct benefits of the multiprocess, multithreaded architecture of Oracle Database 12c when it is enabled? (Choose three.)
Show answer and explanation
Correct answer: B, C, F. Reduced virtual memory utilization; Improved parallel execution performance; Reduced CPU utilization
When the multiprocess, multithreaded model is enabled with threaded_execution set to TRUE, many Oracle processes run as operating system threads inside a small number of processes. Threads share an address space, so the instance uses less virtual memory than one address space per process. Switching between threads is cheaper than switching between processes, which reduces CPU utilization. Parallel execution servers also benefit, because creating and coordinating threads is faster than managing separate processes, improving parallel execution performance.
Why the other options are wrong
- A. Logical I/O depends on the SQL and data access paths, not on whether Oracle processes run as threads.
- D. A single serial statement is executed the same way by one server process or thread, so serial execution does not directly gain.
- E. Physical I/O is driven by the workload and buffer cache behavior, which the threading model does not change.
Question 57
Which three statements are true about Automatic Workload Repository (AWR)? (Choose three.)
Show answer and explanation
Correct answer: B, C, D. The AWR data is stored in memory and in the database; The snapshots collected by AWR are used by the self-tuning components in the database; AWR computes time model statistics based on time usage for activities, which are displayed in the V$SYS_TIME_MODEL and V$SESS_TIME_MODEL views.
AWR collects performance statistics in memory, in the SGA, and periodically writes them as snapshots to tables in the SYSAUX tablespace, so its data lives both in memory and in the database. These snapshots feed the self-tuning components of the database, such as ADDM, automatic SQL tuning and the memory advisors, which analyze them to produce findings and recommendations. AWR also stores time model statistics, which measure time spent on activities such as DB time and SQL execution and are exposed through the V$SYS_TIME_MODEL and V$SESS_TIME_MODEL views.
Why the other options are wrong
- A. AWR tables are owned by SYS and stored in the SYSAUX tablespace, not in the SYSTEM schema.
- E. System-wide tracing and logging information belongs to the Automatic Diagnostic Repository (ADR), not to AWR.
Question 58
Which three tasks can be automatically performed by the Automatic Data Optimization feature of Information Lifecycle Management (ILM)? (Choose three.)
Show answer and explanation
Correct answer: A, B, D. Tracking the most recent read time for a table segment in a user tablespace; Tracking the most recent write time for a table segment in a user tablespace; Tracking the most recent write time for a table block
Automatic Data Optimization relies on Heat Map, which, once the heat_map parameter is ON, tracks data usage automatically. At the segment level it records the most recent read time and the most recent write time for table segments, and at the row level it tracks modifications by recording the most recent write time for each data block. ADO policies use these timestamps to compress or move data that has not been accessed or modified for a given period. Objects in the SYSTEM and SYSAUX tablespaces are not tracked.
Why the other options are wrong
- C. Heat Map does not record an insert time for each row; row-level tracking records the last modification time per block.
- E. Segments in the SYSAUX tablespace are excluded from Heat Map tracking.
- F. Segments in the SYSAUX tablespace are excluded from Heat Map tracking, so their write times are not recorded.
Question 59
Which two statements are true about extents? (Choose two.)
Show answer and explanation
Correct answer: B, C. Data blocks in an extent are logically contiguous but can be non-contiguous on disk; The blocks of a newly allocated extent, although free, may have been used before.
An extent is a set of logically contiguous data blocks within one data file, but the operating system may store those blocks in non-contiguous locations on disk. When the database allocates a new extent, it takes blocks from free space in the tablespace, and that space may previously have belonged to a dropped or truncated object, so the blocks are free but may have been used before. The database formats the blocks as they are needed for the new segment.
Why the other options are wrong
- A. An extent is always allocated within a single data file and cannot span data files.
- D. Deleting all rows does not return extents to the tablespace; they stay with the segment until it is truncated, deallocated or dropped.
Question 60
You create a new pluggable database, HR_PDB, from the seed database. Which three tablespaces are created by default in HR_PDB? (Choose three.)
Show answer and explanation
Correct answer: A, B, E. SYSTEM; SYSAUX; TEMP
A PDB created from the seed is a copy of PDB$SEED, which contains the SYSTEM and SYSAUX tablespaces holding the PDB's own data dictionary and auxiliary data, plus a TEMP temporary tablespace. These three are therefore created by default in HR_PDB. Additional permanent tablespaces such as USERS are created only when requested, for example with the DEFAULT TABLESPACE clause of CREATE PLUGGABLE DATABASE, and undo is provided by the undo tablespace of the CDB root.
Why the other options are wrong
- C. The EXAMPLE tablespace comes from the optional sample schemas and is not part of the seed.
- D. The PDB uses the undo tablespace of the CDB root and has no UNDO tablespace of its own.
- F. USERS is created only when the DEFAULT TABLESPACE clause requests it, not by default.
Question 61
Your database supports an online transaction processing (OLTP) application. The application is undergoing some major schema changes, such as addition of new indexes and materialized views. You want to check the impact of these changes on workload performance. What should you use to achieve this?
Show answer and explanation
Correct answer: D. SQL Performance Analyzer
SQL Performance Analyzer measures how a system change affects the performance of a SQL workload. It executes the statements of a SQL tuning set before and after the change, such as adding indexes or materialized views, and compares their execution statistics and plans to report which statements improved, regressed or stayed the same. Schema changes are one of its documented use cases, which makes it the right tool for checking the impact of the new indexes and materialized views on the OLTP workload's SQL.
Why the other options are wrong
- A. Database Replay replays a captured workload to test system-level changes and does not report the per-statement SQL impact of schema changes.
- B. SQL Tuning Advisor recommends improvements for individual statements but does not compare performance before and after a change.
- C. SQL Access Advisor recommends indexes and materialized views to create but does not measure the impact of changes already made.
- E. AWR compare reports show differences in whole-database activity between two periods without isolating the effect of the schema changes on individual SQL statements.
Question 62
In your multitenant container database (CDB) with two pluggable databases (PDBs), you want to create a new PDB by using SQL Developer. Which statement is true?
Show answer and explanation
Correct answer: A. The CDB must be open.
Creating a PDB, whether with SQL Developer, DBCA or CREATE PLUGGABLE DATABASE, copies the files of the seed (PDB$SEED) and registers the new PDB in the data dictionary of the root. That requires the CDB root to be open in read/write mode, so the CDB must be open. The existing PDBs are not affected by the operation and can stay open while the new PDB is created.
Why the other options are wrong
- B. In the mount state the root data dictionary is not open, so a PDB cannot be created.
- C. In the nomount state only the instance is started, with no control file or data dictionary available for creating a PDB.
- D. Other PDBs do not need to be closed; a new PDB can be created while the existing PDBs remain open.
Question 63
You wish to enable an audit policy for all database users, except SYS, SYSTEM, and SCOTT. You issue the following statements: SQL> AUDIT POLICY ORA_DATABASE_PARAMETER EXCEPT SYS; SQL> AUDIT POLICY ORA_DATABASE_PARAMETER EXCEPT SYSTEM; SQL> AUDIT POLICY ORA_DATABASE_PARAMETER EXCEPT SCOTT; For which database users is the audit policy now active?
Show answer and explanation
Correct answer: B. All users except SCOTT
When the same unified audit policy is enabled several times with different EXCEPT lists, Oracle Database uses only the exception list from the most recent AUDIT POLICY statement. Each AUDIT POLICY ORA_DATABASE_PARAMETER EXCEPT statement replaces the previous exception list rather than adding to it. After the three statements, only SCOTT is excepted, so the policy is active for all users except SCOTT. To exclude all three users, a single statement listing EXCEPT SYS, SYSTEM, SCOTT is required.
Why the other options are wrong
- A. The EXCEPT SYS list was replaced by the later statements, so SYS is audited again.
- C. Exception lists are not cumulative; only the last list, containing SCOTT, applies.
- D. Only the final EXCEPT list applies, so SYS and SYSTEM are no longer excluded.
Question 64
You have successfully taken a database backup by using the command: RMAN> BACKUP AS BACKUPSET DATABASE; Now you execute this command: RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE; What is the outcome?
Show answer and explanation
Correct answer: D. It takes an incremental level 0 backup of the database.
BACKUP AS BACKUPSET DATABASE creates a full backup, which is not part of an incremental strategy and cannot act as the parent of an incremental backup. When BACKUP INCREMENTAL LEVEL 1 DATABASE runs, RMAN looks for a level 0 backup to use as the base. The Oracle Database 12c RMAN reference states that if no level 0 backup exists, then RMAN makes a level 0 backup automatically. The command therefore succeeds and produces an incremental level 0 backup of the database, copying all used blocks, which then serves as the base for later level 1 backups.
Why the other options are wrong
- A. A level 0 base can be a backup set or an image copy, and the command does not fail; RMAN creates the missing level 0 automatically.
- B. The command does not fail when no level 0 exists, because RMAN automatically makes a level 0 backup instead.
- C. A full backup is not a parent for incrementals, so RMAN does not back up only changed blocks; it takes a level 0 of all used blocks.
- E. RMAN takes only the level 0 backup in this case; no level 1 backup is taken afterward by the same command.
Question 65
Which two are prerequisites for performing a flashback transaction? (Choose two.)
Show answer and explanation
Correct answer: C, D. EXECUTE privilege on the DBMS_FLASHBACK package must be granted to the user flashing back transaction; Supplemental logging must be enabled.
Flashback Transaction uses DBMS_FLASHBACK.TRANSACTION_BACKOUT to undo a transaction and its dependent transactions, and it relies on LogMiner to read the redo for those transactions. The user performing the backout must therefore be granted EXECUTE on the DBMS_FLASHBACK package (along with SELECT ANY TRANSACTION and DML privileges on the affected tables). The database must also have supplemental logging enabled, with ALTER DATABASE ADD SUPPLEMENTAL LOG DATA and primary key supplemental logging, so that LogMiner can build correct compensating SQL. ARCHIVELOG mode is required as well, but Flashback Database, guaranteed undo retention, the recycle bin and block change tracking are not prerequisites.
Why the other options are wrong
- A. Flashback Database rewinds the whole database using flashback logs and is not needed to back out an individual transaction.
- B. Guaranteed undo retention is not a requirement, because the backout is built from redo mined by LogMiner rather than from retained undo.
- E. The recycle bin only holds dropped objects and plays no part in backing out a transaction.
- F. Block change tracking only speeds up RMAN incremental backups and has nothing to do with Flashback Transaction.
Question 66
In your multitenant container database (CDB) containing pluggable databases (PDBs), the HR user executes the following commands to create and grant privileges on a procedure: CREATE OR REPLACE PROCEDURE create_test (v_emp_id NUMBER, v_ename VARCHAR2, v_SALARY NUMBER, v_dept_id NUMBER) AS BEGIN INSERT INTO hr.test VALUES (V_emp_id, V_ename, V_salary, V_dept_id); END; / GRANT EXECUTE ON CREATE_TEST TO john, jim, smith, king; How can you prevent users having the EXECUTE privilege on the CREATE_TEST procedure from inserting values into tables on which they do not have any privileges?
Show answer and explanation
Correct answer: C. Create the CREATE_TEST procedure with invoker's rights.
By default a PL/SQL procedure runs with definer's rights, so anyone granted EXECUTE on CREATE_TEST inserts into HR.TEST with HR's privileges, even without any privilege on the table. Creating the procedure with invoker's rights, using the AUTHID CURRENT_USER clause, makes it run with the privileges of the user who calls it. Users such as JOHN, JIM, SMITH and KING can then insert only into tables on which they themselves hold the INSERT privilege.
Why the other options are wrong
- A. Definer's rights is the default behavior that lets callers insert with HR's privileges, which is the problem being solved.
- B. WITH GRANT OPTION only lets grantees pass the EXECUTE privilege on to others and does not restrict what the procedure can do.
- D. A package also runs with definer's rights by default, so placing the procedure in one changes nothing.
Question 67
Which three statements are true about automated maintenance tasks? (Choose three.)
Show answer and explanation
Correct answer: A, B, E. They run at predefined time intervals that are intended to occur during a period of low system load; An Oracle Scheduler job is created for each maintenance task that is scheduled to run in a maintenance window; Predefined maintenance tasks consist of automatic optimizer statistics collection, running Automatic Segment Advisor, and running Automatic SQL Tuning Advisor.
Automated maintenance tasks run in predefined maintenance windows, which are scheduled for periods expected to have low system load, such as nights and weekends. When a window opens, the autotask framework creates an Oracle Scheduler job for each maintenance task scheduled to run in it. The predefined tasks are automatic optimizer statistics collection, the Automatic Segment Advisor and Automatic SQL Tuning Advisor.
Why the other options are wrong
- C. A maintenance window is not extended; tasks still running when the window closes are stopped.
- D. Maintenance task history is kept in the SYSAUX tablespace, not the SYSTEM tablespace.
Question 68
Which two statements are true about Oracle Data Pump export and import operations? (Choose two.)
Show answer and explanation
Correct answer: A, E. You can detach from a data pump export job and reattach later; You can detach from a data pump import job and reattach later.
Data Pump jobs run on the database server independently of the client that started them, so both the expdp and impdp clients can detach from a running job, for example with Ctrl+C followed by EXIT_CLIENT in interactive-command mode, while the job keeps running. The same job can later be reattached with the ATTACH parameter to monitor or control it, and this works for export jobs and import jobs alike.
Why the other options are wrong
- B. Parallel import is carried out by the master control process assigning work to multiple Data Pump worker processes (DWnn), not by parallel execution server processes.
- C. Data Pump accesses files through a directory object, and the operating system directory must be readable and writable by the Oracle software owner but does not have to be owned by it.
- D. The master table is not exported as a database object; Data Pump writes its contents to the dump file set to record the job.
Question 69
You executed this command to create a password file: $ orapwd file = orapworcl entries = 10 ignorecase = N Which two statements are true about the password file? (Choose two.)
Show answer and explanation
Correct answer: A, D. It will permit the use of uppercase passwords for database users who have been granted the SYSOPER role; It will permit the use of lowercase passwords for database users who have been granted the SYSDBA role.
With ignorecase=N, the password file stores passwords case sensitively, which is also the default. Passwords for administrative users can therefore contain uppercase, lowercase or mixed-case characters, and the exact case must be supplied at login. Users granted SYSOPER can use uppercase passwords and users granted SYSDBA can use lowercase passwords, because a case-sensitive file accepts passwords in any case. The file holds entries for users granted administrative privileges such as SYSDBA and SYSOPER, and entries=10 sets the number of such users it can hold.
Why the other options are wrong
- B. Members of the OSOPER group are authenticated by the operating system, not stored in the password file.
- C. Members of the OSDBA group are authenticated by the operating system, not stored in the password file.
- E. ignorecase=N makes passwords case sensitive, which permits mixed-case passwords.
Question 70
Which two statements are true? (Choose two.)
Show answer and explanation
Correct answer: B, C. A role can be granted to other roles; A role can contain both system and object privileges.
A role is a named group of privileges that can be granted to users and to other roles, which allows privileges to be organized hierarchically, for example granting a CLERK role to a MANAGER role. A single role can contain both system privileges, such as CREATE TABLE, and object privileges, such as SELECT on a specific table, so one grant of the role gives a user everything needed for a job function.
Why the other options are wrong
- A. A role can be created with IDENTIFIED EXTERNALLY so that it is authorized by the operating system or another external service.
- D. In Oracle Database 12c the RESOURCE role no longer includes the UNLIMITED TABLESPACE system privilege.
- E. Roles are not owned by any user or schema, including SYS.
- F. No role is granted automatically at user creation; CONNECT must be granted explicitly.
Question 71
You run a script that completes successfully using SQL*Plus that performs these actions: 1. Creates a multitenant container database (CDB) 2. Plugs in three pluggable databases (PDBs) 3. Shuts down the CDB instance 4. Starts up the CDB instance using STARTUP OPEN READ WRITE Which two statements are true about the outcome after running the script? (Choose two.)
Show answer and explanation
Correct answer: B, D. The seed will be opened read-only; The other PDBs will be in mount state.
STARTUP OPEN READ WRITE opens the root of the CDB. The seed, PDB$SEED, is always opened read only when the CDB opens, while the other PDBs remain in the MOUNTED state until they are opened with ALTER PLUGGABLE DATABASE ... OPEN, whether manually, through a startup trigger or through a saved PDB state. Because the script only created the CDB, plugged in the PDBs and restarted the instance, the three plugged-in PDBs are left mounted.
Why the other options are wrong
- A. The seed is always opened read only when the CDB opens, not left mounted.
- C. The seed is always read only and is never opened read/write in normal operation.
- E. The PDBs stay mounted after startup, and nothing opens them read only.
- F. Opening the CDB read/write does not open the PDBs, which stay mounted.
Question 72
Examine the following parameters for a database instance: MEMORY_MAX_TARGET=0 MEMORY_TARGET=0 SGA_TARGET=0 PGA_AGGREGATE_TARGET=500m Which three initialization parameters are not controlled by Automatic Shared Memory Management (ASMM)? (Choose three.)
Show answer and explanation
Correct answer: A, E, F. LOG_BUFFER; DB_16K_CACHE_SIZE; DB_KEEP_CACHE_SIZE
Automatic Shared Memory Management automatically sizes the default buffer cache, shared pool, large pool, Java pool and Streams pool. Some SGA components are always sized manually even when ASMM is enabled, with their memory taken from sga_target: the log buffer (log_buffer), the KEEP and RECYCLE buffer caches (db_keep_cache_size and db_recycle_cache_size), and the nonstandard block size buffer caches such as db_16k_cache_size. These parameters are therefore not controlled by ASMM.
Why the other options are wrong
- B. sort_area_size is not an SGA parameter at all but a PGA work area setting, ignored here because pga_aggregate_target is set, so it is not one of the SGA components left to manual sizing under ASMM.
- C. java_pool_size is an automatically tuned component under ASMM, and a value set for it acts only as a minimum.
- D. streams_pool_size is an automatically tuned component under ASMM, and a value set for it acts only as a minimum.
Question 73
You performed an incremental level 0 backup of a database: RMAN > BACKUP INCREMENTAL LEVEL 0 DATABASE; To enable block change tracking after the incremental level 0 backup, you issued this command: SQL > ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE /mydir/rman_change_track.f; To perform an incremental level 1 cumulative backup, you issued this command: RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE; Which three statements are true? (Choose three.)
Show answer and explanation
Correct answer: A, D, E. Block change tracking will sometimes reduce I/O performed during cumulative incremental backups; More than one database block may be read by an incremental backup for a change made to a single block; The incremental level 1 backup that immediately follows the enabling of block change tracking will not read the change tracking file to discover changed blocks.
Block change tracking records changed blocks in bitmaps so that RMAN can avoid scanning whole data files, but the benefit depends on the history kept in the tracking file. The file retains a limited number of bitmaps, so a cumulative level 1 backup whose parent is older than that history must fall back to a full scan, which is why I/O is only sometimes reduced. Changes are tracked in chunks of several blocks, so a change to one block can cause RMAN to read more than one block. Because tracking was enabled after the level 0 backup, the first level 1 backup has no bitmap covering the period since its parent and cannot use the change tracking file.
Why the other options are wrong
- B. RMAN does not back up the change tracking file; it is not needed for recovery and is re-created if lost.
- C. The I/O saving is not guaranteed, because RMAN must scan the data files when the tracking file does not cover the period since the parent backup.
Question 74
An administrator account is granted the CREATE SESSION and SET CONTAINER system privileges. A multitenant container database (CDB) instance has the following parameter set: THREADED_EXECUTION = FALSE Which four statements are true about this administrator establishing connections to root in a CDB that has been opened in read only mode? (Choose four.)
Show answer and explanation
Correct answer: A, C, D, E. You can connect as a common user by using the connect statement; You can connect by using easy connect; You can connect by using OS authentication; You can connect by using a Net Service name.
The root of a CDB can contain only common users, so the administrator connects to the root as a common user with the CONNECT statement, which the CREATE SESSION privilege allows. With threaded_execution set to FALSE, the traditional process model is in use, so operating system authentication works. Connections through the listener also work, whether they use an Easy Connect string or a net service name that resolves to the root's service. A CDB opened read only still accepts all of these connections, because connecting does not modify data.
Why the other options are wrong
- B. Local users cannot exist in the root, so no local user can connect to it.
- F. Local users cannot exist in the root, so a local user cannot switch into it with SET CONTAINER.
Question 75
Examine the following command: ALTER SYSTEM SET enable_ddl_logging = TRUE; Which statement is true?
Show answer and explanation
Correct answer: C. All DDL commands are logged in a different log file that contains DDL statements and their execution dates.
With enable_ddl_logging set to TRUE, Oracle Database 12c writes DDL statements to a separate DDL log rather than to the alert log. There are two DDL logs holding the same information, an XML file and a plain text file, stored in the log/ddl subdirectory of the ADR home, and each entry records the DDL statement together with the time it was executed. This gives a dedicated history of DDL activity that is kept apart from the alert log.
Why the other options are wrong
- A. Logging is not limited to failed statements, and the entries go to the DDL log rather than the alert log.
- B. DDL statements are written to the separate DDL log, not to the alert log.
- D. DDL logging is not restricted to statements that create new segments.
- E. The DDL log is written to the log/ddl subdirectory of the ADR home, not to the alert directory, and it is kept in both XML and text form.
Question 76
Your multitenant container database (CDB) containing three pluggable databases (PDBs) is running in ARCHIVELOG mode. You find that the SYSAUX tablespace is corrupted in the root container. The steps to recover the tablespace are as follows: 1. Mount the CDB. 2. Close all the PDBs. 3. Open the database. 4. Apply the archive redo logs. 5. Restore the data file. 6. Take the SYSAUX tablespace offline. 7. Place the SYSAUX tablespace online. 8. Open all the PDBs with RESETLOGS. 9. Open the database with RESETLOGS. 10. Execute the command SHUTDOWN ABORT. Which option identifies the correct sequence to recover the SYSAUX tablespace?
Show answer and explanation
Correct answer: A. 6, 5, 4, 7
SYSAUX is not a SYSTEM or undo tablespace, so even in the root container it can be taken offline while the CDB and all PDBs remain open. The recovery therefore needs no shutdown: take SYSAUX offline (step 6, for example ALTER TABLESPACE sysaux OFFLINE IMMEDIATE), restore its data file (step 5), apply archived redo to recover it (step 4), and bring the tablespace back online (step 7). Because this is a complete recovery in ARCHIVELOG mode, no RESETLOGS is involved.
Why the other options are wrong
- B. Aborting the CDB is unnecessary for SYSAUX, the sequence skips applying redo, and PDBs are never opened with RESETLOGS.
- C. Complete recovery of one tablespace does not need a shutdown or RESETLOGS, and PDBs are never opened with RESETLOGS.
- D. This sequence omits recovery with archived redo, shuts the database down needlessly and misuses RESETLOGS.
Question 77
You notice a high number of waits for the db file scattered read and db file sequential read events in the recent Automatic Database Diagnostic Monitor (ADDM) report. After further investigation, you find that queries are performing too many full table scans and indexes are not being used even though the filter columns are indexed. Identify three possible reasons for this.
Show answer and explanation
Correct answer: A, C, D. Missing or stale histogram statistics; High clustering factor for the indexes; High value for the DB_FILE_MULTIBLOCK_READ_COUNT parameter
Missing or stale histograms on skewed columns lead the optimizer to misestimate the selectivity of filter predicates, so it can overestimate the rows returned and prefer a full table scan over an index. A high clustering factor means table rows for adjacent index entries are scattered across many blocks, which makes index range scans expensive in the cost model. A high DB_FILE_MULTIBLOCK_READ_COUNT lowers the estimated cost of multiblock full scans, making them look cheaper than index access.
Why the other options are wrong
- B. An undersized shared pool causes parsing and library cache problems, not a preference for full table scans over indexes.
- E. An oversized buffer cache does not cause the optimizer to ignore available indexes.
Question 78
You have the following entry in the tnsnames.ora of your hq.us.example.com host machine: ORCL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = hq.us.example.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = ORCL.us.example.com) ) ) You issue the following command at the command prompt: sqlplus HR/HR@ORCL Which statement is true about the connection to the ORCL database instance?
Show answer and explanation
Correct answer: D. The connection succeeds, provided the ORCL.us.example.com database service is registered with a listener, the listener is up, and the database is open.
The tnsnames.ora entry in the exhibit is named ORCL and points to TCP host hq.us.example.com on port 1521, with a connect data section specifying SERVICE_NAME = ORCL.us.example.com. The connect string HR/HR@ORCL matches the entry name directly, so the client resolves it and contacts the listener at that address asking for service ORCL.us.example.com. The connection succeeds only if that service is registered with the listener, the listener is running and the database is open.
Why the other options are wrong
- A. NAMES.DEFAULT_DOMAIN is not needed, because the connect identifier ORCL already matches the unqualified entry name in tnsnames.ora.
- B. The net service name does not need the domain suffix; it only has to match the entry name, which is ORCL.
- C. The client requests service ORCL.us.example.com, so a service registered only as ORCL would not satisfy the request.
Question 79
A database instance is started by using an SPFILE. The database is configured in ARCHIVELOG mode and the control file autobackup is configured. Daily full database backups are performed by using RMAN. You lost all control files due to media failure. Given the steps to recover from the error in random order: 1. Shut down the instance, if it is not already down. 2. Restore the control file from autobackup to a new location. 3. Start the database instance to NOMOUNT state. 4. Recover the database to the point of failure of the control file. 5. Open the database with the RESETLOGS option. 6. Mount the database. 7. Update the SPFILE with the new location of the control file by using the ALTER SYSTEM command. Identify the correct sequence of the required steps.
Show answer and explanation
Correct answer: A. 1, 3, 2, 6, 7, 4, 5
With all control files lost, you shut down the instance if it is still running, then start it in NOMOUNT state because no control file is needed to start an instance. RMAN restores the control file from the autobackup to a new location, and the database is mounted with the restored control file. ALTER SYSTEM SET control_files ... SCOPE=SPFILE then records the new location in the SPFILE so subsequent startups find it. Because a backup control file is now in use, you recover the database and open it with the RESETLOGS option.
Why the other options are wrong
- B. The control file must be restored before the SPFILE can be updated to point to its new location.
- C. This sequence never mounts the database or records the new control file location, both of which are required.
- D. The control file cannot be restored from autobackup until the instance has been started in NOMOUNT state.
- E. A database cannot be mounted without a control file, so mounting before the restore fails.
Question 80
Which three statements are true about space usage alerts? (Choose three.)
Show answer and explanation
Correct answer: C, D, E. Database alerts can provide warnings about low space availability at both tablespace and segment levels; Alerts are not issued for locally managed tablespaces that are offline or in read-only mode; A newly created locally managed tablespace is automatically assigned the default threshold values defined for a database.
Oracle's proactive space management issues server-generated alerts for low space at the tablespace level and for segment-level space conditions, such as a segment that cannot extend or suspended resumable operations. Tablespace threshold alerts are not issued for locally managed tablespaces that are offline or in read-only mode, because their space usage cannot change. When a new locally managed tablespace is created, it automatically inherits the database-wide default warning and critical thresholds (85% and 97% full) unless specific thresholds are set.
Why the other options are wrong
- A. Alerts are raised when either the warning threshold or the critical threshold is crossed, not only the critical one.
- B. For an undo tablespace, space usage is computed from active and unexpired extents, not from user quotas.
Question 81
The HR user receives the following error while inserting data into the sales table: On investigation, you find that the users tablespace uses Automatic Segment Space Management (ASSM). It is the default tablespace for the HR user with an unlimited quota on it. Which two methods would you use to resolve this error? (Choose two.)
Show answer and explanation
Correct answer: A, B. Altering the data file associated with the USERS tablespace to extend automatically; Adding a data file to the USERS tablespace
The exhibit shows ORA-01653: unable to extend table HR.SALES by 128 in tablespace USERS, meaning the tablespace has no free space for the next extent of the table. Since HR has an unlimited quota, the problem is physical space, not quota. The fix is to give the USERS tablespace more space: either enable AUTOEXTEND on its existing data file with ALTER DATABASE DATAFILE ... AUTOEXTEND ON, or add a new data file with ALTER TABLESPACE users ADD DATAFILE. Either method lets the SALES segment allocate the extent it needs.
Why the other options are wrong
- C. Segment space management cannot be changed for an existing tablespace, and it would not add any free space.
- D. Changing HR's default tablespace does not move the existing SALES table, whose inserts still require space in USERS.
- E. Resumable space allocation only suspends the statement for a time instead of failing it; it does not add space to the tablespace.
Question 82
Identify three benefits of Unified Auditing.
Show answer and explanation
Correct answer: A, B, E. Decreased use of storage to store audit trail rows in the database; It improves overall auditing performance; It automatically audits Recovery Manager (RMAN) events.
Unified auditing consolidates the separate audit trails of earlier configurations (the standard, fine-grained, SYS and component trails) into a single trail with one record format, held in the AUDSYS schema, which removes duplicated trails and reduces the storage needed for audit rows. Overall auditing performance improves because, in the default queued-write mode, audit records are buffered in an SGA queue and written to disk periodically instead of synchronously. The unified audit trail also captures Recovery Manager events automatically, without an audit policy having to be created for them.
Why the other options are wrong
- C. Zero-loss auditing is not guaranteed, because records held in the SGA queue in the default queued-write mode can be lost if the instance fails before they are written.
- D. Although the unified audit trail is stored in read-only tables in the AUDSYS schema, it can still be purged or archived by AUDIT_ADMIN users through DBMS_AUDIT_MGMT, so this is not one of the three benefits the question is asking for.
Question 83
You enabled an audit policy by issuing the following statements: SQL> AUDIT POLICY ORA_DATABASE_PARAMETER BY SCOTT; SQL> AUDIT POLICY ORA_DATABASE_PARAMETER BY SYS, SYSTEM; For which database users and for which executions is the audit policy now active? (Choose two.)
Show answer and explanation
Correct answer: A, E. SYS, SYSTEM; Both successful and failed executions
ORA_DATABASE_PARAMETER is a predefined unified audit policy that audits the ALTER DATABASE, ALTER SYSTEM and CREATE SPFILE statements used to change database parameters. The final AUDIT POLICY statement enables it BY SYS, SYSTEM, so the policy is active for the SYS and SYSTEM administrative users. Neither statement includes a WHENEVER SUCCESSFUL or WHENEVER NOT SUCCESSFUL clause, and when that clause is omitted the policy audits the actions regardless of whether they succeed or fail.
Why the other options are wrong
- B. SCOTT is not among the users named in the final AUDIT POLICY statement, and the SCOTT demonstration account is not created by default in an Oracle Database 12c database.
- C. Without a WHENEVER SUCCESSFUL clause, auditing is not restricted to successful executions.
- D. Without a WHENEVER NOT SUCCESSFUL clause, auditing is not restricted to failed executions.
Question 84
You have installed two 64G flash devices to support the Database Smart Flash Cache feature on your database server that is running on Oracle Linux. You have set the DB_FLASH_CACHE_FILE parameter: DB_FLASH_CACHE_FILE= /dev/flash_device_1 , /dev/flash_device_2 How should the DB_FLASH_CACHE_SIZE be configured to use both devices?
Show answer and explanation
Correct answer: B. Set DB_FLASH_CACHE_SIZE = 64G, 64G
Database Smart Flash Cache can use several flash files or devices. The db_flash_cache_file parameter lists them, and db_flash_cache_size must then list a size for each device, in the same order. To use both 64G devices fully, the setting is db_flash_cache_size = 64G, 64G, which assigns 64G to /dev/flash_device_1 and 64G to /dev/flash_device_2.
Why the other options are wrong
- A. A single size value with two flash files is invalid, because each listed file needs its own size.
- C. A single combined total is not accepted; the sizes must be listed per device.
- D. The instance does not size the flash cache automatically; db_flash_cache_size must be set explicitly.
Question 85
Examine the current value for the following parameters in your database instance: SGA_MAX_SIZE = 1024M SGA_TARGET = 700M DB_8K_CACHE_SIZE = 124M LOG_BUFFER = 200M You issue the following command to increase the value of DB_8K_CACHE_SIZE: SQL> ALTER SYSTEM SET DB_8K_CACHE_SIZE=140M; Which statement is true?
Show answer and explanation
Correct answer: B. It succeeds only if memory is available from the autotuned components of SGA.
With SGA_TARGET set to 700M, Automatic Shared Memory Management is active, and manually sized components such as DB_8K_CACHE_SIZE (124M) and LOG_BUFFER (200M) are allocated from within SGA_TARGET, leaving the remainder for the automatically tuned components. Increasing DB_8K_CACHE_SIZE to 140M takes the extra 16M from the automatically sized components, such as the default buffer cache and shared pool. The ALTER SYSTEM command therefore succeeds only if the autotuned components can give up that memory without going below their minimum sizes.
Why the other options are wrong
- A. DB_8K_CACHE_SIZE is a dynamic parameter that can be changed with ALTER SYSTEM while the instance is running.
- C. The 124M and 200M components are already counted inside the 700M SGA_TARGET, and the extra 16M comes from the autotuned components within it.
- D. SGA_TARGET of 700M is well below SGA_MAX_SIZE of 1024M, and this change does not increase the total SGA size.
Question 86
You are administering a database and you receive a requirement to apply the following restrictions: 1. A connection must be terminated after four unsuccessful login attempts by user. 2. A user should not be able to create more than four simultaneous sessions. 3. User session must be terminated after 15 minutes of inactivity. 4. Users must be prompted to change their passwords every 15 days. How would you accomplish these requirements?
Show answer and explanation
Correct answer: C. By creating and assigning a profile to the users and setting the SEC_MAX_FAILED_LOGIN_ATTEMPTS parameter to 4
Session limits and password rules are enforced through a profile assigned to the users. The profile sets SESSIONS_PER_USER 4 to cap simultaneous sessions, IDLE_TIME 15 to end sessions after 15 minutes of inactivity, and PASSWORD_LIFE_TIME 15 to require a password change every 15 days, with resource_limit set to TRUE so the kernel limits are enforced. Setting the sec_max_failed_login_attempts parameter to 4 makes the server drop a connection after four failed authentication attempts on it, which covers the first requirement.
Why the other options are wrong
- A. A secure application role only enables a role after a PL/SQL check and cannot limit sessions, idle time or password lifetime.
- B. remote_os_authent controls remote operating system authentication and enforces none of the listed limits.
- D. Fine-Grained Auditing records data access and remote_login_passwordfile controls password file use, so neither enforces session or password limits.
- E. Resource Manager plans allocate resources among consumer groups and neither expire passwords nor cap each user's simultaneous sessions.
Question 87
You created an encrypted tablespace: SQL> CREATE TABLESPACE securespace DATAFILE '/home/user/oradata/secure01.dbf' SIZE 150M ENCRYPTION USING '3DES168' DEFAULT STORAGE(ENCRYPT); You then closed the encryption wallet because you were advised that this is secure. Later in the day, you attempt to create the EMPLOYEES table in the SECURESPACE tablespace with the SALT option on the EMPNAME column. Which is true about the result?
Show answer and explanation
Correct answer: B. It generates an error when creating the table because the wallet is closed.
The exhibit creates SECURESPACE with ENCRYPTION USING '3DES168' and DEFAULT STORAGE(ENCRYPT), which depends on the master encryption key held in the wallet. Once the wallet is closed, every Transparent Data Encryption operation that needs the master key stops working. Creating a table with an encrypted column using SALT requires the master key to generate and protect the table key, so the CREATE TABLE statement fails with ORA-28365: wallet is not open.
Why the other options are wrong
- A. The table is not created at all, because defining an encrypted column needs the open wallet at creation time.
- C. The wallet must be open for any encryption or decryption operation, not only when the tablespace is created.
- D. SALT is a valid column encryption option for a table in an encrypted tablespace; the error comes from the closed wallet.
Question 88
On your Oracle 12c database, you issue the following commands to create indexes: SQL> CREATE INDEX oe.ord_customer_ix1 ON oe.orders (customers_id, sales_rep_id) INVISIBLE; SQL> CREATE BITMAP INDEX oe.ord_customer_ix2 ON oe.orders (customers_id, sales_rep_id); Which two statements are correct? (Choose two.)
Show answer and explanation
Correct answer: A, E. Both the indexes are created; however, only the ORD_CUSTOMER index is visible; Both the indexes are updated when a new row is inserted, updated, or deleted in the orders table.
Oracle Database 12c allows more than one index on the same column list provided the indexes differ in type (here a B-tree and a bitmap index) and only one of them is visible at a time. ORD_CUSTOMER_IX1 is created as an invisible B-tree index, so ORD_CUSTOMER_IX2, the bitmap index, can be created as the visible one, and both indexes exist with only ORD_CUSTOMER_IX2 visible. An invisible index is still a normal index structure: the database maintains it for every INSERT, UPDATE and DELETE on ORDERS, exactly like the visible index, so both are updated by DML.
Why the other options are wrong
- B. The optimizer ignores the invisible ORD_CUSTOMER_IX1 index unless optimizer_use_invisible_indexes is set to TRUE, so only the visible index is evaluated.
- C. The bitmap index is also created, because indexes of different types on the same columns are allowed when only one is visible.
- D. The invisible B-tree index is created successfully as well; being invisible does not prevent its creation.
Question 89
Identify three valid methods of opening pluggable databases (PDBs).
Show answer and explanation
Correct answer: A, E, G. ALTER PLUGGABLE DATABASE OPEN ALL issued from the root; ALTER DATABASE OPEN issued from that PDB; ALTER PLUGGABLE DATABASE OPEN issued from that PDB
ALTER PLUGGABLE DATABASE OPEN ALL, issued from the root, opens every PDB in the CDB. When connected to a PDB, ALTER PLUGGABLE DATABASE OPEN opens that PDB, and ALTER DATABASE OPEN issued from the same PDB does the same thing, because ALTER DATABASE statements run inside a PDB are supported for backward compatibility with applications written for non-CDBs. In each case the PDB opens read/write by default unless the CDB is a physical standby database.
Why the other options are wrong
- B. OPEN ALL can be issued only from the root, because a session in a PDB can affect only that PDB.
- C. The seed is read only and cannot be used to open other PDBs.
- D. ALTER DATABASE has no PDB OPEN clause; PDBs are opened from the root with ALTER PLUGGABLE DATABASE.
- F. A session in one PDB cannot change the open mode of another PDB.
Question 90
You are connected to a pluggable database (PDB) as a common user with DBA privileges. The STATISTICS_LEVEL parameter is PDB_MODIFIABLE. You execute the following: SQL > ALTER SYSTEM SET STATISTICS_LEVEL = ALL SID = * SCOPE = SPFILE; Which is true about the result of this command?
Show answer and explanation
Correct answer: A. The STATISTICS_LEVEL parameter is set to all whenever this PDB is re-opened.
When a PDB-modifiable parameter such as statistics_level is set with ALTER SYSTEM while connected to a PDB, the setting applies only to that PDB. With SCOPE=SPFILE, the value is not written to the CDB server parameter file; it is stored in the CDB data dictionary for that PDB, and it takes effect the next time this PDB is opened, overriding the value inherited from the root.
Why the other options are wrong
- B. A parameter set inside a PDB affects only that PDB, not other PDBs.
- C. The value belongs to this PDB and applies when the PDB is opened, not to the whole CDB on restart.
- D. The statement is not ignored; PDB parameter values are stored in the CDB data dictionary and applied when the PDB opens.
Question 91
What is the effect of specifying the "ENABLE PLUGGABLE DATABASE" clause in a "CREATE DATABASE" statement?
Show answer and explanation
Correct answer: B. It will create a CDB with root opened and seed read only.
CREATE DATABASE with the ENABLE PLUGGABLE DATABASE clause creates a CDB, and Oracle automatically creates the root, CDB$ROOT, and the seed, PDB$SEED, along with it. When the statement completes, the root is open and the seed is open in read-only mode, ready to serve as the template for new PDBs. The statement itself creates no user PDB.
Why the other options are wrong
- A. The seed is also created and opened read only, so the root is not the only container open.
- C. The statement creates no user PDB, and the seed is opened read only rather than read/write.
- D. A CDB is a standalone container database and is never plugged into another CDB.
- E. The seed is opened read only, not left in the mounted state.
Question 92
Your multitenant container database (CDB) contains pluggable databases (PDBs), you are connected to the HR_PDB. You execute the following command: SQL> CREATE UNDO TABLESPACE undotb01 DATAFILE u01/oracle/rddb1/undotbs01.dbf SIZE 60M AUTOEXTEND ON; What is the result?
Show answer and explanation
Correct answer: E. It executes successfully but neither tablespace nor the data file is created.
With the shared undo model, each CDB instance uses a single active undo tablespace that belongs to the root, and all PDBs share it. When the current container is a PDB such as HR_PDB, a CREATE UNDO TABLESPACE statement completes without returning an error, but it is effectively ignored: no undo tablespace and no data file are created.
Why the other options are wrong
- A. PDBs use the undo tablespace of the root, so no undo tablespace is created in HR_PDB.
- B. The statement does not raise an error, and the root of a CDB can contain more than one undo tablespace.
- C. CONTAINER=ALL is not a clause of CREATE UNDO TABLESPACE, and the statement does not fail.
- D. CONTAINER=CURRENT is not required, and the statement completes without an error.
Question 93
Which three statements are true concerning the multitenant architecture? (Choose three.)
Show answer and explanation
Correct answer: B, D, G. A PDB can have a private temp tablespace; Log switches occur only at the multitenant container database (CDB) level; Instance recovery is always performed at the CDB level.
A CDB has one instance, one set of background processes, one control file and one set of online redo logs shared by the root and all PDBs. Because the redo is shared, log switches happen only at the CDB level, and instance recovery after a failure is always performed for the whole CDB. Tablespaces, by contrast, belong to individual containers: the root and each PDB have their own SYSTEM and SYSAUX tablespaces, and a PDB can have its own temporary tablespace in addition to the default temporary tablespace of the CDB.
Why the other options are wrong
- A. The root and all PDBs share a single set of background processes.
- C. Each PDB has its own SYSAUX tablespace; SYSAUX is not shared between PDBs.
- E. The standard block size is set by db_block_size for the whole CDB, so PDBs cannot have different default block sizes.
- F. Each PDB has its own SYSTEM tablespace holding its own data dictionary metadata.
Question 94
You create a table with the PERIOD FOR clause to enable the use of the Temporal Validity feature of Oracle Database 12c. Examine the table definition: CREATE TABLE employees (empno NUMBER, salary NUMBER, deptid NUMBER, name VARCHAR2(100), PERIOD FOR employee_time); Which three statements are true concerning the use of the Valid Time Temporal feature for the EMPLOYEES table? (Choose three.)
Show answer and explanation
Correct answer: A, B, C. The valid time columns employee_time_start and employee_time_end are automatically created; The same statement may filter on both transaction time and valid temporal time by using the AS OF TIMESTAMP and PERIOD FOR clauses; The valid time columns are not populated by the Oracle Server automatically.
The exhibit defines EMPLOYEES with only a PERIOD FOR employee_time clause and no start or end columns, so Oracle automatically creates two hidden columns, employee_time_start and employee_time_end, to hold the valid time. These columns are not populated by the server; the application must set them, and a NULL value means the row is valid without a limit at that end. Because valid time and transaction time are independent, a single query can combine AS OF TIMESTAMP (Flashback Query) with AS OF PERIOD FOR employee_time to filter on both.
Why the other options are wrong
- D. The automatically created valid time columns are hidden and do not appear when the table is described.
- E. ENABLE_AT_VALID_TIME sets a session-level valid time filter on the rows that queries return; it is not a visibility setting that governs DML and DDL operations.
Question 95
Your database is configured in ARCHIVELOG mode. Examine the RMAN configuration parameters: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default Examine the command: RMAN> BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT; What is the outcome?
Show answer and explanation
Correct answer: B. It executes successfully and creates a backup set of the database along with archived log files and then deletes the original archived log files.
The exhibit shows CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET, control file autobackup OFF and one archived log backup copy for disk. BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT therefore creates backup sets of the database and of the archived redo logs. DELETE INPUT then removes the original archived log files after they have been backed up successfully, which frees space in the archiving destination.
Why the other options are wrong
- A. DELETE INPUT is valid for archived logs regardless of backup type and is not limited to BACKUP AS BACKUPSET.
- C. The configured backup type is BACKUPSET, so RMAN creates backup sets, not image copies.
- D. DELETE INPUT is not limited to BACKUP AS COPY and works with backup sets.
Question 96
Which three statements are true about the purpose of checkpoints? (Choose three.)
Show answer and explanation
Correct answer: B, D, E. They ensure that all the dirty buffers are written to disk during a normal shutdown; They ensure that dirty buffers in the buffer cache are written to disk regularly; They reduce the time required for recovery in case of an instance failure.
Checkpoints make DBWn write dirty buffers from the buffer cache to the data files. Regular incremental checkpoints ensure that dirty buffers are written to disk at regular intervals, advancing the checkpoint position, which limits how much redo must be applied and so reduces the time needed for instance recovery after a failure. During a consistent shutdown such as SHUTDOWN NORMAL, a full checkpoint ensures all dirty buffers are written to disk before the database closes.
Why the other options are wrong
- A. Uncommitted transactions are rolled back using undo during instance recovery, not by checkpoints.
- C. Instance recovery starts automatically at the next startup; the checkpoint only determines where redo application begins.
That was 96 of 382.
The full Oracle 1Z0-062 pack has all 382 questions, each with the answer, the explanation and why the other options are wrong, plus a questions-only copy for timed runs. US$39, paid once, with free monthly updates and a pass-or-your-money-back guarantee.
Not ready yet? Get the first 191 free as one PDF by email. Get the Half Pack →