| 1: | You issue the command ALTER TABLESPACE TEMP ADD DATAFILE '/mydatabases/mydb1/temp02.dbf' 100M;
and you receive the error ORA-03217: invalid option for alter of TEMPORARY TABLESPACE
What is the problem with the statement? A. You didn't specify any storage specifications. B. You can't add a data file to a temporary tablespace. C. You can't add a data file to a temporary tablespace with the ALTER TABLESPACE command. D. You specified an invalid location for the file.
|
| A1: | Answer C is correct. You must use the ALTER DATABASE command to add a tempfile to the temporary tablespace. Answer A is incorrect because you don't have to specify any storage specifications when you add a data file to an existing tablespace. It takes on the same specifications as the other data files associated with the tablespace. Answer B is incorrect; you can add tempfiles to temporary tablespaces but you have to use the parameter add temp file. Answer D is incorrect; your location is likely correct, and that isn't the error you would get if it was incorrect. |
| 2: | You run the following statement: CREATE TABLESPACE mydb1data2 DATAFILE '/mydatabases/mydb1/mydb1data2_01.dbf' SIZE 200M MINIMUM EXTENT 4K NOLOGGING DEFAULT STORAGE (INITIAL 10K NEXT 10K) EXTENT MANAGEMENT DICTIONARY SEGMENT SPACE MANAGEMENT AUTO;
Why does this statement return an error? A. Segment storage management cannot be set to AUTO for a dictionary managed tablespace. B. You can't define a tablespace as NOLOGGING if it is managed in the data dictionary. C. You can't define a tablespace as dictionary managed in Oracle 9i. D. You can't create a tablespace with a minimum extent of only 4KB.
|
| A2: | Answer A is correct. You cannot set extent management to AUTO for a dictionary managed tablespace. Answer B is incorrect; you can define a tablespace as NOLOGGING regardless of whether it is locally managed or dictionary managed. Answer C is incorrect; not only can you create a dictionary managed tablespace in Oracle 9i, it is still the default if not the recommended method. Answer D is incorrect; you can specify any minimum extent size you want to for a tablespace. |
| 3: | You issue the following query to move your undo tablespace to a more appropriate location: [View full width] ALTER TABLESPACE undo01 RENAME DATAFILE '/mydatabases/mydb1/undo01_01.dbf' TO '  /mydatabases2/mydb1/undo01_01.dbf';
What is the outcome of this command? A. An error because the new data file is the same name as the old data file. B. The data file is moved to the new location. C. An error because the DATAFILE keyword does not need to be specified. D. An error because you cannot rename an undo tablespace's filename with an ALTER TABLESPACE command.
|
| A3: | Answer D is correct. You can only move the undo tablespace's data file with the ALTER DATABASE command when the database is mounted but not open and only if you have copied the data file to the new location with the specified name. Answer A is incorrect; although you cannot move a data file with the ALTER TABLESPACE command, as long as the file exists, it doesn't matter what the name is. Answer B is incorrect because you cannot move an undo tablespace's data file in this manner. Answer C is incorrect; even if it were a "regular" tablespace, you have to use the DATAFILE keyword in the statement. |
| 4: | Which of the flowing is true? A. Segments make up extents, extents make up tablespaces, and tablespaces make up databases. B. Extents make up tablespaces, tablespaces make up segments, and segments make up databases. C. Extents make up segments, segments make up tablespaces, and tablespaces make up databases. D. Databases make up segments, segments make up extents, and extents make up data files.
|
| A4: | Answer C is correct. Extents make up segments, segments make up tablespaces, and tablespaces make up databases. Answers A, B, and D are incorrect. |
| 5: | You have created a new data tablespace as locally managed with auto-matic space management. Where is the free space managed? |
| A5: | Answer C is correct. The free space is managed and tracked in a bitmap in the data file. Answer A is incorrect; dictionary managed tablespace manages free space in the data dictionary. Answer B is incorrect; free space is never tracked in the control file. Answer D is incorrect; free space for a locally managed data tablespace is never tracked in the temporary tablespace. |
| 6: | You have decided to create your database with Oracle Managed Files. Where are the data files created by default? A. In the location specified by DB_CREATE_ONLINE_LOG_DEST_n B. In the SYSTEM tablespace C. In a user's default temporary tablespace whenever the user creates the tablespace D. In the location specified by the DB_CREATE_FILE_DEST
|
| A6: | Answer D is correct. Data files are created in the location specified by the DB_CREATE_FILE_DEST parameter. Answer A is incorrect; redo logs and control files are created in the location specified by DB_CREATE_ONLINE_LOG_DEST_n. Answer B is incorrect because data files are not created in the SYSTEM tablespace; they are created in the file system of the operating system on which the database is running. Answer C is incorrect; data files are not created in tablespaces, but in the file system. |
| 7: | Evaluate the following SQL command: CRATE TEMPORARY TABLESPACE mydb1_temp TEMPFILE '/mydatabases/mydb1/mydb1_temp_01.dbf' SIZE 100M AUTOEXTEND ON;
Which statements are true about the statement? [choose all that apply] A. Your new temporary tablespace is locally managed. B. Your new temporary tablespace is dictionary managed. C. You can rename the tempfile with the ALTER TABLESPACE command. D. You can rename the tempfile with the ALTER DATABASE command when the database is mounted but not open. E. You can add a tempfile with an ALTER TABLESPACE command. F. You can add a tempfile with an ALTER DATABASE command.
|
| A7: | Answers B, D, and F are correct. Your new temporary tablespace is dictionary managed, it can only have its tempfiles renamed with the ALTER DATABASE command, and the only way to add a tempfile to the temporary tablespace is to alter the database. Answer A is incorrect; unless otherwise specified (and it is not in this case), tablespaces are still created by default as dictionary managed. Answer C is incorrect; you cannot rename tempfiles with the ALTER TABLESPACE command. Answer E is incorrect; you cannot add a tempfile with the ALTER TABLESPACE command. |
| 8: | Which of the following identifies a feature of Oracle 9i that, when implemented, will force all segment extents allocated in a tablespace to be the same size, regardless of storage allocations defined on the objects in the tablespace? |
| A8: | Answer B is correct; uniform extent management causes all extents in all segments in a tablespace to be the same size regardless of storage parameters provided to any of the individual objects stored in that tablespace. Answer A is incorrect; AUTOEXTEND is concerned with tablespaces and allowing them to grow automatically without intervention from the DBA. Answer C is incorrect; MAXEXTENTS tells Oracle the maximum number of extents that you want to be able to be built in a tablespace or on an object in the tablespace depending on where the parameter is used. Answer D is incorrect, a CREATE TABLESPACE statement has a storage clause, but the word storage itself won't cause the extents to be the same size. |
| 9: | Which are ways that tablespaces can be brought offline? [check all that apply] A. IMMEDIATE B. ABORT C. NORMAL D. trANSACTIONAL E. TEMPORARY
|
| A9: | Answers A, C, and E are all ways that you can bring tablespaces offline. NORMAL will not require media recovery, IMMEDIATE probably will require media recovery, and TEMPORARY won't likely require media recovery if all the data files were online at the time that you took the tablespace offline. Answers B and D are both ways that you can shut down the database but not ways of taking tablespaces offline. |
| 10: | Which tablespace or tablespaces cannot be brought offline as long as the database is open? [select all that apply] |
| A10: | Answers A and D are correct; you cannot offline the SYSTEM tablespace nor the default temporary tablespace while the database is open. All the other tablespaces can be brought offline, including the SYS tablespace because it is not an Oracle-provided tablespace and would have been user defined. |
