oracle database 10g:administration I

1. You are connecting to an Oracle database server from a client by using the

following connect string:
SQL> CONNECT hr/hr@pdserver.us.oracle.com:1521/proddb
Which naming method is being used in this case?
A.Local Naming
B.Easy Connect
C.External Naming
D.Directory Naming
Answer: B

2. User Scott has updated the salary of one of the employees in the EMPLOYEES table

and has not committed the transaction. What are the two types of locks that this

scenario would lead to? (Choose two.)
A.null lock on the row being updated
B.null lock on the table containing the row
C.ROW SHARE lock for the row being updated
D.ROW EXCLUSIVE lock for the row being updated
E.shared row-exclusive lock for the row being updated
F.a shareable table lock for the table containing the row
G.exclusive table-level lock for the table containing the row
Answer: DF

3. You are using the backup scheduler in Enterprise Manager (EM) to schedule a

backup of your database.
Which type of script does the backup scheduler generate?
A.SQL script
B.PL/SQL script
C.Operating System (OS) script
D.Enterprise Manager (EM) script
E.Recovery Manager (RMAN) script
Answer: E

4. The data file belonging to the SYSTEM tablespace is corrupted and no backup of

the file is available. How do you recover the data file?
A.The data file cannot be recovered.
B.The data file can be restored from the SYSTEM auto backup.
C.Take the tablespace offline, drop, and re-create it.
D.Recovery Manager (RMAN) is used to recover the data file.
E.The ALTER DATABASE CREATE DATAFILE.. command is used to recover the data file.
Answer: A

5. After being hired as a database administrator, you find that there is only one

database that is functional and that is being accessed by the applications. You want

to create a replica of the database, to be used for testing purposes.
What is the best method to create the replica?
A.create a database by using CREATE DATABASE .. command and manually copy the data
B.use Database Configuration Assistant (DBCA) to create a template from the existing

database to contain the database structure
C.use DBCA to create a template from the existing database to contain the database

structure and then manually copy the data using Oracle Data Pump
D.use DBCA to create a template from the existing database to contain the database

structure with data files and then use the same template to create the database in

the new location
Answer: D

6. You are creating a locally managed tablespace to meet the following requirements:
All the extents should be of the same size.
The data should be spread across two data files.
A bitmap should be used to record the free space within the allocated extents.
Which three options would you choose? (Choose three.)
A.set PCTFREE and PCTUSED to 50
B.specify extent allocation as Uniform
C.specify extent allocation as Automatic
D.create the tablespace as bigfile tablespace
E.create the tablespace as smallfile tablespace
F.set segment space management to Automatic
G.use the RESIZE clause while creating the tablespace

Answer: BEF

7. As a result of performance analysis, you created an index on the prod_name column

of the prod_det table, which contains about ten thousand rows. Later, you updated a

product name in the table. How does this change affect the index?
A.A leaf will be marked as invalid.
B.An update in a leaf row takes place.
C.The index will be updated automatically at commit.
D.A leaf row in the index will be deleted and inserted.
E.The index becomes invalid when you make any updates.
Answer: D

8. Which naming method uses the tnsnames.ora file to store the connect descriptor

used by the client while connecting to the database instance from a remote machine?
A.host naming method
B.local naming method
C.external naming method
D.directory naming method
Answer: B

9. In your database, the snapshot interval is set to 10 minutes for the Automatic

Workload Repository (AWR). The database instance is running for the past 30 days.
View the Exhibit to examine the Automatic Database Diagnostic Monitor (ADDM) task.
The ADDM task is performed every 10 minutes, except the last task. What could be the

reason for this?
A.The snapshot retention period for the AWR was increased before the last task.
B.The optimizer statistics for few objects in the database were locked before the

last task.
C.The STATISTICS_LEVEL initialization parameter was changed to BASIC and reset to

TYPICAL before the last task.
D.The optimizer statistics was collected manually by using the DBMS_STATS package

before the last task.
Answer: C

10. Your database is configured with the following parameters related to SGA:
SGA_TARGET=256MB
SHARED_POOL_SIZE=32MB
DB_CACHE_SIZE=100MB
LARGE_POOL_SIZE=0
JAVA_POOL_SIZE=0
STREAMS_POOL_SIZE=0
Which two statements are true about the configuration? (Choose two.)
A.The SGA_TARGET value cannot be sized smaller than 100 MB.
B.The shared pool and the default buffer pool will not be sized bigger than 32 MB

and 100 MB, respectively.
C.The shared pool and the default buffer pool will not be sized smaller than 32 MB

and 100 MB, respectively.
D.124 MB (256 minus 132) of memory is available for use by all the manually sized

components.
E.124 MB (256 minus 132) of memory is available for use by all the manually and

automatically sized components.
Answer: CE

