NEW QUESTION 1

Which of the following supplied functions is used to identify external tables, directories, and BFILES?

• A. DBMS_TDB.CHECK_DIRECTORIES
• B. DBMS_TDB.CHECK_EXTERNAL
• C. DBMS_TDB.CHECK_BFILE
• D. DBMS_TDB.CHECK_EXT

Answer: B

NEW QUESTION 2

Which client requests to the database can be captured as a part of the workload capture? (Choose all that apply.)

• A. flashback query
• B. distributed transactions
• C. logging in and logging out of sessions
• D. all DDL statements having bind variables
• E. direct path load of data from external files

Answer: CD

NEW QUESTION 3

Which three statements are true regarding persistent lightweight jobs? (Choose three.)

• A. Persistent lightweight jobs modify several tables in the data dictionary to generate a lot of redo.
• B. The user cannot set privileges on persistent lightweight jobs.
• C. Persistent lightweight jobs are useful when users need to create a large number of jobs in a short time.
• D. Persistent lightweight jobs are useful when users need to create a small number of jobs that run infrequently.
• E. The use of a template is mandatory to create persistent lightweight jobs.

Answer: BCE

NEW QUESTION 4

When issuing the duplicate database command, you use the parameter DB_FILE_NAME_CONVERT. For what purpose do you use this parameter?

• A. To indicate the location of the auxiliary-instance online redo logs.
• B. To indicate the location of the target database datafiles.
• C. To indicate the location of the auxiliary-instance control file and online redo logs.
• D. To indicate the location of the auxiliary-instance database datafiles.
• E. This is not a valid parameter when duplicating a database.

Answer: D

NEW QUESTION 5

When a job exceeds the date specified in its END_DATE attribute, which of the following will happen? (Choose all that apply.)

• A. The job will be dropped automatically if the value of the AUTO_DROP attribute is TRUE.
• B. The job will only be disabled if the value of the AUTO_DROP attribute is FALSE.
• C. The STATE attribute of the job will be set to COMPLETED if the value of the AUTO_DROP attribute is FALSE.
• D. All objects referenced by the job will be dropped if the value of the AUTO_DROP attribute is TRUE and the value of the CASCADE attribute is TRUE.
• E. The STATE column of the job table will be set to COMPLETED for the job.

Answer: ABC

Explanation:
auto_drop, If TRUE (the default), indicates that the job should be dropped once completed. end_date, This attribute specifies the date and time after which the job expires and is no longer run. After the end_date, if auto_drop is TRUE, the job is dropped. If auto_drop is FALSE, the job is disabled and the STATE of the job is set to COMPLETED.
If no value for end_date is specified, the job repeats forever unless max_runs or max_failures is set, in which case the job stops when either value is reached. The value for end_date must be after the value for start_date. If it is not, an error is generated when the job is enabled.

NEW QUESTION 6

The ORACLE_SID environment variable is set to +ASM. ASMLIB is not used in the configuration. You executed the following command to startup the Automatic Storage Management (ASM) instance.
SQL> STARTUP;
Which two activities are performed during a successful start up operation? (Choose two.)

• A. The databases configured to use the ASM instance are mounted
• B. The disk groups are mounted as per the ASM_DISKGROUPS initialization parameter
• C. ASM starts the Oracle Cluster Synchronization Services (CSS) daemon if it is not started
• D. ASM discovers and examines the contents of all files that are in the paths specified in theASM_DISKGROUPS initialization parameters

Answer: BC

Explanation:
Refer to Starting Up an ASM Instance. To start up an ASM instance, you must:
1. To connect to the ASM instance with SQL*Plus, you must set the ORACLE_SID environment variable to the ASM SID.
2. The initialization parameter file, which can be a server parameter file, must contain: INSTANCE_TYPE = ASM
3. The STARTUP command, tries to mount the disk groups specified by the initialization parameter ASM_DISKGROUPS.
If ASM_DISKGROUPS is blank, the ASM instance starts and warns that no disk groups were mounted. You can then mount disk groups with the ALTER DISKGROUP...MOUNT command.
The Cluster Synchronization Services (CSS) daemon is required to enable synchronization between ASM and its client database instances. The CSS daemon is normally started (and configured to start upon reboot) when you use Database Configuration Assistant (DBCA) to create your database. If you did not use DBCA to create the database, you must ensure that the CSS daemon is running before you start the ASM instance.

