OCA/OCP: Oracle9i DBA Fundamentals I Study Guide
by Biju Thomas and Bob Bryla
Review Questions
1. Which two of the following statements do you execute to make the USERS tablespace read-only, if the tablespace is offline?
A. ALTER TABLESPACE USERS READ ONLY
B. ALTER DATABASE MAKE TABLESPACE USERS READ ONLY
C. ALTER TABLESPACE USERS ONLINE
D. ALTER TABLESPACE USERS TEMPORARY
---
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.
Ans: C, A.
2. When is a sort segment that is allocated in a temporary tablespace released?
A. When the sort operation completes
B. When the instance is shut down
C. When you issue ALTER TABLESPACE COALESCE
D. When SMON clears up inactive sort segments
---
The sort segment or temporary segment created in a temporary tablespace is released only when the instance is shut down. Each instance can have one sort segment in the tablespace; the sort segment is created when the first sort for the instance is started.
Ans: B.
3. You created a tablespace using the following statement:
CREATE TABLESPACE MYTS
DATAFILE SIZE 200M AUTOEXTEND ON MAXSIZE 2G
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 5M
SEGMENT SPACE MANAGEMENT AUTO;
Which three parameters does Oracle ignore when you create a table in the MYTS tablespace?
A. PCTFREE
B. PCTUSED
C. FREELISTS
D. FREELIST GROUPS
E. INITIAL
-----
When the tablespace has automatic segment space management, Oracle manages free space automatically using bitmaps. For manual segment space management, Oracle uses free lists.
Ans: B, C, D.
4. What will be the minimum size of the segment created in a tablespace if the tablespace’s default storage values are specified as (INITIAL 2M NEXT 2M MINEXTENTS 3 PCTINCREASE 50) and no storage clause is specified for the object?
A. 2MB
B. 4MB
C. 5MB
D. 7MB
E. 8MB
-----
When the segment is created, it will have three extents; the first extent is 2MB, the second is 2MB, and the third is 3MB. So the total size of the segment is 7MB.
Ans: D.
5. Which of the following would you use to add more space to a tablespace? (Choose two.)
A. ALTER TABLESPACE
B. ALTER DATABASE DATAFILE
C. ALTER DATAFILE
D. ALTER TABLESPACE
-----
You can add more space to a tablespace either by adding a data file or by increasing the size of an existing data file. Option A does not specify a file name and uses the OMF feature to generate file name.
Ans: A, B.
6. If the DB_BLOCK_SIZE of the database is 8KB, what will be the size of the third extent when you specify the storage parameters as (INITIAL 8K NEXT 8K PCTINCREASE 50 MINEXTENTS 3)?
A. 16KB
B. 24KB
C. 12KB
D. 40KB
----
The third extent size will be NEXT + 0.5 * NEXT, which is 12KB, but the block size is 8KB, so the third extent size will be 16KB. The initial extent allocated will be 16KB (the minimum size for INITIAL is two blocks), and the total segment size is 16 + 8 + 16 = 40KB.
Ans: A.
7. The standard block size for the database is 8KB. You need to create a tablespace with block size of 16KB. Which initialization parameters should be set? (Choose two.)
A. DB_8K_CACHE_SIZE
B. DB_16K_CACHE_SIZE
C. DB_CACHE_SIZE
D. UNDO_MANAGEMENT
E. DB_CREATE_FILE_DEST
----
Set DB_CACHE_SIZE for the standard block size, and set DB_16K_CACHE_SIZE for the non-standard block size. You must not set the DB_8K_CACHE_SIZE parameter because the standard block size is 8KB.
Ans: B, C.
8. Which data dictionary view can you query 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
----
You create locally managed temporary tablespaces using the CREATE TEMPORARY TABLESPACE command. The data files (temporary files) belonging to these tablespaces are in the DBA_TEMP_FILES view. The EXTENT_MANAGEMENT column of the DBA_TABLESPACES view shows the type of the tablespace. You can query the data files belonging to locally managed permanent tablespaces and dictionary-managed (permanent and temporary) tablespaces from DBA_DATA_FILES. Locally managed temporary tablespaces reduce contention on the data dictionary tables.
Ans: C.
9. When does the SMON process automatically coalesce the tablespaces?
A. When the initialization parameter COALESCE_TABLESPACES is set to TRUE
B. When the PCTINCREASE default storage of the tablespace is set to 0
C. When the PCTINCREASE default storage of the tablespace is set to 50
D. Whenever the tablespace has more than one free extent
-----
The SMON process automatically coalesces free extents in the tablespace when the tablespace’s PCTINCREASE is set to a nonzero value. You can manually coalesce a tablespace by using ALTER TABLESPACE
Ans: C.
Notes: For LMT, will the SMON coalesce the tablespaces?
10. Which operation is permitted on a read-only tablespace?
A. Delete data from table
B. Drop table
C. Create new table
D. None of the above
-----
A table can be dropped from a read-only tablespace. When a table is dropped, Oracle does not have to update the data file; it updates the dictionary tables. Any change to data or creation of new objects is not allowed in a read-only tablespace.
Ans: B.
11. 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
D. Use DROP TABLESPACE
-----
You use the INCLUDING CONTENTS clause 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 operating system command. Oracle updates only the control file. To remove the files, you include the INCLUDING CONTENTS AND DATAFILES clause.
Ans: D.
12. Which command is used to enable the auto-extensible 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 auto-extensible feature once the data file created.
----
You can use the ALTER TABLESPACE command to rename a file that belongs to the tablespace, but you handle all other file management operations through the ALTER DATABASE command. To enable auto-extension, use ALTER DATABASE DATAFILE
Ans: A.
13. The database block size is 4KB. You created a tablespace using the following command.
CREATE TABLESPACE USER_DATA DATAFILE 'C:/DATA01.DBF'
EXTENT MANAGEMENT DICTIONARY;
If you create an object in the database without specifying any storage parameters, what will be the size of the third extent that belongs to the object?
A. 6KB
B. 20KB
C. 50KB
D. 32KB
----
When you create a tablespace with no default storage parameters, Oracle assigns (5 x DB_BLOCK_SIZE) to INITIAL and NEXT; PCTINCREASE is 50. So the third extent would be 50 percent more than the second. The first extent is 20KB, the second is 20KB, and the third is 32KB (because the block size is 4KB).
Ans: D.
14. Which of the following statements is false?
A. You can make a dictionary-managed temporary tablespace permanent.
B. You cannot change the size of the locally managed temporary tablespace file.
C. Once it is created, you cannot alter the extent management of a tablespace using ALTER TABLESPACE.
D. You cannot make a locally managed permanent tablespace temporary.
E. If you do not specify an extent management clause when creating a tablespace, Oracle creates a locally managed tablespace.
----
B. You can change the size of a temporary file using ALTER DATABASE TEMPFILE
Ans: B.
15. Which of the following statements is true regarding the SYSTEM tablespace?
A. Can be made read-only.
B. Can be offline.
C. Data files can be renamed.
D. Data files cannot be resized.
----
You can rename the data files that belong to the SYSTEM tablespace when the database is in the MOUNT state by using the ALTER DATABASE RENAME FILE statement.
Ans: C.
16. What are the recommended INITIAL and NEXT values for a temporary tablespace, to reduce fragmentation?
A. INITIAL = 1MB; NEXT = 2MB
B. INITIAL = multiple of SORT_AREA_SIZE + 1; NEXT = INITIAL
C. INITIAL = multiple of SORT_AREA_SIZE + DB_BLOCK_SIZE; NEXT = INITIAL
D. INITIAL = 2 \ SORT_AREA_SIZE; NEXT = SORT_AREA_SIZE
----
The recommended storage for a TEMPORARY tablespace is a multiple of SORT_AREA_SIZE + DB_BLOCK_SIZE. For example, if the sort area size is 100KB and the block size is 4KB, the sort extents should be sized 104KB, 204KB, 304KB, and so on. The disk is sorted only when there is not enough space available in memory. Memory sort size is specified by the SORT_AREA_SIZE parameter. Therefore, when the sorting is done on disk, the minimum area required is as big as the SORT_AREA_SIZE, and one block is added for the overhead. The INITIAL and NEXT storage parameters should be the same for the TEMPORARY tablespace, and PCTINCREASE should be zero. You can achieve these storage settings by creating a locally managed temporary tablespace with uniform extent sizes.
Ans: C.
17. Which parameter specified in the DEFAULT STORAGE clause of CREATE TABLESPACE cannot be altered after you create the tablespace?
A. INITIAL
B. NEXT
C. MAXEXTENTS
D. None
----
You can change all the default storage parameters defined for the tablespace using the ALTER TABLESPACE command. Once objects are created, you cannot change their INITIAL and MINEXTENTS values.
Ans: D.
18. How would you determine how much sort space is used by a user session?
A. Query the DBA_SORT_SEGMENT view.
B. Query the V$SORT_SEGMENT view.
C. Query the V$SORT_USAGE view.
D. You can obtain only the total sort segment size; you cannot find information on individual session sort space usage.
----
The V$SORT_USAGE view provides the number of EXTENTS and number of BLOCKS used by each sort session. This view provides the username also. It can be joined with V$SESSION or V$SQL to obtain more information on the session or the SQL statement causing the sort.
Ans: C.
19. If you issue ALTER TABLESPACE USERS OFFLINE IMMEDIATE, which of the following statements is true? (Choose two.)
A. All data files belonging to the tablespace must be online.
B. Does not ensure that the data files are available.
C. Need not do media recovery when bringing the tablespace online.
D. Need to do media recovery when bringing the tablespace online.
----
When you take a tablespace offline with the IMMEDIATE clause, Oracle does not perform a checkpoint and does not make sure that all data files are available. You must perform a media recovery when the tablespace is brought online.
Ans: B, D.
20. Which format strings does Oracle use to generate OMF file names? (Choose three.)
A. %s
B. %t
C. %g
D. %a
E. %u
F. %%
---
The data file names have the format ora_%t_%u.dbf, redo log files have the format ora_%g_%u.log, and control files have the format ora_%u.ctl. %t is the tablespace name and can be a maximum of 8 characters, %u is an 8-character unique string, and %g is the redo log group number.
Ans: B, C, E.
No comments:
Post a Comment