Wednesday, August 23, 2006

Fundamentals I Ch6 - Logical and Physical Database Structures

Chapter 6 - Logical and Physical Database Structures


OCA/OCP: Oracle9i DBA Fundamentals I Study Guide
by Biju Thomas and Bob Bryla


Review Questions



1. Which two of the following statements do you execute to make the USERS tablespace read-only, if the tablespace is offline?


A. ALTER TABLESPACE USERS READ ONLY
B. ALTER DATABASE MAKE TABLESPACE USERS READ ONLY
C. ALTER TABLESPACE USERS ONLINE
D. ALTER TABLESPACE USERS TEMPORARY

---
To make a tablespace read-only, all the data files belonging to the tablespace must be online and available. So, bring the tablespace online, and then make it read-only.
Ans: C, A.


2. When is a sort segment that is allocated in a temporary tablespace released?


A. When the sort operation completes
B. When the instance is shut down
C. When you issue ALTER TABLESPACE COALESCE
D. When SMON clears up inactive sort segments

---
The sort segment or temporary segment created in a temporary tablespace is released only when the instance is shut down. Each instance can have one sort segment in the tablespace; the sort segment is created when the first sort for the instance is started.
Ans: B.


3. You created a tablespace using the following statement:


CREATE TABLESPACE MYTS
DATAFILE SIZE 200M AUTOEXTEND ON MAXSIZE 2G
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 5M
SEGMENT SPACE MANAGEMENT AUTO;

Which three parameters does Oracle ignore when you create a table in the MYTS tablespace?

A. PCTFREE
B. PCTUSED
C. FREELISTS
D. FREELIST GROUPS
E. INITIAL
-----
When the tablespace has automatic segment space management, Oracle manages free space automatically using bitmaps. For manual segment space management, Oracle uses free lists.
Ans: B, C, D.


4. What will be the minimum size of the segment created in a tablespace if the tablespace’s default storage values are specified as (INITIAL 2M NEXT 2M MINEXTENTS 3 PCTINCREASE 50) and no storage clause is specified for the object?


A. 2MB
B. 4MB
C. 5MB
D. 7MB
E. 8MB

-----
When the segment is created, it will have three extents; the first extent is 2MB, the second is 2MB, and the third is 3MB. So the total size of the segment is 7MB.
Ans: D.

5. Which of the following would you use to add more space to a tablespace? (Choose two.)


A. ALTER TABLESPACE ADD DATAFILE SIZE
B. ALTER DATABASE DATAFILE RESIZE
C. ALTER DATAFILE RESIZE
D. ALTER TABLESPACE DATAFILE RESIZE
-----
You can add more space to a tablespace either by adding a data file or by increasing the size of an existing data file. Option A does not specify a file name and uses the OMF feature to generate file name.
Ans: A, B.


6. If the DB_BLOCK_SIZE of the database is 8KB, what will be the size of the third extent when you specify the storage parameters as (INITIAL 8K NEXT 8K PCTINCREASE 50 MINEXTENTS 3)?


A. 16KB
B. 24KB
C. 12KB
D. 40KB
----
The third extent size will be NEXT + 0.5 * NEXT, which is 12KB, but the block size is 8KB, so the third extent size will be 16KB. The initial extent allocated will be 16KB (the minimum size for INITIAL is two blocks), and the total segment size is 16 + 8 + 16 = 40KB.
Ans: A.


7. The standard block size for the database is 8KB. You need to create a tablespace with block size of 16KB. Which initialization parameters should be set? (Choose two.)


A. DB_8K_CACHE_SIZE
B. DB_16K_CACHE_SIZE
C. DB_CACHE_SIZE
D. UNDO_MANAGEMENT
E. DB_CREATE_FILE_DEST

----
Set DB_CACHE_SIZE for the standard block size, and set DB_16K_CACHE_SIZE for the non-standard block size. You must not set the DB_8K_CACHE_SIZE parameter because the standard block size is 8KB.
Ans: B, C.



8. Which data dictionary view can you query to obtain information about the files that belong to locally managed temporary tablespaces?
A. DBA_DATA_FILES
B. DBA_TABLESPACES
C. DBA_TEMP_FILES
D. DBA_LOCAL_FILES
----
You create locally managed temporary tablespaces using the CREATE TEMPORARY TABLESPACE command. The data files (temporary files) belonging to these tablespaces are in the DBA_TEMP_FILES view. The EXTENT_MANAGEMENT column of the DBA_TABLESPACES view shows the type of the tablespace. You can query the data files belonging to locally managed permanent tablespaces and dictionary-managed (permanent and temporary) tablespaces from DBA_DATA_FILES. Locally managed temporary tablespaces reduce contention on the data dictionary tables.
Ans: C.



9. When does the SMON process automatically coalesce the tablespaces?

A. When the initialization parameter COALESCE_TABLESPACES is set to TRUE
B. When the PCTINCREASE default storage of the tablespace is set to 0
C. When the PCTINCREASE default storage of the tablespace is set to 50
D. Whenever the tablespace has more than one free extent
-----
The SMON process automatically coalesces free extents in the tablespace when the tablespace’s PCTINCREASE is set to a nonzero value. You can manually coalesce a tablespace by using ALTER TABLESPACE COALESCE.
Ans: C.

Notes: For LMT, will the SMON coalesce the tablespaces?


10. Which operation is permitted on a read-only tablespace?


A. Delete data from table
B. Drop table
C. Create new table
D. None of the above
-----
A table can be dropped from a read-only tablespace. When a table is dropped, Oracle does not have to update the data file; it updates the dictionary tables. Any change to data or creation of new objects is not allowed in a read-only tablespace.
Ans: B.


11. How would you drop a tablespace if the tablespace were not empty?

A. Rename all the objects in the tablespace and then drop the tablespace
B. Remove the data files belonging to the tablespace from the disk
C. Use ALTER DATABASE DROP CASCADE
D. Use DROP TABLESPACE INCLUDING CONTENTS
-----
You use the INCLUDING CONTENTS clause to drop a tablespace that is not empty. Oracle does not remove the data files that belong to the tablespace, if the files are not Oracle managed; you need to do it manually using an operating system command. Oracle updates only the control file. To remove the files, you include the INCLUDING CONTENTS AND DATAFILES clause.
Ans: D.


12. Which command is used to enable the auto-extensible feature for a file, if the file is already part of a tablespace?

A. ALTER DATABASE.
B. ALTER TABLESPACE.
C. ALTER DATA FILE.
D. You cannot change the auto-extensible feature once the data file created.
----
You can use the ALTER TABLESPACE command to rename a file that belongs to the tablespace, but you handle all other file management operations through the ALTER DATABASE command. To enable auto-extension, use ALTER DATABASE DATAFILE AUTOEXTEND ON NEXT MAXSIZE .
Ans: A.


13. The database block size is 4KB. You created a tablespace using the following command.


CREATE TABLESPACE USER_DATA DATAFILE 'C:/DATA01.DBF'
EXTENT MANAGEMENT DICTIONARY;

If you create an object in the database without specifying any storage parameters, what will be the size of the third extent that belongs to the object?

A. 6KB
B. 20KB
C. 50KB
D. 32KB
----
When you create a tablespace with no default storage parameters, Oracle assigns (5 x DB_BLOCK_SIZE) to INITIAL and NEXT; PCTINCREASE is 50. So the third extent would be 50 percent more than the second. The first extent is 20KB, the second is 20KB, and the third is 32KB (because the block size is 4KB).
Ans: D.


14. Which of the following statements is false?

A. You can make a dictionary-managed temporary tablespace permanent.
B. You cannot change the size of the locally managed temporary tablespace file.
C. Once it is created, you cannot alter the extent management of a tablespace using ALTER TABLESPACE.
D. You cannot make a locally managed permanent tablespace temporary.
E. If you do not specify an extent management clause when creating a tablespace, Oracle creates a locally managed tablespace.
----
B. You can change the size of a temporary file using ALTER DATABASE TEMPFILE RESIZE . You cannot rename a temporary file.
Ans: B.


15. Which of the following statements is true regarding the SYSTEM tablespace?


A. Can be made read-only.
B. Can be offline.
C. Data files can be renamed.
D. Data files cannot be resized.

----
You can rename the data files that belong to the SYSTEM tablespace when the database is in the MOUNT state by using the ALTER DATABASE RENAME FILE statement.
Ans: C.


16. What are the recommended INITIAL and NEXT values for a temporary tablespace, to reduce fragmentation?


A. INITIAL = 1MB; NEXT = 2MB
B. INITIAL = multiple of SORT_AREA_SIZE + 1; NEXT = INITIAL
C. INITIAL = multiple of SORT_AREA_SIZE + DB_BLOCK_SIZE; NEXT = INITIAL
D. INITIAL = 2 \ SORT_AREA_SIZE; NEXT = SORT_AREA_SIZE
----
The recommended storage for a TEMPORARY tablespace is a multiple of SORT_AREA_SIZE + DB_BLOCK_SIZE. For example, if the sort area size is 100KB and the block size is 4KB, the sort extents should be sized 104KB, 204KB, 304KB, and so on. The disk is sorted only when there is not enough space available in memory. Memory sort size is specified by the SORT_AREA_SIZE parameter. Therefore, when the sorting is done on disk, the minimum area required is as big as the SORT_AREA_SIZE, and one block is added for the overhead. The INITIAL and NEXT storage parameters should be the same for the TEMPORARY tablespace, and PCTINCREASE should be zero. You can achieve these storage settings by creating a locally managed temporary tablespace with uniform extent sizes.
Ans: C.


17. Which parameter specified in the DEFAULT STORAGE clause of CREATE TABLESPACE cannot be altered after you create the tablespace?

A. INITIAL
B. NEXT
C. MAXEXTENTS
D. None
----
You can change all the default storage parameters defined for the tablespace using the ALTER TABLESPACE command. Once objects are created, you cannot change their INITIAL and MINEXTENTS values.
Ans: D.


18. How would you determine how much sort space is used by a user session?


A. Query the DBA_SORT_SEGMENT view.
B. Query the V$SORT_SEGMENT view.
C. Query the V$SORT_USAGE view.
D. You can obtain only the total sort segment size; you cannot find information on individual session sort space usage.
----
The V$SORT_USAGE view provides the number of EXTENTS and number of BLOCKS used by each sort session. This view provides the username also. It can be joined with V$SESSION or V$SQL to obtain more information on the session or the SQL statement causing the sort.
Ans: C.


19. If you issue ALTER TABLESPACE USERS OFFLINE IMMEDIATE, which of the following statements is true? (Choose two.)