NEW QUESTION 7

Which options must you configure while performing an automated Tablespace Point-in- Time Recovery (TSPITR) by using Recovery Manager (RMAN)?

• A. New channels for restore and recovery tasks
• B. New name for the data files of the tablespace
• C. Auxiliary name for the data files of the tablespace
• D. Auxiliary destinations for an auxiliary set of data files

Answer: D

NEW QUESTION 8

Which statement most accurately describes the implementation of a SQL Access Advisor recommendation?

• A. SQL Access Advisor recommendations are automatically implemented.
• B. Individual SQL Access Advisor recommendations can be scheduled for implementation.
• C. All SQL Access Advisor recommendations for a specific task must be implemented at the same time.
• D. SQL Access Advisor recommendations are automatically scheduled for implementation during the maintenance window.
• E. None of the above.

Answer: B

Explanation:
Implementing the SQL Access Advisor Recommendations (link)
A SQL Access Advisor recommendation can range from a simple suggestion to a complex solution that requires partitioning a set of existing base tables and implementing a set of database objects such as indexes, materialized views, and materialized view logs. You can select the recommendations for implementation and schedule when the job should be executed.

NEW QUESTION 9

Which DBMS_SCHEDULER procedures can be used to enable a program? (Choose all that apply.)

• A. ENABLE
• B. ENABLE_PROGRAM
• C. VALIDATE_PROGRAM
• D. SET_ATTRIBUTE
• E. SET_ENABLED

Answer: AD

Explanation:
Refer to here.
ENABLE Procedure, Enables a program, job, chain, window, or window group SET_ATTRIBUTE Procedure, Changes an attribute of a job, schedule, or other Scheduler object

NEW QUESTION 10

You are managing an Oracle 11g database with ASM storage, for which the COMPATIBLE initialization parameter is set to 11.1.0. In the ASM instance, the COMPATIBLE.RDBMS attribute for the disk group is set to 10.2 and the COMPATIBLE.ASM attribute is set to 11.1.
Which two statements are true in this scenario for the features enabled for ASM? (Choose two.)

• A. The ASM-preferred mirror read feature is enabled.
• B. The ASM supports variable sizes for extents of 1, 8, and 64 allocation units.
• C. The ASM disk is dropped immediately from a disk group when it becomes unavailable.
• D. The RDBMS always reads the primary copy of a mirrored extent of the ASM disk group.

Answer: AB

NEW QUESTION 11

What two are the prerequisites for enabling Flashback Database? (Choose two)

• A. The database must be in ARCHIVELOG mode
• B. The database must be in MOUNT EXCLUSIVE mode
• C. The database must be opened in RESTRICTED mode
• D. The database instance must be started in the NOMOUNT state
• E. The database instance must have the keep buffer pool defined

Answer: AB

NEW QUESTION 12

You have a range-partitioned table in your database. Each partition in the table contains the sales data for a quarter.
The partition related to the current quarter is modified frequently and other partitions undergo fewer data manipulations. The preferences for the table are set to their default values. You collect statistics for the table using the following command in regular intervals:
SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS('SH','SALES',GRANULARITY=>'GLOBAL');
You need statistics to be collected more quickly. What can you do to achieve this?

• A. Set DYNAMIC_SAMPLING to level 4.
• B. Set the STATISTICS_LEVEL parameter to BASIC.
• C. Set the INCREMENTAL value to TRUE for the partition table.
• D. Increase the value of STALE_PERCENT for the partition table.

Answer: C

Explanation:
Oracle will update the global table statistics by scanning only the partitions that have been changed instead of the entire table if the following conditions hold:
✑ INCREMENTAL value for the partitioned table is set to TRUE
✑ PUBLISH value for the partitioned table is set to TRUE;
✑ User specifies AUTO_SAMPLE_SIZE for ESTIMATE_PERCENT and AUTO for GRANULARITY when gathering statistics on the table
If the INCREMENTAL value for the partitioned table was set to FALSE (default value), a full table scan is used to maintain the global statistics which is a much more resource intensive and time-consuming operation for large tables.

