OCA/OCP: Oracle9i DBA Fundamentals I Study Guide
Review Questions
1. A table is created as follows:
CREATE TABLE MY_TABLE (COL1 NUMBER)
STORAGE (INITIAL 2M NEXT 2M MINEXTENTS 6 PCTINCREASE 0);
When you issue the following statement, what will be the size of the table, if the high-water mark of the table is 200KB?
ALTER TABLE MY_TABLE DEALLOCATE UNUSED KEEP 1000K;
A. 1000KB
B. 200KB
C. 12000KB
D. 2MB
E. 13MB
---
Ans:
C. You use the KEEP parameter in the DEALLOCATE clause to specify the amount of space you want to keep in the table above the HWM. If you do not specify the KEEP parameter, Oracle deallocates all the space above the HWM if the HWM is above MINEXTENTS; otherwise, free space is de-allocated above MINEXTENTS.
2. Which command is used to drop a constraint?
A. ALTER TABLE MODIFY CONSTRAINT
B. DROP CONSTRAINT
C. ALTER TABLE DROP CONSTRAINT
D. ALTER CONSTRAINT DROP
----
Ans C. Constraints are defined on the table and are dropped using the ALTER TABLE command DROP clause. For dropping the primary key, you can also specify PRIMARY KEY instead of the constraint name. Similarly, to drop a unique constraint, you can also specify UNIQUE (
3. When you define a column with datatype TIMESTAMP WITH LOCAL TIME ZONE, what is the precision of seconds stored?
A. 2
B. 6
C. 9
D. 0
---
Ans:
B. The TIMESTAMP datatypes have a default precision of 6 digits. The range of values can be from 0 to 9.
4. Which data dictionary view has the timestamp of the table creation?
A. DBA_OBJECTS
B. DBA_SEGMENTS
C. DBA_TABLES
D. All the above
---
Ans: A. The DBA_OBJECTS view has information about all the objects created in the database and has the timestamp and status of the object in the column CREATED. DBA_TABLES does not show the timestamp.
5. What happens when you issue the following statement and the CHAINED_ROWS table does not exist in the current schema?
ANALYZE TABLE EMPLOYEE LIST CHAINED ROWS;
A. Oracle creates the CHAINED_ROWS table.
B. Oracle updates the dictionary with the number of chained rows in the table.
C. Oracle creates the CHAINED_ROWS table under the SYS schema; if one already exists under SYS, Oracle uses it.
D. The statement fails.
---
Ans: D. If you do not specify a table name to insert the ROWID of chained/ migrated rows, Oracle looks for a table named CHAINED_ROWS in the user’s schema. If the table does not exist, Oracle returns an error. The dictionary (the DBA_TABLES view) is updated with the number of chained rows when you do a COMPUTE STATISTICS on the table.
6. The following statement is issued against the primary key constraint (PK_BONUS) of the BONUS table. (Choose two statements that are true.)
ALTER TABLE BONUS MODIFY CONSTRAINT PK_BONUS DISABLE VALIDATE;
A. No new rows can be added to the BONUS table.
B. Existing rows of the BONUS table are validated before disabling the constraint.
C. Rows can be modified, but the primary key columns cannot change.
D. The unique index created when defining the constraint is dropped.
---
6. A and D. DISABLE VALIDATE disables the constraint and drops the index, but keeps the constraint valid. No DML operation is allowed on the table.
7. Which clause in the ANALYZE command checks for the integrity of the rows in the table?
A. COMPUTE STATISTICS
B. VALIDATE STRUCTURE
C. LIST INVALID ROWS
D. None of the above
----
Ans: B. The VALIDATE STRUCTURE clause of the ANALYZE TABLE command checks the structure of the table and makes sure all rows are readable.
8. Which statement is not true?
A. A partition can be range-partitioned.
B. A subpartition can be range-partitioned.
C. A partition can be hash-partitioned.
D. A subpartition can be hash-partitioned.
----
Ans:
B. Subpartitions can only be hash-partitioned. A partition can be range-partitioned or hash-partitioned.
9. A table is created with an INITRANS value of 2. Which value would you choose for INITRANS of an index created on this table?
A. 4
B. 2
C. 1
----
Ans:
A. Since index blocks hold more entries per block than table data blocks hold, you should provide a higher value of INITRANS to the index than to the table.
10. When validating a constraint, why would you specify the EXCEPTIONS clause?
A. To display the ROWIDs of the rows that do not satisfy the constraint
B. To move the bad rows to the table specified in the EXCEPTIONS clause
C. To save the ROWIDs of the bad rows in the table specified in the EXCEPTIONS clause
D. To save the bad rows in the table specified in the EXCEPTIONS clause
---
Ans:
C. If you specify the EXCEPTIONS INTO clause when validating or enabling a constraint, the ROWIDs of the rows that do not satisfy the constraint are saved in the table specified in the EXCEPTIONS clause. You can remove the bad rows or fix the column values and enable the constraint.
11. Which keyword is not valid for the BUFFER_POOL parameter of the STORAGE clause?
A. DEFAULT
B. LARGE
C. KEEP
D. RECYCLE
----
The BUFFER_POOL parameter specifies a buffer pool cache for the table or index. The KEEP pool retains the blocks in the SGA. RECYCLE removes blocks from the SGA as soon as the operation is completed, and the DEFAULT pool is for objects for which KEEP or RECYCLE is not specified.
Ans: B
12. Which clause in the ALTER TABLE command do you use to reorganize a table?
A. REORGANIZE
B. REBUILD
C. RELOCATE
D. MOVE
----
You use the MOVE clause to reorganize a table. You can specify new tablespace and storage parameters. Queries are allowed on the table, but no DML operations are allowed during the move.
Ans: D.
13. Which line in the following code has an error?
1 ALTER TABLE MY_TABLE
2 STORAGE (
3 MINEXTENTS 4
4 NEXT 512K)
5 NOLOGGING;
A. 2
B. 3
C. 4
D. 5
----
When you change the storage parameters for an existing index or table, you cannot change the MINEXTENTS and INITIAL values.
Ans: B.
14. Which component is not part of the ROWID?
A. Tablespace
B. Data file number
C. Object ID
D. Block ID
----
The format of a ROWID is OOOOOOFFFBBBBBBRRR; OOOOOO is the object number, FFF is the relative data file number where the block is located, BBBBBB is the block ID where the row is located, and RRR is the row in the block.
Ans: A.
15. Which keyword do you use in the CREATE INDEX command to create a function-based index?
A. CREATE FUNCTION INDEX
B. CREATE INDEX ORGANIZATION INDEX
C. CREATE INDEX FUNCTION BASED
D. None of the above
----
You don’t need to specify a keyword to create a function-based index; you need only to specify the function itself. To enable a function- based index, you set the parameter QUERY_REWRITE_ENABLED to TRUE, and you set QUERY_REWRITE_INTEGRITY to TRUSTED.
Ans: D.
16. Which data dictionary view shows statistical information from the ANALYZE INDEX VALIDATE STRUCTURE command?
A. INDEX_STATS
B. DBA_INDEXES
C. IND
D. None; VALIDATE STRUCTURE does not generate statistics.
---
The INDEX_STATS and INDEX_HISTOGRAMS views show statistical information from the ANALYZE INDEX VALIDATE STRUCTURE statement.
Ans: A.
17. A constraint is created with the DEFERRABLE INITIALLY IMMEDIATE clause. What does this mean?
A. Constraint checking is done only at commit time.
B. Constraint checking is done after each SQL statement, but you can change this behavior by specifying SET CONSTRAINTS ALL DEFERRED.
C. Existing rows in the table are immediately checked for constraint violation.
D. The constraint is immediately checked in a DML operation, but subsequent constraint verification is done at commit time.
---
DEFERRABLE specifies that the constraint can be deferred using the SET CONSTRAINTS command. INITIALLY IMMEDIATE specifies that the constraint’s default behavior is to validate the constraint for each SQL statement.
Ans: B.
18. Which script creates the CHAINED_ROWS table?
A. catproc.sql
B. catchain.sql
C. utlchain.sql
D. No script is necessary; ANALYZE TABLE LIST CHAINED ROWS creates the table.
---
The utlchain.sql script, located in the rdbms/admin directory for the Oracle software installation, creates the table. When chained or migrated rows are found in the table after you issue the ANALYZE TABLE LIST CHAINED ROWS command, the ROWIDs of such chained/ migrated rows are inserted into the CHAINED_ROWS table.
Ans: C.
19. What is the difference between a unique constraint and a primary key constraint?
A. A unique key constraint requires a unique index to enforce the constraint, whereas a primary key constraint can enforce uniqueness using a unique or non-unique index.
B. A primary key column can be NULL, but a unique key column cannot be NULL.
C. A primary key constraint can use an existing index, but a unique constraint always creates an index.
D. A unique constraint column can be NULL, but primary key column(s) cannot be NULL.
----
Columns that are part of the primary key cannot accept NULL values.
Ans: D.
20. You can monitor an index for its usage by using the MONITORING USAGE clause of the ALTER INDEX statement. Which data dictionary view do you use to query the index usage?
A. USER_INDEX_USAGE
B. V$OBJECT_USAGE
C. V$INDEX_USAGE
D. DBA_INDEX_USAGE
----
The V$OBJECT_USAGE view has information about the indexes that are monitored. The START_MONITORING and END_ MONITORING columns give the start and end timestamp of monitoring. If an index is used, the USED column will have a value YES.
Ans: B.
No comments:
Post a Comment