A. All data files belonging to the tablespace must be online.
B. Does not ensure that the data files are available.
C. Need not do media recovery when bringing the tablespace online.
D. Need to do media recovery when bringing the tablespace online.
----
When you take a tablespace offline with the IMMEDIATE clause, Oracle does not perform a checkpoint and does not make sure that all data files are available. You must perform a media recovery when the tablespace is brought online.
Ans: B, D.


20. Which format strings does Oracle use to generate OMF file names? (Choose three.)


A. %s
B. %t
C. %g
D. %a
E. %u
F. %%
---
The data file names have the format ora_%t_%u.dbf, redo log files have the format ora_%g_%u.log, and control files have the format ora_%u.ctl. %t is the tablespace name and can be a maximum of 8 characters, %u is an 8-character unique string, and %g is the redo log group number.
Ans: B, C, E.
Posted by yds at 8:21 PM 0 comments
Labels:

Data Concurrency and Consistency

Data Concurrency and Consistency

Data Concurrency
- Many users can access data at the same time.

Data consistency
- Each user sees a consistent view of the data, including visible changes made
by the user's own transactions and transactions of other users.


The three preventable phenomena are:
- Dirty reads: A transaction reads data that has been written by another transaction that has not been committed yet.
- Nonrepeatable(fuzzy) reads: A transacton rereads data it has previously read and finds that another committed transaction has modified or deleted the data.
- Phantom reads: A transaction re-executes a query returing a set of rows that satisfies a search condition and finds that another committed transaction has inserted additional rows that satisfy the condition


SQL92 defines four levels of isolation


---------------------------------------------------------------------
Isolation Level     Dirty Read      Nonrepeatable Read   Phantom Read
---------------------------------------------------------------------
Read uncommitted    Possible        Possible             Possible
Read committed      Not possible    Possible             Possible
Repeatable read     Not possible    Not possible         Possible
Serializable        Not possible    Not possible         Not possible
----------------------------------------------------------------------



Oracle offers
- the read committed and
- serializable isolation

Oracle Isolation Levels
------------------------
- Read Committed
-- default
-- Each query executed by a transaction sees only data that was committed before
the query (not the transaction) began. An Oracle query never reads dirty (uncommitted) data.

- Serializable
-- Serializable transactions see only those changes that were committed at the time the transaction began, plus those changes made by the transaction itself through INSERT, UPDATE, and DELETE statements.

- Read-only
Posted by yds at 1:59 PM 0 comments
Labels:

Sunday, August 20, 2006

Fundamentals I -Ch5 - Control and Redo Log Files

Chapter 5 - Control and Redo Log Files


OCA/OCP: Oracle9i DBA Fundamentals I Study Guide
by Biju Thomas and Bob Bryla



Review Questions


1. Which method is best for renaming a control file?


A. Use the ALTER DATABASE RENAME FILE command.
B. Shut down the database, rename the control file by using an operating system command, and restart the database after changing the CONTROL_FILES parameter in the initialization parameter file.
C. Put the database in RESTRICTED mode and issue the ALTER DATABASE RENAME FILE command.
D. Shut down the database, change the CONTROL_FILES parameter, and start up the database.
E. Re-create the control file using the new name.

To rename (or multiplex, or drop) a control file, you shut down the database, rename (or copy, or delete) the control file by using operating systems commands, change the parameter CONTROL_FILES in the initialization parameter file, and start up the database.
Ans: B.



2. Which piece of information is not available in the control file?


A. Instance name
B. Database name
C. Tablespace names
D. Log sequence number
----
The instance name is not in the control file. The control file has information about the physical database structure.
Ans: A.


3. When you create a control file, the database has to be:


A. Mounted
B. Not mounted
C. Open
D. Restricted
-----
The database should be in the NOMOUNT state to create a control file. When you mount the database, Oracle tries to open the control file to read the physical database structure.
Ans: B.


4. Which data dictionary view provides the names of the control files?


A. V$DATABASE
B. V$INSTANCE
C. V$CONTROLFILESTATUS
D. None of the above
----
The V$CONTROLFILE view shows the names of the control files in the database.
Ans: D.


5. The initialization parameter FAST_START_MTTR_TARGET has been set to 500. What does this mean?


A. A checkpoint will occur every 500 seconds regardless of database activity.
B. A checkpoint will occur after writing 500 blocks to the redo log file.
C. Upon instance startup, the database will not be ready for connections for at least 500 seconds while instance recovery is performed by SMON.
D. Recovery from an instance failure should not take more than 500 seconds.
---
Oracle will automatically adjust other parameters (buffer sizes, intervals, and so on) to ensure that instance recovery will not exceed a specified number of seconds.
Ans: D.


6. Which data dictionary view shows that the database is in ARCHIVELOG mode?


A. V$INSTANCE
B. V$LOG
C. V$DATABASE
D. V$THREAD
---
The V$DATABASE view shows whether the database is in ARCHIVELOG mode or in NOARCHIVELOG mode.
Ans: C.


7. What is the biggest advantage of having the control files on different disks?


A. Database performance.
B. Guards against failure.
C. Faster archiving.
D. Writes are concurrent, so having control files on different disks speeds up control file writes.
-----
Having the control files on different disks ensures that even if you lose one disk, you lose only one control file. If you lose one of the control files, you can shut down the database, copy a control file, or change the CONTROL_FILES parameter and restart the database.
Ans: B.


8. Which file records all changes made to the database and is used only when recovering an instance?


A. Archive log file
B. Redo log file
C. Control file
D. Alert log file
----
The redo log file records all changes made to the database. The LGWR process writes the redo log buffer entries to the redo log files. These entries are used to roll forward, or to update, the data files during an instance recovery. Archive log files are used for media recovery.
Ans: B.


9. What will happen if ARCn could not write to a mandatory archive destination?


A. The database will hang.
B. The instance will shut down.
C. ARCn starts writing to LOG_ARCHIVE_DUPLEX_DEST if it is specified.
D. Oracle stops writing the archived log files.
----
Oracle will write a message to the alert file, and all database operations will be stopped. Database operation resumes automatically after successfully writing the archived log file. If the archive destination becomes full, you can make room for archives either by deleting the archive log files after copying them to a different location or by changing the parameter to point to a different archive location.
Ans: A.


10. How many ARCn processes can be associated with an instance?


A. Five
B. Four
C. Ten
D. Operating system dependent
----
You can have a maximum of ten archiver processes.
Ans: C.



11. Which of the following is an invalid status code in the V$LOGFILE view?


A. STALE
B. Blank
C. ACTIVE
D. INVALID
----

The STATUS column in V$LOGFILE can have the values INVALID (file is not accessible), STALE (file’s contents are incomplete), DELETED (file is no longer used), or blank (file is in use).

Ans: C.


12. If you have two redo log groups with four members each, how many disks does Oracle recommend to keep the redo log files?


A. Eight
B. Two
C. One
D. Four
----
Oracle recommends that you keep each member of a redo log group on a different disk. You should have a minimum of two redo log groups, and it is recommended that you have two members in each group. The maximum number of redo log groups is determined by the MAXLOGFILES database parameter. The MAXLOGMEMBERS database parameter specifies the maximum number of members per group.
Ans: D.


13. What will happen if you issue the following command?


ALTER DATABASE ADD LOGFILE
(‘/logs/file1’ REUSE, ‘/logs/file2’ REUSE);

A. Statement will fail, because the group number is missing
B. Statement will fail, because log file size is missing
C. Creates a new redo log group, with two members
D. Adds two members to the current redo log group
----
The statement creates a new redo log group with two members. When you specify the GROUP option, you must use an integer value. Oracle will automatically generate a group number if the GROUP option is not specified. Use the SIZE option if you are creating a new file. Use the REUSE option if the file already exists.

Ans: C.


14. Which two parameters cannot be used together to specify the archive destination?


A. LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST
B. LOG_ARCHIVE_DEST and LOG_ARCHIVE_DEST_1
C. LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_DEST_2
D. None of the above; you can specify all the archive destination parameters with valid destination names.
-----
When using a LOG_ARCHIVE_DEST_n parameter, you cannot use the LOG_ARCHIVE_DEST or LOG_ARCHIVE_DUPLEX_DEST parameters to specify other archive locations. Using a LOG_ARCHIVE_DEST_n parameter, you can specify as many as five archiving locations.
Ans: B.


15. Which of the following statements is NOT true regarding the use of OMF for redo logs?


A. Dropping log files with OMF automatically drops the related operating system file.
B. OMF manages archived redo log files using the initialization parameter DB_CREATE_ARCHIVE_LOG_DEST_n.
C. A new log file group can be added without specifying a filename in the ALTER DATABASE statement.
D. A log file group managed with OMF can be dropped by specifying only the log group number.
----
You canot manage archived redo logs with OMF.
Ans: B.


16. Querying which view will show whether automatic archiving is enabled?


A. V$ARCHIVE_LOG
B. V$DATABASE
C. V$PARAMETER
D. V$LOG
---
You enable automatic archiving by setting the initialization parameter LOG_ARCHIVE_START = TRUE. All the parameter values can be queried using the V$PARAMETER view. The ARCHIVE LOG LIST command will also show whether automatic archiving is enabled.
Ans: C.


17. If you need to have your archive log files named with the log sequence numbers as arch_0000001, arch_0000002, and so on (zero filled, fixed width), what should be the value of the LOG_ARCHIVE_FORMAT parameter?


A. arch_%S
B. arch_%s
C. arch_000000%s
D. arch_%0%s
----
Four formatting variables are available to use with archive log file names: %s specifies the log sequence number; %S specifies the log sequence number, leading zero filled; %t specifies the thread; and %T specifies the thread, leading zero filled.
Ans: A.


18. Following are the steps needed to rename a redo log file. Order them in the proper sequence.


A. Use an operating system command to rename the redo log file.
B. Shut down the database.
C. ALTER DATABASE RENAME FILE ‘oldfile’ TO ‘newfile’
D. STARTUP MOUNT
E. ALTER DATABASE OPEN
F. Backup the control file.
----
Ans: B, A, D, C, E, and F. The correct order is:

Shut down the database.
Use an operating system command to rename the redo log file.
STARTUP MOUNT
ALTER DATABASE RENAME FILE ‘oldfile’ TO ‘newfile’
ALTER DATABASE OPEN
Back up the control file.


19. Which of the following commands is a key step in multiplexing redo log files using an SPFILE?


A.
ALTER SYSTEM SET CONTROL_FILES= ‘/u01/oradata/PRD/cntrl01.ctl’, ‘/u01/oradata/PRD/cntrl02.ctl’ SCOPE=SPFILE;

