1Z0-053 Online Practice Questions and Answers

The database users regularly complain about the difficulty in performing transactions. On investigation, you find that some users perform long-running transactions that consume huge amounts of space in the undo tablespace, which caused the problem. You want to control the usage of the undo tablespace only for these user sessions and you do not want these sessions to perform long-running operations.

How would you achieve this?

A. Implement a profile for the users.

B. Implement external roles for the users.

C. Set the threshold for the undo tablespace.

D. Implement a Database Resource Manager plan.

The ADMIN_EMP table has columns EMPNO, ENAME, DEPTNO, and SAL. It has a materialized view EMP_MV with a materialized log and an ENAME_IDX index on the ENAME column. You need to perform an online table redefinition on the ADMIN_EMP table to move it from the TBS1 tablespace to the TBS2 tablespace in the same schema.

What action is required for the dependent objects when you perform online redefinition on the table?

A. The materialized view should have a complete refresh performed after the online table redefinition is completed.

B. The materialized view should have a fast refresh performed after the online table redefinition is completed.

C. The materialized view, materialized log, and the index should be dropped and re-created after the online table redefinition is complete.

D. The materialized view and materialized log should be dropped and all constraints disabled and re-created after the online table redefinition is complete.

Which of these recommendations should be followed before capturing a workload? (Choose all that apply.)

A. Make sure your replay database has the same structure as the capture database, except without data.

B. Make sure the replay and capture databases are similar in data content.

C. Perform a clean shutdown and restart of the capture database before beginning a workload capture.

D. Start the capture database in UNRESTRICTED mode, then start the capture.

E. Define inclusion and exclusion filters.

Which two prerequisites are needed for performing workload capture and replay? (Choose two.)

A. Close all sessions performing queries using database links.

B. running the database in shared server mode

C. The database on which the workload is replayed has to be a restore of the original database to a specific SCN.

D. setting up the directory to capture the workload

The OPTIMIZER_USE_PLAN_BASELINES parameter is set to TRUE. The optimizer generates a plan for a SQL statement but does not find a matching plan in the SQL plan baseline.

Which two operations are performed by the optimizer in this scenario? (Choose two.)

A. The optimizer adds the new plan to the plan history.

B. The optimizer selects the new plan for the execution of the SQL statement.

C. The optimizer adds the new plan to the SQL plan baseline as an accepted plan.

D. The optimizer adds the new plan to the SQL plan baseline but not in the ENABLED state.

E. The optimizer costs each of the accepted plans in the SQL plan baseline and picks the one with the lowest cost.

Which statement about Automatic Memory Management with Oracle 11g is true?

A. You cannot specify MEMORY_TARGET if you explicitly specify SGA_TARGET or PGA_AGGREGATE_TARGET values that are greater than zero in your parameter file.

B. Oracle can reallocate memory between the SGA and PGA automatically as needed.

C. To use Automatic Memory Management, you must explicitly set both the MEMORY_TARGET and MEMORY_MAX_TARGET parameters in your parameter file.

D. You can set the MEMORY_TARGET parameter to a maximum value of the current SGA size plus the current PGA size.

Which two statements are true regarding the starting of the database instance using the following command? (Choose two.)

SQL>STARTUP UPGRADE

A. It enables all system triggers.

B. It allows only SYSDBA connections.

C. It ensures that all job queues remain active during the upgrade process.

D. It sets system initialization parameters to specific values that are required to enable database upgrade scripts to be run.

A user named Arren is executing this query: select table_name, operation, undo_sql from flashback_transaction_query t, (select versions_xid as xid from employees versions between scn minvalue and maxvalue where employee_id = 123) e where t.xid = e.xid;

When the query runs, he receives an ORA-01031: insufficient privileges error. Since the user owns the employees table, you know that it is not the problem.

Which of the following SQL statements will correct this problem?

A. GRANT SELECT ANY TRANSACTION TO ARREN;

B. GRANT SELECT ON FLASHBACK_TRANSACTION_QUERY TO ARREN;

C. GRANT SELECT_ANY_TRANSACTION TO ARREN;

D. GRANT FLASHBACK TO ARREN;

E. GRANT SELECT ANY VIEW TO ARREN;

Users notify you that their application is failing every time they try to add new records. Because of poor application design, the actual ORA error message is unavailable. What might be the problem? (Choose the best answers.)

A. The application user has exceeded their undo quota.

B. The FLASHBACK GUARANTEE option is set on the undo tablespace.

C. The table is currently being queried by a Flashback Transaction Query operation.

D. The table is currently being queried by a Flashback Versions Query operation.

E. The RETENTION GUARANTEE option is set on the undo tablespace.

Identify two situations in which you can use Data Recovery Advisor for recovery. (Choose two.)

A. The user has dropped an important table that needs to be recovered.

B. The database files are corrupted when the database is open.

C. You are not able to start up the database instance because the required database files are missing.

D. The archived log files are missing for which backup is not available.