Oracle Oracle Certifications 1Z0-053 Questions & Answers

• Question 511:

  You need to create a partitioned table to store historical data and you issued the following command:

  CREATE TABLE purchase_interval PARTITION BY RANGE (time_id) INTERVAL (NUMTOYMINTERVAL(1,'month')) STORE IN (tbs1,tbs2,tbs3) ( PARTITION p1 VALUES LESS THAN(TO_DATE('1-1-2005', 'dd-mm-yyyy')), PARTITION p2 VALUES LESS THAN(TO_DATE('1-1- 2007', 'dd-mm-yyyy')) ) AS SELECT * FROM purchases WHERE time_id < TO_DATE('1-1-2007','dd-mm-yyyy');

  What is the outcome of the above command?

  A. It returns an error because the range partitions P1 and P2 should be of the same range.

  B. It creates two range partitions (P1, P2). Within each range partition, it creates monthwise subpartitions.

  C. It creates two range partitions of varying range. For data beyond '1-1-2007,' it creates partitions with a width of one month each.

  D. It returns an error because the number of tablespaces (TBS1,TBS2,TBS3)specified does not match the number of range partitions (P1,P2) specified.

  Correct Answer: C

• Question 512:

  View the Exhibit for some of the current parameter settings. A user logs in to the HR schema and issues the following commands:

  SQL> CREATE TABLE emp (empno NUMBER(3), ename VARCHAR2(20), sal NUMBER(8,2));

  SQL> INSERT INTO emp(empno,ename) VALUES(1,'JAMES');

  At this moment, a second user also logs in to the HR schema and issues the following command:

  SQL> ALTER TABLE emp MODIFY sal NUMBER(10,2);

  What happens in the above scenario?

  Exhibit:

  

  A. The second user's session immediately produces the resource busy error.

  B. The second user's command executes successfully.

  C. The second user's session waits for a time period before producing the resource busy error.

  D. A deadlock is created.

  Correct Answer: C

• Question 513:

  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.

  Correct Answer: A

  Restrictions for Online Redefinition of Tables

  After redefining a table that has a materialized view log, the subsequent refresh of any dependent materialized view must be a complete refresh.

• Question 514:

  Which statement describes the effect of table redefinition on the triggers attached to the table?

  A. All triggers on the table are invalidated and are automatically revalidated with the next DML execution on the table.

  B. All triggers on the table are invalidated and must be manually recompiled before the next DML execution on the table.

  C. All triggers on the table remain valid.

  D. Only triggers that are affected by the changes to the structure of the table are invalidated and automatically revalidated with the next DML execution on the table.

  Correct Answer: A

• Question 515:

  Which statements are true regarding table compression? (Choose all that apply.)

  A. It saves disk space and reduces memory usage.

  B. It saves disk space but has no effect on memory usage.

  C. It incurs extra CPU overhead during DML as well as direct loading operations.

  D. It incurs extra CPU overhead during DML but not direct loading operations.

  E. It requires uncompress operation during I/O.

  Correct Answer: AC

• Question 516:

  Observe the following PL/SQL block:

  BEGIN dbms_spm.configure('SPACE_BUDGET_PERCENT', 30); END;

  Which statement is correct regarding the above PL/SQL block?

  A. It automatically purges the SQL management objects when SMB occupies more than 30% of the SYSAUX tablespace.

  B. It reserves 30% of the space in the SYSAUX tablespace for SQL Management Base (SMB).

  C. It reserves 30% of the space in the SYSTEM tablespace for SMB.

  D. It generates a weekly warning in the alert log file when SMB occupies more than 30% of the SYSAUX tablespace.

  Correct Answer: D

• Question 517:

  Examine the following command:

  SQL> ALTER TABLE booking SHRINK SPACE COMPACT;

  Which activity is performed when the preceding command is executed?

  A. The shrink operation touches every block in the BOOKING table

  B. The high-water mark (HWM) for the BOOKING table is shifted from its original position

  C. The progress of the shrink operation is saved in the bitmap blocks of the BOOKING table

  D. The data manipulation language (DML) triggers on the BOOKING table are executed because the shrink operation is internally handled by the INSERT/DELETE operation

  Correct Answer: C

• Question 518:

  You notice that a long-running transaction is suspended due to a space constraint, and there is no AFTER SUSPEND triggered event addressing the issue. You also note that the critical transaction is just about to reach the RESUMABLE_TIMEOUT value.

  Which of these actions is appropriate?

  A. Abort the session, fix the space problem, then resubmit the transaction.

  B. Use the DBMS_RESUMABLE.SET_SESSION_TIMEOUT procedure to extend the time-out for the session while you fix the problem.

  C. Do nothing, let the transaction fail, then fix the problem.

  D. Use Segment Shrink to clean up the table.

  E. Use the DBMS_RESUMABLE.SET_TIMEOUT procedure to extend the time-out for the session while you fix the problem.

  Correct Answer: B

• Question 519:

  Evaluate the following command and its output: SQL>SELECT * FROM dba_temp_free_space;

  TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE =============== =============== =============== ========== LMTEMP 250609664 101048576 149561088

  Which two statements correctly interpret the output? (Choose two.)

  A. FREE_SPACE indicates only the space that is currently unallocated.

  B. ALLOCATED_SPACE indicates only the space currently allocated and in use.

  C. FREE_SPACE indicates only the space that is currently allocated and available for reuse.

  D. ALLOCATED_SPACE indicates both the space currently allocated and used, and the space that is available for reuse.

  E. FREE_SPACE indicates both the space that is currently allocated and available for reuse, and the space that is currently unallocated.

  Correct Answer: DE

• Question 520:

  View the exhibit and examine the TRANS table's storage information. After a massive delete operation, you executed the following statement to shrink the TRANS table:

  SQL> ALTER TABLE trans SHRINK SPACE CASCADE;

  Which statement describes the outcome of the command?

  Exhibit:

  

  A. An error is produced.

  B. The table and all related objects are compacted and the position of the high-water mark (HWM) for the table is adjusted

  C. The table and related indexes are compacted but the position of the high-water mark (HWM) for the table remains unchanged

  D. The unused space in the table is reclaimed and returned to the tablespace and the data manipulation language (DML) triggers on the table are fired during the shrinking process

  Correct Answer: B