Docstoc

Solutions

Document Sample
Solutions Powered By Docstoc
					Answer Key

Chapter 1


Review Questions



1. False

2. Oracle9i database, Oracle Net, Java/Web, Oracle Enterprise Manager, Management packs, SQL*Plus,

   Utilities, Precompilers, and Add-ons

3. Oracle Net provides the network link between the Oracle9i database and most applications, utilities, and

   tools that communicate with the database.

4. The utilities use Oracle Net to pass requests and receive data to and from the database.

5. Oracle Enterprise Manager and SQL*Plus

6. A precompiler translates the SQL command in a program into a set of commands in the program’s native

   language, such as C++ or COBOL.

7. True

8. The Administrator provides tools for remote database administration; the Runtime option provides

   connectivity only to a remote database, usually for either programmers or end users running applications.

9. True

10. False

11. To name a separate directory structure when installing a second version of the database software on a

   computer; to switch between two different database software versions on the same computer.

12. setup.exe –responseFile resp0901.txt –silent

13. OFA reduces I/O bottlenecks by separating data from the software.

14. False

15. False
Exam Prep Questions



1. b. Less than fifty

2. b. This structure does not comply with OFA.

3. e. All of the above

4. d. Windows 98

5. b. A sales projection application requiring large quantities of historical data

6. c. Oracle Spatial

7. a. You must replace the Oracle8i software with the Oracle9i software; b. Your ORACLE_HOME is

   permanently set to point to the newest software.

8. b. Database files; c. System Global Area

9. c. Developing allpcations on a programmer workstation

10. e. None of the above
Chapter 2


Review Questions



1. False

2. True

3. The dispatcher process distributes user processes among the available server processes.

4. False

5. The file that stores Oracle Net configuration data is called tnsnames.ora .

6. The Enterprise Manager Console runs in the following two modes: stand-alone and Enterprise Management

   Server.

7. b. Centralizing access to tools and utilities

8. b. Shared pool

9. False. The Instance Manager can shut down the database.

10. The Storage Manager displays the full path name of datafiles.

11. a. Killing a user session

12. The DBWn process writes buffers to the data files.




Exam Prep Questions



1. d. HAROLDK

2. a. Assign users the ability to create tables, c. Limit the resources a user can use on the database, e. Create a

   new user

3. False
4. a. Net Manager, c. Net Configuration Assistant

5. a. User Name, c. Service name, d. Password

6. b. Incorrect port number specified, c. Database instance not running, d. User SCOTT was removed or

    password was changed

7. a. PGA, c. Server process

8. a. Copying redo log files to a remote storage area

9. b. PMON

10. a. SGA, b. PGA, d. Background processes

11. b. Redo log buffer

12. a. Redo log files, c. Control files
Chapter 3


Review Questions



1. False

2. SQL (manual method), and Oracle Database Configuration Assistant (automated method)

3. False

4. Installation guide

5. d, c, a, b, e

6. b. Selecting the database type

7. OS_AUTHENT_PREFIX

8. True

9. a, c, d

10. The DBA controls the names, locations, and sizes of each data file when using user-managed files while the

    DBA only controls the location and sometimes the sizes of the data file when using OMF.

11. False

12. # (the pound sign)

13. Static (Permanent), Static (Adjustable), Dynamic (System only), Dynamic (System and Session), Derived

14. False

15. c. ORADIM

16. d. NCLOB

17. catalog.sql, catproc.sql

18. True
Exam Prep Questions



1. c.

2. a, c

3. d

4. c, d, e

5. c

6. c

7. c, d

8. b

9. b

10. d
Chapter 4


Review Questions



1. Dictionary

2. False

3. e

4. OWNER

5. GV$

6. DBA_TABLES view displays all tables owned by all schemas, while the USER_TABLES view displays

   only tables owned by the Oracle user currently logged on

7. An alias to a database object that all users are able to see. Used to provide a shorter name or a non-prefixed

   name for an object.

8. The data dictionary views are queried and the information displayed in the Schema Manager's windows.

9. When I need a list of invalid views in my schema; when I have forgotten the exact spelling of a column

   name.