NEW QUESTION 13

View the Exhibit to examine the Automatic Database Diagnostic Monitor (ADDM) tasks. You executed the following commands:
SQL> VAR tname VARCHAR2(60); SQL> BEGIN
:tname := 'my_instance_analysis_mode_task'; DBMS_ADDM.INSERT_SEGMENT_DIRECTIVE(:tname,'Sg_directive','SCOTT'); END;
Which statement describes the consequence? Exhibit:

• A. The ADDM task is filtered to suppress the Segment Advisor suggestions for the SCOTT schema.
• B. The ADDM task is filtered to produce the Segment Advisor suggestions for the SCOTT schema only.
• C. The PL/SQL block produces an error because the my_instance_analysis_mode_task task has not been reset to its initial state.
• D. All subsequent ADDM tasks including my_instance_analysis_mode_task are filtered to suppress the Segment Advisor suggestions for the SCOTT schema.

Answer: C

Explanation:
sys@TEST1107> VAR tname VARCHAR2(60);
sys@TEST1107> BEGIN :tname := 'my_instance_analysis_mode_task';
2 DBMS_ADDM.INSERT_SEGMENT_DIRECTIVE(:tname,'Sg_directive','SCOTT');
3 END;
4 /
PL/SQL procedure successfully completed.
sys@TEST1107> SELECT DBMS_ADVISOR.GET_TASK_REPORT(:tname, 'TEXT', 'ALL') FROM
DUAL; ERROR:
ORA-13631: The most recent execution of task my_instance_analysis_mode_task contains no results.
ORA-06512: at "SYS.PRVT_ADVISOR", line 3189 ORA-06512: at "SYS.DBMS_ADVISOR", line 590
ORA-06512: at line 1 no rows selected

NEW QUESTION 14

Which of the following information will be gathered by the SQL Test Case Builder for the problems pertaining to SQL-related problems? (Choose all that apply.)

• A. ADR diagnostic files
• B. all the optimizer statistics
• C. initialization parameter settings
• D. PL/SQL functions, procedures, and packages
• E. the table and index definitions and actual data

Answer: BCD

Explanation:
The information gathered by SQL Test Case Builder includes:
✑ the query being executed,
✑ table and index definitions (but not the actual data),
✑ PL/SQL functions, procedures, and packages,
✑ optimizer statistics,
✑ and initialization parameter settings.

NEW QUESTION 15

Which option is best practice for creating a recovery catalog owner in the catalog database?

• A. Granting UNLIMITED QUOTA on the SYSTEM tablespace to the owner
• B. Allocating the SYSTEM tablespace as the default tablespace and granting the SYSDBA privilege to the user
• C. Creating a new tablespace, allocating this as the default, and granting UNLIMITED QUOTA on this tablespace to the user
• D. Allocating the SYSAUX tablespace as the default tablespace and granting UNLIMITED QUOTA on this tablespace to the user

Answer: C

Explanation:
To create the recovery catalog schema in the recovery catalog database:
1. Start SQL*Plus and connect with administrator privileges to the database containing the recovery catalog. In this example, the database is catdb.
2. Create a user and schema for the recovery catalog. For example, you could enter the following SQL statement (replacing password with a user-defined password):
CREATE USER rman IDENTIFIED BY password TEMPORARY TABLESPACE temp
DEFAULT TABLESPACE tools QUOTA UNLIMITED ON tools;
3. Grant the RECOVERY_CATALOG_OWNER role to the schema owner. This role provides the user with all privileges required to maintain and query the recovery catalog. GRANT RECOVERY_CATALOG_OWNER TO rman;

NEW QUESTION 16

How can you reverse the effects of an ALTER DISKGROUP ... DROP DISK command if it has already completed?

