Oracle Test Bobby Flashcards
1
Q
Which three statements are true about a job chain? (Choose three.)
A. It can contain a nested chain of jobs.
B. It can be used to implement dependency-based scheduling.
C. It cannot invoke the same program or nested chain in multiple steps in the chain.
D. It cannot have more than one dependency.
E. It can be executed using event-based or time-based schedules.
A
A. It can contain a nested chain of jobs.
B. It can be used to implement dependency-based scheduling.
E. It can be executed using event-based or time-based schedules.
2
Q
Examine the parameters for your database instance:
You execute the following command:
SQL> ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;
Which statement is true in this scenario?
A. Undo data is written to flashback logs after 1200 seconds.
B. Inactive undo data is retained for 1200 seconds even if subsequent transactions fail due to lack of space
in the undo tablespace.
C. You can perform a Flashback Database operation only within the duration of 1200 seconds.
D. An attempt is made to keep inactive undo for 1200 seconds but transactions may overwrite the undo
before that time has elapsed.
A
D. An attempt is made to keep inactive undo for 1200 seconds but transactions may overwrite the undo before that time has elapsed.
3
Q
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 mane “HQ” is used to connect to the remote HQ database.
Which command would you execute to create the database link?
A. CREATE DATABASE LINK HQ USING ‘HQ’;
B. CREATE DATABASE LINK HQ CONNECT TO CURRENT_USER USING `HQ’;
C. CREATE PUBLIC DATABASE LINK HQ CONNECT TO scott IDENTIFIED BY tiger USING ‘HQ’;
D. CREATE DATABASE LINK HQ CONNECT TO scott IDENTIFIED BY tiger USING ‘HQ’;
A
C. CREATE PUBLIC DATABASE LINK HQ CONNECT TO scott IDENTIFIED BY tiger USING ‘HQ’;
4
Q
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?
A. a General Purpose database template with default memory allocation
B. a Data Warehouse database template, with the dedicated server mode option and AMM enabled
C. a General Purpose database template, with the shared server mode option and Automatic Memory Management (AMM) enabled
D. a default database configuration
A
C. a General Purpose database template, with the shared server mode option and Automatic Memory Management (AMM) enabled
5
Q
Which two statements correctly describe the relationship between data files and logical database structures? (Choose two.)
A. A segment cannot span data files.
B. A data file can belong to only one tablespace.
C. An extent cannot span data files.
D. The size of an Oracle data block in a data file should be the same as the size of an OS block.
A
B. A data file can belong to only one tablespace.
C. An extent cannot span data files.
6
Q
Which two tasks can be performed on an external table? (Choose two.)
A. partitioning the table
B. creating an invisible index
C. updating the table by using an update statement
D. creating a public synonym
E. creating a view
A
D. creating a public synonym
E. creating a view
7
Q
Which three factors influence the optimizer’s choice of an execution plan? (Choose three.)
A. the optimizer_mode initialization parameter
B. operating system (OS) statistics
C. cardinality estimates
D. object statistics in the data dictionary
E. fixed baselines
A
A. the optimizer_mode initialization parameter
C. cardinality estimates
D. object statistics in the data dictionary
8
Q
Examine the resources consumed by a database instance whose current Resource Manager plan is displayed.
Which two statements are true? (Choose two.)
A. An attempt to start a new session by a user belonging to DSS_QUERIES fails with an error.
B. An attempt to start a new session by a user belonging to OTHER_GROUPS fails with an error.
C. The CPU_WAIT_TIME column indicates the total time that sessions in the consumer group waited for the CPU due to resource management.
D. The CPU_WAIT_TIME column indicates the total time that sessions in the consumer group waited for the CPU due to I/O waits and latch or enqueue contention.
E. A user belonging to the DSS__QUERIES resource consumer group can create a new session but the session will be queued.
A
C. The CPU_WAIT_TIME column indicates the total time that sessions in the consumer group waited for the CPU due to resource management.
E. A user belonging to the DSS__QUERIES resource consumer group can create a new session but the session will be queued.
9
Q
Which action takes place when a file checkpoint occurs?
A. The checkpoint position is advanced in the checkpoint queue.
B. All buffers for a checkpointed file that were modified before a specific SCN are written to disk by DBWn and the SCN is stored in the control file.
C. The Database Writer process (DBWn) writes all dirty buffers in the buffer cache to data files.
D. The Log Writer process (LGWR) writes all redo entries in the log buffer to online redo log files.
A
C. The Database Writer process (DBWn) writes all dirty buffers in the buffer cache to data files.
10
Q
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?
A. Row movement is enabled.
B. Referential integrity constraints for the table are disabled.
C. No queries are running on this table.
D. Extra disk space equivalent to the size of the segment is available in the tablespace.
E. No pending transaction exists on the table.
A
A. Row movement is enabled.
11
Q
Which statement is true regarding the startup of a database instance?
A. The instance does not start up normally and requires manual media recovery after a shutdown using the abort option.
B. Uncommitted transactions are rolled back during the startup of the database instance after a shutdown using the immediate option.
C. There is no difference in the underlying mechanics of the startup whether the database is shut down by using the immediate option or the abort option.
D. Media recovery is required when the database is shut down by using either the immediate option or the abort option.
E. Instance recovery is not required if the database instance was shut down by using SHUTDOWN IMMEDIATE.
A
E. Instance recovery is not required if the database instance was shut down by using SHUTDOWN IMMEDIATE.
12
Q
Examine the memory-related parameters set in the SPFILE of an Oracle database:
Which statement is true?
A. Only SGA components are sized automatically.
B. Memory is dynamically re-allocated between the SGA and PGA as needed.
C. The size of the PGA cannot grow automatically beyond 500 MB.
D. The value of the MEMORY_TARGET parameter cannot be changed dynamically.
A
B. Memory is dynamically re-allocated between the SGA and PGA as needed.
13
Q
Which two statements are true about extents? (Choose two.)
A. Blocks belonging to an extent can be spread across multiple data files.
B. Data blocks in an extent are logically contiguous but can be non-contiguous on disk.
C. The blocks of a newly allocated extent, although free, may have been used before.
D. Data blocks in an extent are automatically reclaimed for use by other objects in a tablespace when all the rows in a table are deleted.
A
B. Data blocks in an extent are logically contiguous but can be non-contiguous on disk.
C. The blocks of a newly allocated extent, although free, may have been used before.
14
Q
You execute the commands:
Which two statements are true? (Choose two.)
A. The create user command fails if any role with the name Sidney exists in the database.
B. The user Sidney can connect to the database instance but cannot perform sort operations because no space quota is specified for the temp tablespace.
C. The user Sidney is created but cannot connect to the database instance because no profile is default.
D. The user Sidney can connect to the database instance but requires relevant privileges to create objects in the users tablespace.
E. The user Sidney is created and authenticated by the operating system.
A
A. The create user command fails if any role with the name Sidney exists in the database.
D. The user Sidney can connect to the database instance but requires relevant privileges to create objects in the users tablespace.
15
Q
Your database supports a DSS workload that involves the execution of complex queries:
Currently, the library cache contains the ideal workload for analysis.
You want to analyze some of the queries for an application that are cached in the library cache.
What must you do to receive recommendations about the efficient use of indexes and materialized views to improve query performance?
A. Create a SQL Tuning Set (STS) that contains the queries cached in the library cache and run the SQL Tuning Advisor (STA) on the workload captured in the STS.
B. Run the Automatic Workload Repository Monitor (ADDM).
C. Create an STS that contains the queries cached in the library cache and run the SQL Performance Analyzer (SPA) on the workload captured in the STS.
D. Create an STS that contains the queries cached in the library cache and run the SQL Access Advisor on the workload captured in the STS.
A
D. Create an STS that contains the queries cached in the library cache and run the SQL Access Advisor on the workload captured in the STS.
16
Q
Examine this command:
SQL > exec DBMS_STATS.SET_TABLE_PREFS (SH', CUSTOMERS’, PUBLISH', false’);
Which three statements are true about the effect of this command? (Choose three.)
A. Statistics collection is not done for the CUSTOMERS table when schema stats are gathered.
B. Statistics collection is not done for the CUSTOMERS table when database stats are gathered.
C. Any existing statistics for the CUSTOMERS table are still available to the optimizer at parse time.
D. Statistics gathered on the CUSTOMERS table when schema stats are gathered are stored as pending statistics.
E. Statistics gathered on the CUSTOMERS table when database stats are gathered are stored as pending statistics.
A
C. Any existing statistics for the CUSTOMERS table are still available to the optimizer at parse time.
D. Statistics gathered on the CUSTOMERS table when schema stats are gathered are stored as pending statistics.
E. Statistics gathered on the CUSTOMERS table when database stats are gathered are stored as pending statistics.
17
Q
Examine the following impdp command to import a database over the network from a pre-12c Oracle database (source):
Which three are prerequisites for successful execution of the command? (Choose three.)
A. The import operation must be performed by a user on the target database by a user with the
DATAPUMP_IMP_FULL_DATABASE role, and the database link must connect to a user with the
DATAPUMP_EXP_FULL_DATABASE role on the source database.
B. All the user-defined tablespaces must be in read-only mode on the source database.
C. The export dump file must be created before starting the import on the target database.
D. The source and target database must be running on the same operating system (OS) with the same endianness.
E. The impdp operation must be performed by the same user that performed the expdp operation.
A
A. The import operation must be performed by a user on the target database by a user with the
DATAPUMP_IMP_FULL_DATABASE role, and the database link must connect to a user with the
DATAPUMP_EXP_FULL_DATABASE role on the source database.
B. All the user-defined tablespaces must be in read-only mode on the source database.
D. The source and target database must be running on the same operating system (OS) with the same endianness.
18
Q
Which two are true concerning a multitenant container database with three pluggable database? (Choose two.)
A. All administration tasks must be done to a specific pluggable database.
B. The pluggable databases increase patching time.
C. The pluggable databases reduce administration effort.
D. The pluggable databases are patched together.
E. Pluggable databases are only used for database consolidation.
A
C. The pluggable databases reduce administration effort.
D. The pluggable databases are patched together.
19
Q
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?
A. It fails because the DB_8K_CACHE_SIZE parameter cannot be changed dynamically.
B. It succeeds only if memory is available from the autotuned components if SGA.
C. It fails because an increase in DB_8K_CACHE_SIZE cannot be accommodated within SGA_TARGET.
D. It fails because an increase in DB_8K_CACHE_SIZE cannot be accommodated within SGA_MAX_SIZE.
A
B. It succeeds only if memory is available from the autotuned components if SGA.
20
Q
Which three statements are true concerning unplugging a pluggable database (PDB)? (Choose three.)
A. The PDB must be open in read only mode.
B. The PDB must be dosed.
C. The unplugged PDB becomes a non-CDB.
D. The unplugged PDB can be plugged into the same multitenant container database (CDB)
E. The unplugged PDB can be plugged into another CDB.
F. The PDB data files are automatically removed from disk.
A
B. The PDB must be dosed.
D. The unplugged PDB can be plugged into the same multitenant container database (CDB)
E. The unplugged PDB can be plugged into another CDB.
21
Q
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?
A. All users except SYS
B. All users except SCOTT
C. All users except sys and SCOTT
D. All users except sys, system, and SCOTT
A
B. All users except SCOTT
22
Q
On your Oracle 12c database, you invoked SQL *Loader to load data into the EMPLOYEES table in the HR schema by issuing the following command:
$> sqlldr hr/hr@pdb table=employees
Which two statements are true regarding the command? (Choose two.)
A. It succeeds with default settings if the EMPLOYEES table belonging to HR is already defined in the database.
B. It fails because no SQL *Loader data file location is specified.
C. It fails if the HR user does not have the CREATE ANY DIRECTORY privilege.
D. It fails because no SQL *Loader control file location is specified.
A
A. It succeeds with default settings if the EMPLOYEES table belonging to HR is already defined in the database.
C. It fails if the HR user does not have the CREATE ANY DIRECTORY privilege.
23
Q
After implementing full Oracle Data Redaction, you change the default value for the NUMBER data type as follows:
After changing the value, you notice that FULL redaction continues to redact numeric data with zero.
What must you do to activate the new default value for numeric full redaction?
A. Re-enable redaction policies that use FULL data redaction.
B. Re-create redaction policies that use FULL data redaction.
C. Re-connect the sessions that access objects with redaction policies defined on them.
D. Flush the shared pool.
E. Restart the database instance.
A
E. Restart the database instance.
24
Q
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.)
A. Enable supplemental logging for the database.
B. Specify undo retention guarantee for the database.
C. Create a Flashback Data Archive in the tablespace where the tables are stored.
D. Create a Flashback Data Archive in any suitable tablespace.
E. Enable Flashback Data Archiving for the tables that require tracking.
A
D. Create a Flashback Data Archive in any suitable tablespace.
E. Enable Flashback Data Archiving for the tables that require tracking.
25
You are the DBA supporting an Oracle 11g Release 2 database and wish to move a table containing several DATE, CHAR, VARCHAR2, and NUMBER data types, and the table's indexes, to another tablespace.
The table does not have a primary key and is used by an OLTP application.
Which technique will move the table and indexes while maintaining the highest level of availability to the application?
A. Oracle Data Pump.
B. An ALTER TABLE MOVE to move the table and ALTER INDEX REBUILD to move the indexes.
C. An ALTER TABLE MOVE to move the table and ALTER INDEX REBUILD ONLINE to move the indexes.
D. Online Table Redefinition.
E. Edition-Based Table Redefinition.
D. Online Table Redefinition.
26
To implement Automatic Management (AMM), you set the following parameters:
When you try to start the database instance with these parameter settings, you receive the following error message:
SQL \> startup
ORA-00824: cannot set SGA\_TARGET or MEMORY\_TARGET due to existing internal settings, see alert log for more information.
Identify the reason the instance failed to start.
A. The PGA\_AGGREGATE\_TARGET parameter is set to zero.
B. The STATISTICS\_LEVEL parameter is set to BASIC.
C. Both the SGA\_TARGET and MEMORY\_TARGET parameters are set.
D. The SGA\_MAX\_SIZE and SGA\_TARGET parameter values are not equal.
B. The STATISTICS\_LEVEL parameter is set to BASIC.
27
You upgrade your Oracle database in a multiprocessor environment. As a recommended you execute the following script:
SQL \> @utlrp.sql
Which two actions does the script perform? (Choose two.)
A. Parallel compilation of only the stored PL/SQL code
B. Sequential recompilation of only the stored PL/SQL code
C. Parallel recompilation of any stored PL/SQL code
D. Sequential recompilation of any stored PL/SQL code
E. Parallel recompilation of Java code
F. Sequential recompilation of Java code
C. Parallel recompilation of any stored PL/SQL code
E. Parallel recompilation of Java code
28
Which two statements are true concerning dropping a pluggable database (PDB)? (Choose two.)
A. The PDB must be open in read-only mode.
B. The PDB must be in mount state.
C. The PDB must be unplugged.
D. The PDB data files are always removed from disk.
E. A dropped PDB can never be plugged back into a multitenant container database (CDB).
B. The PDB must be in mount state.
C. The PDB must be unplugged.
29
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?
A. Compare Period ADDM report
B. AWR Compare Period report
C. Active Session History (ASH) report
D. Taking a new snapshot and comparing it with a preserved snapshot
A. Compare Period ADDM report
30
You want to capture column group usage and gather extended statistics for better cardinality estimates for the CUSTOMERS table in the SH schema.
Examine the following steps:
1. Issue the SELECT DBMS\_STATS.CREATE\_EXTENDED\_STATS (`SH', `CUSTOMERS') FROM dual statement.
2. Execute the DBMS\_STATS.SEED\_COL\_USAGE (null, `SH', 500) procedure.
3. Execute the required queries on the CUSTOMERS table.
4. Issue the SELECT DBMS\_STATS.REPORT\_COL\_USAGE (`SH', `CUSTOMERS') FROM dual statement.
Identify the correct sequence of steps.
A. 3, 2, 1, 4
B. 2, 3, 4, 1
C. 4, 1, 3, 2
D. 3, 2, 4, 1
B. 2, 3, 4, 1
31
An application accesses a small lookup table frequently.
You notice that the required data blocks are getting aged out of the default buffer cache.
How would you guarantee that the blocks for the table never age out?
A. Configure the KEEP buffer pool and alter the table with the corresponding storage clause.
B. Increase the database buffer cache size.
C. Configure the RECYCLE buffer pool and alter the table with the corresponding storage clause.
D. Configure Automata Shared Memory Management.
E. Configure Automatic Memory Management.
A. Configure the KEEP buffer pool and alter the table with the corresponding storage clause.
32
You conned using SQL Plus to the root container of a multitenant container database (CDB) with SYSDBA privilege.
The CDB has several pluggable databases (PDBs) open in the read/write mode.
There are ongoing transactions in both the CDB and PDBs.
What happens alter issuing the SHUTDOWN TRANSACTIONAL statement?
A. The shutdown proceeds immediately. The shutdown proceeds as soon as all transactions in the PDBs are either committed or rolled hack.
B. The shutdown proceeds as soon as all transactions in the CDB are either committed or rolled back.
C. The shutdown proceeds as soon as all transactions in both the CDB and PDBs are either committed or rolled back.
D. The statement results in an error because there are open PDBs.
B. The shutdown proceeds as soon as all transactions in the CDB are either committed or rolled back.
33
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?
A. Database replay
B. SQL Tuning Advisor
C. SQL Access Advisor
D. SQL Performance Analyzer
E. Automatic Workload Repository compare reports
D. SQL Performance Analyzer
34
An administrator account is granted the CREATE SESSION and SET CONTAINER system privileges.
A multitenant container database (CDB) instant 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.)
A. You can connect as a common user by using the connect statement.
B. You can connect as a local user by using the connect statement.
C. You can connect by using easy connect.
D. You can connect by using OS authentication.
E. You can connect by using a Net Service name.
F. You can connect as a local user by using the SET CONTAINER statement.
A. You can connect as a common user by using the connect statement.
C. You can connect by using easy connect.
D. You can connect by using OS authentication.
E. You can connect by using a Net Service name.
35
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 to further investigation.
Which three findings would you get from the report? (Choose three.)
A. It detects any configuration change that caused a performance difference in both time periods.
B. It identifies any workload change that caused a performance difference in both time periods.
C. It detects the top wait events causing performance degradation.
D. It shows the resource usage for CPU, memory, and I/O in both time periods.
E. It shows the difference in the size of memory pools in both time periods.
F. It gives information about statistics collection in both time periods.
A. It detects any configuration change that caused a performance difference in both time periods.
B. It identifies any workload change that caused a performance difference in both time periods.
D. It shows the resource usage for CPU, memory, and I/O in both time periods.
36
Which three statements are true about adaptive SQL plan management? (Choose three.)
A. It automatically performs verification or evolves non-accepted plans, in COMPREHENSIVE mode when they perform better than existing accepted plans.
B. The optimizer always uses the fixed plan, if the fixed plan exists in the plan baseline.
C. It adds new, bettor plans automatically as fixed plans to the baseline.
D. The non-accepted plans are automatically accepted and become usable by the optimizer if they perform better than the existing accepted plans.
E. 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.
A. It automatically performs verification or evolves non-accepted plans, in COMPREHENSIVE mode when they perform better than existing accepted plans.
D. The non-accepted plans are automatically accepted and become usable by the optimizer if they perform better than the existing accepted plans.
E. 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.
37
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 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?
A. Execute FLASHBACK TABLE OCA.EXAM\_RESULTS TO BEFORE DROP RENAME TO OCP.EXAM\_RESULTS; connected as SYSTEM.
B. Recover the table using traditional Tablespace Point In Time Recovery.
C. Recover the table using Automated Tablespace Point In Time Recovery.
D. Recovery the table using Database Point In Time Recovery.
E. Execute FLASHBACK TABLE OCA.EXAM\_RESULTS TO BEFORE DROP RENAME TO
EXAM\_RESULTS; connected as the OCP user.
C. Recover the table using Automated Tablespace Point In Time Recovery.
38
You created a new database using the "create database" statement without specifying the "ENABLE PLUGGABLE" clause.
What are two effects of not using the "ENABLE PLUGGABLE database" clause?
A. The database is created as a non-CDB and can never contain a PDB.
B. The database is treated as a PDB and must be plugged into an existing multitenant container database (CDB).
C. The database is created as a non-CDB and can never be plugged into a CDB.
D. The database is created as a non-CDB but can be plugged into an existing CDB.
E. The database is created as a non-CDB but will become a CDB whenever the first PDB is plugged in. and then create PDBs within this CDB.
A. The database is created as a non-CDB and can never contain a PDB.
D. The database is created as a non-CDB but can be plugged into an existing CDB.
39
What is the effect of specifying the "ENABLE PLUGGABLE DATABASE" clause in a "CREATE DATABASE" statement?
A. It will create a multitenant container database (CDB) with only the root opened.
B. It will create a CDB with root opened and seed read only.
C. It will create a CDB with root and seed opened and one PDB mounted.
D. It will create a CDB that must be plugged into an existing CDB.
E. It will create a CDB with root opened and seed mounted.
B. It will create a CDB with root opened and seed read only.
40
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.)
A. LOG\_BUFFER
B. SORT\_AREA\_SIZE
C. JAVA\_POOL\_SIZE
D. STREAMS\_POOL\_SIZE
E. DB\_16K\_CACHE\_SZIE
F. DB\_KEEP\_CACHE\_SIZE
A. LOG\_BUFFER
E. DB\_16K\_CACHE\_SZIE
F. DB\_KEEP\_CACHE\_SIZE
41
In your multitenant container database (CDB) containing pluggable database (PDBs), you granted the CREATE TABLE privilege to the common user C # # A\_ADMIN in root and all PDBs.
You execute the following command from the root container:
SQL \> REVOKE create table FROM C # # A\_ADMIN;
What is the result?
A. It executes successfully and the CREATE TABLE privilege is revoked from C # # A\_ADMIN in root only.
B. It fails and reports an error because the CONTAINER=ALL clause is not used.
C. It excludes successfully and the CREATE TABLE privilege is revoked from C # # A\_ADMIN in root and all PDBs.
D. It fails and reports an error because the CONTAINER=CURRENT clause is not used.
E. It executes successfully and the CREATE TABLE privilege is revoked from C # # A\_ADMIN in all PDBs.
A. It executes successfully and the CREATE TABLE privilege is revoked from C # # A\_ADMIN in root only.
42
Which three statements are true concerning the multitenant architecture? (Choose three.)
A. Each pluggable database (PDB) has its own set of background processes.
B. A PDB can have a private temp tablespace.
C. PDBs can share the sysaux tablespace.
D. Log switches occur only at the multitenant container database (CDB) level.
E. Different PDBs can have different default block sizes.
F. PDBs share a common system tablespace.
G. Instance recovery is always performed at the CDB level.
B. A PDB can have a private temp tablespace.
D. Log switches occur only at the multitenant container database (CDB) level.
G. Instance recovery is always performed at the CDB level.
43
You notice that the elapsed time for an important database scheduler Job is unacceptably long.
```
The job belongs to a scheduler job class and window.
Which two actions would reduce the job's elapsed time? (Choose two.)
```
A. Increasing the priority of the job class to which the job belongs
B. Increasing the job's relative priority within the Job class to which it belongs
C. Increasing the resource allocation for the consumer group mapped to the scheduler job's job class within the plan mapped to the scheduler window
D. Moving the job to an existing higher priority scheduler window with the same schedule and duration
E. Increasing the value of the JOB\_QUEUE\_PROCESSES parameter
F. Increasing the priority of the scheduler window to which the job belongs
B. Increasing the job's relative priority within the Job class to which it belongs
C. Increasing the resource allocation for the consumer group mapped to the scheduler job's job class within the plan mapped to the scheduler window
44
You plan to migrate your database from a File system to Automata Storage Management (ASM) on same platform.
Which two methods or commands would you use to accomplish this task? (Choose two.)
A. RMAN CONVERT command
B. Data Pump Export and import
C. Conventional Export and Import
D. The BACKUP AS COPY DATABASE . . . command of RMAN
E. DBMS\_FILE\_TRANSFER with transportable tablespace
A. RMAN CONVERT command
D. The BACKUP AS COPY DATABASE . . . command of RMAN
45
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.)
A. The seed will be in mount state.
B. The seed will be opened read-only.
C. The seed will be opened read/write.
D. The other PDBs will be in mount state.
E. The other PDBs will be opened read-only.
F. The PDBs will be opened read/write.
B. The seed will be opened read-only.
D. The other PDBs will be in mount state.
46
Your database is open and the LISTENER listener running.
You stopped the wrong listener LISTENER by issuing the following command:
1snrctl \> STOP
What happens to the sessions that are presently connected to the database Instance?
A. They are able to perform only queries.
B. They are not affected and continue to function normally.
C. They are terminated and the active transactions are rolled back.
D. They are not allowed to perform any operations until the listener LISTENER is started.
B. They are not affected and continue to function normally.
47
You execute the following PL/SQL:
Which two statements are true? (Choose two.)
A. Fine-Grained Auditing (FGA) is enabled for the PRICE column in the PRODUCTS table for SELECT statements only when a row with PRICE \> 10000 is accessed.
B. FGA is enabled for the PRODUCTS.PRICE column and an audit record is written whenever a row with PRICE \> 10000 is accessed.
C. FGA is enabled for all DML operations by JIM on the PRODUCTS.PRICE column.
D. FGA is enabled for the PRICE column of the PRODUCTS table and the SQL statements is captured in the FGA audit trial.
A. Fine-Grained Auditing (FGA) is enabled for the PRICE column in the PRODUCTS table for SELECT statements only when a row with PRICE \> 10000 is accessed.
B. FGA is enabled for the PRODUCTS.PRICE column and an audit record is written whenever a row with PRICE \> 10000 is accessed.
48
You execute the following commands to audit database activities:
SQL \> ALTER SYSTEM SET AUDIT\_TRIAL=DB, EXTENDED SCOPE=SPFILE;
SQL \> AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE BY JOHN By SESSION WHENEVER SUCCESSFUL;
Which statement is true about the audit record that generated when auditing after instance restarts?
A. One audit record is created for every successful execution of a SELECT, INSERT OR DELETE command on a table, and contains the SQL text for the SQL Statements.
B. One audit record is created for every successful execution of a SELECT, INSERT OR DELETE command, and contains the execution plan for the SQL statements.
C. One audit record is created for the whole session if john successfully executes a SELECT, INSERT, or DELETE command, and contains the execution plan for the SQL statements.
D. One audit record is created for the whole session if JOHN successfully executes a select command, and contains the SQL text and bind variables used.
E. One audit record is created for the whole session if john successfully executes a SELECT, INSERT, or DELETE command on a table, and contains the execution plan, SQL text, and bind variables used.
A. One audit record is created for every successful execution of a SELECT, INSERT OR DELETE command on a table, and contains the SQL text for the SQL Statements.
49
To enable the Database Smart Flash Cache, you configure the following parameters:
DB\_FLASH\_CACHE\_FILE = `/dev/flash_device_1' , `/dev/flash\_device\_2'
DB\_FLASH\_CACHE\_SIZE=64G
What is the result when you start up the database instance?
A. It results in an error because these parameter settings are invalid.
B. One 64G flash cache file will be used.
C. Two 64G flash cache files will be used.
D. Two 32G flash cache files will be used.
A. It results in an error because these parameter settings are invalid.
50
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?
A. by granting a secure application role to the users
B. by creating and assigning a profile to the users and setting the REMOTE\_OS\_AUTHENT parameter to FALSE
C. By creating and assigning a profile to the users and setting the SEC\_MAX\_FAILED\_LOGIN\_ATTEMPTS parameter to 4
D. By Implementing Fine-Grained Auditing (FGA) and setting the REMOTE\_LOGIN\_PASSWORD\_FILE parameter to NONE.
E. By implementing the database resource Manager plan and setting the SEC\_MAX\_FAILED\_LOGIN\_ATTEMPTS parameters to 4.
C. By creating and assigning a profile to the users and setting the SEC\_MAX\_FAILED\_LOGIN\_ATTEMPTS parameter to 4
51
A senior DBA asked you to execute the following command to improve performance:
SQL\> ALTER TABLE subscribe log STORAGE (BUFFER\_POOL recycle);
You checked the data in the SUBSCRIBE\_LOG table and found that it is a large table containing one million rows.
What could be a reason for this recommendation?
A. The keep pool is not configured.
B. Automatic Workarea Management is not configured.
C. Automatic Shared Memory Management is not enabled.
D. The data blocks in the SUBSCRIBE\_LOG table are rarely accessed.
E. All the queries on the SUBSCRIBE\_LOG table are rewritten to a materialized view.
D. The data blocks in the SUBSCRIBE\_LOG table are rarely accessed.
52
Which three tasks can be automatically performed by the Automatic Data Optimization feature of Information lifecycle Management (ILM)? (Choose three.)
A. Tracking the most recent read time for a table segment in a user tablespace
B. Tracking the most recent write time for a table segment in a user tablespace
C. Tracking insert time by row for table rows
D. Tracking the most recent write time for a table block
E. Tracking the most recent read time for a table segment in the SYSAUX tablespace
F. Tracking the most recent write time for a table segment in the SYSAUX tablespace
A. Tracking the most recent read time for a table segment in a user tablespace
B. Tracking the most recent write time for a table segment in a user tablespace
D. Tracking the most recent write time for a table block
53
You configure your database Instance to support shared server connections.
Which two memory areas that are part of PGA are stored in SGA instead, for shared server connection? (Choose two.)
A. User session data
B. Stack space
C. Private SQL area
D. Location of the runtime area for DML and DDL Statements
E. Location of a part of the runtime area for SELECT statements
A. User session data
C. Private SQL area
54
Which two statements are true about Oracle Managed Files (OMF)? (Choose two.)
A. OMF cannot be used in a database that already has data files created with user-specified directions.
B. The file system directions that are specified by OMF parameters are created automatically.
C. OMF can be used with ASM disk groups, as well as with raw devices, for better file management.
D. OMF automatically creates unique file names for table spaces and control files.
E. OMF may affect the location of the redo log files and archived log files.
D. OMF automatically creates unique file names for table spaces and control files.
E. OMF may affect the location of the redo log files and archived log files.
55
Identify three scenarios in which you would recommend the use of SQL Performance Analyzer to analyze impact on the performance of SQL statements.
A. Change in the Oracle Database version
B. Change in your network infrastructure
C. Change in the hardware configuration of the database server
D. Migration of database storage from non-ASM to ASM storage
E. Database and operating system upgrade
A. Change in the Oracle Database version
C. Change in the hardware configuration of the database server
E. Database and operating system upgrade
56
Which two statements are true about the RMAN validate database command? (Choose two.)
A. It checks the database for intrablock corruptions.
B. It can detect corrupt pfiles.
C. It can detect corrupt spfiles.
D. It checks the database for interblock corruptions.
E. It can detect corrupt block change tracking files.
A. It checks the database for intrablock corruptions.
C. It can detect corrupt spfiles.
57
You are required to migrate your 11.2.0.3 database as a pluggable database (PDB) to a multitenant container database (CDB).
The following are the possible steps to accomplish this task:
1. Place all the user-defined tablespace in read-only mode on the source database.
2. Upgrade the source database to a 12c version.
3. Create a new PDB in the target container database.
4. Perform a full transportable export on the source database with the VERSION parameter set to 12 using the expdp utility.
5. Copy the associated data files and export the dump file to the desired location in the target database.
6. Invoke the Data Pump import utility on the new PDB database as a user with the DATAPUMP\_IMP\_FULL\_DATABASE role and specify the full transportable import options.
7. Synchronize the PDB on the target container database by using the DBMS\_PDS.SYNC\_ODB function.
Identify the correct order of the required steps.
A. 2, 1, 3, 4, 5, 6
B. 1, 3, 4, 5, 6, 7
C. 1, 4, 3, 5, 6, 7
D. 2, 1, 3, 4, 5, 6, 7
E. 1, 5, 6, 4, 3, 2
C. 1, 4, 3, 5, 6, 7
58
Which two statements are true about the Oracle Direct Network File system (DNFS)? (Choose two.)
A. It utilizes the OS file system cache.
B. A traditional NFS mount is not required when using Direct NFS.
C. Oracle Disk Manager can manage NFS on its own, without using the operating kernel NFS driver.
D. Direct NFS is available only in UNIX platforms.
E. Direct NFS can load-balance I/O traffic across multiple network adapters.
C. Oracle Disk Manager can manage NFS on its own, without using the operating kernel NFS driver.
E. Direct NFS can load-balance I/O traffic across multiple network adapters.
59
Which three operations can be performed as multipartition operations in Oracle? (Choose three.)
A. Merge partitions of a list partitioned table
B. Drop partitions of a list partitioned table
C. Coalesce partitions of a hash-partitioned global index.
D. Move partitions of a range-partitioned table
E. Rename partitions of a range partitioned table
F. Merge partitions of a reference partitioned index
A. Merge partitions of a list partitioned table
B. Drop partitions of a list partitioned table
F. Merge partitions of a reference partitioned index
60
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.)
A. SYS sessions, regardless of the roles that are set in the session
B. SYSTEM sessions, regardless of the roles that are set in the session
C. SCOTT sessions, only if the MGR role is set in the session
D. SCOTT sessions, only if the MGR role is granted to SCOTT
E. SCOTT sessions, because he is the owner of the table
F. SYSTEM session, only if the MGR role is set in the session
A. SYS sessions, regardless of the roles that are set in the session
B. SYSTEM sessions, regardless of the roles that are set in the session
D. SCOTT sessions, only if the MGR role is granted to SCOTT
61
What is the result of executing a TRUNCATE TABLE command on a table that has Flashback Archiving enabled?
A. It fails with the ORA-665610 Invalid DDL statement on history-tracked message
B. The rows in the table are truncated without being archived.
C. The rows in the table are archived, and then truncated.
D. The rows in both the table and the archive are truncated.
C. The rows in the table are archived, and then truncated.
62
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:
Which three statements are true concerning the use of the Valid Time Temporal feature for the EMPLOYEES table? (Choose three.)
A. The valid time columns employee\_time\_start and employee\_time\_end are automatically created.
B. The same statement may filter on both transaction time and valid temporal time by using the AS OF TIMESTAMP and PERIOD FOR clauses.
C. The valid time columns are not populated by the Oracle Server automatically.
D. The valid time columns are visible by default when the table is described.
E. Setting the session valid time using DBMS\_FLASHBACK\_ARCHIVE.ENABLE\_AT\_VALID\_TIME sets the visibility for data manipulation language (DML), data definition language (DDL), and queries
performed by the session.
A. The valid time columns employee\_time\_start and employee\_time\_end are automatically created.
B. The same statement may filter on both transaction time and valid temporal time by using the AS OF TIMESTAMP and PERIOD FOR clauses.
C. The valid time columns are not populated by the Oracle Server automatically.
63
Which three statements are true regarding the use of the Database Migration Assistant for Unicode (DMU)? (Choose three.)
A. A DBA can check specific tables with the DMU
B. The database to be migrated must be opened read-only.
C. The release of the database to be converted can be any release since 9.2.0.8.
D. The DMU can report columns that are too long in the converted characterset.
E. The DMU can report columns that are not represented in the converted characterset.
A. A DBA can check specific tables with the DMU
D. The DMU can report columns that are too long in the converted characterset.
E. The DMU can report columns that are not represented in the converted characterset.
64
Oracle Grid Infrastructure for a stand-alone 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?
A. When you start an instance on a high with SQL \*Plus dependent listeners and ASM disk groups are automatically started.
B. When a database instance is started by using the SRVCTL utility and listener startup fails, the instance is still started.
C. When a database is created by using SQL\* Plus, it is automatically added to the Oracle Restart configuration.
D. When you create a database service by modifying the SERVICE\_NAMES initialization parameter, it is automatically added to the Oracle Restart configuration.
B. When a database instance is started by using the SRVCTL utility and listener startup fails, the instance is still started.
65
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.)
A. Backup change tracking will sometimes reduce I/O performed during cumulative incremental backups.
B. The change tracking file must always be backed up when you perform a full database backup.
C. Block change tracking will always reduce I/O performed during cumulative incremental backups.
D. More than one database block may be read by an incremental backup for a change made to a single block.
E. 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.
A. Backup change tracking will sometimes reduce I/O performed during cumulative incremental backups.
D. More than one database block may be read by an incremental backup for a change made to a single block.
E. 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.
66
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.)
A. Maximum Undo per consumer group
B. Maximum Idle time
C. Parallel server limit
D. CPU
E. Exadata I/O
F. Local file system I/O
C. Parallel server limit
D. CPU
E. Exadata I/O
67
Which three activities are supported by the Data Recovery Advisor? (Choose three.)
A. Advising on block checksum failures
B. Advising on inaccessible control files
C. Advising on inaccessible block change tracking files
D. Advising on empty password files
E. Advising on invalid block header field values
A. Advising on block checksum failures
B. Advising on inaccessible control files
E. Advising on invalid block header field values
68
You created an encrypted tablespace:
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 EMPLOYEE column.
Which is true about the result?
A. It creates the table successfully but does not encrypt any inserted data in the EMPNAME column because the wallet must be opened to encrypt columns with SALT.
B. It generates an error when creating the table because the wallet is closed.
C. It creates the table successfully, and encrypts any inserted data in the EMPNAME column because the wallet needs to be open only for tablespace creation.
D. It generates error when creating the table, because the salt option cannot be used with encrypted tablespaces.
B. It generates an error when creating the table because the wallet is closed.
69
Which two statements are true when row archival management is enabled? (Choose two.)
A. The ORA\_ARCHIVE\_STATE column visibility is controlled by the ROW ARCHIVAL VISIBILITY session parameter.
B. The ORA\_ARCHIVE\_STATE column is updated manually or by a program that could reference activity tracking columns, to indicate that a row is no longer considered active.
C. The ROW ARCHIVAL VISIBILITY session parameter defaults to active rows only.
D. The ORA\_ARCHIVE\_STATE column is visible if referenced in the select list of a query.
E. The ORA\_ARCHIVE\_STATE column is updated automatically by the Oracle Server based on activity tracking columns, to Indicate that a row is no longer considered active.
C. The ROW ARCHIVAL VISIBILITY session parameter defaults to active rows only.
D. The ORA\_ARCHIVE\_STATE column is visible if referenced in the select list of a query.
70
A warehouse fact table in your Oracle 12c Database is range-partitioned by month and accessed frequently with queries that span multiple partitions
The table has a local prefixed, range partitioned index.
Some of these queries access very few rows in some partitions and all the rows in other partitions, but these queries still perform a full scan for all accessed partitions.
This commonly occurs when the range of dates begins at the end of a month or ends close to the start of a month.
You want an execution plan to be generated that uses indexed access when only a few rows are accessed from a segment, while still allowing full scans for segments where many rows are returned.
Which three methods could transparently help to achieve this result? (Choose three.)
A. Using a partial local Index on the warehouse fact table month column with indexing disabled to the table partitions that return most of their rows to the queries.
B. Using a partial local Index on the warehouse fact table month column with indexing disabled for the table partitions that return a few rows to the queries.
C. Using a partitioned view that does a UNION ALL query on the partitions of the warehouse fact table, which retains the existing local partitioned column.
D. Converting the partitioned table to a partitioned view that does a UNION ALL query on the monthly tables, which retains the existing local partitioned column.
E. Using a partial global index on the warehouse fact table month column with indexing disabling for the table partitions that return most of their rows to the queries.
F. Using a partial global index on the warehouse fact table month column with indexing disabled for the table partitions that return a few rows to the queries.
A. Using a partial local Index on the warehouse fact table month column with indexing disabled to the table partitions that return most of their rows to the queries.
C. Using a partitioned view that does a UNION ALL query on the partitions of the warehouse fact table, which retains the existing local partitioned column.
E. Using a partial global index on the warehouse fact table month column with indexing disabling for the table partitions that return most of their rows to the queries.
71
You use the segment advisor to help determine objects for which space may be reclaimed.
Which three statements are true about the advisor given by the segment advisor? (Choose three.)
A. It may advise the use of online table redefinition for tables in dictionary managed tablespace.
B. It may advise the use of segment shrink for tables in dictionary managed tablespaces it the no chained rows.
C. It may advise the use of online table redefinition for tables in locally managed tablespaces
D. It will detect and advise about chained rows.
E. It may advise the use of segment shrink for free list managed tables.
A. It may advise the use of online table redefinition for tables in dictionary managed tablespace.
C. It may advise the use of online table redefinition for tables in locally managed tablespaces
D. It will detect and advise about chained rows.
72
In your multitenant container database (CDB) containing same pluggable databases (PDBs), you execute the following commands in the root container:
Which two statements are true? (Choose two.)
A. The C # # ROLE1 role is created in the root database and all the PDBs.
B. The C # # ROLE1 role is created only in the root database because the container clause is not used.
C. Privileges are granted to the C##A\_ADMIN user only in the root database.
D. Privileges are granted to the C##A\_ADMIN user in the root database and all PDBs.
E. The statement for granting a role to a user fails because the CONTAINER clause is not used.
A. The C # # ROLE1 role is created in the root database and all the PDBs.
C. Privileges are granted to the C##A\_ADMIN user only in the root database.
73
Flashback is enabled for your multitenant container database (CDB), which contains two pluggable database (PDBs).
A local user was accidently dropped from one of the PDBs.
You want to flash back the PDB to the time before the local user was dropped.
You connect to the CDB and execute the following commands:
SQL \> SHUTDOWN IMMEDIATE
SQL \> STARTUP MOUNT
SQL \> FLASHBACK DATABASE to TIME "TO\_DATE (`08/20/12' , `MM/DD/YY')";
Examine following commands:
1. ALTER PLUGGABLE DATABASE ALL OPEN;
2. ALTER DATABASE OPEN;
3. ALTER DATABASE OPEN RESETLOGS;
Which command or commands should you execute next to allow updates to the flashback back schema?
A. Only 1
B. Only 2
C. Only 3
D. 3 and 1
E. 1 and 2
D. 3 and 1
74
Examine the commands executed to monitor database operations:
$\> conn sys oracle/oracle@prod as sysdba
SQL \> VAR eid NUMBER
SQL \> EXEC: eid := DBMS\_SQL\_MONITOR.BEGIN\_OPERATION (`batch_job' , FORCED_TRACKING =\> `Y');
Which two statements are true? (Choose two.)
A. Database operations will be monitored only when they consume a significant amount of resource.
B. Database operations for all sessions will be monitored.
C. Database operations will be monitored only if the STATISTICS\_LEVEL parameter is set to TYPICAL
and CONTROL\_MANAGEMENT\_PACK\_ACCESS is set DIAGNISTIC + TUNING.
D. Only DML and DDL statements will be monitored for the session.
E. All subsequent statements in the session will be treated as one database operation and will be monitored.
C. Database operations will be monitored only if the STATISTICS\_LEVEL parameter is set to TYPICAL
and CONTROL\_MANAGEMENT\_PACK\_ACCESS is set DIAGNISTIC + TUNING.
E. All subsequent statements in the session will be treated as one database operation and will be monitored.
75
Which three statements are true about the working of system privileges in a multitenant control database (CDB) that has pluggable databases (PDBs)? (Choose three.)
A. System privileges apply only to the PDB in which they are used.
B. Local users cannot use local system privileges on the schema of a common user.
C. The granter of system privileges must possess the set container privilege.
D. Common users connected to a PDB can exercise privileges across other PDBs.
E. 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
A. System privileges apply only to the PDB in which they are used.
C. The granter of system privileges must possess the set container privilege.
E. 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
76
You are about to plug a multi-terabyte non-CDB into an existing multitenant container database (CDB) as a pluggable database (PDB).
**The characteristics of the non-CDB are as follows:**
Version: Oracle Database 12c Releases 1 64-bit
Character set: WE8ISO8859P15
National character set: AL16UTF16
O/S: Oracle Linux6 64-bit
**The characteristics of the CDB are as follows:**
Version: Oracle Database 12c Release 1 64-bit
Character set: AL32UTF8
O/S: Oracle Linux 6 64-bit
Which technique should you use to minimize down time while plugging this non-CDB into the CDB?
A. Transportable database
B. Transportable tablespace
C. Data Pump full export / import
D. The DBMS\_PDB package
E. RMAN
D. The DBMS\_PDB package
77
Which three statements are true about SQL plan directives? (Choose three.)
A. They are tied to a specific statement or SQL ID.
B. They instruct the maintenance job to collect missing statistics or perform dynamic sampling to generate a more optimal plan.
C. They are used to gather only missing statistics.
D. They are created for a query expression where statistics are missing or the cardinality estimates by the optimizer are incorrect.
E. They instruct the optimizer to create only column group statistics.
F. Improve plan accuracy by persisting both compilation and execution statistics in the SYSAUX tablespace.
B. They instruct the maintenance job to collect missing statistics or perform dynamic sampling to generate a more optimal plan.
D. They are created for a query expression where statistics are missing or the cardinality estimates by the optimizer are incorrect.
F. Improve plan accuracy by persisting both compilation and execution statistics in the SYSAUX tablespace.
78
Examine these two statements:
Which three are true about the MRKT tablespace? (Choose three.)
A. The MRKT tablespace is created as a small file tablespace, because the file size is less than the minimum required for big file files.
B. The MRKT tablespace may be dropped if it has no contents.
C. Users who were using the old default tablespace will have their default tablespaces changed to the MRKT tablespace.
D. No more data files can be added to the tablespace.
E. The relative file number of the tablespace is not stored in rowids for the table rows that are stored in the MRKT tablespace.
C. Users who were using the old default tablespace will have their default tablespaces changed to the MRKT tablespace.
D. No more data files can be added to the tablespace.
E. The relative file number of the tablespace is not stored in rowids for the table rows that are stored in the MRKT tablespace.
79
In your database, you want to ensure that idle sessions that are blocking active are automatically terminated after a specified period of time.
How would you accomplish this?
A. Setting a metric threshold
B. Implementing Database Resource Manager
C. Enabling resumable timeout for user sessions
D. Decreasing the value of the IDLE\_TIME resource limit in the default profile
B. Implementing Database Resource Manager
80
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.)
A. It records the usernames and passwords of users when granted the DBA role.
B. It contains the usernames and passwords of users for whom auditing is enabled.
C. Is used by Oracle to authenticate users for remote database administration.
D. It records the usernames and passwords of all users when they are added to the OSDBA or OSOPER operating system groups.
E. It supports the SYSBACKUP, SYSDG, and SYSKM system privileges.
C. Is used by Oracle to authenticate users for remote database administration.
E. It supports the SYSBACKUP, SYSDG, and SYSKM system privileges.
81
Identify two situations in which the alert log file is updated.
A. Running a query on a table returns ORA-600: Internal Error.
B. Inserting a value into a table returns ORA-01722: invalid number.
C. Creating a table returns ORA-00955: name us already in used by an existing object.
D. Inserting a value into a table returns ORA-00001: unique constraint (SYS.OK\_TECHP) violated.
E. Rebuilding an index using ALTER INDEX . . . REBUILD fails with an ORA-01578: ORACLE data block corrupted (file # 14, block # 50) error.
A. Running a query on a table returns ORA-600: Internal Error.
E. Rebuilding an index using ALTER INDEX . . . REBUILD fails with an ORA-01578: ORACLE data block corrupted (file # 14, block # 50) error.
82
Examine the query and its output executed In an RDBMS Instance:
Which three statements are true about the users (other than sys) in the output? (Choose three.)
A. The C # # B\_ADMIN user can perform all backup and recovery operations using RMAN only.
B. The C # # C\_ADMIN user can perform the data guard operation with Data Guard Broker.
C. The C # # A\_ADMIN user can perform wallet operations.
D. The C # # D\_ADMIN user can perform backup and recovery operations for Automatic Storage Management (ASM).
E. The C # # B\_ADMIN user can perform all backup and recovery operations using RMAN or SQL\* Plus.
B. The C # # C\_ADMIN user can perform the data guard operation with Data Guard Broker.
D. The C # # D\_ADMIN user can perform backup and recovery operations for Automatic Storage Management (ASM).
E. The C # # B\_ADMIN user can perform all backup and recovery operations using RMAN or SQL\* Plus.
83
In your Database, the TBS PERCENT USED parameter is set to 60 and the TBS PERCENT FREE parameter is set to 20.
Which two storage-tiering actions might be automated when using information Lifecycle Management (ILM) to automate data movement? (Choose two.)
A. The movement of all segments to a target tablespace with a higher degree of compression, on a different storage tier, when the source tablespace exceeds TBS PERCENT USED
B. Setting the target tablespace to read-only
C. The movement of some segments to a target tablespace with a higher degree of compression, on a different storage tier, when the source tablespace exceeds TBS PERCENT USED
D. Setting the target tablespace offline
E. The movement of some blocks to a target tablespace with a lower degree of compression, on a different storage tier, when the source tablespace exceeds TBS PERCENT USED
B. Setting the target tablespace to read-only
C. The movement of some segments to a target tablespace with a higher degree of compression, on a different storage tier, when the source tablespace exceeds TBS PERCENT USED
84
Which three statements are true about Flashback Database? (Choose three.)
A. Flashback logs are written sequentially, and are archived.
B. Flashback Database uses a restored control file to recover a database.
C. The Oracle database automatically creates, deletes, and resides flashback logs in the Fast Recovery Area.
D. Flashback Database can recover a database to the state that it was in before a reset logs operation.
E. Flashback Database can recover a data file that was dropped during the span of time of the flashback.
F. Flashback logs are used to restore to the blocks before images, and then the redo data may be used to roll forward to the desired flashback time.
C. The Oracle database automatically creates, deletes, and resides flashback logs in the Fast Recovery Area.
D. Flashback Database can recover a database to the state that it was in before a reset logs operation.
F. Flashback logs are used to restore to the blocks before images, and then the redo data may be used to roll forward to the desired
85
Which statement is true about Enterprise Manager (EM) express in Oracle Database 12c?
A. By default, EM express is available for a database after database creation.
B. You can use EM express to manage multiple databases running on the same server.
C. You can perform basic administrative tasks for pluggable databases by using the EM express interface.
D. You cannot start up or shut down a database Instance by using EM express.
E. You can create and configure pluggable databases by using EM express.
D. You cannot start up or shut down a database Instance by using EM express.
86
In which two scenarios do you use SQL\* Loader to load data? (Choose two.)
A. Transform the data while it is being loaded into the database.
B. Use transparent parallel processing without having to split the external data first.
C. Load data into multiple tables during the same load statement.
D. Generate unique sequential key values in specified columns.
C. Load data into multiple tables during the same load statement.
D. Generate unique sequential key values in specified columns.
87
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?
A. The STATISTICS\_LEVEL parameter is set to all whenever this PDB is re-opened.
B. The STATISTICS\_LEVEL parameter is set to ALL whenever any PDB is reopened.
C. The STATISTICS\_LEVEL parameter is set to all whenever the multitenant container database (CDB) is restarted.
D. Nothing happens; because there is no SPFILE for each PDB, the statement is ignored.
A. The STATISTICS\_LEVEL parameter is set to all whenever this PDB is re-opened.
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.)
A. Both the indexes are created; however, only the ORD\_COSTOMER index is visible.
B. The optimizer evaluates index access from both the Indexes before deciding on which index to use for query execution plan.
C. Only the ORD\_CUSTOMER\_IX1 index is created.
D. Only the ORD\_CUSTOMER\_IX2 index is created.
E. Both the indexes are updated when a new row is inserted, updated, or deleted In the orders table.
A. Both the indexes are created; however, only the ORD\_COSTOMER index is visible.
E. Both the indexes are updated when a new row is inserted, updated, or deleted In the orders table.
89
Identify three benefits of Unified Auditing.
A. Decreased use of storage to store audit trail rows in the database.
B. It improves overall auditing performance.
C. It guarantees zero-loss auditing.
D. The audit trail cannot be easily modified because it is read-only.
E. It automatically audits Recovery Manager (RMAN) events.
B. It improves overall auditing performance.
D. The audit trail cannot be easily modified because it is read-only.
E. It automatically audits Recovery Manager (RMAN) events.
90
You upgraded from a previous Oracle database version to 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?
A. Examine the Active Session History (ASH) reports for the time period of the ETL or batch reporting runs.
B. Enable SQL tracing for the queries in the ETL and batch reporting queries and gather diagnostic data from the trace file.
C. Enable real-time SQL monitoring for ETL jobs and gather diagnostic data from the V$SQL\_MONITOR view.
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.
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.
95
Which three statements are true about the Pre-Upgrade Information Tool? (Choose three.)
A. It generates a script to recompile invalid objects post-upgrade.
B. The preupgrade\_fixups.sql script is created to list and describe issues in the source database.
C. A log file, preupgrade.log, is created that contains the output of the Pre-Upgrade Information tool.
D. It checks for required tablespaces and if they are not available, creates them automatically.
E. The preupgrade\_fixups.sql script is executed automatically to fix issues in the source database.
F. The postupgrade\_fixups.sql script is created to address issues that can be fixed after a database has been upgraded.
B. The preupgrade\_fixups.sql script is created to list and describe issues in the source database.
C. A log file, preupgrade.log, is created that contains the output of the Pre-Upgrade Information tool.
F. The postupgrade\_fixups.sql script is created to address issues that can be fixed after a database has been upgraded.
96
Which activity is audited by default and recorded in the operating system audit trail irrespective of whether or not database auditing is enabled?
A. execution of SQL statements by users connected with the SYSDBA privilege
B. creation of a fine-grained audit policy
C. configuration of unified auditing mode
D. usage of the AUDIT statement
A. execution of SQL statements by users connected with the SYSDBA privilege
97
You want to create a role that:
* is protected from unauthorized usage
* does not use a password embedded in the application source code or stored in a table
* is enabled for a user based on security policies defined in a PL/SQL package
How would you create this role?
A. as a secure application role
B. with definer's rights
C. with global authentication
D. with external authentication
A. as a secure application role
98
Your database instance is started by using a server parameter file (SPFILE).
You execute the following command to change the value of the LOG\_BUFFER initialization parameter:
ALTER SYSTEM SET LOG\_BUFFER=32 M;
What is the outcome of this command?
A. The parameter value is changed and it comes into effect as soon as space becomes available in the SGA.
B. It returns an error because the value of this parameter cannot be changed dynamically.
C. The parameter value is changed and it comes into effect at the next instance startup.
D. It returns an error because SCOPE should be set to MEMORY
B. It returns an error because the value of this parameter cannot be changed dynamically.
99
You want to create a database and you have the following:
* Oracle Grid Infrastructure is installed and configured.
* Oracle Database Vault is installed in ORACLE\_HOME to be used for this database.
* Oracle Enterprise Manager Cloud Control is available and an agent is deployed on the database server.
Examine the requirements:
1. configuring the database instance to support shared server mode
2. using Automatic Storage Management (ASM) for storing database files.
3. configuring a naming method to help a remote user connect to a database instance
4. configuring the Fast Recovery Area
5. configuring Database Vault
6. configuring Enterprise Manager (EM) Database Express
7. registering with EM Cloud Control
8. configuring remote log archive destinations
9. enabling daily incremental backups
10. configuring a nondefault block size for nondefault block size tablespaces
Which of these requirements can be met while creating a database by using the Database Configuration Assistant (DBCA)?
A. 1, 2, 4, 5, 7, 8, 9 and 10
B. 1, 2, 4, 5, 6 and 7
C. 1, 2, 3, 8, 9 and 10
D. 1, 2, 3, 4, 6, 8, 9 and 10
E. 1, 2, 4, 5, 6, 7 and 8
D. 1, 2, 3, 4, 6, 8, 9 and 10
100
You want execution of large database operations to suspend, and then resume, in the event of space allocation failures.
You set the value of the initialization parameter RESUMABLE\_TIMEOUT to 3600.
Which two statements are true? (Choose two.)
A. Before a statement executes in resumable mode, the ALTER SESSION ENABLE RESUMABLE statement must be issued in its session.
B. Data Manipulation Language (DML) operations are resumable, provided that they are not embedded in a PL/SQL block.
C. A resumable statement can be suspended and resumed only once during execution.
D. A suspended statement will report an error if no corrective action has taken place during a timeout period.
E. Suspending a statement automatically results in suspending a transaction and releasing all the resources held by the transaction.
A. Before a statement executes in resumable mode, the ALTER SESSION ENABLE RESUMABLE statement must be issued in its session.
D. A suspended statement will report an error if no corrective action has taken place during a timeout period.
101
Which two statements are true about Automatic Database Diagnostic Monitor (ADDM)? (Choose two.)
A. It calls SQL advisors automatically if required.
B. It provides recommendations only for poorly performing SQL statements.
C. Its results are stored in AWR.
D. It runs automatically after each AWR snapshot is created and requires at least two snapshots for analysis.
E. It requires at least one Automatic Workload Repository (AWR) snapshot for analysis.
C. Its results are stored in AWR.
D. It runs automatically after each AWR snapshot is created and requires at least two snapshots for analysis.
102
Which statement is true about using the Database Upgrade Assistant (DBUA) to upgrade your database from Oracle Database 11g to Oracle Database 12c?
A. It terminates if the SYSTEM tablespace in the source database is not autoextensible.
B. It automatically makes necessary changes to Oracle environment variables.
C. It automatically enables unified auditing in the upgraded database.
D. It automatically adds new data files if there is not enough disk space to grow.
B. It automatically makes necessary changes to Oracle environment variables.
103
Identify two prerequisites for configuring Enterprise Manager Database Express (EM Express).
A. Grant the APEX\_PUBLIC\_USER role to the SYSMAN user.
B. Use the DBMS\_XDB\_CONFIG.SETHTTPPORT procedure to configure a port number for Oracle HTTP Server.
C. Install Oracle HTTP Server.
D. Configure at least one dispatcher for the TCP/IP protocol.
E. Create a SYSMAN user with the SYSDBA privilege as an administrator for EM Express.
B. Use the DBMS\_XDB\_CONFIG.SETHTTPPORT procedure to configure a port number for Oracle HTTP Server.
D. Configure at least one dispatcher for the TCP/IP protocol.
104
Your database supports a Decision Support System (DSS) workload that involves the execution of complex queries. Currently, the database is running with peak workload.
You want to analyze some of the most resource-intensive statements cached in the library cache.
What must you run to receive recommendations on the efficient use of indexes and materialized views to improve query performance?
A. Automatic Database Diagnostic Monitor (ADDM)
B. SQL Tuning Advisor
C. SQL Access Advisor
D. SQL Performance Analyzer
E. Automatic Workload Repository (AWR) report
C. SQL Access Advisor
105
In which situations does the Database Writer process (DBWn) write to data files? (choose two).
A. when the RMAN recovery process starts
B. when a user process commits a transaction
C. when a tablespace is made read-only or taken offline
D. when PMON cleans up dirty buffers in the database buffer cache
E. when clean buffers for reading new blocks into the database buffer cache are not found easily
C. when a tablespace is made read-only or taken offline
E. when clean buffers for reading new blocks into the database buffer cache are not found easily
106
Which two statements are true about availability audit features after migration to unified auditing? (Choose two.)
A. The ability of users to audit their own schema objects is not available in the post-migrated database.
B. Operating system audit trail is available in the post-migrated database.
C. Network auditing is available in the post-migrated database.
D. Mandatory auditing of audit administrative actions is available in the post-migrated database.
A. The ability of users to audit their own schema objects is not available in the post-migrated database.
D. Mandatory auditing of audit administrative actions is available in the post-migrated database.
107
Which three statements are true about Database Resource Manager? (Choose three.)
A. A resource plan change can be automated by using the Oracle Scheduler.
B. It can be used to control the consumption of only physical I/Os where excessive physical I/Os can trigger an automatic session termination but excessive logical I/Os cannot.
C. It can be used to control the usage of the undo tablespace by consumer groups.
D. A resource plan can have multiple resource plan directives, each of which controls resource allocation for a different consumer group.
E. It can be used to enable resumable timeout for user sessions.
F. It can be used to control the usage of the temp tablespace by consumer groups.
A. A resource plan change can be automated by using the Oracle Scheduler.
C. It can be used to control the usage of the undo tablespace by consumer groups.
D. A resource plan can have multiple resource plan directives, each of which controls resource allocation for a different consumer group.
108
What is the effect of setting the STATISTICS\_LEVEL initialization parameter to BASIC?
A. Optimizer statistics are collected automatically.
B. Only timed operating system (OS) statistics and plan execution statistics are collected.
C. Automatic Workload Repository (AWR) snapshots are not generated automatically.
D. The Oracle server dynamically generates the necessary object-level statistics on tables as part of query optimization.
C. Automatic Workload Repository (AWR) snapshots are not generated automatically.
109
You are administering a database that supports an OLTP workload.
The default UNDO tablespace is a fixed size tablespace with the RETENTION NOGUARATNEE clause and undo retention set to 12 minutes.
The user SCOTT queries a large table during peak activity.
The query runs for more than 15 minutes and then SCOTT receives the following error:
ORA-01555: snapshot too old
Which is possible reason for this?
A. The Oracle server is unable to generate a read-consistent image for a block containing updates.committed after the query began.
B. The query is unable to place data blocks in the UNDO tablespace.
C. The flashback data archive is not enabled for the table on which the query is issued.
D. There is not enough space in Fast Recovery Area.
E. The Oracle server is unable to generate a read-consistent image for a block containing uncommitted updates.
A. The Oracle server is unable to generate a read-consistent image for a block containing updates.committed after the query began.
110
Which two categories of segments are analyzed by the Automatic Segment Advisor? (Choose two.)
A. segments in tablespaces that have exceeded a critical or warning space threshold
B. segments that have the highest growth rate in a database
C. segments that are sparsely populated and have more than 10% of free space below the high water mark.
D. segments that have unusable indexes
E. segments for tables created using ADVANCED ROW COMPRESSION
A. segments in tablespaces that have exceeded a critical or warning space threshold
B. segments that have the highest growth rate in a database
111
Your database is running in NOARCHIVLOG mode.
Examine the following parameters:
You execute the following command after performing a STARTUP MOUNT:
SQL\> ALTER DATABASE ARCHIVELOG;
Which statement is true about the execution of the command?
A. It executes successfully and sets the Fast Recovery Area as the local archive destination.
B. It executes successfully and issues a warning to set LOG\_ARCHIVE\_DEST while opening the database.
C. It fails and returns an error about LOG\_ARCHIVE\_DEST not being set.
D. It executes successfully and sets $ORACLE\_HOME/dbs as the default archive destination.
A. It executes successfully and sets the Fast Recovery Area as the local archive destination.
112
Automatic Shared Memory Management (ASMM) is enabled for your database instance.
You execute the following command:
SQL\> ALTER SYSTEM SET DB\_CACHE\_SIZE = 100M;
Which statement is true?
A. It succeeds and the minimum size for the DEFAULT buffer pool is set to 100M.
B. It fails because DB\_CACHE\_SIZE is a static initialization parameter.
C. It fails because ASMM is enabled and individual SGA components cannot be sized.
D. It succeeds and the value is changed in the SPFILE immediately, but the change takes effect only at the next instance startup.
A. It succeeds and the minimum size for the DEFAULT buffer pool is set to 100M.
113
You want to create a database with a block size other than the default 8 kilobytes (KB) by using the Database Configuration Assistant (DBCA).
Which option should you use?
A. Automatic Storage Management (ASM) for storage of data files
B. a file system for storage of data files
C. a Data Warehouse database template
D. a custom database template
D. a custom database template
114
Which two statements are true about using SQL\*Loader? (Choose two.)
A. It can load data from external files by using the direct path only.
B. It can load data into multiple tables using the same load statement.
C. It can load data into only one table at a time.
D. It can generate unique sequential key values in specified columns.
E. It can load data from external files by using the conventional path only.
B. It can load data into multiple tables using the same load statement.
D. It can generate unique sequential key values in specified columns.
115
The HR user owns the BONUSES table. HR grants privileges to the user TOM by using the command:
SQL\> GRANT SELECT ON bonuses TO tom WITH GRANT OPTION;
Tom then executes this command to grant privileges to the user JIM:
SQL\> GRANT SELET ON hr.bonuses TO jim;
Which statement is true?
A. TOM cannot revoke the SELECT ON HR.BONUSES privilege from JIM.
B. HR can revoke the SELECT ON HR.BONUSES privilege from JIM.
C. JIM can grant the SELECT ON HR.BONUSES privilege to other users, but cannot revoke the privilege from them.
D. HR can revoke the SELECT ON HR.BONUSES privilege from TOM, which will automatically revoke the SELECT ON HR.BONUSES privilege from JIM.
D. HR can revoke the SELECT ON HR.BONUSES privilege from TOM, which will automatically revoke the SELECT ON HR.BONUSES privilege from JIM.
117
What is the benefit of running the catctl.pl script during an upgrade of a pre-12c database to an Oracle 12c database?
A. It provides a summary of the upgrade results.
B. It recompiles all invalid PL/SQL and Java code.
C. It generates a log file containing the fixes that can be made to the source database.
D. It provides parallel upgrade options to finish the upgrade process with a reduced down time.
E. It generates fix-up scripts to be run on the source database before upgrade.
D. It provides parallel upgrade options to finish the upgrade process with a reduced down time.
118
Which statement is true about redo log files during instance recovery?
A. All current, online, and archived redo logs are required to complete instance recovery.
B. All redo log entries in the current and active logs are applied to data files to reconstruct changes made after the most recent checkpoint.
C. All redo log entries in the current log are applied to data files until the checkpoint position is reached.
D. All current, active, and inactive redo logs are required to complete instance recovery.
B. All redo log entries in the current and active logs are applied to data files to reconstruct changes made after the most recent checkpoint.
119
Your database is running in ARCHIVELOG mode.
You want to take a consistent whole database backup.
Which two statements are true in this scenario? (Choose two.)
A. The user-managed backup consists of only formatted data blocks.
B. The database must be shut down to take a user-managed backup.
C. The RMAN backup contains only data files.
D. The RMAN backup can be performed while the database is open.
E. The database must be in MOUNT state to take RMAN backup.
B. The database must be shut down to take a user-managed backup.
E. The database must be in MOUNT state to take RMAN backup.
120
Your database is configured for ARCHIVELOG mode, and a daily full database backup is taken.
RMAN is configured to perform control file autobackup.
In which three scenarios do you need media recovery? (Choose three.)
A. loss of all the copies of the control file
B. loss of all the inactive online redo log group members
C. loss of a data file that belongs to the active undo tablespace
D. loss of data files that belong to the SYSTEM tablespace
E. logical corruption of data that is caused by a wrong transaction
F. abnormal termination of the database instance
A. loss of all the copies of the control file
C. loss of a data file that belongs to the active undo tablespace
D. loss of data files that belong to the SYSTEM tablespace
121
Which two statements are true about Oracle Data Pump export and import operations? (Choose two.)
A. You cannot specify how partitioned tables should be handled during an import operation.
B. Only data can be compressed during an export operation.
C. Existing dump files can be overwritten during an export operation.
D. Tables cannot be renamed during an import operation.
E. Metadata that is exported and imported can be filtered based on objects and object types.
C. Existing dump files can be overwritten during an export operation.
E. Metadata that is exported and imported can be filtered based on objects and object types.
122
Your database instance has the following parameter setting:
OS\_AUTHENT\_PREFIX = OPS$
You execute the following command:
And then grant OPS$GUEST\_USER the CREATE SESSION privilege.
Which two statements are true? (Choose two.)
A. GUEST\_USER can query the tables created in the USERS tablespace by default.
B. The authentication details for GUEST\_USER are stored in the database password file.
C. A local GUEST\_USER OS account should exist before GUEST\_USER can log on to the database.
D. GUEST\_USER can log on to the database without specifying a username and password.
E. GUEST\_USER is forced to change the password at the first login.
C. A local GUEST\_USER OS account should exist before GUEST\_USER can log on to the database.
D. GUEST\_USER can log on to the database without specifying a username and password.
123
Your database has been running with a peak load for the past hour.
You want to preserve the performance statistics collected during this period for comparison when you analyze the performance of the database later.
What must you do to achieve this?
A. Increase the window size of the moving window baseline so that it equals the Automatic Workload Repository (AWR) snapshot retention period.
B. Create a baseline on a pair of snapshots that span the peak load period.
C. Generate Active Session History reports for the peak load period.
D. Set the snapshot retention period in AWR to 60 to avoid automatic purging of snapshots for the past hour.
B. Create a baseline on a pair of snapshots that span the peak load period.
124
Which four operations performed after the Oracle Restart installation are automatically added to the Oracle Restart configuration? (Choose four.)
A. listener configured by using NETCA
B. database service created by using SRVCTL
C. database created by using a SQL statement
D. database created by using DBCA
E. ASM instance created by using ASMCA
F. database service created by using DBMS\_SERVICE.CREATE\_SERVICE
G. database service created by modifying the SERVICE\_NAMES initialization parameter
A. listener configured by using NETCA
B. database service created by using SRVCTL
D. database created by using DBCA
E. ASM instance created by using ASMCA
125
Your single-instance Oracle 12c database home currently supports conventional auditing and uses Automatic Storage Management (ASM).
You want to enable unified auditing by executing the command:
$ make fins\_rdbms.mk uniaud\_on ioracle ORACLE\_HOME=$ORACLE\_HOME
Which two steps should you perform before executing this command? (Choose two.)
A. Ensure that the initialization parameter AUDIT\_TRAIL is set to DB.
B. Drop any existing fine-grained audit (FGA) policies.
C. Stop the listener.
D. Shut down the database instance.
E. Disable auditing by setting the initialization parameter AUDIT\_TRAIL to NONE.
C. Stop the listener.
D. Shut down the database instance.
126
You create an Oracle 12c database and then import schemas that are required by an application which has not yet been developed.
You want to get advice on creation of or modifications to indexes, materialized views and partitioning in these schemas.
What must you run to achieve this?
A. SQL Access Advisor with a SQL tuning set
B. Automatic Database Diagnostic Monitor (ADDM) report
C. SQL Tuning Advisor
D. SQL Access Advisor with a hypothetical workload
E. SQL Performance Analyzer
D. SQL Access Advisor with a hypothetical workload
127
The HR user updates the salary of one of the employees in the non-partitioned EMPLOYEES table, but does not commit the transaction.
Which two types of lock exist in this situation? (Choose two.)
A. exclusive lock on the EMPLOYEES table
B. null lock on the row being updated
C. null lock on the EMPLOYEES table
D. row level lock on the row being updated
E. shared lock on the EMPLOYEES table
D. row level lock on the row being updated
E. shared lock on the EMPLOYEES table
128
For which three requirements would you use the Database Resource Manager? (Choose three.)
A. specifying an idle time limit that applies to sessions that are idle and blocking other sessions
B. limiting the degree of parallelism of operations performed by user sessions in a consumer group
C. specifying the maximum number of concurrent sessions allowed for a user
D. limiting the CPU used per database call
E. specifying the amount of private space a session can allocate in the shared pool of the SGA.
A. specifying an idle time limit that applies to sessions that are idle and blocking other sessions
B. limiting the degree of parallelism of operations performed by user sessions in a consumer group
C. specifying the maximum number of concurrent sessions allowed for a user
129
In your database instance, the UNDO\_RETENTION parameter is set to 1000 and undo retention is not guaranteed for the fixed size undo tablespace.
Which statement is true about undo retention?
A. Undo is retained in the UNDO tablespace for 1000 seconds, and then moved to the SYSTEM tablespace to provide read consistency.
B. Inactive undo is retained for at least 1000 seconds if free undo space is available.
C. Inactive undo is retained for 1000 seconds even if new transactions fall due to lack of space in the undo tablespace.
D. Undo becomes expired obsolete after 1000 seconds.
B. Inactive undo is retained for at least 1000 seconds if free undo space is available.
130
Which two files must you copy from the Oracle home of the database that is being upgraded to the new Oracle home for Oracle Database 12c? (Choose three.)
A. the tnsnames.ora file
B. the sqlnet.ora file
C. the initialization parameter file
D. the password file
E. the listener.ora file
C. the initialization parameter file
D. the password file
131
In your Oracle 12c database, you invoke SQL \*Loader Express Mode command to load data:
$\> sqlldr hr/hr table=employees
Which two statements are true about this command? (Choose two.)
A. It succeeds and creates the EMPLOYEES table in the HR schema if the table does not exist.
B. It fails because the SQL \*Loader control file location is not specified.
C. It fails because the SQL \*Loader data file location is not specified.
D. It succeeds with default settings if the EMPLOYEES table belonging to the HR schema is already defined in the database.
E. It succeeds even if the HR user does not have the CREATE DIRECTORY privilege.
D. It succeeds with default settings if the EMPLOYEES table belonging to the HR schema is already defined in the database.
E. It succeeds even if the HR user does not have the CREATE DIRECTORY privilege.
132
Backup requirements for a database:
* \* Level 0 backup on Sunday
* \* Cumulative incremental level 1 backup on Monday, Wednesday, and Saturday
* \* Differential incremental level 1 backup on Tuesday, Thursday, and Friday
Which three statements are true about the strategy? (Choose three.)
A. Level 0 backup on Sunday contains all the blocks that have been formatted.
B. Level 0 backup on Sunday contains all the blocks that have been changed since the last level 1 backup.
C. Level 1 backup on Tuesday, Thursday, and Friday contains all the blocks that have been changed since the last level 1 backup.
D. Level 1 backup on Monday, Wednesday, and Saturday contains all the blocks that have been changed since the last level 0 backup.
E. Level 1 backup on Tuesday, Thursday, and Friday contains all the blocks that have been changed since the last level 0 backup.
A. Level 0 backup on Sunday contains all the blocks that have been formatted.
C. Level 1 backup on Tuesday, Thursday, and Friday contains all the blocks that have been changed since the last level 1 backup.
D. Level 1 backup on Monday, Wednesday, and Saturday contains all the blocks that have been changed since the last level 0 backup.
133
Which two statements are true about the Database Configuration Assistant (DBCA)? (Choose two.)
A. It can be used to create a database template from an existing database.
B. It can be used to add a new tablespace.
C. It can generate SQL database creation scripts.
D. It can be used to copy an existing Oracle database to a new host and apply any patches necessary in the new host.
E. It can configure Automatic Storage Management (ASM) diskgroups.
A. It can be used to create a database template from an existing database.
C. It can generate SQL database creation scripts.
134
You are managing an Oracle Database 12c database. The database is open, and you plan to perform Recovery Manager (RMAN) backups.
Which three statements are true about these backups? (Choose three.)
A. The backups would be consistent.
B. The backups would be possible only if the database is running in ARCHIVELOG mode.
C. The backups need to be restored and the database has to be recovered in case of a media failure.
D. The backups would be inconsistent.
E. The backups by default consist of all the data blocks within the chosen files or the full database.
B. The backups would be possible only if the database is running in ARCHIVELOG mode.
C. The backups need to be restored and the database has to be recovered in case of a media failure.
D. The backups would be inconsistent.
135
Which three tools or tasks are run by default as part automated maintenance tasks? (Choose three.)
A. Automatic Database Diagnostic Monitor
B. optimizer statistics gathering
C. SQL Access Advisor
D. Segment Advisor
E. Automatic SQL Tuning Advisor
B. optimizer statistics gathering
D. Segment Advisor
E. Automatic SQL Tuning Advisor
136
You plan to upgrade your Oracle Database 9i to Oracle Database 12c.
Which two methods can you use? (Choose two.)
A. Perform a rolling upgrade.
B. Perform a direct upgrade by running the Database Upgrade Assistant (DBUA).
C. Perform a direct upgrade by manually running the catctl.pl and catupgrd.sql scripts before issuing the STARTUPUPGRADE command.
D. Install the Oracle Database 12c software, create a new Oracle 12c database, and then use the Oracle Data Pump to import data from the source Oracle 9i database to the target Oracle 12c database.
E. Upgrade your current database to Oracle Database release 10.2.0.5, and then upgrade to Oracle Database 12c.
D. Install the Oracle Database 12c software, create a new Oracle 12c database, and then use the Oracle Data Pump to import data from the source Oracle 9i database to the target Oracle 12c database.
E. Upgrade your current database to Oracle Database release 10.2.0.5, and then upgrade to Oracle Database 12c.
137
Examine the following command:
SQL\> DBMS\_STATS. SET\_TABLE\_PREFS ('SH', 'CUSTOMERS', 'PUBLISH', 'false');
What is the effect of executing this command?
A. Existing statistics for the CUSTOMERS table become unusable for the query optimizer.
B. Automatic statistics collection is stopped for the CUSTOMERS table.
C. Statistics for the CUSTOMERS table are locked and cannot be overwritten.
D. Statistics subsequently gathered on the CUSTOMERS table are stored as pending statistics.
D. Statistics subsequently gathered on the CUSTOMERS table are stored as pending statistics.
138
You install Oracle Grid Infrastructure for a standalone server.
Which two components are automatically included in the Oracle Restart configuration? (Choose two.)
A. A pre-existing Oracle Net Listener
B. Oracle Notification services
C. A pre-existing database
D. A pre-existing Oracle management agent
E. Oracle CSSD service
B. Oracle Notification services
E. Oracle CSSD service
139
In your database, the STATISTICS\_LEVEL parameter is set to TYPICAL and an Automatic Workload Repository (AWR) snapshot is taken every 30 minutes.
Which two statements are true about the Automatic Database Diagnostic Monitor (ADDM)? (Choose two.)
A. It measures database performance by analyzing the wait time and CPU time of all non-idle user sessions.
B. It always compares the latest snapshot with the baseline snapshot for analysis.
C. It runs after each AWR snapshot is created and it requires at least two snapshots for analysis.
D. It requires at least four AWR snapshots for analysis.
E. It calls other advisors if required, but does not provide recommendations about the advisors to be used.
A. It measures database performance by analyzing the wait time and CPU time of all non-idle user sessions.
C. It runs after each AWR snapshot is created and it requires at least two snapshots for analysis.
140
Your database is configured in ARCHIVELOG mode, and a daily full database backup is taken by using RMAN.
Control file autobackup is configured.
Loss of which three database files can lead to an incomplete recovery? (Choose three.)
A. inactive online redo log file group
B. a data file belonging to the default temporary tablespace
C. a data file belonging to the SYSAUX tablespace
D. server parameter file (SPFILE)
E. active online redo log file group
F. all the control flies
C. a data file belonging to the SYSAUX tablespace
E. active online redo log file group
F. all the control flies
141
Which two statements are true about resumable space allocation? (Choose two.)
A. A database-level LOGON trigger can be used to automatically configure resumable statement settings for individual sessions.
B. SELECT statements that run out of temporary space for sort areas are candidates for resumable execution.
C. A resumable statement can be suspended and resumed only once during a session.
D. Resumable space allocation does not apply when a database instance uses asynchronous commit.
E. Resumable space allocation does not apply when users exceed their assigned space quota in a tablespace.
F. Free space in a segment is automatically reclaimed when a resumable statement is suspended because of an out-of-space condition.
A. A database-level LOGON trigger can be used to automatically configure resumable statement settings for individual sessions.
B. SELECT statements that run out of temporary space for sort areas are candidates for resumable execution.
142
Your database supports an online transaction processing (OLTP) workload in which one of the applications creates a temporary table for a session and performs transactions on it.
This consumes a lot of undo tablespace and generates lots of redo.
Which two actions would you take to solve this problem? (Choose two.)
A. Increase the size of the temporary tablespace.
B. Enable Automatic Memory Management (AMM).
C. Enable undo retention guarantee.
D. Enable temporary undo for the database.
E. Increase the size of the redo log buffer.
A. Increase the size of the temporary tablespace.
D. Enable temporary undo for the database.
143
You want to create a file watcher and an event-based job for detecting the arrival of files on the local server from various locations.
To achieve this, you enable the raising of file arrival events from remote systems.
Which two conditions must be satisfied to receive file arrival events from a remote system? (Choose two.)
A. The remote system must have a running Oracle Database instance and a scheduler agent installed.
B. The initialization parameter REMOTE\_OS\_AUTHENT must be set to TRUE on your database.
C. The local database must be set up to run remote external jobs.
D. The remote system's scheduler agent must be registered with your database.
E. Database links to remote databases must be created.
C. The local database must be set up to run remote external jobs.
D. The remote system's scheduler agent must be registered with your database.
144
You want to import the schema objects of the HR user from the development database DEVDB to the production database PRODDB by using Oracle Data Pump.
A database link devdb.us.oracle.com is created between PRODDB and DEVDB.
You execute the following command on the PRODDB database server:
What should you do to resolve the error?
A. Add network\_link = devdb.us.oracle.com.
B. Add the SYSTEM user to the schemas option.
C. Change the dumpfile option value to schema.dat@devdb.us.oracle.com.
D. Replace the schemas option with network\_1ink = devdb.us.oracle.com.
E. Replace the dumpfile option with network\_1ink = devdb.us.oracle.com.
E. Replace the dumpfile option with network\_1ink = devdb.us.oracle.com.
145
Unified auditing is enabled in your database.
The HR\_ADMIN and OE\_ADMIN roles exist and are granted system privileges.
You execute the command:
SQL\>CREATE AUDIT POLICY tab1e\_aud PRIVILEGES CREATE ANY TABLE, DROP ANY TABLE
ROLES hr\_admin, oe\_admin;
Which statement is true?
A. It succeeds and needs to be enabled to capture all SQL statements that require either the specified privileges or any privilege granted to the HR\_ADMIN and role. OE\_ADMIN
B. It fails because system privileges cannot be granted with roles in the same audit policy.
C. It succeeds and starts capturing only successful SQL statements for all users who have either the specified privileges or roles granted to them.
D. It fails because the command does not specify when the unified audit policy should be enforced.
A. It succeeds and needs to be enabled to capture all SQL statements that require either the specified privileges or any privilege granted to the HR\_ADMIN and role. OE\_ADMIN
146
You determine that database performance is sub-optimal due to hard parsing statements. Automatic Shared Memory Management (ASMM) is disabled for your database instance.
Which tool would you use to get advice on how to improve performance?
A. Memory Advisor for the PGA
B. SQL Access Advisor
C. Memory Advisor for the shared pool
D. SQL Tuning Advisor
C. Memory Advisor for the shared pool
147
Identify three uses of the CROSSCHECK command (Choose three.)
A. to validate the database backup
B. to synchronize logical backup records with physical files in backup storage
C. to check the obsolete backups that can be deleted from the file system
D. to update information about backups that are deleted, corrupted, or inaccessible in a recovery catalog or control file
E. to update the recovery catalog or control file if archived log files are deleted with operating system commands
B. to synchronize logical backup records with physical files in backup storage
D. to update information about backups that are deleted, corrupted, or inaccessible in a recovery catalog or control file
E. to update the recovery catalog or control file if archived log files are deleted with operating system commands
148
Which statement is true about the Oracle central inventory directory (oraInventory)?
A. oraInventory must not be shared by all Oracle software installations on a single system.
B. If ORACLE\_BASE is set to /u01/app/oracle for the oracle user during an installation, OUI creates the Oracle Inventory directory in the /u01/app/oracle/ path. oraInventory
C. lf an OFA-compliant path is not created and the ORACLE\_BASE environment variable is not set during an Oracle Database installation, the Oracle Inventory directory is placed in the home directory of the user that is performing the installation.
D. Oracle software owners must be members of the same central oraInventory group, but they need not have this group as their primary group.
C. lf an OFA-compliant path is not created and the ORACLE\_BASE environment variable is not set during an Oracle Database installation, the Oracle Inventory directory is placed in the home directory of the user that is performing the installation.
149
Which three statements are true about Automatic Workload Repository (AWR)? (Choose three.)
A. An AWR snapshot shows the SQL statements that are producing the highest load on the system, based on criteria such as elapsed time and CPU time.
B. AWR data is stored in memory and in a database.
C. All AWR tables belong to the SYSTEM schema.
D. The manageability monitor (MMON) process gathers statistics and creates an AWR snapshot that is used by the self-tuning components in a database.
E. An AWR snapshot contains system-wide tracing and logging information.
A. An AWR snapshot shows the SQL statements that are producing the highest load on the system, based on criteria such as elapsed time and CPU time.
B. AWR data is stored in memory and in a database.
D. The manageability monitor (MMON) process gathers statistics and creates an AWR snapshot that is used by the self-tuning components in a database.
151
Which three statements are true about server-generated alerts? (Choose three.)
A. Server-generated alerts notify administrators of problems that cannot be resolved automatically.
B. Alerts are not issued for locally managed read-only tablespaces.
C. Response actions cannot be specified for server-generated alerts.
D. Stateful alerts can be queried only from the DBA\_ALERT\_HISTORY view.
E. When an alert is cleared, it is moved to the DBA\_ALERT\_HISTORY view.
A. Server-generated alerts notify administrators of problems that cannot be resolved automatically.
B. Alerts are not issued for locally managed read-only tablespaces.
E. When an alert is cleared, it is moved to the DBA\_ALERT\_HISTORY view.
152
Which two statements are true about Automatic Storage Management (ASM)? (Choose two.)
A. It mounts databases and diskgroups to make ASM files available to database instances.
B. It spreads files proportionally across all disks in a diskgroup, aiming to ensure that all the disks in a diskgroup have the same I/O load.
C. It automatically places each disk from an external redundancy diskgroup in its own failure group.
D. It divides files into extents and allows an extent to span disks.
E. It mirrors data at the allocation unit (AU) level across failure groups within a normal or high redundancy diskgroup.
B. It spreads files proportionally across all disks in a diskgroup, aiming to ensure that all the disks in a diskgroup have the same I/O load.
E. It mirrors data at the allocation unit (AU) level across failure groups within a normal or high redundancy diskgroup.
153
Examine the parameters:
Users complain that their sessions for certain transactions hang.
You investigate and discover that some users fail to complete their transactions, causing other transactions to wait on row-level locks.
Which two actions would you take to prevent this problem? (Choose two.)
A. Increase the maximum number of ITL slots for segments on which a blocking user performs a transaction.
B. Decrease the SESSIONS\_PER\_USER limit in the profiles assigned to blocking users.
C. Set a limit in the profiles of blocking users to control the number of data blocks that can be accessed in a session.
D. Use Database Resource Manager to automatically kill the sessions that are idle and are blocking other sessions.
E. Decrease the IDLE\_TIME resource limit in the profiles assigned to blocking users.
D. Use Database Resource Manager to automatically kill the sessions that are idle and are blocking other sessions.
E. Decrease the IDLE\_TIME resource limit in the profiles assigned to blocking users.
154
You want to create a test database as a replica of your production database with minimum intervention from a DBA.
Which method would you use?
A. Use DBCA to create a template from the existing database to contain the database structure and then manually copy the data by using Oracle Data Pump.
B. Use Database Configuration Assistant (DBCA) to create a template from the existing database to contain the database structure.
C. Create the database by using the CREATE DATABASE. . . command and manually import data by using Data Pump.
D. Use DBCA to create a template from the existing database to contain the database structure with data files and then use the same template to create the database in the new location.
D. Use DBCA to create a template from the existing database to contain the database structure with data files and then use the same template to create the database in the new location.
155
You want to load data from a large file into your database without causing an overhead on the SGA.
Which tool would you use.
A. external table
B. Oracle data Pump
C. SQL\*Loader with a direct data path
D. SQL\*Loader with a conventional data path
E. Enterprise Manager Database Express
C. SQL\*Loader with a direct data path
156
As part of a manual upgrade of your database to Oracle Database 12c, you plan to issue the command:
SQL\> STARTUP UPGRADE
Which three statements are true about the upgrade process? (Choose three.)
A. All system triggers are disabled during the upgrade process.
B. Only queries on fixed views execute without errors until you run the catctl.pl script.
C. The COMPATIBLE parameter must be set to at least 12.1.0 before issuing the command.
D. All job queues remain active during the upgrade process.
E. Only connections AS SYSDBA are allowed during the upgrade process.
A. All system triggers are disabled during the upgrade process.
B. Only queries on fixed views execute without errors until you run the catctl.pl script.
E. Only connections AS SYSDBA are allowed during the upgrade process.
157
In your database, the RESOURCE\_LIMIT parameter is set to TRUE.
You create the profile:
Which two statements are true about users and their sessions that are subject to this profile? (Choose two.)
A. The CPU\_PER\_CALL is ignored in the user sessions because of the unlimited value of CPU\_PER\_CALL
B. These users can never reuse a password
C. The PASSWORD\_LIFE\_TIME value is ignored because of the unlimited value of PASSWORD\_REUSE\_MAX.
D. In each user session, the limit for LOGICAL\_READS\_PER\_SESSION in the same as defined in the DEFAULT profile.
B. These users can never reuse a password
D. In each user session, the limit for LOGICAL\_READS\_PER\_SESSION in the same as defined in the DEFAULT profile.
158
Which statement is true about using the Export/Import method for migrating data when upgrading to Oracle Database 12c?
A. It automatically restarts a Data Pump Export or Import job after a failure is connected and the job continues from the point of failure.
B. It can be used to migrate a database only if the source and target databases are hosted on the same endian format.
C. It can be used to migrate a database only if the source database does not have any tablespace in readonly mode.
D. It allows migration of a database directly over network links.
D. It allows migration of a database directly over network links.
159
Your database is in ARCHIVELOG mode and you want to automate the backup scheduling for your database.
Which two tools or utilities would you use to achieve this? (Choose two.)
A. Oracle Enterprise Manager Database Express (EM Express)
B. Oracle Enterprise Manager Cloud Control
C. Database Configuration Assistant (DBCA)
D. Recovery Manager (RMAN) script invoked by using scheduler
B. Oracle Enterprise Manager Cloud Control
D. Recovery Manager (RMAN) script invoked by using scheduler
160
Which three functions can be performed by the SQL Tuning Advisor? (Choose three.)
A. recommending creation of indexes based on SQL workload
B. recommending restructuring of SQL statements that have suboptimal plans
C. checking schema objects for missing and state statistics
D. recommending optimization of materialized views
E. generating SQL profiles
B. recommending restructuring of SQL statements that have suboptimal plans
C. checking schema objects for missing and state statistics
E. generating SQL profiles
162
Examine the following steps:
A DBA grants the CREATE TABLE system privilege with ADMIN OPTION to the user SIDNEY.
SIDNEY grants the CREATE TABLE system privilege to the HR user.
Which statement is true?
A. SIDNEY can revoke the CREATE TABLE system privilege only from HR, to whom he granted it.
B. HR can grant the CREATE TABLE system privilege to other users.
C. Neither SIDNEY nor HR can create new tables if the DBA revokes the CREATE TABLE privilege from SIDNEY.
D. HR still retains the CREATE TABLE system privilege if the DBA revokes the CREATE TABLE privilege from SIDNEY.
D. HR still retains the CREATE TABLE system privilege if the DBA revokes the CREATE TABLE privilege from SIDNEY.
163
What is pre-requisite to alter a role?
A. You should be granted the DBA role.
B. You should set the OS\_ROLES parameter to true.
C. You should be granted the role with the GRANT OPTION.
D. You should have the ALTER ANY ROLE system privilege.
D. You should have the ALTER ANY ROLE system privilege.
164
Identify the access that is initially available to connect to your Database as a Service (DBaaS) environment.
A. Enterprise Manager on port 1158
B. telnet on port 23
C. Cloud Control on port 7799
D. SSH on port 22
E. SSL/TLS on port 443
D. SSH on port 22
166
Examine the command:
SQL\> ALTER SYSTEM SET ENABLE\_DDL\_LOGGING=TRUE;
Which two statements are true in this scenario? (Choose two.)
A. All data definition language (DDL) commands are logged in to the alert log file.
B. All DDL commands are logged in to a text file in Automatic Diagnostic Repository (ADR) home.
C. A subset of executed DDL statements is written into an XML file in ADR home.
D. A subset of executed DDL statements is written to the DDL log in ADR home.
E. All DDL commands are logged in to a trace file in ADR home.
C. A subset of executed DDL statements is written into an XML file in ADR home.
D. A subset of executed DDL statements is written to the DDL log in ADR home.
167
What is the outcome of the SHUTDOWN ABORT command?
A. Pending transactions are committed and the database is closed.
B. Dirty buffers in the buffer cache and unwritten redo are not written to the data files and redo log files respectively.
C. Uncommitted transactions are rolled back
D. Instance recovery must be requested by the DBA at the next startup
B. Dirty buffers in the buffer cache and unwritten redo are not written to the data files and redo log files respectively.
168
Which component resides in the System Global Area (SGA) of a database instance only in shared server connections?
A. User Global Area
B. Program Global Area
C. SQL Query Result Cache
D. PL/SQL Function Result Cache
A. User Global Area
169
Which three statements are true about Enterprise Manager Database Express? (Choose three.)
A. It can be used to perform database backup operations.
B. It can use the HTTP protocol.
C. The same port number is used for multiple Database Express configurations on the same host.
D. It can use the HTTPS protocol.
E. It is available only when the database is open.
B. It can use the HTTP protocol.
D. It can use the HTTPS protocol.
E. It is available only when the database is open.
170
An application repeatedly accesses small lookup tables, causing a lot of physical I/O operations.
What do you recommend to minimize this?
A. Configure the nonstandard buffer cache with a buffer size greater than the size of the default buffer cache.
B. Increase the size of the shared pool
C. Configure the KEEP buffer cache and alter the tables to use the KEEP cache.
D. Configure the RECYCLE buffer cache and alter the tables to use the RECYCLE cache.
C. Configure the KEEP buffer cache and alter the tables to use the KEEP cache.
171
Your database is open in read/write mode and multiple users are connected to the database instance.
You execute the following command:
SQL\> ALTER SYSTEM ENABLE RESTRICTED SESSION;
What would be the effect on current sessions?
A. They are not terminated but may only issue queries.
B. They are not affected.
C. They are terminated immediately.
D. They are terminated after completing the transaction.
B. They are not affected.
172
You want to distribute a set of structured data to your customers who can integrate this data into their existing databases irrespective of the platform.
Which method provides the fastest way of achieving this?
A. using the DBVERIFY utility
B. using direct-path INSERT SQL statements
C. using SQL\*Loader
D. using RMAN transportable tablespace operation
D. using RMAN transportable tablespace operation
173
You create a locally managed tablespace ORDERS\_TBS with automatic segment management.
You then create the table DAILY\_ORDS\_LST in the ORDERS\_TBS tablespace using the command.
CREATE TABLE daily\_ords\_1st(ordno NUMBER, ord\_date DATE) PCTFREE 20;
How does the PCTFREE storage parameter influence data storage for this table?
A. It allows only 80% of space to be occupied in all data blocks of this table.
B. It minimizes row chaining during row insertion.
C. It minimizes row migration during existing row data updation.
D. It automatically coalesces free space of a data block when it reaches 20% of
A. It allows only 80% of space to be occupied in all data blocks of this table.
174
What must you use to read data from a table in your database and write it to an external table?
A. Use SQL\* LOADER conventional path load.
B. Use SQL\* LOADER direct path load.
C. Use CREATE TABLE. . ORGANIZATION EXTERNAL command with ORACLE\_LOADER access driver.
D. Use CREATE TABLE. . ORGANIZATION EXTERNAL command with ORACLE\_DATAPUMP access driver.
D. Use CREATE TABLE. . ORGANIZATION EXTERNAL command with ORACLE\_DATAPUMP access driver.
175
Which two statements are true about initialization parameter files? (Choose two.)
A. A lost or damaged SPFILE can be re-created by using the parameter values listed in the alert log.
B. A PFILE must exist for an SPFILE to be created.
C. The ALTER SYSTEM command cannot be used to change the value of any parameter if a database instance has started using a PFILE.
D. Both the SPFILE and PFILE must always reside on a file system accessible from the database host server.
E. On startup, by default a database instance always first searches for an SPFILE, and if it does not find any, searches for a PFILE.
A. A lost or damaged SPFILE can be re-created by using the parameter values listed in the alert log.
E. On startup, by default a database instance always first searches for an SPFILE, and if it does not find any, searches for a PFILE.
176
You configured the Fast Recovery Area (FRA) for your database.
The database instance is in ARCHIVELOG mode. The default location for the archived redo log files is the FRA.
Which two files are removed automatically if space is required in the FRA as per the retention policy? (Choose two.)
A. Archived redo log files that have multiple copies in a different archive location
B. user-managed backups of data files and control files
C. RMAN backups that are obsolete
D. flashback logs
C. RMAN backups that are obsolete
D. flashback logs
177
Which statement is true about a database in ARCHIVELOG mode?
A. All backups taken prior to switching to ARCHIVELOG mode can be used to perform complete recovery.
B. Online redo log files have to be multiplexed before putting the database in ARCHIVELOG mode.
C. A Fast Recovery Area (FRA) must be configured for the database.
D. Full database backups can be performed when the database is opened.
D. Full database backups can be performed when the database is opened.
178
Which statement is true about profiles?
A. Resource limits specified in a profile assigned to a user are always enabled.
B. A user can exist without any profile.
C. A profile can be assigned only to one user.
D. Password management using profiles is always enabled.
D. Password management using profiles is always enabled.
179
You want to create a locally managed tablespace called NEWTBS to store segments with different extent sizes.
Which set of tablespace attributes can be specified for a tablespace that satisfies the requirements?
A. EXTENT MANAGEMENT LOCAL STORAGE (INITIAL 5M MAXSIZE 10M)
B. REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
C. EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT UNIFORM
D. EXTENT MANAGEMENT LOCAL AUTOALLOCATE
D. EXTENT MANAGEMENT LOCAL AUTOALLOCATE
180
The schema SALES exists in two databases, ORCL1 and ORCL2, and has the same password, SALES123.
User SALES has CREATE DATABASE LINK and CREATE SESSION privileges on both databases.
Examine these commands:
Conn SALES/SALES123
CREATE DATABASE LINK orcl2 USING 'orcl2';
What is the outcome of executing these commands in the ORCL1 database?
A. ORCL2 is created as a public database link to connect a single session to the SALES schema in the ORCL2 database.
B. ORCL2 is created as a shared database link to connect multiple sessions to the SALES schema in the ORCL2 database.
C. ORCL2 is created as a private database link to connect to only the SALES schema in the ORCL2 database.
D. ORCL2 database link creation fails.
C. ORCL2 is created as a private database link to connect to only the SALES schema in the ORCL2 database.
181
Examine the command:
SQL\> CONNECT hr/hr@orcl
Which two configurations allow this command to execute successfully? (Choose two.)
A. In the tnsnames.ora file, the SERVICE\_NAME value of CONNECT\_DATA should be explicitly suffixed with the domain name.
B. The SERVICE\_NAMES initialization parameter should contain the name orcl in the database host.
C. The orcl TNS alias should be defined such that it is resolvable by a client running on the database host.
D. The orcl TNS alias should be defined in the tnsnames.ora file on both the client and the database host.
E. The TNS\_ADMIN environment variable should be set to orcl on the client.
B. The SERVICE\_NAMES initialization parameter should contain the name orcl in the database host.
C. The orcl TNS alias should be defined such that it is resolvable by a client running on the database host.
182
You executed the following query:
Considering that all the redo logs are available, what information can you derive from the output of the preceding query?
A. The time when the last flashback operation in your database was performed
B. The time when the first flashback operation in our database was performed
C. The approximate time and the lowest system change number (SCN) to which you can flash back your database
D. The system change number (SCN) and the time when the Flashback Database was enabled in the database instance
C. The approximate time and the lowest system change number (SCN) to which you can flash back your database
183
Which three statements are true about Oracle Data Pump? (Choose three.)
A. Oracle Data Pump export and import operations can be performed to move data across different database releases.
B. DBMS\_DATAPUMP PL/SQL packages can be used independent of Data Pump clients.
C. A directory object must exist and a user performing an EXPDP or IMPDP operation must have read and write permission on that directory object.
D. Oracle Data Pump export and import operations can be performed only by users with the SYSDBA privilege.
E. Oracle Data Pump export operations invoked from the clients that are connected remotely by using a connection string, create Data Pump files on the client file system.
A. Oracle Data Pump export and import operations can be performed to move data across different database releases.
B. DBMS\_DATAPUMP PL/SQL packages can be used independent of Data Pump clients.
C. A directory object must exist and a user performing an EXPDP or IMPDP operation must have read and write permission on that directory object.
184
In your database, archive logging and control file autobackup are enabled.
The data files and redo log files are intact but control files are impacted due to media failure.
In which two recovery scenarios must you use the RESETLOGS option? (Choose two.)
A. One control file copy is intact so the spfile is changed to refer to only one copy.
B. One control file copy is intact and damaged control file copies have to be restored to the default location.
C. All copies of the control file are damaged and the CREATE CONTROLFILE statement is executed manually.
D. All copies of the control file are damaged and the auto backed up control file is used for recovery.
E. One control file copy is intact and damaged control file copies have to be restored to a non-default location.
C. All copies of the control file are damaged and the CREATE CONTROLFILE statement is executed manually.
D. All copies of the control file are damaged and the auto backed up control file is used for recovery.
185
Which statement is true regarding the DEFAULT profile?
A. The values assigned to the resource limits and password parameters in the default profile can be altered.
B. A different DEFAULT profile can be created before each user in a database.
C. It can be dropped and recreated.
D. it must be explicitly assigned to the user.
A. The values assigned to the resource limits and password parameters in the default profile can be altered.
186
Your database is running in ARCHIVELOG mode.
You want to take a consistent whole database backup.
Which two statements are true in this scenario? (Choose two.)
A. RMAN backups contain only data files.
B. The database instance must be shut down to take a user-managed consistent backup.
C. Consistent RMAN backups can be taken while the database is open.
D. User-managed backups only contain formatted data blocks.
E. The database must be in MOUNT state to take consistent RMAN backups.
B. The database instance must be shut down to take a user-managed consistent backup.
E. The database must be in MOUNT state to take consistent RMAN backups.
187
Which background process does Automatic Shared Memory Management use to coordinate the sizing of memory components?
A. PMON
B. SMON
C. MMNL
D. MMAN
E. MMON
D. MMAN
188
You enabled block change tracking for faster incremental backups in your database.
Which background process writes to the change tracking file?
A. RBAL
B. CKPT
C. SMON
D. PMON
E. MMON
F. CTWR
G. DBWR
F. CTWR
189
You configured the flash recovery area in the database.
Which two files would you expect to find in the flash recovery area? (Choose two.)
A. backup pieces
B. copies of all parameter files
C. trace file generated using BACKUP CONTROLFILE TO TRACE
D. control file autobackups
A. backup pieces
D. control file autobackups
190
Because of a logical corruption in the EMPLOYES tables, you want to perform Tablespace Point-in-Time Recovery (TSPITR) to recover the table.
Before you started the TSPITR process, you queried the TS\_PITR\_CHECK view and you realized that the table has a referential constraint with DEPARTMENTS that exists in another tablespace, MASTERTBS.
Which two actions will permit the TSPITR to work?
(Choose two.)
A. Taking the MASTERTBS tablespace offline
B. Dropping the relationship between the tables
C. Adding the MASTERTBS tablespace to the recovery set
D. Putting the MASTERTBS tablespace in read-only mode
B. Dropping the relationship between the tables
C. Adding the MASTERTBS tablespace to the recovery set
192
To enable faster incremental backups, you enabled block change tracking for the database.
Which two statements are true about the block change tracking file? (Choose two.)
A. Multiple change tracking files can be created for a database.
B. The change tracking file must be created after the first level 0 backup.
C. RMAN does not support backup and recovery of the change tracking file.
D. The database clears the change tracking file and starts tracking changes again, after whole database restore and recovery operations.
C. RMAN does not support backup and recovery of the change tracking file.
D. The database clears the change tracking file and starts tracking changes again, after whole database restore and recovery operations.
193
Which three statements are true about the Pre-Upgrade Information Tool? (Choose three.)
A. It clears all user recycle bins in a database and releases their storage space.
B. It writes a list of invalid SYS and SYSTEM object to the registry$sys\_inv\_objs table.
C. It evaluates the dependencies of network utility packages.
D. It identifies any deprecated and unsupported parameters.
E. It generates fix-up scripts and automatically runs them to resolve issues that are flagged in the source database.
B. It writes a list of invalid SYS and SYSTEM object to the registry$sys\_inv\_objs table.
C. It evaluates the dependencies of network utility packages.
D. It identifies any deprecated and unsupported parameters.
194
You install Oracle Grid Infrastructure standalone server and issue the following command:
crsctl start has
Which two existing components get automatically added to the Oracle Restart configuration? (Choose two.)
A. Oracle CSSD services
B. the database whose instance is running
C. Oracle Notification services
D. Oracle Healthcheck services
E. Oracle Net Listener
A. Oracle CSSD services
C. Oracle Notification services
195
Examine this command executed on a client that is remote from the database server.
SQL\> CONNECT hr/hr@orcl
Which two are required for this command to connect the SQLPLUS client to a database instance? (Choose two.)
A. An orcl TNS entry must be defined in the client-side and server-side tnsnames.ora files
B. An orcl TNS entry must be defined in the client-side tnsnames.ora file
C. A service name must be defined to the listener that matches the service name in the orcl TNS entry
D. An orcl TNS entry must be defined in the server-side tnsnames.ora file
E. The service name orcl must be defined to the listener
B. An orcl TNS entry must be defined in the client-side tnsnames.ora file
C. A service name must be defined to the listener that matches the service name in the orcl TNS entry
196
One of your databases supports an OLTP workload.
The default UNDO tablespace is fixed size with:
1. RETENTION NOGUARANTEE
2. UNDO\_RETENTION is 12 minutes
User SCOTT gets this error after a query on the SALES table has run for more than 15 minutes:
ORA-01555: snapshot too old
Which three factors taken separately or in some combination might be the cause? (Choose three.)
A. An update was made to the SALES table after the query began
B. An update to the SALES table was committed after the query began
C. A committed delete to the SALES table was made more than 12 minutes before the query began
D. An uncommitted update to the SALES table was made more than 12 minutes before the query began
E. A committed update to the SALES table was made more than 12 minutes before the query began
F. An uncommitted delete to the SALES table was made more than 12 minutes before the query began
G. An update was made to the SALES table before the query began
A. An update was made to the SALES table after the query began
D. An uncommitted update to the SALES table was made more than 12 minutes before the query began
E. A committed update to the SALES table was made more than 12 minutes before the query began
197
One of your databases has archive logging enabled and RMAN backups are taken at regular intervals.
The data file for the USERS tablespace is corrupt.
Which command must you execute before starting the recovery of this tablespace?
A. STARTUP FORCE
B. ALTER TABLESPACE users OFFLINE IMMEDIATE;
C. SWITCH DATAFILE ALL;
D. ALTER TABLESPACE users OFFLINE NORMAL;
E. ALTER TABLESPACE users OFFLINE TEMPORARY;
E. ALTER TABLESPACE users OFFLINE TEMPORARY;
198
Which two statements are true about SQL\*Loader Express Mode in an Oracle 12c database? (Choose two.)
A. It loads data faster than conventional SQL\*Loader
B. No data file needs to be specified
C. It can load data in parallel
D. It loads data more efficiently than conventional SQL\*Loader
E. It requires Enterprise Manager Express to be configured
A. It loads data faster than conventional SQL\*Loader
C. It can load data in parallel
199
Which are two ways for a database service to be recognized by a listener in Oracle Database 12c? (Choose two.)
A. Dynamic Registration by the LREG process
B. Dynamic Registration by the SMON process
C. Static registration in the listener.ora file using the GLOBAL\_DBNAME parameter
D. Dynamic Registration by the PMON process
E. Static registration in the listener.ora file using the SERVICE\_NAME parameter
A. Dynamic Registration by the LREG process
E. Static registration in the listener.ora file using the SERVICE\_NAME parameter
200
You ran this command on a source database:
$\> expdp hr/hr DIRECTORY=dumpdir DUMPFILE=emp1.dmp VIEWS\_AS\_TABLES=emp\_dept
On the target database, you run this command:
$\> impdp hr/hr DIRECTORY=dumpdir DUMPFILE=emp1.dmp VIEWS\_AS\_TABLES=emp\_dept
Which two statements are true? (Choose two.)
A. The expdp operation exports all rows for tables contained in the defining query of the EMP\_DEPT view
B. The impdp operation creates separate tables for each table contained in the defining query of the EMP\_DEPT view
C. The expdp operation exports all rows that are displayed when querying the EMP\_DEPT view with no filter
D. The impdp operation creates EMP\_DEPT as a table
E. The expdp operation exports the table definitions for tables that are queried in the EMP\_DEPT view.
F. The impdp operation creates EMP\_DEPT as a view
D. The impdp operation creates EMP\_DEPT as a table
E. The expdp operation exports the table definitions for tables that are queried in the EMP\_DEPT view.
201
Which four statements are true about database instance behavior? (Choose four.)
A. An idle instance is created when a STARTUP NOMOUNT is successful
B. All dynamic performance views (v$ views) return data when queried from a session connected to an instance in NOMOUNT state
C. The consistency of redo logs and data files is checked when mounting the database
D. Redo log files can be renamed in MOUNT state
E. An SPFILE can be updated when connected to an idle instance
F. Datafiles can be renamed in MOUNT state
C. The consistency of redo logs and data files is checked when mounting the database
D. Redo log files can be renamed in MOUNT state
E. An SPFILE can be updated when connected to an idle instance
F. Datafiles can be renamed in MOUNT state
202
Examine this command:
SQL\> ALTER SYSTEM SET ENABLE\_DDL\_LOGGING=TRUE;
Which two statements are true? (Choose two.)
A. All data definition language (DDL) statements are written to the control file
B. Some DDL statements are written to an XML file in the ADR home
C. All DDL statements are logged in to a text file in Automatic Diagnostic Repository (ADR) home
D. Some data definition language (DDL) statements are written to the control file
E. Some DDL statements are written to a text file in the ADR home
F. The Alert Log still contains some DDL statements
B. Some DDL statements are written to an XML file in the ADR home
E. Some DDL statements are written to a text file in the ADR home
203
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?
A. by granting a secure application role to the user
B. by implementing Database Resource Manager
C. by using Oracle Label Security options
D. by assigning a profile to the user
D. by assigning a profile to the user
204
What happens if a maintenance window closes before a job that collects optimizer statistics completes?
A. The job is terminated and the gathered statistics are not saved.
B. The job is terminated but the gathered statistics are not published.
C. The job continues to run until all statistics are gathered.
D. The job is terminated and statistics for the remaining objects are collected the next time the maintenance window opens.
D. The job is terminated and statistics for the remaining objects are collected the next time the maintenance window opens.
205
Which two statements are true about the logical storage structure of an Oracle database? (Choose two.)
A. An extent contains data blocks that are always physically contiguous on disk.
B. An extent can span multiple segments.
C. Each data block always corresponds to one operating system block.
D. It is possible to have tablespaces of different block sizes.
E. A data block is the smallest unit of I/O in data files.
D. It is possible to have tablespaces of different block sizes.
E. A data block is the smallest unit of I/O in data files.
206
Which statement is true about the Log Writer process?
A. It writes when it receives a signal from the checkpoint process (CKPT).
B. It writes concurrently to all members of multiplexed redo log groups.
C. It writes after the Database Writer process writes dirty buffers to disk.
D. It writes when a user commits a transaction.
D. It writes when a user commits a transaction.
207
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?
A. connecting by using an external naming method
B. connecting by using the easy connect method
C. creating a service in the database by using the dbms\_service.create\_service procedure and using this
service for creating a local naming service
D. connecting by using the local naming method with the server = dedicated parameter set in the
tnsnames.ora file for the net service
E. connecting by using a directory naming method
D. connecting by using the local naming method with the server = dedicated parameter set in the
tnsnames.ora file for the net service
208
The HR user receives the following error while inserting data into the sales table:
ERROR at line 1:
ORA-01653: unable to extend table HR.SALES by 128 in tablespace USERS
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.)
A. Altering the data file associated with the USERS tablespace to extend automatically
B. Adding a data file to the USERS tablespace
C. Changing segment space management for the USERS tablespace to manual
D. Creating a new tablespace with autoextend enabled and changing the default tablespace of the HR user
to the new tablespace
E. Enabling resumable space allocation by setting the RESUMABLE\_TIMEOUT parameter to a nonzero value
A. Altering the data file associated with the USERS tablespace to extend automatically
B. Adding a data file to the USERS tablespace
209
Which task would you recommend before using the Database Upgrade Assistant (DBUA) to upgrade a single-instance Oracle 11g R2 database to Oracle Database 12c?
A. shutting down the database instance that is being upgraded
B. executing the catctl.pl script to run the upgrade processes in parallel
C. running the Pre-Upgrade Information Tool
D. copying the listener.ora file to the new ORACLE\_HOME
C. running the Pre-Upgrade Information Tool
210
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?
A. Only sessions based on static listener registrations are disconnected
B. Existing connections are not disconnected; however, they cannot perform any operations until the
listener completes the re-registration of the database instance and service handlers.
C. The sessions are not affected and continue to function normally.
D. All the sessions are terminated and active transactions are rolled back.
C. The sessions are not affected and continue to function normally.
211
Examine the query and its output:
What might have caused three of the alerts to disappear?
A. The threshold alerts were cleared and transferred to DBA\_ALERT\_HISTORY.
B. An Automatic Workload Repository (AWR) snapshot was taken before the execution of the second
query.
C. An Automatic Database Diagnostic Monitor (ADOM) report was generated before the execution of the
second query.
D. The database instance was restarted before the execution of the second query
A. The threshold alerts were cleared and transferred to DBA\_ALERT\_HISTORY.
213
Which two statements are true? (Choose two.)
A. A role cannot be assigned external authentication.
B. A role can be granted to other roles.
C. A role can contain both system and object privileges.
D. The predefined resource role includes the unlimited\_tablespace privilege.
E. All roles are owned by the sys user.
F. The predefined connect role is always automatically granted to all new users at the time of their creation
B. A role can be granted to other roles.
C. A role can contain both system and object privileges.
214
Identify three valid options for adding a pluggable database (PDB) to an existing multitenant container
database (CDB).
A. Use the CREATE PLUGGABLE DATABASE statement to create a PDB using the files from the SEED.
B. Use the CREATE DATABASE . . . ENABLE PLUGGABLE DATABASE statement to provision a PDB by
copying file from the SEED.
C. Use the DBMS\_PDB package to clone an existing PDB.
D. Use the DBMS\_PDB package to plug an Oracle 12c non-CDB database into an existing CDB.
E. Use the DBMS\_PDB package to plug an Oracle 11 g Release 2 (11.2.0.3.0) non-CDB database into an
existing CDB.
A. Use the CREATE PLUGGABLE DATABASE statement to create a PDB using the files from the SEED.
C. Use the DBMS\_PDB package to clone an existing PDB.
D. Use the DBMS\_PDB package to plug an Oracle 12c non-CDB database into an existing CDB.
215
The following parameter are set for your Oracle 12c database instance:
OPTIMIZER\_CAPTURE\_SQL\_PLAN\_BASELINES=FALSE
OPTIMIZER\_USE\_SQL\_PLAN\_BASELINES=TRUE
You want to manage the SQL plan evolution task manually.
Examine the following steps:
1. Set the evolve task parameters.
2. Create the evolve task by using the DBMS\_SPM.CREATE\_EVOLVE\_TASK function.
3. Implement the recommendations in the task by using the DBMS\_SPM.IMPLEMENT\_EVOLVE\_TASK
function.
4. Execute the evolve task by using the DBMS\_SPM.EXECUTE\_EVOLVE\_TASK function.
5. Report the task outcome by using the DBMS\_SPM.REPORT\_EVOLVE\_TASK function.
Identify the correct sequence of steps:
A. 2, 4, 5
B. 2, 1, 4, 3, 5
C. 1, 2, 3, 4, 5
D. 1, 2, 4, 5
B. 2, 1, 4, 3, 5
216
In a recent Automatic Workload Repository (AWR) report for your database, you notice a high number of
buffer busy waits.
The database consists of locally managed tablespaces with free list managed segments.
On further investigation, you find that buffer busy waits is caused by contention on data blocks.
Which option would you consider first to decrease the wait event immediately?
A. Decreasing PCTUSED
B. Decreasing PCTFREE
C. Increasing the number of DBWN process
D. Using Automatic Segment Space Management (ASSM)
E. Increasing db\_buffer\_cache based on the V$DB\_CACHE\_ADVICE recommendation
D. Using Automatic Segment Space Management (ASSM)
217
Examine the following command:
CREATE TABLE (prod\_id number(4),
Prod\_name varchar2 (20),
Category\_id number(30),
Quantity\_on\_hand number (3) INVISIBLE);
Which three statements are true about using an invisible column in the PRODUCTS table? (Choose three.)
A. The %ROWTYPE attribute declarations in PL/SQL to access a row will not display the invisible column
in the output.
B. The DESCRIBE commands in SQL \*Plus will not display the invisible column in the output.
C. Referential integrity constraint cannot be set on the invisible column.
D. The invisible column cannot be made visible and can only be marked as unused
E. A primary key constraint can be added on the invisible column.
A. The %ROWTYPE attribute declarations in PL/SQL to access a row will not display the invisible column
in the output.
B. The DESCRIBE commands in SQL \*Plus will not display the invisible column in the output.
E. A primary key constraint can be added on the invisible column.
218
What are two benefits of installing Grid Infrastructure software for a stand-alone server before installing and
creating an Oracle database?
A. Effectively implements role separation
B. Enables you to take advantage of Oracle Managed Files.
C. Automatically registers the database with Oracle Restart.
D. Helps you to easily upgrade the database from a prior release.
E. Enables the Installation of Grid Infrastructure files on block or raw devices
A. Effectively implements role separation
C. Automatically registers the database with Oracle Restart.
219
Identify two correct statements about multitenant architectures.
A. Multitenant architecture can be deployed only in a Real Application Clusters (RAC) configuration.
B. Multiple pluggable databases (PDBs) share certain multitenant container database (CDB) resources.
C. Multiple CDBs share certain PDB resources.
D. Multiple non-RAC CDB instances can mount the same PDB as long as they are on the same server
E. Patches are always applied at the CDB level.
F. A PDB can have a private undo tablespace.
B. Multiple pluggable databases (PDBs) share certain multitenant container database (CDB) resources.
E. Patches are always applied at the CDB level.
220
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.
A. Missing or stale histogram statistics
B. Undersized shared pool
C. High clustering factor for the indexes
D. High value for the DB\_FILE\_MULTIBLOCK\_READ\_COUNT parameter
E. Oversized buffer cache
A. Missing or stale histogram statistics
C. High clustering factor for the indexes
D. High value for the DB\_FILE\_MULTIBLOCK\_READ\_COUNT parameter
221
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.)
A. Bind Variable Peeking
B. SQL Plan Baselines
C. Adaptive Cursor Sharing
D. Bind variable used in a SQL statement
E. Literals in a SQL statement
A. Bind Variable Peeking
C. Adaptive Cursor Sharing
D. Bind variable used in a SQL statement
222
You upgraded your database from pre-12c to a multitenant container database (CDB) containing pluggable
databases (PDBs).
Examine the query and its output:
Which two tasks must you perform to add users with SYSBACKUP, SYSDG, and SYSKM privilege to the
password file? (Choose two.)
A. Assign the appropriate operating system groups to SYSBACKUP, SYSDG, SYSKM.
B. Grant SYSBACKUP, SYSDG, and SYSKM privileges to the intended users.
C. Re-create the password file with SYSBACKUP, SYSDG, and SYSKM privilege and the FORCE argument set to No.
D. Re-create the password file with SYSBACKUP, SYSDG, and SYSKM privilege, and FORCE arguments
set to Yes.
E. Re-create the password file in the Oracle Database 12c format.
B. Grant SYSBACKUP, SYSDG, and SYSKM privileges to the intended users.
D. Re-create the password file with SYSBACKUP, SYSDG, and SYSKM privilege, and FORCE arguments
set to Yes.
223
Which three statements are true about Automatic Workload Repository (AWR)? (Choose three.)
A. All AWR tables belong to the SYSTEM schema.
B. The AWR data is stored in memory and in the database.
C. The snapshots collected by AWR are used by the self-tuning components in the database
D. 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.
E. AWR contains system wide tracing and logging information.
B. The AWR data is stored in memory and in the database.
C. The snapshots collected by AWR are used by the self-tuning components in the database
D. 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.
225
Examine the following query output:
You issue the following command to import tables into the hr schema:
$ \> impdp hr/hr directory = dumpdir dumpfile = hr\_new.dmp schemas=hr
TRANSFORM=DISABLE\_ARCHIVE\_LOGGING: Y
Which statement is true?
A. All database operations performed by the impdp command are logged.
B. Only CREATE INDEX and CREATE TABLE statements generated by the import are logged.
C. Only CREATE TABLE and ALTER TABLE statements generated by the import are logged.
D. None of the operations against the master table used by Oracle Data Pump to coordinate its activities
are logged.
C. Only CREATE TABLE and ALTER TABLE statements generated by the import are logged.
226
Examine the parameter for your database instance:
Identify the reason why the optimizer chose different execution plans.
A. The optimizer used a dynamic plan for the query.
B. The optimizer chose different plans because automatic dynamic sampling was enabled.
C. The optimizer used re-optimization cardinality feedback for the query.
D. The optimizer chose different plan because extended statistics were created for the columns used.
A. The optimizer used a dynamic plan for the query.
227
You are planning the creation of a new multitenant container database (CDB) and want to store the ROOT
and SEED container data files in separate directories.
You plan to create the database using SQL statements.
Which three techniques can you use to achieve this? (Choose three.)
A. Use Oracle Managed Files (OMF).
B. Specify the SEED FILE\_NAME\_CONVERT clause.
C. Specify the PDB\_FILE\_NAME\_CONVERT initialization parameter.
D. Specify the DB\_FILE\_NAMECONVERT initialization parameter.
E. Specify all files in the CREATE DATABASE statement without using Oracle managed Files (OMF).
A. Use Oracle Managed Files (OMF).
B. Specify the SEED FILE\_NAME\_CONVERT clause.
C. Specify the PDB\_FILE\_NAME\_CONVERT initialization parameter.
228
You are about to plug a multi-terabyte non-CDB into an existing multitenant container database (CDB).
The characteristics of the non-CDB are as follows:
Version: Oracle Database 11g Release 2 (11.2.0.2.0) 64-bit
Character set: AL32UTF8
National character set: AL16UTF16
O/S: Oracle Linux 6 64-bit
The characteristics of the CDB are as follows:
Version: Oracle Database 12c Release 1 64-bit
Character Set: AL32UTF8
National character set: AL16UTF16
O/S: Oracle Linux 6 64-bit
Which technique should you use to minimize down time while plugging this non-CDB into the CDB?
A. Transportable database
B. Transportable tablespace
C. Data Pump full export/import
D. The DBMS\_PDB package
E. RMAN
B. Transportable tablespace
231
Examine the contents of SQL loader control file:
Which three statements are true regarding the SQL\* Loader operation performed using the control file?
(Choose three.)
A. An EMP table is created if a table does not exist. Otherwise, if the EMP table is appended with the
loaded data.
B. The SQL\* Loader data file myfile1.dat has the column names for the EMP table.
C. The SQL\* Loader operation fails because no record terminators are specified.
D. Field names should be the first line in the both the SQL\* Loader data files.
E. The SQL\* Loader operation assumes that the file must be a stream record format file with the normal
carriage return string as the record terminator.
A. An EMP table is created if a table does not exist. Otherwise, if the EMP table is appended with the
loaded data.
B. The SQL\* Loader data file myfile1.dat has the column names for the EMP table.
E. The SQL\* Loader operation assumes that the file must be a stream record format file with the normal
carriage return string as the record terminator.
232
You create a new pluggable database, HR\_PDB, from the seed database. Which three tablespaces are
created by default in HR\_PDB? (Choose three.)
A. SYSTEM
B. SYSAUX
C. EXAMPLE
D. UNDO
E. TEMP
F. USERS
A. SYSTEM
B. SYSAUX
E. TEMP
233
Which two statements are true about variable extent size support for large ASM files? (Choose two.)
A. The metadata used to track extents in SGA is reduced.
B. Rebalance operations are completed faster than with a fixed extent size
C. An ASM Instance automatically allocates an appropriate extent size.
D. Resync operations are completed faster when a disk comes online after being taken offline.
E. Performance improves in a stretch cluster configuration by reading from a local copy of an extent.
A. The metadata used to track extents in SGA is reduced.
C. An ASM Instance automatically allocates an appropriate extent size.
234
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.)
A. Data is redacted for the EMP.SAL column only if the SCOTT session does not have the MGR role set.
B. Data is redacted for EMP.SAL column only if the SCOTT session has the MGR role set.
C. Data is never redacted for the EMP\_V.SAL column.
D. Data is redacted for the EMP\_V.SAL column only if the SCOTT session has the MGR role set.
E. Data is redacted for the EMP\_V.SAL column only if the SCOTT session does not have the MGR role
set.
A. Data is redacted for the EMP.SAL column only if the SCOTT session does not have the MGR role set.
C. Data is never redacted for the EMP\_V.SAL column.
235
In your multitenant container database (CDB) containing pluggable database (PDBs), the HR user executes
the following commands to create and grant privileges on a procedure:
CREATE OR REPLACE PROCEDURE create\_test\_v (v\_emp\_id NUMBER, v\_ename VARCHAR2,
v\_SALARY NUMBER, v\_dept\_id NUMBER)
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?
A. Create the CREATE\_TEST procedure with definer’s rights.
B. Grant the EXECUTE privilege to users with GRANT OPTION on the CREATE\_TEST procedure.
C. Create the CREATE\_TEST procedure with invoker’s rights.
D. Create the CREATE\_TEST procedure as part of a package and grant users the EXECUTE privilege the
package.
C. Create the CREATE\_TEST procedure with invoker’s rights.
236
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\_SMART\_FLASH\_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?
A. Set DB\_FLASH\_CACHE\_ZISE = 64G.
B. Set DB\_FLASH\_CACHE\_ZISE = 64G, 64G
C. Set DB\_FLASH\_CACHE\_ZISE = 128G.
D. DB\_FLASH\_CACHE\_SIZE is automatically configured by the instance at startup.
B. Set DB\_FLASH\_CACHE\_ZISE = 64G, 64G
238
Which two statements are true concerning the Resource Manager plans for individual pluggable databases
(PDB plans) in a multitenant container database
(CDB)? (Choose two.)
A. 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.
B. In a PDB plan, subplans may be used with up to eight consumer groups.
C. If a PDB plan is enabled for a pluggable database, then resources are allocated to consumer groups
across all PDBs in the CDB.
D. If no PDB plan is enabled for a pluggable database, then the PDB share in the CDB plan is dynamically
calculated.
E. 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
A. 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.
E. 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
239
You use a recovery catalog for maintaining your database backups.
You execute the following command:
$rman TARGET / CATALOG rman / cat@catdb
RMAN \> BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
Which two statements are true? (Choose two.)
A. Corrupted blocks, if any, are repaired.
B. Checks are performed for physical corruptions.
C. Checks are performed for logical corruptions.
D. Checks are performed to confirm whether all database files exist in correct locations
E. Backup sets containing both data files and archive logs are created.
B. Checks are performed for physical corruptions.
D. Checks are performed to confirm whether all database files exist in correct locations
241
Which three statements are true about using flashback database in a multitenant container database
A. The root container can be flashed back without flashing back the pluggable databases (PDBs).
B. To enable flashback database, the CDB must be mounted.
C. Individual PDBs can be flashed back without flashing back the entire CDB.
D. The DB\_FLASHBACK RETENTION\_TARGET parameter must be set to enable flashback of the CDB.
E. A CDB can be flashed back specifying the desired target point in time or an SCN, but not a restore
point.
| (CDB)? (Choose three.)
A. The root container can be flashed back without flashing back the pluggable databases (PDBs).
B. To enable flashback database, the CDB must be mounted.
D. The DB\_FLASHBACK RETENTION\_TARGET parameter must be set to enable flashback of the CDB.
242
You support Oracle Database 12c Oracle Database 11g, and Oracle Database log on the same server.
All databases of all versions use Automatic Storage Management (ASM).
Which three statements are true about the ASM disk group compatibility attributes that are set for a disk
group? (Choose three.)
A. The ASM compatibility attribute controls the format of the disk group metadata.
B. RDBMS compatibility together with the database version determines whether a database Instance can
mount the ASM disk group.
C. The RDBMS compatibility setting allows only databases set to the same version as the compatibility
value, to mount the ASM disk group.
D. The ASM compatibility attribute determines some of the ASM features that may be used by the Oracle
disk group.
E. The ADVM compatibility attribute determines the ACFS features that may be used by the Oracle 10 g
database.
A. The ASM compatibility attribute controls the format of the disk group metadata.
B. RDBMS compatibility together with the database version determines whether a database Instance can
mount the ASM disk group.
D. The ASM compatibility attribute determines some of the ASM features that may be used by the Oracle
disk group.
243
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.)
A. It will permit the use of uppercase passwords for database users who have been granted the SYSOPER
role.
B. It contains username and passwords of database users who are members of the OSOPER operating
system group.
C. It contains usernames and passwords of database users who are members of the OSDBA operating
system group.
D. It will permit the use of lowercase passwords for database users who have granted the SYSDBA role.
E. It will not permit the use of mixed case passwords for the database users who have been granted the
SYSDBA role.
A. It will permit the use of uppercase passwords for database users who have been granted the SYSOPER
role.
D. It will permit the use of lowercase passwords for database users who have granted the SYSDBA role.
244
Identify three valid methods of opening, pluggable databases (PDBs).
A. ALTER PLUGGABLE DATABASE OPEN ALL ISSUED from the root
B. ALTER PLUGGABLE DATABASE OPEN ALL ISSUED from a PDB
C. ALTER PLUGGABLE DATABASE PDB OPEN issued from the seed
D. ALTER DATABASE PDB OPEN issued from the root
E. ALTER DATABASE OPEN issued from that PDB
F. ALTER PLUGGABLE DATABASE PDB OPEN issued from another PDB
G. ALTER PLUGGABLE DATABASE OPEN issued from that PDB
A. ALTER PLUGGABLE DATABASE OPEN ALL ISSUED from the root
E. ALTER DATABASE OPEN issued from that PDB
G. ALTER PLUGGABLE DATABASE OPEN issued from that PDB
245
Examine the parameters for your database instance:
Which three statements are true about the process of automatic optimization by using cardinality feedback?
(Choose three.)
A. The optimizer automatically changes a plan during subsequent execution of a SQL statement if there is
a huge difference in optimizer estimates and execution statistics.
B. The optimizer can re optimize a query only once using cardinality feedback.
C. The optimizer enables monitoring for cardinality feedback after the first execution of a query.
D. The optimizer does not monitor cardinality feedback if dynamic sampling and multicolumn statistics are
enabled.
E. After the optimizer identifies a query as a re-optimization candidate, statistics collected by the collectors are submitted to the optimizer.
A. The optimizer automatically changes a plan during subsequent execution of a SQL statement if there is
a huge difference in optimizer estimates and execution statistics.
C. The optimizer enables monitoring for cardinality feedback after the first execution of a query.
D. The optimizer does not monitor cardinality feedback if dynamic sampling and multicolumn statistics are
enabled.
246
You administer an online transaction processing (OLTP) system whose database is stored in
Automatic Storage Management (ASM) and whose disk group use normal redundancy.
One of the ASM disks goes offline, and is then dropped because it was not brought online before
DISK\_REPAIR\_TIME elapsed.
When the disk is replaced and added back to the disk group, the ensuing rebalance operation is too slow.
Which two recommendations should you make to speed up the rebalance operation if this type of
failure happens again? (Choose two.)
A. Increase the value of the ASM\_POWER\_LIMIT parameter.
B. Set the DISK\_REPAIR\_TIME disk attribute to a lower value.
C. Specify the statement that adds the disk back to the disk group.
D. Increase the number of ASMB processes.
E. Increase the number of DBWR\_IO\_SLAVES in the ASM instance.
A. Increase the value of the ASM\_POWER\_LIMIT parameter.
D. Increase the number of ASMB processes.
247
You are connected using SQL\* Plus to a multitenant container database (CDB) with SYSDBA privileges and
execute the following sequence statements:
What is the result of the last SET CONTAINER statement and why is it so?
A. It succeeds because the PDB\_ADMIN user has the required privileges.
B. It fails because common users are unable to use the SET CONTAINER statement.
C. It fails because local users are unable to use the SET CONTAINER statement.
D. If fails because the SET CONTAINER statement cannot be used with PDB$SEED as the target pluggable database (PDB).
C. It fails because local users are unable to use the SET CONTAINER statement.
248
Examine the details of the Top 5 Timed Events in the following Automatic Workloads Repository (AWR)
report:
What are three possible causes for the latch-related wait events?
A. The size of the shared pool is too small.
B. Cursors are not being shared.
C. A large number COMMITS are being performed.
D. There are frequent logons and logoffs.
E. The buffers are being read into the buffer cache, but some other session is changing the buffers.
A. The size of the shared pool is too small.
B. Cursors are not being shared.
D. There are frequent logons and logoffs.
249
Which two partitioned table maintenance operations support asynchronous Global Index Maintenance in
Oracle database 12c? (Choose two.)
A. ALTER TABLE SPLIT PARTITION
B. ALTER TABLE MERGE PARTITION
C. ALTER TABLE TRUNCATE PARTITION
D. ALTER TABLE ADD PARTITION
E. ALTER TABLE DROP PARTITION
F. ALTER TABLE MOVE PARTITION
C. ALTER TABLE TRUNCATE PARTITION
E. ALTER TABLE DROP PARTITION
250
Your multitenant container database (CDB) contains some pluggable databases (PDBs), you execute the
following command in the root container:
Which two statements are true? (Choose two.)
A. Schema objects owned by the C# # A\_ADMIN common user can be shared across all PDBs
B. The C # # A\_ADMIN user will be able to use the TEMP\_TS temporary tablespace only in root.
C. The command will, create a common user whose description is contained in the root and each PDB.
D. The schema for the common user C # # A\_ADMIN can be different in each container.
E. The command will create a user in the root container only because the container clause is not used.
C. The command will, create a common user whose description is contained in the root and each PDB.
D. The schema for the common user C # # A\_ADMIN can be different in each container.
251
You find this query being used in your Oracle 12c database:
Which method a used by the optimizer to limit the rows being returned?
A. A filter is added to the table query dynamically using ROWNUM to limit the rows to 20 percent of the
total rows
B. All the rows are returned to the client or middle tier but only the first 20 percent are returned to the
screen or the application.
C. A view is created during execution and a filter on the view limits the rows to 20 percent of the total rows.
D. A TOP-N query is created to limit the rows to 20 percent of the total rows
C. A view is created during execution and a filter on the view limits the rows to 20 percent of the total rows.
252
Which four actions are possible during an Online Data file Move operation? (Choose four.)
A. Creating and dropping tables in the data file being moved
B. Performing file shrink of the data file being moved
C. Querying tables in the data file being moved
D. Performing Block Media Recovery for a data block in the data file being moved
E. Flashing back the database
F. Executing DML statements on objects stored in the data file being moved
A. Creating and dropping tables in the data file being moved
C. Querying tables in the data file being moved
D. Performing Block Media Recovery for a data block in the data file being moved
F. Executing DML statements on objects stored in the data file being moved
253
Your multitenant container database (CDB) contains a pluggable database, HR\_PDB.
The default permanent tablespace in HR\_PDB is USERDATA.
The container database (CDB) is open and you connect RMAN.
You want to issue the following RMAN command:
RMAN \> BACKUP TABLESPACE hr\_pdb:userdata;
Which task should you perform before issuing the command?
A. Place the root container in ARHCHIVELOG mode.
B. Take the user data tablespace offline.
C. Place the root container in the nomount stage.
D. Ensure that HR\_PDB is open.
A. Place the root container in ARHCHIVELOG mode.
254
You install a non-RAC Oracle Database.
During Installation, the Oracle Universal Installer (OUI) prompts you to enter the path of the Inventory directory and also to specify an operating system group name.
Which statement is true?
A. The ORACLE\_BASE base parameter is not set.
B. The installation is being performed by the root user.
C. The operating system group that is specified should have the root user as its member.
D. The operating system group that is specified must have permission to write to the inventory directory.
D. The operating system group that is specified must have permission to write to the inventory directory.
255
In your multitenant container database (CDB) with two pluggable database (PDBs).
You want to create a new PDB by using SQL Developer.
Which statement is true?
A. The CDB must be open.
B. The CDB must be in the mount stage.
C. The CDB must be in the nomount stage.
D. Alt existing PDBs must be closed.
A. The CDB must be open.
257
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.)
A. Thread creation must be routed through a dispatcher process
B. The local listener may spawn a now process and have that new process create a thread
C. Each Oracle process runs an SCMN thread.
D. Each multithreaded Oracle process has an SCMN thread.
E. The local listener may pass the request to an existing process which in turn will create a thread.
A. Thread creation must be routed through a dispatcher process
D. Each multithreaded Oracle process has an SCMN thread.
E. The local listener may pass the request to an existing process which in turn will create a thread.
260
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? Select two.
A. SYS, SYSTEM
B. SCOTT
C. Only for successful executions
D. Only for failed executions
E. Both successful and failed executions
A. SYS, SYSTEM
E. Both successful and failed executions
261
A database is stored in an Automatic Storage Management (ASM) disk group, disk group, DGROUP1 with
SQL:
There is enough free space in the disk group for mirroring to be done.
What happens if the CONTROLLER1 failure group becomes unavailable due to error of for maintenance?
A. Transactions and queries accessing database objects contained in any tablespace stored in DGROUP1
will fall.
B. Mirroring of allocation units will be done to ASM disks in the CONTROLLER2 failure group until the
CONTROLLER1 for failure group is brought back online.
C. The data in the CONTROLLER1 failure group is copied to the controller2 failure group and rebalancing
is initiated.
D. ASM does not mirror any data until the controller failure group is brought back online, and newly
allocated primary allocation units (AU) are stored in the controller2 failure group, without mirroring.
E. Transactions accessing database objects contained in any tablespace stored in DGROUP1 will fail but
queries will succeed.
D. ASM does not mirror any data until the controller failure group is brought back online, and newly
allocated primary allocation units (AU) are stored in the controller2 failure group, without mirroring.
264
On your Oracle Database, you issue the following commands to create indexes:
SQL \> CREATE INDEX oe.ord\_customer\_ix1 ON oe.orders (customer\_id, sales\_rep\_id) INVISIBLE;
SQL\> CREATE BITMAP INDEX oe.ord\_customer\_ix2 ON oe.orders (customer\_id, sales\_rep\_id);
Which two statements are true? (Choose two.)
A. Only the ORD\_CUSTOMER\_IX1 index created.
B. Both the indexes are updated when a row is inserted, updated, or deleted in the ORDERS table.
C. Both the indexes are created: however, only ORD\_CUSTOMERS\_IX1 is used by the optimizer for
queries on the ORDERS table.
D. The ORD\_CUSTOMER\_IX1 index is not used by the optimizer even when the
OPTIMIZER\_USE\_INVISIBLE\_INDEXES parameters is set to true.
E. Both the indexes are created and used by the optimizer for queries on the ORDERS table.
F. Both the indexes are created: however, only ORD\_CUSTOMERS\_IX2 is used by the optimizer for
queries on the ORDERS table.
B. Both the indexes are updated when a row is inserted, updated, or deleted in the ORDERS table.
F. Both the indexes are created: however, only ORD\_CUSTOMERS\_IX2 is used by the optimizer for
queries on the ORDERS table.
265
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.)
A. The index remains invisible.
B. The index is not updated by the DML statements on the indexed table.
C. The index automatically becomes visible in order to have it updated by DML on the table.
D. The index becomes unusable but the table is updated by the DML.
E. The index is updated by the DML on the table.
A. The index remains invisible.
E. The index is updated by the DML on the table.
266
The persistent configuration settings for RMAN have default for all parameters.
Identify four RMAN commands that produce a multi-section backup.
A. BACKUP TABLESPACE SYSTEM SECTION SIZE 100M;
B. BACKUP AS COPY TABLESPACE SYSTEM SECTION SIZE 100M;
C. BACKUP ARCHIVELOG ALL SECTION SIZE 25M;
D. BACKUP TABLESPACE "TEMP" SECTION SIZE 10M;
E. BACKUP TABLESPACE "UNDO" INCLUDE CURRENT CONTROLFILE SECTION SIZE 100M;
F. BACKUP SPFILE SECTION SIZE 1M;
G. BACKUP INCREMENTAL LEVEL 0 TABLESPACE SYSAUX SECTION SIZE 100M;
A. BACKUP TABLESPACE SYSTEM SECTION SIZE 100M;
B. BACKUP AS COPY TABLESPACE SYSTEM SECTION SIZE 100M;
E. BACKUP TABLESPACE "UNDO" INCLUDE CURRENT CONTROLFILE SECTION SIZE 100M;
G. BACKUP INCREMENTAL LEVEL 0 TABLESPACE SYSAUX SECTION SIZE 100M;
267
You have the following entry in the tnsnames.ors of your hq.us.example.com host machine:
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?.
A. The connection succeeds, provided the NAMES.DEFAULT\_DOMAIN parameter is set to
us.example.com in the sqlnet.ora file on the client side.
B. The connection fails because the net service name does not have the suffix us.example.com.
C. The connection succeeds, provided the SERVICE\_NAMES initialization parameter is set to ORCL.
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.
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.
268
Your database has the SRV1 service configured for an application that runs on middle-tier application
server.
The application has multiple modules.
You enable tracing at the service level by executing the following command:
SQL \> exec DBMS\_MONITOR.SERV\_MOD\_ACT\_TRACE\_ENABLE (SRV1);
The possible outcome and actions to aggregate the trace files are as follows:
1. The command fails because a module name is not specified.
2. A trace file is created for each session that is running the SRV1 service.
3. An aggregated trace file is created for all the sessions that are running the SRV1 service.
4. The trace files may be aggregated by using the trcess utility.
5. The trace files be aggregated by using the tkprof utility.
Identify the correct outcome and the step to aggregate by using tkprof utility?
A. 1
B. 2 and 4
C. 2 and 5
D. 3 and 4
E. 3 and 5
B. 2 and 4
269
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?
A. It executes successfully and creates an UNDO tablespace in HR\_PDB.
B. It falls and reports an error because there can be only one undo tablespace in a CDB.
C. It fails and reports an error because the CONTAINER=ALL clause is not specified in the command.
D. It fails and reports an error because the CONTAINER=CURRENT clause is not specified in the
command.
E. It executes successfully but neither tablespace nor the data file is created.
E. It executes successfully but neither tablespace nor the data file is created
270
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.)
A. The database must have multiplexed redo logs for the flashback to succeed.
B. The database must be MOUNTED for the flashback to succeed.
C. The database must use block change tracking for the flashback to succeed.
D. The database must be opened in restricted mode for the flashback to succeed.
E. The database must be opened with the RESETLOGS option after the flashback is complete.
F. The database must be opened in read-only mode to check if the database has been flashed back to the
correct SCN.
B. The database must be MOUNTED for the flashback to succeed.
E. The database must be opened with the RESETLOGS option after the flashback is complete.
271
Which two statements are true about Oracle Data Pump export and import operations? (Choose two.)
A. You can detach from a data pump export job and reattach later.
B. Data pump uses parallel execution server processes to implement parallel import.
C. Data pump import requires the import file to be in a directory owned by the oracle owner.
D. The master table is the last object to be exported by the data pump.
E. You can detach from a data pump import job and reattach later.
A. You can detach from a data pump export job and reattach later.
E. You can detach from a data pump import job and reattach later.
272
Examine the following command;
ALTER SYSTEM SET enable\_ddl\_logging = TRUE;
Which statement is true?
A. Only the data definition language (DDL) commands that resulted in errors are logged in the alert log file.
B. All DDL commands are logged in the alert log file.
C. All DDL commands are logged in a different log file that contains DDL statements and their execution
dates.
D. Only DDL commands that resulted in the creation of new segments are logged.
E. All DDL commands are logged in XML format in the alert directory under the Automatic Diagnostic Repository (ADR) home.
E. All DDL commands are logged in XML format in the alert directory under the Automatic Diagnostic Repository (ADR) home.
273
Which two are prerequisites for performing a flashback transaction? (Choose two.)
A. Flashback Database must be enabled.
B. Undo retention guarantee for the database must be configured.
C. EXECUTE privilege on the DBMS\_FLASHBACK package must be granted to the user flashing back transaction.
D. Supplemental logging must be enabled.
E. Recycle bin must be enabled for the database.
F. Block change tracking must be enabled tor the database.
C. EXECUTE privilege on the DBMS\_FLASHBACK package must be granted to the user flashing back transaction.
D. Supplemental logging must be enabled.
275
Your multitenant container database has three pluggable databases (PDBs): PDB1, PDB2, and PDB3.
Which two RMAN commands may be; used to back up only the PDB1 pluggable database? (Choose two.)
A. BACKUP PLUGGABLE DATABASE PDB1 while connected to the root container
B. BACKUP PLUGGABLE DATABASE PDB1 while connected to the PDB1 container
C. BACKUP DATABASE while connected to the PDB1 container
D. BACKUP DATABASE while connected to the boot container
E. BACKUP PLUGGABLE database PDB1 while connected to PDB2
A. BACKUP PLUGGABLE DATABASE PDB1 while connected to the root container
C. BACKUP DATABASE while connected to the PDB1 container
276
Your multitenant container (CDB) contains two pluggable databases (PDB), HR\_PDB and ACCOUNTS\_PDB, both of which use the CDB tablespace. The temp file is called temp01.tmp.
A user issues a query on a table on one of the PDBs and receives the following error:
ERROR at line 1:
ORA-01565: error in identifying file /u01/app/oracle/oradata/CDB1/temp01.tmp
ORA-27037: unable to obtain file status
Identify two ways to rectify the error.
A. Add a new temp file to the temporary tablespace and drop the temp file that that produced the error.
B. Shut down the database instance, restore the temp01.tmp file from the backup, and then restart the
database.
C. Take the temporary tablespace offline, recover the missing temp file by applying redo logs, and then
bring the temporary tablespace online.
D. Shutdown the database instance, restore and recover the temp file from the backup, and then open the
database with RESETLOGS.
E. Shut down the database instance and then restart the CDB and PDBs.
A. Add a new temp file to the temporary tablespace and drop the temp file that that produced the error.
E. Shut down the database instance and then restart the CDB and PDBs.
277
You set the following parameters in the parameter file and restart the database instance:
Which two statements are true? (Choose two.)
A. The MEMORY\_MAX\_TARGET parameter is automatically set to 500 MB.
B. The PGA\_AGGREGATE\_TARGET and SGA\_TARGET parameters are automatically set to zero.
C. The value of the MEMORY\_MAX\_TARGET parameter remains zero for the database instance.
D. The lower limits of the PGA\_AGGREGATE\_TARGET and SGA\_TARGET parameters are set to 90 MB
and 270 MB respectively.
E. The instance does not start up because Automatic Memory Management (AMM) is enabled but
PGA\_AGGREGATE\_TARGET and SGA\_TARGET parameters are set to nonzero values.
A. The MEMORY\_MAX\_TARGET parameter is automatically set to 500 MB.
D. The lower limits of the PGA\_AGGREGATE\_TARGET and SGA\_TARGET parameters are set to 90 MB
and 270 MB respectively.
278
Your database is configured in ARCHIVELOG mode.
Examine the RMAN configuration parameters:
Examine the command:
RMAN\> BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;
What is the outcome?
A. It fails because the DELETE INPUT option can be used only with the BACKUP AS BACKUPSET
command.
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.
C. It executes successfully and creates an image copy of the database along with archive log files and then
deletes the original archived log files.
D. It fails because the DELETE INPUT option can be used only with the BACKUP AS COPY command.
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.
279
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?
A. Place CDB1 in the mount stage and then the USERDATA tablespace offline in ACCOUNTS\_PDB.
B. Place CDB1 in the mount stage and issue the ALTER PLUGGABLE DATABASE accounts\_pdb CLOSE
IMMEDIATE command.
C. Issue the ALTER PLUGGABLE DATABASE accounts\_pdb RESTRICTED command.
D. Take the USERDATA tablespace offline in ACCOUNTS\_PDB.
D. Take the USERDATA tablespace offline in ACCOUNTS\_PDB.
280
Which two statements are true about the Automatic Database Diagnostic Monitor (ADDM)? (Choose two.)
A. The ADDM requires at least four AWR snapshots for analysis
B. The ADDM runs after each AWR snapshot is collected automatically by MMON
C. The results of the ADDM analysis are stored in the Automatic Workload Repository (AWR)
D. The ADDM analysis provides only diagnostics information but does not provide recommendations
E. The ADDM calls other advisors if required, but does not provide recommendations about the advisors
B. The ADDM runs after each AWR snapshot is collected automatically by MMON
C. The results of the ADDM analysis are stored in the Automatic Workload Repository (AWR)
281
In your database, USERS is the default permanent tablespace.
Examine the commands and their outcome:
Which two statements are true? (Choose two.)
A. The MYTAB table is created in the SYSTEM tablespace but no rows can be inserted into the table by USER02.
B. The MYTAB table is created in the SYSTEM tablespace and rows can be inserted into the table by USER02.
C. The MYTAB table is created in the USERS tablespace but no rows can be inserted into the table by USER02.
D. The CREATE TABLE statement generates an error because the SYSDBA privilege does not provide
any space quota on the SYSTEM tablespace by default.
E. The MYTAB table is owned by the SYS user.
B. The MYTAB table is created in the SYSTEM tablespace and rows can be inserted into the table by USER02.
E. The MYTAB table is owned by the SYS user.
282
Which three statements are true about space usage alerts? (Choose three.)
A. Alerts are issued only when the critical threshold for space available in a tablespace is breached.
B. The sum of active extents and allocated user quotas is considered to compute space usage for an undo
tablespace.
C. Database alerts can provide warnings about low space availability at both tablespace and segment levels.
D. Alerts are not issued for locally managed tablespaces that are offline or in read-only mode.
E. A newly created locally managed tablespace is automatically assigned the default threshold values
defined for a database.
C. Database alerts can provide warnings about low space availability at both tablespace and segment levels.
D. Alerts are not issued for locally managed tablespaces that are offline or in read-only mode.
E. A newly created locally managed tablespace is automatically assigned the default threshold values
defined for a database.
284
Which two statements are true about the (PMON) background process in Oracle Database 12c? (Choose
two.)
A. It records checkpoint information in the control file.
B. It frees unused temporary segments.
C. It kills sessions that exceed idle time.
D. It registers database services with all local and remote listeners known to the database instance.
E. It frees resources held by abnormally terminated processes.
C. It kills sessions that exceed idle time.
E. It frees resources held by abnormally terminated processes.
285
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?
A. Specify the maximum amount of quota a user can be allocated in the undo tablespace.
B. Decrease the number of Interested Transaction List (ITL) slots for the segments on which these users perform transactions.
C. Implement a profile for these users.
D. Implement a Database Resource Manager plan.
D. Implement a Database Resource Manager plan.
286
Examine the parameter settings in your local ORCL database:
As the SYS user, you issue the following command on ORCL:
CREATE PUBLIC DATABASE LINK sales.us.example.com USING sales1;
The sales1 tnsnames entry points to the sales database instance.
Which two are requirements to ensure that you can successfully connect by using this database link?
(Choose two.)
A. The GLOBAL\_NAMES initialization parameter should be set to FALSE in the ORCL database.
B. The SALES1 net service name should be known to both the local and remote databases.
C. Local database users who use this database link should have accounts and the required privileges in the remote database.
D. The REMOTE\_OS\_AUTHENT initialization parameter should be set to FALSE in the remote database.
E. Only the user creating the database link must have an account and the required privileges in the remote
database.
A. The GLOBAL\_NAMES initialization parameter should be set to FALSE in the ORCL database.
C. Local database users who use this database link should have accounts and the required privileges in the remote database.
287
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.
A. 1, 3, 2, 6, 7, 4, 5
B. 1, 3, 7, 2, 6, 4, 5
C. 1, 3, 2, 4, 5
D. 1, 2, 6, 4, 5
E. 1, 6, 2, 4, 5
A. 1, 3, 2, 6, 7, 4, 5
288
What can be automatically implemented after the SQL Tuning Advisor is run as part of the Automated
Maintenance Task?
A. statistics recommendations
B. SQL profile recommendations
C. SQL statement restructure recommendations
D. creation of materialized views to improve query performance
B. SQL profile recommendations
289
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?
A. Run catuppst.sql to perform revalidation actions
B. Run utluiobj.sql to filter out objects that were invalidated by the upgrade process.
C. Run preupgrd.sql and then execute the generated "fix-up" scripts to resolve status issues.
D. Run utlrp.sql to recompile stored PL/SQL and Java code and check the DBA\_REGISTRY view.
D. Run utlrp.sql to recompile stored PL/SQL and Java code and check the DBA\_REGISTRY view.
290
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?
A. Create an event-based job.
B. Create a lightweight job.
C. Specify the job as a member of a job class.
D. Use a job array.
B. Create a lightweight job.
291
```
You plan to install the Oracle Database 12c software on a new server. The database will use Automatic
Storage Management (ASM) and Oracle Restart.
```
Oracle Grid Infrastructure for a standalone server is already installed on the server.
You want to configure job role separation. You create the following operating system users and groups:
- The user oracle as the owner of the Oracle database installation
- The user grid as the owner of Oracle Grid Infrastructure
- The group oinstall as an Oracle Inventory group
- The group dba as the OSDBA group for Oracle database
- The group asmdba as the OSDBA group for Oracle ASM
- The group asmadmin as the administration privileges group for Oracle ASM
- The group asmoper as the group for Oracle ASM
Which two additional tasks should you perform with regard to the OS-level owners and groups? (Choose
two.)
A. creating a separate central inventory group for the Oracle Database 12c installation
B. assigning oinstall as the primary group for the oracle user
C. assigning asmadmin and asmoper as primary groups for the oracle user
D. creating OS groups associated with the OSBACKUPDBA, OSDGDBA, and OSKMDBA system
privileges
E. assigning asmdba as the secondary group for the oracle user
B. assigning oinstall as the primary group for the oracle user
D. creating OS groups associated with the OSBACKUPDBA, OSDGDBA, and OSKMDBA system
privileges
292
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?
A. DEALLOCATE UNUSED
B. SHRINK SPACE CASCADE
C. SHRINK SPACE COMPACT
D. ROW STORE COMPRESS BASIC
C. SHRINK SPACE COMPACT
293
Examine the parameters for a database instance:
Which two statements are true? (Choose two.)
A. Undo records for temporary tables are stored in a temporary tablespace.
B. Undo records for temporary tables are stored in the undo tablespace and logged in the redo.
C. Undo records for temporary tables are stored in the undo tablespace and logged in the redo only for
those sessions where temporary undo is enabled.
D. No redo is generated for the undo records belonging to temporary tables.
E. No redo and undo records are generated for temporary tables.
A. Undo records for temporary tables are stored in a temporary tablespace.
D. No redo is generated for the undo records belonging to temporary tables.
295
A database uses Automatic Storage Management (ASM) as database storage, which has a diskgroup, DATA1, which is created as follows:
What happens when the FAILGRP1 failure group is corrupted?
A. Mirroring of allocation units occurs within the FAILGRP2 failure group.
B. Transactions that are using the diskgroup fail.
C. ASM does not mirror any data and newly allocated primary allocation units (AU) are stored in the FAILGRP2 failure group.
D. Data in the FAILGRP1 failure group is moved to the FAILGRP2 failure group and rebalancing is started.
C. ASM does not mirror any data and newly allocated primary allocation units (AU) are stored in the FAILGRP2 failure group.
297
Your production database uses file system storage.
You want to move storage to Oracle Automatic Storage Management (ASM).
How would you achieve this?
A. by using a transportable database
B. by using the Database Upgrade Assistant (DBUA)
C. by using Data Pump
D. by using RMAN
D. by using RMAN
298
In your Oracle 12c database, you plan to execute the command:
SQL\> CREATE TABLESPACE tbs1 DATAFILE /u02/oracle/data/tbs01.dbf SIZE 50M;
The u02 file system has 1 GB of free space available.
What is the outcome?
A. It creates a locally managed tablespace with manual segment space management enabled.
B. It raises an error because extent management is not specified.
C. It creates a locally managed tablespace with automatic segment space management enabled.
D. It creates a dictionary-managed tablespace with manual segment space management enabled.
C. It creates a locally managed tablespace with automatic segment space management enabled.
300
You perform RMAN backups for your database and use a recovery catalog for managing the backups.
To free space, you execute this command:
RMAN\> DELETE OBSOLETE;
Which three statements are true is this scenario? (Choose three.)
A. The backup sets marked as expired are deleted.
B. The information related to the backups is removed from the recovery catalog and the control file.
C. The physical files related to the backup need to be manually deleted.
D. The physical files related to the backup are deleted automatically.
E. The backups deleted are based on the backup retention policy.
B. The information related to the backups is removed from the recovery catalog and the control file.
D. The physical files related to the backup are deleted automatically.
E. The backups deleted are based on the backup retention policy.
301
Your production database PROD uses file system storage.
You want to migrate storage including the Fast Recovery Area for the PROD database to Oracle Automatic Storage Management (ASM) by using RMAN.
You back up the entire database.
What should the next step be in this migration process?
A. enabling row movement for the database
B. disabling Oracle Flashback Database if enabled
C. opening the database in exclusive mode
D. placing all tablespaces in read-only mode
B. disabling Oracle Flashback Database if enabled
302
Which two statements describe the relationship between a scheduler window, a resource manager plan,
and a job class? (Choose two.)
```
A. A scheduler window together with a job class, controls resource allocation for a job using that job class
in that scheduler window.
```
B. A job class specifies a scheduler window that will be open when that job class becomes active.
C. A scheduler window specifies a resource manager plan that will be activated when that scheduler window becomes active.
```
D. A scheduler window specifies a job class that will be activated when that scheduler window becomes
active.
```
E. A scheduler window can control resource allocation by itself.
A. A scheduler window together with a job class, controls resource allocation for a job using that job class in that scheduler window.
C. A scheduler window specifies a resource manager plan that will be activated when that scheduler window becomes active.
303
Your database is configured in ARCHIVELOG mode, and daily full database backups are taken.
RMAN is configured to perform control file autobackups.
Which statement is true about the loss of a duplexed control file?
A. The database remains open but transactions are not permitted.
B. The database instance aborts, and media recovery is required after restoration of the control file to open
the database.
C. The database instance remains open and the control file can be restored without shutting down the
database.
D. The database instance aborts and a control file restore operation does not require media recovery.
C. The database instance remains open and the control file can be restored without shutting down the
database.
305
Which three are activities performed by SMON? (Choose three.)
A. cleaning up the database buffer cache and freeing resources that a client process was using
B. applying online redo during instance recovery
C. cleaning up temporary segments that are no longer needed
D. performing database services registration with the default listener
E. restarting a server or a dispatcher process that terminated abnormally
F. recovering failed transactions that were skipped during instance recovery because of file-read or tablespace offline errors
B. applying online redo during instance recovery
C. cleaning up temporary segments that are no longer needed
F. recovering failed transactions that were skipped during instance recovery because of file-read or tablespace offline errors
306
A user establishes a connection to a database instance by using an Oracle Net.
You want to ensure that:
1. The user account must be locked after five consecutive 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.
Which two would you do to implement this? (Choose two.)
A. by alerting the appropriate user attributes with an ALTER USER command
B. by using appropriate PASSWORD parameters set in the profile assigned to the user
C. by implementing Database Resource Manager and assign it a profile for the user
D. by implementing Database Resource Manager and assign it a role for the user
E. by using appropriate KERNEL parameters set in the profile assigned to the user
B. by using appropriate PASSWORD parameters set in the profile assigned to the user
E. by using appropriate KERNEL parameters set in the profile assigned to the user
307
Which two statements are true about SQL \*Loader Express Mode in an Oracle 12c database? (Choose two.)
A. The DEGREE\_OF\_PARALLELISM parameter is set to AUTO.
B. You cannot have multiple SQL \*Loader data files.
C. If no data file is specified, it assumes the data file to be .dat in the current directory and uses it.
D. You can have multiple bad files created when loading in parallel.
E. You can selectively load rows into a table based on a filter.
A. The DEGREE\_OF\_PARALLELISM parameter is set to AUTO.
C. If no data file is specified, it assumes the data file to be .dat in the current directory and uses it.
308
You install "Oracle Grid Infrastructure for a standalone server" on a host on which the ORCL1 and
ORCL2 databases both have their instances running.
Which two statements are true? (Choose two.)
A. All databases subsequently created by using the Database Configuration Assistant (DBCA) are
automatically added to the Oracle Restart configuration.
B. The srvctl add database command must be used to add ORCL1 and ORCL2 to the ORACLE Restart configuration.
C. Both ORCL1 and ORCL2 are automatically added to the Oracle Restart configuration.
D. All database listeners running from the database home are automatically added to the Oracle Restart
configuration.
E. The crsctl start has command must be used to start software services for Oracle Automatic Storage
Management (ASM) after the "Oracle Grid
A. All databases subsequently created by using the Database Configuration Assistant (DBCA) are
automatically added to the Oracle Restart configuration.
B. The srvctl add database command must be used to add ORCL1 and ORCL2 to the ORACLE Restart configuration.
309
You are using RMAN to back up your database.
All the data files are in read/write mode.
Examine the RMAN configuration parameters:
Which two statements are true about a whole consistent backup of a database running in ARCHIVELOG
mode? (Choose two.)
A. The backup can be used as an incremental level 0 backup.
B. The database instance must be shut down to take the backup.
C. The database must be in MOUNT state to take the backup.
D. The backup consists of blocks that have been formatted.
E. The system Change Number (SCN) is the same for all the data files in the backup.
C. The database must be in MOUNT state to take the backup.
E. The system Change Number (SCN) is the same for all the data files in the backup.
310
Which statement is true about unified auditing?
A. The unified audit trail, by default, resides in a read-only table in the AUDSYS schema in the SYSAUX tablespace.
B. Only the CREATE, ALTER, and DROP statements are audited for all users, including SYS.
C. Unified auditing is enabled only if the AUDIT\_TRAIL parameter is set to NONE.
D. The unified audit trail contains audit records only from unified audit policies and AUDIT settings.
A. The unified audit trail, by default, resides in a read-only table in the AUDSYS schema in the SYSAUX
tablespace.
311
Which two statements are true about Oracle network connections? (Choose two.)
A. A listener may listen on behalf of only one database instance at a time.
B. A server process checks a user’s authentication credentials and creates a session if the credentials are
valid.
C. The listener continuously monitors a connection after the user process connects to a service handler.
D. The listener always spawns a new server process to deal with each new connection.
E. A connection request from a client is always first received by a listener running on the port that is used
for the connection request for the database server.
B. A server process checks a user’s authentication credentials and creates a session if the credentials are
valid.
E. A connection request from a client is always first received by a listener running on the port that is used
for the connection request for the database server.
312
Which two options can be configured for an existing database by using the Database Configuration Assistant (DBCA)? (Choose two.)
A. Configure Label Security
B. Database Vault in ORACLE\_HOME
C. Oracle Suggested Backup Strategy
D. Database Resident Connection Pooling
E. Nondefault blocksize tablespaces
A. Configure Label Security
B. Database Vault in ORACLE\_HOME
313
Which set of statements is true about data dictionary views?
1. They are stored in the SYSTEM tablespace.
2. They are based on virtual tables.
3. They are owned by the SYS user.
4. They can be queried by a normal user only if the 07\_DICTIONARY\_ACCESSIBILITY parameter is set to
TRUE.
5. The V$FIXED\_TABLE view can be queried to list the names of these views.
6. They are owned by the SYSTEM user
A. 2, 5, and 6
B. 1, 2, and 3
C. 1 and 3
D. 2, 3, 4, and 5
C. 1 and 3
315
Examine the parameters:
Your database instance is started with a PFILE.
You want to increase the size of the buffer cache.
Free memory is available to increase the size of the buffer cache.
You execute the command:
SQL\> ALTER SYSTEM SET DB\_CACHE\_SIZE=1024M;
Which is the outcome?
A. Change is applied to the current instance, but does not persist after instance restart.
B. The value is changed only in the PFILE and takes effect at the next instance startup.
C. The value is chang
D. It fails because the SCOPE clause is missing.
A. Change is applied to the current instance, but does not persist after instance restart.
316
You plan to install the Oracle Database 12c software. You want to use Oracle Automatic Storage Management (ASM) for storage and Oracle Restart for managing components.
Oracle Grid Infrastructure for a Standalone Server is already installed on the server.
Which three statements must be true for successful installation of the Oracle Database 12c software?
(Choose three.)
A. The OSBACKUPDBA, OSDGDBA, and OSKMDBA OS groups must be created.
B. An operating system OSDBA group must be created for users with the SYSDBA system privilege.
C. The Oracle Database 12c software owner and the Oracle Grid Infrastructure owner must belong to the
same Oracle Inventory Group.
D. The Oracle Database 12c installation must have a separate Oracle inventory directory.
E. An operating system OSOPER group be created for users with the SYSOPER system privilege.
B. An operating system OSDBA group must be created for users with the SYSDBA system privilege.
C. The Oracle Database 12c software owner and the Oracle Grid Infrastructure owner must belong to the
same Oracle Inventory Group.
E. An operating system OSOPER group be created for users with the SYSOPER system privilege.
317
You want to upgrade an Oracle Database running Oracle Database 11g to Oracle Database 12c.
Which three tasks should be performed before a manual upgrade? (Choose three.)
A. running preupgrad.sql in Oracle Database 11g to generate fix-up scripts and a log file
B. running utlu121s.sql from the new Oracle home to display information about the required initialization
parameters
C. copying the initialization parameter file to the new Oracle home
D. copying the password file to the new Oracle home
E. copying net configuration files to the new Oracle home
A. running preupgrad.sql in Oracle Database 11g to generate fix-up scripts and a log file
C. copying the initialization parameter file to the new Oracle home
D. copying the password file to the new Oracle home
318
You have a production Oracle 12c database running on a host.
You want to install and create databases across multiple new machines that do not have any Oracle
database software installed. You also want the new databases to have the same directory structure and
components as your existing 12c database.
The steps in random order:
1. Create directory structures similar to the production database on all new machines.
2. Create a response file for Oracle Universal Installer (OUI) with the same configurations as the production
database.
3. Create a database clone template for the database.
4. Run the Database Configuration Assistant (DBCA) to create the database.
5. Run OUI in graphical mode on each machine.
6. Run OUI in silent mode using the OUI response file.
Identify the required steps in the correct sequence to achieve the requirement with minimal human
intervention.
A. 1, 5, and 4
B. 3, 1, 5, and 6
C. 2, 3, 6, and 4
D. 2, 1, 6, and 4
E. 2, 3, 1, and 6
C. 2, 3, 6, and 4
319
The HR.DEPARTMENTS table is the parent of the HR.EMPLOYEES table.
The EMPLOYEES.DEPARTMENT\_ID column has a foreign key constraint with the ON option that refers to the
DEPARTMENTS.DEPARTMENT\_ID column.
An index exists on the DEPARTMENTS.DEPARTMENT\_ID
column.
A DELETE CASCADE
transaction deletes a primary key in the DEPARTMENTS table, which has child rows in the EMPLOYEES
table.
Which statement is true?
A. The transaction acquires a table lock only on the DEPARTMENTS table until the transaction is
complete.
B. The transaction acquires a table lock on the DEPARTMENTS table. This lock enables other sessions to
query but not update the DEPARTMENTS table until the transaction on the DEPARTMENTS table is
complete.
C. The transaction acquires a table lock on the EMPLOYEES table. This lock enables other sessions to
query but not update the EMPLOYEES table until the transaction on the DEPARTMENTS table is
complete.
D. Only the rows that are deleted in the DEPARTMENTS and EMPLOYEES tables are locked until the
transactions on the DEPARTMENTS table is complete.
D. Only the rows that are deleted in the DEPARTMENTS and EMPLOYEES tables are locked until the
transactions on the DEPARTMENTS table is complete.
320
SMD is a smallfile locally managed tablespace with manual segment space management.
The SH user receives the following error while inserting data into the sales table:
Which three actions can be taken to enable the user to insert data? (Choose three.)
A. altering the data file associated with the SMD tablespace to grow automatically
B. adding a data file to the SMD tablespace, provided the tablespace has not reached its maximum number of file
C. changing segment space management for the SMD tablespace to automatic
D. resizing the data file associated with the SMD tablespace to make it larger, provided all data files have
not yet reached their maximum size
E. increasing the space quota on the SMD tablespace for the SH user, provided the files have not yet
reached their maximum size
A. altering the data file associated with the SMD tablespace to grow automatically
B. adding a data file to the SMD tablespace, provided the tablespace has not reached its maximum number of file
D. resizing the data file associated with the SMD tablespace to make it larger, provided all data files have
not yet reached their maximum size
321
When is the UNDO\_RETENTION parameter value ignored by a transaction?
A. when there are multiple undo tablespaces available in a database
B. when the data file of the undo tablespace is autoextensible
C. when the undo tablespace is of a fixed size and retention guarantee is not enabled
D. when Flashback Database is enabled
C. when the undo tablespace is of a fixed size and retention guarantee is not enabled
322
Which four statements are true about the components of the Oracle Scheduler? (Choose four.)
A. A schedule can be specified to a single job only.
B. A scheduler job can point to a chain instead of pointing to a single program object.
C. A job may get started automatically when a window opens.
D. A program and job can be specified as part of a schedule definition.
E. A job is specified as part of a program specification.
F. A program can be used in the definition of multiple jobs.
G. A program and schedule can be specified as part of a job definition.
B. A scheduler job can point to a chain instead of pointing to a single program object.
C. A job may get started automatically when a window opens.
F. A program can be used in the definition of multiple jobs.
G. A program and schedule can be specified as part of a job definition.
323
Which three statements are true about checkpointing? (Choose three.)
A. It prompts the Checkpoint (CKPT) process to write data to the data files and redo information to the
online redo log files.
B. It ensures that all dirty buffers are written to data files during consistent shutdown.
C. It reduces the time required for recovery in case of an instance failure.
D. Frequent thread checkpoints can degrade database performance.
E. It prompts the Database Writer (DBWn) process to write checkpoint information into data file headers
and the control file.
B. It ensures that all dirty buffers are written to data files during consistent shutdown.
C. It reduces the time required for recovery in case of an instance failure.
D. Frequent thread checkpoints can degrade database performance.
324
Your database instance has Automatic Memory Management enabled and supports shared server
connections.
Examine the following:
1. Parallel execution messages and control structures
2. Local variables for a process
3. Security and resource usage information
4. Runtime memory values, such as rows retrieved for a SQL statement using a serial execution plan
5. SQL execution work areas
Which option indicates what is allocated from the large pool in this instance?
A. only 1
B. 1, 2, and 5
C. 1, 2, 3, and 5
D. 1, 2, and 4
D. 1, 2, and 4
325
The performance of your database degrades between 11:00 AM and 3:00 PM. Automatic Workload Repository (AWR) snapshots are collected on an hourly basis.
What is the most efficient way of diagnosing this problem?
A. Create a custom ADDM task for the period defined by the snapshots taken between 11:00 AM and 3:00 PM.
B. Analyze the latest Automatic Database Diagnostic Monitor (ADDM) report.
C. Analyze the hourly ADDM reports generated between 11:00 AM and 3:00 PM.
D. Create a SQL Tuning Set (STS) for the currently cached SQL statements in the shared pool and run SQL Performance Analyzer (SPA) to generate
A. Create a custom ADDM task for the period defined by the snapshots taken between 11:00 AM and 3:00 PM.
326
Automatic Shared Memory Management is enabled for your database instance.
You notice that there are SQL statements performing poorly because of repeated parsing activity.
Which action generates recommendations to overcome the performance issues?
A. running the Memory Advisor for the buffer cache
B. running the Memory Advisor for the library cache
C. running the Memory Advisor for the SGA
D. running the Memory Advisor for the PGA
B. running the Memory Advisor for the library cache
327
Which two statements are true about a server parameter file (SPFILE)? (Choose two.)
A. An SPFILE can be created from a PFILE or from memory.
B. A PFILE can be used to start up a database instance even if an SPFILE exists.
C. An SPFILE must reside in the ORACLE\_HOME/dbs directory.
D. An SPFILE can be created only before a database instance is started.
E. An SPFILE contains only those dynamic parameters that can be changed without having to restart the
database instance.
A. An SPFILE can be created from a PFILE or from memory.
B. A PFILE can be used to start up a database instance even if an SPFILE exists.
328
Examine the details of the uncompressed, non-partitioned heap table CITIES.
Examine the command:
SQL\> ALTER TABLE cities SHRINK SPACE COMPACT;
What must you do before executing it?
A. Ensure free space that is approximately equal to the space used by the table should be available.
B. Ensure there are no pending transactions on the table
.
C. Enable row movement is enabled.
D. Disable all indexes on the table
C. Enable row movement is enabled.
329
Which three statements are true about Automatic Workload Repository (AWR)? (Choose three.)
A. An AWR snapshot shows the SQL statements that are producing the highest load on the system, based
on criteria such as elapsed time and CPU time.
B. AWR data is stored in memory and in a database.
C. All AWR tables belong to the SYSTEM schema.
D. The manageability monitor (MMON) process gathers statistics and creates an AWR snapshot that is
used by the self-tuning components in a database.
E. An AWR snapshot contains system-wide tracing and logging information.
A. An AWR snapshot shows the SQL statements that are producing the highest load on the system, based
on criteria such as elapsed time and CPU time.
B. AWR data is stored in memory and in a database.
D. The manageability monitor (MMON) process gathers statistics and creates an AWR snapshot that is
used by the self-tuning components in a database.
331
Which two would you recommend to an application developer for reducing locking conflicts on tables in an
Oracle database? (Choose two.)
A. Create objects in locally managed tablespaces with Automatic Segment Space Management (ASSM) enabled.
B. Avoid coding long-running transactions.
C. Allow the database to handle locks ln default locking mode.
D. Create objects in locally managed tablespaces with manual segment space management enabled.
E. Enable undo retention guarantee.
B. Avoid coding long-running transactions.
C. Allow the database to handle locks ln default locking mode.
332
Which statement is true about the loss of a data file belonging to the default undo tablespace?
A. The database remains open in read-only mode.
B. The database is put in MOUNT state and requires recovery to be opened.
C. The database remains open for querying but no DML statements can be executed except by the users
with SYSDBA privilege.
D. All the noncommitted transactions are lost.
E. The database instance aborts.
B. The database is put in MOUNT state and requires recovery to be opened.
333
Your database is in NOARCHIVELOG mode.
You want to enable archiving for the database.
Examine the steps:
1. Execute the ALTER DATABASE ARCHIVELOG command.
2. Execute SHUTDOWN IMMEDIATE.
3. Execute STARTUP MOUNT.
4. Set the DB\_RECOVERY\_FILE\_DEST parameter to $ORACLE\_HOME/dbs/.
5. Execute STARTUP NOMOUNT.
6. Open the database.
Identify the required steps in the correct sequence.
A. 4, 2, 5, 1, 6
B. 1, 2, 3, 4, 6
C. 2, 3, 1, 6
D. 2, 5, 1, 6
C. 2, 3, 1, 6
334
Which three statements are true about naming methods? (Choose three.)
A. Local naming supports multiple protocols, but for any one connection, the client and server must use the
same protocol.
B. In the Easy Connect method, clients can connect to a database server by using the host name of the
database with an optional port and service name.
C. In the Easy Connect method, the listener port and IP address must be provided for the connection to be
successful.
D. The local naming method does not support connect-time failover and load-balancing options.
E. The directory naming method supports connect-time failover and load-balancing options.
A. Local naming supports multiple protocols, but for any one connection, the client and server must use the
same protocol.
B. In the Easy Connect method, clients can connect to a database server by using the host name of the
database with an optional port and service name.
E. The directory naming method supports connect-time failover and load-balancing options.
335
Which task is performed by a background process in a database instance?
A. Connecting between a client process and a dispatcher
B. Executing PL/SQL code
C. Creating dedicated server connections
D. Copying online redo log files to offline storage
D. Copying online redo log files to offline storage
337
Identify three situations in which messages are written to the alert log file. (Choose three.)
A. Rebuilding an index using ALTER INDEX . . . REBUILD fails with an ORA-01578: ORACLE data block corrupted (file # 14, block # 50)"
B. Creating a table returns "ORA-00955: name is already in used by an existing object"
C. Inserting a value into a table returns "ORA-01722: invalid number"
D. Updating a record in a table returns "ORA-00060: deadlock detected while waiting for resource"
E. Inserting a value into a table returns "ORA-00001: unique constraint (SYS.PK\_XXXX) violated"
F. Running a query on a table returns "ORA-01578: ORACLE data block corrupted (file # 4, block # 131)"
A. Rebuilding an index using ALTER INDEX . . . REBUILD fails with an ORA-01578: ORACLE data block corrupted (file # 14, block # 50)"
D. Updating a record in a table returns "ORA-00060: deadlock detected while waiting for resource"
F. Running a query on a table returns "ORA-01578: ORACLE data block corrupted (file # 4, block # 131)"
338
When does a database checkpoint occur?
A. When there is an online redo log switch.
B. When a user session terminates abnormally.
C. When a server process terminates abnormally.
D. When the SHUTDOWN ABORT command is issued.
A. When there is an online redo log switch.
339
What action must you take to ensure complete database recovery till the point of failure?
A. Multiplex the control files
B. Duplex the RMAN backup sets.
C. Multiplex the online redo log files.
D. Configure the database to run in ARCHIVELOG mode.
D. Configure the database to run in ARCHIVELOG mode.
340
You want to create a table, DAILY\_ORDERS, for an OLTP application, where data should be compressed
during both direct-path INSERT and conventional DML.
The table will also be used for queries.
Which compression option should be used?
A. ROW STORE COMPRESS
B. COLUMN STORE COMPRESS FOR QUERY
C. COLUMN STORE COMPRESS FOR ARCHIVE LOW
D. ROW STORE COMPRESS ADVANCED
D. ROW STORE COMPRESS ADVANCED
341
Examine the command:
SQL\> CREATE TABLESPACE test1
DATAFILE '/u01/app/oracle/oradata/orc1/test01.dbf' SIZE 5M
AUTOEXTEND ON UNIFORM;
Which statement is true?
A. The data file, TEST01.DBF, can be auto extended to a maximum size of 5M.
B. The tablespace, TEST1, can contain a maximum of one data file.
C. Allocated and free extents are tracked using bitmaps.
D. Segment free space is tracked in the data dictionary.
C. Allocated and free extents are tracked using bitmaps.
