# OCA: Oracle Database 11g Administrator Certified Associate- P2

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

0
66
lượt xem
8

## OCA: Oracle Database 11g Administrator Certified Associate- P2

Mô tả tài liệu

Tham khảo tài liệu 'oca: oracle database 11g administrator certified associate- p2', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

Chủ đề:

Bình luận(0)

Lưu

## Nội dung Text: OCA: Oracle Database 11g Administrator Certified Associate- P2

1. SQL Fundamentals I Assessment Test xlv 36. A view is created using the following code. Which of the following operations are permitted on the view? CREATE VIEW USA_STATES AS SELECT * FROM STATE WHERE CNT_CODE = 1 WITH READ ONLY; A. SELECT B. SELECT, UPDATE C. SELECT, DELETE D. SELECT, INSERT 37. You query the database with the following: SELECT PRODUCT_ID FROM PRODUCTS WHERE PRODUCT_ID LIKE ‘%S\_J\_C’ ESCAPE ‘\’; Choose the two PRODUCT_ID strings that will satisfy the query. A. BTS_J_C B. SJC C. SKJKC D. S_J_C 38. The EMPLOYEE table is defined as follows: EMP_NAME VARCHAR2(40) HIRE_DATE DATE SALARY NUMBER (14,2) Which query is most appropriate to use if you need to find the employees who were hired before January 1, 1998 and have a salary greater than 5,000 or less than 1,000? A. SELECT emp_name FROM employee WHERE hire_date > TO_DATE(‘01011998’,’MMDDYYYY’) AND SALARY < 1000 OR > 5000; B. SELECT emp_name FROM employee WHERE hire_date < TO_DATE(‘01011998’,’MMDDYYYY’) AND SALARY < 1000 OR SALARY > 5000; C. SELECT emp_name FROM employee WHERE hire_date < TO_DATE(‘01011998’,’MMDDYYYY’) AND (SALARY < 1000 OR SALARY > 5000); D. SELECT emp_name FROM employee WHERE hire_date < TO_DATE(‘01011998’,’MMDDYYYY’) AND SALARY BETWEEN 1000 AND 5000;
2. xlvi SQL Fundamentals I Assessment Test 39. What happens when you issue the following command? (Choose all that apply.) TRUNCATE TABLE SCOTT.EMPLOYEE; A. All the rows in the table EMPLOYEE owned by SCOTT are removed. B. The storage space used by the table EMPLOYEE is released (except the initial extent). C. If foreign key constraints are defined to this table using the ON DELETE CASCADE clause, the rows from the child tables are also removed. D. The indexes on the table are dropped. E. You cannot truncate a table if triggers are defined on the table. 40. Which two statements will drop the primary key defined on table EMP? The primary key name is PK_EMP. A. ALTER TABLE EMP DROP PRIMARY KEY; B. DROP CONSTRAINT PK_EMP; C. ALTER TABLE EMP DROP CONSTRAINT PK_EMP; D. ALTER CONSTRAINT PK_EMP DROP CASCADE; E. DROP CONSTRAINT PK_EMP ON EMP;
4. xlviii Answers to SQL Fundamentals I Assessment Test 10. A, B. The index contains all the information needed to satisfy the query in option A, and a full-index scan would be faster than a full-table scan. A subset of index columns is specified in the WHERE clause of option B; hence, Oracle 11g can use the index. For more information on indexes, see Chapter 7. 11. D. The CREATE SEQUENCE statement will create an increasing sequence that will start with 1, will increment by 1, and will be unaffected by the rollback. A rollback will never stuff vales back into a sequence. See Chapter 7 to learn more about sequences. 12. B, C. Primary and unique key constraints can be enforced using nonunique indexes. Unique constraints allow NULL values in the columns, but primary keys do not. Read Chap- ter 6 to learn more about constraints. 13. B. The SYSDATE function returns the date and time on the server where the database instance is started. CURRENT_DATE returns the local date and time. For information on the built-in date functions, read Chapter 2. 14. D. The first INSERT statement and last INSERT statement will be saved in the database. The ROLLBACK TO A statement will undo the second and third inserts. To know more about transaction control and ROLLBACK, read Chapter 5. 15. B. There should be at least n-1 join conditions when joining n tables to avoid a Cartesian join. To learn more about joins, see Chapter 4. 16. C. The table and column names can include only three special characters: #, $, and _. No other characters are allowed in the table name. You can have letters and numbers in the table name. To learn more about table and column names, read Chapter 6. 17. B, D. You can use the IS NULL or IS NOT NULL operator to search for NULLs or non-NULLs in a column. Since NULLs are sorted higher, they appear at the bottom of the result set in an ascending-order sort. See Chapter 1 for more information about sorting NULL values. 18. D. COUNT () does not include the NULL values, whereas COUNT (*) includes the NULL values. No other aggregate function takes NULL into consideration. To learn more about aggregate functions, read Chapter 3. 19. E. These statements don’t account for possible NULL values in the BONUS column. For more information about NULL values, see Chapter 2. 20. D. Using the ALTER TABLE statement, you can add new columns, rename existing columns, and drop existing columns. To learn more about managing tables, read Chapter 6. 21. B. Since DEPARTMENT_ID is NULL for employee 178, NULL will be sorted after the non-NULL values when doing an ascending-order sort. Since I did not specify the sort order or the NULLS FIRST clause, the defaults are ASC and NULLS LAST. Read Chapter 1 for more infor- mation on SELECT and sort orders. 22. D, E, F, G. The INTERVAL DAY TO SECOND datatype is used to store an interval between two datetime components. See Chapter 6 for more information on the INTERVAL and TIMESTAMP datatypes. 5. Answers to SQL Fundamentals I Assessment Test xlix 23. D, E. In the join view, CITY is the key-preserved table. You can update the columns of the CITY table, except STATE_CD, because STATE_CD is not part of the view definition (the STATE_CD column in the view is from the STATE table). Since I did not include the STATE_CD column from the CITY table, no INSERT operations are permitted (STATE_CD is part of the primary key). If the view were defined as follows, all the columns of the CITY table would have been updatable, and new records could be inserted into the CITY table. CREATE OR REPLACE VIEW state_city AS SELECT b.state_cd, a.state_name, b.city_cd, b.city_name FROM states a, cities b WHERE a.state_cd = b.state_cd; See Chapter 7 for more information about views. 24. B. When altering an existing column to add a NOT NULL constraint, no rows in the table should have NULL values. In the example, there are two rows with NULL values. Creating and modifying tables are discussed in Chapter 6. 25. D. NATURAL JOIN and JOIN…USING clauses will not allow alias names to be used. Since a self-join is getting data from the same table, you must include alias names and qualify col- umn names. To learn more about ANSI join syntax, read Chapter 4. 26. E. The TRUNC function used with a negative second argument will truncate to the left of the decimal. To learn more about TRUNC and other numeric functions, read Chapter 2. 27. C. Oracle creates unique indexes for each unique key and primary key defined in the table. The table ADDRESSES has one unique key and a primary key. Indexes will not be created for NOT NULL or foreign key constraints. Constraints are discussed in Chapter 6. 28. D. Although there is no error in this statement, the statement will not return the desired result. When a NULL is compared, you cannot use the = or != operator; you must use the IS NULL or IS NOT NULL operator. See Chapter 1 for more information about the comparison operators. 29. A. You cannot explicitly change the next value of a sequence. You can set the MAXVALUE or INCREMENT BY value to a negative number, and NOCYCLE tells Oracle to not reuse a sequence number. See Chapter 7 for more information. 30. B. Private synonyms override public synonyms, and tables or views owned by the user always resolve first. To learn more about synonyms, see Chapter 7. 31. C, D. When COMMIT is executed, all locks are released, all savepoints are erased, and que- ries started before the COMMIT will constitute a read-consistent view using the undo infor- mation. To learn more about COMMIT, read Chapter 5. 32. B, C. The operators OR and AND are used to add more joining conditions to the query. NOT is a negation operator, and a comma is used to separate column names and table names. Read more about joins and join conditions in Chapter 4. 6. l Answers to SQL Fundamentals I Assessment Test 33. C. Since you are finding the aggregate of the aggregate, you should not use nonaggregate columns in the SELECT clause. To read more about nesting of aggregate functions, see Chapter 3. 34. B. You can create primary key, foreign key, and unique key constraints on a view. The con- straints on views are not enforced by Oracle. To enforce a constraint, it must be defined on a table. Views can be created with the WITH CHECK OPTION and READ ONLY attributes dur- ing view creation. Read Chapter 7 to learn more. 35. A, C. The maximum lengths of CHAR and VARCHAR2 columns can be defined in charac- ters or bytes. BYTE is the default. To learn more about CHAR and VARCHAR2 datatypes, read Chapter 6. 36. A. When the view is created with the READ ONLY option, only reads are allowed from the view. See Chapter 7 to learn more about creating views as read-only. 37. A, D. The substitution character % can be substituted for zero or many characters. The substitution character _ does not have any effect in this query because an escape character precedes it, so it is treated as a literal. Read Chapter 1 to learn more about substitution characters. 38. C. You have two main conditions in the question: one on the hire date and the other on the salary. So, you should use an AND operator. In the second part, you have two options: the salary can be either more than 5,000 or less than 1,000, so the second part should be enclosed in parentheses and should use an OR operator. Option B is similar to option C except for the parentheses, but the difference changes the meaning completely. Option B would select the employees who were hired before January 1, 1998 or have a salary greater than 5,000 or less than 1,000. Read Chapter 1 to learn more about writing queries using filtering conditions. 39. A, B. The TRUNCATE command is used to remove all the rows from a table or cluster. By default, this command releases all the storage space used by the table and resets the table’s high-water mark to zero. No indexes, constraints, or triggers on the table are dropped or disabled. If there are valid foreign key constraints defined to this table, you must disable all of them before truncating the table. Chapter 5 includes a comparison between using TRUNCATE and the DELETE statement to remove rows. 40. A, C. Since there can be only one primary key per table, the syntax in option A works. Any constraint (except NOT NULL) can be dropped using the syntax in option C. Learn more about constraints in Chapter 6. 7. Administration I Assessment Test 1. Which of the following is not considered part of Oracle Database? A. Data files B. Redo logs C. Pfile and spfile D. Control files 2. The following are the steps required for relocating a data file belonging to the USERS tablespace. Order the steps in their proper sequence. A. Copy the file /disk1/users01.dbf to /disk2/users01.dbf using an OS command. B. ALTER DATABASE RENAME FILE ‘/disk1/users01.dbf’ TO ‘/disk2/users01.dbf’ C. ALTER TABLESPACE USERS OFFLINE D. ALTER TABLESPACE USERS ONLINE 3. You manage one non-Oracle Database and several Oracle Databases. An application needs to access the non-Oracle database as if it were part of the Oracle database. What tool allows you to do this? (Choose the best answer.) A. Oracle Advanced Security B. Oracle Connection Manager C. Heterogeneous Services D. Oracle Net E. None of the above 4. Choose two utilities that can be used to apply CPU patches on an Oracle 11g database. A. Oracle Universal Installer B. OPatch C. EM Database Control D. DBCA 5. The loss of a data file in which two tablespaces requires an instance shutdown to recover the tablespace? A. TEMP B. SYSTEM C. UNDO D. SYSAUX 8. lii Administration I Assessment Test 6. Which of the following statements is not always true? (Choose two.) A. Every database should have at least two tablespaces. B. Every database should have at least two data files. C. Every database should have at least three multiplexed redo logs. D. Every database should have at least three control files. 7. Which statement about the initialization-parameter files is true? A. The pfile and spfile can be modified using the ALTER SYSTEM statement. B. You cannot have both an spfile and a pfile under the$ORACLE_HOME/dbs directory. C. The pfile is used only to read by the Oracle instance, whereas the spfile is used to read and write to. D. On Windows systems, pfile and spfiles are not used because parameters are modified using the system registry. 8. Which initialization parameter determines the location of the alert log file? A. DIAGNOSTIC_DEST B. BACKGROUND_DUMP_DEST C. ALERT_LOG_DEST D. USER_DUMP_DEST 9. Which parameter is used to set up the directory for Oracle to create data files if the DATAFILE clause does not specify a filename when creating or altering tablespaces? A. DB_FILE_CREATE_DEST B. DB_CREATE_FILE_DEST C. DB_8K_CACHE_SIZE D. USER_DUMP_DEST E. DB_CREATE_ONLINE_LOG_DEST_1 10. Which component of the SGA has the dictionary cache? A. Buffer cache B. Library cache C. Shared pool D. Program global area E. Large pool F. Result cache
9. Administration I Assessment Test liii 11. A constraint is created with the DEFERRABLE INITIALLY IMMEDIATE clause. What does this mean? A. Constraint checking is done only at commit time. B. Constraint checking is done after each SQL, but you can change this behavior by speci- fying SET CONSTRAINTS ALL DEFERRED. C. Existing rows in the table are immediately checked for constraint violation. D. The constraint is immediately checked in a DML operation, but subsequent constraint verification is done at commit time. 12. You have just made changes to the listener.ora file for the listener called listener1 using Oracle Net Manager. Which of the following commands or combinations of commands would you use to put the changes into effect with the least amount of client disruption? A. lsnrctl stop listener1 followed by lsnrctl start listener1 B. lsrnctl restart listener1 C. lsnrctl reload listener1 D. lsnrctl reload 13. What is the prefix for dynamic performance views? A. X$B. V$ C. ALL_ D. DBA_ 14. If you are updating one row in a table using the ROWID in the WHERE clause (assume that the row is not already in the buffer cache), what will be the minimum amount of information copied to the database buffer cache? A. The entire table is copied to the database buffer cache. B. The extent is copied to the database buffer cache. C. The block is copied to the database buffer cache. D. The row is copied to the database buffer cache. 15. When you are configuring Shared Server, which initialization parameter would you likely need to modify? A. DB_CACHE_SIZE B. DB_BLOCK_BUFFERS C. LARGE_POOL_SIZE D. BUFFER_SIZE E. None of the above
14. lviii Administration I Assessment Test 38. You need to copy the GL schema from production to qa_test, changing the tablespace for indexes from gl_index to fin_indx. What is the best way to satisfy these requirements? A. First, use Data Pump to copy the schema without indexes. Then, change the default tablespace for user GL in qa_test to fin_indx. Next, use Data Pump to copy the indexes. Finally, change the default tablespace for user GL back to gl_data. B. Use the dbms_metadata package to extract table and index DDL. Then, use Notepad (or sed) to edit this DDL, changing the tablespace for the indexes. Finally, run the DDL in the qa_test database. C. Use Data Pump import, specifying a remap_datafile parameter to change the data file location for indexes. D. Use Data Pump import, specifying a remap_tablespace parameter to change the tablespace location for indexes. 39. Identify the statement that is not true about checkpoints. A. Instance recovery is complete when the data from the last checkpoint up to the latest SCN in the control file has been written to the data files. B. A checkpoint keeps track of what has already been written to the data files. C. The redo log group writes must occur before a Commit complete is returned to the user. D. The distance between the checkpoint position in the redo log file and the end of the redo log group can never be more than 90 percent of the size of the largest redo log group. E. How much the checkpoint lags behind the SCN is controlled by both the size of the redo log groups and by setting the parameter FAST_START_MTTR_TARGET. 40. The STATUS column of the dynamic performance view V$LOG contains what value if the redo log file group has just been added? A. INVALID B. STALE C. UNUSED D. NULL 41. When performing Data Pump import using impdp, which of the following options is not a valid value to the TABLE_EXISTS_ACTION parameter? A. SKIP B. APPEND C. TRUNCATE D. RECREATE 15. Administration I Assessment Test lix 42. What would you do to reduce the time required to start the instance after a database crash? A. Multiplex the redo log files. B. Increase the size of the redo log files. C. Set the FAST_START_MTTR_TARGET parameter to 0. D. All of the above. E. None of the above. 16. lx Answers to Administration I Assessment Test Answers to Administration I Assessment Test 1. C. Although pfiles and spfiles are physical files used to configure the Oracle instance, they are not considered part of the database. To learn more about Oracle Database structure, read Chapter 8. 2. C, A, B, D. To rename a data file, you need to make the tablespace offline so that Oracle does not try to update the data file while you are renaming. Using OS commands, copy the data file to the new location, and using the ALTER DATABASE RENAME FILE command or the ALTER TABLESPACE RENAME FILE command, rename the file in the database’s con- trol file. To rename the file in the database, the new file should exist. Bring the tablespace online for normal database operation. See Chapter 10 for more information. 3. C. Heterogeneous Services is the correct answer because these services provide cross- platform connectivity to non-Oracle databases. Oracle Advanced Security would not solve this application problem because it addresses security and is not accessibility to non-Oracle databases. Oracle Net would be part of the solution, but another Oracle Network com- ponent is necessary. Connection Manager would also not be able to accommodate this requirement on its own. Read Chapter 11 to learn more. 4. B, C. CPU patches and interim patches can be applied using the OPatch utility or using EM Database Control. EM Database Control also includes patch search and download options. See Chapter 17 for more information. 5. B, C. Only the SYSTEM and UNDO tablespaces require the instance to be shut down when their data files need recovery. Read Chapter 16 to learn about database recovery. 6. C, D. Every database must have at least two redo log files, which may or may not be multi- plexed. Every database must have one control file. It is a good idea to have more than one con- trol file for redundancy. Since SYSTEM and SYSAUX are mandatory tablespaces in Oracle 11g, there will be at least two data files. See Chapter 8 for more information. 7. C. A pfile is a read-only file, and no database changes are written to the pfile. There is no harm in having both an spfile and a pfile in the$ORACLE_HOME/dbs directory; Oracle will only read the spfile when starting the database. On Windows systems also, you will need a parameter-initialization file; the registry is not used. Read more about parameter files in Chapter 9. 8. A. Oracle 11g uses the Automatic Diagnostic Repository to maintain the alert log and other diagnostic information. In pre–Oracle 11g databases, the BACKGROUND_DUMP_DEST parameter determined the alert log location; in Oracle 11g, this parameter value is ignored. To learn more about the alert log and its contents, read Chapter 9. 9. B. DB_CREATE_FILE_DEST specifies the directory to create data files and temp files. This directory is also used for control files and redo log files if the DB_CREATE_ONLINE_LOG_ DEST_1 parameter is not set. Learn more in Chapter 10.
17. Answers to Administration I Assessment Test lxi 10. C. The shared pool has three components: the library cache, the result cache, and the dic- tionary cache. Read Chapter 8 to learn more about SGA and Oracle instances. 11. B. DEFERRABLE specifies that the constraint can be deferred using the SET CONSTRAINTS command. INITIALLY IMMEDIATE specifies that the constraint’s default behavior is to vali- date the constraint for each SQL. Constraints are discussed in Chapters 7 and 10. 12. C. Although you can use option A to stop and start the listener, doing so temporarily dis- rupts clients attempting to connect to the database. Option D is fine if you are starting and stopping the default listener called LISTENER, but you are using a nondefault listener here. Option B is not valid because RESTART is not a valid command-line argument for lsnrctl. Therefore, the best method is to use the lsnrctl reload listener1 command to load the new set of values in for the listener without disrupting connection service to the databases that the listener is servicing. For more information, read Chapter 11. 13. B. Dynamic performance views begin with V$. The actual views have a prefix of V_$, and the synonyms have a prefix of V$. The V$ views are based on the X\$ tables, known as dynamic performance tables. To learn more about dynamic performance views and tables, read Chapter 9. 14. C. The block is the smallest unit that can be copied to the buffer cache. Information in the dictionary cache is copied as rows. To learn about buffer cache and dictionary cache, read Chapter 8. 15. C. Shared Server requires a shift of memory away from individual session processes to the SGA. More information has to be kept in the SGA (in the UGA) within the shared pool. A large pool is configured and is responsible for most of the SGA space allocation. Option C is the correct answer. The cache size and block buffers settings do not affect Shared Server. Read Chapter 11 for more information. 16. D. PUBLIC is the group or class of database users where all existing and future database users belong. See Chapter 12 for more information. 17. A, C. You cannot dynamically change the parameter UNDO_MANAGEMENT after the instance has started. You can, however, change the UNDO_TABLESPACE parameter to switch to another undo tablespace while the instance is up and running. Read Chapter 13 to learn more. 18. C. The Manageability Monitor (MMON) process gathers performance statistics from the system global area (SGA) and stores them in the AWR. Manageability Monitor Light (MMNL) also does some AWR-related statistics gathering, but not to the extent that MMON does. QMN1 is the process that monitors Oracle advanced queuing features. Memory Manager (MMAN) is the process that dynamically manages the sizes of each SGA component when directed to make changes by the Automatic Database Diagnostic Monitor (ADDM). For more information, see Chapter 14. 19. B. Oracle automatically performs instance recovery after a database crash or SHUTDOWN ABORT. All uncommitted changes are rolled back, and committed changes are written to data files during instance recovery. Read Chapter 9 for more information.