Lession-01: Oracle Architectural Components

1.         You decided to use multiple buffer pools in the database buffer cache of your database.

You set the sizes of the buffer pools with the DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE parameters and restarted your instance.

What else must you do to enable the use of the buffer pools?

A.   Re-create the schema objects and assign them to the appropriate buffer pool.

B.   List each object with the appropriate buffer pool initialization parameter.

C. Shut down the database to change the buffer pool assignments for each schema object.

D.   Issue the ALTER statement and specify the buffer pool in the BUFFER_POOL clause for the schema objects you want to assign to each buffer pool.

Ans: D

2.         Which statement about the shared pool is true?

A.   The shared pool CANNOT be dynamically resized.

B.   The shared pool contains only fixed structures

C.   The shared pool consists 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.

Ans: D

3.         Which type of file is part of the Oracle database?

A.   Control file

B.   Password file

C.   Parameter files

D.   Archived log files

Ans: A

4.         Extents are a logical collection of contiguous _________________.

A.   Segments

B.   Database blocks

C.   Tablespaces

D.   Operating system blocks

Ans: B

5.         Select the memory structure(s) 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;

/

A.   PGA only

B.   Row cache and PGA

C.   PGA and library cache

D.   Shared pool only

E.   Library cache and buffer cache

Ans: C

6.         Which background process performs a checkpoint in the database by writing modified blocks from the database buffer cache in the SGA to the data files?

A.   LGWR

B.   SMON

C.   DBWn

D.   CKPT

E.   PMON

Ans: C

7.         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.

Ans: D, G

8.         Which is a complete list of the logical components of the Oracle database?

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

Ans: B