10. Shut down the database. Update the init<sid>.ora file by specifying two control files in the

   CONTROL_FILES initialization parameter, or specifying two locations using the

   DB_CREATE_ONLINE_LOG_FILE_DEST_n initialization parameters. Copy the original control file and

   rename it and then start up the database.

11. False

12. Better safeguard against physical damage to the control file; better security in case of accidental damage by

   human error.
13. DB_CREATE_FILE_DEST: this defines the directory where data files are created and stored;

   DB_CREATE_ONLINE_LOG_DEST_n: this defines the directory or directories where control files and

   redo log files are created and stored.

14. False. It can succeed if you use the NOMOUNT clause along with it.




Exam Prep Questions



1. a, b, e

2. a, c, e

3. c, d

4. b, d

5. c, d

6. b, c

7. c

8. c

9. c

10. b
Chapter 5


Review Questions



1. A database in ARCHIVELOG mode has one or more background processes called ARCn that is activated

   after a log switch to copy the filled redo log group member to a separate file with a unique name. This

   allows you to recover from lost data in the past. A database in NOARCHIVELOG mode does not archive

   the redo log group member and therefore cannot recover from lost data beyond the earliest point stored in

   the online redo log file.

2. b

3. False

4. a. LGWR fills Redolog3.log before writing to Redolog2.log. ; d. A log switch changes from Group 1 to

   Group 2.

5. Add another redo log group

6. d

7. Multiplexing should keep copies of the files on separate disks to minimize the risk of losing all the files due

   to failure of one disk

8. When a checkpoint occurs, the LGWR background process writes redo log buffers to disk.

9. System Change Number: A sequential number that is incremented for each change that modifies the

   physical database files. Controlled by the CKPT background process

10. A log switch is initiated

11. When you are using Personal Oracle9i

12. To find the number of the current redo log group, query the V$LOG view.
13. Set the location of the alert log file with the BACKGROUND_DUMP_DEST parameter.




Exam Prep Questions

1. a, c, e

2. c. 2, 4, 5, 3, 1

3. c. 2, 4, 5, 3, 1

4. d. Your changes will not be restored even if they were recorded in the redo log file.

5. d. Create a new member in each group on a separate disk.

6. b. The database already has two redo log groups.; c. The redo log files are managed by OMF.; e. The

    database is started and in the MOUNT mode.

7. b. The statement succeeds and the redo log files are multiplexed.

8. d. my000211.log

9. c. Operating system dependent

10. c. SHUTDOWN IMMEDIATE; move file to new destination; STARTUP MOUNT; RENAME FILE...;

    ALTER DATABASE OPEN;
Chapter 6


Review Questions



1. b. 2, 3, 1, 4

2. False

3. False

4. This could happen when the tablespace has uniform extents, and the table's initial extent size is larger than

the uniform extent size. In this case, the table's initial extent will be broken up into two or more uniform extents.

5. b. Partition; c. Unpartitioned table

6. The object has extents and the extents can reside in different datafiles.

7. Deallocated extents are automatically coalesced in locally managed tablespaces.

8. To create a locally managed tablespace with all extents 3M in size, use the clause EXTENT

MANAGEMENT LOCAL UNIFORM SIZE 3M.

9. Undo extents are stored in undo tablespace or rollback segments.

10.

CREATE TEMPORARY TABLESPACE USERTEMP

TEMPFILE 'D:\oracle\data\usertemp01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL

UNIFORM SIZE 2M



11. False.

12. To find out how the names of the tempfiles in a temporary tablespace, query the V$TEMPFILE view.
Exam Prep Questions

1. b. Local and Dictionary

2. b, d, e

3. a, d

4. c

5. a

6. b

7. b, d

8. c
Chapter 7


Review Questions

1. Relational tables must have a primary key. True or False? False.

2. A non-partitioned table has one SEGMENT and one or more EXTENTS for storage.

3. You want to speed up queries on a table. Queries are always looking up rows based on the values of the

     row's primary key. What type of table would improve query speed? INDEX-ORGANIZED TABLE

4.   e. All of the above

5. True

6. d. Line 4 TIMEZONE should be TIME ZONE

