| A1: | Answer D is the only correct answer. Checkpoint doesn't actually write anything out to files; it causes the DBWn process to write the data from the database buffer cache out to the database files. Answer A is incorrect because LOG_CHECKPOINT_INTERVAL tells CKPT how often in terms of volume to cause a checkpoint, but it does not ever write anything. Answer B is incorrect because LOG_CHECKPOINT_TIMEOUT tells CKPT how often in terms of periods of time in seconds to cause a checkpoint, but it does not ever write anything. Answer C is incorrect because a log switch causes CKPT to occur, but it does not ever write anything. |
| A2: | Answer C is correct. Rerunning the script immediately won't resolve any issues and will likely present you with new ones that may cloud the original issue. Answer A should be done first; check to see whether you have syntax errors in your CREATE DATABASE script. Answer B should be done next; make sure that you have enough space in the place you are using for your files to house them. Answer D should be done right before you are ready to run the script again; leaving files out in the same location will likely cause your script to fail again. |
| A3: | Answer B is correct. p.password is the procedure used to authenticate the role. Answer A is incorrect; passwords cannot contain special characters such as periods. Answer C is incorrect; the default role isn't set at the role level but at the user level. Answer D is incorrect because answer B is indeed correct. |
| A4: | Answer A is correct; you cannot edit the SPFILE with a text editor. The SPFILE is a binary file that will no longer work if you edit it with a text editor. Answer B is incorrect; many of the values in the SPFILE can be altered dynamically during the time when the database is operational. Answer C is incorrect; the SPFILE is binary and is maintained by Oracle. Answer D is incorrect; the SPFILE is an initialization parameter file that houses initialization parameters that are persistent between shutdown and startup of the database. |
| A5: | Answer D is correct; for a table to be redefined online, it needs to have a primary key defined for it. Answer A is incorrect because although primary keys cannot be NULL, the table can have nullable columns. Answer B is incorrect; no table requires check constraints, and redefinition has no influence on this decision. Answer C is incorrect; although primary key infers uniqueness, a unique key is not the constraint required. |
| A6: | Answer C is correct; the first thing that you need to do when you define a Database Resource Manager solution for your database is to create a pending area. Answers A, B, and D are incorrect; you have to have a pending area to define a resource plan, a plan directive, or a consumer group. |
| A7: | Answer A is correct. The only action allowed on an external table is select. Answers B, C, D, and E are all incorrect. Only select is allowable on external tables. They are read-only tables. One of the drawbacks to external tables is that they are not indexable. |
| A8: | Answer D is correct; for automatic segment space management to work, the segment management clause needs to be specified with the CREATE TABLESPACE command, and the tablespace has to have been created locally managed. Answer A is incorrect; in Oracle 9i the default is still dictionary managed for all tablespaces. Answer B is incorrect; tablespaces are automatic space managed. This carries down to the table level, but a table cannot have a different space management clause than its tablespace. Answer C is incorrect; for a tablespace to be automatic space managed, it has to be locally managed, not dictionary managed. |
| A9: | Answer B is correct; to use the password file, the REMOTE_LOGON_PASSWORDFILE parameter has to be set to EXCLUSIVE if you want those to whom SYSDBA has been granted to be able to log in as sysdba using the password file. Answer A is incorrect; it is not necessary to be DBA. You have to be SYSDBA to use password files for authentication. Answer C is incorrect; it is irrelevant where the password file is created as long as it is available to Oracle. Answer D is incorrect; the user used the correct password to connect. |
| A10: | Answer B is correct; to determine the size of the initial extent and the total number of extents allocated to a given segment, you would query the USER_SEGMENTS view. Answer A is incorrect; USER_EXTENTS provides information on the segment name, partition, type of segment, tablespace in which it is located, and ID of the current extent. Answer C is incorrect; USER_OBJECTS provides information about the object, its type, its ID, and when it was created but not anything dealing with extents. Answer D is incorrect; USER_OBJECT_SIZE does not provide information in extents or provide extent sizing information. Answers E and F are incorrect because they include USER_OBJECT_SIZE. |
| A11: | Answers B, E, and F are correct; she can create a materialized view in any schema, she can enable the role and exercise any privileges in the role's privilege domain, and she can grant the role to and revoke the role from other users because she has been given WITH ADMIN OPTION. Answer A is incorrect because nothing in the statement provides the information that she can select from a table. Answer C is incorrect; just because she can create a materialized view does not mean that she can alter any materialized view other than those that she owns. Answer D is incorrect; nowhere does it indicate that she can connect or create a session in the database. |
| A12: | Answers A and C are correct; you can use SQL*Plus or Enterprise Manager to start up and shut down Oracle. Answer B, Server Manager, was a utility available in previous releases of Oracle but is no longer supported or available. Answer D, PL/SQL, is a procedural language used in Oracle, not a utility available through which you can start up or shut down a database. |
| A13: | Answer A is correct. First you connect as SYSDBA or as a user with SYSDBA role and shut down the database. Then you restart the database in NOMOUNT mode and alter the system state to point CONTROL_FILES to the correct location. Then shut down the database again and restart and open for operation. Answer B is incorrect because you would not open the database in MOUNT mode and then remove the SPFILE with an OS command. The file is being read by the instance, and if you read the answer carefully, you will see that there is no way to start an already running database. This would be a trick answer. Answer C is incorrect because you don't want to remove the SPFILE. You just want to correct where it is pointing. Answer D is incorrect because simply recopying won't help; the control files have already had their headers changed, and recopying the control file won't solve your problems. |
| A14: | Answer B is correct; this command would create a B-tree index. Answer A is incorrect; there are no keywords in the command (CREATE BITMAP INDEX) to indicate that it would be a bitmap index. Answer C is incorrect; there is no partitioning clause on the CREATE INDEX statement. Answer D is incorrect; nothing is included to indicate that the index should be reverse key; therefore, it will not be. |
| A15: | Answers A and B are correct. Either deliberately issuing the ALTER SYSTEM SWITCH LOGFILE command or when the current online redo log file is filled will cause a log switch. Answer C is incorrect; redo log files do not switch when the instances is shut down. Answer D is incorrect; redo log switches are not connected to transactions. Answer E is incorrect; starting the database will not automatically cause the redo logs to switch. |
| A16: | Answer A is correct; you need to REVOKE the CREATE SESSION privilege from the users to make sure that they can't log in to the database but to also allow the owned objects to remain in the database to be accessed by others. Answer B is incorrect; dropping the users also drops the objects belonging to those users. Answer C is incorrect; again, dropping the users would drop the objects as well, and after you drop the users, you can no longer revoke privileges from them. Answer D is incorrect because not only will removing the users remove the objects, the command would be drop, not delete. |
| A17: | Answer B is correct; errors generated by SQL*Plus sessions would typically go into the user trace files found in the udump directory. Answer A is incorrect because errors would never be found in the control file. Answer C is incorrect because SQL*Plus sessions are user sessions and would typically not create background trace files. Those are created by server processes in the instance. Answer D is incorrect; core trace files, or core dump files, are generated when the server core dumps but not when SQL*Plus generates errors. Answer E is incorrect because errors would never be generated in the initialization files. |
| A18: | Answer A is correct; DBA_TAB_PRIVS would give you an overview of what table privileges have been given to all database users. Answer B is incorrect; ALL_TAB_PRIVS would show you all the table privileges that the logged-in user has been granted. Answer C is incorrect; USER_TAB_PRIVS would show you all the privileges that the given user has on his or her own tables. Answer D is incorrect; ALL_TAB_PRIVS_MADE is not a valid data dictionary view. |
| A19: | Answer B is correct; this will not only create a unique index in the correct tablespace, it also will have the proper space management, will be dictionary managed, and will generate no redo information (NOLOGGING keyword). Answer A is incorrect; it will generate redo log entries. Answer C is incorrect; you need to specify the PCTFREE not PCTUSED. Answer D is incorrect; you need to specify PCTFREE not PCTUSED, and answer D will generate redo log entries. |
| A20: | Answer D is correct. The shared pool does store the most recently executed SQL statements and the most recently accessed data definitions. Answer A is incorrect; the shared pool is dynamically resized starting in Oracle 9i. Answer B is incorrect; the shared pool does not contain only fixed structures. Answer C is incorrect; the library cache and the buffer cache are not the only structures in the SGA. |
| A21: | Answer C is correct; the control file is a part of the database. Answer A is incorrect; the database creation script created the database but is not a part of the database. Answer B is incorrect; the password file is one of the auxiliary files to the database but is not a part of it. Answer D is incorrect; parameter files are required for the starting of the database but not a part of it. Answer E is incorrect; archive log files are auxiliary structures to the database not a part of the database. |
| A22: | Answer C is correct; you should contact Mike and request that he commit the current transactions. Answer A is incorrect; Mike has the records locked that Steve needs to access. Steve is not just being impatient. Answer B is not the answer; Steve should not have to abort his transaction. Answer D is incorrect; Steve is seeing hung systems already. Issuing a commit will not only not solve the current problem, it will also not work because the session is in effect locked. |
| A23: | Answer B is correct; multiple independent instances can coexist on the same physical server. Answer A is incorrect; the redo log buffers are a part of the shared memory area. Answer C is incorrect; user processes are not a part of the Oracle instance. Answer D is incorrect; you can have multiple PGAs in a shared server environment. |
| A24: | Answers A, B, and F are correct; the purpose of redo log files is to allow for elegant instance recovery in case of an instance failure, and the redo log group individual files are called members. Answer C is incorrect; you need a minimum of two groups. Answer D is incorrect; Oracle requires a minimum of two redo log groups, each only has to hold one file. Answer E is incorrect; UNDO ensures read consistency, not the redo log files. |
| A25: | Answer C is correct; the information required can be found in the DBA_USERS table. Answer A is incorrect; ALL_USERS only gives you a listing of each user in the database when they were created, and their user_id. Answer B is incorrect; USER_USERS shows you all the information needed except the profile. Answer D is incorrect; V$USERS is not a dynamic performance view. |
| A26: | Answer B is correct; under these circumstances, the server process places the commit record into the redo log buffer. Answer A is incorrect; COMMIT does not cause the database buffer cache to be flushed. Answer C is incorrect; log writer does not play a part in a COMMIT operation. Answer D is incorrect. Users do not have to notify anything of the COMMIT operation; Oracle handles that itself. Answer E is incorrect; Oracle handles the notification, not the user process. |
| A27: | Answers A and C are correct; MOUNT mounts the database for certain DBA activities but does not provide user access to the database, and the OPEN command enables users to access the database. Answer B is incorrect; NOMOUNT does not create the buffers. Answer D is incorrect; STARTUP starts the instance and the database. Answer E is incorrect; because A and C are correct. |
| A28: | Answer B is correct; you want to manage templates. Answer A is incorrect; you would use this option to create a new database. Answer C is incorrect; you would use this option to change configuration settings in a database that already exists. Answer D is incorrect; it is not an option in the DBCA. |
| A29: | Answer D is correct; you need to create a new tablespace that has the desired settings and then move the existing tables into that tablespace. Answer A is incorrect; you can meet the requirements easily without having to rebuild the database. Answer B is incorrect; you don't in any case need to touch the control files. Answer C is incorrect; you cannot simply alter the current tablespace to meet the cited requirements. |
| A30: | Answers A and D are correct. All newly started transactions will use the UNDOTBS_2 tablespace, and the UNDOTBS_1 enters into PENDING OFFLINE status as long as any active transactions are using it. Answer B is incorrect; currently active transactions will not switch but will continue using the UNDO tablespace to which they were originally assigned. Answer C is incorrect; it is permissible to switch to a new UNDO tablespace with active transactions in the database. Answer E is incorrect; new transactions will see UNDOTBS_2 as the active UNDO tablespace. Answer F is incorrect; you can have two UNDO tablespaces defined just not active at the same time. |
| A31: | Answer B is correct; extents are logical collections of database blocks. Answer A is incorrect; segments are made up of extents, not extents made up of segments. Answer C is incorrect; tablespaces are made up of segments and extents. Answer D is incorrect; operating system blocks are not necessarily contiguous. |
| A32: | Answer B is correct; IDENTIFIED GLOBALLY indicates that the user must be authorized to use the role by the enterprise directory service before it can be enabled. Answer A is incorrect; ar_clerk does not need to be authorized. Answer C is incorrect; it is identified by password. Answer D is incorrect; the role is authorized using a package. Answer E is incorrect; the role is authorized by the OS. |
| A33: | Answer B is correct; because the column has low cardinality and is rarely updated, a bitmap index would be best. Answer A is incorrect; although a B-tree index would work, it is not the optimum solution. Answer C is incorrect; simply sorting in the other order. Answer D is incorrect; there is no point in a function-based index. |
| A34: | Answer A is correct; low cardinality columns are prime candidates for bitmap indexes. Answer B is incorrect; there is no indication that the column is itself or that it is included in a set of unique columns. Answer C is incorrect; there is no indication that the table or the index should be partitioned. Answer D is incorrect; reverse key is not indicated. Answer E is incorrect; although the bitmap may be on a single column, that is not a type of index. Answer F is incorrect; there is no function indicated as associated with the column in question. |
| A35: | Answer B is correct. The SGA, as part of the memory structure, is created when the instance is started and remains allocated until the instance is shut down. Answer A is incorrect because the SGA isn't part of the database. Answer C is incorrect because user processes have to interact with an open instance, and the SGA, as a memory structure, opens when the instance opens. Answer D is incorrect because, as a memory structure, the SGA has to be available before any server processes can start. |
| A36: | Answer A is correct. When you use STARTUP NOMOUNT, the instance is started, and the database is not mounted. Answer B is incorrect because NOMOUNT means that the database has not been mounted. Answer C is incorrect because the database is neither mounted nor opened. Answer D is incorrect because the instance is started, and the database is not mounted. |
| A37: | Answer D is correct; regardless of the space management, rollback information is controlled in the undo tablespace. Answer A is incorrect; free space is managed in the extent. Answer B is incorrect; the control file plays no part in either space management or rollback information. Answer C is incorrect; in dictionary managed tablespaces, the data dictionary controls space management. |
| A38: | Answers A and D are correct; the directories in question already have to exist, and they must have the appropriate permissions to allow Oracle to write to them. Answer B is incorrect; it doesn't matter whether files already exist in the directory. Answer C is incorrect; the location of the directory is irrelevant. |
| A39: | Answer C is correct; with it you can alter the user lonny to be authenticated externally and have profile CLERK with this script. Answer A is incorrect; although it is authenticated externally, it grants a role not a profile to user lonny. Answer B is incorrect; it is authenticated with a password. Answer D is incorrect; it grants a role to lonny, not a profile. |
| A40: | Answers B and D are correct; Oracle creates an index for primary keys and for unique indexes. Answer A is incorrect; check constraints do not automatically have associated indexes. Answer C is incorrect; not null is not associated automatically with an index. Answer E is incorrect; foreign keys are not automatically associated with indexes. |
| A41: | Answer B is correct; to keep users from using the SYSTEM tablespace as a sort area, you should create a default temporary tablespace. Answer A is incorrect; an UNDO tablespace does not keep users from sorting in the SYSTEM tablespace. Answer C is incorrect; UNDO keyword creates an UNDO tablespace. Answer D is incorrect; although the TEMPORARY keyword creates a temporary tablespace, it is not a default temporary tablespace and therefore does not keep users not deliberately assigned to this tablespace from using the SYSTEM tablespace as a sort area. |
| A42: | Answer C is correct; the parse information and the bind variable information would be stored in the PGA and the library cache. Answer A is incorrect; it requires both the library cache with the PGA. Answer B is incorrect; the row cache won't participate in either the parse values or the bind variable information. Answer D is incorrect; it requires the PGA along with the library cache portion of the SGA. Answer E is incorrect; the buffer cache will not participate as it holds the data. |
| A43: | Answer C is correct; database writer performs the writing of the modified blocks to be written out to the data files. Answer A is incorrect. Log writer does not write data; it writes redo log files. Answer B is incorrect; the SYSTEM MONITOR does not write out any files. Answer D is incorrect; although checkpoint does participate in the checkpointing process, it does not write out the data to the data files. Answer E is incorrect; PROCESS MONITOR does not participate in the checkpoint or data writing process. |
| A44: | Answers D and G are correct; in the event of a network error that terminates a user's session, PMON will rollback the current transaction and release all table and row locks held by that session. Answer A is incorrect; checkpoints will not occur. Answer B is incorrect; no commits will happen automatically. Answer C is incorrect; recovery manager will not recover the session. Answers E and F are incorrect; SMON is not involved in cleaning up user sessions when they fail. |
| A45: | Answers C and D are correct; NEXT_EXTENT and PCT_INCREASE give you the information on the next extents. Answer A is incorrect; BLOCKS tells you the number of blocks in the segment but not the size of the next extent. Answer B is incorrect; nothing about free space will let you know about extent sizing. Answer E is incorrect; INITIAL_EXTENT plays only a minimal role in the next extent. |
| A46: | Answer B is correct; the SGA is created when the instance is started. Answer A is incorrect; the SGA needs to be available before the database is created to create the structures in the new database. Answer C is incorrect; the SGA needs to be available to mount the database. Answer D is incorrect; open comes after mounted, and therefore the SGA would have to be available. Answer E is incorrect; there is no SGA server process. |
| A47: | Answer C is correct; partitioning makes highly scalable applications and is useful to segment large tables so that they can be accessed in parallel. Answer A is incorrect; a regular table is far less scalable than a partitioned table. Answer B is incorrect; cluster tables often speed up access but not in parallel. Answer D is incorrect; index organized tables are not any more parallelizable than the others. |
| A48: | Answer A is correct; 1/1440 is 1 minute. Answer B is incorrect; 5 minutes plays no part in any of the equation. Answer C is incorrect; grace time is 10 minutes, not the lock out time. Answer D is incorrect; 14 minutes is not one of the options. Answer E is incorrect; 18 minutes is not one of the options. Answer F is incorrect; 60 days is the life of the password, not the number of minutes that the password is locked. |
| A49: | Answer C is correct; the order that Oracle searches for the initialization files is spfile<sid>.ora, spfile.ora, init<sid>.ora, and finally, init.ora. Answer A is incorrect; init.ora would be the last file to be searched for. Answer B is incorrect; spfile<sid>.ora would be before spfile.ora. Answer D is incorrect; the init.ora files would be searched after the spfile.ora files. |
| A50: | Answer A is correct; ENABLE NOVALIDATE would provide the required validation. Answer B would disable the constraint. Answer C is incorrect; ENABLE VALIDATE would enable the constraint but validate the current data. Answer D is incorrect; it would disable the constraint. |
| A51: | Answers A, C, and E are correct; data blocks are logical structures. Segments consist of at least one extent when they are created. A tablespace can consist of several data files each on the same or different disks. Answer B is incorrect; multiple files can belong to a tablespace but not vice versa and would create error conditions. Answer D is incorrect; data blocks of an extent have to belong to the same file. Answer F is incorrect; because you can have multiple files to a tablespace, you can have extents from more than one file. |
| A52: | Answer B is correct; you would need to set the LOG_CHECKPOINT_TIMEOUT value, force a checkpoint with the FAST_START_MTTR_TARGET, and then go start monitoring the alert log files to watch checkpoints. Answer A would not provide sufficient information in the alert logs but would provide information only from that period forward. To be sure of the information that you gather, you need to have a beginning and an end point, created with a forced checkpoint. Answer C would provide more than enough information in the log switch but not sufficient with the checkpointing. Answer D is incorrect; you don't force the checkpoint with the ALTER SYSTEM CHECKPOINT command to gather the required information. |
| A53: | Answers A, C, and E are correct; each file is a member, all the members belong to a group, and the sequence number of the currently active redo log group is stored in the control file. Answer B is incorrect; the members in a group are identical. Answer D is incorrect; Oracle needs at least two groups to function. Answer F is incorrect; archived redo log files are not written to at the same time as the online redo log files. |
| A54: | Answer B is correct; in order they are tablespaces, segments, extents, and Oracle blocks. Answer A is incorrect; data files are typically bigger than extents. Answer C is incorrect; databases are bigger than tablespaces. Answer D is incorrect; databases are bigger than tablespaces. Answer E is incorrect. Data files are not logical; they are physical. |
| A55: | Answers B, C, and E are correct; the extents are released, the definition is removed from the data dictionary, and if there are PK FK relationships, CASCADE CONSTRAINTS needs to be specified. Answer A is incorrect; there do not need to be underlying tables for synonyms. Answer D is incorrect; triggers and indexes are dropped as well. |
| A56: | Answer D is correct; rolling back transactions is an example of transaction rollback. Answer A is incorrect; there is no such thing as insert recovery. Answer B is incorrect; read consistency is accomplished with rollback information but not when a statement fails. Answer C is incorrect; roll forward is transaction recovery. |
| A57: | Answer C is correct; the user did not provide the password leighlynn to connect as SYSDBA. Answer A is incorrect; angela needed SYSDBA not dba. Answer B is incorrect; SYSDBA is required not SYSOPER. Answer D is incorrect; angela is stored in the password file. |
| A58: | Answer B is correct; parameters on the server provide defaults, parameters on the client override locale-dependent variables, and alter session parameters override whatever they specify. |
| A59: | Answers B and D are correct. Oracle looks in the SPFILE and the INIT file for its initialization parameters. Answer A is a control file and provides useful information to the database and instance, but does not contain initialization parameters. Answer C is a data file, not a parameter file. |
| A60: | Answer D is correct. Columns that appear commonly in a where clause should be indexed. Answer A is incorrect; care should be taken to not index all columns updated frequently. Answer B is incorrect; often indexes are necessary even on regularly updated columns. Answer C is incorrect; all columns are queried at some point or they would be irrelevant in the database. Answer E is incorrect; data warehouses are typically highly indexed. |