• A. Issue the ALTER DISKGROUP ... ADD DISK command.
• B. Issue the ALTER DISKGROUP ... UNDROP DISKS command.
• C. Issue the ALTER DISKGROUP ... DROP DISK CANCEL command.
• D. Retrieve the disk from the Recycle Bin after the operation completes.

Answer: A

Explanation:
You cannot UNDROP DISKS if the DROP DISK command has already completed.

NEW QUESTION 17

Which two statements regarding a SQL profile are true? (Choose two.)

• A. It is built by Automatic Tuning Optimizer.
• B. It cannot be stored persistently in the data dictionary.
• C. It can be used by the query optimizer automatically.
• D. It can be created manually by using the CREATE PROFILE command.

Answer: AC

NEW QUESTION 18

Which of the following describes how a distributed resumable transaction behaves?

• A. The resumable setting on the initiating session determines the resumable conditions for the entire distributed transaction.
• B. The resumable setting for the initiating instance determines the resumable conditions for the entire distributed transaction.
• C. The resumable setting on the initiating session controls only that part of the transaction that occurs within the local instance; remote resumable settings determine the behavior of the distributed parts of the transaction.
• D. None of the above.

Answer: C

NEW QUESTION 19

View the following SQL statements: Transaction T1
SQL> INSERT INTO hr.regions 2 VALUES (5,'Pole');
3 COMMIT;
Transaction T2
SQL> UPDATE hr.regions 2 SET region_name='Poles'
3 WHERE region_id = 5;
4 COMMIT;
Transaction T3
SQL> UPDATE hr.regions
2 SET region_name='North and South Poles'
3 WHERE region_id = 5;
You want to back out transaction T2. Which option would you use?

• A. It is possible, but transaction T3 also backs out.
• B. It is possible with the NOCASCADE_FORCE option.
• C. It is possible with the NONCONFLICT_ONLY option.
• D. It is not possible because it has conflicts with transaction T3.

Answer: B

Explanation:
Table 12-2 Flashback TRANSACTION_BACKOUT Options
CASCADE
Backs out specified transactions and all dependent transactions in a post-order fashion (that is, children are backed out before parents are backed out).
Without CASCADE, if any dependent transaction is not specified, an error occurs.
NOCASCADE
Default. Backs out specified transactions, which are expected to have no dependent transactions. First dependent transactions causes an error and appears in
*_FLASHBACK_TXN_REPORT.
NOCASCADE_FORCE
Backs out specified transactions, ignoring dependent transactions. Server runs undo SQL statements for specified transactions in reverse order of commit times. If no constraints break and you are satisfied with the result, you can commit the changes; otherwise, you can roll them back.
NONCONFLICT_ONLY
Backs out changes to nonconflicting rows of the specified transactions. Database remains consistent, but transaction atomicity is lost.

NEW QUESTION 20

You have enabled backup optimization in RMAN. You issue the following RMAN command to configure a redundancy-based retention policy:
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
Which statement is true?

• A. The command fails because you cannot configure a redundancy-based retention policy when backup optimization is enabled
• B. Backup optimization is performed, but RMAN considers the redundancy-based retention policy when it determines which datafiles should be backed up
• C. Backup optimization is permanently disabled
• D. Backup optimization is temporarily disabled because a redundancy-based retention policy is specified

Answer: B

NEW QUESTION 21

Which statements are true regarding the system-defined moving window baseline in Oracle Database 11g? (Choose all that apply.)

• A. It does not allow you to change the moving window size.
• B. Adaptive threshold functionalities use it by default to compute statistics.
• C. It is created by default with the window size being equal to the AWR retention time.
• D. It is created when the first snapshot is collected by the Automatic Workload Repository (AWR).

Answer: BC

Explanation:
A moving window baseline corresponds to all AWR data that exists within the AWR retention period. This is useful when using adaptive thresholds because the database can use AWR data in the entire AWR retention period to compute metric threshold values. Oracle Database automatically maintains a system-defined moving window baseline. The
default window size for the system-defined moving window baseline is the current AWR retention period, which by default is 8 days. If you are planning to use adaptive thresholds, consider using a larger moving window—such as 30 days—to accurately compute threshold values. You can resize the moving window baseline by changing the number of days in the moving window to a value that is equal to or less than the number of days in the AWR retention period. Therefore, to increase the size of a moving window, you must first increase the AWR retention period accordingly.