B.
ALTER SYSTEM SET CONTROL_FILES=
‘/u01/oradata/PRD/cntrl01.ctl’,
‘/u01/oradata/PRD/cntrl02.ctl’ SCOPE=MEMORY;

C.
ALTER SYSTEM SET CONTROL_FILES=
‘/u01/oradata/PRD/cntrl01.ctl’,
‘/u01/oradata/PRD/cntrl02.ctl’ SCOPE=BOTH;

D.
The number of control files is fixed when the database is created.
-----
The location of the new control files is not valid until an operating system copy is made of the current control file to the new location(s), and the instance is restarted. The SCOPE=SPFILE option specifies that the parameter change will not take place until a restart. Specifying either MEMORY or BOTH will cause an error, since the new control file does not exist yet.
Ans: A.


20. Which statement will add a member /logs/redo22.log to log file group 2?


A. ALTER DATABASE ADD LOGFILE ‘/logs/redo22.log’ TO GROUP 2;
B. ALTER DATABASE ADD LOGFILE MEMBER ‘/logs/redo22.log’ TO GROUP 2;
C. ALTER DATABASE ADD MEMBER ‘/logs/redo22.log’ TO GROUP 2;
D. ALTER DATABASE ADD LOGFILE ‘/logs/redo22.log’;
---
When adding log file members, specify the group number or specify all the existing group members. Option D would create a new group with one member.
Ans: B.
Posted by yds at 4:18 PM 0 comments
Labels:

Saturday, August 19, 2006

Fundamentals I Ch4 - Creating a Database and Data Dictionary

Chapter 4 - Creating a Database and Data Dictionary

OCA/OCP: Oracle9i DBA Fundamentals I Study Guide


Review Questions


1. How many control files are required to create a database?


A. One
B. Two
C. Three
D. None
---
You do not need any control files to create a database; the control files are created when you create the database, based on the filenames specified in the CONTROL_FILES parameter of the parameter file.
Ans: D.


2. Which environment variable or registry entry variable represents the instance name?


A. ORA_SID
B. INSTANCE_NAME
C. ORACLE_INSTANCE
D. ORACLE_SID
----
Ans D. The ORACLE_SID environment variable represents the instance name. When you connect to the database without specifying a connect string, Oracle connects you to this instance.


3. Complete the following sentence: The recommended configuration for control files is


A. One control file per database
B. One control file per disk
C. Two control files on two disks
D. Two control files on one disk
----
Ans: C. Oracle allows multiplexing of control files. If you have two control files on two disks, one disk failure will not damage both control files.


4. You have specified the LOGFILE clause in the CREATE DATABASE command as follows. What happens if the size of the log file redo0101.log, which already exists, is 10MB?


LOGFILE GROUP 1
    (‘/oradata02/PROD01/redo0101.log’,
     ‘/oradata03/PROD01/redo0102.log) SIZE 5M REUSE,
    GROUP 2
    (‘/oradata02/PROD01/redo0201.log’,
     ‘/oradata03/PROD01/redo0202.log) SIZE 5M REUSE


A. Oracle adjusts the size of all the redo log files to 10MB.
B. Oracle creates all the redo log files as 5MB.
C. Oracle creates all the redo log files as 5MB except redo0101.log, which is created as 10MB.
D. The command fails.
----
The CREATE DATABASE command succeeds, and sets all log files to a size of 5M, including the log file that already exists. If a particular log file does not exist, the REUSE clause is ignored and a new file with a size of 5M is created. If a file already exists, you must specify REUSE.
Ans: B.


5. Which command must you issue before you can execute the CREATE DATABASE command?


A. STARTUP INSTANCE
B. STARTUP NOMOUNT
C. STARTUP MOUNT
D. None of the above
----
You must start up the instance to create the database. Connect to the database by using the SYSDBA privilege, and start up the instance by using the command STARTUP NOMOUNT.
Ans: B.


6. Which initialization parameter cannot be changed after creating the database?


A. DB_BLOCK_SIZE
B. DB_NAME
C. CONTROL_FILES
D. None; all the initialization parameters can be changed as and when required.
----
The block size of the database cannot be changed after database creation. The database name can be changed after re-creating the control file with the new name, and the CONTROL_FILES parameter can be changed if the files are copied to a new location.
Ans: A.


7. Which of the following objects or structures can be added or removed from a DBCA template? (Choose three.)

A. Tablespaces
B. File destinations
C. Datafiles
D. Control files
E. Log file groups
----
In addition to tablespaces and data files, undo segments and initialization parameters must also remain exactly the same as defined in the template.
Ans: B, D, E.


8. When you are creating a database, where does Oracle find information about the control files that need to be created?


A. From the initialization parameter file
B. From the CREATE DATABASE command line
C. From the environment variable
D. Files created under $ORACLE_HOME and name derived from .ctl
----
The control file names and locations are obtained from the initialization parameter file. The parameter name is CONTROL_FILES. If this parameter is not specified, Oracle creates a control file; the location and name depend on the operating system platform.
Ans: A.


9. Which script creates the data dictionary views?


A. catalog.sql
B. catproc.sql
C. sql.bsq
D. dictionary.sql
----
The catalog.sql script creates the data dictionary views. The base tables for these views are created by the script sql.bsq, which is executed when you issue the CREATE DATABASE command.
Ans: A.


10. Which prefix for the data dictionary views indicates that the contents of the view belong to the current user?


A. ALL_

B. DBA_

C. USR_

D. USER_
----
DBA_ prefixed views are accessible to the DBA or anyone with the SELECT_CATALOG_ROLE privilege; these views provide information on all the objects in the database and have an OWNER column. The ALL_ views show information about the structures that the user has access to. USER_ views show information about the structures owned by the user.
Ans: D.


11. Which data dictionary view shows information about the status of a procedure?


A. DBA_SOURCE
B. DBA_OBJECTS
C. DBA_PROCEDURES
D. DBA_STATUS
----
The DBA_OBJECTS dictionary view contains information on the objects, their creation, and modification timestamp and status.
Ans: B.


12. How do you correct a procedure that has become invalid when one of the tables it is referring to was altered to drop a constraint?


A. Re-create the procedure
B. ALTER PROCEDURE RECOMPILE
C. ALTER PROCEDURE COMPILE
D. VALIDATE PROCEDURE
----
The invalid procedure, trigger, package, or view can be recompiled by using the ALTER COMPILE command.
Ans: C


13. Which of the following views does not have information about the operating system locations of the components?


A. V$CONTROLFILE
B. V$DATAFILE
C. V$PWFILE_USERS
D. V$LOGFILE
E. V$TEMPFILE
----
The view V$PWFILE_USERS contains the list of users that have SYSDBA and SYSOPER rights; however, the password file is not a part of the database. The database consists of data files, log files, and control files.
Ans: C.


14. How many data files can be specified in the DATAFILE clause when creating a database?

A. One.
B. Two.
C. More than one; only one will be used for the SYSTEM tablespace.
D. More than one; all will be used for the SYSTEM tablespace.
---
You can specify more than one data file; the files will be used for the SYSTEM tablespace. The files specified cannot exceed the number of data files specified in the MAXDATAFILES clause.
Ans D.

notes: is 'C' the correct answer? How about the DATAFILE clause for UNDO tablespacew
 
DATAFILE ‘/oradata01/PROD01/system01.dbf’ SIZE 80M
     AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
UNDO TABLESPACE UNDOTBS
DATAFILE ‘/oradata04/PROD01/undo01.dbf’ SIZE 35M
DEFAULT TEMPORARY TABLESPACE TEMP
TEMPFILE ‘/oradata05/PROD01/temp01.dbf’ SIZE 20M;



15. Who owns the data dictionary?


A. SYS
B. SYSTEM
C. DBA
D. ORACLE
----
The SYS user owns the data dictionary. The SYS and SYSTEM users are created when the database is created.
Ans: A.


16. What is the default password for the SYS user?


A. MANAGER
B. CHANGE_ON_INSTALL
C. SYS
D. There is no default password.
----
The default password for SYS is CHANGE_ON_INSTALL, and for SYSTEM it is MANAGER. You should change these passwords once the database is created.
Ans: B


17. Which data dictionary view provides information about the version of the database and installed components?


A. DBA_VERSIONS
B. PRODUCT_COMPONENT_VERSION
C. PRODUCT_VERSIONS
D. ALL_VERSION
----
The dictionary view PRODUCT_COMPONENT_VERSION shows information about the database version. The view V$VERSION has the same information.
Ans: B.


18. What is the prefix for dynamic performance views?


A. DBA_
B. X$
C. V$
D. X#
---
Ans: C. The dynamic performance views have a prefix of V$. The actual views have the prefix of V_$, and the synonyms have a V$ prefix. The views are called dynamic performance views because they are continuously updated while the database is open and in use, and their contents relate primarily to performance.


19. Which is an invalid clause in the CREATE DATABASE command?


A. MAXLOGMEMBERS
B. MAXLOGGROUPS
C. MAXDATAFILES
D. MAXLOGHISTORY
---
MAXLOGGROUPS is an invalid clause; the maximum log file groups are specified using the clause MAXLOGFILES.
Ans: B.


20. Which database underlying table can be updated directly by the DBA without severe consequences to the operation of the database?

A. AUD$
B. LINK$
C. sql.bsq
D. DICT
E. HELP
----
Ans: A. AUD$ contains records that audit DML operations against the database. No other base tables should be modified directly and should rarely be accessed read-only other than through a data dictionary view. sql.bsq is a script, not a table; DICT is a synonym for the DICTIONARY view. LINK$ and HELP are base tables.
Posted by yds at 3:41 PM 0 comments
Labels:

Friday, August 18, 2006

Fundamentals I Ch3 - Installing and Managing Oracle

Chapter 3 - Installing and Managing Oracle


Review Questions



1. Which of the following is an invalid database start-up option?


A. STARTUP NORMAL
B. STARTUP MOUNT
C. STARTUP NOMOUNT
D. STARTUP FORCE
----

STARTUP NORMAL is an invalid option; to start the database, you issue the STARTUP command without any options or with STARTUP OPEN.
Ans: A.


2. Which two values from the V$SESSION view are used to terminate a user session?


A. SID
B. USERID
C. SERIAL#
D. SEQUENCE#
----
SID and SERIAL# are used to kill a session. You can query the V$SESSION view to obtain these values. The command is ALTER SYSTEM KILL SESSION ‘, ’ .
Ans: A, C



3. To use operating system authentication to connect to the database as an administrator, what should the value of the parameter REMOTE_LOGIN_PASSWORDFILE be set to?


A. SHARED
B. EXCLUSIVE
C. NONE
D. OS