7. c. 5M

8. d. Not enough information. If the table's PCTFREE is set at 20%, the data block would be taken off the free

     list (assuming the table is in a dictionary managed tablespace)

9. A MIGRATED or CHAINED row spans multiple data blocks

10. b. Restricted rowid

11. False. The data is deleted but the temporary table stays.

12. VARRAY or NESTED TABLE

13. a. List-hash

14. b. BFILE
Exam Prep Questions

1. c. All the others are false

2. b. Column data

3. c. 80 percent

4. b,c,and d. "a" is incorrect -- the initial extent is 5M (there will be 4 of them allocated when the table is

    created)

5. d and e are true. No length is stored for SALES_AMOUNT and MANAGER_APPROVAL because they are

    at the end and are null, so they are not recorded at all in the row.

6. b, d, and e

7. c. 4, 2, 1, 3

8. a. "b" is incorrect because you cannot create an index on a varray.

9. a.

10. c.
Chapter 8

Review Questions



1. CLOB, BLOB, NCLOB, NBLOB

2. False

3. External

4. b. Out of line

5. c. The clause is invalid because the initial extent must be a multiple of the chunk size

6. Because other rows in the index-organized table may need to be moved to rebalance the table's organization

7. HASH or RANGE

8. b. To help you decide if storage settings fit the actual data, and d. To improve query performance

9. a. TRUNCATE TABLE, b. ALTER TABLE, c. DROP TABLE. (MOVE TABLE is not a command)

10. Execute an ALTER TABLE ... UPGRADE INCLUDING DATA command.

11. Invalid. You cannot use DROP COLUMN and RENAME COLUMN in the same command

12. c. 25K


Exam Prep Questions



1. b. TRUNCATE TABLE CAR;, and d. DELETE FROM CAR; (a. removes data and the table) (c is an

   invalid command) (the foreign key is immaterial because it is referencing another table, not the other way

   around)

2. b. V$OBJECT_STATS

3. c. Execute an ALTER TABLE MODIFY command and let the data automatically be converted
4. b.

 CREATE TABLE OLDMOVIE

(MOVIE_ID NUMBER(10),

 MOVIE_TITLE VARCHAR2(30),

 MOVIECLIP BLOB)

LOB (MOVIECLIP)            STORE        AS   MOVIELOB

        (TABLESPACE USER_LOCAL

          DISABLE STORAGE IN ROW

          PCTVERSION 25

   STORAGE (INITIAL 64M NEXT 32M));

5. b, c

6. d. USER_TAB_COLUMNS only

7. c. 7, 4 (1, 2, 3, 5, 6 not needed)

8. ALTER TABLE CUSTOMER ALLOCATE EXTENT SIZE 55M

9. b, c, d

10. b. One column will be dropped, and one column will be added.
Chapter 9

Review Questions


1. False

2. The UNIQUE key constraint index allows null values in the indexed columns and the PRIMARY KEY

   constraint index does not.

3. REVERSE stores the data of each indexed column in reversed byte order. DESC stores the index entries

   sorted in descending order.

4. a. CREATE INDEX BOOK_X

5. Line 1: missing the name of the table

6. b. NOSORT

7. c. Depends on the indexed columns

8. c. Bitmap

9. A LOCAL partitioned index is partitioned the same as the table while a GLOBAL partitioned index is

   partitioned differently than the table.

10. False

11. A function-based index cannot be used by the RULE-BASED Optimizer.

12. The DBA_IND_COLUMNS data dictionary view can tell you the name of a column in an index.
Exam Prep Questions



1.

a. BEGIN

         DBMS_STATS.GATHER_SCHEMA_STATS

        (ownname=>'ACCT',cascade=>TRUE);

      END;



2. c. V$OBJECT_USAGE

3. a. When the underlying table is partitioned on the same columns as the index

4. c.




     SELECT INDEX_NAME FROM USER_INDEXES

  WHERE TABLESPACE_NAME = 'USERS';



5.

     a. Changing a reverse index to a normal index

     b. Moving an index to another tablespace

     d. Moving an index partition to another tablespace




6. b. Release unused space in leaves of the b-tree

7. d. Composite index

8. c. Function-based index

9. a. Several columns with low cardinality need indexing, the table has 50 million rows and infrequent updates.
10. c.




    CREATE UNIQUE INDEX BILLX ON BILLING

    (CUST_ID, ORDER_NO)

         ONLINE STORAGE (INITIAL 5M NEXT 5M)

  TABLESPACE INDEXES;



11. b. A row with LOANID='M001' and CUST_CODE=004 will fall into the L3 partition, d. The L2 partition

    will be located in ACCTMGR's default tablespace.

12. b. Unused space is released except 10K above the HWM., e. The partition's leaves are adjusted to release

    unused space.
Chapter 10

Review Questions



1. b. Your integrity constraints are complex and involve multiple table lookups

2. PRIMARY KEY, CHECK, UNIQUE, FOREIGN KEY, NOT NULL

3. An inline constraint appears immediately next to the column to which it applies. It is used to create a

   constraint in the CREATE TABLE command. The inline constraint can also be used with the ALTER

   TABLE command, but only for the NOT NULL command, unless you are adding a new column with its

   integrity constraint.

4. All but the NOT NULL constraint.

5. False. Add storage settings at the end of the command as usual.

6. The UNIQUE constraint allows nulls and not duplicate values, while the PRIMARY KEY constraint does

   not allow nulls or duplicate values.

7. True. The word NULLS should be NULL.

8. True. Either PRIMARY KEY or UNIQUE constraints allow FOREIGN KEY constraints.

9. The SET CONSTRAINT ALL DEFERRED command changes a deferrable constraint to deferred state

   during your session.

10. ALTER TABLE MOUSETRAP RENAME CONSTRAINT KEY1 TO PK1;

11. P=PRIMARY KEY, U=UNIQUE, C=CHECK, R=FOREIGN KEY

12. The DEFERRED state tells Oracle9i to check the constraint only when a transaction commits.
Exam Prep Questions

1. a. A compound primary key defined with ALTER TABLE; b. A CHECK constraint on an existing column;

   d. A FOREIGN KEY constraint on an existing column

2. d. First insert succeeds, second insert fails, update succeeds, commit succeeds.

3. c. Existing rows are validated, after which the constraint is immediate unless later deferred

4. d. ALTER TABLE , e. SET CONSTRAINT

5. b. All child rows have the column(s) set to null when a parent row is deleted

6. c. SELECT CONSTRAINT_NAME, DEFERRABLE FROM ALL_CONSTRAINTS;

7. a. Each constraint contains a different set or order of columns; c. All three constraints are FOREIGN KEY

   constraints

8. c. Application code; d. Database trigger

9. a. The statement will fail if the PHONEX index does not exist; d. Existing rows will be validated

   immediately

10. b. The COMPANY table has no rows; d. The COMPANY table has no PRIMARY KEY constraint
Chapter 11


Review Questions



1. Create new users for the purpose of (1) owning new database objects and (2) accessing data

2. External, Global

3. d. The JOEBANKS user must be identified EXTERNALLY

4. c. UNLIMITED TABLESPACE

5. DBA_TS_QUOTAS

6. c. The table remains but cannot increase in size

7. DBA_TS_QUOTAS

8. a

9. b

10. b

11. b

12. a




Exam Prep Questions

1. b. USER101 has an unlimited quota on the USER_LOCAL tablespace; d. USER101 can use either USERS

   or USER_LOCAL to create a new index

2. c. STEVENS is authenticated by the operating system; d. STEVENS logs in as USA$STEVENS

3. a. All statements succeed; d. FRANCIS.NEWCAR table has 9 rows

4. c. Revoke system privileges from a user
5. a. Complexity of passwords; b. Days until password expires; c. Hours after failed login until a locked

   account unlocks

6. a. KATE's password will expire in ten days; c. KATE can run an unlimited number of concurrent sessions

7. a. The RESOURCE_LIMIT parameter must be set to TRUE to enforce composite limits; d. The user

   sessions for JOE and BETTY have exceeded the composite limit

8. a. The five accountants with the ACCOUNTING profile switch to the DEFAULT profile