9.         Which three statements about the Oracle database storage structure are true? (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.

Ans: A, C, E

10.        When is the SGA created in an Oracle database environment?

A.   When the database is created

B.   When the instance is started

C.   When the database is mounted

D.   When a user process is started

E.   When a server process is started

Ans: B

11.        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.

Monitor the alert log file.

C.   Set the LOG_CHECKPOINT_TIMEOUT parameter.

Force a log switch by using the command ALTER SYSTEM FORCE LOGSWITCH.

Force a checkpoint by using the command ALTER SYSTEM FORCE 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 FORCE CHECKPOINT.

Monitor the alert log file.

Ans: B

12.        The current password file allows for five entries. New DBAs have been hired and five more entries need to be added to the file, for a total of ten.

How can you increase the allowed number of entries in the password file?

A.   Manually edit the password file and add the new entries.

B.   Alter the current password file and resize it to be larger.

C.   Add the new entries; the password file will automatically grow.

D.   Drop the current password file, recreate it with the appropriate number of entries and add everyone again.

Ans: D

13.        Which two statements about segments are true? (Choose two.)

A.   Each table in a cluster has its own segment.

B.   Each partition in a partitioned table is a segment.

C.   All data in a table segment must be stored in one tablespace.

D.   If a table has three indexes only one segment is used for all indexes.

E.   A segment is created when an extent is created, extended, or altered.

F.   A nested table of a column within a table uses the parent table segment.

Ans: B, C

14.        Which memory structure contains the information used by the server process to validate the user privileges?

A.   Buffer cache

B.   Library cache

C.   Data dictionary cache

D.   Redo log buffer cache

Ans: C

15.        The Database Writer (DBWn) background process writes the dirty buffers from the database buffer cache into the _______.

A.   Data files only

B.   Data files and control files only

C.   Data files and redo log files only

D.   Data files, redo log files, and control files

Ans: A

16.        Which process is started when a user connects to the Oracle server in a dedicated server mode?

A.   DBWn

B.   PMON

C.   SMON

D.   Server

Ans:  D

17.        Which option lists the correct hierarchy of storage structures, from largest to the smallest?

A.   Segment, extent, tablespace, data block

B.   Data block, extent, segment, tablespace

C.   Tablespace, extent, data block, segment

D.   Tablespace, segment, extent, data block

E.   Tablespace, data block, extent, segment

Ans: D

18.        When an Oracle instance is started, background processes are started.

Background processes perform which two functions? (Choose two.)

A.   Perform I/O

B.   Lock rows that are not data dictionary rows

C.   Monitor other Oracle processes

D.   Connect users to the Oracle instance

E.   Execute SQL statements issued through an application

Ans: A, C

19.        Which three are the physical structures that constitute the Oracle database? (Choose three)

A.   Table

B.   Extent

C.   Segment

D.   Data file

E.   Log file

F.   Tablespace

G.   Control file

Ans: D, E, G

20.        An Oracle instance is executing in a nondistributed configuration. The instance fails because of an operating system failure.

Which background process would perform the instance recovery when the database is

reopened?

A.   PMON

B.   SMON

C.   RECO

D.   ARCn

E.   CKPT

Ans: B

21.        You check the alert log for your database and discover that there are many lines that say “Checkpoint Not Complete”.

What are two ways to solve this problem? (Choose two.)

A.   Delete archived log files

B.   Add more online redo log groups

C.   Increase the size of archived log files

D.   Increase the size of online redo log files

Ans: B, D

22.        The user is trying to execute a SELECT statement.

Which of the following background processes will obtain data from a disk for the user?

A.   DISPATCHER

B.   USER

C.   SERVER

D.   LGWR

E.   DBW0

Ans: C

23.        Which component of the SGA stores parsed SQL statements is used for process sharing?

A.   Buffer cache

B.   Redo log buffer

C.   Private SQL area

D.   Library cache

E.   Row cache

Ans: C

24.        Flushing dirty buffers out of the buffer cache is influenced to the greatest extent by which of the following process?

A.   ARCH

B.   SMON

C.   LGWR

D.   SERVER

Ans: C

25.        Which of the following choices identifies a database component that will be used for multiplexing control files?

A.   V$CONTROLFILE

B.   V$DATABASE

C.   init.ora

D.   DBA_FILES

Ans: C

26.        By default, checkpoints happen at least as often as _________.

A.   Redo log switches.

B.   UPDATE statements are issued against the database

C.   The SYSTEM tablespace is accessed.

D.   SMON coalesces free space in a tablespase.

Ans: A

27.        You are attempting to increase the checkpoint interval on your database. Each of the following choices will affect the duration and/or frequency of checkpoints, except one.

Which is it?

A.   Size of redo logs

B.   Number of datafiles

C.   LOG_CHECKPOINT_INTERVAL

D.   LOG_CHECKPOINT_TIMEOUT

Ans: B

28.        Records from the data dictionary information are stored in which of the following database memory areas?

A.   Library cache

B.   Row cache

C.   Session UGA

D.   Buffer cache

Ans: B

29.        A user issues a SELECT command against the Oracle database.

Which of the following choices describes a step that Oracle will execute in support of this statement?

A.   Acquire locks on table queried.

B.   Generate redo for statement.

C.   Fetch data from disk into memory.

D.   Write changes to disk.

Ans: C

30.       During regular database operation, which background process will take smaller blocks of free space in a dictionary-managed tablespace and move things around to make bigger pieces of free space?

A.   DBW0

B.   LGWR

C.   ARCH

D.   SMON

E.   PMON

Ans: D

31.        You are designing the physical database layout on your host machine.

What is the relationship between tablespaces and datafiles in the Oracle database?

A.   One tablespace has only one datafile.

B.   Many tablespaces can share one datafile.

C.   One tablespace can have many datafiles.

D.   One datafile can contain many tablespaces.

Ans: C

32.        You are analyzing how Oracle processes user statements.

SQL and PL/SQL parse information is stored in which of the following database memory areas?

A.   Library cache

B.   Row cache

C.   Dictionary cache

D.   Large area

E.   Buffer cache

Ans: B

33.        You are analyzing the components of the redo log mechanisms in your Oracle database.

Which of the following purposes does the CKPT process serve?

A.   Writes buffers to disk

B.   Writes current redo log number to datafile headers

C.   Writes redo log information to disk

D.   Reads information into memory for users

Ans: B

 

Lession-02: Getting Started with Oracle Server

1.         Which graphical DBA administration tool would you use to tune an Oracle database?

A.   SQL*Plus

B.   Oracle Enterprise Manager

C.   Oracle Universal Installer

D.   Oracle Database Configuration Assistant

Ans: B

2.         You can use the Database Configuration Assistant to create a template using an existing database structure.

Which three will be included in this template? (Choose three.)

A.   Data files

B.   Tablespaces

C.   User defined schemas

D.   User defined schema data

E.   Initialization parameters

Ans: A, B, E

3.         You have just accepted the position of DBA with a new company. One of the first things you want to do is examine the performance of the database.

Which tool will help you to do this?

A.   Recovery Manager

B.   Oracle Enterprise Manager

C.   Oracle Universal Installer

D.   Oracle Database Configuration Assistant

Ans: B

4.         You are using the Database Configuration Assistant to configure your Oracle database.

Which of the following terms pertains to the creation of an object from which creation of the other database can be based?

A.   Clone

B.   Copy

C.   Template

D.   Terminal

Ans: C

Lession-03: Managing Oracle Instance

1          User A issues this command:

UPDATE emp

SET id=200

WHERE id=1

Then user B issues this command:

UPDATE emp

SET id=300

WHERE id=1

User B informs you that the UPDATE statement seems to be hung.

How can you resolve the problem so user B can continue working?

A.   No action is required

B.   Ask user B to abort the statement

C.   Ask user A to commit the transaction

D.   Ask user B to commit the transaction

Ans: C

2          Which statement about an Oracle instance is true?

A.   The redo log buffer is NOT part of the shared memory area of an Oracle instance.

B.   Multiple instances can execute on the same computer, each 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.

Ans: B

3.         You examine the alert log file and notice that errors are being generated from a SQL*Plus session. Which files are best for providing you with more information about the nature of the problem?

A.   Control file

B.   User trace files

C.   Background trace files

D.   Initialization parameter files

Ans: B

4.         Examine these statements:

1.   MOUNT mounts the database for certain DBA activities but does not provide user

access to the database.

2.   The NOMOUNT command creates only the Data Buffer but does not provide access

to the database.

3.   The OPEN command enables users to access the database.

4.   The STARTUP command starts an instance.

Which option correctly describes whether some or all of the statements are TRUE or FALSE?

A.   2 and 3 are TRUE

B.   1 and 3 are TRUE

C.   1 is TRUE, 4 is FALSE

D.   1 is FALSE, 4 is TRUE

E.   1 is FALSE, 3 is TRUE

F.   2 is FALSE, 4 is FALSE

Ans: B

5.         You have a database with the DB_NAME set to PROD and ORACLE_SID set to PROD.

These files are in the default location for the initialization files:

init.ora

initPROD.ora

spfile.ora

spfilePROD.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, initPROD.ora, spfilePROD.ora

B.   spfile.ora, spfilePROD.ora, initPROD.ora

C.   spfilePROD.ora, spfile.ora, initPROD.ora

D.   initPROD.ora, spfilePROD.ora, spfile.ora

Ans: C

6.         The database currently has one control file. You decide that three control files will provide better protection against a single point of failure. To accomplish this, you modify the SPFILE to point to the locations of the three control files. The message “system altered” was received after execution of the statement.

You shut down the database and copy the control file to the new names and locations. On startup you receive the error ORA-00205: error in identifying control file. You look in the alert log and determine that you specified the incorrect path for the for control file.

Which steps are required to resolve the problem and start the database?

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 a UNIX command.

5.   Recreate the SPFILE from the PFILE.

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.

7.   Use the ALTER SYSTEM SET CONTROL_FILES command to define the control

files.

8.   Shut down the database.

9.   Start the database.

Ans: A

7.         You just issued the STARTUP command.

Which file is checked to determine the state of the database?

A.   The control file

B.   The first member of redo log file group 1

C.   The data file belonging to the SYSTEM tablespace

D.   The most recently created archived redo log file

Ans: A

8.         You need to shut down your database. You want all of the users who are connected to be able to complete any current transactions.

Which shutdown mode should you specify in the SHUTDOWN command?

A.   ABORT

B.   NORMAL

C.   IMMEDIATE

D.   TRANSACTIONAL

Ans: D

9.         When preparing to create a database, you should be sure that you have sufficient disk space for your database files. When calculating the space requirements you need to consider that some of the files may be multiplexed.

Which two types of files should you plan to multiplex? (Choose two.)

A.   Data files

B.   Control file

C.   Password file

D.   Online redo log files

E.   Initialization parameter file

Ans: B, D

10.        The server parameter file (SPFILE) provides which three advantages when managing initialization parameters? (Choose three.)

A.   The Oracle server maintains the server parameter file.

B.   The server parameter file is created automatically when the instance is started.

C.   Changes can be made in memory and/or in the SPFILE with the ALTER SYSTEM

command.

D.   The use of SPFILE provides the ability to make changes persistent across shut down and start up.

E.   The Oracle server keeps the server parameter file and the text initialization parameter file synchronized.

Ans: B, C, D

11.        The database needs to be shut down for hardware maintenance. All users sessions except one have either voluntarily logged off or have been forcibly killed. The one remaining user session is running a business critical data manipulation language (DML) statement and it must complete prior to shutting down the database.

Which shutdown statement prevents new user connections, logs off the remaining user, and shuts down the database after the DML statement completes?

A.   SHUTDOWN

B.   SHUTDOWN ABORT

C.   SHUTDOWN NORMAL

D.   SHUTDOWN IMMEDIATE

E.   SHUTDOWN TRANSACTIONAL

Ans: E

12.        You are managing the Oracle database.

Which of the following choices correctly identifies when Oracle reads the contents of the init.ora file?

A.   When the instance is started

B.   When the database is mounted

C.   When the database is opened

D.   When the database is closed

Ans: A

13.        You issue this command:

STARTUP MOUNT

Which three events occur when the instance is started and the database is mounted?

(Choose three)

A.   The SGA is allocated.

B.   The control file is opened.

C.   The background process is started.

D.   The existence of the datafile is verified.

E.   The existence of the online redo log file is verified.

Ans: A, B, C

14.        Which four do you find in the alert log file? (Choose four)

A.   An entry for creation for a user.

B.   An entry for creation of a table.

C.   An entry for creation of a tablespace.

D.   An entry for the startup of the instance.

E.   An entry indicating a log switch has occurred.

F.   A list of the values of an undefault initialization parameter at the time the instance

starts.

Ans: C, D, E, F

 

Lession-04: Creating Database

1. Which privilege is required to create a database?

A. DBA
B. SYSDBA
C. SYSOPER
D. RESOURCE
Ans: B

2. You are logged on to a client. You do not have a secure connection from your client to the host where your Oracle database is running.

Which authentication mechanism allows you to connect to the database using the SYSDBA privilege?

A. Control file authentication
B. Password file authentication
C. Data dictionary authentication
D. Operating system authentication
Ans: B

3. Which two environment variables should be set before creating a database? (Choose two)

A. DB_NAME
B. ORACLE_SID
C. ORACLE_HOME
D. SERVICE_NAME
E. INSTANCE_NAME

Ans: B, C

4. Which method is correct for starting an instance to create a database?

A. STARTUP
B. STARTUP OPEN
C. STARTUP MOUNT
D. STARTUP NOMOUNT
Ans: D

5. You are going to create a new database. You will NOT use operating system authentication.

Which two files do you need to create before creating the database? (Choose two)

A. Control file
B. Password file
C. Redo log file
D. Alert log file
E. Initialization parameter file
Ans: B, E

 

6. You are in the planning stages of creating a database.

How should you plan to influence the size of the control file?

A. Specify size by setting the CONTROL_FILES initialization parameter instead of using
the Oracle default value.
B. Use the CREATE CONTROLFILE command to create the control file and define a
specific size for the control file.
C. Define the MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY,
MAXDATAFILES, MAXINSTANCES parameters in the CREATE DATABASE
command.
D. Define specific values for the MAXLOGFILES, MAXLOGGROUPS,
MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES parameters within the
initialization parameter file.
Ans: C

7. Your company hired Joe, a DBA who will be working from home. Joe needs to have the ability to start the database remotely.

You created a password file for your database and set

REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE in the parameter file.

Which command adds Joe to the password file, allowing him remote DBA access?

A. GRANT DBA TO JOE;
B. GRANT SYSDBA TO JOE;
C. GRANT RESOURCE TO JOE;
D. orapwd file=orapwdPROD user=JOE password=DBA
Ans: B
Lession-05: Using Data Dictionary and Dynamic Performance Views

1. John has created a procedure named SALARY_CALC.

Which SQL query allows him to view the text of the procedure?

A. SELECT text FROM user_source
WHERE name =’SALARY_CALC’;
B. SELECT * FROM user_source
WHERE source_name =’salary_calc’;
C. SELECT * FROM user_objects
WHERE object_name = ‘SALARY_CALC’;
D. SELECT * FROM user procedures
WHERE object_name =’SALARY_CALC’;
E. SELECT text FROM user_source
WHERE name=’SALARY_CALC’
AND owner =’JOHN’;

2. Which two are true about the data dictionary views with prefix USER_? (Choose two)

A. The column OWNER is implied to be the current user.
B. A user needs the SELECT ANY TABLE system privilege to query these views.
C. The definitions of these views are stored in the user’s default tablespace.
D. These views return information about all objects to which the user has access.
E. Users can issue an INSERT statement on these views to change the value in the
underlying base tables.
F. An user who has the CREATE PUBLIC SYNONYM system privilege can create public synonyms for these views.
Ans: A, F

3. Which view provides the names of all the data dictionary views?

A. DBA_NAMES
B. DBA_TABLES
C. DICTIONARY
D. DBA_DICTIONARY
Ans: C

4. You are about to create your Oracle data dictionary for use with the database.

Which of the following users would you connect to the database as for this purpose in Oracle9i and later releases?

A. SYSTEM
B. OUTLN
C. INTERNAL
D. SYS
Ans: D

5. You are identifying dictionary objects in the Oracle database.

Which of the following is a view in the data dictionary?

A. V$DATABASE
B. DBA_TABLES
C. SYS.AUD$
D. EMP
Ans: B/

Lession-06: Maintaining the Control Files

1. You are going to re-create your database and want to reuse all of your existing database files.

You issue the following SQL statement:

CREATE DATABASE sampledb
DATAFILE
‘/u01/oradata/sampledb/system0l.dbf’
SIZE 100M REUSE
LOGFILE
GROUP 1 (‘/u01/oradata/sampledb/logla.rdo’,
‘/u02/oradata/sampledb/loglb.rdo’)
SIZE 50K REUSE,
GROUP 2 (‘/u01/oradata/sampledb/log2a.rdo’,
‘/u02/oradata/sampledb/log2b.rdo’)
SIZE 50K REUSE
MAXLOGFILES 5
MAXLOGHISTORY 100
MAXDATAFILES 10;

Why does the CREATE DATABASE statement fail?

A. You have set MAXLOGFILES too low.
B. You omitted the CONTROLFILE REUSE clause.
C. You cannot reuse the online redo log files.
D. You cannot reuse the data file belonging to the SYSTEM tablespace.
Ans: B

2. Which statement should you use to obtain information about the number, names, status, and location of the control files?

A. SELECT name, status FROM v$parameter;
B. SELECT name, status FROM v$controlfile;
C. SELECT name, status, location FROM v$control_files;
D. SELECT status, location FROM v$parameter WHERE parameter=control_files;
Ans: B

3. You need to know how many data files were specified as the maximum for the database when it was created. You did not create the database and do not have the script used to create the database.

How could you find this information?

A. Query the DBA_DATA_FILES data dictionary view.
B. Query the V$DATAFILE dynamic performance view.
C. Issue the SHOW PARAMETER CONTROL_FILES command.
D. Query the V$CONTROLFILE_RECORD_SECTION dynamic performance view.
Ans: D

4. Which two statements are true about the control file? (Choose two.)

A. The control file can be multiplexed up to eight times.
B. The control file is opened and read at the NOMOUNT stage of startup.
C. The control file is a text file that defines the current state of the physical database.
D. The control file maintains the integrity of the database, therefore loss of the control file requires database recovery.
Ans: A, D

5. You should back up the control file when which two commands are executed? (Choose two)

A. CREATE USER
B. CREATE TABLE
C. CREATE INDEX
D. CREATE TABLESPACE
E. ALTER TABLESPACE <tablespace name> ADD DATAFILE
Ans: D, E

6. You are experiencing intermittent hardware problems with the disk drive on which your control file is located. You decide to multiplex your control file.

While your database is open, you perform these steps:

1. Make a copy of your control file using an operating system command.
2. Add the new file name to the list of files for the CONTROL FILES parameter in
your text initialization parameter file using an editor.
3. Shut down the instance.
4. Issue the STARTUP command to restart the instance, mount, and open the
database.

The instance starts, but the database mount fails. Why?

A. You copied the control file before shutting down the instance.
B. You used an operating system command to copy the control file.
C. The Oracle server does not know the name of the new control file.
D. You added the new control file name to the CONTROL_FILES parameter before
shutting down the instance.
Ans: A

7. The control file defines the current state of the physical database.

Which three dynamic performance views obtain information from the control file? (Choose three.)

A. V$LOG
B. V$SGA
C. V$THREAD
D. V$VERSION
E. V$DATAFILE
F. V$PARAMETER
Ans: A, C, E

8. You are adding redo logs to the Oracle database.

Creating a new redo log adds information to which of the following Oracle resources?

A. Shared pool
B. Control file
C. SGA
D. PGA
Ans: B

9. If you wanted to find the name and location of your control files, you could find that information in each of the following locations except one.

Which is it?

A. V_CONTROLFILE_RECORD_SECTION
B. V_CONTROLFILE
C. V$PARAMETER
D. init.ora file
Ans: A

10. You are implementing control file multiplexing.

Which of the following choices identifies the method you can use in order to generate the control file copies that Oracle will maintain?

A. Issue ALTER DATABASE BACKUP CONTROLFILE TO filename.
B. Make a copy of the control file with the database shut down.
C. Issue ALTER DATABASE BACKUP CONTROLFILE TO trace.
D. Make a copy of the control file with the database still running.
Ans: B

11. In a situation where no multiplexing of redo logs takes place, what happens when Oracle cannot read data from the online redo log group for archiving?

A. Nothing happens.
B. Oracle will automatically switch redo logs when detected.
C. Oracle eventually won’t allow new records to be added to the database.
D. The instance crashes.
Ans: C

Lession-07: Maintaining Online Redo Log Files

1 You issue the following queries to obtain information about the redo log files:

SQL> SELECT group#, type, member FROM v$logfile;

GROUP# TYPE MEMBER
—— ——- ————————————–
1 ONLINE /databases/DB01/ORADATA/u02/log1a.rdo
1 ONLINE /databases/DB01/ORADATA/u03/log1b.rdo
2 ONLINE /databases/DB01/ORADATA/u02/log2a.rdo
2 ONLINE /databases/DB01/ORADATA/u03/log2b.rdo
3 ONLINE /databases/DB01/ORADATA/u02/log3a.rdo
3 ONLINE /databases/DB01/ORADATA/u03/log3b.rdo

SQL> SELECT group#, sequence#, status FROM v$log;

GROUP# SEQUENCE# STATUS
—— ——— ———
1 250 INACTIVE
2 251 CURRENT
3 249 INACTIVE

You immediately issue this command:

ALTER DATABASE DROP LOGFILE MEMBER
‘/databases/DB01/ORADATA/u03/log2b.rdo’;

Why does the command fail?

A. Each online redo log file group must have two members.
B. You cannot delete any members of online redo log file groups.
C. You cannot delete any members of the current online redo log file group
D. You must delete the online redo log file in the operating system before issuing the ALTER DATABASE command.
Ans: C

2. Which three statements are true about the use of online redo log files? (Choose three)

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.
Ans: A, B, F

3. Which two actions cause a log switch? (Choose two.)

A. A transaction completes.
B. The instance is started.
C. The instance is shut down
D. The current online redo log group is filled
E. The ALTER SYSTEM SWITCH LOGFILE command is issued.
Ans: D, E

4. Consider this SQL statement:

UPDATE employees SET first_name = ‘John’
WHERE emp_id = 1009;
COMMIT;

What happens when a user issues the COMMIT in the above 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. Log Writer (LGWR) writes the redo log buffer entries to the redo log files and data files.
D. The user process notifies the server process that the transaction is complete.
E. The user process notifies the server process that the resource locks can be released.
Ans: B

5. The DBA can structure an Oracle database to maintain 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.
Ans: A, C, E

6. Which initialization parameter determines the location of the alert log file?

A. USER_DUMP_DEST
B. DB_CREATE_FILE_DEST
C. BACKGROUND_DUMP_DEST
D. DB_CREATE_ONLINE_LOG_DEST_n
Ans: C

7. You started your database with this command:

STARTUP PFILE=initSAMPLEDB.ora

One of the values in the initSAMPLEDB.ora parameter file is:

LOG_ARCHIVE_START=false

While your database is open, you issue this command to start the Archiver process:

ALTER SYSTEM ARCHIVE LOG START;

You shut down your database to take a back up and restart it using the
initSAMPLEDB.ora parameter file again. When you check the status of the Archiver, you
find that it is disabled.

Why is the Archiver disabled?

A. When you take a backup the Archiver process is disabled.
B. The Archiver can only be started by issuing the ALTER DATABASE ARCHIVELOG
command.
C. LOG_ARCHIVE_START is still set to FALSE because the PFILE is not updated when you issue the ALTER SYSTEM command.
D. The Archiver can only be started by issuing the ALTER SYSTEM ARCHIVE LOG
START command each time you open the database.
Ans: C

8. What provides for recovery of data that has not been written to the data files prior to a failure?

A. Redo log
B. Undo segment
C. Rollback segment
D. System tablespace
Ans: A

9. During a checkpoint in an Oracle9i database, a number of dirty database buffers covered by the log being check pointed are written to the data files by DBWn.

Which parameter determines the number of buffers being written by DBWn?

A. LOG_CHECKPOINT_TARGET
B. FAST_START_MTTR_TARGET
C. LOG_CHECKPOINT_IO_TARGET
D. FAST_START_CHECKPOINT_TARGET
Ans: B

10. Which steps should you follow to increase the size of the online redo log groups?

A. Use the ALTER DATABASE RESIZE LOGFILE GROUP command for each group to
be resized.
B. Use the ALTER DATABASE RESIZE LOGFILE MEMBER command for each member within the group being resized.
C. Add new redo log groups using the ALTER DATABASE ADD LOGFILE GROUP
command with the new size. Drop the old redo log files using the ALTER DATABASE DROP LOGFILE GROUP command.
D. Use the ALTER DATBASE RESIZE LOGFILE GROUP command for each group to be resized. Use the ALTER DATABASE RESIZE LOGFILE MEMBER command for each member within the group.
Ans: C

11. As a DBA, one of your tasks is to periodically monitor the alert log file and the background trace files. In doing so, you notice repeated messages indicating that Log Writer (LGWR) frequently has to wait for a redo log group because a checkpoint has not completed or a redo log group has not been archived.

What should you do to eliminate the wait LGWR frequently encounters?

A. Increase the number of redo log groups to guarantee that the groups are always available to LGWR.
B. Increase the size of the log buffer to guarantee that LGWR always has information to
write.
C. Decrease the size of the redo buffer cache to guarantee that LGWR always has
information to write.
D. Decrease the number of redo log groups to guarantee that checkpoints are completed prior to LGWR writing.
Ans: A

12. Your database is in ARCHIVELOG mode

Which two must be true before the Log Writer (LGWR) can reuse a filled online redo log
file? (Choose two).

A. The redo log file must be archived.
B. All of the data files must be backed up.
C. All transactions with entries in the redo log file must complete.
D. The data files belonging to the SYSTEM tablespace must be backed up.
E. The changes recorded in the redo log file must be written to the data files.
Ans: A, E

13. In which two situations does the Log Writer (LGWR) process write the redo entries from the redo log buffer to the current online redo log group? (Choose two.)

A. When a transaction commits
B. When a rollback is executed
C. When the redo log buffer is about to become completely full (90%)
D. Before the DBWn writes modified blocks in the database buffer cache to the data files
E. When there is more than a third of a megabyte of changed records in the redo log buffer
Ans: A, D

14. You are attempting to clear an unarchived redo log file.

In order to manually enact a log switch, which of the following statements is appropriate?

A. ALTER DATABASE
B. ALTER SYSTEM
C. ALTER USER
D. ALTER REDO LOG
Ans: B

15. You have implemented OMF for redo log management.

Which of the following choices reflects a log filename that might be employed when OMF is enabled?

A. log01.log
B. logORCL01.log
C. 1_2.logALTER ROLLBACK SEGMENT
D. ora_1_asdf1234.log
Ans: D

16. You are creating a data base manually and you need to limit the number of initial online redo log groups and members.

Which two keywords should you use within the create database command to define the maximum number of online redo log files? (Choose two).

A. MAXLOGMEMBERS, which determines the maximum number of members per
group.
B. MAXREDOLOGS, which specifies the maximum number of online redo log files.
C. MAXLOGFILES, which determines the absolute maximum of online redo log
groups.
D. MAXLOGGROUPS, which specifies the maximum number of online redo log files,
groups and members.
Ans: A, C
Lession-08: Managing Tablespaces and Data Files

1. Evaluate the SQL statement:

CREATE TABLESPACE hr_tbs
DATAFILE ‘/usr/oracle9i/OraHomel/hr_data.dbf’ SIZE 2M
AUTOEXTEND ON
MINIMUM EXTENT 4K
NOLOGGING
DEFAULT STORAGE (INITIAL 5K NEXT 5K PCTINCREASE 50)
EXTENT MANAGEMENT DICTIONARY
SEGMENT SPACE MANAGEMENT AUTO;

Why does the statement return an error?

A. The value of PCTINCREASE is too high.
B. The size of the data file is too small.
C. You cannot specify default storage for dictionary managed tablespaces.
D. Segment storage management cannot be set to auto for a dictionary managed tablespace.
E. You cannot specify default storage for a tablespace that consists of an autoextensible data file.
F. The value specified for INITIAL and NEXT storage parameters should be a multiple of the value specified for MINIMUM EXTENT.
Ans: D

2. Examine the tablespace requirements for a new database.

Tablespace Purpose Size
APP_DATA Application Data 1 Gig
APP_NDX Application Index 500M
SYSTEM System Data 300M
TEMP Temporary Data 100M
UNDOTBS Undo Data 150M
USERS User Data 100M

Which three tablespaces can be created in the CREATE DATABASE statement? (Choose three.)

A. TEMP
B. USERS
C. SYSTEM
D. APP_NDX
E. UNDOTBS
F. APP_DATA
Ans: A, C, E

3. You decide to use Oracle Managed Files in your database.

Which two are requirements with respect to the directories you specify in the
DB_CREATE_FILE_DEST and
DB_CREATE_ONLINE_LOG_DEST_n initialization parameters? (Choose two).

A. The directory must already exist.
B. The directory must not contain any other files.
C. The directory must be created in the $ORACLE_HOME directory.
D. The directory must have appropriate permissions that allow Oracle to create files in it.
Ans: A, D

4. 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.
Ans: B

5. What determines the initial size of a tablespace?

A. The INITIAL clause of the CREATE TABLESPACE statement
B. The MINEXTENTS clause of the CREATE TABLESPACE statement
C. The MINIMUM EXTENT clause of the CREATE TABLESPACE statement
D. The sum of the INITIAL and NEXT clauses of the CREATE TABLESPACE statement
E. The sum of the sizes of all data files specified in the CREATE TABLESPACE statement
Ans: E

6. A table is stored in a data dictionary managed tablespace.

Which two columns are required from DBA_TABLES to determine the size of the extent?
When it extends? (Choose two.)

A. BLOCKS
B. PCT_FREE
C. NEXT_EXTENT
D. PCT_INCREASE
E. INITIAL_EXTENT
Ans: C, D

7. Evaluate the SQL command:

CREATE TEMPORARY TABLESPACE temp_tbs
TEMPFILE ‘/usr/oracle9i/OraHomel/temp_data.dbf’
SIZE 2M
AUTOEXTEND ON;

Which two statements are true about the TEMP_TBS tablespace? (Choose two.)

A. TEMP_TBS has locally managed extents.
B. TEMP_TBS has dictionary managed extents.
C. You can rename the tempfile temp_data.dbf.
D. You can add a tempfile to the TEMP_TBS tablespace.
E. You can explicitly create objects in the TEMP_TBS tablespace.
Ans: B, D

8. Which data dictionary view shows the available free space in a certain tablespace?

A. DBA_EXTENTS
B. V$FREESPACE
C. DBA_FREE_SPACE
D. DBA_TABLESPACFS
E. DBA_FREE_EXTENTS
Ans: C

9. SALES_DATA is a nontemporary tablespace. You have set the SALES_DATA tablespace OFFLINE by issuing this command:

ALTER TABLESPACE sales_data OFFLINE NORMAL;

Which three statements are true? (Choose three.)

A. You cannot drop the SALES_DATA tablespace.
B. The SALES_DATA tablespace does not require recovery to come back online.
C. You can read the data from the SALES_DATA tablespace, but you cannot perform any write operation on the data.
D. When the tablespace SALES_DATA goes offline and comes back online, the event will be recorded in the data dictionary.
E. When the tablespace SALES_DATA goes offline and comes back online, the event will be recorded in the control file.
F. When you shut down the database the SALES_DATA tablespace remains offline, and is checked when the database is subsequently mounted and reopened.
Ans: B, D, E

10. Examine the SQL statement:

CREATE TABLESPACE user_data
DATAFILE ‘/u01/oradata/user_data_0l.dbf’ SIZE 100M
LOCALLY MANAGED UNIFORM SIZE 1M
AUTOMATIC SEGMENT SPACE MANAGEMENT;

Which part of the tablespace will be of a uniform size of 1 MB?

A. Extent
B. Segment
C. Oracle block
D. Operating system block
Ans: A

11. A user calls and informs you that a ‘failure to extend tablespace’ error was received while inserting into a table. The tablespace is locally managed.

Which three solutions can resolve this problem? (Choose three.)

A. Add a data file to the tablespace
B. Change the default storage clause for the tablespace
C. Alter a data file belonging to the tablespace to autoextend
D. Resize a data file belonging to the tablespace to be larger
E. Alter the next extent size to be smaller, to fit into the available space
Ans: A, C, D

12. You created a tablespace SH_TBS. The tablespace consists of two data files:

sh_tbs_datal.dbf and sh_tbs_data2.dbf. You created a nonpartitioned table SALES_DET in the SH_TBS tablespace.

Which two statements are true? (Choose two.)

A. The data segment is created as soon as the table is created.
B. The data segment is created when the first row in the table is inserted.
C. You can specify the name of the data file where the data segment should be stored.
D. The header block of the data segment contains a directory of the extents in the segment.
Ans: A, D

13. Examine the list of steps to rename the data file of a non-SYSTEM tablespace HR_TBS.

The steps are arranged in random order.

1. Shut down the database.
2. Bring the HR_TBS tablespace online.
3. Execute the ALTER DATABASE RENAME DATAFILE command
4. Use the operating system command to move or copy the file
5. Bring the tablespace offline.
6. Open the database.

What is the correct order for the steps?

A. 1, 3, 4, 6; steps 2 and 5 are not required
B. 1, 4, 3, 6; steps 2 and 5 are not required
C. 2, 3, 4, 5; steps 1 and 6 are not required
D. 5, 4, 3, 2; steps 1 and 6 are not required
E. 5, 3, 4, 1, 6, 2
F. 5, 4, 3, 1, 6, 2
Ans: D

14. You decided to use Oracle Managed Files (OMF) for the control files in your database.

Which initialization parameter do you need to set to specify the default location for control
files if you want to multiplex the files in different directories?

A. DB_FILES
B. DB_CREATE_FILE_DEST
C. DB_FILE_NAME_CONVERT
D. DB_CREATE_ONLINE_LOG_DEST_n

Ans: D

15. Temporary tablespaces should be locally managed and the uniform size should be a multiple of the ________.

A. DB_BLOCK_SIZE
B. DB_CACHE_SIZE
C. SORT_AREA_SIZE
D. Operating system block size
Ans: C

16. You are trying to determine how many disk sorts are happening on the database right now.

Which of the following dictionary tables would you use to find that information?

A. V$SESSION
B. V$SYSSTAT
C. DBA_SEGMENTS
D. V$SORT_USAGE
Ans : D

17. You issue the following command in Oracle: CREATE TABLESPACE DAT_TBS DATAFILE .dat_tbs.dbf. SIZE 2M; Later queries against the database reveal that the tablespace is located in the /u01/oradata/oracle directory.

Which of the following choices identifies how Oracle likely determined what directory to place dat_tbs.dbf file in?

A. DB_CREATE_FILE_DEST
B. DB_CREATE_ONLINE_LOG_1
C. DB_CREATE_ONLINE_LOG_2
D. The directory is an operating system-specific default value in Oracle that can neigher
be specified manually nor changed.
Ans: A

18. You are creating tablespaces in Oracle.

Which of the following keywords or clauses permits the datafiles of a database to grow automatically in order to accommodate data growth?

A. DEFAULT STORAGE
B. EXTENT MANAGEMENT
C. AUTOEXTEND
D. DATAFILE
Ans: C

19. You need to make one of the data file of the PROD_TBS tablespace auto extensible.

You issue this SQL command:

ALTER TABLESPACE prod_tbs DATAFILE
‘/uo1/private/oradata/prod.dbf’
AUTOEXTEND ON;

Which error occurs?

A. ORA 02789 Max number of files reached.
B. ORA 03280 Invalid DATAFILE filename specified.
C. ORA 03283 SPECIFIED DATAFILE STRING does not exist.
D. ORA 02142 Missing or invalid ALTER TABLESPACE option.
E. ORA 01516 Non existent log file, data file or tempfile ‘string’.
F. ORA 03244 No free space found to place the control information.
G. ORA 00238 Operation would reuse a filename that is part of the database.
Ans: D

20. You need to determine the amount of space currently used in each tablespace.

You can retrieve this information in a single SQL statement using only one DBA view in the FROM clause providing you use either the _______ or _______ DBA view.

A. DBA_EXTENTS.
B. DBA_SEGMENTS.
C. DBA_DATA_FILES.
D. DBA_TABLESPACES.
Ans: A, C
Lession-09: Storage Structure and Relationships

1. Which type of table is usually created to enable the building of scalable applications, and is 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
Ans: C

2. Which storage structure provides a way to physically store rows from more than one table in the same data block?

A. Cluster table
B. Partitioned table
C. Unclustered table
D. Index-organized table
Ans: A

3. You need to determine the location of all the tables and indexes owned by one user.

In which DBA view would you look?

A. DBA_TABLES
B. DBA_INDEXES
C. DBA_SEGMENTS
D. DBA_TABLESPACES
Ans: C

4. Which are considered types of segments?

A. Only LOBS
B. Only nested tables
C. Only index-organized tables
D. Only LOBS and index-organized tables
E. Only nested tables and index-organized tables
F. Only LOBS, nested tables, and index-organized tables
G. Nested tables, LOBS, index-organized tables, and boot straps
Ans: G

5. Which type of segment is used to improve the performance of a query?

A. Index
B. Table
C. Temporary
D. Boot strap
Ans: A

6. Your application regularly issues the following statement:

SELECT * FROM BANK_ACCT
WHERE ACCT_BALANCE BETWEEN 1000 and 10000;

Which of the following database objects would be inappropriate for use with this
statement?

A. Materialized views
B. Indexes
C. Index-organized tables
D. Hash clusters
Ans: D

Lession-10: Managing Undo Data

1. The user Smith created the SALES HISTORY table. Smith wants to find out the following information about the SALES HISTORY table:

The size of the initial extent allocated to the sales history data segment
The total number of extents allocated to the sales history data segment

Which data dictionary view(s) should Smith query for the required information?

A. USER_EXTENTS
B. USER_SEGMENTS
C. USER_OBJECT_SIZE
D. USER_OBJECT_SIZE and USER_EXTENTS
E. USER_OBJECT_SIZE and USER_SEGMENTS
Ans: B

2. You have two undo tablespaces defined for your database. The instance is currently using the undo tablespace named UNDOTBS_1. You issue this command to switch to UNDOTBS 2 while there are still transactions using UNDOTBS_1:

ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_2

Which two results occur? (Choose two.)

A. New transactions are assigned to UNDOTBS_2.
B. Current transactions are 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 mode (status).
E. The switch to UNDOTBS_2 does not take place until all transactions in UNDOTBS_1
are completed.
Ans: A, D

3. For a tablespace created with automatic segment-space management, where is free space managed?

A. In the extent
B. In the control file
C. In the data dictionary
D. In the undo tablespace
Ans: D

4. An INSERT statement failed and is rolled back. What does this demonstrate?

A. Insert recovery
B. Read consistency
C. Transaction recovery
D. Transaction rollback
Ans: D

5. You want to limit the number of transactions that can simultaneously make changes to data in a block, and increase the frequency with which Oracle returns a block back on the free list.

Which parameters should you set?

A. INITRANS and PCTUSED
B. MAXTRANS and PCTFREE
C. INITRANS and PCTFREE
D. MAXTRANS and PCTUSED
Ans: D

6. Which structure provides for statement-level read consistency?

A. Undo segments
B. Redo log files
C. Data dictionary tables
D. Archived redo log files
Ans: A

7. You omit the UNDO tablespace clause in your CREATE DATABASE statement. The UNDO_MANAGEMENT parameter is set to AUTO.

What is the result of your CREATE DATABASE statement?

A. The Oracle server creates no undo tablespaces.
B. The Oracle server creates an undo segment in the SYSTEM tablespace.
C. The Oracle server creates one undo tablespace with the name SYS_UNDOTBS.
D. Database creation fails because you did not specify an undo tablespace on the CREATE DATABASE statement.
Ans: C

8. Oracle guarantees read-consistency for queries against tables. What provides read consistency?

A. Redo logs
B. Control file
C. Undo segments
D. Data dictionary
Ans: C

9 When determining the number of UNDO segments in a database, which of the following choices identifies a factor to consider?

A. Size of typical transactions
B. Concurrent transactions
C. Size of rows in table most frequently changed
D. Number of anticipated disk sorts
Ans: B

10. When an UNDO segment is created manually by you, its availability status is set to which of the following automatically by Oracle?

A. Online
B. Pending online
C. Offline
D. Stale
Ans: C

11. You have a long-running process you want to assign to a specific UNDO segment brought online for that express purpose. You are not using automatic UNDO management.

What statement can be used for this task?

A. ALTER DATABASE
B. SET TRANSACTION
C. ALTER ROLLBACK SEGMENT
D. ALTER TABLE
Ans: B

12. Which of the following operations does not require Oracle to store information in an UNDO segments as part of the transaction?

A. INSERT
B. SELECT
C. UPDATE
D. DELETE
Ans: B

Lession-11: Managing Tables

1. You are in the process of dropping the BUILDING_LOCATION column from the HR.EMPLOYEES table. The table has been marked INVALID until the operation completes. Suddenly the instance fails. Upon startup, the table remains INVALID.

Which step(s) should you follow to complete the operation?

A. Continue with the drop column command:
ALTER TABLE hr.employees DROP COLUMNS CONTINUE;
B. Truncate the INVALID column to delete remaining rows in the column and release
unused space immediately.
C. Use the Export and Import utilities to remove the remainder of the column from the table and release unused space.
D. Mark the column as UNUSED and drop the column:
ALTER TABLE hr.employees
SET UNUSED COLUMN building location;
ALTER TABLE hr.employees
DPOP UNUSED COLUMN building_location
CASCADE CONSTRAINTS;
Ans: A

2. Your database contains a locally managed uniform sized tablespace with automatic segment-space management, which contains only tables. Currently, the uniform size for the tablespace is 512 K.

Because the tables have become so large, your configuration must change to improve performance. Now the tables must reside in a tablespace that is locally managed, with uniform size of 5 MB and automatic segment-space management.

What must you do to meet the new requirements?

A. The new requirements cannot be met.
B. Re-create the control file with the correct settings.
C. Use the ALTER TABLESPACE command to increase the uniform size.
D. Create a new tablespace with correct settings then move the tables into the new
tablespace.
Ans: D

3. A table can be dropped if it is no longer needed, or if it will be reorganized.

Which three statements are true about dropping a table? (Choose three.)

A. All synonyms for a dropped table are 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.
Ans: B, C, E

4. You need to drop two columns from a table.

Which sequence of SQL statements should be used to drop the columns and limit the number of times the rows are updated?

A. ALTER TABLE employees
DROP COLUMN comments
DROP COLUMN email;
B. ALTER TABLE employees
DROP COLUMN comments;
ALTER TABLE employees
DROP COLUMN email;
C. ALTER TABLE employees
SET UNUSED COLUMN comments;
ALTER TABLE employees
DROP UNUSED COLUMNS;
ALTER TABLE employees
SET UNUSED COLUMN email;
ALTER TABLE employees
DROP UNUSED COLUMNS;
D. ALTER TABLE employees
SET UNUSED COLUMN comments;
ALTER TABLE employees
SET UNUSED COLUMN email;
ALTER TABLE employees
DROP UNUSED COLUMNS;
Ans: D

5. Which table type should you use to provide fast key-based access to table data for queries involving exact matches and range searches?

A. Regular table
B. Clustered table
C. Partitioned table
D. Index-organized table
Ans: D

6. Examine this TRUNCATE TABLE command:

TRUNCATE TABLE departments;

Which four are true about the command? (Choose four.)

A. All extents are released.
B. All rows of the table are deleted.
C. Any associated indexes are truncated.
D. No undo data is generated for the table’s rows.
E. It reduces the number of extents allocated to the DEPARTMENTS table to the original setting for MINEXTENTS.
Ans: B, C, D, E

7. ABC Company consolidated into one office building, so the very large EMPLOYEES table no longer requires the OFFICE_LOCATION column. The DBA decided to drop the column using the syntax below:

ALTER TABLE hr.employees
DROP COLUMN building_location
CASCADE CONSTRAINTS;

Dropping this column has turned out to be very time consuming and is requiring a large
amount of undo space.

What could the DBA have done to minimize the problem regarding time and undo space
consumption?

A. Use the Export and Import utilities to bypass undo.
B. Mark the column as UNUSED.
C. Remove the column at a later time when less activity is on the system.
D. Drop all indexes and constraints associated with the column prior to dropping the
column.
E. Mark the column INVALID prior to beginning the drop to bypass undo.
F. Remove the column using the DROP UNUSED COLUMNS command.
G. Add a checkpoint to the DROP UNUSED COLUMNS command to minimize undo
space.
Ans: B

8. Examine the syntax below, which creates a DEPARTMENTS table:

CREATE TABLE hr.departments(
Department_id NUMBER(4),
department_name VARCNAR2(30),
manager_id NUMBER(6),
location_id NUMBER(4))
STORAGE(INITIAL 200K NEXT 200K
PCTINCREASE 50 MINEXTENTS 1 MAXEXTENTS 5)
TABLESPACE data;

What is the size defined for the fifth extent?

A. 200 K
B. 300 K
C. 450 K
D. 675 K
E. Not defined
Ans: D

9. Which of the following datatypes are used in situations where you want an ordered set of data elements, where every element is the same datatype, and where you predefine the number of elements that appear in the set?

A. REF
B. TABLE
C. CLOB
D. VARRAY
Ans: D

10. In order to find out how many database objects a user has created, which view would the DBA query in the Oracle data dictionary?

A. DBA_USERS
B. DBA_OBJECTS
C. DBA_TS_QUOTAS
D. DBA_TAB_PRIVS
Ans: B

11. You have identified a table in the database that is experiencing severe row chaining.

Which of the following choices best identifies a way to correct the problem?

A. Increase PCTUSED.
B. Increase PCTFREE.
C. Increase PCTINCREASE.
D. Increase NEXT.
Ans: B

12. You are trying to alter the initial segment size given to a table in a dictionary-managed tablespace.

Which of the following keywords would be used as part of this process?

A. DROP TABLE
B. ALTER TABLE
C. RESIZE
D. COALESCE
Ans: A

13. You alter a tablespace.s DEFAULT STORAGE settings in the Oracle database to increase the size of initial extents.

Which of the following choices identifies when the change will take effect for tables that already exist in that tablespace?

A. The change takes effect immediately.
B. The change takes effect when data is added to the table.
C. The change takes effect when data is removed from the table.
D. The change will not take effect for existing tables.
Ans: D

14. A table was just created on your Oracle database with six extents allocated to it.

Which of the following factors most likely caused the table to have so many extents allocated?

A. The value for MINEXTENTS setting
B. The value for PCTINCREASE setting
C. The value for MAXEXTENTS setting
D. By default, Oracle allocates six extents to all database objects.
Ans: A

15. The rows inside three Oracle tables supporting a customer order entry system are frequently accessed together by means of a table join. Because data is always being added to the tables, you leave a lot of extra space inside each block to accommodate growth.

Which of the following types of tables would be useful for storing the data in this context?

A. Temporary tables
B. Index-organized tables
C. Cluster tables
D. Standard Oracle tables
Ans: D

16. You just issued the following statement: ALTER TABLE SALES DROP COLUMN PROFIT.

Which of the following choices identifies when the column will actually be removed from Oracle?

A. Immediately following statement execution
B. After the ALTER TABLE DROP UNUSED COLUMNS command is issued
C. After the ALTER TABLE SET UNUSED COLUMN command is issued
D. After the ALTER TABLE MODIFY command is issued
Ans: A

17. Which statement is true regarding the maintenance of tables?

A. Truncating a table deletes al rows in a table. Corresponding indexes will not be truncated.
B. Non-partitioned table reorganization moves date into a new segment while
persevering the index. Constraints, privileges, and triggers are disabled.
C. Once a column within a table has been marked as UNUSED, a new column
with the same name as the UNUSED column can be added to the table.
D. Dropping a column from a table removes the column length and date from
each row. Indexes and constraints must be re-created.

Lession-12: Managing Indexes

1. Which type of index does this syntax create?

CREATE INDEX hr.employees_last_name_idx
ON hr.employees(last_name)
PCTFREE 30
STORAGE(INITIAL 200K NEXT 200K
PCTINCREASE 0 MAXEXTENTS 50)
TABLESPACE indx;

A. Bitmap
B. B-Tree
C. Partitioned
D. Reverse key
Ans: B

2. You issue these queries to obtain information about the REGIONS table:

SQL> SELECT segment_name, tablespace_name
2> FROM user_segments
3> WHERE segment_name = ‘REGIONS’;

SEGMENT_NAME TABLESPACE_NAME
————— ——————-
REGIONS SAMPLE

SQL> SELECT constraint_name, constraint_type
2> FROM user constraints
3> WHERE table_name = ‘REGIONS’;

CONSTRAINT_NAME C
—————————– –
REGION_ID_NN C
REG_ID P

SQL> SELECT index_named
2> FROM user indexes
3> WHERE table_name = ‘REGIONS’;

INDEX_NAME
—————————-
REG_ID_PK

You then issue this command to move the REGIONS table:

ALTER TABLE regions
MOVE TABLESPACE user_data;

What else must you do to complete the move of the REGIONS table?

A. You must rebuild the REG_ID_PK index.
B. You must re-create the REGION_ID_NN and REG_ID_PK constraints.
C. You must drop the REGIONS table that is in the SAMPLE tablespace.
D. You must grant all privileges that were on the REGIONS table in the SAMPLE
tablespace to the REGIONS table in the USER_DATA tablespace.
Ans: A

3. You need to create an index on the CUSTOMER_ID column of the CUSTOMERS table.

The index has these requirements:

1. The index will be called CUST_PK.
2. The index should be sorted in ascending order.
3. The index should be created in the INDEX01 tablespace, which is a dictionary
4. All extents of the index should be 1 MB in size.
5. The index should be unique.
6. No redo information should be generated when the index is created.
7. 20% of each data block should be left free for future index entries.

Which command creates the index and meets all the requirements?

A. CREATE UNIQUE INDEX cust_pk ON customers (customer_id)
TABLESPACE index0l
PCTFREE 20
STORAGE (INITIAL lm NEXT lm PCTINCREASE 0);
B. CREATE UNIQUE INDEX cust_pk ON customers (customer_id)
TABLESPACE index0l
PCTFREE 20
STORAGE (INITIAL 1m NEXT 1m PCTINCREASE 0)
NOLOGGING;
C. CREATE UNIQUE INDEX cust_pk ON customers (customer_id)
TABLESPACE index0l
PCTUSED 80
STORAGE (INITIAL lm NEXT lm PCTINCREASE 0)
NOLOGGING;
D. CREATE UNIQUE INDEX cust_pk ON customers (customer_id)
TABLESPACE index0l
PCTUSED 80
STORAGE (INITIAL lm NEXT lm PCTINCREASE 0);
Ans: B

4. You need to create an index on the PASSPORT_RECORDS table. It contains 10 million rows of data. The key columns have low cardinality. The queries generated against this table use 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
Ans: A

5. Your developers asked you to create an index on the PROD_ID column of the SALES_HISTORY table, which has 100 million rows.

The table has approximately 2 million rows of new data loaded on the first day of every month. For the remainder of the month, the table is only queried. Most reports are generated according to the PROD_ID, which has 96 distinct values.

Which type of index would be appropriate?

A. Bitmap
B. Reverse key
C. Unique B-Tree
D. Normal B-Tree
E. Function based
F. Non-unique concatenated
Ans: A

6. The credit controller for your organization has complained that the report she runs to show customers with bad credit ratings takes too long to run. You look at the query that the report runs and determine that the report would run faster if there were an index on the CREDIT_RATING column of the CUSTOMERS table.

The CUSTOMERS table has about 5 million rows and around 100 new rows are added
every month. Old records are not deleted from the table.

The CREDIT_RATING column is defined as a VARCHAR2(5) field. There are only 10
possible credit ratings and a customer’s credit rating changes infrequently. Customers with bad credit ratings have a value in the CREDIT_RATINGS column of ‘BAD’ or ‘F’.

Which type of index would be best for this column?

A. B-Tree
B. Bitmap
C. Reverse key
D. Function-based
Ans: B

7. After running the ANALYZE INDEX orders cust_idx VALIDATE STRUCTURE command, you query the INDEX_STATS view and discover that there is a high ratio of DEL_LF_ROWS to LF_ROWS values for this index.

You decide to reorganize the index to free up the extra space, but the space should remain allocated to the ORDERS_CUST_IDX index so that it can be reused by new entries inserted into the index.

Which command(s) allows you to perform this task with the minimum impact to any users
who run queries that need to access this index while the index is reorganized?

A. ALTER INDEX REBUILD
B. ALTER INDEX COALESCE
C. ALTER INDEX DEALLOCATE UNUSED
D. DROP INDEX followed by CREATE INDEX
Ans: B

8. Which is true when considering the number of indexes to create on a table?

A. Every column that is updated requires an index.
B. Every column that is queried is a candidate for an index.
C. Columns that are part of a WHERE clause are candidates for an index.
D. On a table used in a Data Warehouse application there should be no indexes.
Ans: C

9. You need to create an index on the SALES table, which is 10 GB in size. You want your index to be spread across many tablespaces, decreasing contention for index lookup, and increasing scalability and manageability.

Which type of index would be best for this table?

A. Bitmap
B. Unique
C. Partitioned
D. Reverse key
E. Single column
F. Function-based
Ans: C

10. User Smith created indexes on some tables owned by user John.

You need to display the following:

Index names
Index types

Which data dictionary view(s) would you need to query?

A. DBA_INDEXES only
B. DBA_IND_COLUMNS only
C. DBA_INDEXES and DBA_USERS
D. DBA_IND COLUMNS and DBA_USERS
E. DBA_INDEXES and DBA_IND_EXPRESSIONS
F. DBA_INDEXES, DBA_TABLES, and DBA_USERS
Ans: A

11. Which two statements are true about rebuilding an index? (Choose two)

A. The resulting index may contain deleted entries.
B. A new index is built using an existing index as the data source.
C. Queries cannot use the existing index while the new index is being built.
D. During a rebuild, sufficient space is needed to accommodate both the old and the new index in their respective tablespaces.
Ans: B, D

12. The ORDERS table has a constant transaction load 24 hours a day, so down time is not allowed. The indexes become fragmented.

Which statement is true?

A. The index needs to be dropped, and then re-created.
B. The resolution of index fragmentation depends on the type of index.
C. The index can be rebuilt while users continue working on the table.
D. The index can be rebuilt, but users will not have access to the index during this time.
E. The fragmentation can be ignored because Oracle resolves index fragmentation by means of a freelist.
Ans: C

13. Which two statements are true about identifying unused indexes? (Choose two.)

A. Performance is improved by eliminating unnecessary overhead during DML operations.
B. V$INDEX_STATS displays statistics that are gathered when using the MONITORING
USAGE keyword.
C. Each time the MONITORING USAGE clause is specified, the V$OBJECT_USAGE
view is reset for the specified index.
D. Each time the MONITORING USAGE clause is specified, a new monitoring start time is recorded in the alert log.
Ans: A, C

14. You are configuring your index to be stored in a tablespace.

Which of the following storage parameters are NOT appropriate for indexes?

A. OPTIMAL
B. INITIAL
C. PCTINCREASE
D. NEXT
Ans: A

Lession-13: Maintaining Data Integrity

1. You query DBA_CONSTRAINTS to obtain constraint information on the HR_EMPLOYEES table:

SQL> select constraint_name, constraint_type, deferrable,
2> deferred, validated
3> from dba_constraints
4> where owner = ‘HR’ and table_name=’EMPLOYEES’;

CONSTRAINT_NAME C DEFERRABLE DEFERRED VALIDATED
——————— – ————– ———– ————–
EMP_DEPT_FK R NOT DEFERRABLE IMMEDIATE VALIDATED
EMP_EMAIL_NV C NOT DEFERRABLE IMMEDIATE VALIDATED
EMP_EMAIL_UK U NOT DEFERRABLE IMMEDIATE VALIDATED
EMP_EMP_ID_PK P NOT DEFERRABLE IMMEDIATE VALIDATED
EMP_HIRE_DATE_NN C NOT DEFERRABLE IMMEDIATE VALIDATED
EMP_JOB_FK R NOT DEFERRABLE IMMEDIATE VALIDATED
EMP_JOB_NN C DEFERRABLE DEFERRED NOT VALIDATED
EMP_LAST_NAME_NN C NOT DEFERRABLE IMMEDIATE VALIDATED
EMP_MANAGER_FK R NOT DEFERRABLE IMMEDIATE VALIDATED
EMP_SALARY_MIN C NOT DEFERRABLE IMMEDIATE VALIDATED

Which type of constraint is EMP_JOB_NN?

A. Check
B. Unique
C. Not null
D. Primary key
E. Foreign key
Ans: C

2. For which two constraints are indexes created when the constraint is added? (Choose two)

A. Check
B. Unique
C. Not null
D. Primary key
E. Foreign key
Ans: B, D

3. Which constraint state prevents new data that violates the constraint from being entered, but allows invalid data to exist in the table?

A. ENABLE VALIDATE
B. DISABLE VALIDATE
C. ENABLE NOVALIDATE
D. DISABLE NOVALIDATE
Ans: A

4. Examine the command:

CREATE TABLE employee
( employee_id NUMBER CONSTRAINT employee_empid_pk
PRIMARY KEY,
employee_name VARCNAR2(30),
manager_id NUMBER CONSTRAINT employee_mgrid_fk
REFERENCES employee(employee_id));

The EMP table contains self referential integrity requiring all NOT NULL values inserted
in the MANAGER_ID column to exist in the EMPLOYEE_ID column.

Which view or combination of views is required to return the name of the foreign key
constraint and the referenced primary key?

A. DBA_TABLES only
B. DBA_CONSTRAINTS only
C. DBA_TABS_COLUMNS only
D. DBA_CONS_COLUMNS only
E. DBA_TABLES and DBA_CONSTRAINTS
F. DBA_TABLES and DBA_CONS_COLUMNS
Ans: B

5. You need to enforce these two business rules:

1. No two rows of a table can have duplicate values in the specified column.
2. A column cannot contain null values.

Which type of constraint ensures that both of the above rules are true?

A. Check
B. Unique
C. Not null
D. Primary key
E. Foreign key
Ans: D

6. Which statement is true regarding enabling constraints?

A. ENABLE NOVALIDATE is the default when a constraint is enabled.
B. Enabling a constraint NOVALIDATE places a lock on the table.
C. Enabling a UNIQUE constraint to VALIDATE does not check for constraint violation if
the constraint is deferrable.
D. A constraint that is currently disabled can be enabled in one of two ways: ENABLE
NOVALIDATE or ENABLE VALIDATE.
Ans: D

7. In order design a table that enforces uniqueness on a column, which three of the following choices are appropriate?

A. Unique constraint
B. Bitmap index
C. Primary key
D. Foreign key
E. Not NULL constraint
F. Partitioned index
G. Unique index
H. Check constraint
Ans: A, C, G

Lession-14: Managing Password Security and Resources
1. You intend to use only password authentication and have used the password file utility to create a password file as follows:

$orapwd file=$ORACLE_HOME/dbs/orapwDB01
password=orapass entries=5

The REMOTE_LOGIN_PASSWORDFILE initialization parameter is set to NONE.
You created a user and granted only the SYSDBA privilege to that user as follows:

CREATE USER dba_user
IDENTIFIED BY dba_pass;

GRANT sysdba TO dba_user;

The user attempts to connect to the database as follows:

connect dba_user/dba_pass as sysdba;

Why does the connection fail?

A. The DBA privilege was not granted to dba_user.
B. REMOTE_LOGIN_PASSWORDFILE is not set to EXCLUSIVE.
C. The password file has been created in the wrong directory.
D. The user did not specify the password orapass to connect as SYSDBA.
Ans: B

2. Based on the following profile limits, if a user attempts to log in and fails after five 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 1800
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 5
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
Ans: A

3. Which password management feature ensures a user cannot reuse a password for a specified time interval?

A. Account Locking
B. Password History
C. Password Verification
D. Password Expiration and Aging
Ans: B

4. Which data dictionary view(s) do you need to query to find the following information about a user?

 Whether the user’s account has expired
 The user’s default tablespace name
 The user’s profile name

A. DBA_USERS only
B. DBA_USERS and DBA_PROFILES
C. DBA_USERS and DBA_TABLESPACES
D. DBA_USERS, DBA_TS_QUOTAS, and DBA_PROFILES
E. DBA_USERS, DBA_TABLESPACES, and DBA_PROFILES
Ans: A

5. You set the value of the OS_AUTHENT_PREFIX initialization parameter to OPS$ and created a user account by issuing this SQL statement:

CREATE USER OPS$smith
IDENTIFIED EXTERNALLY;

Which two statements are true? (Choose two.)

A. Oracle server assigns the DEFAULT profile to the user.
B. You can specify the PASSWORD EXPIRE clause for an external user account.
C. The user does not require CREATE SESSION system privilege to connect to the
database.
D. If you query the DBA_USERS data dictionary view the USERNAME column will
contain the value SMITH.
E. The user account is maintained by Oracle, but password administration and user
authentication are performed by the operating system or a network service.
Ans: A, E

6. Which four statements are true about profiles? (Choose four)

A. Profiles can control the use of passwords.
B. Profile assignments do not affect current sessions.
C. All limits of the DEFAULT profile are initially unlimited.
D. Profiles can be assigned to users and roles, but not other profiles.
E. Profiles can ensure that users log off the database when they have left their session idle for a period of time.
Ans: A, B, C, E

7. As the DBA, you are attempting to limit users. misuse of Oracle.s capability to use host machine resources.

Which of the following features of the Oracle database is useful forthis purpose?

A. UNDO segments
B. Roles
C. Profiles
D. Parameter files
Ans: C
8. You are defining areas on your Oracle database.

Which of the following profile areas can be used to control the resource usage for the other four?

A. LOGICAL_READS_PER_SESSION
B. CONNECT_TIME
C. COMPOSITE_LIMIT
D. CPU_PER_SESSION
E. PRIVATE_SGA
Ans: C
Lession-15: Managing Users

1. You create a new table named DEPARTMENTS by issuing this statement:

CREATE TABLE departments(
department_id NUMBER(4),
department_name VARCHAR2(30),
manager_id NUMBER(6),
location_id NUMBER(4))
STORAGE(INITIAL 200K NEXT 200K
PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 5);

You realize that you failed to specify a tablespace for the table. You issue these queries:

SQL> SELECT username, default_tablespace,
temporary tablespace
2> FROM user_users;

USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
———- ——————- ——————–
HR SAMPLE TEMP

SQL> SELECT * from user_ts_quotas;

TABLESPACE_NAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS
————— ——— ——— —— ———–
SAMPLE 28311552 -1 6912 -1
INDX 0 -1 0 -1

In which tablespace was your new DEPARTMENTS table created?

A. TEMP
B. SYSTEM
C. SAMPLE
D. USER_DATA
Ans: C

2. Evaluate the following SQL:

CREATE USER sh IDENTIFIED BY sh;
GRANT
CREATE ANY MATERIALIZED VIEW
CREATE ANY DIMENSION
, DROP ANY DIMENSION
, QUERY REWRITE
, GLOBAL QUERY REWRITE
TO dw_manager
WITH ADMIN OPTION;

GRANT dw_manager TO sh WITH ADMIN OPTION;

Which three actions is the user SH able to perform? (Choose three.)

A. Select from a table
B. Create and drop a materialized view
C. Alter a materialized view that you created
D. Grant and revoke the role to and from other users
E. Enable the role and exercise any privileges in the role’s privilege domain
Ans: B, D, E

3. Which two methods enforce resource limits? (Choose two.)

A. ALTER SYSTEM SET RESOURCE_LIMIT= TRUE
B. Set the RESOURCE_LIMIT parameter to TRUE
C. CREATE PROFILE sessions LIMIT
SESSIONS_PER_USER 2
CPU_PER_SESSION 10000
IDLE_TIME 60
CONNECT_TIME 480;
D. ALTER PROFILE sessions LIMIT
SESSIONS_PER_USER 2
CPU_PER_SESSION 10000
IDLE_TIME 60
CONNECT_TIME 480;
Ans: A, B

4. More stringent user access requirements have been issued. You need to do these tasks for the user pward:

1. Change user authentication to external authentication.
2. Revoke the user’s ability to create objects in the TEST TS tablespace.
3. Add a new default and temporary tablespace and set a quota of unlimited.
4. Assign the user to the CLERK profile.

Which statement meets the requirements?

A. ALTER USER pward
IDENTIFIED EXTERNALLY
DEFAULT TABLESPACE data_ts
TEMPORARY TABLESPACE temp_ts
QUOTA UNLIMITED ON data_ts
QUOTA 0 ON test_ts
GRANT clerk TO pward;
B. ALTER USER pward
IDENTIFIED by pward
DEFAULT TABLESPACE dsta_ts
TEMPORARY TABLESPACE temp_ts
QUOTA UNLIMITED ON data_ts
QUOTA 0 ON test_ts
PROFILE clerk;
C. ALTER USER pward
IDENTIFIED EXTERNALLY
DEFAULT TABLESPACE data_ts
TEMPORARY TABLESPACE temp_ts
QUOTA UNLIMITED ON data_ts
QUOTA 0 ON test_ts
PROFILE clerk;
D. ALTER USER pward
IDENTIFIED EXTERNALLY
DEFAULT TABLESPACE data_ts
TEMPORARY TABLESPACE temp_ts
QUOTA UNLIMITED ON data_ts
QUOTA 0 ON test ts;
GRANT clerk to pward;
Ans: C

5. A new user, psmith, has just joined the organization. You need to create psmith as a valid user in the database. You have the following requirements:

1. Create a user who is authenticated externally.
2. Make sure the user has CONNECT and RESOURCE privileges.
3. Make sure the user does NOT have DROP TABLE and CREATE USER privileges.
4. Set a quota of 100 MB on the default tablespace and 500 K on the temporary
tablespace.
5. Assign the user to the DATA_TS default tablespace and the TEMP_TS temporary
tablespace.

Which statement would you use to create the user?

A. CREATE USER psmith
IDENTIFIED EXTERNALLY
DEFAULT TABLESPACE data_ts
QUOTA 100M ON data_ts
QUOTA 500K ON temp_ts
TEMPORARY TABLESPACE temp_ts;
REVOKE DROP TABLE, CREATE USER from psmith;
B. CREATE USER psmith
IDENTIFIED EXTERNALLY
DEFAULT TABLESPACE data_ts
QUOTA 500K ON temp_ts
QUOTA 100M ON data_ts
TEMPORARY TABLESPACE temp_ts;
GRANT connect, resource TO psmith;
C. CREATE USER psmith
IDENTIFIED EXTERNALLY
DEFAULT TABLESPACE data_ts
QUOTA 100M ON data_ts
QUOTA 500K ON temp_ts
TEMPORARY TABLESPACE temp_ts;
GRANT connect TO psmith;
D. CREATE USER psmith
INDENTIFIED GLOBALLY AS ‘’
DEFAULT TABLESPACE data_ts
QUOTA 500K ON temp_ts
QUOTA 100M ON data_ts
TEMPORARY TABLESPACE temp_ts;
GRANT connect, resource TO psmith;
REVOKE DROP TABLE, CREATE USER from psmith;
Ans: B

6. Which data dictionary view would you use to get a list of all database users and their default settings?

A. ALL_USERS
B. USERS_USERS
C. DBA_USERS
D. V$SESSION
Ans: C

7. The user’s pward and psmith have left the company. You no longer want them to have access to the database. You need to make sure that the objects they created in the database remain.

What do you need to do?

A. Revoke the CREATE SESSION privilege from the user.
B. Drop the user from the database with the CASCADE option.
C. Delete the users and revoke the CREATE SESSION privilege.
D. Delete the users by using the DROP USER command from the database.
Ans: A

8. Tom was allocated 10 MB of quota in the USERS tablespace. He created database objects in the USERS tablespace. The total space allocated for the objects owned by Tom is 5 MB.

You need to revoke Tom’s quota from the USERS tablespace. You issue this command:

ALTER USER Tom QUOTA 0 ON users;

What is the result?

A. The statement raises the error: ORA-00940: invalid ALTER command.
B. The statement raises the error: ORA-00922: missing or invalid option.
C. The objects owned by Tom are automatically deleted from the revoked USERS
tablespace.
D. The objects owned by Tom remain in the revoked tablespace, but these objects cannot be allocated any new space from the USERS tablespace.
Ans: D

9. A DBA has issued the following SQL statement:

SELECT max_blocks
FROM dba_ts_quotas
WHERE tablespace_name=’USER_TBS’
AND username=’JENNY’;

User Jenny has unlimited quota on the USER_TBS tablespace. Which value will the query return?

A. 0
B. 1
C. -1
D. NULL
E. ‘UNLIMITED’
Ans: C

10. A user cannot change aspects of his or her account configuration with the exception of one item.

Which of the following choices identifies an area of the user’s account that the user can change himself or herself using an ALTER USER statement?

A. IDENTIFIED BY
B. DEFAULT TABLESPACE
C. TEMPORARY TABLESPACE
D. QUOTA ON
E. PROFILE
F. DEFAULT ROLE
Ans: C

Lession-16: Managing Privileges
1. Which data dictionary view would you use 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
Ans: A

2. You used the password file utility to create a password file as follows:

$orapwd file=$ORACLE_HOME/dbs/orapwDB01
password=orapass entries=5

You created a user and granted only the SYSDBA privilege to that user as follows:

CREATE USER dba_user
IDENTIFIED BY dba_pass;
GRANT sysdba TO dba_user;

The user attempts to connect to the database as follows:

connect dba_user/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 dba_pass to connect as SYSDBA.
D. The information about dba_user has not been stored in the password file.
Ans: C

3. Which two statements grant an object privilege to the user Smith? (Choose two)

A. GRANT CREATE TABLE TO smith;
B. GRANT CREATE ANY TABLE TO smith;
C. GRANT CREATE DATABASE LINK TO smith;
D. GRANT ALTER ROLLBACK SEGMENT TO smith;
E. GRANT ALL ON scott.salary_view TO smith;
F. GRANT CREATE PUBLIC DATABASE LINK TO smith;
G. GRANT ALL ON scott.salary_view TO smith WITH GRANT OPTION;
Ans: E, G

4. The new Human Resources Application will be used to manage employee data in the EMPLOYEES table. You are developing a strategy to manage user privileges. Your strategy should allow for privileges to be granted or revoked from individual users or groups of users with minimal administrative effort.

The users of the Human Resources application have these requirements:

A Manager should be able to view the personal information of the employees in his/her group and make changes to their Title and Salary.

What should you grant to the manager user?

A. Grant SELECT on the EMPLOYEES table
B. Grant INSERT on the EMPLOYEES table
C. Grant UPDATE on the EMPLOYEES table
D. Grant SELECT on the EMPLOYEES table and then grant UPDATE on the TITLE and
SALARY columns
E. Grant SELECT on the EMPLOYEES table and then grant INSERT on the TITLE and
SALARY columns
F. Grant UPDATE on the EMPLOYEES table and then grant SELECT on the TITLE and
SALARY columns
G. Grant INSERT on the EMPLOYEES table and then grant SELECT on the TITLE,
MANAGER, and SALARY columns
Ans: D

5. Bob is an administrator who has FULL DBA privileges. When he attempts to drop the DEFAULT profile as shown below, he receives the error message shown.

Which option best explains this error?

SQL> drop profile SYS.DEFAULT;
drop profile SYS.DEFAULT
*
ERROR at line 1:
ORA-00950: invalid DROP option

A. The DEFAULT profile cannot be dropped.
B. Bob requires the DROP PROFILE privilege.
C. Profiles created by SYS cannot be dropped.
D. The CASCADE option was not used in the DROP PROFILE command.
Ans: A

6. Anne issued this SQL statement to grant Bill access to the CUSTOMERS table in Anne’s schema:

GRANT SELECT ON customers TO bill WITH GRANT OPTION;

Bill issued this SQL statement to grant Claire access to the CUSTOMERS table in Anne’s
schema:

GRANT SELECT ON anne.customers TO claire;

Later, Anne decides to revoke the select privilege on the CUSTOMERS table from Bill.

Which statement correctly describes both what Anne can do to revoke the privilege, and
the effect of the REVOKE command?

A. Anne can run the REVOKE SELECT ON customers FROM bill statement. Both Bill and Claire lose their access to the CUSTOMERS table.
B. Anne can run the REVOKE SELECT ON customers FROM bill statement. Bill loses
access to the CUSTOMERS table, but Claire will keep her access.
C. Anne cannot run the REVOKE SELECT ON customers from BILL statement unless Bill first revokes Claire’s access to the CUSTOMERS table.
D. Anne must run the REVOKE SELECT ON customers FROM bill CASCADE statement. Both Bill and Claire lose their access to the CUSTOMERS table.
Ans: A

7. User ANN has INSERT privilege on the EMP table.

What is the most immediate effect of the DBA revoking ANN.s privilege?

A. ANN.s records will be removed from the database.
B. ANN will not have the ability to create tables.
C. ANN will not be able to access the database any more.
D. Users to which ANN granted INSERT privileges will not be able to insert.
Ans: D

Lession-17: Managing Roles

1. You just created five roles using the statements shown:

CREATE ROLE payclerk;
CREATE ROLE oeclerk IDENTIFIED BY salary;
CREATE ROLE hr_manager IDENTIFIED EXTERNALLY;
CREATE ROLE genuser IDENTIFIED GLOBALLY;
CREATE ROLE dev IDENTIFIED USING dev_test;

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 payclerk;
B. CREATE ROLE genuser IDENTIFIED GLOBALLY;
C. CREATE ROLE oeclerk IDENTIFIED BY salary;
D. CREATE ROLE dev IDENTIFIED USING dev_test;
E. CREATE ROLE hr_manager IDENTIFIED EXTERNALLY;
Ans: B

2. John has issued the following SQL statement to create a new user account:

CREATE USER john
IDENTIFIED BY john
TEMPORARY TABLESPACE temp_tbs
QUOTA 1M ON system
QUOTA UNLIMITED ON data_tbs
PROFILE apps_profile
PASSWORD EXPIRE
DEFAULT ROLE apps_dev_role;

Why does the above statement return an error?

A. You cannot assign a role to a user within a CREATE USER statement.
B. You cannot explicitly grant quota on the SYSTEM tablespace to a user.
C. You cannot assign a profile to a user within a CREATE USER statement.
D. You cannot specify PASSWORD EXPIRE clause within a CREATE USER statement.
E. You cannot grant UNLIMITED quota to a user within a CREATE USER statement.
Ans: A

3. Which command would revoke the ROLE_EMP role from all users?

A. REVOKE role_emp FROM ALL;
B. REVOKE role_emp FROM PUBLIC;
C. REVOKE role_emp FROM default;
D. REVOKE role_emp FROM ALL_USERS;
Ans: B

4. As SYSDBA you created the PAYCLERK role and granted the role to Bob. Bob in turn attempts to modify the authentication method of the PAYCLERK role from SALARY to NOT IDENTIFIED, but when doing so he receives the insufficient privilege error shown below.

SQL> connect bob/crusader
Connected.
SQL> alter role payclerk not identified;
alter role payclerk not identified
*
ERROR at line 1:
ORA-01031: insufficient privileges

Which privilege does Bob require to modify the authentication method of the PAYCLERK role?

A. ALTER ANY ROLE
B. MANAGE ANY ROLE
C. UPDATE ANY ROLE
D. MODIFY ANY ROLE
Ans: A

5. How do you enable the HR_CLERK role?

A. SET ROLE hr_clerk;
B. CREATE ROLE hr_clerk;
C. ENABLE ROLE hr_clerk;
D. SET ENABLE ROLE hr_clerk;
Ans: A

6. The DBA is defining role for users.

Which of the following is not an acceptable method for defining a default role?

A. ALTER USER DEFAULT ROLE ALL;
B. ALTER USER DEFAULT ROLE ALL EXCEPT ROLE_1;
C. ALTER USER DEFAULT ROLE NONE;
D. ALTER USER DEFAULT ROLE NONE EXCEPT ROLE_1;
Ans: D

7. To allocate another role to a user, which command is most appropriate?

A. ALTER USER
B. ALTER DATABASE
C. ALTER SYSTEM
D. GRANT
Ans: D

Lession-18: Auditing

1. The DBA is about to enable auditing on the Oracle database in an attempt to discover some suspicious activity.

Audit trail information is stored in which of the following database object names?

A. SYS.SOURCE$
B. SYS.AUD$
C. DBA_SOURCE
D. DBA_AUDIT_TRAIL
Ans: B

Lession-19: Loading Data into a Database

1. Which of the following choices correctly describes the difference between a data load via the conventional path and the direct path?

A. One runs faster than the other.
B. A conventional path data load bypasses most of the Oracle RDBMS, whereas a direct path load is a high-speed version of the SQL INSERT.
C. A direct path data load bypasses most of the Oracle RDBMS, whereas a conventional path load is a high-speed version of the SQL INSERT.
D. The conventional path runs when the CONVENTIONAL command-line parameter is
set to TRUE.
Ans: C

Lession-20: Using Globalization Support
1. There are three ways to specify National Language Support parameters:

1. initialization parameters
2. environment variables
3. ALTER SESSION parameters

Match each of these with their appropriate definitions.

A. 1) Parameters on the client side to specify locale-dependent behavior overriding the
defaults set for the server
2) Parameters on the server side to specify the default server environment
3) Parameters override the default set for the session or the server
B. 1) Parameters on the server side to specify the default server environment
2) Parameters on the client side to specify locale-dependent behavior overriding the
defaults set for the server
3) Parameters override the default set for the session or the server
C. 1) Parameters on the server side to specify the default server environment
2) Parameters override the default set for the session or the server
3) Parameters on the client side to specify locale-dependent behavior overriding the
defaults set for the server
D. 1) Parameters on the client side to specify locale-dependent behavior overriding the
defaults set for the server
2) Parameters override the default set for the session or the server
3) Parameters on the server side to specify the default server environment
Ans: B