NEW QUESTION 22

Which is the correct command to back up the database, back up the archived redo logs, and then remove the backed-up archived redo logs?

• A. backup database
• B. backup database and archivelogs
• C. backup database plus archivelogs
• D. backup database plus archivelog delete input
• E. backup database and archivelog delete input

Answer: D

NEW QUESTION 23

ENCRYPT_TS is an encrypted tablespace that contains tables with data.
Which statement is true regarding the effect of queries and data manipulation language (DML) statements on the encrypted data in the tables?

• A. The data is decrypted during SORT and JOIN operations.
• B. The data remains encrypted when it is read into memory.
• C. The data remains encrypted when it is stored in the redo logs.
• D. The data remains encrypted in the UNDO tablespace provided that the UNDO tablespace was created with the encryption option enabled.

Answer: C

NEW QUESTION 24

ASM supports all but which of the following file types? (Choose all that apply.)

• A. Database files
• B. SPFILEs
• C. Redo-log files
• D. Archived log files
• E. RMAN backup sets
• F. Password files
• G. init.ora files

Answer: FG

Explanation:
What Types of Files Does Oracle ASM Support?

NEW QUESTION 25

A user receives the following error while performing a large volume of inserts into a table:
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'
The issue is resolved by increasing the space quota on the USERS tablespace for the user. But the user may perform such transaction in the future. You want to ensure that the command waits rather than produce an error when such an event occurs the next time.
What can you do to achieve this before running the command in the future?

• A. Set RESUMABLE_TIMEOUT for the instance.
• B. Set the RESOURCE_LIMIT Parameter to TRUE.
• C. Enable the database instance to use asynchronous commit.
• D. Set the LOG_CHECKPOINT_TIMEOUT parameter to a nonzero value for the database instance.

Answer: A

Explanation:
How Resumable Space Allocation Works
The following is an overview of how resumable space allocation works. Details are contained in later sections.
✑ A statement executes in resumable mode only if its session has been enabled for resumable space allocation by one of the following actions:
The ALTER SESSION ENABLE RESUMABLE statement is issued in the session before the statement executes when the RESUMABLE_TIMEOUT initialization parameter is set to a nonzero value. The ALTER SESSION ENABLE RESUMABLE TIMEOUT timeout_value statement is issued in the session before the statement executes, and thetimeout_value is a nonzero value.
✑ A resumable statement is suspended when one of the following conditions occur
(these conditions result in corresponding errors being signalled for non-resumable statements):
Out of space condition Maximum extents reached condition Space quota exceeded condition.
✑ When the execution of a resumable statement is suspended, there are
mechanisms to perform user supplied operations, log errors, and query the status of the statement execution. When a resumable statement is suspended the following actions are taken:
The error is reported in the alert log.
The system issues the Resumable Session Suspended alert.
If the user registered a trigger on the AFTER SUSPEND system event, the user trigger is executed. A user supplied PL/SQL procedure can access the error message data using the DBMS_RESUMABLE package and the DBA_ or USER_RESUMABLE view.
✑ Suspending a statement automatically results in suspending the transaction. Thus all transactional resources are held through a statement suspend and resume. When the error condition is resolved (for example, as a result of user intervention or perhaps sort space released by other queries), the suspended statement automatically resumes execution and the Resumable Session Suspended alert is cleared.
✑ A suspended statement can be forced to throw the exception using the
DBMS_RESUMABLE.ABORT() procedure. This procedure can be called by a DBA, or by the user who issued the statement.
✑ A suspension time out interval, specified by the RESUMABLE_TIMEOUT
initialization parameter or by the timeout value in the ALTER SESSION ENABLE RESUMABLETIMEOUT statement, is associated with resumable statements. A resumable statement that is suspended for the timeout interval wakes up and returns the exception to the user if the error condition is not resolved within the timeout interval.
✑ A resumable statement can be suspended and resumed multiple times during execution.

NEW QUESTION 26
......