9. b. utlpwdmg.sql

10. c. Drop the user with the CASCADE parameter
Chapter 12


Review Questions



1. a. SELECT TABLE

2. False.

3. To create a table in another schema, a user must have the CREATE ANY TABLE system privilege.

4. d. EXECUTE

5. To grant all users the privilege without having to grant it to each specific user. Usually, this would not be

   done with a system privilege because it would allow too much access to the database.

6. b. REVOKE CREATE TABLE FROM SMITH;

7. a. GRANT SELECT ON CUSTOMER TO SMITH;

8. b. Grant the SELECT object privilege to PUBLIC

9. The BY SESSION clause of the GRANT command changes the audit trail so it writes one record per

   session.

10. b. USER_TAB_PRIVS_MADE




Exam Prep Questions

1. d. Line 4 (it should say WITH ADMIN OPTION)

2. b. CREATE USER; c. CREATE ANY TABLE; d. GRANT ANY OBJECT PRIVILEGE

3. b. Query fails with error: "table does not exist"

4. c. SELECT * FROM DBA_SYS_PRIVS WHERE GRANTOR = 'ORADBA';
5. b. SYSTEM, Henry, and Albert.

6. c. Shut down the database; d. Create an spfile

7. b. Shutdown and restart the database

8. a. AUDIT SELECT ON NOT EXISTS BY JOEY;

9. b. AUDIT SELECT TABLE BY STUDENTA WHENEVER NOT SUCCESSFUL;

10. c. JOEY
Chapter 13


Review Questions



1. System privileges, object privileges, and other roles

2. d. DBA

3. To make a new role, use the CREATE ROLE command.

4. The NOT IDENTIFIED clause of the CREATE ROLE command is optional and the default.

5. True

6. d. GRANT SKIPPERROLE TO BOATROLE IDENTIFIED BY CAPTAIN;

7. b. Drop and re-create the role

8. a. SET ROLE ALL;

9. USER_ROLE_PRIVS

10. DROP ROLE ACCTMGR;




Exam Prep Questions

1. a. IMP_FULL_DATABASE; c. CONNECT

2. c. SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = 'ADMINACT';

3. b. CREATE ROLE SALESREAD NOT IDENTIFIED;

4. c. HAROLD keeps the SUPERUSER role
5. b. GRANT INSERT ON CUSTOMERS TO ROLEB, ROLEC, USER1; c. GRANT ROLEA TO ROLEB

   WITH ADMIN OPTION;

6. c. The TOPDOG role stays enabled

7. c. MANAGERROLE only

8. d. SET ROLE MGR IDENTIFIED BY MGR123, SALES IDENTIFED BY SALES789;

9. a. MARTHA can now create tables and views

10. b. Easier to grant additional privileges to users; c. Simplifies security administration; d. Provides additional

   security through extra passwords
Chapter 14


Review Questions



1. National Language Support is a subset of globalization support.

2. NCHAR, NVARCHAR2, NCLOB

3. unicode

4. c. Large document

5. True

6. Database character set and national character set

7. True

8. False

9. A variable-length, multi-byte character set uses less space by allowing different numbers of bytes for

   different characters.

10. c. NLS_DATE_LANGUAGE; d. NLS_LANGUAGE




Exam Prep Questions

1. b. Line 5

2. c. Chinese

3. b. In Italian

4. e. $6,502.40
5. c. 07.05.2004     $150.4   (ISO_CURRENCY format is "C", not "L")

6. d. SELECT * FROM CUSTOMER ORDER BY NLSSORT(FULLNAME,'NLS_SORT=GERMAN');

7. b. V$NLS_PARAMETERS; d. USER_SESSION_PARAMETERS

8. a. Write the query using the NLS_DATE_LANGUAGE='SPANISH' in the TO_CHAR function of the date

   column; c. Change your session's NLS_LANGUAGE parameter to 'SPANISH' and then write the query

9. a. NLS_TERRITORY

10. d. NLS_NUMERIC_CHARACTERS; e. NLS_CURRENCY

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:183
posted:9/13/2012
language:Latin
pages:29