2. Your database is currently configured with the database character set to WEBIS08859P1 and national character set to AL16UTF16.

Business requirements dictate the need to expand language requirements beyond the
current character set, for Asian and additional Western European languages, in the form
of customer names and addresses.

Which solution saves space storing Asian characters and maintains consistent character
manipulation performance?

A. Use SQL CHAR data types and change the database character set to UTF8.
B. Use SQL NCHAR data types and change the national character set to UTF8.
C. Use SQL CHAR data types and change the database character set to AL32UTF8.
D. Use SQL NCHAR data types and keep the national character set to AL16UTF16.
Ans: C

3. Which command can you use to display the date and time in the form 17:45:01 JUL-12-2000 using the default US7ASCII character set?

A. ALTER SYSTEM SET NLS_DATE_FORMAT=’HH24:MI:SS MON-DD-YYYY’;
B. ALTER SESSION SET DATE_FORMAT=’HH24:MI:SS MON-DD-YYYY’;
C. ALTER SESSION SET NLS_DATE_FORMAT=’HH24:MI:SS MON-DD-YYYY’;
D. ALTER SYSTEM SET NLS_DATE_FORMAT=’HH:MI:SS MON-DD-YYYY’;
Ans: C

4. In order to enable remote administration of users and tablespaces on an Oracle database, which of the following types of files must exist in the database?

A. Password file
B. Initialization file
C. Datafile
D. Control file
E. Nothing . SYSDBA privileges are not required for these actions.
Ans: E