-----
The value of the REMOTE_LOGIN_PASSWORDFILE parameter should be set to NONE to use OS authentication. To use password file authentication, the value should be either EXCLUSIVE or SHARED.
Ans: C.


4. What information is available in the alert log files?


A. Block corruption errors
B. Users connecting and disconnecting from the database
C. All user errors
D. The default values of the parameters used to start up the database

-----
The alert log stores information about block corruption errors, internal errors,
and the non-default initialization parameters used at instance start-up.
The alert log also records information about database start-up, shutdown, archiving, recovery, tablespace modifications, undo segment modifications, and data file modifications.
Ans: A.


5. Which parameter value is used to set the directory path where the alert log file is written?


A. ALERT_DUMP_DEST
B. USER_DUMP_DEST
C. BACKGROUND_DUMP_DEST
D. CORE_DUMP_DEST
----
The alert log file is written in the BACKGROUND_DUMP_DEST directory.
This directory also records the trace files generated by the background processes.
The USER_DUMP_DEST directory has the trace files generated by user sessions.
The CORE_DUMP_DEST directory is used primarily on Unix platforms to save the core dump files.
ALERT_DUMP_ DEST is not a valid parameter.
Ans: C.


6. Which SHUTDOWN option requires instance recovery when the database is started the next time?

A. SHUTDOWN IMMEDIATE
B. SHUTDOWN TRANSACTIONAL
C. SHUTDOWN NORMAL
D. None of the above
---
SHUTDOWN ABORT requires instance recovery when the database is started the next time. Oracle will also roll back uncommitted transactions during start-up. This option shuts down the instance without dismounting the database.
Ans: D.


7. Which SHUTDOWN option will wait for the users to complete their uncommitted transactions?


A. SHUTDOWN IMMEDIATE
B. SHUTDOWN TRANSACTIONAL
C. SHUTDOWN NORMAL
D. SHUTDOWN ABORT
----
When SHUTDOWN TRANSACTIONAL is issued, Oracle waits for the users to either commit or roll back their pending transactions. Once all users have either rolled back or committed their transactions, the database is shut down. When using SHUTDOWN IMMEDIATE, the user sessions are disconnected and the changes are rolled back. SHUTDOWN NORMAL waits for the user sessions to disconnect from the database.
Ans: B.


8. How do you make a database read-only? (Choose the best answer.)

A. STARTUP READ ONLY
B. STARTUP MOUNT; ALTER DATABASE OPEN READ ONLY
C. STARTUP NOMOUNT; ALTER DATABASE READ ONLY
D. STARTUP; ALTER SYSTEM ENABLE READ ONLY
---
To put a database into read-only mode, you can mount the database and open the database in read-only mode. This can be accomplished in one step by using STARTUP OPEN READ ONLY.
Ans: B.


9. Which role is created by default to administer databases?

A. DATABASE_ADMINISTRATOR
B. SUPER_USER
C. DBA
D. No such role is created by default; you need to create administrator roles after logging in as SYS.

----
The DBA role is created when you create the database and is assigned to the SYS and SYSTEM users.
Ans: C.


10. Which parameter in the ORAPWD utility is optional?


A. FILE
B. PASSWORD
C. ENTRIES
D. All the parameters are optional; if you omit a parameter, Oracle substitutes the default.
----
The parameter ENTRIES is optional. You must specify a password file name and the SYS password. The password file created will be used for authentication.
Ans: C.


11. Which privilege do you need to connect to the database, if the database is started up by using STARTUP RESTRICT?


A. ALTER SYSTEM
B. RESTRICTED SESSION
C. CONNECT
D. RESTRICTED SYSTEM
----
The RESTRICTED SESSION privilege is required to access a database that is in restricted mode. You start up the database in restricted mode by using STARTUP RESTRICT, or you change the database to restricted mode by using ALTER SYSTEM ENABLE RESTRICTED SESSION.
Ans: B.

Notes: when do you what to use STARTUP RESTRICT?



12. At which stage of the database start-up is the control file opened?

A. Before the instance start-up
B. Instance started
C. Database mounted
D. Database opened

----
The control file is opened when the instance mounts the database. The data files and redo log files are opened after the database is opened. When the instance is started, the background processes are started.
Ans: C


13. User SCOTT has opened a SQL*Plus session and left for lunch. When you queried the V$SESSION view, the STATUS was INACTIVE. You terminated SCOTT’s session. What will be the status of SCOTT’s session in V$SESSION?

A. INACTIVE
B. There will be no session information in V$SESSION view
C. TERMINATED
D. KILLED
----
When you terminate a session that is INACTIVE, the STATUS in V$SESSION will show as KILLED. When SCOTT tries to perform any database activity in the SQL*Plus window, he receives an error that his session is terminated. When an ACTIVE session is killed, the changes are rolled back and an error message is written to the user’s screen.
Ans: D.


14. Which command will "bounce" the database - that is, shut down the database and start up the database in a single command?

A. STARTUP FORCE
B. SHUTDOWN FORCE
C. SHUTDOWN START
D. There is no single command to “bounce” the database; you need to shut down the database and then restart it
---
STARTUP FORCE will terminate the current instance and start up the database. It is equivalent to issuing SHUTDOWN ABORT and STARTUP OPEN.
Ans: A.


15. When performing the command SHUTDOWN TRANSACTIONAL, Oracle performs the following tasks in what order?


A. Terminates the instance
B. Performs a checkpoint
C. Closes the data files and redo log files
D. Waits for all user transactions to complete
E. Dismounts the database
F. Closes all sessions
----
Ans: D, F, B, C, E, and A. SHUTDOWN TRANSACTIONAL waits for all user transactions to complete. Once no transactions are pending, it disconnects all sessions and proceeds with the normal shutdown process. The normal shutdown process performs a checkpoint, closes data files and redo log files, dismounts the database, and shuts down the instance.


16. What is the primary benefit of using an SPFILE to maintain the parameter file?

A. The SPFILE can be mirrored across several drives, unlike PFILEs.
B. Changes to the database configuration can be made persistent across shutdown and startup.
C. Because the SPFILE is binary, the DBA will be less likely to edit it.
D. The ALTER SYSTEM command cannot modify the contents of an SPFILE.
----
Using the ALTER SYSTEM command, the changes can be made to the current (MEMORY) configuration, to the next restart (SPFILE), or to both (BOTH).
Ans: B.


17. Using SQL*Plus, which two options below will display the value of the parameter DB_BLOCK_SIZE?

A. SHOW PARAMETER DB_BLOCK_SIZE
B. SHOW PARAMETERS DB_BLOCK_SIZE
C. SHOW ALL
D. DISPLAY PARAMETER DB_BLOCK_SIZE

---
The SHOW PARAMETER command will display the current value of the parameter. If you provide the parameter name, its value is displayed; if you omit the parameter name, all the parameter values are displayed. SHOW ALL in SQL*Plus will display the SQL*Plus environment settings, not the parameters.
Ans: A and B.


18. When you issue the command ALTER SYSTEM ENABLE RESTRICTED SESSION, what happens to the users who are connected to the database?

A. The users with DBA privilege remain connected, and others are disconnected.
B. The users with RESTRICTED SESSION remain connected, and others are disconnected.
C. Nothing happens to the existing users. They can continue working.
D. The users are allowed to complete their current transaction and are disconnected.
----
If you enable RESTRICTED SESSION when users are connected, nothing happens to the already connected sessions. Future sessions are started only if the user has the RESTRICTED SESSION privilege.
Ans. C


19. Which view has information about users who are granted SYSDBA or SYSOPER privilege?

A. V$PWFILE_USERS
B. DBA_PWFILE_USERS
C. DBA_SYS_GRANTS
D. None of the above
----

The dynamic view V$PWFILE_USERS has the username and a value of TRUE in column SYSDBA if the SYSDBA privilege is granted, or a value of TRUE in column SYSOPER if the SYSOPER privilege is granted.
Ans: A.


20. Which of the following initialization parameters is NOT used in OMF operations?

A. DB_CREATE_FILE_DEST
B. DB_CREATE_FILE_DEST_2
C. DB_CREATE_ONLINE_LOG_DEST_1
D. DB_CREATE_ONLINE_LOG_DEST_5
---
Only one file destination is allowed. Control files and redo log files use the same parameter; the parameter DB_CREATE_ONLINE_LOG_ DEST_n (n can have values from 1 to 5).
Ans B.
Posted by yds at 4:15 PM 0 comments
Labels:

Fundamentals I Ch2 - Oracle Overview and Architecture

Chapter 2 - Oracle Overview and Architecture

OCA/OCP: Oracle9i DBA Fundamentals I Study Guide
by Biju Thomas and Bob Bryla


Review Questions


1. Which component is not part of the Oracle instance?

A. System Global Area
B. Process monitor
C. Control file
D. Shared pool
---
The Oracle instance consists of memory structures and background processes. The Oracle database consists of the physical components such as data files, redo log files, and the control file. The System Global Area and shared pool are memory structures. The process monitor is a background process.
Ans: C.


2. Which background process and associated database component guarantee that committed data is saved even when the changes have not been recorded in the data files?
A. DBWn and database buffer cache
B. LGWR and online redo log file
C. CKPT and control file
D. DBWn and archived redo log file

----
The LGWR process writes the redo log buffer entries when a COMMIT occurs. The redo log buffer holds information on the changes made to the database. The DBWn process writes dirty buffers to the data file, but it is independent of the COMMIT. The dirty buffers can be written to the disk before or after a COMMIT. Writing the committed changes to the online redo log file ensures that the changes are never lost in case of a failure.
Ans: B



3. What is the maximum number of database writer processes allowed in an Oracle instance?

A. 1
B. 10
C. 256
D. Limit specified by an operating system parameter

----
By default, every Oracle instance has one database writer process: DBW0.
Additional processes can be started by setting the initialization parameter DB_WRITER_PROCESSES(DBW1 through DBW9).
Ans: B.


4. Which background process is not started by default when you start up the Oracle instance?

A. DBWn

B. LGWR

C. CKPT

D. ARCn


5. Which of the following best describes a Real Application Cluster configuration?

A. One database, multiple instances
B. One instance, multiple databases
C. Multiple databases on multiple servers
D. Shared server process takes care of multiple user processes


6. Choose the correct hierarchy, from largest to smallest, from this list of logical database structures.
A. Database, tablespace, extent, segment, block
B. Database, tablespace, segment, extent, block
C. Database, segment, tablespace, extent, block
D. Database, extent, tablespace, segment, block


7. Which component of the SGA contains the parsed SQL code?


A. Buffer cache
B. Dictionary cache
C. Library cache
D. Parse cache


