Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. User A executes the following command to drop a large table in your database:
SQL> DROP TABLE trans;
While the drop table operation is in progress; user B executes the following command on the same table;
SQL> DELETE FROM trans WHERE tr_type='SL'
Which statement is true regarding the DELETE command?
A. It fails to delete the records because the records are locked in the SHARE mode
B. It deletes the rows successfully because the table is locked in the SHARE mod
C. It fails to delete the records because the table is locked in EXCLUSIVE mode
D. It deletes the rows successfully because the table is locked in SHARE ROW EXCLUSIVE mode
Answer: C
Q2. Your database is functional with a peak load for the last one hour. You want to preserve the performance statistics collected during this period to be used for comparison when you analyze the performance of the database in the future.
What action would you take to achieve this task?
A. Insert finding directives for ADDM tasks in the future.
B. Create a baseline on a pair of snapshots spanning the peak-load period.
C. Decrease the snapshot interval in the AWR to collect more snapshots during the peakload period.
D. Set the snapshot retention period in the Automatic Workload Repository (AWR) to zero to avoid automatic purging of snapshots.
Answer: B
Q3. You created a profile APP_USER and assigned it to the users. After a month, you decide to drop the profile. Some user sessions are currently connected to the database instance and are using the APP_USER profile.
This command is used to drop the profile:
SQL> DROP PROFILE app_user;
Which statement describes the result?
A. The command produces an error.
B. The profile is dropped and current user sessions use the DEFAULT profile immediately.
C. The profile is dropped and only the subsequent user sessions use the DEFAULT profile.
D. The profile is dropped, the sessions are terminated, and the subsequent user sessions use the DEFAULT profile.
Answer: A
Q4. You want to access employee details contained in flat files as part of the EMPLOYEE table. You plan to add a new column to the EMPLOYEE table to achieve this.
Which data types would you use for the new column?
A. CLOB
B. BLOB
C. BFILE
D. LONG RAW
Answer: C
Q5. User SCOTT wants to perform a bulk insert operation in the EMP_DEP table. SCOTT receives the following error after the INSERT statement is issued and few rows are inserted:
INSERT INTO EMP_DEP (emp_id,name,salary,dep_name,mgr_id)
*
ERROR at line 1:
ORA-01653: unable to extend table SCOTT.EMP_DEP by 128 in tablespace USERS
Identify two actions either of which will help you resolve this problem. (Choose two.)
A. Grant the RESOURCE role to SCOTT.
B. Add data files to the USERS tablespace.
C. Grant the CREATE ANY TABLE privilege to SCOTT.
D. Increase the space for SCOTT on the USERS tablespace.
E. Increase the size of the data file associated with the USERS tablespace.
Answer: BE
Q6. Your database is in shutdown state. What will happend if you issue next command:
SQL> startup
A. instance will started
B. instance started and DB is mounted
C. instance started, DB opened and finally mounted
D. instance started, DB mounted and finally opened
Answer: D
Q7. In which situation may the UNDO_RETENTION parameter be ignored, even if it is set to a value?
A. When the data file of the undo tablespace is autoextensible
B. When there are more than one undo tablespace available in the database
C. When the undo tablespace is of a fixed size and retention guarantee is not enabled
D. When the undo tablespace is autoextensible and retention guarantee is not enabled
Answer: C
Q8. Examine the command:
SQL>DBMS_STATS.SET_TABLE_PREFS('SH','CUSTOMERS','PUBLISH','false');
Which statement describes the effect of the above command?
A. Automatic statistics collection is stopped for the CUSTOMERS table
B. Statistics for the CUSTOMERS table are locked and cannot be overwritten
C. Existing statistics for the CUSTOMERS table become unusable for the query optimizer
D. Subsequently, statistics gathered on the CUSTOMERS table are stored as pending statistics
Answer: D
Q9. Given below is a list of scenarios:
1. A user terminates his session abnormally.
2. The connection between the server and the client application terminates because of a network failure.
3. A schema is accidentally dropped.
4. The tablespace is accidentally dropped from the database.
5. The hard disk gets corrupted and the data files in the disk are lost.
6. The database instance abnormally shuts down because of power failure.
Which scenarios require DBA intervention to perform recovery?
A. 1, 3, and 6
B. 4, 5, and 6
C. 3, 4, and 5
D. 1, 2, and 6
Answer: C
Q10. Which two statements are true regarding Oracle Data Pump? (Choose two.)
A. EXPDP and IMPDP are the client components of Oracle Data Pump
B. DBMS_DATAPUMP PL/SQL packages can be used indedendently of the DATA Pump clients
C. Oracle Data Pump export and import operations can be performed only by users with the SYSDBA privilege
D. Oracle Data Pump imports can be done from the export files generated in the Original Export Release 9.x
E. EXPDP and IMPDP use the procedures provided by DBMS_METADATA to execute export and import commands
Answer: AB
Q11. You plan to configure the new Oracle Database 11g installation by performing these tasks:
- Use Automatic Storage Management (ASM) as the storage option.
- Use Oracle Enterprise Manager Database Control to manage the database.
- Provide the same password for SYS, SYSTEM, SYSMAN, and DBSNMP accounts during the installation.
- Configure the database to send email notifications.
Some tools available to you for installation are:
1: Oracle Universal Installer (OUI)
2: Database Configuration Assistant (DBCA)
3: Database Upgrade Assistant (DBUA)
4: Oracle Net Configuration Assistant
Which tools would you use for this installation?
A. 2
B. 3
C. 1 and 2
D. 3 and 4
E. 1,2 and 4
Answer: E
Q12. You have an ORDERS table with the following structure:
The table has data in the ODATE column for all rows. Many orders are placed in a single day. You need to ensure that the ODATE column must contain data for every order in future.
Which method would serve the purpose?
A. Modify the column using the ALTER TABLE ... MODIFY command.
B. Add a UNIQUE constraint to the column using the ALTER TABLE ... ADD CONSTRAINT command.
C. Add a NOT NULL constraint to the column using the ALTER TABLE ... ADD CONSTRAINT command.
D. Add a PRIMARY KEY constraint to the column using the ALTER TABLE ... ADD CONSTRAINT command.
Answer: A
Q13. In your database instance, the user sessions are connected to the database server from the remote machines. You want to achieve the following for these users:
1. The user account must be locked after four unsuccessful login attempts.
2. The user must be prompted to change the password at regular intervals.
3. The user may not have more than three simultaneous sessions.
4. The user session must automatically be logged off if more than 10 minutes elapsed time used.
How would you accomplish the above?
A. By assigning profiles for the users
B. By implementing Fine-Grained Auditing (FGA)
C. By granting a secure application role to the users
D. By implementing the Database Resource Manager plan
Answer: A
Q14. You execute a command to resize a data file, sales.dbf, of size 200 MB in your database:
ALTER DATABASE DATAFILE '/remorse/sales.dbf' RESIZE 150M;
Which statement is true about this command?
A. The command must be issued in MOUNT state.
B. The command succeeds only if the data file is offline.
C. The command must be issued when the database is in the archivelog mode.
D. The command succeeds if no extent in the tablespace is currently allocated above the 150 MB boundary of the file.
Answer: D
Q15. You plan to implement the distributed database system in your company. You invoke Database Configuration Assistant (DBCA) to create a database on the server. During the installation, DBCA prompts you to specify the Global Database Name.
What must this name be made up of?
A. It must be made up of a database name and a domain name.
B. It must be made up of the value in ORACLE_SID and HOSTNAME.
C. It must be made up of the value that you plan to assign for INSTANCE_NAME and HOSTNAME.
D. It must be made up of the value that you plan to assign for ORACLE_SID and SERVICE_NAMES.
Answer: A