Oracle 9i Database Administrator 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
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
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
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
7. c
8. c
9. c
10. b
Chapter 5
Review Questions
7. d
8. 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
9. When a checkpoint occurs, the LGWR background process writes redo log buffers to disk.
10. System Change Number: A sequential number that is incremented for each change that modifies the physical database files. Controlled by the CKPT background process
11. A log switch is initiated
12. When you are using Personal Oracle9i
13. To find the number of the current redo log group, query the V$LOG view.
14. 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
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 EXTEN
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
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
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.
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.
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
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
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
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
3. To make a new role, use the CREATE ROLE command.
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
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