8. Julie, one of the database analysts, is complaining that her queries are taking longer and longer to complete, although they seem to produce the correct results. The DBA suspects that the buffer cache is not sized correctly and is causing delays due to data blocks not being available in memory. Which initialization parameter should the DBA use to monitor the usage of the buffer cache?

A. BUFFER_POOL_ADVICE
B. DB_CACHE_ADVICE
C. DB_CACHE_SIZE
D. SHARED_POOL_SIZE
----
The parameter DB_CACHE_ADVICE can be set to YES to enable cache usage monitoring. DB_CACHE_SIZE and SHARED_POOL_SIZE are sizing parameters for SGA structures; the parameter BUFFER_POOL_ ADVICE does not exist.
Ans: B.


9. Which background process is responsible for writing the dirty buffers to the database files?

A. DBWn
B. SMON
C. LGWR
D. CKPT
E. PMON


10. Which component in the SGA has the dictionary cache?

A. Buffer cache
B. Library cache
C. Shared pool
D. Program Global Area
E. Large pool


11. When a server process is terminated abnormally, which background process is responsible for releasing the locks held by the user?


A. DBWn
B. LGWR
C. SMON
D. PMON

---
D. PMON, or the process monitor, is responsible for cleaning up failed user processes. It reclaims all resources held by the user and releases all locks on tables and rows held by the user.
Ans: D


12. What is a dirty buffer?

A. Data buffer that is being accessed
B. Data buffer that is changed but is not written to the disk
C. Data buffer that is free
D. Data buffer that is changed and written to the disk
---
Dirty buffers are the buffer blocks that need to be written to the data files. The data in these buffers has changed and is not yet written to the disk. A block waiting to be written to disk is on the dirty list and cannot be overwritten.
Ans: B


13. If you are updating one row in a table using the ROWID in the WHERE clause (assume that the row is not already in the buffer cache), what will be the minimum amount of information read to the database buffer cache?

A. The entire table is copied to the database buffer cache.
B. The extent is copied to the database buffer cache.
C. The block is copied to the database buffer cache.
D. The row is copied to the database buffer cache.
---
The block is the smallest unit that can be copied to the buffer cache.
Ans: C.


14. What happens next when a server process is not able to find enough free buffers to copy the blocks from disk?

A. Signals the CKPT process to clean up the dirty buffers
B. Signals the SMON process to clean up the dirty buffers
C. Signals the CKPT process to initiate a checkpoint
D. Signals the DBWn process to write the dirty buffers to disk
---
To reduce disk I/O contention, the DBWn process does not write the changed buffers immediately to the disk. They are written only when the dirty buffers reach a threshold, when there are not enough free buffers available, or when the checkpoint occurs.
Ans: D.


15. Which memory structures are shared? Choose two.

A. Sort area
B. Program Global Area
C. Library cache
D. Large pool
---
The sort area is allocated to the server process as part of the PGA. The PGA is allocated when the server process starts and is de- allocated when the server process completes. The library cache and the large pool are part of the SGA and are shared. The SGA is created when the instance starts.
Ans: C and D.


16. Which of the following initialization parameters does NOT determine the size of the buffer cache?


A. DB_KEEP_CACHE_SIZE
B. DB_CACHE_SIZE
C. DB_BLOCK_SIZE
D. DB_RECYCLE_CACHE_SIZE
----
The parameter DB_BLOCK_SIZE does not change the size of the buffer cache. It changes only the size of each Oracle block written to and read from disk.
Ans: C.


17. Which memory structure records all database changes made to the instance?

A. Database buffer
B. Dictionary cache
C. Redo log buffer
D. Library cache
-----
The redo log buffer keeps track of all changes made to the database before writing them to the redo log files. The database buffer contains the data blocks that are read from the data files, and are most recently used. The dictionary cache holds the most recently used data dictionary information. The library cache holds the parsed SQL statements and PL/SQL code.
Ans: C.


18. What is the minimum number of online redo log files required in a database?

A. One
B. Two
C. Four
D. Zero


19. When are the system change numbers assigned?


A. When a transaction begins
B. When a transaction ends abnormally
C. When a checkpoint occurs
D. When a COMMIT is issued
----
A system change number (SCN) is assigned when the transaction is committed. The SCN is a unique number acting as an internal time- stamp, used for recovery and read-consistent queries.
Ans: D.


20. Which of the following is not part of the database buffer pool?

A. KEEP
B. RECYCLE
C. LIBRARY
D. DEFAULT
----
There is no database buffer cache named LIBRARY. The DBA can configure multiple buffer pools by using the appropriate initialization parameters for performance improvements. The KEEP buffer pool retains the data blocks in memory; they are not aged out. The RECYCLE buffer pool removes the buffers from memory as soon as they are not needed. The DEFAULT buffer pool contains the blocks that are not assigned to the other pools.
Ans: C


21. Memory granules are not allocated at instance startup for which of the following SGA components?

A. Database buffer cache
B. Shared pool
C. Redo log buffers
D. Large pool
E. None of the above
----
All of these SGA components are allocated in granule units. A minimum of three granules are allocated for the SGA at instance startup: one for the fixed portion of the SGA, one for the database buffer cache, and one for the shared pool.
Ans: E.

$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

Other Answers


4. D. ARCn is the archiver process, which is started only when the LOG_ARCHIVE_START initialization parameter is set to TRUE. DBWn, LGWR, CKPT, SMON, and PMON are the default processes associated with all instances.

5. A. In a Real Application Cluster configuration, multiple instances (known as nodes) can mount one database. One instance can be associated with only one database. In a multithreaded configuration, one shared server process takes requests from multiple user processes.

6. B. The first level of logical database structure is the tablespace. A tablespace may have segments, segments have one or more extents, and extents have one or more contiguous blocks.

7. C. The library cache contains the parsed SQL code. If a query is executed again before it is aged out of the library cache, Oracle will use the parsed code and execution plan from the library cache. The buffer cache has data blocks that are cached. The dictionary cache caches data dictionary information. There is no SGA component named parse cache.

9. A. The DBWn process writes the dirty buffers to the data files under two circumstances—when a checkpoint occurs or when the server process searches the buffer cache for a set threshold.

10. C. The shared pool has three components: the library cache, the dictionary cache, and the control structures.

18. B. There should be at least two redo log files in a database. The LGWR process writes to the redo log files in a circular manner, so there should be at least two files.
Posted by yds at 1:09 PM 0 comments
Labels:

Thursday, August 17, 2006

Fundamentals I Ch1- New Features for Administrators

Chapter 1 - Oracle9i New Features for Administrators
OCA/OCP: Oracle9i DBA Fundamentals I Study Guide
by Biju Thomas and Bob Bryla


Review Questions ( 14 total)


1. Which new feature of Oracle9i allows users to view the contents of a table at some point in the past?


A. LogMiner
B. Import
C. Metadata Viewer
D. Oracle Flashback
---

Ans D. The package DBMS_FLASHBACK allows the user to view the contents of a table or tables at a specified time in the past.


2. Choose the statement below that is true regarding enhancements to shared SQL statements in the shared pool.


A. The cursor sharing feature can re-use a SQL statement even if the columns in the statement are in a different order or the GROUP BY clause is different.

B. The new columns CPU_COST, IO_COST, and TEMP_SPACE in PLAN_ TABLE help the rule-based optimizer (RBO) to be more accurate.

C. Even if the only difference in SQL statements is in the literal values, the SQL statement may not be re-used if the histogram statistics are skewed for a column in the WHERE clause.

D. The CURSOR_SHARING parameter now supports the SIMILAR and DERIVED values.

-----
Ans C. If the execution plan is independent of the literal values used in the query,
it is likely that the query can be re-used. The new columns in the PLAN_TABLE assist the cost-based optimizer,
not the rule-based optimizer, and the CURSOR_SHARING parameter does not have DERIVED as a possible value.


3. Given the table declaration below, identify invalid use of timestamp datatypes in an expression or function. (Choose two.)

CREATE TABLE TRANSACTIONS
(TRANS_ID NUMBER,
AMOUNT NUMBER(10,2),
TRANS_START TIMESTAMP,
TRANS_END TIMESTAMP,
SHIP_DATE DATE,
EXPIRE_DATE INTERVAL DAY(0) TO SECOND(0));


A. TRANS_START - TRANS_END

B. TO_TIMESTAMP(AMOUNT, ‘YY-MM-DD HH:MI:SS’)

C. TRANS_START + INTERVAL ‘4’ DAY

D. TRANS_START + SHIP_DATE
----

Any reasonable combination of date and time data types is allowed. However, date fields cannot be added together, and dollar amounts are not valid arguments to date conversion functions.

Ans: B, D

4. Which of the following operations cannot be performed online without any disruption to ongoing online transactions?


A. Dropping a user-defined column
B. Rebuilding secondary IOT indexes
C. Adding new columns to a heap-based table
D. Rebuilding a primary IOT index
-----
Primary keys cannot be dropped nor can columns with user- defined data types be dropped without making a table unavailable.
Ans: A.


5. Which of the following types of joins are now allowed in the FROM clause of a SQL statement? (Choose all that apply.)



A. cross joins
B. inner joins
C. full outer joins
D. left outer joins
----
Ans: A, B, C, D. All the above joins are now specified in the WHERE clause. Previous versions of Oracle supported all these join types other than the full outer join in the WHERE clause.


6. How many panes exist in the new version of Oracle Enterprise Manager (OEM)?


A. One, with pop-up windows

B. Four, as in previous versions

C. Two, in a master/detail format

D. Two, with DBA tools in the right-hand pane
---
The new OEM not only uses a cleaner, easier-to-use two-pane layout, it integrates all the tools previously available through DBA Studio.
Ans: C


7. The DBA is importing a table and an index from a dump file that was exported from another Oracle9i database. Which options does the DBA have when using the statistics from this dump file? (Choose all that apply.)

A. Explicitly accept all statistics
B. Explicitly reject all statistics
C. Let IMPORT decide if the statistics are safe; otherwise recalculate
D. Accept statistics only for non-partitioned tables
E. Explicitly re-calculate statistics, regardless of whether the original statistics are good or bad
---
Ans A, B, C, E. IMPORT cannot reject statistics based on whether the table is partitioned.


8. The Secure Application Role feature in Oracle9i allows a user to authenticate role privileges by doing which of the following?


A. Calling a stored procedure

B. Using OS authentication

C. Using PWFILE authentication

D. Using an encrypted role password

-----
The stored procedure can restrict access to the role in a number of ways, such as by date and time or by the IP address of the user requesting access to the role.
Ans: A.


