Page: 1
/ 18
Total 264 questions
Evaluate these statements:
CREATE TABLE purchase_orders -
(po_idNUMBER(4),
po_dateTIMESTAMP,
supplier_idNUM8ER(6),
po_totalNUMBER(8,2), CONSTRAINT order_pk PRIMARY KEY(po_id))
PARTITION BY RANGE(po_date)
(PARTITIONQ1 VALUES LESS THAN (TO_DATE('01-apr-2007','dd-mm-yyyy')), PARTITIONQ2VALUESLESSTHAN(TO_DATE('01-jul-
2007','dd-mm-yyyy')), PARTITIONQ3VALUESLESSTHAN (TO~DATE('01-oct-2007','dd-mm-yyyy')), PARTITIONQ4VALUESLESSTHAN
(TO_DATE('Ol-jan-2008','dd-mm-yyyy')));
CREATE TABLE purchase_order_items
(po_idNUM3ER(4)NOT NULL,
product_idNUMBER(6)NOT NULL,
unit_price NUMBER(8,2),
quantity NUMBER(8),
CONSTRAINT po_items_f k -
FOREIGN KEY(po_id)REFERENCES purchase_orders(po_id))
PARTITION BY REFERENCE(po_items_fk);
Which two statements are true? (Choose two.)
- A. Partitions of purchase_order_items are assigned unique names based on a sequence.
- B. The purchase_orders and purchase_order_items tables are created with four partition each.
- C. purchase_order_items table partitions exist in the same tablespaces as the purchase_orders table partitions.
- D. The purckase_order_:teks table inherits the partitioning key by duplicating the key columns from the parent table.
- E. Partition maintenance operations on the purchase_order_items table require disabling the foreign key constraint.
Answer : BC
Which four actions are possible during an Online Datafile Move operation? (Choose four.)
- A. Creating and dropping tables in the datafile being moved
- B. Performing file shrink of the data file being moved
- C. Querying tables in the datafile being moved
- D. Performing Block Media Recovery for a data block in the datafile being moved
- E. Flashing back the database
- F. Executing DML statements on objects stored in the datafile being moved
Answer : ACDF
Examine the command used to perform an incremental level-0 backup:
RMAN> BACKUP INCREMENTAL LEVEL0 DATABASE;
To enable block change tracking, after the incremental level 0 backup, you issue the command:
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING
FILE'/mydir/rman_change_track.f';
To perform an incremental level-1 cumulative backup, you issue the command:
RMAN> BACKUP INCREMENTAL LEVEL1 CUMULATIVE DATABASE;
Which two statements are true in the preceding situation? (Choose two.)
- A. The block change tracking data is used only from the next incremental backup.
- B. The incremental level 1 backup fails because a block change tracking file Is created after the level 0 backup.
- C. The incremental level 1 backup does not use change tracking data for accomplishing the backup.
- D. The block change tracking file scans all blocks and creates a bitmap for the blocks backed up in the level 0 backup.
- E. The block change tracking data is used for the next incremental level 1 backup only after the next level 0 backup.
Answer : CE
You specified the warning and critical thresholds for a locally managed tablespace to be 60% and 70%, respectively.
From the tablespace space usage metrics, you find that the space usage has reached the specified warning threshold value, but no alerts have been generated.
What could be the reason for this?
- A. The event parameter was not set.
- B. The sql_trace parameter is set to false.
- C. Enterprise Manager was not used.
- D. The statistics_level parameter is set to basic.
- E. The timed_statistics parameter is set to false.
Answer : D
You are administering a multitenant container database (CDB) CDB1 that is running in ARCHIVELOG mode and contains pluggable databases (PDBs), PDB_1 and PDB_2.
While opening pdb_1, you get an error:
SQL> alter pluggable database pdb_1 open;
ORA-01157: cannot identify/lock data file 11-see DBWR trace file
ORA-01110: data file 11: '/u01/app/oracle/oradata/cdb1/pcb_1/example01.dbf'
To repair the failure, you open an RMAN session for the target database CDB$ROOT. You execute the following as the first command:
RMAN> REPAIR FAILURE;
Which statement describes the consequence of the command?
- A. The command performs the recovery and closes the failure.
- B. The command produces an error because RMAN is not connected to the target database pdb_1.
- C. The command produces an error because the ADVISE FAILURE command was not executed before the REPAIR FAILURE command.
- D. The command executes successfully, performs recovery, and opens PDB_1.
Answer : C
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
Answer : B
Explanation:
You use RMAN with a recovery catalog to back up your database. The backups and the archived redo log files are backed up to media daily. Because of a media failure, the entire database along with the recovery catalog database is lost.
Examine the steps required to recover the database:
1. Restore an autobackup of the server parameter file.
2. Restore the control file.
3. Start up the database instance in nomount state.
4. Mount the database.
5. Restore the data files.
6. Open the database with the resetlogs option.
7. Recover the data files.
8. Set D3ID for the database.
Identify the required steps in the correct order.
- A. 1, 8, 3, 2, 4, 5, 7, 6
- B. 8, 1, 3, 2, 4, 5, 7, 6
- C. 1, 3, 2, 4, 8, 5, 6, 7
- D. 8, 3, 2, 4, 5, 7, 6
- E. 8, 1, 3, 2, 4, 5, 6
Answer : B
Which three statements are true about the startup and shutdown of multitenant container databases (CDBs) and pluggable databases (PDBs)? (Choose three.)
- A. A PDB opened in restricted mode allows only local users to connect.
- B. When a CDB is open in restricted mode, PDBs must also be opened in restricted mode.
- C. When a CDB is in mount state, PDBs are automatically placed in mount state.
- D. All PDBs must be shut down before shutting down a CDB instance.
- E. When a CDB instance is started, PDBs can be placed in open state by using database triggers or by executing the alter pluggable database command.
Answer : BCE
A telecom company wishes to generate monthly bills to include details of customer calls, listed in order of time of call.
Which table organization allows for generating the bills with minimum degree of row sorting?
- A. a hash cluster
- B. an index cluster
- C. a partitioned table
- D. a sorted hash cluster
- E. a heap table with a rowid column
Answer : D
Examine the following steps of privilege analysis for checking and revoking excessive, unused privileges granted to users:
1. Create a policy to capture the privileges used by a user for privilege analysis.
2. Generate a report with the data captured for a specified privilege capture.
3. Start analyzing the data captured by the policy.
4. Revoke the unused privileges.
5. Compare the used and unused privileges' lists.
6. Stop analyzing the data.
Identify the correct sequence of steps.
- A. 1, 3, 5, 6, 2, 4
- B. 1, 3, 6, 2, 5, 4
- C. 1, 3, 2, 5, 6, 4
- D. 1, 3, 5, 2, 6, 4
Answer : B
Explanation:
1. Create a policy to capture the privilege used by a user for privilege analysis.
3. Start analyzing the data captured by the policy.
6. Stop analyzing the data.
2. Generate a report with the data captured for a specified privilege capture.
5. Compare the used and unused privileges"™ lists.
4. Revoke the unused privileges.
Your multitenant container database (CDB) CDB1 that is running in ARCHIVELOG mode contains two pluggable databases (PDBs), PDB2_1 and PDB2_2, both of which are open. RMAN is connected to the target pluggable database PDB2_1.
RMAN> BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;
Which statement is true about the execution of this command to back up the database?
- A. All data files belonging to PDB2_1 are backed up and all archive log files are deleted.
- B. All data files belonging to PDB2_1 are backed up along with the archive log files.
- C. Only the data files belonging to pdb2_1 are backed up.
- D. This command gives an error because archive log files can be backed up only when RMAN is connected to the root container database.
Answer : B
You issue commands in SQL*Plus as the Oracle owner, to enable multithreading for your UNIX-based Oracle 12c database:
CONNECT /AS SYSDBA -
ALTER SYSTEM SET THREADED_EXECUTION=TRUE SCOPE=SPFILE;
SHUTDOWN IMMEDIATE -
You then restart the instance and get an error:
STARTUP -
ORA-01031: insufficient privileges
Why does the startup command return the error shown?
- A. because the threaded architecture requires exiting from sql*plus and reconnecting with sql*Plus / as sysdba before issuing a startup command
- B. because the threaded architecture requires issuing a new connect / as sysdba from within sql*plus before issuing a startup command
- C. because the threaded architecture requires authentication using a password file before issuing a startup command
- D. because the threaded architecture requires connecting to the instance via a listener before issuing a startup command
- E. because the threaded architecture requires restarting the listener before issuing a startup command
Answer : C
Your multitenant container database (CDB) cdb1, which has no startup triggers and contains multiple pluggable databases (PDBs), is started up by using the command:
SQL> STARTUP -
Which two statements are true about the successful execution of the command? (Choose two.)
- A. All redo log files are opened.
- B. The root, the seed, and all the PDBs are opened in read-write mode.
- C. All the PDBs are opened in read-write mode.
- D. All the PDBs are in closed state.
- E. Only the root database is opened in read-write mode.
Answer : AE
Examine the resources consumed by a database instance whose current Resource Manager plan is displayed.
SQL> SELECT name, active_sessions, queue_length,
consumed_cpu_time, cpu_waits, cpu_wait_time
FROM v$rsrc_consumer_group;
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 OTHE_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.
Answer : CE
Examine the commands executed to monitor database operations:
$> conn sys/oracle@prod as sysdba
SQL> VAR eid NUMBER -
SQL>EXEC :eid := DBMS_SQL_MONITOR.BEGlN_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.
Answer : CE
Explanation:
C: Setting the CONTROL_MANAGEMENT_PACK_ACCESS initialization parameter to DIAGNOSTIC+TUNING (default) enables monitoring of database operations. Real-Time SQL Monitoring is a feature of the Oracle Database Tuning Pack.
Note:
* The DBMS_SQL_MONITOR package provides information about Real-time SQL Monitoring and Real-time Database Operation Monitoring.
*(not B) BEGIN_OPERATION Function starts a composite database operation in the current session.
/ (E) FORCE_TRACKING - forces the composite database operation to be tracked when the operation starts. You can also use the string variable 'Y'.
/ (not A) NO_FORCE_TRACKING - the operation will be tracked only when it has consumed at least 5 seconds of CPU or I/O time. You can also use the string variable 'N'.
30 days access 