Oracle database 10g:Administration I Sample Questions:

1. The HR user owns the EMP table. The HR user grants privileges to the SCOTT user by
using this command:
SQL> GRANT SELECT,INSERT,UPDATE ON emp TO scott WITH GRANT OPTION;
The SCOTT user executes this command to grant privileges to the JIM user:
SQL> GRANT SELECT,INSERT,UPDATE ON hr.emp TO jim;
Now the HR user decides to revoke privileges from JIM by using this command:
SQL> REVOKE SELECT,INSERT,UPDATE ON emp FROM jim;
Which statement is true after HR issues the REVOKE command?

A) The command succeeds and privileges are revoked from JIM.
B) The command fails because SCOTT still has privileges with him.
C) Only HR can perform SELECT, INSERT, and UPDATE operations on the EMP table.
D) The command fails because HR cannot revoke the privileges from JIM.

2. Due to media failure you lost one of the data files belonging to the USERS tablespace, and the tablespace is not available to use. Which statement regarding the status of the database is true?

A) The database remains open.
B) The database instance gets aborted.
C) The database gets dismounted.
D) The database gets shut down automatically.
E) The database becomes read only.
F) The status of the database depends on the status of the USERS tablespace.

3. The application development team has developed PL/SQL procedures and functions for different purposes and calls them as and when required. The loading of individual procedures or functions into memory degrades performance with every call. Also, it causes a security problem for individual subprograms and loss of program units when the whole system is transported into a new location. Which method would you recommend to the application developers to solve this problem?

A) creating PL/SQL packages to include interrelated subprograms
B) using anonymous PL/SQL blocks instead of subprograms
C) referring to views instead of tables inside the subprograms
D) avoiding the use of cursors in the subprograms

4. You are in the middle of a transaction and very crucial data has been modified. Because of a hardware failure, the instance has shut down before synchronizing all the database files. Which two statements are true? (Choose two.)

A) On startup, use RMAN to perform instance recovery.
B) On startup, all the files will be synchronized and you get both committed and uncommitted data.
C) On startup, SMON coordinates instance recovery.
D) On startup, perform media recovery and then instance recovery.
E) On startup, CKPT coordinates instance recovery.
F) Uncommitted changes will be rolled back after the database is opened.

5. In your database, the STATISTICS_LEVEL initialization parameter is set to BASIC. What is
the impact of this setting?

A) Only the timed operating system (OS) statistics and plan execution statistics are collected.
B) The optimizer statistics are collected automatically.
C) Snapshots cannot be collected manually by using DBMS_WORKLOAD_REPOSITORY PACKAG
D) The snapshots for the Automatic Workload Repository (AWR) are not generated automatically.
E) The Oracle server dynamically generates the necessary statistics on tables as part of query optimization.

Solutions: