| 1: | You were in the process of dropping the invoice_number column in the orders table. The table was marked as invalid while the command was processing. While the column drop was in process, suddenly the instance failed. You restart the instance and notice that the orders table is still invalid. You want to be able to access the data in the table and finish the drop column that you had in process at the failure point. What could you do? [check all that apply] A. Reissue the ALTER TABLE orders DROP COLUMN invoice_number command. B. Issue the ALTER TABLE orders DROP COLUMNS CONTINUE command. C. Truncate the table. D. Drop the table and start over fresh. E. Mark the column as unused and worry about it later.
|
| A1: | Answer B is correct; you can issue the ALTER TABLE orders DROP COLUMNS CONTINUE command to complete the drop and return the table to a valid state. Answer A is incorrect. Because the table is currently invalid, you won't be able to alter it. Answer C is incorrect; if you truncate the table, you will not only no longer be able to get to the data that you have just truncated, you also will not finish removing the column. Answer D is incorrect; dropping the table will mean that you lose all the current data. Answer E is incorrect; simply marking the column as unused will probably fail and will not re-mark the table as valid. |
| 2: | You are logged in to the database as user123, and you issue the following command: CREATE TABLE contacts ( Fname varchar2(35), Lname varchar2(35), Birthday date, Email_address varchar2(100), Street_addr1 varchar2(100), Street_addr2 varchar2(100), Street_addr3 varchar2(100), City varchar2(50), State_abbr char(2), Zip_code number);
As soon as it completes, you realize that you forgot to specify a tablespace into which you want the new table built. You recall that user_users data dictionary view gives you information about user defaults. You issue the following query to determine what tablespace the table is most likely to be built in: SELECT username, default_tablespace, temporary_tablespace FROM user_users WHERE username = 'USER123';
The query returns no rows selected
Where is the most likely place for the table to have been built? |
| A2: | Answer D is correct; the most likely place for the table to be located is in the SYSTEM tablespace because the user didn't have an assigned default tablespace. Answers A and B are incorrect because user123 didn't have USERS as the default tablespace nor the DATA tablespace as the default tablespace for the user. Answer C is incorrect; temporary tablespaces can hold no permanent objects. |
| 3: | Based on the information provided in question 2, where would be the most likely place to look for the location in which the table was actually built? |
| A3: | Answer B is correct; the DBA_TABLES view (as well as the USER_TABLES view and the ALL_TABLES view) will provide information on which tablespace a table is located in. Answer A is incorrect; the control file does not contain table information. Answer C is incorrect; there is no V$TABLES view in the data dictionary. Answer D is incorrect; the DBA_TAB_COMMENTS will provide information on the comments on tables but not the location of the tables. |
| 4: | The backup_addresses external table needs to be dropped from the database because it is no longer needed. Which of the following statements are true about dropping this table? [choose all that apply] A. The data in the table will be completely removed from the tablespace and the data files in which its segments reside. B. The definition will be removed from the data dictionary. C. The data will remain available in the data file. D. You can recover the space with the INCLUDING CONTENTS command. E. You should export the data right before you drop the table in case you need to get the data back. F. If you are using OMF, the data files will be removed from the operating system.
|
| A4: | Answers B and C are correct; the definition will be removed from the data dictionary, but the data will remain in the independent operating system flat file. Answer A is incorrect; external tables are not located in any tablespaces. Answer D is incorrect. There is no space taken up in the database because of the external table's existence; therefore, none will be recovered. Answer E is incorrect; you needn't export the data because it remains in the external operating system file in readable format. Answer F is incorrect; dropping the external table will not for any reason or in any circumstance cause Oracle to delete the data file. |
| 5: | You build a table in a dictionary managed tablespace. Which columns from DBA_TABLES are required to help the DBA determine what the size of the next extents will be whenever the table requires it? [select all that apply] A. PCT_INCREASE B. BLOCKS C. EMPTY_BLOCKS D. INITIAL_EXTENT E. NEXT_EXTENT
|
| A5: | Answers A and E are correct; NEXT_EXTENT shows the size the very next extent will be, and PCT_INCREASE shows the amount that will increase for the following extent. Answers B and C are incorrect; the number of blocks (either empty or that exist in the table) does not play a part in the determination of the size of extents. Answer D is incorrect; it will only lead you back to the size of the first extent in the table. |
| 6: | Which kind of table is created to allow for performance increases and to distribute processing across several tablespaces? |
| A6: | Answer D is correct; partitioned tables can be used to distribute data and therefore processing over different tablespaces. Answer A is incorrect; the data file that underlies external tables can reside in exactly one place on the operating system. Answer B is incorrect; index-organized tables are used to store the data in the leaf blocks of the index but do not distribute data across several tablespaces. Answer C is incorrect; regular tables are the tables that data is typically stored in. |
| 7: | Which kind of table would you create to provide users with fast, primary key based access to the data without reliance on index searches? |
| A7: | Answer B is correct; index-organized tables provide fast access to data based on keyed access because the data is stored in the key. Answer A is incorrect; external tables cannot be indexed. Answer C is incorrect; regular tables can be indexed, but it still requires access to the index and then access to the data. Answer D is incorrect; partitioned tables are often faster but not necessarily by primary key, more often by partition key. |
| 8: | Which statement is true about segments? A. Each table in a cluster has its own segment. B. All the data in a table segment must be stored in one tablespace. C. The data in a regular table segment can be distributed across tablespaces. D. Nested tables within a table use that parent's segment for storage.
|
| A8: | Answer B is correct; all the data in a regular table segment has to be located in a single tablespace. Answer A is incorrect; all the tables in a cluster share blocks and therefore share a single segment. Answer C is incorrect; the data in a partitioned table can be distributed across tablespaces because they are located in different segments. Answer D is incorrect; the nested table has its own storage segment independent of the parent table. |
| 9: | Examine the following statement: TRUNCATE TABLE addresses;
What is true about this command? [select all that apply] A. Only the data is affected; the indexes remain valid and usable. B. All data is deleted from the table. C. Associated indexes are also truncated. D. Undo data is generated for the action.
|
| A9: | Answers B and C are correct; the data is deleted from the table, and the associated indexes are also truncated. Answer A is incorrect; the indexes are truncated as well as the data and therefore contain no rows. Answer D is incorrect; undo data is not generated for a truncate operation. |
| 10: | A user issues the following command on a one million row table TRUNCATE TABLE mytable;
He then realizes that he made a mistake and wanted to truncate the mydata table. He issues the rollback command. What will be the result? A. The table's data will be restored immediately. B. The table's data will be restored, but it will take a while for the rollback to complete. C. The table will remain empty because you can not roll back a trUNCATE command. D. You can't truncate a regular table; the data will remain in the data file.
|
| A10: | Answer C is correct; you cannot roll back from a trUNCATE command. Answers A and C are incorrect; trUNCATE cannot be rolled back. Answer D is incorrect; you cannot truncate an EXTERNAL table, and the data will remain in its data file, but external was not specified in this case. |