11. Which two statements regarding archive log destinations are true? (Choose two.)
A.A maximum of 10 destinations can be specified.
B.The archive logs must be written to all the destinations.
C.The archive log files can be written only to local destinations.
D.The archiving information can be traced to the alert log file whenever the

archiving to a destination is successful.
E.The number of archiving destinations must be equal to the number of archive

processes (ARCn).
Answer: AD

12. Which dynamic performance view would you query to determine the space usage of

the Flash Recovery Area?
A.V$RECOVERY_FILE
B.V$RECOVERY_SPACE
C.V$RECOVERY_FILE_DEST
D.V$RECOVERY_FREE_SPACE
Answer: C

13. A user is dropped from the database. Which two statements are true about the

objects owned by this user? (Choose two.)
A.The objects would be deleted permanently.
B.The objects would be placed in the recycle bin.
C.The objects owned by user placed in the recycle bin would be purged.
D.The quota for the user remains until the objects in the recycle bin are purged.
E.The user object would be placed in the recycle bin, and the items in the recycle

bin would be purged.
Answer: AC

14. While planning to manage more than one database in your system, you perform the

following activities:
1. Organize different categories of files into independent subdirectories.
2. Use consistent naming convention for database files.
3. Separate administrative information pertaining to each database.
Which option corresponds to the type of activities performed by you?
A.Oracle Managed Files
B.Oracle Grid Architecture
C.Optimal Flexible Architecture
D.Oracle database architecture
Answer: C

15. The junior DBA in your organization has accidentally deleted the alert log file.

What will you do to create new alert log file?
A.Create the new text file file as ALERT.LOG.
B.You have to recover the alert log file from the valid backup.
C.Change the value for the BACKGROUND_DUMP_DEST parameter.
D.No action required. The file would be created automatically by the instance.
Answer: D

16. You want a local operating system (OS) user to access the Oracle database in the

system without specifying the username and password.
Which task would you perform to achieve this objective?
A.Create a user in the Oracle database with the same name as that of the OS user,

and set the OS_ROLES parameter to TRUE.
B.Create a user in the Oracle database with the same name and password as that of

the OS user, and assign password authentication.
C.Create a user in the Oracle database with the same name as that of the OS user,

prefixed with the value of the OS_AUTHENT_PREFIX parameter, and assign global

authentication.
D.Create a user in the Oracle database with the same name as that of the OS user,

prefixed with the value of the OS_AUTHENT_PREFIX parameter, and assign external

authentication.
Answer: D

17. Which three statements are correct about temporary tables? (Choose three.)
A.Indexes and views can be created on temporary tables.
B.Both the data and the structure of temporary tables can be exported.
C.Temporary tables are always created in a user's temporary tablespace.
D.The data inserted into a temporary table in a session is available to other

sessions.
E.Data manipulation language (DML) locks are never acquired on the data of temporary

tables.
Answer: ACE

18. Which three statements are true about the data dictionary in the Oracle

database? (Choose three.)
A.It is owned by the SYS user.
B.It should by modified directly by using SQL commands.
C.It contains the metadata of users, objects, constraints, and storage.
D.It is modified by the Oracle server when object definitions are changed.
E.Read consistency is not guaranteed for SELECT operations on data dictionary views.
Answer: ACD

19. User SCOTT executes the following command on the EMP table but has not issued

COMMIT, ROLLBACK, or any data definition language (DDL) command:
SQL> SELECT job FROM emp
2 WHERE job='CLERK' FOR UPDATE OF empno;
SCOTT has opened another session to work with the database. Which three operations

would wait when issued in SCOTT's second session? (Choose three.)
A.LOCK TABLE emp IN SHARE MODE;
B.LOCK TABLE emp IN EXCLUSIVE MODE;
C.DELETE FROM emp WHERE job='MANAGER';
D.INSERT INTO emp(empno,ename) VALUES (1289,'Dick');
E.SELECT job FROM emp WHERE job='CLERK' FOR UPDATE OF empno;
Answer: ABE

20. In the PROD database you have granted the RESUMABLE system privilege to the

CONNECT role. Resumable space operation has been enabled for all user sessions. You

want users NOT to be aware of any kind of space-related problems while performing

transactions. Instead, you want the problem to be resolved by a database trigger

automatically.
Which combination of triggering time and event would you use to achieve this

objective?
A.AFTER CREATE
B.AFTER SUSPEND
C.BEFORE CREATE
D.AFTER TRUNCATE
E.BEFORE SUSPEND
F.AFTER INSERT OR DELETE OR UPDATE
G.BEFORE INSERT OR DELETE OR UPDATE
='CLERK' FOR UPDATE OF empno;
Answer: B

21. You observe that in your PROD database, customer information is being modified

by some unauthorized users. You want to keep track of all of the transactions

