| 1: | At what point does the CKPT process write data blocks to the datafiles? [select all that apply] A. Whenever LOG_CHECKPOINT_INTERVAL parameter tells it to. B. Whenever LOG_CHECKPOINT_TIMEOUT tells it to. C. Whenever there is a log switch. D. CKPT doesn't write anything out to files; it tells the other processes to do that.
|
| 2: | What should you not do if your database creation script fails? A. Check the SQL syntax. B. Make sure that there is sufficient space in the file system, directory, or drive. C. Rerun the script immediately. D. Delete existing files.
|
| 3: | What is the p.password in the following role creation statement? CREATE ROLE accountant IDENTIFIED USING p.password;
A. The password for the role B. The procedure to authenticate the role C. The procedure making the role the default role D. None of the above
|
| 4: | Which of the following statements is not true concerning spfiles? A. You can manually edit the spfile with any text editor. B. Many of the values in the spfile can be altered dynamically during the period when the database is up. C.spfiles are binary files maintained by Oracle. D. The initialization parameters in the spfile are persistent between a database shutdown and a database startup.
|
| 5: | Which of the following is a requirement for online redefinition of a table? A. Not null B. Check constraint C. Unique constraint D. Primary key
|
| 6: | What is the first step you have to take when specifying a Database Resource Manager solution? A. Create a resource plan. B. Create plan directives. C. Create a pending area. D. Create a consumer group.
|
| 7: | Which of the following actions are permitted on an external table? [select all that apply] A. Select B. Insert C. Update D. Delete E. Create Index
|
| 8: | Which of the following statements must be true for a database to use automatic segment space management? A. In Oracle 9i, all space is automatically managed so nothing special must be done. B. The segment management clause must be specified on the CREATE TABLE command with no additional requirements necessary. C. The segment management clause is specified with the CREATE TABLESPACE command, and the tablespace has to be created as dictionary managed. D. The segment management clause is specified with the CREATE TABLESPACE command, and the tablespace has to be created as locally managed.
|
| 9: | You have decided to use password file authentication and are using the password file utility to create the file as follows: $orapwd file=$ORACLE_HOME/dbs/orapwMYDB password=mypassword entries=15
Your REMOTE_LOGIN_PASSWORDFILE initialization parameter is set to NONE, and you have created a user as follows: CREATE USER adam IDENTIFIED BY angelboy; GRANT sysdba TO adam;
Your user adam attempts to connect to the database with the following command: connect adam/angleboy as sysdba;
Why did this connection fail? A. adam was not granted DBA role. B. REMOTE_LOGON_PASSWORDFILE was set to NONE instead of EXCLUSIVE. C. The password file should have been created in a different directory. D. adam used the wrong password to connect as SYSDBA; it should have been mypassword.
|
| 10: | Amandya has created the SOCCER_STATS table. She now wants to find out the following information about the table: The size of the initial extent allocated to the SOCCER_STATS segment The current total number of extents allocated to the SOCCER_STATS segment From which data dictionary view or views would you find this information? [select all that apply] |
| 11: | Evaluate the following SQL: CREATE USER alice IDENTIFIED BY madre; GRANT CREATE ANY MATERIALIZED VIEW, CREATE ANY DIMENSION, DROP ANY DIMENSION QUERY REWRITE GLOBAL QUERY REWRITE TO myrole WITH ADMIN OPTION; GRANT myrole TO alice WITH ADMIN OPTION;
Which three actions can the user alice perform? [choose three] A. Select from a table. B. Create and drop a materialized view. C. Alter a materialized view that you created. D. Connect to the database. E. Enable the role and exercise any privileges in the role's privilege domain. F. Grant and revoke the role to and from other users.
|
| 12: | What two tools can be used for starting up and shutting down the Oracle instance and database? [select two] A. SQL*Plus B. Server Manager C. Enterprise Manager D. PL/SQL
|
| 13: | You are administering a database that currently has one control file. You decide that three control files would provide you with better protection against a single point of failure. To accomplish this, you need to modify the SPFILE so that it points to the locations of all three control files. You receive the message "system altered" after you have executed the CREATE CONTROLFILE command. You shut down the database and copy the single original control file to the new names and locations with operating system commands. On startup you receive the error ORA-00205: Error in identifying control file. You look in the alert log and determine that you inadvertently messed up the path for the control file. What steps are required to resolve the problem and get the database to start? A. 1. | Connect as SYSDBA.
| 2. | Shut down the database.
| 3. | Start the database in NOMOUNT mode.
| 4. | Use the ALTER SYSTEM SET CONTROL_FILES command to correct the error.
| 5. | Shut down the database.
| 6. | Start the database.
|
B. 1. | Connect as SYSDBA.
| 2. | Shut down the database.
| 3. | Start the database in MOUNT mode.
| 4. | Remove the SPFILE by using an OS command.
| 5. | Re-create the SPFILE from the PFILE by issuing the CREATE SPFILE FROM PFILE command.
| 6. | Use the ALTER SYSTEM SET CONTROL_FILES command to correct the error.
| 7. | Start the database.
|
C. 1. | Connect as SYSDBA.
| 2. | Shut down the database.
| 3. | Remove the control files using the OS command.
| 4. | Start the database in NOMOUNT mode.
| 5. | Remove the SPFILE by using an OS command.
| 6. | Re-create the SPFILE from the PFILE using the CREATE SPFILE FROM PFILE command.
| 7. | Use the ALTER SYSTEM SET CONTROL_FILES command to redefine the control files pointing to the locations correctly.
| 8. | Shut down the database.
| 9. | Start the database.
|
D. 1. | Connect as SYSDBA.
| 2. | Shut down the database.
| 3. | Copy the control files to the new location with OS commands.
| 4. | Start the database.
|
|
| 14: | Which type of index does this syntax create? CREATE INDEX adam.merit_badge_index ON adam.merit_badge (badge_name) TABLESPACE adam_indx;
A. Bitmap B. B-tree C. Partitioned D. Reverse key
|
| 15: | Which two actions cause a log switch? [choose two] A. The current online redo log group is filled. B. The ALTER SYSTEM SWITCH LOGFILE command is issued. C. The instance is shut down. D. A transaction completes. E. The instance is started.
|
| 16: | The users adam and amandya have left the company. Company policy states that they can no longer have any access to the database. However, both have created vital structures in the database. You need to make sure that the objects they created in the database remain available. What do you need to do? A. Revoke the CREATE SESSION privilege from the adam and amandya users. B. Drop the adam and amandya users from the database with the CASCADE option. C. Delete the adam and amandya users and revoke the CREATE SESSION privilege. D. Delete the adam and amandya users from the database by using the DROP USER command.
|
| 17: | You examine your alert log file and notice that errors are being generated from a SQL*Plus session. Which of the following files would be your first and best option for providing more information about the nature of the problem? |
| 18: | Which data dictionary view would you use from which to get a list of object privileges for all database users? A. DBA_TAB_PRIVS B. ALL_TAB_PRIVS C. USER_TAB_PRIVS D. ALL_TAB_PRIVS_MADE
|
| 19: | You need to create an index on the SOFTWARE_ID column of the SOFTWARE_INVENTORY table. The index has the following requirements: The index will be called SOFTWARE_PK. The index should be sorted in ascending order. The index should be created in the SOFTWARE_INDEX tablespace, which is a dictionary managed tablespace. All extents of the index should be 1MB in size. The index should be unique. No redo information should be generated when the index is created. There should be 20% allocated to additions to the index.
Which command creates the index and meets all the requirements? A. CREATE UNIQUE INDEX software_pk ON software_inventory (software_id) TABLESPACE software_index PCTFREE 20 STORAGE (INITIAL lm NEXT lm PCTINCREASE 0);
B. CREATE UNIQUE INDEX software_pk ON software_inventory (software_id) TABLESPACE software_index PCTFREE 20 STORAGE (INITIAL 1m NEXT 1m PCTINCREASE 0) NOLOGGING;
C. CREATE UNIQUE INDEX software_pk ON software_inventory (software_id) TABLESPACE software_index PCTUSED 80 STORAGE (INITIAL lm NEXT lm PCTINCREASE 0) NOLOGGING;
D. CREATE UNIQUE INDEX software_pk ON software_inventory (software_id) TABLESPACE software_index PCTUSED 80 STORAGE (INITIAL lm NEXT lm PCTINCREASE 0);
|
| 20: | Which statement about the shared pool is true? A. The shared pool cannot be dynamically resized in Oracle 9i. B. The shared pool contains only fixed structures. C. The shared pool consists entirely of the library cache and buffer cache. D. The shared pool stores the most recently executed SQL statements and the most recently accessed data definitions.
|
| 21: | Which type of file is part of the Oracle database? |
| 22: | User mike issues the following command: UPDATE merit_badge SET id=200 WHERE id=1
Then user steve issues this command: UPDATE merit_badge SET id=300 WHERE id=1
Steve informs you that his UPDATE statement seems to be hung. How could you resolve the current issues so that Steve can continue working? A. No action is required; Steve is just being impatient. B. Ask Steve to abort the statement. C. Ask Mike to commit the transaction. D. Ask Steve to commit the transaction.
|
| 23: | Which statement about an Oracle instance is true? A. The redo log buffer is not a part of the shared memory area of an Oracle instance. B. Multiple Oracle instances can execute on the same computer with each instance accessing its own physical database. C. An Oracle instance is a combination of memory structures, background processes, and user processes. D. In a shared server environment, the memory structure component of an instance consists of a single SGA and a single PGA.
|
| 24: | Which of the following statements are true about the use of online redo log files? [select all that apply] A. Redo log files are used only for recovery. B. Each redo log within a group is called a member. C. Redo log files are organized into a minimum of three groups. D. An Oracle database requires at least three online redo log members. E. Redo log files provide the database with a read consistency method. F. Redo log files provide the means to redo transactions in the event of an instance failure.
|
| 25: | Which data dictionary view would you use to get a listing of all database users and their default settings? A. ALL_USERS B. USERS_USERS C. DBA_USERS D. V$USERS
|
| 26: | Consider this SQL statement: UPDATE employees SET first_name = 'Junior' WHERE emp_id = 100; COMMIT;
What happens when this user issues the COMMIT in the preceding SQL statement? A. Dirty buffers in the database buffer cache are flushed. B. The server process places the commit record in the redo log buffer. C. LGWR writes the redo log buffer entries to the redo log files and data files. D. The user manually notifies the server processes that the transaction is complete. E. The user process automatically notifies the server processes that the transaction is complete.
|
| 27: | Examine the following statements: MOUNT mounts the database for certain DBA activities but does not provide user access to the database. The NOMOUNT command creates only the data buffers but does not provide access to the database. The OPEN command enables users to access the database. The STARTUP command starts an instance.
Which statements are true [select all that apply] A. 1 B. 2 C. 3 D. 4 E. None are true
|
| 28: | You are using DBCA and find that you need to change some of the custom templates that you have stored. What option from the first screen would you use to accomplish this? |
| 29: | Your database contains a locally managed uniform sized tablespace with automatic segment-space management. These tablespaces contain only tables. Currently, the uniform size defined for the tablespaces is 512KB. Because several of the tables have become very large, you determine that your configuration must change to improve performance and that now those very large tables must reside in tablespaces that are locally managed, with uniform size of 5MB and automatic segment-space management. What must you do to meet the new requirements? A. The new requirements cannot be met without re-creating the database. B. The control file needs to be re-created with the new, corrected settings. C. Use the ALTER TABLESPACE command for the current tablespaces to increase the uniform size. D. Create a new tablespace with correct settings and then move the tables in question into the new tablespace.
|
| 30: | You have two UNDO tablespaces defined for your database. The instance is currently using the undo tablespace named UNDO_1. You issue the following command to switch to the other UNDO tablespace, UNDOTBS_2. The switch occurs while there are still transactions using UNDOTBS_1: ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_2
What is the result of this statement under these conditions? [choose all that apply] A. All new transactions will be assigned to UNDOTBS_2. B. Current transactions will be switched to the UNDOTBS_2 tablespace. C. The switch to UNDOTBS_2 fails, and an error message is returned. D. The UNDOTBS_1 undo tablespace enters into a PENDING OFFLINE status while there are still active transactions accessing it. E. The switch to UNDOTBS_2 does not take place until all transactions in UNDOTBS_1 are completed. F. You can't have two UNDO tablespaces defined for your database.
|
| 31: | Extents are a logical collection of contiguous _________________. |
| 32: | You just created the following roles using the statements shown: CREATE ROLE ar_clerk; CREATE ROLE ap_clerk IDENTIFIED BY payables; CREATE ROLE gl_clerk IDENTIFIED EXTERNALLY; CREATE ROLE genuser IDENTIFIED GLOBALLY; CREATE ROLE developer IDENTIFIED USING p.pass;
Which statement indicates that a user must be authorized to use the role by the enterprise directory service before the role is enabled? A. CREATE ROLE ar_clerk; B. CREATE ROLE genuser IDENTIFIED GLOBALLY; C. CREATE ROLE ap_clerk IDENTIFIED BY payables; D. CREATE ROLE developer IDENTIFIED USING p.pass; E. CREATE ROLE gl_clerk IDENTIFIED EXTERNALLY;
|
| 33: | The accounting controller for your organization has complained that the report she runs monthly takes too long to run. You look at the query that the report runs and determine that the report would run faster if there was an index on the AP_ENTRY_MONTH column of the AP_TRANS table. The AP_TRANS table has about 5 million rows currently, and around 100 new rows are added every month. Old records are never deleted from the table. The AP_ENTRY_MONTH column is defined as a VARCHAR2(15) field. There are only 12 possible months, and they change very infrequently. Which type of index would be best for this column? A. B-tree B. Bitmap C. Reverse key D. Function-based
|
| 34: | You need to create an index on the MERIT_BADGES_EARNED table. It contains 5 million rows of data, and the key columns have low cardinality. The queries generated against this table use a combination of multiple WHERE conditions involving the OR operator. Which type of index would be best for this type of table? A. Bitmap B. Unique C. Partitioned D. Reverse key E. Single column F. Function-based
|
| 35: | When is the SGA created in an Oracle instance? A. When the database is created B. When the instance is opened C. When the user process is started D. When a server process is started
|
| 36: | Which pair of words completes the following statement? When issuing the STARTUP NOMOUNT command, the Oracle instance is _____________, and the Oracle database is ______________? |
| 37: | For a tablespace created with automatic segment-space management, where is rollback information maintained? |
| 38: | Which two conditions must exist with respect to the directories you specify in the DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n initialization parameters when using Oracle managed files? [choose two] A. The directories must already exist. B. The directories must not contain any other files. C. The directories must be created as subdirecties of the $ORACLE_HOME directory. D. The directories must have appropriate permissions that allow Oracle to create files in them.
|
| 39: | Your organization finds that more stringent user access is required, and those requirements have been issued. You need to do the following tasks for the user lonny: Change user authentication to external authentication. Revoke the user's ability to create objects in the test tablespace. Add a new default and temporary tablespace and set a quota of unlimited. Assign the user to the CLERK profile.
Which statement meets the requirements? A. ALTER USER lonny IDENTIFIED EXTERNALLY DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp01 QUOTA UNLIMITED ON users QUOTA 0 ON test_ts GRANT clerk TO lonny;
B. ALTER USER lonny IDENTIFIED by dion DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp01 QUOTA UNLIMITED ON users QUOTA 0 ON test PROFILE clerk;
C. ALTER USER lonny IDENTIFIED EXTERNALLY DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp01 QUOTA UNLIMITED ON users QUOTA 0 ON test PROFILE clerk;
D. ALTER USER lonny IDENTIFIED EXTERNALLY DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp01 QUOTA UNLIMITED ON users QUOTA 0 ON test; GRANT clerk to lonny;
|
| 40: | For which constraints will indexes automatically be created when the constraint is added? [choose all that apply] A. Check B. Unique C. Not null D. Primary key E. Foreign key
|
| 41: | You are creating a new database. You do not want users to use the SYSTEM tablespace for sorting operations. What should you do when you issue the CREATE DATABASE statement to prevent this? A. Create an undo tablespace. B. Create a default temporary tablespace. C. Create a tablespace with the UNDO keyword. D. Create a tablespace with the TEMPORARY keyword.
|
| 42: | Select the memory structure or structures that would be used to store the parse information and actual value of the bind variable ID for the following set of commands: VARIABLE id NUMBER; BEGIN :id:=1; END;
|
| 43: | Which background process performs a checkpoint in the database causing the writing of modified blocks from the database buffer cache in the SGA to the data files? A. LGWR B. SMON C. DBWn D. CKPT E. PMON
|
| 44: | A network error unexpectedly terminated a user's database session. Which `two events occur in this scenario? [choose two] A. Checkpoint occurs. B. A fast commit occurs. C. RECO performs the session recovery. D. PMON rolls back the user's current transaction. E. SMON rolls back the user's current transaction. F. SMON frees the system resources reserved for the user session. G. PMON releases the table and row locks held by the user session.
|
| 45: | A table is stored in a dictionary managed tablespace. Which two columns are required from DBA_TABLES to determine the size of the next extent when it extends? [choose two] A. BLOCKS B. PCT_FREE C. NEXT_EXTENT D. PCT_INCREASE E. INITIAL_EXTENT
|
| 46: | When is the SGA created in an Oracle database? A. When the database is created B. When the instance is started C. When the database is mounted D. When the database is opened E. When the SGA server process is started
|
| 47: | Which type of table is usually created to enable the building of highly scalable applications and is particularly useful for large tables that can be queried or manipulated using several processes concurrently? A. Regular table B. Clustered table C. Partitioned table D. Index-organized table
|
| 48: | Based on the following profile limits, if a user attempts to log in and fails after three tries, how long must the user wait before attempting to log in again? ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 60 PASSWORD_GRACE_TIME 10 PASSWORD_REUSE_TIME 3600 PASSWORD_REUSE_MAX UNLIMITED FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 1/1440 PASSWORD_VERIFY_FUNCTION verify_function;
A. 1 minute B. 5 minutes C. 10 minutes D. 14 minutes E. 18 minutes F. 60 minutes
|
| 49: | You have a database with the DB_NAME set to TEST and ORACLE_SID set to TEST. These files are in the default locations for the initialization files: init.ora initTEST.ora spfile.ora spfileTEST.ora
The database is started with this command: SQL> startup
Which initialization files does the Oracle Server attempt to read, and in which order? A. init.ora, initTEST.ora, spfileTEST.ora B. spfile.ora, spfileTEST.ora, initTEST.ora C. spfileTEST.ora, spfile.ora, initTEST.ora D. initTEST.ora, spfileTEST.ora, spfile.ora
|
| 50: | Which constraint state prevents new data that violates the constraint from being entered but allows invalid data to exist in the table? A. ENABLE NOVALIDATE B. DISABLE NOVALIDATE C. ENABLE VALIDATE D. DISABLE VALIDATE
|
| 51: | Which three statements are true about the Oracle database storage structure? [choose three] A. A data block is a logical structure. B. A single data file can belong to multiple tablespaces. C. When a segment is created, it consists of at least one extent. D. The data blocks of an extent may or may not belong to the same file. E. A tablespace can consist of multiple data files, each from a separate disk. F. Within a tablespace, a segment cannot include extents from more than one file.
|
| 52: | Which steps should you take to gather information about checkpoints? A. Set the LOG_CHECKPOINTS_TO_ALERT initialization parameter to trUE. Monitor the alert log file. B. Set the LOG_CHECKPOINT_TIMEOUT parameter. Force a checkpoint by using the FAST_START_MTTR_TARGET parameter. Then begin to monitor the alert log file. C. Set the LOG_CHECKPOINT_TIMEOUT parameter. Force a log switch by using the command ALTER SYSTEM LOGSWITCH. Force a checkpoint by using the command ALTER SYSTEM CHECKPOINT. Monitor the alert log file. D. Set the FAST_START_MTTR_TARGET parameter to trUE. Force a checkpoint by using the command ALTER SYSTEM CHECKPOINT. Monitor the alert log file.
|
| 53: | The DBA can structure an Oracle database so that it will maintain backup copies of online redo log files to avoid losing database information. Which three are true regarding the structure of online redo log files? [choose three] A. Each online redo log file in a group is called a member. B. Each member in a group has a unique log sequence number. C. A set of identical copies of online redo log files is called an online redo log group. D. The Oracle Server needs a minimum of three online redo log file groups for the normal operation of a database. E. The current log sequence number of a redo log file is stored in the control file and in the header of all data files. F. The LGWR background process concurrently writes the same information to all online and archived redo log files in a group.
|
| 54: | Which is a complete list of the logical components of the Oracle database in order of size? A. Tablespaces, segments, extents, and data files B. Tablespaces, segments, extents, and Oracle blocks C. Tablespaces, database, segments, extents, and data files D. Tablespaces, database, segments, extents, and Oracle blocks E. Tablespaces, segments, extents, data files, and Oracle blocks
|
| 55: | A table can be dropped if it is no longer needed. Which three statements are true about dropping a table? [choose three] A. All synonyms for a dropped table are automatically deleted. B. When a table is dropped, the extents used by the table are released. C. Dropping a table removes the table definition from the data dictionary. D. Indexes and triggers associated with the table are not dropped but marked INVALID. E. The CASCADE CONSTRAINTS option is necessary if the table being dropped is the parent table in a foreign key relationship.
|
| 56: | An INSERT statement failed and is rolled back. What concept does this demonstrate? A. Insert recovery B. Read consistency C. Transaction recovery D. Transaction rollback
|
| 57: | You used the password file utility to create a password file as follows: $orapwd file=$ORACLE_HOME/dbs/orapwMYDB01 password=orapass entries=50
You created a user and granted only the SYSDBA privilege to that user as follows: CREATE USER angela IDENTIFIED BY leighlynn; GRANT sysdba TO angela;
The user attempts to connect to the database as follows: connect angela/orapass as sysdba;
Why does the connection fail? A. The DBA privilege had not been granted to dba_user. B. The SYSOPER privilege had not been granted to dba_user. C. The user did not provide the password leighlynn to connect as SYSDBA. D. The information about dba_user has not been stored in the password file.
|
| 58: | There are three ways to specify National Language Support parameters: Initialization parameters Environment variables ALTER SESSION parameters
Match each of these with their appropriate definitions. A. Parameters on the client side specify locale-dependent behavior overriding the defaults set for the server. Parameters on the server side specify the default server environment. Parameters override the default set for the session or the server.
B. Parameters on the server side specify the default server environment. Parameters on the client side specify locale-dependent behavior overriding the defaults set for the server. Parameters override the default set for the session or the server.
C. Parameters on the server side specify the default server environment. Parameters override the default set for the session or the server. Parameters on the client side specify locale-dependent behavior overriding the defaults set for the server.
D. Parameters on the client side specify locale-dependent behavior overriding the defaults set for the server. Parameters override the default set for the session or the server. Parameters on the server side specify the default server environment
|
| 59: | Which of the following are valid places where initialization parameters can be found for use when the database is starting up? [select all that apply] A. control01.ctl B. initmydb1.ora C. system01.dbf D. spfilemydb1.ora
|
| 60: | Which is true when considering the number of indexes to create on a table? A. Every column that is updated requires an index. B. No column that is updated should have an index. C. Every column that is queried is a candidate for an index. D. Columns that are part of a WHERE clause are candidates for an index. E. On tables used in a data warehouse application, there should be no indexes.
|