9. Chad normally runs queries against very small tables, but has informed the DBA that he will soon be running some queries against the data warehouse tables for the operations manager. What can the DBA do to make sure that these new queries won’t slow down OLTP operations? (Choose the best answer.)

A. The DBA can use the Active Session Pool feature to put Chad’s session on hold until another user in the same consumer group finishes their session.

B. The DBA can use the Automatic Consumer Group Switching feature to switch Chad’s consumer group to the same group as the OLTP users.

C. The DBA can use the Active Session Pool feature to suspend the session if there are too many active OLTP sessions.

D. The DBA can use the Automatic Consumer Group Switching feature to switch Chad’s consumer group to a secondary group that has a lower priority.
----
Ans D. Switching to another consumer group with a lower priority will allow the query to finish while minimizing the impact on the ongoing OLTP transactions.
The Active Session Pool feature controls resource usage within the same consumer group and will not necessarily reduce the contention with OLTP transactions.


10. Which of the following is not an advantage of having the data dictionary in the redo logs when using LogMiner for DML and DDL activity?


A. The LogMiner activity will not impact other users’ activity against the data dictionary.

B. The LogMiner reports will be more accurate against a snapshot of the data dictionary rather than a constantly changing live data dictionary.

C. Bad blocks in one of the redo logs will not stop the LogMiner analysis with a static data dictionary.

D. The database does not need to be open to use LogMiner, since all needed information is in the redo logs.

----
Bad blocks can be ignored in LogMiner; however this feature is independent of where LogMiner retrieves the data dictionary information.
Ans: C.



11. Which of the new RMAN options can the DBA use to save time when a backup does not complete successfully?

A. Restart the backup with the NOT BACKED UP option.

B. Use mirrored backups to send the backup to two different device types.

C. Include the archive logs in the backup.

D. There is no alternative to a failed backup other than to restart the backup.
-----
Running RMAN with the NOT BACKED UP option backs up only the missing or incomplete files.
Ans: A.


12. Identify the true statement regarding binary SPFILEs.

A. All changes to an SPFILE are implemented only after the instance is restarted.

B. Changes made to an SPFILE with the ALTER SYSTEM command can be made simultaneously with the change to the memory copy of the parameter.

C. An SPFILE can exist on the client side.

D. SPFILEs can be used in conjunction with a text-format PFILE.
----
Ans B. The changes to an SPFILE may be made at the same time the change is made to the memory copy of the parameter. SPFILEs only exist on the server side and are created using a PFILE. Once the SPFILE is activated, the PFILE is no longer needed.



13. Place the following block read options in order of access time, shortest to longest.


A. Block is read from remote cache without Cache Fusion

B. Block is read from a local cache

C. Block is read from a remote cache with Cache Fusion

D. Block is read from a shared disk
---
Blocks read from a remote cache without Cache Fusion must be written to the shared disk by the remote instance before the blocks can be retrieved by the local instance.
Ans: B,C, D, A


14. PL/SQL execution is significantly more efficient at runtime for which of the following reasons? (Choose two.)

A. Native C code is generated for PL/SQL procedures.

B. The compiled code resides in the SGA.

C. Byte code is generated by the compiler and therefore can easily be re-used by different transactions.

D. The compiled code resides in the PGA.

---
The compiled code is moved to the PGA to reduce contention on the SGA; interpreted byte code is inherently less efficient to execute than native compiled C code.
Ans: A, D.
Posted by yds at 10:42 PM 0 comments
Labels:

Tuesday, August 15, 2006

Demonstrating FULL, LEFT, RIGHT Join 


SQL> select * from movie;

       MID        DID
---------- ----------
       100          1
       101          2
       102          2
       103          3
       104          1
       105
       106

7 rows selected.

SQL> select * from director;

       DID NAME
---------- --------------------
         1 Jane
         2 Bob
         3 Denis
         4 Jack
         5 Dev
         6 Kay

6 rows selected.

SQL> select * from movie join director  using (did) order by mid;

       DID        MID NAME
---------- ---------- --------------------
         1        100 Jane
         2        101 Bob
         2        102 Bob
         3        103 Denis
         1        104 Jane

SQL> select * from movie left join director  using (did) order by mid;

       DID        MID NAME
---------- ---------- --------------------
         1        100 Jane
         2        101 Bob
         2        102 Bob
         3        103 Denis
         1        104 Jane
                  105
                  106

7 rows selected.

SQL> select * from movie right join director  using (did) order by mid;

       DID        MID NAME
---------- ---------- --------------------
         1        100 Jane
         2        101 Bob
         2        102 Bob
         3        103 Denis
         1        104 Jane
         4            Jack
         5            Dev
         6            Kay

8 rows selected.

SQL> select * from movie full join director  using (did) order by mid;

       DID        MID NAME
---------- ---------- --------------------
         1        100 Jane
         2        101 Bob
         2        102 Bob
         3        103 Denis
         1        104 Jane
                  105
                  106
         4            Jack
         5            Dev
         6            Kay

10 rows selected.
Posted by yds at 4:07 PM 0 comments
Labels: ,

ALL, SOME and subquery -A Test 


SQL> select * from tab1;

         A          B          C
---------- ---------- ----------
         1          2          3
         2          2          3
         3          2          3
         4          4          3

SQL> select * from tab1 where a = (select a from tab1);
select * from tab1 where a = (select a from tab1)
                              *
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row


SQL> select * from tab1 where a = all (select a from tab1);

no rows selected

SQL> select * from tab1 where a = some (select a from tab1);

         A          B          C
---------- ---------- ----------
         1          2          3
         2          2          3
         3          2          3
         4          4          3

SQL> select * from tab1 where a = all (select b from tab1);

no rows selected

SQL> select * from tab1 where a = some (select b from tab1);

         A          B          C
---------- ---------- ----------
         2          2          3
         4          4          3

SQL> select * from tab1 where a = all (select c from tab1);

         A          B          C
---------- ---------- ----------
         3          2          3

SQL> select * from tab1 where a = some (select c from tab1);

         A          B          C
---------- ---------- ----------
         3          2          3

SQL> select * from tab1 where a = some (select b, c from tab1);
select * from tab1 where a = some (select b, c from tab1)
                                   *
ERROR at line 1:
ORA-00913: too many values
Posted by yds at 12:33 PM 1 comments
Labels:

Function TRIM examples 


SQL> select trim('*' from '***comments***') from dual;

TRIM('*'
--------
comments

SQL> select trim(leading '*' from '***comments***') from dual;

TRIM(LEADIN
-----------
comments***

SQL> select trim(trailing '*' from '***comments***') from dual;

TRIM(TRAILI
-----------
***comments

SQL> select trim(both '*' from '***comments***') from dual;

TRIM(BOT
--------
comments
Posted by yds at 9:50 AM 0 comments
Labels:

Monday, August 14, 2006

Real World Scenario: Using a Role to Facilitate Granting Developer Privileges

From:

Chapter 10 - User Access and Security
OCA/OCP: Introduction to Oracle9i SQL Study Guide
by Chip Dawes and Biju Thomas


In your shop, the developers must be able to create and alter a session, as well as tables, clusters, views, sequences, and synonyms. The developers also need the SELECT privilege on the data dictionary views SYS.V_$SESSION and SYS.V_$SESSION_LONGOPS. You could grant all the privileges to each individual developer when he or she starts to work on a new database, but this is tedious and prone to error. Instead, you can create a role called DEVELOPER that will be granted to each of your developers. This role will incorporate all the system privileges and will be granted to the developers Chuck, Dave, and Erik, as follows:

 
 CREATE ROLE developer;

 GRANT CREATE SESSION, ALTER SESSION TO developer;

 GRANT CREATE CLUSTER, CREATE TABLE, CREATE VIEW,

      ,CREATE SEQUENCE, CREATE SYNONYM TO developer;

 GRANT SELECT ON v_$session TO developer;

 GRANT SELECT ON v_$session_longops TO developer;


 GRANT developer TO chuck, dave, erik;


After the role is created and the privileges are granted, the development manager discovers the need to use stored SQL (procedures, functions, packages, and triggers). You need to grant the necessary privileges to the developers so they can create and use these objects. You could grant the CREATE PROCEDURE and CREATE TRIGGER privileges to each developer, but since you have the DEVELOPER role, the better solution is to simply grant the privileges to the role, and then the users will inherit this new privilege.



 GRANT CREATE PROCEDURE, CREATE TRIGGER TO developer;



A couple of months later, the development manager wants her team to create and use
materialized views for the new data mart, so the developers now need the CREATE SNAPSHOT, CREATE DIMENSION, QUERY_REWRITE, and GLOBAL QUERY REWRITE privileges. Also, there are two new developers, Karen and Annie, who need all the privileges granted to Chuck, Dave, and Erik. Again, with your DEVELOPER role, you don't need to grant these privileges to each individual developer. Instead, you just grant these privileges to the role, and the developers inherit these privileges.

 

 GRANT CREATE SNAPSHOT, CREATE DIMENSION, QUERY REWRITE

    ,GLOBAL QUERY REWRITE TO developer;

 GRANT developer TO karen, annie;


As you can see, the use of roles has a greater benefit over time than the direct
granting of privileges. Maintenance and the introduction of new features or systems are less tedious and error prone.
Posted by yds at 11:29 AM 0 comments

SQL Chap 10 - User Access and Security

This is the last chapter of the book.


Review Questions


1. Which of the following assertions most correctly describes the privileges in force after the SQL below is executed?



connect athos/musketeer
grant select,insert,update,delete on
   athos.services to porthos
   with grant option;
grant all on athos.services to aramis;
connect porthos/musketeer
grant select,delete,insert,update on
   athos.services to aramis
   with grant option;
connect athos/musketeer
revoke all on athos.services from aramis;


A. Aramis can create an index on athos.services.
B. Aramis has no privileges on athos.services.
C. Aramis can select from athos.services.
D. Aramis can select, insert, update, and delete rows from athos.services.
-----
Object privileges can be obtained from more than one grantor. To completely remove object privileges from an account, all grantors must revoke these privileges. Aramis was granted the four privileges SELECT, INSERT, UPDATE, and DELETE on athos.services from Porthos, as well as ALL (SELECT, INSERT, UPDATE, DELETE, ALTER, INDEX, and REFERENCE) from Athos. After Athos revokes the privileges that he granted, Aramis still retains the privileges that were granted from Porthos.
Ans: D.


2. Which of the following assertions most correctly describes the privileges in force after the SQL below is executed?


connect system/manager
grant select any table to jon with admin option;
grant select any table to jason;
connect jon/seekrit
grant select any table to jason;
revoke select any table from jason;

A. Jason can select from any table regardless of any individual table privileges.
B. Jason can only select from tables that he has been granted SELECT privileges on or has acquired via a role.
C. Jason can only select from his own tables.
D. Jason continues to enjoy the SELECT ANY TABLE privilege.
---
Oracle does not retain the grantor on system privileges, so if anyone revokes a system privilege, that privilege is gone, even if the grantee obtained it from more than one grantor. This behavior is the same as role privileges, but different from object privileges, such as SELECT, INSERT, or EXECUTE.

Ans: B.


3. You need to create a database-authenticated account named selena. This account should have the password welcome, and Selena should be required to change this password as soon as she connects. Which of the following SQL statements most completely meets these requirements?


A. create user selena password welcome expired;
B. create user selena identified by welcome expire;
C. create user selena identified by welcome expire password;
D. create user selena identified by welcome password expire;

----
You create a database-authenticated account with the CREATE USER statement. You assign the password with the IDENTIFIED BY clause and expire the password with the PASSWORD EXPIRE clause. When the password expires, the user will be required to change it on the next connection to the database.
Ans: D.


4. You have an account called sales that owns the tables for an application. You have created the tables and need to ensure that no one will be able to connect as this account. Which of the following SQL statements most completely meets these requirements?


A. alter user sales account lock;
B. alter user sales disable account;
C. alter user sales lock account;
D. alter account sales lock;
----
To lock an account, disabling logons for that account, you alter the account with the ACCOUNT LOCK option.
Ans: A.


5. Which of the following queries will include the privileges on another user's procedure that you have granted to a third party?


A. SELECT owner, proc_name, grantor, grantee FROM all_sql_privs;

B. SELECT owner, sql_name, grantor, grantee FROM all_sql_privs;

C. SELECT owner, table_name, grantor, grantee, privilege FROM all_tab_privs_made;

D. SELECT owner, sql_name, grantor, grantee FROM user_table_privs;
----
Ans C. All of the other data dictionary tables are fictitious.


6. You have a few developers who insist on connecting to the database as the well-known table-owning account HR, which is reserved for system testing. These developers need to periodically connect to the HR account to promote changes, but the corporate guidelines say that development should be done in each of the developer's personal accounts so they don't conflict with each other. The development manager has asked you to enable any database settings that might help discourage these developers from all connecting to the HR account at the same time. Which of the following options will best assist the development manager?


A. Give the development manager SELECT privileges on the V$SESSION table, so she can monitor her team's connection activity.
B. Lock the HR account and make the developers come to a DBA when they need to promote changes to system test.
C. Use a profile to limit the number of concurrent sessions for user HR to one.
D. Create an after logon trigger that causes the logon to fail if someone else is logged into the HR account.
----
This one is really tricky. All of the options would work technically. However, the development manager probably has better things to do than monitor who on her team is connecting as which user. Unless the corporate standards say a DBA must promote changes to system test, the DBA probably has better things to do than slow down the development efforts by getting involved in promotions to system test. The after logon trigger is a clever bit of engineering, but it actually does the same thing as the profile with added complexity, overhead, and maintenance.
Ans: C


7. Which of the following actions cannot be done with an ALTER USER statement?


A. Expire a password.
B. Enable DBA privileges.
C. Set the default tablespace for tables.
D. Set different default tablespaces for indexes and tables.
----
It would be nice, but Oracle does not (yet) let you set a default tablespace for indexes. DBA privileges can be enabled by default with an ALTER USER statement if the role was granted to the user previously and set to disabled.
Ans: D.


8. Which init.ora parameter will limit the number of concurrent session from non-DBA accounts to 16?


A. sessions=16
B. license_max_sessions =16
C. processes=16
D. max_concurrent_logons=16
-----
Option A is a hard limit that includes restricted session logons. The processes setting includes such non-logon processes as pmon, lgwr, and parallel I/O slaves. The max_concurrent_logons parameter is fictitious. When the number of logon sessions reaches license_max_sessions, only restricted session (DBA) logons are allowed.
Ans: B.


9. What cannot be done with a profile?


A. Limit the number of physical reads per session to 100,000.
B. Limit the number of logical reads per session to 1,000,000.
C. Limit passwords to expire after 90 days.
D. Limit the duration of each session to 9 hours.
----
You can limit a number of resources with a profile, but the number of physical reads can be dependent on how warm the cache is and cannot be limited via a profile.
Ans: A.


10. Which of the following assertions most correctly describes the privileges in force after the SQL below is executed?


connect system/manager
grant dba to arsal with admin option;
grant dba to gretchen;
connect arsal/troodon
grant dba to gretchen;
revoke dba from gretchen;

A. Gretchen can exercise DBA privileges.

B. Gretchen can grant DBA privileges to other accounts.

C. Arsal loses DBA privileges.

D. Gretchen loses DBA privileges.
----
Oracle does not retain the grantor on role privileges, so if anyone revokes a role privilege, that privilege is gone, even if the grantee obtained it from more than one grantor. This behavior is the same as system privileges, but different from object privileges, such as SELECT, INSERT, or EXECUTE.
Ans: D.


11. Which statement will configure the principle_user profile to lock any account after three failed logon attempts?


A. alter profile principle_user set failed_logon_attempts=3;

B. alter profile principle_user limit failed_logon_attempts 3;

C. alter principle_user profile set failed_logon_attempts=3;

D. alter profile principle_user lock account when failed_logon_attempts=3;

E. You can't limit failed logon attempts.

-----
B. Know the syntax for changing resource limits in a profile.
Ans: B.


12. Which of the following SQL statements will give user Nikki the privileges to assign SELECT authority on HR.EMPLOYEES to other user accounts?


A. grant select on hr.employees to nikki;

B. grant select on hr.employees to nikki with grant option;

C. grant select on hr.employees to nikki with admin option;

D. grant select on hr.employees to nikki cascade;
----
The WITH GRANT OPTION clause is used to give the grantee the ability to grant the privilege to other accounts. The WITH ADMIN OPTION does the same thing with system and role privileges.
Ans: B.


13. Which statement will set a five-minute limit to the maximum time that a user with the default profile can remain idle?


A. alter user default set profile max_idle_time=300;

B. alter profile default limit max_idle_time 300;

C. alter profile default limit idle_time 5;

D. alter profile default limit idle_time 300;

-----
The ALTER PROFILE statement is used to change a profile, and the idle_time parameter is set in minutes, not seconds.
Ans: C.


14. Which init.ora parameter will assist you in enforcing named user licensing, by limiting the number of user accounts that can be created in your database?


A. max_users
B. license_max_users
C. max_named_users
D. named_users_max
----
license_max_users can be used to limit the number of user accounts created. The other options are fictitious.
Ans: B.


15. Which of the following statements will give user Zachary the privilege to modify only the COMMENTS column in the CUSTOMER table?


A. grant update on customer(comments) to zachary;
B. grant update (comments) on customer to zachary;
C. grant update on customer.comments to zachary;
D. grant update on customer columns(comments) to zachary;
---
Any additional columns would appear as a comma-delimited list within the parentheses.
Ans: B.


16. Mary has granted INSERT WITH GRANT OPTION, UPDATE WITH GRANT OPTION, and DELETE WITH GRANT OPTION privileges on the CHART_OF_ACCOUNTS table to Charlie. Charlie is changing jobs and should not have the grant option. How can Mary leave the INSERT, UPDATE, and DELETE privileges, but remove the WITH GRANT OPTION? Mary also wants to ensure that whomever Charlie granted the privileges to will retain the privileges.


A. Grant the privileges on CHART_OF_ACCOUNTS without the grant option, and then revoke the privileges WITH GRANT OPTION.
B. Simply revoke the grant option.
C. Revoke the privileges, so that the grant option goes away, and then grant the privileges without the grant option.
D. Extract all the grants that Charlie made from the data dictionary, revoke the privileges on CHART_OF_ACCOUNTS, grant the privileges on CHART_OF_ACCOUNTS without the grant option, and regrant all the extracted privileges.

-----
There is no simple and easy way to remove the WITH GRANT OPTION while retaining the privilege. Revoking a privilege from someone will cascade through and revoke it from all grantees, so it would be crucial to first extract these privileges before revoking them.
Ans: D.


17. You need to report on all of the column privileges that you have made on your BONUS table. You must include the name of the account receiving the privilege, which column, and which privilege. Which of the following statements will accomplish this task?


A.
select grantor, table_name, column_name, privilege
from user_col_privs_recd
where table_name ='BONUS';

B.
select * from all_col_privs_made
where table_name='BONUS';

C.
select table_name, column_name, privilege, grantee
from user_col_privs_made
where table_name ='BONUS';

D.
select grantee, table_name, column_name, privilege
from all_tab_col_privs
where owner=user and table_name='BONUS';

----
The grantee is the recipient of the privilege. Every one of the ALL_DATA dictionary views contains not only the user's own objects, but also those that the user has access to, so ALL_COL_PRIVS_MADE may contain privileges on other schemas' tables. ALL_TAB_COL_PRIVS is not a valid data dictionary view.
Ans: C.


18. EMP is a table. Mary is a user. Sales_mgr is a role. Which one of the following statements will fail?


A. grant sales_mgr to mary with admin option;
B. grant read on emp to mary;
C. grant insert,update,delete on emp to mary with grant option;
D. grant reference on emp to mary;
---
The READ privilege is valid only on directories.
Ans: B.


19. Which of the following table privileges cannot be granted to a role (can only be granted to a user)?


A. INDEX
B. ALTER
C. REFERENCE
D. TRUNCATE
-----
TRUNCATE is not a table privilege. INDEX and ALTER can be granted to either a user or a role, but REFERENCE can be granted only to a user.
Ans: C.


20. If Judy grants ALL on her table FORMAT_CODES to PUBLIC, which operation will user Jerry not be able to perform without being granted other privileges?


A. create index on judy.format_codes
B. alter table judy.format_codes
C. delete table judy.format_codes
D. truncate table judy.format_codes
----
Ans: D. TRUNCATE is not a table privilege.
Posted by yds at 10:11 AM 1 comments
Labels:

Sunday, August 13, 2006

SQL Chap 9 - Other Database Objects

Chapter 9 - Other Database Objects

OCA/OCP: Introduction to Oracle9i SQL Study Guide
by Chip Dawes and Biju Thomas

Review Questions


1. Which statement will create a sequence that starts with 0 and gets smaller one whole number at a time?


A. create sequence desc_seq start with 0 increment by -1 maxvalue 1;
B. create sequence desc_seq increment by -1;
C. create sequence desc_seq start with 0 increment by -1;
D. Sequences can only increase.
----
For a descending sequence, the default START WITH value is -1, and the default MAXVALUE value is 0. To start the sequence with 0, you must explicitly override both of these defaults.

Ans A.


2. Which statement is most correct in describing what happens to a synonym when the underlying object is dropped?


A. The synonym's status is changed to INVALID.
B. You can't drop the underlying object if a synonym exists unless the CASCADE clause is used in the DROP statement.
C. The synonym is automatically dropped with the underlying object.
D. Nothing happens to the synonym.

----
Synonyms do not have a status. The CASCADE CONSTRAINTS option does not drop synonyms. Synonyms can point to nonexisting objects.
Ans: D.


3. The built-in packaged procedure DBMS_APPLICATION_INFO.SET_MODULE has, in the package specification, the following declaration:


PROCEDURE DBMS_APPLICATION_INFO.SET_MODULE
(module_name IN VARCHAR2
,action_name IN VARCHAR2);

Which of the following statements will successfully call this procedure passing 'Monthly Load' and 'Rebuild Indexes' for the MODULE_NAME and ACTION_NAME, respectively? (Choose all that apply.)

A.
dbms_application_info('Monthly Load'
'Rebuild Indexes');

B.
dbms_application_info(
module_name=>'Monthly Load'
,action_name=>'Rebuild Indexes');

C.
dbms_application_info('Rebuild Indexes'
,'Monthly Load');

D.
dbms_application_info(
module_name->'Monthly Load'
,action_name->'Rebuild Indexes');

----
Option A almost uses the correct positional notation, except the delimiting comma is missing. Option B uses the correct named notational style. Option C transposes the module and action name using positional notation. Option D uses the wrong assignment syntax.
Ans: B.


4. With which of the following statements could you expect improved performance over a full-table scan, when a B-tree index is created on the two columns HIRE_DATE and SALARY in the HR.EMPLOYEES table?


A.
select max(salary)
from hr.employees
where hire_date < sysdate -90;

B.
select last_name, first_name
from hr.employees
where salary > 90000;

C.
update hr.employees
set salary = salary * 1.05
where department_id = 102;

D.
None of these statements would benefit from the index.
----
The index could be used if a leading subset of columns in the index is referenced. Options B and C do not reference the leading subset of columns in their WHERE clauses.
Ans: A


5. Which of the following statements will raise an exception?


A. alter sequence emp_seq nextval 23050;
B. alter sequence emp_seq nocycle;
C. alter sequence emp_seq increment by -5;
D. alter sequence emp_seq maxvalue 10000;
----
You cannot explicitly change the next value of a sequence. You can set the MAXVALUE or INCREMENT BY value to a negative number, and NOCYCLE tells Oracle to not reuse a sequence number.
Ans: A.


6. Rajiv has created a private synonym NEW_PRODUCTS for the MEG.PRODUCTS table. Who can select from RAJIV.NEW_PRODUCTS?


A. The users that Rajiv has granted SELECT on NEW_PRODUCTS to and Meg has granted SELECT on PRODUCTS to.
B. The users that Rajiv has granted SELECT on NEW_PRODUCTS to.
C. The users that Meg has granted SELECT on PRODUCTS to, even if Rajiv does not grant privileges to his synonym.
D. The users that Rajiv has granted SELECT on NEW_PRODUCTS to, if Meg has granted him SELECT WITH ADMIN OPTION.
----
Private synonyms can be referenced by anyone who has privileges on the underlying objects. You cannot grant privileges on synonyms, only on the underlying object. Option D is close, but the WITH ADMIN OPTION is only for roles and system privileges, not for table privileges.
Ans: C.


7. Which type of stored program must return a value?


A. PL/SQL procedure
B. PL/SQL function
C. Java trigger
D. Java procedure
-----
Functions must include a RETURN statement and must return a value.
Ans: B.


8. What does the following SQL statement enable all users in the database to do?


create public synonym plan_table
for system.plan_table;

A. Use the EXPLAIN PLAN feature of the database

B. Save execution plans in the system repository

C. Reference a table as PLAN_TABLE instead of SYSTEM.PLAN_TABLE

D. Turn on SQL tracing

---------
This statement creates a public synonym or global alias, which allows users to reference the underlying table without needing to explicitly specify the owner. A table named PLAN_TABLE is needed to use the EXPLAIN PLAN feature, but the statement above creates a public synonym. Also, the existence of a public synonym does not grant to public any privileges on the underlying object. An ALTER SESSION statement is used to enable and disable SQL tracing.
Ans: C


9. There is a public synonym named PLAN_TABLE for SYSTEM.PLAN_TABLE. Which of the following statements will remove this public synonym from the database?


A. drop table system.plan_table;
B. drop synonym plan_table;
C. drop table system.plan_table cascade;
D. drop public synonym plan_table;
----
To remove a public synonym, use the DROP PUBLIC SYNONYM statement. The DROP TABLE statement will remove a table from the database, but will not affect any synonyms on the table.

Ans: D.


10. A developer reports that she is receiving the following error:


SELECT key_seq.currval FROM dual;

ERROR at line 1:
ORA-08002: sequence KEY_SEQ.CURRVAL is not yet defined

Which of the following statements does the developer need to run to fix this condition?

A. create sequence key_seq;
B. create synonym key_seq;
C. select key_seq.nextval from dual;
D. grant create sequence to public;
----
A sequence is not yet defined if NEXTVAL has not yet been selected from it within the current session. It has nothing to do with creating a sequence, creating a synonym, or granting privileges.

Ans: C.


11. A power user is running some reports and has asked you to put two new B-tree indexes on a large table so that her reports will run faster. You acknowledge that the indexes would speed up her reports. Can the proposed indexes slow other processes? (Choose the best answer.)

A. No, indexes only speed up queries.
B. Yes, the indexes will make the optimizer take longer to decide the best execution plan.
C. Yes, DML will run more slowly.
D. Yes, table reorganization operations will be slower.
----
This one's a little tricky. B, C, and D are all true, but C is the best answer. Two additional indexes should not appreciably slow the optimizer, and table reorganization in Oracle (unlike in other databases) is usually not needed. DML (INSERT, UPDATE, and DELETE) operations will definitely be slowed, as the new indexes will need to be maintained.
Ans: C.


12. Bitmapped indexes are best suited for which type of environment?


A. High-cardinality columns

B. Online transaction processing (OLTP) applications

C. Full-table scan access

D. Low- to medium-cardinality columns
----
Bitmapped indexes are not suited for high-cardinality columns (those with highly selective data). OLTP applications tend to need row-level locking, which is not available with bitmap indexes. Full-table scans do not use indexes. Bitmap indexes are best suited for multiple combinations of low- to medium-cardinality columns.
Ans: D



13. The INSURED_AUTOS table has one index on the columns YEAR, MAKE, and MODEL, and one index on VIN. Which of the following SQL statements could not benefit from using these indexes?


A.
select vin from insured_autos
where make='Ford' and model = 'Taurus';

B.
select count(*) from insured_autos
where make='Ford' and year = 1998;

C.
select vin from insured_autos
where year = 1998 and owner = 'Dahlman';

D.
select min(year) from insured_autos
where make='Ford' and model = 'Taurus';
----
Option A does not use a leading subset of columns in an index, nor do all of the columns come from the index. A full-table scan on the table will be needed. Options B and C use a leading subset of the three-column index, so that index could be used. Option D uses data that is found completely in the three-column index, and a full scan of this index would likely be faster than a full scan of the larger table.
Ans: A.


14. Which clauses in a SELECT statement can an index be used for? (Choose all that apply.)


A. SELECT

B. FROM

C. WHERE

D. HAVING
----
The obvious answer is C, but an index also can be used for the SELECT clause. If an index contains all of the columns needed to satisfy the query, the table does not need to be accessed.
Ans: A, C.


15. You need to generate artificial keys for each row inserted into the PRODUCTS table. You want the first row to use a sequence value of 1000, and you want to make sure that no sequence value is skipped. Which of the following statements will meet these requirements?


A.
CREATE SEQUENCE product_key2
START WITH 1000
INCREMENT BY 1
NOCACHE;

B.
CREATE SEQUENCE product_key2
START WITH 1000
NOCACHE;

C.
CREATE SEQUENCE product_key2
START WITH 1000
NEXTVAL 1
NOCACHE;

D. Options A and B meet the requirements.

E. None of the above statements meet all of the requirements.
-----
Both options A and B produce identical results, because the INCREMENT BY 1 clause is the default if it is not specified. Option C is invalid because NEXTVAL is not a valid keyword within a CREATE SEQUENCE statement.
Ans: D.


16. Which statement will display the last number generated from the EMP_SEQ sequence?


A. select emp_seq.curr_val from dual;
B. select emp_seq.currval from dual;
C. select emp_seq.lastval from dual;
D. select last_number from all_sequences where sequence_name ='EMP_SEQ';
E. You cannot get the last sequence number generated.
-----
Option D is close, but it shows the greatest number in the cache, not the latest generated. The correct answer is from the sequence itself, using the pseudo-column CURRVAL.
Ans: B.


17. Which statement will create a sequence that will rotate through 100 values in a round-robin manner?


A. create sequence roundrobin cycle maxvalue 100;

B. create sequence roundrobin cycle to 100;

C. create sequence max_value 100 roundrobin cycle;

D. create rotating sequence roundrobin min 1 max 100;
----
The keyword CYCLE will cause the sequence to wrap and reuse numbers. The keyword MAXVALUE will set the largest value the sequence will cycle to. The name roundrobin is there to confuse to you.
Ans: A.


18. The following statements are executed:


create sequence my_seq;
select my_seq.nextval from dual;
select my_seq.nextval from dual;
rollback;
select my_seq.nextval from dual;

What will be selected when the last statement is executed?

A. 0
B. 1
C. 2
D. 3
----
The CREATE SEQUENCE statement will create an increasing sequence that will start with 1, increment by 1, and be unaffected by the rollback. A rollback will never stuff vales back into a sequence.
Ans: D.


19. Which of the following can you not do with a package?


A. Overload procedures and functions
B. Hide data
C. Retain data across commits
D. Grant EXECUTE privileges on one procedure in a package
----
You can only grant EXECUTE privileges on the entire package, not on individual packaged programs.
Ans: D.


20. Which of the following calls to the stored function my_sine() will raise an exception? 

 
A. Theta := my_sine(45); 

B. IF (my_sine(45) > .3 ) THEN 

C. DECLARE
      Theta   NUMBER DEFAULT my_sine(45);
   BEGIN ... 

D. my_sine(45);

----

Functions cannot be called as stand-alone statements; only procedures can be called this way
Ans: D.
Posted by yds at 4:30 PM 0 comments
Labels:
Subscribe to: Posts (Atom)