1. Which of the following statements about tablespaces is true?
A. A tablespace is the physical implementation of a logical structure called a namespace.
B. A tablespace can hold the objects of only one schema.
C. A bigfile tablespace can have only one data file.
D. The SYSAUX tablespace is an optional tablespace created only if you install certain database options.
2. Automatic segment space management on the tablespace causes which of the following table attributes in that tablespace to be ignored?
A. The whole storage clause
B. NEXT and PCTINCREASE
C. BUFFERPOOL and FREEPOOL
D. PCTFREE and PCTUSED
3. Which is not a type of segment that is stored in a tablespace?
A. Undo
B. Redo
C. Permanent
D. Temporary
4. Which allocation unit is the smallest?
A. Data file
B. Extent
C. Data block
D. Segment
5. You performed the following statement in the database. What actions can you perform on the CUST_INFO table in the CUST_DATA tablespace. (Choose all that apply.) ALTER TABLESPACE CUST_DATA READ ONLY;
A. ALTER TABLE CUST_INFO DROP COLUMN xx;
B. TRUNCATE TABLE CUST_INFO;
C. INSERT INTO CUST_INFO VALUES (...);
D. DROP TABLE CUST_INFO;
E. RENAME CUST_INFO TO CUSTOMER_INFO;
6. If the tablespace is offline, which statements should be executed to make the USERS tablespace read-only? (Choose all that apply.)
A. ALTER TABLESPACE USERS READ ONLY
B. ALTER DATABASE MAKE TABLESPACE USERS READ ONLY
C. ALTER TABLESPACE USERS ONLINE
D. ALTER TABLESPACE USERS TEMPORARY
7. How would you add more space to a tablespace? (Choose all that apply.)
A. ALTER TABLESPACE <TABLESPACE NAME> ADD DATAFILE SIZE < N >
B. ALTER DATABASE DATAFILE <FILENAME> RESIZE < N >
C. ALTER DATAFILE < FILENAME > RESIZE < N >
D. ALTER TABLESPACE < TABLESPACE NAME > DATAFILE < FILENAME > RESIZE < N >
8. The database is using automatic memory management. The standard block size for the database is 8KB. You need to create a tablespace with a block size of 16KB. Which initialization parameter should be set?
A. DB_8K_CACHE_SIZE
B. DB_16K_CACHE_SIZE
C. DB_CACHE_SIZE
D. None of the above
9. Which data dictionary view can be queried to obtain information about the files that belong to locally managed temporary tablespaces?
A. DBA_DATA_FILES
B. DBA_TABLESPACES
C. DBA_TEMP_FILES
D. DBA_LOCAL_FILES
10. How would you drop a tablespace if the tablespace were not empty?
A. Rename all the objects in the tablespace, and then drop the tablespace.
B. Remove the data files belonging to the tablespace from the disk.
C. Use ALTER DATABASE DROP <TABLESPACE NAME> CASCADE .
D. Use DROP TABLESPACE <TABLESPACE NAME> INCLUDING CONTENTS .
11. Which command is used to enable the autoextensible feature for a file if the file is already part of a tablespace?
A. ALTER DATABASE .
B. ALTER TABLESPACE .
C. ALTER DATA FILE .
D. You cannot change the autoextensible feature once the data file is created.
12. Which statement is true regarding the SYSTEM tablespace?
A. It can be made read-only.
B. It can be offline.
C. Data files can be renamed.
D. Data files cannot be resized.
13. The default critical threshold for a tablespace is set at 97 percent, and you think that is too low. Which two options can you use to change the threshold value to 90 percent for tablespace APPS_DATA ?
A. Use Oracle Enterprise Manager Database Express.
B. Use Oracle Enterprise Manager Cloud Control.
C. Use DBMS_SERVER_ALERT package.
D. Use DBMS_SPACE package.
14. Choose the statements that are resumable. (Choose three.)
A. ALTER TABLE ... SPLIT PARTITION
B. SELECT
C. INSERT INTO ... SELECT
D. CREATE TABLESPACE
E. ALTER TABLE ... SHRINK SPACE
15. How do you ensure you are notified when a resumable session is suspended? (Choose two.)
A. You’ll be notified by email at the address registered in database properties.
B. Write a custom script to look for “statement in resumable session * was suspended” in the alert log and notify the DBA.
C. Create an AFTER SUSPEND trigger and a code notification.
D. A suspended session displays a message on the screen.
16. Which statement regarding reclaiming wasted space is true?
A. Segment shrink is accomplished using the ALTER TABLE ... MOVE and ALTER INDEX ... REBUILD statements.
B. Segment shrink and reorganize are similar operations.
C. When a table segment shrink operation is completed, the dependent indexes are in invalid state and need to be rebuilt.
D. A segment shrink operation is applicable only on tablespaces with automatic segment space management.
17. Which compression option should be specified to compress blocks of tables that are used by the OLTP application?
A. COMPRESS
B. NOCOMPRESS
C. COMPRESS FOR OLTP
D. COMPRESS FOR ONLINE DML
18. Choose the best option regarding extents.
A. An extent is a grouping of Oracle blocks.
B. An extent is a grouping of OS blocks.
C. An extent is a grouping of segments.
D. An extent is allocated when a table is created.
19. You issue the statement CREATE TABLESPACE X; . Which of the following is the best option?
A. The statement fails because mandatory properties are not defined.
B. The 100MB tablespace is created.
C. The mandatory DATAFILE clause is missing.
D. The tablespace name should be at least three characters long.
20. Choose the information that is not part of a ROWID.
A. Data object number
B. Relative file number
C. Segment ID where the block belongs
D. Block number in the file
