-
web.groovymark@gmail.com
- December 5, 2024
Question 41
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
Correct Answer: D) PCTFREE and PCTUSED
Explanation: Segment space management refers to free-space management, with automatic segment space management using bitmaps instead of FREELISTS, PCTFREE, and PCTUSED.
Question 42
Which is not a type of segment that is stored in a tablespace?
A) Undo
B) Redo
C) Permanent
D) Temporary
Correct Answer: B) Redo
Explanation: Redo information is not stored in a segment; it is stored in the redo logs. Undo segments are stored in the undo tablespace; temporary segments are in the temporary tablespace; and permanent segments go into all the other tablespaces.
Question 43
Which allocation unit is the smallest?
A) Data file
B) Extent
C) Data block
D) Segment
Correct Answer: C) Data block
Explanation: An extent is composed of two or more data blocks; a segment is composed of one or more extents; and a data file houses all these.
Question 44
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
Correct Answer: D, E) DROP TABLE CUST_INFO; RENAME CUST_INFO TO CUSTOMER_INFO.
Explanation: When a tablespace is read-only, DML operations and operations that affect data in the table are not allowed. TRUNCATE is not allowed, but the DROP operation is allowed. You can also rename the table using the RENAME statement or the ALTER TABLE statement.
Question 45
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
Correct Answer: C, A) ALTER TABLESPACE USERS ONLINE; ALTER TABLESPACE USERS READ ONLY.
Explanation: To make a tablespace read-only, all the data files belonging to the tablespace must be online and available. So bring the tablespace online, and then make it read-only.
Question 46
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>
Correct Answer: A, B) ALTER TABLESPACE <TABLESPACE NAME> ADD DATAFILE SIZE <N>; ALTER DATABASE DATAFILE <FILENAME> RESIZE <N>.
Explanation: You can add more space to a tablespace either by adding a data file or by increasing the size of an existing data file.
Question 47
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
Correct Answer: B) DB_16K_CACHE_SIZE
Explanation: DB_CACHE_SIZE doesn't need to be set for the standard block size because Automatic Memory Management is used. If you set DB_CACHE_SIZE, its value will be used as the minimum. DB_16K_CACHE_SIZE should be set for the nonstandard block size.
Question 48
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
Correct Answer: C) DBA_TEMP_FILES
Explanation: Locally managed temporary tablespaces are created using the CREATE TEMPORARY TABLESPACE command. The data files (temporary files) belonging to these tablespaces are in the DBA_TEMP_FILES view.
Question 49
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.
Correct Answer: D) DROP TABLESPACE <TABLESPACE NAME> INCLUDING CONTENTS.
Explanation: The INCLUDING CONTENTS clause is used to drop a tablespace that is not empty. Oracle does not remove the data files that belong to the tablespace if the files are not Oracle-managed; you need to do it manually using an OS command. Oracle updates only the control fi
Question 50
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.
Correct Answer: A) ALTER DATABASE
Explanation: You can use the ALTER TABLESPACE command to rename a file that belongs to the tablespace, but all other file-management operations are done through the ALTER DATABASE command. To enable autoextension, use ALTER DATABASE DATAFILE <FILENAME> AUTOEXTEND ON.
