Section: (none)
QUESTION 224
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.
Answer: C Section: (none)
Explanation/Reference:
QUESTION 225
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?
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 Section: (none)
QUESTION 226
Examine the following PL/SQL block:
DECLARE
my_plans pls_integer; BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (sql_id => ?9twu5t2dn5xd?;
END;
Which statement is true about the plan being loaded into the SQL plan baseline by the above command?
A. It is loaded with the FIXED status. B. It is loaded with the ACCEPTED status. C. It is not loaded with the ENABLED status. D. It is not loaded with the ACCEPTED status.
Answer: B Section: (none)
Explanation/Reference:
QUESTION 227
You plan to control idle sessions that are blocking other sessions from performing transactions. Your requirement is to automatically terminate these blocking sessions when they remain idle for a specified amount of time. How would you accomplish this task?
A. Set metric threshold
B. Implement Database Resource Manager C. Enable resumable timeout for user sessions
D. Add directives to Automatic Database Diagnostic Monitor (ADDM)
Answer: B Section: (none)
QUESTION 228
Evaluate the following statements:
CREATE TABLE purchase_orders (po_id NUMBER(4),
po_date TIMESTAMP, supplier_id NUMBER(6), po_total NUMBER(8,2),
CONSTRAINT order_pk PRIMARY KEY(po_id)) PARTITION BY RANGE(po_date)
(PARTITION Q1 VALUES LESS THAN (TO_DATE(?1-apr-2007?d-mon-yyyy?), PARTITION Q2 VALUES LESS THAN
(TO_DATE(?1-jul-2007?d-mon-yyyy?), PARTITION Q3 VALUES LESS THAN (TO_DATE(?1-oct - 2007?d-mon-yyyy?), PARTITION Q4 VALUES LESS THAN
(TO_DATE(?1-jan-2008?d-mon-yyyy?));
CREATE TABLE purchase_order_items (po_id NUMBER(4) NOT NULL, product_id NUMBER(6) NOT NULL, unit_price NUMBER(8,2), quantity NUMBER(8), CONSTRAINT po_items_fk FOREIGN KEY
(po_id) REFERENCES purchase_orders(po_id)) PARTITION BY REFERENCE(po_items_fk);
What are the two consequences of the above statements? (Choose two.)
A. Partitions of PURCHASE_ORDER_ITEMS have system-generated names.
B. Both PURCHASE_ORDERS and PURCHASE_ORDER_ITEMS tables are created with four partitions each. C. Partitions of the PURCHASE_ORDER_ITEMS table exist in the same tablespaces as the partitions of
the PURCHASE_ORDERS table.
D. The PURCHASE_ORDER_ITEMS table inherits the partitioning key from the parent table by automatically
duplicating the key columns.
E. Partition maintenance operations performed on the PURCHASE_ORDER_ITEMS table are automatically
reflected in the PURCHASE_ORDERS table.
Answer: BC Section: (none)
Explanation/Reference:
QUESTION 229
Which statements are true regarding SecureFile LOBs? (Choose all that apply.)
A. The amount of undo retained is user controlled.
B. SecureFile LOBs can be used only for nonpartitioned tables.
C. Fragmentation is minimized by using variable-sized chunks dynamically. D. SecureFile encryption allows for random reads and writes of the encrypted datA.
E. It automatically detects duplicate LOB data and conserves space by storing only one copy.
Answer: CD Section: (none)
QUESTION 230
You plan to use Flashback Drop feature to recover a dropped table SALES_EMP. No other table with the same name exists in the schema.
You query RECYCLEBIN and find multiple entries for the SALES_EMP table as follows: You
then issue the following statement to recover the table:
SQL> FLASHBACK TABLE sales_emp TO BEFORE DROP;
What would be the outcome of the precedent statement?
A. It retrieves the latest version of the table from the recycle bin B. It retrieves the oldest version of the table from the recycle bin
C. It retrieves the version of the table for which undo information is available
D. It returns an error because the table name is not specified as per the names in the OBJECT_NAME column
Answer: A Section: (none)
QUESTION 231
You upgraded Oracle Database 10g to Oracle Database 11g. How would this affect the existing users' passwords?
A. All passwords automatically become case-sensitive.
B. All passwords remain non-case-sensitive till they are changed. C. All passwords remain non-case-sensitive and cannot be changed.
D. All passwords remain non-case-sensitive until their password attribute in the profile is altered.
Answer: B Section: (none)
Explanation/Reference:
QUESTION 232
What recommendations does the SQL Access Advisor provide for optimizing SQL queries?