2018ocp052题目

Question 1
USER1.EMP has a referential integrity constraint defined on EMP.DNO that references
USER1.DEPT.DNO.
USER1 executes these commands:
SQL> UPDATE dept SET loc='UK' where dno=1;
1 row updated.
SQL> UPDATE emp SET sal=1000 where eno=100;
1 row updated.
SQL>ALTER TABLE dept DROP(dno)
What will be the outcome and why?
A.It will fail because a referential integrity constraint is defined on USER1.EMP.
B.It will execute successfully and drop DEPNT.DNO.
C.It will fail because there is an uncommitted transaction on USER1.DEPT.
D.It will execute successfully and drop the DEPT.DNO and EMP.DNO columns.
E.It will fail because there is an uncommitted transaction on USER1.EMP
Answer:A
Question 2
Examine these commands and their output:
SQL> SELECT * FROM emp
ENO ENAME
--- -------
100 Adam
101 Alan
SQL> INSERT INTO emp VALUES(102,'Ben');
1 row created.
SQL>COMMIT;
Commit completed.
SQL>UPDATE emp SET ename='Bryan' WHERE eno=102;
1 row updated.
A power failure occurs. The instance is restarted and this query is executed.
SQL>SELECT ename FROM emp;
What is the outcome?
A.Only Adam and Alan are displayed.
B.No rows are returned.
C.Only Adam, Alan, and Bryan are displayed.
D.Adam, Alan, Ben, and Bryan are displayed.
E.Only Adam, Alan, and Ben are displayed.
Answer: E
Question 3
Each row in the TRANS_SUMMARY table contains details of one product.
Each row in the TRANS_MONTHLY table contains transaction details either for new products
or for
updates to existing products.
There is a monthly requirement to:
1. Insert TRANS_MONTHLY rows for new products to the TRANS_SUMMARYtable
2. Update the TRANS_SUMMARY table when a TRANS_MONTHLY row refers to an existingproduct.
Which two can be used to do this?
A.INSERT INTO .. SELECT FROM ..
B.a called procedure.
C.an Oracle loader type external table.
D.the MERGE command.
E.CREATE TABLE AS SELECT (CTAS)
Answer: BD
Question 4
Which four statements are true about truncating a table?
A.Any insert triggers for the table will be executed.
B.Any of the table’ s indexes are also truncated.
C.Any delete triggers for the table will be executed.
D.All table storage can be retained.
E.Only HEAP tables may be truncated.
F.All table storage is converted to free space.
G.MINEXTENTS amount of storage for the table can be retained.
Answer: BDFG
Question 5
Examine these attributes for the profile assigned to USER1 whose password is oracle_4U.
PASSWORD_REUSE_MAX is 5.
PASSWORD_REUSE_TIME is 10.
PASSWORD_GRACE_TIME is 15.
Which two conditions must be met for USER1 to reuse oracle_4U?
A.At least 5 days must have passed since oracle_4U was last used.
B.The password must have changed 5 times since oracle_4U was last used.
C.At least 10 days must have passed since oracle_4U was last used.
D.The password must have changed 10 times since oracle_4U was last used.
E.At least 15 days must have passed since oracle_4U was last used.
Answer: BC
Question 6
Examine these attributes for the profile assigned to USER1 whose password is oracle_4U
PASSWORD_REUSE_MAX is 5
PASSWORD_REUSE_TIME is 10
PASSWORD_GRACE_TIME is 15
Which two are true?
A.The USER1 password must not have been used for at least 10 days before oracle_4U can
be
reused as the password
B.The USER1 account will be locked if the password is changed more then 5 times in 10
days
C.The USER1 password must be changed 5 times before oracle_4U can be reused as the
password
D.The USER1 account will be locked if the password is not changed at least 5 times in
15 days
E.The USER1 password must not have been used for at least 25 days before oracle_4U can
be
reused as the password
Answer: AC
Question 7
Which is true about invalid PL/SQL objects?
A.They are automatically recompiled against the new definition of a referenced object
when they are called.
B.They must be manually recompiled before they can be used if a DDL is performed on
a table that is referenced in the PL/SQL object.
C.They are automatically recompiled against the new definition of a referenced object
at the same time as the referenced object is modified.
D.They can be manually recompiled only by using SQL commands.
Answer: A
Question 8
Which two are true about PL/SQL objects?
A.They must be in an invalid state to be recompiled.
B.They can never be manually recompiled.
C.They can be manually recompiled.
D.They must be manually recompiled.
E.They can be automatically recompiled.
Answer: CE
Question 9
Which three are true about Optimizer Statistics?
A.They can be gathered manually
B.They are automatically uupdated by DML statements
C.They contain the execution plan for each executed SQL statement.
D.They include the number of rows in a table and the number of distinct values per column.
E.They can be gathered automatically by an automatic maintenance job.
Answer: ADE
Question 10
Which two are true about Oracle's Optimizer statistics?
A.They are automatically updated when ddl statements execute.
B.They can be calculated exactly.
C.They can be gathered on external tables.
D.They are automatically updated when dml statements execute.
E.They can be estimated.
Answer: BE
Question 11
Which two are true about Optimizer Statistics?
A.They are gathered by the Manageability Monitor(MMON.background process.
B.They can be gathered by executing the PL/SQL procedures in the DBMS_STATES package.
C.They do not persist across instance restarts.
D.They can be used to determine the events on which a database instance waited.
E.They provide information to the Optimizer so that it can determine optimal execution
plans for SQL statements.
F.They are automatically updated when DDL statements execute.
Answer: BE
Question 12
Which three are true about the Automatic Workload Repository (AWR)?
A.AWR data is stored in the SYSAUX tablespace.
B.Automatic AWR snapshots are created by the Manageability Monitor (MMON.background
process.
C.It includes statistics that contain the number of distinct values per table column.
D.By default, AWR data is retained for eight days.
E.it is maintained by executing the pl/sql procedures in the dbms_stats package.
F.It includes statistics that contain the number of rows per table.
Answer: ABD
Question 13
Which three are true about the Automatic Workload Repository (AWR)?
A.AWR data is stored in the SYSTEM tablespace.
B.By default, AWR snapshots are taken every 60 minutes.
C.It is the foundation for the self-managing infrastructure.
D.The taking of AWR snapshots cannot be disabled.
E.Its collection level is determined by the value of the STATISTICS_LEVEL database
parameter.
Answer: BCE
Question 14
Which three are true about the Automatic Workload Repository (AWR)?
A.AWR data is stored in the SYSTEM tablespace.
B.By default, AWR snapshots are taken every 60 minutes.
C.It is the foundation for the self-managing infrastructure.
D.The taking of AWR snapshots cannot be disabled.
E.Its collection level is determined by the value of the STATISTICS_LEVEL database
parameter.
F.It is used by the Optimizer to determine optimal execution plans.
Answer: BCE
Question 15
Which two are true about AWR snapshots?
A.They are stored in the SYSAUX tablespace.
B.They are always created automatically.
C.They can be retained forever.
D.They can be modified by using DMLs.
E.They are not generated if STATISTICS_LEVEL is set to TYPICAL.
Answer: AC
Question 16
Which three are true about AWR snapshots?
A.They are always created automatically.
B.They are always created manually.
C.They are generated If statistics—level is set to basic.
D.They are generated If statistics—level is set to typical.
E.They are always retained forever.
F.They can be retained forever.
G.They are generated if statistics-level Is set to all.
Answer: DFG
Question 17
Which four are true about the Automatic Workload Repository (AWR)?
A.AWR data can be retained for a month.
B.It includes statistics on the number of columns in each view.
C.Automatic AWR snapshots can be written once per hour.
D.It includes statistics on the number of columns in each table.
E.Automatic AWR snapshots can be written once every 6 hours
F.AWR data can be retained for 3 months.
Answer: ACEF
Question 18
Which two events always request the LGWR to write?
A.When DBWn writes dirty buffers.
B.When a log switch occurs.
C.When a checkpoint occurs.
D.When LGWR is notified by a server process that performs a COMMIT or ROLLBACK.
E.When PMON restarts a serve process.
Answer: AB
Question 19
Which three events always result in a write request made to the LGWRprocess?
A.after undo is applied by PMON during process recovery
B.when dirty buffers are found during the processing of a checkpoint
C.when the password file is updated
D.when an Instance Is shutdown
E.when flashback logs are updated
Answer: ABD
Question 20
Which two are true about LGWR?
A.LGWR always writes to the redo logs each time a COMMIToccurs.
B.LGWR always writes to the redo logs each time a ROLLBACK occurs.
C.LGWR never writes a single COMMIT to the redo logs.
D.LGWR may write to the redo logs when DBWR writes a dirty buffer.
E.Multiple COMMITs can be written by LGWR in the same write request.
F.LGWR always writes to the redo logs when DBWR writes a dirty buffer.
Answer: AF