happening on the table using PL/SQL. Which type of PL/SQL subprogram or construct

would you use to accomplish this task?
A.functions
B.packages
C.procedures
D.database triggers
E.anonymous PL/SQL block
Answer: D

22. You have been recently hired as a database administrator. Your senior manager

asks you to study the production database server and submit a report on the settings

done by the previous DBA. While observing the server settings, you find that the

following parameter has been set in the parameter file of the database:
REMOTE_OS_AUTHENT = TRUE
What could have been the reason to set this parameter as TRUE?
A.to enable operating system authentication for a remote client
B.to restrict the scope of administration to identical operating systems
C.to allow the start up and shut down of the database from a remote client
D.to enable the administration of the operating system from a remote client
E.to disable the administration of the operating system from a remote client

Answer: A

23. While creating a new user or changing the password for existing users, the

password must adhere to the restrictions specified below:
a) The password must contain at least five characters with one alphabetic character

in upper case, one
numeric and one special character.
b) The new password must be different from the old password.
c) The user name and password must be different.
Which option would you use to achieve this objective?
A.use a trigger to validate the password
B.use a user defined PL/SQL block to validate the password
C.use a profile to include the password verify function to validate the password
D.use Oracle's default password verification procedure to validate the password
E.use a procedure name in the CREATE USER command to validate the password
Answer: C

24. In your database, the users SYS and SYSTEM have been assigned the profiles,

ADMIN_PROF1 and ADMIN_PROF2, respectively. The rest of the database administrators

are assigned the profile ADMIN_PROF3.
You create a profile, USER_PROF, and assign it to the application user, Adams. After

a week you realize that the profile is no longer required and you decide to drop it.
What will Adam's profile be after the USER_PROF profile has been dropped?
A.no profiles
B.the default profile
C.same as the profile of SYS user
D.same as the profile of SYSTEM user

Answer: B

25. You manually create an additional listener process, L1, for databases PROD,

PAYDB and FINDB. While starting up the listener process, you get the following error

message:
LSNRCTL> start l1
Starting /u01/app/oracle/product/10.1.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.1.0.2.0 - Production
System parameter file is

/u01/app/oracle/product/10.1.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.1.0/db_1/network/log/l1.log
Error listening on: (ADDRESS=(PROTOCOL=TCP)(HOST=144.23.230.185)(port=8899))
TNS-12542: TNS:address already in use
TNS-12560: TNS:protocol adapter error
TNS-00512: Address already in use
Linux Error: 98: Address already in use
Listener failed to start. See the error message(s) above...
What action would you take to overcome this error without disrupting any existing

database communications?
A.stop and restart the listener process
B.set the TNS_ADMIN environmental variable
C.stop all running listener processes and restart the listener process
D.change the port number to an unused port number and restart the listener process
E.change the IP address to host machine name in the LISTENER.ORA file and restart

the listener process
Answer: D

26. You have a listener process, L1, currently listening for the connections to the

PROD and ADMIN databases. You create a new database, PAY, using CREATE DATABASE..

command. You have not enabled dynamic instance registration. You modified the

LISTENER.ORA file manually to include PAY database for L1 listener process.
Which command of Listener control utility (LSNRCTL) would you use to enable L1

process to start listening for the connections to the PAY database without

disrupting any existing database communications?
A.START
B.RELOAD
C.RESTART
D.REFRESH
E.START L1
F.RELOAD L1
G.RESTART L1
H.REFRESH L1
Answer: F

27. Your application demands frequent connection and disconnection from the

database. You have three listener processes that are listening for the database

PROD. While setting up the connect string using Oracle Enterprise Manager 10g

Database Control, which two options would you select to balance the connection load

across all the listener processes? (Choose two.)
A.Use only the first address.
B.Try one address, selected at random.
C.Try each address, in order, until one succeeds.
D.Try each address, randomly, until one succeeds.
Answer: BD

28. You are using three databases, DEVDB01, DEVDB02 and DEVDB03, on different host

machines in your development environment. The database server configurations, such

as IP address and listener port number, change frequently due to development

requirements, and you have the task of notifying the developers of the changes.
Which connection method would you use to overcome this overhead?
A.Host naming
B.Local naming
C.Easy Connect
D.External naming
E.Directory naming
Answer: E

29. Your database is not configured for session failover. Your tnsnames.ora file

contains the following details:
test.us.oracle.com=
Which feature is enabled in this case?
A.Load balancing
B.Instance failover
C.Database failover
D.Connect-time failover
E.Transparent Application Failover (TAF)
Answer: D

30. Client connection requests are failing because the listener is not responding.

You want to forward client connect requests to another listener if one listener is

not responding. How do you achieve this?
A.enable instance failover
B.configure shared server
C.enable database failover
D.enable connect-time failover
E.enable Transparent Application Failover (TAF)
Answer: D