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.

No comments: