================================================
Review Questions
1. Which of the following conditions prevents you from being able to
insert into a view?
A. A TO_NUMBER function on one of the base table columns
B. A CONNECT BY clause in the view definition
C. A column of type RAW
D. All of the above
----
Ans: B
B. You cannot insert into a view that contains a CONNECT BY, ORDER BY,
or GROUP BY clause.
2. Changes made with an UPDATE statement are made permanent in the
database after what occurs?
A. DBWR flushes the changes to disk.
B. You issue a SAVEPOINT statement.
C. You issue a COMMIT statement.
D. A checkpoint occurs.
---
Ans: C
A commit makes pending DML changes permanent. When a checkpoint occurs,
DBWR
flushes dirty buffers to disk, which is independent of transaction
boundaries.
3. Which of the following is not a PL/SQL program?
A. Library
B. Trigger
C. Function
D. Procedure
---
Ans: A.
A library can be a database object, but is associated with an operating
system shared library.
PL/SQL programs include procedures, functions, packages, package bodies,
and triggers.
4. Why would you execute a CREATE OR REPLACE PROCEDURE statement instead
of a DROP
PROCEDURE and a CREATE PROCEDURE?
A. It is less typing.
B. There is no difference between the two.
C. CREATE OR REPLACE PROCEDURE does not invalidate dependent objects.
D. DROP PROCEDURE and CREATE PROCEDURE require regranting of privileges.
----
Ans: D.
Using CREATE OR REPLACE PROCEDURE is less typing, but, more importantly,
when you drop
an object, all privileges granted on that object are dropped as well.
When you perform a CREATE
OR REPLACE, you do not lose privileges granted on that object.
5. Which of the following is not a trigger event?
A. UPDATE
B. SELECT
C. NOAUDIT
D. SERVERERROR
---
Ans: B.
You can create a trigger for just about any database event that involves
a change to data, but
you cannot create a SELECT trigger in Oracle10g.
6. Which of the following statements can be used to enable triggers?
A. ALTER SYSTEM
B. ALTER TABLE
C. ALTER USER
D. ALTER PACKAGE
---
Ans: B.
You can enable triggers with either an ALTER TRIGGER ENABLE statement or
with an ALTER
TABLE ENABLE ALL TRIGGERS statement.
7. Which of the following is true?
A. Directories are like roles or profiles; they are not owned by any
individual user.
B. Directories are like synonyms or database links; they can be public
or private.
C. Directories are like triggers and packages; they are owned by a
schema.
D. You can grant SELECT and UPDATE object privileges on directories.
----
Ans A.
The READ and WRITE object privileges are applicable to directory
objects; SELECT and UPDATE
are not. Directory objects are not owned by any individual schema, so
they are like roles and
profiles in this regard, not triggers and packages.
8. Which of the following keywords is used with a Data Pump import to
copy data from one
schema to another?
A. fromuser and touser
B. source_schema and target_schema
C. rename_schema
D. remap_schema
--
Ans D.
The keywords fromuser and touser are from the older imp import program.
Data Pump
uses remap_schema (not rename_schema) to map a source to a target
schema.
9. The application development manager asks you to make sure the new
Oracle10g database that
her team will use has PL/SQL warning enabled and the optimizing compiler
set to maximum
optimization. What do you have to do to satisfy these requirements?
A. Nothing. They are the default settings.
B. The optimizing compiler is set to maximum by default, but you have to
set the initialization
parameter plsql_warnings to ENABLE:ALL.
C. plsql_warnings is set to maximum by default, but you need to set
plsql_optimize_mode =2
to increase it to maximum.
D. You need to set both plsql_warnings and plsql_optimize_mode to
nondefault settings.
---
Ans: B.
The default settings are to disable plsql_warnings and enable maximum
optimization (level 2).
10. Which of the following is not a mode for Data Pump export?
A. Database
B. Tablespace
C. Table
D. Metadata
---
Ans D.
Data Pump export modes include database, tablespace, table, and schema.
The keyword
metadata is used in conjunction with the content parameter. To export
only metadata, you
specify content=metadata.
11. To perform a Data Pump import from a live database, which parameter
needs to be set?
A. db_link
B. network_link
C. dumpfile
D. directory
---
Ans: B
The network_link parameter specifies a database link to the source
database.
12. You perform nightly metadata-only Data Pump exports of your
development database. One
morning, you get a frantic call from a developer who accidentally
dropped a package he had
been working on for the past week. Which Data Pump import parameter will
you need to extract
the package DDL from the dump file?
A. show
B. sqlfile
C. reuse_datafiles
D. tablespaces
----
Ans: B. To extract the package DDL, you need to specify a sqlfile= and
an include parameter.
13. What will the following Data Pump import command do?
impdp system/password network_link=sys_test
schemas="GL" remap_schema="GL:GLI" content=both
nologfile=y include=TABLE:"LIKE 'PROD%'"
A. Copy the GL tables whose names start with PROD from the sys_test
database, placing them
into the GLI schema.
B. Copy the GLI tables whose names start with PROD from the sys_test
database, placing
them into the GL schema.
C. Load the data in file sys_test.dmp into the table GLI.products.
D. Copy the metadata from user GL in sys_test into the local database.
---
Ans: A
A. The remap_schema parameter defines the source and target schemas, and
the include
parameter identifies which objects to include in the import job.
14. Your requirements call for loading a lot of data from a flat file
dumped from the mainframe into
the orders table at night while a few users in Singapore enter orders
interactively. You are using
SQL*Loader to load the data, but it takes longer than desired. What
might you do to speed up
the process?
A. Switch to direct path mode.
B. Increase the bind size.
C. Write a Java program to parse and load the data.
D. Write a C program to parse and load the data.
----
Ans: B.
Writing your own Java or C program to do the same thing as SQL*Loader
would be a costly
decision in terms of development effort. Getting your own program to
outperform SQL*Loader
would be difficult at best. Direct path loading would certainly speed up
the load, but would lock
the table exclusively, prohibiting your Singapore users from entering
data interactively. Increasing
the bind size would allow SQL*Loader to make fewer database calls,
resulting in fewer network
round-trip communications and potentially improving the performance. The
key concept
to grasp with this question is that direct path loads lock the table
exclusively; therefore they
should not be used on active tables.
15. Which type of PL/SQL program can be called in the select list of a
SQL statement?
A. Trigger
B. Procedure
C. Function
D. None of the above
--
Ans: C.
Functions can be called in several places, including in the SELECT,
WHERE, ORDER BY, and
GROUP BY clauses of SQL statements. Triggers are invoked when their
trigger event fires. Procedures
are invoked as stand-alone statements.
16. You need to let an application role execute the SLEEP procedure in
the DBMS_LOCK package,
but do not want to let an application role have access to the other more
powerful capabilities of
the DBMS_LOCK package. How can you satisfy these requirements best?
A. Grant EXECUTE on dbms_lock to user system. Then create a procedure in
the system schema
that calls DBMS_LOCK.SLEEP. Finally, grant EXECUTE on this procedure to
the application role.
B. Grant EXECUTE on DBMS_LOCK to the application role.
C. Grant EXECUTE on DMBS_LOCK.SLEEP to the application role.
D. Write your own procedure to mimic the functionality of the
DBMS_LOCK.SLEEP procedure.
---
Ans: A.
You cannot grant privileges on only one packaged procedure. You can only
grant EXECUTE
on the whole package. To be more restrictive in granting privileges, you
need to create an intermediate
procedure that calls the single procedure you want and grant EXECUTE on
that intermediate
procedure to the grantee.
17. Which of the following triggering events fire when statistics are
gathered on a table?
A. ANALYZE and DDL
B. ASSOCIATE STATISTICS and ANALYZE
C. GATHER STATISTICS and DDL
D. ASSOCIATE STATISTICS and DISASSOCIATE STATISTICS
---
Ans: A.
The ASSOCIATE STATISTICS and DISASSOCIATE STATISTICS events fire when an
ASSOCIATE
STATISTICS or DISASSOCIATE STATISTICS statement is executed, not when
statistics are gathered.
There is no GATHER STATISTICS triggering event. The triggering events
that will fire when
statistics are gathered on a table are the ANALYZE and DDL events.
18. Which Data Pump export mode captures user definitions?
A. Table mode
B. Tablespace mode
C. DBA mode
D. Schema mode
--
Ans: D.
Table and tablespace modes do not capture user account definitions.
Schema mode can, and
database mode always captures user definitions. There is no DBA mode.
19. You need to copy the GL schema from production to qa_test, changing
the tablespace for
indexes from gl_index to fin_indx. What is the best way to satisfy these
requirements?
A. First, use Data Pump to copy the schema without indexes. Then, change
the default tablespace
for user GL in qa_test to fin_indx. Next, use Data Pump to copy the
indexes. Finally, change
the default tablespace for user GL back to gl_data.
B. Use the dbms_metadata package to extract table and index DDL. Then,
use Notepad (or
sed) to edit this DDL, changing the tablespace for the indexes. Finally,
run the DDL in the
qa_test database.
C. Use Data Pump import, specifying a remap_datafile parameter to change
the datafile location
for indexes.
D. Use Data Pump import specifying a remap_tablespace parameter to
change the tablespace
location for indexes.
----
Ans: D.
Options A and B are a lot of work. The remap_datafile parameter applies
only to CREATE
TABLESPACE and CREATE DIRECTORY statements, not indexes. The
remap_tablespace parameter
tells Data Pump Import to change the tablespace that objects are stored
in between the source and
the target database.
20. Which of the following INSERT statements raises an exception?
A. INSERT INTO ORDERS SELECT * FROM STANDING_ORDERS
B. INSERT FIRST WHEN ORDER_TYPE IN (2,5,12) THEN INSERT INTO ORDERS
SELECT *
FROM STANDING_ORDERS
C. INSERT FIRST WHEN ORDER_TYPE IN (2,5,12) THEN INTO ORDERS SELECT *
FROM
STANDING_ORDERS
D. INSERT INTO ALL WHEN ORDER_TYPE IN (2,5,12) THEN INTO ORDERS SELECT *
FROM
STANDING_ORDERS
-----
Ans: D.
Single table inserts must begin with the keywords INSERT INTO and cannot
contain the keywords
THEN INTO. Multitable INSERT statements cannot begin with the keywords
INSERT INTO
and may contain the keywords THEN INTO. Option D contains an invalid
combination of keywords
and will thus raise an exception.
No comments:
Post a Comment