QUESTION 1
You have a text file that maintains information on thousands of items. The end-user application requires the transfer of that information into a table in the database. What would you use to achieve this task? (Choose two.)
A.Data Pump
B.SQL*Loader
C.External table
D.Oracle Text
E.Oracle Import
Correct Answer: BC
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 2
Which step do you need to perform to enable a user with the SYSDBA privilege to log in as SYSDBA in iSQL*Plus?
A.The user must be granted the database administrator (DBA) privilege.
B.Set up a user in the Oracle Application Server Containers for J2EE (OC4J) user manager, and grant the webDba role to the user.
C.The user must be listed in the password file for the authentication.
D.No special setup is needed for the user to connect as SYSDBA in iSQL*Plus.
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 3
You specified extent management as local for a tablespace. How will it affect space management in the tablespace?
A.The tablespace will be system managed and the users cannot specify the extent size.
B.Free extents will be managed by the data dictionary tables.
C.All the extents will be of the same size.
D.Bitmap will be used to record free and allocated extents.
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 4
Which two operations require undo data? (Choose two.)
A.recovering from failed transactions
B.recording a transaction to redo log files
C.rolling back a transaction
D.committing a transaction
E.rolling forward during instance recovery
Correct Answer: AC
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 5
The employee IDs of employees who have made sales in the company are transferred from the EMPLOYEES table to the BONUS table with a default bonus value. Later, the human resources department decides to give bonuses to employees as per the following conditions:
1. Employees with a salary of $8,000 or less should receive a bonus.
2. Employees who have not made sales get a bonus of 1% of their salary.
3. Employees who already made sales get an increase in their bonus equal to 1% of their salary.
What would you do to implement these changes in one step?
A.Use SQL*Loader utility.
B.Use the MERGE statement.
C.Use a multitable insert operation.
D.Use a correlated UPDATE statement.
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 6
The ST_INFO master table has millions of rows that are updated very rarely. It has a STATE_CODE column that contains the value for 25 states. The table is frequently queried on the STATE_CODE column. Which type of index would you suggest to improve the query performance?
A.bitmap
B.B-tree
C.function based
D.reverse key
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 7
You want to monitor and control the resource usage by sessions. You want to be warned automatically when more than 100 sessions are opened with your database. What action would you take to achieve this?
A.Set the warning threshold for the Current Logons Count metric.
B.Set the limits in the profiles used by users.
C.Use the Database Resource Manager.
D.Modify the SESSIONS initialization parameter.
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 8
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.a shareable table lock for the table containing the row
B.null lock on the table containing the row
C.exclusive table-level lock for the table containing the row
D.null lock on the row being updated
E.ROW SHARE lock for the row being updated
F.shared row-exclusive lock for the row being updated
G.ROW EXCLUSIVE lock for the row being updated
Correct Answer: AG
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 9
Which statement regarding the contents of the V$PARAMETER view is true?
A.displays only the list of default values
B.displays the current contents of the server parameter file
C.displays the currently in effect parameter values
D.displays the list of all the parameter files of a database
E.displays only the list of all advanced parameters
F.displays only the list of all basic parameters
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 10
Redo log files are not multiplexed in your database. Redo log blocks are corrupted in group 2, and archiving has stopped. All the redo logs are filled and database activity is halted. Database writer has written everything to disk. Which command would you execute to proceed further?
A.ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;
B.RECOVER LOGFILE BLOCK GROUP 2;
C.ALTER DATABASE CLEAR LOGFILE GROUP 2;
D.ALTER DATABASE DROP LOGFILE GROUP 2;
E.ALTER DATABASE RECOVER LOGFILE GROUP 2;
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 11
Which three statements are true regarding the fine-grained auditing (FGA)? (Choose three.)
A.FGA is possible on SELECT statements only.
B.The audit trail for FGA is stored in the AUD_LOG$ table.
C.FGA includes the SQL statement used by the user as part of the audit event entry.
D.FGA enables a SQL predicate to define when to audit an event.
E.The audit trail for FGA is stored in the FGA_LOG$ table.
F.FGA audits DELETE statements only when audit columns are specified.
Correct Answer: CDE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 12
Your database instance is started using the server parameter file (SPFILE). Control files are multiplexed and stored on different disks. Because of a disk failure, you lost one of these control files. You replaced the damaged disk. What is the correct sequence of steps that you would follow to recover the control file?
1. Shut down the instance, if not already done.
2. Copy one of the remaining control files to a new location.
3. Change the value of the CONTROL_FILES initialization parameter to correspond to the new location of the control files.
4. Start up the database instance to the NOMOUNT stage.
5. Recover the database to the point of failure of the control file.
6. Open the database.
A.2,4,3,4,5
B.5,2,3,4
C.4,5,6,2,3
D.1,2,4,3,5
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 13
You executed the STARTUP MOUNT command to start your database. For which database operation do you need to start the database in the MOUNT state?
A.dropping a user in your database
B.dropping a tablespace in your database
C.enabling or disabling redo log archiving
D.renaming the control files
E.re-creating the control files, after you lost all the control files in your database
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 14
View the Exhibit to see the source and target databases.
You have created a database link, devdb.us.oracle.com, between the databases PRODDB and DEVDB. You want to import schema objects of the HR user using Oracle Data Pump from the Exammitra.com development database, DEVDB, to the production database, PRODDB. You execute the following command on the target database server:
$impdp system/manager directory = DB_DATA
dumpfile = schemas.dat
schemas = hr
flashback_time = 2004-02-03 09:00
The command fails, displaying the following error:
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/home/oracle/schema/schemas.dat" for read
ORA-27037: unable to obtain file status
What would you do to overcome the error?
A. remove the dumpfile option in the command
B.remove the flashback_time option in the command
C.remove the dumpfile option and add the network_link = devdb.us.oracle.com option in the command
D.add the user, SYSTEM, to the schemas option in the command
E.remove the schemas option and add the network_link = devdb.us.oracle.com option in the command
F.add network_link = devdb.us.oracle.com option in the command
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 15
You want to create a tablespace with the following specifications:
1. The tablespace extends automatically.
2. Used and free extents should be managed by bitmaps.
3. Default PCTUSED attribute is set to 60.
4. All the extents would be of size 1 MB.
Which three options would you choose to create the tablespace? (Choose three.)
A.tablespace with segment space management as automatic
B.tablespace with AUTOEXTEND enabled
C.tablespace with a uniform extent allocation of 1 MB
D.tablespace with dictionary-managed extents
E.tablespace with segment space management as manual
Correct Answer: BCE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 16
Examine the following commands executed in your database:
SQL> ALTER SESSION RECYCLEBIN=ON;
Session altered
SQL> CREATE TABLE emp TABLESPACE tbsfd AS SELECT * FROM hr.employees; Table created.
Further, you executed the following command to drop the table:
SQL> DROP TABLE emp; Table dropped.
What happens in this scenario?
A.The table is moved to the SYSTEM tablespace.
B.The table is renamed and remains in the TBSFD tablespace.
C.The table is moved to the SYSAUX tablespace.
D.The table is removed from the database permanently.
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 17
Which two statements are true about the roles in the Oracle database? (Choose two.) ActualTests.com
A.A role cannot be assigned external authentication.
B.Roles can be granted to other roles.
C.Roles are owned by the SYS user.
D.A role can be granted to itself.
E.A role can contain both system and object privileges.
Correct Answer: BE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 18
The user SCOTT executes the following command successfully to increase the salary values in one of his sessions:
SQL> UPDATE emp SET sal=sal*1.15 WHERE deptno=20;
Before SCOTT ends the transaction, user HR who has the privileges on EMP table executes a query to fetch the salary details but finds the old salary values instead of the increased values.
Why does HR still see the old data?
A.because of redo data from redo log file
B.because of data from a temporary tablespace
C.because of undo data from the undo tablespace
D.because of data from database buffer cache
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 19
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 STATISTICS_LEVEL initialization parameter was changed to BASIC and reset to TYPICAL before the last task.
C.The optimizer statistics was collected manually by using the DBMS_STATS package before the last task.
D.The optimizer statistics for few objects in the database were locked before the last task.
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 21
You want to protect your database from single point of failure by multiplexing the control file. Your database is started using SPFILE. You have to perform the following tasks:
1. Shut down the database instance.
2. Copy the control file to new location.
3. Configure the CONTROL_FILES parameter.
4. Restart the instance.
Select the correct order in which these tasks need to be performed.
A.3,2,1,4
B.3,1,2,4
C.2,3,1,4
D.1,2,3,4
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 22
Which two statements are true regarding the database in ARCHIVELOG mode? (Choose two.)
A.Archiving information is written to the data files and redo log files.
B.You can perform complete database backups without closing the database.
C.Online redo log files have to be multiplexed before putting the database in ARCHIVELOG mode.
D.All the previous database backups become invalid after you configure the database to ARCHIVELOG mode.
E.You have to shut down the database to perform the backups.
Correct Answer: BD
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 23
Which three statements are true about the stages of database startup? (Choose three.)
A.Data files and redo log files can be renamed at the MOUNT stage.
B.Control files are required to bring the database to the NOMOUNT stage.
C.Data files and online redo log files are checked for consistency while opening the database.
D.Data files and redo log files are made available to users at the OPEN stage.
E.Control files are read at the OPEN stage for the location of data files.
Correct Answer: ACD
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 24
Which is the memory area that is created when a dedicated server process is started, and contains data and control information for that server process?
A. SGA
B.Streams Pool
C.Shared Pool
D.PGA
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 25
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 fails because SCOTT still has privileges with him.
B.Only HR can perform SELECT, INSERT, and UPDATE operations on the EMP table.
C.The command fails because HR cannot revoke the privileges from JIM.
D.The command succeeds and privileges are revoked from JIM.
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 26
View the Exhibit.
Which statement regarding the dept and emp tables is true?
A.When you delete a row from the dept table, automatically the corresponding rows are updated with null values in the emp table.
B.When you delete a row from the dept table, automatically the corresponding rows are deleted from the emp table.
C.When you delete a row from the emp table, automatically the corresponding rows are updated with null values in the dept table.
D.When you delete a row from the emp table, automatically the corresponding rows are deleted from the dept table.
E.When you delete a row from the emp table, you would receive a constraint violation error.
F.When you delete a row from the dept table, you would receive a constraint violation error.
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 27
You created a response file and want to check it before starting installations in silent mode. You started installation of Oracle software in interactive mode by providing the response file. In the middle of the installation, you realize that the behavior of the Oracle Universal Installer (OUI) is not consistent with the response file. What action would you take to detect the cause of this behavior?
A.Refer to the alert log file for information regarding the actions performed by OUI during installation.
B.Refer to the contents of the oraInst.loc file to verify the steps performed by OUI.
C.Compare the contents of the installActions.log file with that of the response file.
D.Compare the contents of the install.log file with the response file.
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 28
In which situation would you use the Oracle Shared Server configuration?
A. when performing export and import using Oracle Data Pump
B. when performing batch processing and bulk loading operation in a data warehouse environment
C.in an online transaction processing (OLTP) system where large number of client sessions are ActualTests.com idle most of the time
D.when performing backup and recovery operations using Oracle Recovery Manager
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 29
You find that the database performance degrades while you backup the PROD database using Recovery Manager (RMAN). The PROD database is running in shared server mode. The database instance is currently using 60% of total operating system memory. You suspect the shared pool fragmentation to be the reason. Which action would you consider to overcome the performance degradation?
A.Increase the total System Global Area (SGA) size to increase memory hits.
B.Increase Database Buffer Cache size to increase cache hits.
C.Increase Shared Pool size to cache more PL/SQL objects.
D.Configure Java Pool to cache the java objects.
E.Configure Streams Pool to enable parallel processing.
F.Configure Large Pool to be used by RMAN and shared server.
Correct Answer: F
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 30
View the Exhibit to see the structure of the EMPLOYEES and DEPARTMENTS tables.
Your organization plans to dissolve the department with department ID 30. You execute the following command to delete rows from the DEPARTMENTS table:
SQL>delete from DEPARTMENTS where DEPT_ID = 30;
The command fails and displays the following error:
ERROR at line 1: ORA-02292: integrity constraint (HR.SYS_C005374) violated - child record found
Which two actions would you take to overcome this error? (Choose two.)
A.first, delete rows from the EMPLOYEES table for department id 30 and then delete the rows from the DEPARTMENTS table for department id 30
B.first, drop the EMPLOYEES table and then delete the rows from the DEPARTMENTS table
C.first, drop the DEPARTMENTS table and then delete the rows from the EMPLOYEES table
D.alter the foreign key constraint to include the on delete cascade option
E.first, delete all of the rows from EMPLOYEES table and then delete the rows from the DEPARTMENTS table for department id 30
F.alter the foreign key constraint to include the cascade option
Correct Answer: AD
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 31
Which two statements are true about the primary key constraint in a table? (Choose two.)
A.It is not possible to disable the primary key constraint.
B.The primary key constraint can be imposed by combining more than one column.
C.The non-deferrable primary key constraint creates an unique index on the primary key column if it is not already indexed.
D.The primary key constraint can be referred by only one foreign key constraint.
E.It is possible to have more than one primary key constraint in a single table.
Correct Answer: BC
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 32
You are working on an online transaction processing (OLTP) system. You notice that a PL/SQL procedure got executed twice at 2:00 p.m. This has incorrectly updated the EMP_SAL table. How would you revert the table to its state at 2:00 p.m.?
A.Perform point-in-time recovery to 2:00 p.m. ActualTests.com
B.Issue the rollback statement with system change number (SCN).
C.Restore the entire database from the recent backup and open it.
D.Use Flashback Table feature to revert the changes.
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 33
While running the Oracle Universal Installer on a Unix platform to install Oracle Database 10g software, you are prompted to run orainstRoot.sh script. What does this script accomplish?
A.It creates the Inventory pointer file.
B.It modifies the Unix kernel parameters to match Oracle's requirement.
C.It creates the Oracle user for installation.
D.It creates the base directory.
E.It creates the pointer file.
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 34
You executed the following command to back up your control file:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
Which initialization parameter is used to specify the location of the trace file?
A.BACKUP_DUMP_DEST
B.USER_DUMP_DEST
C.TRACE_DUMP_DEST
D.CORE_DUMP_DEST
E.BACKGROUND_DUMP_DEST
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 35
You require the Oracle server to manage the undo segments and space among various active sessions automatically. You created an undo tablespace, UNDO_TBS1, in your database. Which two additional steps would you perform to achieve this? (Choose two.)
A.Create an initial undo segment in the undo tablespace.
B.Enable the retention guarantee for the undo tablespace.
C.Set the UNDO_TABLESPACE parameter to UNDO_TBS1.
D.Set the UNDO_MANAGEMENT initialization parameter to AUTO.
E.Set the UNDO_RETENTION parameter to 900 or more.
Correct Answer: CD
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 36
Examine the following commands executed in your database:
SQL> ALTER SESSION RECYCLEBIN=ON; Session altered SQL> CREATE TABLE emp TABLESPACE tbsfd AS SELECT * FROM hr.employees; Table created.
Further, you executed the following command to drop the table:
SQL> DROP TABLE emp; Table dropped.
What happens in this scenario?
A.The table is moved to the SYSTEM tablespace.
B.The table is removed from the database permanently.
C.The table is moved to the SYSAUX tablespace.
D.The table is renamed and remains in the TBSFD tablespace.
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 37
You perform differential incremental level 1 backups of your database on each working day and level 0 backup on Sundays. Which two statements are true about the differential incremental backups? (Choose two.)
A.The backup performed on each working day contains all the blocks that have changed since the last level 0 backup.
B.The backup performed on Sundays contains all the blocks that have changed since the last level 1 backup.
C.The backup performed on Sundays contains all the blocks that have ever been use in the ActualTests.com database.
D.The backup performed on each working day contains all the blocks that have changed since the last level 0 or level 1 backup.
Correct Answer: CD
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 38
Which two statements about Flashback Query are true? (Choose two.)
A.It helps in row-level recovery from user errors.
B.It is generated by using the redo log files.
C.It fails when undo data pertaining to the transaction is overwritten.
D.The database has to be opened with the resetlogs option after performing Flashback Query.
E.It can be performed to recover ALTER TABLE statements
Correct Answer: AC
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 39
You want to refer the employee personal information stored in operating system(OS) files in EMPLOYEE table. You plan to add a new column to EMPLOYEE table to achieve this. Which data type would you use for the new column?
A.CLOB
B.LONG RAW
C.BFILE
D.BLOB
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 40
A user complains that he gets the following error message repeatedly after executing some SQL statements.
The error message forces the user to log off from and log on to the database to continue his work.
ORA-02392: exceeded session limit on CPU usage, you are being logged off
Which action would you take to increase the session limit on CPU usage?
A.Modify the object privileges assigned to the user.
B.Modify the system privileges assigned to the users.
C.Modify the roles assigned to the users.
D.Modify the value for the RESOURCE_LIMIT parameter in the parameter file.
E.Modify the profile assigned to the user.
Correct Answer: E
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 41
You execute the following command to audit the database activities:
SQL> AUDIT DROP ANY TABLE BY scott BY SESSION WHENEVER SUCCESSFUL; What is the effect of this command?
A.One audit record is created for the whole session if user SCOTT successfully drops one or more tables in his session.
B.One audit record is generated for the session when SCOTT grants the DROP ANY TABLE privilege to other users in his session.
C.One audit record is created for each successful DROP TABLE command executed in the session of SCOTT.
D.One audit record is created for each successful DROP TABLE command executed by any user to drop tables owned by SCOTT.
E.One audit record is created for every session when any user successfully drops a table owned by SCOTT.
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 42
You execute the following command to audit the database activities:
SQL> AUDIT DROP ANY TABLE BY scott BY SESSION WHENEVER SUCCESSFUL;
What is the effect of this command?
A.One audit record is created for each successful DROP TABLE command executed in the session of SCOTT.
B.One audit record is created for each successful DROP TABLE command executed by any user to drop tables owned by SCOTT.
C.One audit record is created for the whole session if user SCOTT successfully drops one or more tables in his session.
D.One audit record is generated for the session when SCOTT grants the DROP ANY TABLE privilege to other users in his session.
E.One audit record is created for every session when any user successfully drops a table owned by SCOTT.
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 43
The SCOTT user has an index on the ITEM_DESC column of the ITEM table. As part of the year- ending task, SCOTT updates the ITEM_DESC column for most of the rows in the ITEM table. How does this change to the table affect the index?
A.An update in a leaf row takes place.
B.The index becomes invalid after the update.
C.The leaf block containing the row to be updated is marked as invalid.
D.A row in the leaf block of the index for the key value is deleted and inserted.
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 44
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 gets shut down automatically.
B.The database remains open.
C.The status of the database depends on the status of the USERS tablespace.
D.The database gets dismounted.
E.The database instance gets aborted.
F.The database becomes read only.
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 45
Which three descriptions are correct about the effects of the TRUNCATE command on a table? (Choose three.)
A.Delete triggers on the table are fired during the execution of the TRUNCATE command.
B.The high-water mark (HWM) is set to point to the first useable data block in the table segment.
C.The corresponding indexes for the table are also truncated.
D.Very little or no undo data is generated during the execution of the TRUNCATE command.
E.The child table is truncated when the TRUNCATE command is applied on the parent table.
Correct Answer: BCD
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 46
Your alert log file has the following information:
Tue May 25 17:43:38 2004 ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/ORCL/udump/orcl_ora_3173.trc. What would you do to ensure that database is still running correctly?
A.examine the trace file and kill the session that caused the deadlock
B.examine the trace file for details and remember that deadlocks are resolved automatically by Oracle database
C.examine the trace file for details and ask the user who caused the deadlock to roll back the transaction
D.examine the trace file for details and use the Undo Advisor to rollback the transaction that caused the deadlock
E.examine the trace file and kill both the sessions responsible for the deadlock
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 47
When you try to start the Database Control by using the emctl start dbconsole command the following error is displayed:
TZ set to America/New_york OC4J Configuration issue. /u01/app/oracle/product/10.1.0/db_1/oc4j/j2ee/OC4J_DBConsole_orcl.us.oracle.com not found.
Which two environment variables do you need to set appropriately to avoid such errors, and start the Database Control successfully? (Choose two.)
A.ORACLE_SID
B.ORACLE_BASE
C.NLS_LANG
D.ORACLE_HOME
E.LD_LIBRARY_PATH
Correct Answer: AD
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 48
Your database is functional with peak load for one hour. You want to preserve the performance statistics collected during this period so that they can be used for comparison while analyzing the performance of the database in the future. What action would you take to achieve this task?
A.Set the snapshot retention period in the Automatic Workload Repository (AWR) to zero to avoid the automatic purging of the snapshots.
B.Set the STATISTICS_LEVEL initialization parameter to ALL.
C.Create a baseline on a pair of snapshots that have the statistics of the peak-load period in the database.
D.Decrease the snapshot interval in the AWR to collect more number of snapshots during the peak-load period.
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 49
Your database is having two control files, three redo log file groups with two members in each group. Failure of which file would cause an instance to shut down?
A.one of the redo log members
B.any control file
C.loss of the initialization parameter file
D.any archive log file
E.any data file belonging to the default permanent tablespace
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 50
View the Exhibit.
The DBA creates a new user as follows:
SQL> CREATE USER manfus IDENTIFIED BY manfus TEMPORARY TABLESPACE temp; ActualTests.com User manfus creates a table as follows:
SQL> CREATE TABLE material(id NUMBER(3));
In which tablespace will the material table be created?
A.TEMP
B.SYSAUX
C.PROD
D.SYSTEM
E.USERS
Correct Answer: E
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 51
You are working on the 24X7 database with high transaction volume, to ensure faster instance recovery on your database you set the FAST_START_MTTR_TARGET initialization parameter to a very low value. What effect it will have on the database?
A.The overall database performance would be degraded. "Pass Any Exam. Any Time." - www.exammitra.com Oracle 1z0-042: Practice Exam
B.The database performance would be enhanced.
C.The mean time to recover (MTTR) would be increased.
D.The redo log files would be get filled more frequently.
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 52
Which two statements are true about simple views? (Choose two.)
A. Views can be created as read only.
B.Data manipulation language (DML) operations cannot be performed on views.
C.Views are data segments like tables.
D.Views can be created on the basis of more than one table.
Correct Answer: AD
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 53
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.124 MB (256 minus 132) of memory is available for use by all the manually sized components.
B.124 MB (256 minus 132) of memory is available for use by all the manually and automatically sized components.
C.The shared pool and the default buffer pool will not be sized smaller than 32 MB and 100 MB, respectively.
D.The shared pool and the default buffer pool will not be sized bigger than 32 MB and 100 MB, respectively.
E.The SGA_TARGET value cannot be sized smaller than 100 MB.
Correct Answer: BC
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 54
Which three statements are true regarding the logical structure of the Oracle database? (Choose three.)
A.A data block is the smallest unit of I/O for data files.
B.Each segment contains one or more extents.
C.Multiple tablespaces can share single data file.
D.Each data block in the database always corresponds to one OS block.
E.It is possible to have tablespaces of different block sizes in a database.
Correct Answer: ABE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 55
View the Exhibit and examine the output.
Which statement describes the conclusion?
A.The dictionary cache is consuming more space than the library cache.
B.Preparing indexes on the tables used in the SQL statements would improve the library cache performance.
C.The shared pool size should be increased to accommodate the SQL statements.
D.The users should use bind variables instead of literals in the query.
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 56
Which two statements about Flashback Query are true? (Choose two.)
A.The database has to be opened with the resetlogs option after performing Flashback Query.
B.It can be performed to recover ALTER TABLE statements
C.It fails when undo data pertaining to the transaction is overwritten.
D.It helps in row-level recovery from user errors.
E.It is generated by using the redo log files. "Pass Any Exam. Any Time." - www.actualtests.com 25 Oracle 1z0-042: Practice Exam
Correct Answer: CD
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 57
Which two are valid locking levels that are used by transactions in an Oracle database? (Choose two.)
A.object level
B.block level
C.schema level
D.row level
E.database level
Correct Answer: AD
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 58
Your database is in NOARCHIVELOG mode and a logswitch happens every 20 minutes. On Thursday morning, media failure resulted in corruption of a data file belonging to the TOOLS tablespace. Backups are taken on every Sunday. What would you do in this situation?
A.restore the data files from backup and perform cancel-based recovery
B.restore the data files from backup and perform a complete recovery
C.restore only the corrupted data file and perform tablespace recovery
D.recover using Oracle Flashback Database technology
E.restore the entire database from the most recent backup and start the instance and open the database
Correct Answer: E
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 59
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.The index becomes invalid when you make any updates.
B.The index will be updated automatically at commit.
C.A leaf row in the index will be deleted and inserted.
D.A leaf will be marked as invalid.
E.An update in a leaf row takes place.
"Pass Any Exam. Any Time." - www.actualtests.com 26
Oracle 1z0-042: Practice Exam
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 60
The session of user SCOTT receives the following error after executing an UPDATE command on the EMP table:
ERROR at line 1: ORA-00060: deadlock detected while waiting for resource
On investigation, you find that a session opened by user JIM has a transaction that caused the deadlock.
Which two statements are true regarding the session of SCOTT in this scenario? (Choose two.)
A.SCOTT has to reexecute the last command in the transaction after he commits the transaction.
B.SCOTT should perform a COMMIT or ROLLBACK to allow JIM to continue with his transaction.
C.It is terminated after receiving the error and JIM can continue with his transaction.
D.The transaction in it is terminated after receiving the error and JIM can continue with his transaction.
Correct Answer: AB
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 61
You want to create a new optimized database for your transactional production environment to be used by a financial application. While creating the database, you want the Oracle software to take care of all basic settings to optimize the database performance. Which method would you use to ActualTests.com achieve this objective?
A.Use Enterprise Manager to create a new database with the Online Transaction Processing (OLTP) option.
B.Use the CREATE DATABASE .. command to create the database with Oracle-managed files.
C.Use the CREATE DATABASE .. command to create the database with Automatic Storage Management (ASM) file system.
D.Use Database Configuration Assistant (DBCA) to create the database with Transaction Processing template.
E.Use the Database Configuration Assistant (DBCA) to create the database with Oracle-managed files.
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 62
The UNDO_RETENTION parameter in your database is set to 1000 and undo retention is not guaranteed. Which statement regarding retention of undo data is correct?
A.Undo data will be retained in the UNDO tablespace for 1,000 seconds, then it gets moved to the TEMPORARY tablespace to provide read consistency.
B.Undo data will be stored permanently after 1,000 seconds.
C.Undo data becomes obsolete after 1,000 seconds.
D.Undo data gets refreshed after every 1,000 seconds.
E.Committed undo data would be retained for 1,000 seconds if free undo space is available.
Correct Answer: E
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 63
Your database is open, and you plan to perform Recovery Manager (RMAN) backups. Which three statements are true about these backups? (Choose three.)
A.The backups would be possible only if the database is running in ARCHIVELOG mode.
B.The backups would be consistent.
C.The backups need not be restored during recovery in case of a media failure.
D.The backups need to be restored and database has to be recovered in case of a media failure.
E.The backups would be possible only if the database is running in NOARCHIVELOG mode.
F.The backups would be inconsistent.
Correct Answer: ADF
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 64
While setting up an Oracle database for one of your critical applications, you want to ensure that the database is backed up at regular intervals without your intervention. What should you do to achieve the objective?
A.schedule the database backup using DBMS_JOB package after creating the database
B.schedule the database backup using Database Configuration Assistant (DBCA) while creating the database
C.schedule the database backup using Recovery Manager (RMAN) commands after creating the database
D.configure the database to run in ARCHIVELOG mode
"Pass Any Exam. Any Time." - www.actualtests.com 28 Oracle
1z0-042: Practice Exam
E.configure the Flash Recovery Area to enable automatic database backup
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 65
You have a large amount of historical data in an operating system file. Some analysts in your organization need to query this data. The file is too large to load into your current database. Which is the most effective method to accomplish the task?
A.Load the data into your database by using the PARALLEL clause.
B.Upgrade the hardware/memory to accommodate the data.
C.Use an external table so you can have the metadata available in your database, but leave the data in the operating system files.
D.Give analysts DBA privilege, so that they can query DBA_EXTERNAL_TABLES.
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 66
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.No action required.The file would be created automatically by the instance.
D.Change the value for the BACKGROUND_DUMP_DEST parameter.
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 67
Users of HR schema complain about slower-than-normal performance. On investigation the DBA found that maintenance was recently performed on some of the tables. The DBA traced the query that takes longer than normal to execute.
View the Exhibit exhibit_before.
After the DBA resolves the problem, the query performs normally.
View the Exhibit exhibit_after.
"Pass Any Exam. Any Time." - www.actualtests.com 29
Oracle 1z0-042: Practice Exam
What action would the DBA have taken to resolve the performance problem?
A.moved the indexes associated with the EMPLOYEES table to the same tablespace where the EMPLOYEES table exists
B.moved the EMPLOYEES table in to a locally managed tablespace
C.moved the EMPLOYEES table to another location in the same tablespace ActualTests.com
D.analyzed the EMPLOYEES table to collect the current statistics
E.reorganized the associated indexes for the EMPLOYEES table that were in an unusable state
Correct Answer: E
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 68
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.
"Pass Any Exam. Any Time." - www.actualtests.com 30
Oracle 1z0-042: Practice Exam
Which three options would you choose? (Choose three.)
A.set PCTFREE and PCTUSED to 50
B.set segment space management to Automatic
C.specify extent allocation as Automatic
D.specify extent allocation as Uniform
E.use the RESIZE clause while creating the tablespace
F.create the tablespace as smallfile tablespace
G.create the tablespace as bigfile tablespace
Correct Answer: BDF
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 69
View the Exhibit to observe the roles assigned to the SCOTT user.
Which statement is true about the assignment of the SELECT_CATALOG_ROLE role to the SCOTT user?
A.The user needs to enable the role explicitly.
B.The user can start using the role immediately.
C.The user can grant the role to other users.
D.The user cannot use the role at all.
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 70
"Pass Any Exam. Any Time." - www.actualtests.com 31
Oracle 1z0-042: Practice Exam
Which two statement about Automatic Storage Management (ASM) are true? (Choose two.)
A.ASM can be used to store trace files, alert log files, and the server parameter file (SPFILE).
B.ASM supports the Oracle database and operating system files.
C.ASM provides automatic load balancing across all ASM disks.
D.ASM provides mirroring on file by file basis.
Correct Answer: CD
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 71
A constraint in a table is defined with the INITIALLY IMMEDIATE clause. You executed the ALTER TABLE command with the ENABLE VALIDATE option to enable the constraint that was disabled. What are the two effects of this command? (Choose two.)
A.It does not validate the existing data in the table.
B.It prevents insert, update, and delete operations on the table while the constraint is in the process of being enabled.
C.It enables the constraint to be enforced at the end of each transaction.
D.It fails if any existing row violates the constraint.
Correct Answer: BD
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 72
Which three pieces of information are to be mandatorily provided while creating a new listener using Enterprise Manager Database Control? (Choose three.)
A.the port used by the listener ActualTests.com
B.the database services to be registered with the listener
C.the server name where the listener runs
D.the log file and trace file destination for the listener
E.the protocol used by the listener
Correct Answer: ACE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 73
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:
"Pass Any Exam. Any Time." - www.actualtests.com 32
Oracle 1z0-042: Practice Exam
REMOTE_OS_AUTHENT = TRUE
What could have been the reason to set this parameter as TRUE?
A.to disable the administration of the operating system from a remote client
B.to allow the start up and shut down of the database from a remote client
C.to enable operating system authentication for a remote client
D.to restrict the scope of administration to identical operating systems
E.to enable the administration of the operating system from a remote client
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 74
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.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
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.create a database by using CREATE DATABASE .. command and manually copy the data
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 75
View the Exhibit.
Which statement causes more undo generation?
"Pass Any Exam. Any Time." - www.actualtests.com 33
Oracle 1z0-042: Practice Exam
A.DELETE FROM emp WHERE empno=7934;
B.UPDATE emp SET comm=400 WHERE empno=7844;
C.INSERT INTO emp VALUES(7999,'JHON','CLERK',7782,'10-MAY-83',1500,NULL,10);
D.SELECT * FROM emp;
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 76
Which step do you need to perform to enable a user with the SYSDBA privilege to log in as SYSDBA in iSQL*Plus? ActualTests.com
A.No special setup is needed for the user to connect as SYSDBA in iSQL*Plus.
B.Set up a user in the Oracle Application Server Containers for J2EE (OC4J) user manager, and grant the webDba role to the user.
C. The user must be listed in the password file for the authentication.
D.The user must be granted the database administrator (DBA) privilege.
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 77
Data files of which three tablespaces can be recovered by performing an open recovery? (Choose three.)
"Pass Any Exam. Any Time." - www.actualtests.com 34
Oracle 1z0-042: Practice Exam
A.INDEX
B.TEMP
C.SYSTEM
D.SYSAUX
E.UNDO
Correct Answer: ABD
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 78
Which two operations can be flashed back using the Flashback technology? (Choose two.)
A.ALTER TABLE EMPLOYEES DROP COLUMN DESIG_ID;
B.DROP USER SMITH;
C.DROP TABLE EMPLOYEES;
D.ALTER TABLE SALES_REP DROP PARTITION P1;
E.DROP TABLESPACE USERS;
Correct Answer: BC
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 79
Users of HR schema complain about slower-than-normal performance. On investigation the DBA found that maintenance was recently performed on some of the tables. The DBA traced the query that takes longer than normal to execute.
View the Exhibit exhibit_before.
ActualTests.com After the DBA resolves the problem, the query performs normally.
View the Exhibit exhibit_after.
What action would the DBA have taken to resolve the performance problem?
"Pass Any Exam. Any Time." - www.actualtests.com 35
Oracle 1z0-042: Practice Exam
A.analyzed the EMPLOYEES table to collect the current statistics
B.moved the EMPLOYEES table in to a locally managed tablespace
C.reorganized the associated indexes for the EMPLOYEES table that were in an unusable state
D.moved the EMPLOYEES table to another location in the same tablespace
E.moved the indexes associated with the EMPLOYEES table to the same tablespace where the EMPLOYEES table exists ActualTests.com
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 80
Your database is running in the ARCHIVELOG mode. You placed a tablespace, tbs_1, offline with the immediate option. Which statement is correct in this scenario?
A.The operation would fail if tbs_1 were a read/write tablespace.
B.The operation would fail if tbs_1 were the default tablespace for the database.
C.The operation would fail if tbs_1 were a read-only tablespace.
D.If the above operation were successful, media recovery would be required to bring the tablespace online.
"Pass Any Exam. Any Time." - www.actualtests.com 36
Oracle 1z0-042: Practice Exam E.If the above operation were successful, instance recovery would be required to bring the tablespace online.
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 81
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 TRUNCATE
B.BEFORE INSERT OR DELETE OR UPDATE
C.BEFORE CREATE
D.BEFORE SUSPEND
E.AFTER CREATE
F.AFTER INSERT OR DELETE OR UPDATE
G.AFTER SUSPEND
Correct Answer: G
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 82
Your database is started by using the server parameter file (SPFILE). You issued this command to change the value of the LOG_BUFFER initialization parameter:
ActualTests.com ALTER SYSTEM SET LOG_BUFFER=24M SCOPE=BOTH;
What would be the outcome of this command?
A.The command would return an error because LOG_BUFFER is a static parameter.
B.You need to restart the database so that parameter changes can come into effect.
C.The command would succeed only if initialization parameter LOG_ARCHIVE_MAX_PROCESS is set to value 2.
D.The parameter value would be changed and it would come into effect immediately.
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 83
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.Operating System (OS) script
B.Recovery Manager (RMAN) script
C.Enterprise Manager (EM) script
D.SQL script
E.PL/SQL script
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 84
You want the user APP_DBA to administer the Oracle database from a remote machine. APP_DBA is granted the SYSDBA privilege to perform administrative tasks on the database. Which file is used by the Oracle database server to authenticate APP_DBA?
A.listener controller file
B.password file
C.control file
D.control file and password file
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 85
You work in a data warehouse environment that involves the execution of complex queries. The ActualTests.com current content of the SQL cache holds the ideal workload for analysis. You want to analyze only few most resource-intensive statements. What would be your suggestion to receive recommendations on the efficient use of indexes and materialized views to improve query performance?
A.Run the Automatic Database Diagnostic Monitor (ADDM).
B.Run the SQL Access Advisor.
C.Run the SQL Tuning Advisor (STA).
D.Run the Automatic Workload Repository (AWR) report.
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 86
Which two statements are true about the Automatic Database Diagnostic Monitor (ADDM)? (Choose two.)
A.The ADDM analysis provides only the diagnostic information but does not provide recommendations.
B.The ADDM requires at least four AWR snapshots for analysis.
C.The results of the ADDM analysis are stored in the Automatic Workload Repository (AWR).
D.The ADDM calls other advisors if required, but does not provide recommendations about the advisors.
E.The ADDM runs after each AWR snapshot is collected.
Correct Answer: CE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 87
Which three pieces of information are considered while deciding the size of the undo tablespace in your database? (Choose three.)
A.undo blocks generated per second
B.the size of an undo block
C.the size of the database buffer cache
D.the size of the redo log files
E.the value of the UNDO_RETENTION parameter
Correct Answer: AB
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 88
ActualTests.com 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, perform media recovery and then instance recovery.
B.On startup, CKPT coordinates instance recovery.
C.Uncommitted changes will be rolled back after the database is opened.
D.On startup, use RMAN to perform instance recovery.
E.On startup, SMON coordinates instance recovery.
F.On startup, all the files will be synchronized and you get both committed and uncommitted data.
Correct Answer: CE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 89
User A executes the following command to update the TRANS table:
SQL> UPDATE B.trans SET tr_amt=tr_amt+500 WHERE c_code='C005';
Before user A issues a COMMIT or ROLLBACK command, user B executes the following command on the TRANS table:
SQl> ALTER TABLE trans MODIFY (tr_type VARCHAR2(3));
What would happen in this scenario?
A.The ALTER TABLE command fails due to the resource being busy.
B.The transaction for user A is rolled back.
C.The ALTER TABLE command modifies the column successfully.
D.The ALTER TABLE command waits until user A ends the transaction.
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 90
Your database is not configured for session failover. Your tnsnames.ora file contains the following details:
test.us.oracle.com=
(DESCRIPTION=
ActualTests.com
(ADDRESS_LIST=
(LOAD_BALANCE=off)
(FAILOVER=ON)
(ADDRESS=(PROTOCOL=tcp)(HOST=test1-server)(PORT=1521)) (ADDRESS=(PROTOCOL=tcp)
(HOST=test2-server)(PORT=1521))) (CONNECT_DATA=(SERVICE_NAME=test.us.oracle.com)))
Which feature is enabled in this case?
A.Connect-time failover
B.Instance failover
C.Database failover
D.Load balancing
"Pass Any Exam. Any Time." - www.actualtests.com 40
Oracle 1z0-042: Practice Exam
E.Transparent Application Failover (TAF)
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 91
You backed up the control file to trace. Which statement is true about the trace file generated?
A.The trace file contains the instructions to manually re-create the control file.
B.The trace file is a backup set created during the backup of the control file.
C.The trace file is an image copy of the control file created during the backup of the control file.
D.The trace file has a SQL script to re-create the control file.
E.The trace file is in binary format.
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 92
You are using flat files as the data source for one of your data warehousing applications. You plan to move the data from the flat file structures to an Oracle database to optimize the application performance. In your database you have clustered tables. While migrating the data, you want to have minimal impact on the database performance and optimize the data load operation. Which method would you use to load data into Oracle database?
A.use the external table population
B.use the conventional path data load of SQL*Loader utility
C.use the Oracle Data Pump export and import utility
D.use the direct path data load of Oracle export and import utility ActualTests.com
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 93
You execute the following set of commands to create a database user and to grant the system privileges in your production environment.
SQL> CREATE USER user01
IDENTIFIED BY oracle DEFAULT TABLESPACE tbs1 TEMPORARY TABLESPACE temp PROFILE default / "Pass Any Exam. Any Time." - www.actualtests.com 41 Oracle 1z0-042: Practice Exam
SQL> GRANT create session, create table TO user01;
While executing the command to create a table, the user gets the following error message and the CREATE TABLE.. command fails.
ERROR at line 1: ORA-01950: no privileges on tablespace
What could be the possible reason for this error message?
A.The tablespace TBS1 is full.
B.The user does not have sufficient system privileges to create table in the TBS1 tablespace.
C.The user does not have sufficient privileges to create table on the default permanent tablespace.
D.The user does not have quota on the TBS1 tablespace.
E.The user is not the owner of the SYSTEM tablespace.
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 94
You are using flat files as the data source for one of your data warehousing applications. You plan to move the data from the flat file structures to an Oracle database to optimize the application performance. In your database you have clustered tables. While migrating the data, you want to have minimal impact on the database performance and optimize the data load operation. Which method would you use to load data into Oracle database?
A.use the external table population
B.use the direct path data load of Oracle export and import utility ActualTests.com
C.use the Oracle Data Pump export and import utility
D.use the conventional path data load of SQL*Loader utility
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 95
User SCOTT wants to export his objects using Oracle Data Pump and executes the following command:
$ expdp scott/tiger
directory = EXPORT_DIR
dumpfile = scott.dmp
"Pass Any Exam. Any Time." - www.actualtests.com 42
Oracle 1z0-042: Practice Exam
include = table include = view:"like '%DEPARTMENTS%'" content = DATA_ONLY
Which task would the command accomplish?
A.Oracle Data Pump would export all of the table structures along with data and all the views.
B.Oracle Data Pump would export the table data and the view definitions where the view name contains a string named DEPARTMENTS.
C.Oracle Data Pump would export only the data of all of the tables and views.
D.Oracle Data Pump would export the table data and the view definitions with data where view name contains a string named DEPARTMENTS.
E.Oracle Data Pump would export all of the table structures and the view definitions with data where view name contains a string named DEPARTMENTS.
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 96
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.local naming method
B.host naming method
C.external naming method
D.directory naming method
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 97
View the Exhibit to observe the message received while trying to drop the SL_REP user in Oracle Enterprise Manager.
The SL_REP user owns objects and is currently connected to the database instance. What would happen if you click YES in the Exhibit?
"Pass Any Exam. Any Time." - www.actualtests.com 43
Oracle 1z0-042: Practice Exam
A.The drop operation waits until the session started by the user ends.
B.The user is dropped successfully, and the session started by the user is killed.
C.An error is returned, and the user is not dropped.
D.The user is dropped, and the objects owned by the user are transferred to the recycle bin.
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 98
You want to move all the objects of the APPS user in the test database to the DB_USER schema of the production database. Which option of IMPDP would you use to accomplish this task?
A.REMAP_TABLESPACE
B.FULL
C.SCHEMAS
D.REMAP_SCHEMA
E.REMAP_DATAFILES
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 99
Which two statements regarding the LOGGING clause of the CREATE TABLESPACE... statement are correct? (Choose two.)
A.This clause is not valid for a temporary or undo tablespace.
B.The tablespace-level logging attribute can be overridden by logging specifications at the table, index, materialized view, materialized view log, and partition levels.
C.If the tablespace is in the NOLOGGING mode, no operation on the tablespace will generate redo.
D.The tablespace will be in the NOLOGGING mode by default, if not specified while creating a tablespace.
"Pass Any Exam. Any Time." - www.actualtests.com 44
Oracle 1z0-042: Practice Exam
Correct Answer: AB
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 100
You work in a data warehouse environment that involves the execution of complex queries. The current content of the SQL cache holds the ideal workload for analysis. You want to analyze only few most resource-intensive statements. What would be your suggestion to receive recommendations on the efficient use of indexes and materialized views to improve query performance?
A.Run the Automatic Database Diagnostic Monitor (ADDM).
B.Run the SQL Tuning Advisor (STA).
C.Run the Automatic Workload Repository (AWR) report.
D.Run the SQL Access Advisor.
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 101 Which two statements are true about a bitmap index? (Choose two.)
A.It is recommended for the columns that have unique values.
B.It can be converted to a B-tree index by using the ALTER INDEX command.
C.It has a bitmap segment for each distinct value in the key column, containing a string of bits in which each bit represents the presence or absence of a key column value.
D.Updating the key column locks the whole bitmap segment that contains the bit for the key value to be updated.
Correct Answer: CD
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 102
You scheduled a backup by using the Schedule Backup option in Oracle Enterprise Manager. Which statement about the script created by Oracle Enterprise Manager is true?
A.It is a Recovery Manager (RMAN) script.
B.It is a PL/SQL file.
C.It is a SQL script.
D.It is an operating system (OS) file.
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 103
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.using anonymous PL/SQL blocks instead of subprograms
B.referring to views instead of tables inside the subprograms
C.creating PL/SQL packages to include interrelated subprograms
D.avoiding the use of cursors in the subprograms
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 104
Your database is running under automatic undo management and the UNDO_RETENTION parameter is set to 900 sec. You executed the following command to enable retention guarantee:
SQL> ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;
What effect would this statement have on the database?
A.The extents containing committed data in the undo tablespace are not overwritten until the instance is shut down. ActualTests.com
B.The extents in undo tablespace retain data until the next full database backup.
C.The extents containing committed data in the undo tablespace are never overwritten.
D.The extents which no longer contain uncommitted data in the undo tablespace are not overwritten for at least15 minutes.
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 105
You have a large amount of historical data in an operating system file. Some analysts in your organization need to query this data. The file is too large to load into your current database. Which is the most effective method to accomplish the task?
"Pass Any Exam. Any Time." - www.actualtests.com 46
Oracle 1z0-042: Practice Exam
A.Load the data into your database by using the PARALLEL clause.
B. Upgrade the hardware/memory to accomodate the data
C.Use an external table so you can have the metadata available in your database, but leave the data in the operating system files.
D.Give analysts DBA privilege, so that they can query DBA_EXTERNAL_TABLES.
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 106
According to your backup strategy, you performed an incremental level 0 backup of your database. Which statement regarding this backup is true?
A.The backup is similar to image copy.
B.The backup contains only unused data blocks.
C.The backup contains all used data blocks.
D.The backup contains all data blocks changed since the last incremental level 1 backup.
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 107
Which two statements about bigfile tablespaces are true? (Choose two.)
A.The bigfile tablespace have only one data file.
B.The bigfile tablespace can be converted to a smallfile tablespace.
C.The database can have only one bigfile tablespace.
D.The extent management is dictionary managed.
E.The segment space management is automatic.
Correct Answer: AE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 108
View the Exhibit and examine the output.
Which statement describes the conclusion?
"Pass Any Exam. Any Time." - www.actualtests.com 47
Oracle 1z0-042: Practice Exam
A.The users should use bind variables instead of literals in the query.
B.The shared pool size should be increased to accommodate the SQL statements.
C.Preparing indexes on the tables used in the SQL statements would improve the library cache performance.
D.The dictionary cache is consuming more space than the library cache.
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 109
View the Exhibit and analyze the CREATE TABLE statements used to create the ITEMS and ORDERS tables.
The ITEMS table has 50 rows and ORDERS table has 500 rows. Because you decide to not deal with the item code 188 in future, you execute the following command to remove the item: SQL> DELETE FROM items WHERE item_code=188;
What is the effect of this command?
A.The command fails with integrity violation error.
B.The row containing the value 188 corresponding to the ITEM_CODE column is deleted from the ITEMS table,
and the dependent rows in the ORDERS table are populated with NULL values in their ITEM_CODE columns.
C.The row containing the value 188 corresponding to the ITEM_CODE column is deleted from the ITEMS table,
and the foreign key constraint is disabled in the ORDERS table.
D.The row containing the value 188 corresponding to the ITEM_CODE column is deleted from the ITEMS table,
and the dependent rows in the ORDERS table are also deleted automatically.
"Pass Any Exam. Any Time." - www.actualtests.com 48
Oracle 1z0-042: Practice Exam
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 110
Which statement is true about the loss of system-critical data files in ARCHIVELOG mode?
A.The data files can be recovered till the last committed transaction.
B.The data files can be recovered to the time of the most recent backup.
C.The data files can be recovered without shutting down the database.
D.The data file is taken offline automatically; the database remains open.
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 111
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 Oracle server dynamically generates the necessary statistics on tables as part of query optimization.
C.The optimizer statistics are collected automatically.
D.The snapshots for the Automatic Workload Repository (AWR) are not generated automatically.
E.Snapshots cannot be collected manually by using DBMS_WORKLOAD_REPOSITORY PACKAGE.
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 112
You define an alert to be raised when the USERS tablespace usage has reached 80% of the total space. Which area would you refer to, in order to confirm that the alert has been raised due the event?
A.the Database Control Home page
B.the DBA_ALERTS view
C.the Database Control performance page
D.the alert.log file
E.the Database Control Maintenance page
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 113 View the Exhibit.
What would happen if you change the value of Desired Mean Time To Recover to 30?
A.The Redo Log Advisor would be disabled.
B.Automatic checkpoint tuning would be disabled.
C.The MTTR Advisor would be enabled.
D.The redo log from log buffers would be written to redo log files at a slower rate.
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 114
Your database is configured in NOARCHIVELOG mode. All the control files have been lost due to a hard disk failure but the data files are not lost. You have the closed whole database backup available to you. Which two statements are true in this scenario? (Choose two.)
A.The instance aborts. ActualTests.com
B.The database can be recovered by restoring the control files from the backup.
C.The database cannot be recovered.
D.The database can be restored till the point of the last closed whole database backup.
E.The database remains opened and you have to shut it down with the ABORT option.
Correct Answer: AD
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 115
On which three can you use Recovery Manager (RMAN) to perform incremental backup? (Choose three.)
"Pass Any Exam. Any Time." - www.actualtests.com 50
Oracle 1z0-042: Practice Exam
A.flashback log file
B.control files
C.whole database
D.parameter file
E.data files
F.archived log files
G.change tracking file
H.password file
I.tablespaces
Correct Answer: CEI
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 116
You notice this warning in the alert log file:
ORA-19815: WARNING: db_recovery_file_dest_size of 3221225472 bytes is 100.00% used, and has 0 remaining bytes available.
What would you do to reclaim the used space in the Flash Recovery Area?
A.Decrease the retention time for the files.
B.Manually delete all the archived log files from the Flash Recovery Area by using operating system (OS) commands.
C.Back up the Flash Recovery Area.
D.Increase the retention time for the files.
Correct Answer: AC
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 117 View the Exhibit.
Your Oracle 10g database has 6 tablespaces in which:
-TEMP is the default temporary tablespace-UNDOTBS1 is the default undo tablespace-USERS is the default permanent tablespace
In this database, which two tablespaces can be made read only? (Choose two.)
"Pass Any Exam. Any Time." - www.actualtests.com 51
Oracle 1z0-042: Practice Exam
A.SYSAUX
B.TEMP
C.UNDOTBS1
D.USERS
E.PROD
F.SYSTEM
Correct Answer: DE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 118
While setting up an Oracle database for one of your critical applications, you want to ensure that the database is backed up at regular intervals without your intervention. What should you do to achieve the objective?
A.schedule the database backup using DBMS_JOB package after creating the database
B.schedule the database backup using Recovery Manager (RMAN) commands after creating the database
C.configure the Flash Recovery Area to enable automatic database backup ActualTests.com
D.schedule the database backup using Database Configuration Assistant (DBCA) while creating the database
E.configure the database to run in ARCHIVELOG mode
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 119
Which two statements regarding archive log destinations are true? (Choose two.)
A.The archive log files can be written only to local destinations.
B.The archiving information can be traced to the alert log file whenever the archiving to a destination is successful.
"Pass Any Exam. Any Time." - www.actualtests.com 52
Oracle 1z0-042: Practice Exam
C.The archive logs must be written to all the destinations.
D.The number of archiving destinations must be equal to the number of archive processes (ARCn).
E.A maximum of 10 destinations can be specified.
Correct Answer: BE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 120
Which two statements are true regarding the SGA_TARGET initialization parameter? (Choose two.)
A.It can be increased up to the value of the SGA_MAX_SIZE parameter.
B.Increasing the value of SGA_TARGET up to the value of SGA_MAX_SIZE disables the automatic shared memory management feature.
C.Reducing the value of the SGA_TARGET parameter takes away memory from both autotuned and manually sized components.
D.Increasing the value of the SGA_TARGET parameter distributes the increased memory among all the autotuned components.
Correct Answer: AD
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 121
A user complains about getting this error after issuing a certain SQL statement:
ORA-02393: exceeded call limit on CPU usage
ActualTests.com Because of the error, the SQL statement gets aborted. What action would you take to increase the CPU usage limit in the subsequent sessions of the user?
A.Set the RESOURCE_LIMIT initialization parameter to FALSE.
B.Modify the resource limit in the profile used by the user.
C.Increase the value of the SESSION_CACHED_CURSORS initialization parameter.
D.Increase the value of the SESSION_MAX_OPEN_FILES initialization parameter.
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 122
You are working on an online transaction processing (OLTP) system. You notice that a PL/SQL procedure got executed twice at 2:00 p.m. This has incorrectly updated the EMP_SAL table. How
"Pass Any Exam. Any Time." - www.actualtests.com 53
Oracle 1z0-042: Practice Exam
would you revert the table to its state at 2:00 p.m.?
A.Perform point-in-time recovery to 2:00 p.m.
B.Restore the entire database from the recent backup and open it.
C.Issue the rollback statement with system change number (SCN).
D.Use Flashback Table feature to revert the changes.
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 123
Your database is started with SPFILE. You want the database instance to be dynamically registered with a listener L2 with the following details:
Protocol: TCP Host: indl151e Port: 1525
Which is the correct order of the steps that you would follow to achieve this?
1. Set the LOCAL_LISTENER parameter to L2 dynamically.
2. Make an entry for L2 in tnsnames.ora on the database server.
3. Restart L2.
4. Modify the listener.ora file to add the instance name in SID_LIST of L2.
A.1, 2, 4, 3
B.1, 2; 3 and 4 are not required.
C.1, 2, 3; 4 is not required.
D.2, 1; 3 and 4 are not required.
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 124
View the Exhibit to observe the privileges granted to the MGR_ROLE role. The SCOTT user has been granted the CONNECT and RESOURCE roles only.
The database administrator (DBA) grants MGR_ROLE to the SCOTT user by using this command:
SQL> GRANT MGR_ROLE TO SCOTT WITH ADMIN OPTION;
Which statement is true about the SCOTT user after he is granted this role?
"Pass Any Exam. Any Time." - www.actualtests.com 54
Oracle 1z0-042: Practice Exam
A.The SCOTT user can grant the MGR_ROLE role and the privileges in it to other users.
B.The SCOTT user can grant only the MGR_ROLE role to other users but not the privileges in it.
C.The SCOTT user can grant the privileges in the MGR_ROLE role to other users but cannot revoke privileges from them.
D.The SCOTT user can grant the privileges in the MGR_ROLE role to other users but not with ADMIN_OPTION.
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 125
Your tnsnames.ora file has the following entry for the service alias ORCL:
ORCL =
( DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.156.24.216)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED)
(SERVICE_NAME = orcl.oracle.com)
)
)
The TNSPING command executes successfully when tested with ORCL but you are not able to connect to the database instance with the following command:
SQL> CONNECT scott/tiger@orcl
What could be the reason for this?
"Pass Any Exam. Any Time." - www.actualtests.com 55
Oracle 1z0-042: Practice Exam
A.The listener is not running at the server.
B.The orcl.oracle.com database service is not registered with the listener.
C.The DEFAULT_DOMAIN parameter is set to a wrong value in the SQLNET.ORA file.
D.The TNS_ADMIN environmental variable is set to a wrong value.
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 126
A user wants to connect to the database instance from an application that is running on a remote machine. Which tools should the DBA use to establish the required configuration to ensure that the user is able to connect to the database instance? (Choose two.)
A.Oracle Net Manager
B.Oracle Universal Installer (OUI)
C.Oracle Enterprise Manager
D.Data Pump
E.Database Configuration Assistant (DBCA)
Correct Answer: AC
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 127
Your database is open and users are connected using the LISTENER listener. The new DBA of the system stops the listener by using the following command:
LSNRCTL> STOP
What would happen to the sessions that are presently connected to the database instance?
A.The sessions are not allowed to perform any operations till the listener is started.
B.The sessions are able to perform only queries.
C.The active transactions are rolled back and the sessions get terminated.
D.The sessions are not affected and continue to function normally.
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 128 View the Exhibit.
Which statement regarding the dept and emp tables is true? "Pass Any Exam. Any Time." -www.actualtests.com 56
Oracle 1z0-042: Practice Exam
A.When you delete a row from the emp table, automatically the corresponding rows are updated with null values in the dept table.
B.When you delete a row from the emp table, you would receive a constraint violation error.
C.When you delete a row from the dept table, you would receive a constraint violation error.
D.When you delete a row from the dept table, automatically the corresponding rows are deleted from the emp table.
E.When you delete a row from the emp table, automatically the corresponding rows are deleted from the dept table.
F.When you delete a row from the dept table, automatically the corresponding rows are updated ActualTests.com with null values in the emp table.
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 129
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
"Pass Any Exam. Any Time." - www.actualtests.com 57
Oracle 1z0-042: Practice Exam
What could have been the reason to set this parameter as TRUE?
A.to enable the administration of the operating system from a remote client
B.to enable operating system authentication for a remote client
C.to allow the start up and shut down of the database from a remote client
D.to restrict the scope of administration to identical operating systems
E.to disable the administration of the operating system from a remote client
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 130
Which two operations can be flashed back using the Flashback technology? (Choose two.)
A.DROP TABLE EMPLOYEES;
B.DROP USER SMITH;
C.ALTER TABLE EMPLOYEES DROP COLUMN DESIG_ID;
D.DROP TABLESPACE USERS;
E.ALTER TABLE SALES_REP DROP PARTITION P1;
Correct Answer: AB
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 131
Which two statements about Recovery Manager (RMAN) backups are
A.Online redo log files can be backed up.
B.RMAN backup can be taken only if the database is configured in ARCHIVELOG mode.
C.Only consistent database backups can be performed.
D.Archived redo log files are backed up.
E.Only used data blocks can be backed up as backup sets.
Correct Answer: DE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 132
Which two statements are true regarding a PL/SQL package body? (Choose two.)
A.It can be changed and recompiled without making the package specification invalid.
B.It cannot invoke subprograms defined in other packages.
C.It cannot be created without a package specification. "Pass Any Exam. Any Time." - www.actualtests.com 58
Oracle 1z0-042: Practice Exam
D.It can contain only the subprograms defined in the package specification.
Correct Answer: AC
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 133
You want to create a new optimized database for your transactional production environment to be used by a financial application. While creating the database, you want the Oracle software to take care of all basic settings to optimize the database performance. Which method would you use to achieve this objective?
A.Use Enterprise Manager to create a new database with the Online Transaction Processing (OLTP) option.
B.Use the CREATE DATABASE .. command to create the database with Automatic Storage Management (ASM) file system.
C.Use the Database Configuration Assistant (DBCA) to create the database with Oracle-managed files.
D.Use the CREATE DATABASE .. command to create the database with Oracle-managed files.
E.Use Database Configuration Assistant (DBCA) to create the database with Transaction Processing template.
Correct Answer: E
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 134
Which statement is true when you run the SQL Tuning Advisor with limited scope?
A. SQL structure analysis is not performed for the SQL statements.
B. Profile recommendations are not generated for the SQL statements.
C.The staleness and absence of the statistics are not checked for the objects in the query supplied to the SQL Tuning Advisor.
D.Access path analysis is not performed for the SQL statements.
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 135
You have to shut down the database instance with the ABORT option because of a hardware failure.
Which statement is true about the subsequent opening of the database?
A.The database would open normally.
"Pass Any Exam. Any Time." - www.actualtests.com 59
Oracle 1z0-042: Practice Exam
B.The database would open after automatically performing instance recovery.
C.The database would not open, and it would stop at mount stage.
D.The database would not open, and you have to perform database recovery to open it.
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 136
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 Grid Architecture
B.Optimal Flexible Architecture
C.Oracle database architecture
D.Oracle Managed Files
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 137
You want to set up the Oracle Database 10g installation to follow the French language, with France as the territory, and the date to be displayed in French for all users' sessions.Which ActualTests.com environmental variable would you set to achieve this objective?
A.NLS_DATE_LANGUAGE
B.NLS_LANG
C.NLS_NCHAR_CHARACTERSET
D.NLS_LANGUAGE
E.NLS_CHARACTERSET
F.NLS_TERRITORY
G.NLS_LENGTH_SEMANTICS
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 138
Your test database is running in NOARCHIVELOG mode. What are the implications of this?
A.You can perform closed database backups.
B.You can perform the backup of only the SYSTEM tablespace when the database is open.
C.You can perform open database backups.
D.You cannot perform schema-level logical backups.
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 139
Which two database operations can be performed at the MOUNT stage of database startup? (Choose two.)
A.configuring the database in ARCHIVELOG mode
B.multiplexing of control files
C.dropping the database user
D.renaming of data files
E.renaming of control files
Correct Answer: AD
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 140
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? ActualTests.com
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 with data files and then use the same template to create the database in the new location
D.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
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 141
"Pass Any Exam. Any Time." - www.actualtests.com 61
Oracle 1z0-042: Practice Exam
View the Exhibit.
Your Oracle 10g database has 6 tablespaces in which:
-TEMP is the default temporary tablespace- UNDOTBS1 is the default undo tablespace- USERS is the default permanent tablespace
In this database, which three tablespaces can be made offline? (Choose three.)
A.PROD
B.SYSAUX
C.USERS
D.SYSTEM
E.TEMP
F.UNDOTBS1
Correct Answer: ABC
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 142
While running the Oracle Universal Installer on a Unix platform to install Oracle Database 10g software, you are prompted to run orainstRoot.sh script.
What does this script accomplish?
A.It creates the pointer file.
B.It creates the Inventory pointer file.
C.It creates the Oracle user for installation.
D.It modifies the Unix kernel parameters to match Oracle's requirement.
E.It creates the base directory.
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 143
Because of a power outage, instance failure has occurred. From what point in the redo log does recovery begin and where does it end?
A.all redo logs before the point of last commit
B.beginning of redo log to end of redo log
C.beginning of redo log to checkpoint position
D.checkpoint position to end of redo log
E.current redo log and inactive redo log
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 144
Which is the correct description of the significance of the ORACLE_HOME environmental variable?
A.It specifies the base directory of Optimal Flexible Architecture (OFA).
B.It specifies the directory containing the Oracle-Managed Files.
C.It specifies the directory for database files, if not specified explicitly.
D.It specifies the directory containing the Oracle software.
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 145
You specified segment space management as automatic for a tablespace. What effect would this have on space management? ActualTests.com
A.The segment would be managed by the freelists.
B.Extents would be managed by the freelists.
C.Free space would be managed by the data dictionary.
D.The segment would be managed by bitmaps.
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 146
The junior DBA in your organization has accidentally deleted the alert log file. What will you do to create new alert log file?
"Pass Any Exam. Any Time." - www.actualtests.com 63
Oracle 1z0-042: Practice Exam
A.Change the value for the BACKGROUND_DUMP_DEST parameter.
B.You have to recover the alert log file from the valid backup.
C.No action required.The file would be created automatically by the instance.
D.Create the new text file file as ALERT.LOG.
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 147
What is the implication of setting the initialization parameter FAST_START_MTTR_TARGET to 0 in your database?
A.Checkpoint information would not be written to the alert log file.
B.Automatic tuning of checkpoint would be disabled.
C.Redo Log Advisor would be disabled.
D.MTTR Advisor would be disabled.
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 148
You suspect unauthorized data manipulation language (DML) operations on a particular table. You want to track users who are performing the transactions and the values used in the transactions. Also, you plan to transfer these values to another table for analysis. How would you achieve this?
A.by using anonymous PL/SQL blocks
B.by using triggers
C.by auditing all DML operations on the table
D.by using external tables ActualTests.com
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 149
You executed the following command to start the database:
SQL> STARTUP ORACLE instance started.
Total System Global Area 281018368 bytes Fixed Size 789000 bytes Variable Size 229635576 bytes
"Pass Any Exam. Any Time." - www.actualtests.com 64
Oracle 1z0-042: Practice Exam
Database Buffers 50331648 bytes
Redo Buffers 262144 bytes
ORA-00205: error in identifying controlfile,
check alert log for more info
Which view would you query at this stage to investigate this missing control file?
A.DBA_CONTROL_FILES
B.V$INSTANCE
C.V$CONTROLFILE
D.V$CONTROLFILE_RECORD_SECTION
E.V$DATABASE_PROPERTIES
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 150
The application workload on your database is same between 10 a.m. and 11 a.m. on weekdays. Suddenly you observe poor performance between 10 a.m. and 11 a.m. in the middle of the week. How would you identify the changes in configuration settings, workload profile, and statistics to diagnose the possible causes of the performance degradation?
A.by using the Automatic Workload Repository report
B.by using the SQL access advisor
C. by running the Automatic Database Diagnostic Monitor (ADDM)
D.by analyzing the output of the V$ACTIVE_SESSION_HISTORY view
E.by using the Automatic Workload Repository Compare Period report
Correct Answer: E
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 151
In your database, the Log Writer (LGWR) process is unable to write to a member of a current redo log group due to read/write failure. Which two effects would you see in your database? (Choose two.)
A.The database instance aborts.
B.The database operation temporarily halts till the member becomes available.
C.The member would be marked as STALE and an error message would be written to the alert log file and LGWR trace file.
D.The status of the group changes to INACTIVE and an error message would be written to the alert log file and LGWR trace file.
"Pass Any Exam. Any Time." - www.actualtests.com 65
Oracle 1z0-042: Practice Exam
E.Writing proceeds as normal. LGWR writes to the available members of a group and ignores the unavailable members.
Correct Answer: CE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 152
Your database is in NOARCHIVELOG mode. After which two operations you should take the backup of control file ? (Choose two.)
A.dropping a table from the database
B.adding a new user to the database
C.dropping a data file from a tablespace
D.dropping a user from the database
E.adding a new tablespace to the database
Correct Answer: CE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 153
View this parameter setting in your database:
DB_CREATE_FILE_DEST='D:\oracle\product\10.2.0\oradata\oracle'
You created a tablespace by using this command:
CREATE TABLESPACE USERS;
ActualTests.com Which two statements are true about the USERS tablespace? (Choose two.)
A.The tablespace has two data files.
B.Data files belonging to the USERS tablespace cannot be renamed.
C.The tablespace can be extended without specifying the data file.
D.An error is reported and tablespace creation fails.
E.Data files are created with names generated by the instance.
Correct Answer: CE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 154
The HR user creates a view with this command:
"Pass Any Exam. Any Time." - www.actualtests.com 66
Oracle 1z0-042: Practice Exam
SQL> CREATE VIEW emp_v AS SELECT * FROM scott.emp;
Now HR wants to grant the SELECT privilege on the EMP_V view to the JIM user. Which statement is true in this scenario?
A.SCOTT has to grant the SELECT privilege on the EMP table to JIM before this operation.
B.HR can grant the privilege to JIM but without GRANT OPTION.
C.HR can grant the privilege to JIM because HR is the owner of the view.
D.HR needs the SELECT privilege on the EMP table with GRANT OPTION from SCOTT for this operation.
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 155
You executed the following command to export the EMPLOYEES table from a remote machine:
$ EXPDP hr/hr@data.us.oracle.com DUMPFILE=my_dir:exp_hr.log LOGFILE=data_pump_dir:log_hr.log TABLES=employees
What would be the outcome of this command?
A.The command would execute successfully. But log file would not be created as DATA_PUMP_DIR directory is only accessible to user with SYSDBA privilege.
B.The command fails with an error because no absolute path is specified for log file and dumpfile.
C.The command would execute successfully and the export dump file would be created in the destination of the directory object MY_DIR. ActualTests.com
D.The command fails with an error because DATA_PUMP_DIR directory have higher precedence over the per-file directory.
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 156 Which three statements are correct about temporary tables? (Choose three.)
A.The data inserted into a temporary table in a session is available to other sessions.
B.Temporary tables are always created in a user's temporary tablespace.
C.Indexes and views can be created on temporary tables.
"Pass Any Exam. Any Time." - www.actualtests.com 67
Oracle 1z0-042: Practice Exam
D.Data manipulation language (DML) locks are never acquired on the data of temporary tables. E.Both the data and the structure of temporary tables can be exported.
Correct Answer: BCD
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 157
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.SELECT job FROM emp WHERE job='CLERK' FOR UPDATE OF empno;
B.DELETE FROM emp WHERE job='MANAGER';
C.LOCK TABLE emp IN EXCLUSIVE MODE;
D.LOCK TABLE emp IN SHARE MODE;
E.INSERT INTO emp(empno,ename) VALUES (1289,'Dick');
Correct Answer: ACD
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 158
In the middle of a transaction, a user session was abnormally terminated but the instance is still up and the database is open.
Which two statements are true in this scenario? (Choose two.) ActualTests.com
A.The alert log file gives detailed information about the failure.
B.Data modified by the transaction up to the last commit before the abnormal termination is retained in the database.
C.The transaction is rolled back by the next session that refers to any of the blocks updated by the failed transaction.
D.PMON rolls back the transaction and releases the locks.
E.SMON rolls back the transaction and releases the locks.
F.Event Viewer gives more details on the failure.
Correct Answer: BD
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 159
You created a response file and want to check it before starting installations in silent mode. You started installation of Oracle software in interactive mode by providing the response file. In the middle of the installation, you realize that the behavior of the Oracle Universal Installer (OUI) is not consistent with the response file. What action would you take to detect the cause of this behavior?
A.Refer to the contents of the oraInst.loc file to verify the steps performed by OUI.
B.Compare the contents of the installActions.log file with that of the response file.
C.Refer to the alert log file for information regarding the actions performed by OUI during installation.
D.Compare the contents of the install.log file with the response file.
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 160
In your Oracle 10g database , you have scheduled a job to update the optimizer statistics at 05:00 pm every Friday. The job has successfully completed. Which three pieces of information would you check to confirm that the statistics have been collected? (Choose three.)
A.last analyzed date
B.average row size
C.number of extents present in the table
D.size of table in bytes
E.size of table in database blocks
F.number of free blocks in the free list
Correct Answer: ABE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 161
View the Exhibit to examine the output of the DBA_OUTSTANDING_ALERT view.
After 30 minutes, you executed the following command:
SQL> SELECT reason,metric_value FROM dba_outstanding_alerts;
REASON METRIC_VALUE
------------------------------ ---------------------- Tablespace [TEST] is [28 perce 28.125 nt] full
"Pass Any Exam. Any Time." - www.actualtests.com 69
Oracle 1z0-042: Practice Exam
What could be the two reasons for the elimination of the other rows in the output? (Choose two.)
A.The threshold alerts are transferred to DBA_ALERT_HISTORY.
B.The non-threshold-based alerts are transferred to DBA_ALERT_HISTORY.
C.The threshold alerts related to database metrics are permanently stored in DBA_ALERT_HISTORY but not the threshold alerts related to instance metrics.
D.The threshold alert conditions are cleared.
Correct Answer: AD
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 162
View the Exhibit, and examine the alert messages.
You added space to the TEST tablespace to bring the space usage below the threshold value.
Which statement is true about the Tablespace Full alert?
A.The alert is automatically cleared and sent to the alert history.
B.The alert is cleared and transferred to the alert log file.
C.The alert appears in Oracle Enterprise Manager Database Control until it is manually cleared.
D.The alert is purged because it is a threshold alert.
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 163
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 database architecture
C.Optimal Flexible Architecture
D.Oracle Grid Architecture
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 164
The SAVE_AMT column in the ACCOUNTS table contains the balance details of customers in a bank. As part of the year-end tax and interest calculation process, all the rows in the table need to be accessed. The bank authorities want to track access to the rows containing balance amounts exceeding $200,000, and then send an alert message to the administrator. Which method would you suggest to the bank for achieving this task?
A.performing standard database auditing to audit SQL statements with granularity level set to ACCESS
B.implementing value-based auditing by using triggers ActualTests.com
C.performing standard database auditing to audit object privileges by setting the AUDIT_TRAIL parameter to EXTENDED
D.implementing fine-grained auditing with audit condition and event handler
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 165
You received complaints about the degradation of SQL query performance. You identified top SQL queries that consume time. What would be your next step to find out recommendations about statistics collection and restructuring of the SQL statement to improve query performance?
A. run Segment Advisor
"Pass Any Exam. Any Time." - www.actualtests.com 71
Oracle 1z0-042: Practice Exam
B.run the Automatic Workload Repository (AWR) report
C.run the Automatic Database Diagnostic Monitor (ADDM) on top SQL statements
D.run SQL Tuning Advisor on top SQL statements
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 166
You are working on a database created with the Oracle Database 10g software in which the initialization parameter COMPATIBLE is set to 10.0.0.
The hr_tbs tablespace in the database is created as follows:
CREATE TABLESPACE hr_tbs DATAFILE '/oracle/oradata/hr_tbs.dbf' SIZE 50M;
View the Exhibit to see the properties of the database.
Which statement is correct in this scenario?
A.All the tablespaces in the database must be of the smallfile tablespace type.
B.hr_tbs is a bigfile tablespace.
C.The tablespace type bigfile or smallfile must be mentioned in the command.
D.hr_tbs is a dictionary-managed tablespace.
E.All the tablespaces in the database will be locally managed by default.
Correct Answer: E
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 167
A user receives the following error while executing a query:
ORA-01555: snapshot too old
Which two options can be the solutions to avoid such errors in future? (Choose two.)
A.increase the size of the Database Buffer Cache
B.increase the size of the default temporary tablespace
C.increase the size of redo log files
D.increase the size of the undo tablespace
E.enable the retention guarantee for the undo tablespace
Correct Answer: DE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 168
What are the consequences of executing the SHUTDOWN ABORT command? (Choose two.)
.The database files are synchronized.
B.The database is closed, but the instance is still started.
C.The database undergoes automatic media recovery during the next startup.
D.Database buffers and redo buffers are not written to the disk.
E.Uncommitted changes are not rolled back.
Correct Answer: DE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 169
The employee IDs of employees who have made sales in the company are transferred from the EMPLOYEES table to the BONUS table with a default bonus value. Later, the human resources department decides to give bonuses to employees as per the following conditions:
1. Employees with a salary of $8,000 or less should receive a bonus.
2. Employees who have not made sales get a bonus of 1% of their salary.
3. Employees who already made sales get an increase in their bonus equal to 1% of their salary.
What would you do to implement these changes in one step?
A.Use SQL*Loader utility.
B.Use the MERGE statement.
C.Use a multitable insert operation.
D.Use a correlated UPDATE statement.
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 170
Your database instance is started using the server parameter file (SPFILE). Control files are multiplexed and stored on different disks. Because of a disk failure, you lost one of these control files. You replaced the damaged disk. What is the correct sequence of steps that you would follow to recover the control file?
1. Shut down the instance, if not already done.
2. Copy one of the remaining control files to a new location.
3. Change the value of the CONTROL_FILES initialization parameter to correspond to the new location of the control files.
4. Start up the database instance to the NOMOUNT stage.
"Pass Any Exam. Any Time." - www.actualtests.com 5
Oracle 1z0-042: Practice Exam
5. Recover the database to the point of failure of the control file.
6. Open the database.
A. 2,4,3,4,5
B. 5,2,3,4
C.4,5,6,2,3
D.1,2,4,3,5
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 171
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 STATISTICS_LEVEL initialization parameter was changed to BASIC and reset to TYPICAL before the last task.
C.The optimizer statistics was collected manually by using the DBMS_STATS package before the last task.
D.The optimizer statistics for few objects in the database were locked before the last task.
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 172 Examine the following commands executed in your database:
SQL> ALTER SESSION RECYCLEBIN=ON;
"Pass Any Exam. Any Time." - www.actualtests.com 16
Oracle 1z0-042: Practice Exam
Session altered SQL> CREATE TABLE emp TABLESPACE tbsfd AS SELECT * FROM hr.employees; Table created.
Further, you executed the following command to drop the table:
SQL> DROP TABLE emp; Table dropped.
What happens in this scenario?
A.The table is moved to the SYSTEM tablespace.
B.The table is removed from the database permanently.
C.The table is moved to the SYSAUX tablespace.
D.The table is renamed and remains in the TBSFD tablespace.
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 173
You execute the following command to audit the database activities:
SQL> AUDIT DROP ANY TABLE BY scott BY SESSION WHENEVER SUCCESSFUL;
What is the effect of this command?
A.One audit record is created for each successful DROP TABLE command executed in the session of SCOTT.
B.One audit record is created for each successful DROP TABLE command executed by any user to drop tables owned by SCOTT.
C.One audit record is created for the whole session if user SCOTT successfully drops one or ActualTests.com more tables in his session.
D.One audit record is generated for the session when SCOTT grants the DROP ANY TABLE privilege to other users in his session.
E.One audit record is created for every session when any user successfully drops a table owned by SCOTT.
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 174
You are working on the 24X7 database with high transaction volume, to ensure faster instance recovery on your database you set the FAST_START_MTTR_TARGET initialization parameter to a very low value. What effect it will have on the database?
A.The overall database performance would be degraded.
"Pass Any Exam. Any Time." - www.actualtests.com 23
Oracle 1z0-042: Practice Exam
B.The database performance would be enhanced.
C. The mean time to recover (MTTR) would be increased.
D.The redo log files would be get filled more frequently.
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 175
You execute the following set of commands to create a database user and to grant the system privileges in your production environment.
SQL> CREATE USER user01
IDENTIFIED BY oracle
DEFAULT TABLESPACE tbs1
TEMPORARY TABLESPACE temp
PROFILE default
/
"Pass Any Exam. Any Time." - www.actualtests.com 41
Oracle 1z0-042: Practice Exam
SQL> GRANT create session, create table TO user01;
While executing the command to create a table, the user gets the following error message and the CREATE
TABLE.. command fails.
ERROR at line 1: ORA-01950: no privileges on tablespace
What could be the possible reason for this error message?
A.The tablespace TBS1 is full.
B.The user does not have sufficient system privileges to create table in the TBS1 tablespace.
C.The user does not have sufficient privileges to create table on the default permanent tablespace.
D.The user does not have quota on the TBS1 tablespace.
E.The user is not the owner of the SYSTEM tablespace.
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 176
You work in a data warehouse environment that involves the execution of complex queries. The current content of the SQL cache holds the ideal workload for analysis. You want to analyze only few most resource-intensive statements. What would be your suggestion to receive recommendations on the efficient use of indexes and materialized views to improve query performance?
A.Run the Automatic Database Diagnostic Monitor (ADDM).
B.Run the SQL Tuning Advisor (STA).
C.Run the Automatic Workload Repository (AWR) report.
D.Run the SQL Access Advisor.
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 177
View the Exhibit and examine the output.
Which statement describes the conclusion?
"Pass Any Exam. Any Time." - www.actualtests.com 47
Oracle 1z0-042: Practice Exam
A.The users should use bind variables instead of literals in the query.
B.The shared pool size should be increased to accommodate the SQL statements.
C.Preparing indexes on the tables used in the SQL statements would improve the library cache performance.
D.The dictionary cache is consuming more space than the library cache.
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 178
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.a shareable table lock for the table containing the row
B.null lock on the table containing the row
C.exclusive table-level lock for the table containing the row
D.null lock on the row being updated
E.ROW SHARE lock for the row being updated
F.shared row-exclusive lock for the row being updated
G.ROW EXCLUSIVE lock for the row being updated
Correct Answer: AG
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 179
A constraint in a table is defined with the INITIALLY IMMEDIATE clause. You executed the ALTER TABLE command with the ENABLE VALIDATE option to enable the constraint that was disabled. What are the two effects of this command? (Choose two.)
A.It does not validate the existing data in the table.
B.It prevents insert, update, and delete operations on the table while the constraint is in the process of being enabled.
C.It enables the constraint to be enforced at the end of each transaction.
D.It fails if any existing row violates the constraint.
Correct Answer: BD
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 180
Your test database is running in NOARCHIVELOG mode. What are the implications of this?
A.You can perform closed database backups.
B.You can perform the backup of only the SYSTEM tablespace when the database is open.
C.You can perform open database backups.
D.You cannot perform schema-level logical backups.
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 181
You execute the following command to audit the database activities:
SQL> AUDIT DROP ANY TABLE BY scott BY SESSION WHENEVER SUCCESSFUL;
What is the effect of this command?
A.One audit record is created for each successful DROP TABLE command executed in the session of SCOTT.
B.One audit record is created for each successful DROP TABLE command executed by any user to drop tables owned by SCOTT.
C.One audit record is created for the whole session if user SCOTT successfully drops one or ActualTests.com more tables in his session.
D.One audit record is generated for the session when SCOTT grants the DROP ANY TABLE privilege to other users in his session.
E.One audit record is created for every session when any user successfully drops a table owned by SCOTT.
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
You have a text file that maintains information on thousands of items. The end-user application requires the transfer of that information into a table in the database. What would you use to achieve this task? (Choose two.)
A.Data Pump
B.SQL*Loader
C.External table
D.Oracle Text
E.Oracle Import
Correct Answer: BC
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 2
Which step do you need to perform to enable a user with the SYSDBA privilege to log in as SYSDBA in iSQL*Plus?
A.The user must be granted the database administrator (DBA) privilege.
B.Set up a user in the Oracle Application Server Containers for J2EE (OC4J) user manager, and grant the webDba role to the user.
C.The user must be listed in the password file for the authentication.
D.No special setup is needed for the user to connect as SYSDBA in iSQL*Plus.
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 3
You specified extent management as local for a tablespace. How will it affect space management in the tablespace?
A.The tablespace will be system managed and the users cannot specify the extent size.
B.Free extents will be managed by the data dictionary tables.
C.All the extents will be of the same size.
D.Bitmap will be used to record free and allocated extents.
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 4
Which two operations require undo data? (Choose two.)
A.recovering from failed transactions
B.recording a transaction to redo log files
C.rolling back a transaction
D.committing a transaction
E.rolling forward during instance recovery
Correct Answer: AC
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 5
The employee IDs of employees who have made sales in the company are transferred from the EMPLOYEES table to the BONUS table with a default bonus value. Later, the human resources department decides to give bonuses to employees as per the following conditions:
1. Employees with a salary of $8,000 or less should receive a bonus.
2. Employees who have not made sales get a bonus of 1% of their salary.
3. Employees who already made sales get an increase in their bonus equal to 1% of their salary.
What would you do to implement these changes in one step?
A.Use SQL*Loader utility.
B.Use the MERGE statement.
C.Use a multitable insert operation.
D.Use a correlated UPDATE statement.
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 6
The ST_INFO master table has millions of rows that are updated very rarely. It has a STATE_CODE column that contains the value for 25 states. The table is frequently queried on the STATE_CODE column. Which type of index would you suggest to improve the query performance?
A.bitmap
B.B-tree
C.function based
D.reverse key
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 7
You want to monitor and control the resource usage by sessions. You want to be warned automatically when more than 100 sessions are opened with your database. What action would you take to achieve this?
A.Set the warning threshold for the Current Logons Count metric.
B.Set the limits in the profiles used by users.
C.Use the Database Resource Manager.
D.Modify the SESSIONS initialization parameter.
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 8
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.a shareable table lock for the table containing the row
B.null lock on the table containing the row
C.exclusive table-level lock for the table containing the row
D.null lock on the row being updated
E.ROW SHARE lock for the row being updated
F.shared row-exclusive lock for the row being updated
G.ROW EXCLUSIVE lock for the row being updated
Correct Answer: AG
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 9
Which statement regarding the contents of the V$PARAMETER view is true?
A.displays only the list of default values
B.displays the current contents of the server parameter file
C.displays the currently in effect parameter values
D.displays the list of all the parameter files of a database
E.displays only the list of all advanced parameters
F.displays only the list of all basic parameters
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 10
Redo log files are not multiplexed in your database. Redo log blocks are corrupted in group 2, and archiving has stopped. All the redo logs are filled and database activity is halted. Database writer has written everything to disk. Which command would you execute to proceed further?
A.ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;
B.RECOVER LOGFILE BLOCK GROUP 2;
C.ALTER DATABASE CLEAR LOGFILE GROUP 2;
D.ALTER DATABASE DROP LOGFILE GROUP 2;
E.ALTER DATABASE RECOVER LOGFILE GROUP 2;
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 11
Which three statements are true regarding the fine-grained auditing (FGA)? (Choose three.)
A.FGA is possible on SELECT statements only.
B.The audit trail for FGA is stored in the AUD_LOG$ table.
C.FGA includes the SQL statement used by the user as part of the audit event entry.
D.FGA enables a SQL predicate to define when to audit an event.
E.The audit trail for FGA is stored in the FGA_LOG$ table.
F.FGA audits DELETE statements only when audit columns are specified.
Correct Answer: CDE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 12
Your database instance is started using the server parameter file (SPFILE). Control files are multiplexed and stored on different disks. Because of a disk failure, you lost one of these control files. You replaced the damaged disk. What is the correct sequence of steps that you would follow to recover the control file?
1. Shut down the instance, if not already done.
2. Copy one of the remaining control files to a new location.
3. Change the value of the CONTROL_FILES initialization parameter to correspond to the new location of the control files.
4. Start up the database instance to the NOMOUNT stage.
5. Recover the database to the point of failure of the control file.
6. Open the database.
A.2,4,3,4,5
B.5,2,3,4
C.4,5,6,2,3
D.1,2,4,3,5
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 13
You executed the STARTUP MOUNT command to start your database. For which database operation do you need to start the database in the MOUNT state?
A.dropping a user in your database
B.dropping a tablespace in your database
C.enabling or disabling redo log archiving
D.renaming the control files
E.re-creating the control files, after you lost all the control files in your database
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 14
View the Exhibit to see the source and target databases.
You have created a database link, devdb.us.oracle.com, between the databases PRODDB and DEVDB. You want to import schema objects of the HR user using Oracle Data Pump from the Exammitra.com development database, DEVDB, to the production database, PRODDB. You execute the following command on the target database server:
$impdp system/manager directory = DB_DATA
dumpfile = schemas.dat
schemas = hr
flashback_time = 2004-02-03 09:00
The command fails, displaying the following error:
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/home/oracle/schema/schemas.dat" for read
ORA-27037: unable to obtain file status
What would you do to overcome the error?
A. remove the dumpfile option in the command
B.remove the flashback_time option in the command
C.remove the dumpfile option and add the network_link = devdb.us.oracle.com option in the command
D.add the user, SYSTEM, to the schemas option in the command
E.remove the schemas option and add the network_link = devdb.us.oracle.com option in the command
F.add network_link = devdb.us.oracle.com option in the command
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 15
You want to create a tablespace with the following specifications:
1. The tablespace extends automatically.
2. Used and free extents should be managed by bitmaps.
3. Default PCTUSED attribute is set to 60.
4. All the extents would be of size 1 MB.
Which three options would you choose to create the tablespace? (Choose three.)
A.tablespace with segment space management as automatic
B.tablespace with AUTOEXTEND enabled
C.tablespace with a uniform extent allocation of 1 MB
D.tablespace with dictionary-managed extents
E.tablespace with segment space management as manual
Correct Answer: BCE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 16
Examine the following commands executed in your database:
SQL> ALTER SESSION RECYCLEBIN=ON;
Session altered
SQL> CREATE TABLE emp TABLESPACE tbsfd AS SELECT * FROM hr.employees; Table created.
Further, you executed the following command to drop the table:
SQL> DROP TABLE emp; Table dropped.
What happens in this scenario?
A.The table is moved to the SYSTEM tablespace.
B.The table is renamed and remains in the TBSFD tablespace.
C.The table is moved to the SYSAUX tablespace.
D.The table is removed from the database permanently.
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 17
Which two statements are true about the roles in the Oracle database? (Choose two.) ActualTests.com
A.A role cannot be assigned external authentication.
B.Roles can be granted to other roles.
C.Roles are owned by the SYS user.
D.A role can be granted to itself.
E.A role can contain both system and object privileges.
Correct Answer: BE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 18
The user SCOTT executes the following command successfully to increase the salary values in one of his sessions:
SQL> UPDATE emp SET sal=sal*1.15 WHERE deptno=20;
Before SCOTT ends the transaction, user HR who has the privileges on EMP table executes a query to fetch the salary details but finds the old salary values instead of the increased values.
Why does HR still see the old data?
A.because of redo data from redo log file
B.because of data from a temporary tablespace
C.because of undo data from the undo tablespace
D.because of data from database buffer cache
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 19
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 STATISTICS_LEVEL initialization parameter was changed to BASIC and reset to TYPICAL before the last task.
C.The optimizer statistics was collected manually by using the DBMS_STATS package before the last task.
D.The optimizer statistics for few objects in the database were locked before the last task.
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 21
You want to protect your database from single point of failure by multiplexing the control file. Your database is started using SPFILE. You have to perform the following tasks:
1. Shut down the database instance.
2. Copy the control file to new location.
3. Configure the CONTROL_FILES parameter.
4. Restart the instance.
Select the correct order in which these tasks need to be performed.
A.3,2,1,4
B.3,1,2,4
C.2,3,1,4
D.1,2,3,4
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 22
Which two statements are true regarding the database in ARCHIVELOG mode? (Choose two.)
A.Archiving information is written to the data files and redo log files.
B.You can perform complete database backups without closing the database.
C.Online redo log files have to be multiplexed before putting the database in ARCHIVELOG mode.
D.All the previous database backups become invalid after you configure the database to ARCHIVELOG mode.
E.You have to shut down the database to perform the backups.
Correct Answer: BD
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 23
Which three statements are true about the stages of database startup? (Choose three.)
A.Data files and redo log files can be renamed at the MOUNT stage.
B.Control files are required to bring the database to the NOMOUNT stage.
C.Data files and online redo log files are checked for consistency while opening the database.
D.Data files and redo log files are made available to users at the OPEN stage.
E.Control files are read at the OPEN stage for the location of data files.
Correct Answer: ACD
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 24
Which is the memory area that is created when a dedicated server process is started, and contains data and control information for that server process?
A. SGA
B.Streams Pool
C.Shared Pool
D.PGA
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 25
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 fails because SCOTT still has privileges with him.
B.Only HR can perform SELECT, INSERT, and UPDATE operations on the EMP table.
C.The command fails because HR cannot revoke the privileges from JIM.
D.The command succeeds and privileges are revoked from JIM.
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 26
View the Exhibit.
Which statement regarding the dept and emp tables is true?
A.When you delete a row from the dept table, automatically the corresponding rows are updated with null values in the emp table.
B.When you delete a row from the dept table, automatically the corresponding rows are deleted from the emp table.
C.When you delete a row from the emp table, automatically the corresponding rows are updated with null values in the dept table.
D.When you delete a row from the emp table, automatically the corresponding rows are deleted from the dept table.
E.When you delete a row from the emp table, you would receive a constraint violation error.
F.When you delete a row from the dept table, you would receive a constraint violation error.
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 27
You created a response file and want to check it before starting installations in silent mode. You started installation of Oracle software in interactive mode by providing the response file. In the middle of the installation, you realize that the behavior of the Oracle Universal Installer (OUI) is not consistent with the response file. What action would you take to detect the cause of this behavior?
A.Refer to the alert log file for information regarding the actions performed by OUI during installation.
B.Refer to the contents of the oraInst.loc file to verify the steps performed by OUI.
C.Compare the contents of the installActions.log file with that of the response file.
D.Compare the contents of the install.log file with the response file.
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 28
In which situation would you use the Oracle Shared Server configuration?
A. when performing export and import using Oracle Data Pump
B. when performing batch processing and bulk loading operation in a data warehouse environment
C.in an online transaction processing (OLTP) system where large number of client sessions are ActualTests.com idle most of the time
D.when performing backup and recovery operations using Oracle Recovery Manager
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 29
You find that the database performance degrades while you backup the PROD database using Recovery Manager (RMAN). The PROD database is running in shared server mode. The database instance is currently using 60% of total operating system memory. You suspect the shared pool fragmentation to be the reason. Which action would you consider to overcome the performance degradation?
A.Increase the total System Global Area (SGA) size to increase memory hits.
B.Increase Database Buffer Cache size to increase cache hits.
C.Increase Shared Pool size to cache more PL/SQL objects.
D.Configure Java Pool to cache the java objects.
E.Configure Streams Pool to enable parallel processing.
F.Configure Large Pool to be used by RMAN and shared server.
Correct Answer: F
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 30
View the Exhibit to see the structure of the EMPLOYEES and DEPARTMENTS tables.
Your organization plans to dissolve the department with department ID 30. You execute the following command to delete rows from the DEPARTMENTS table:
SQL>delete from DEPARTMENTS where DEPT_ID = 30;
The command fails and displays the following error:
ERROR at line 1: ORA-02292: integrity constraint (HR.SYS_C005374) violated - child record found
Which two actions would you take to overcome this error? (Choose two.)
A.first, delete rows from the EMPLOYEES table for department id 30 and then delete the rows from the DEPARTMENTS table for department id 30
B.first, drop the EMPLOYEES table and then delete the rows from the DEPARTMENTS table
C.first, drop the DEPARTMENTS table and then delete the rows from the EMPLOYEES table
D.alter the foreign key constraint to include the on delete cascade option
E.first, delete all of the rows from EMPLOYEES table and then delete the rows from the DEPARTMENTS table for department id 30
F.alter the foreign key constraint to include the cascade option
Correct Answer: AD
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 31
Which two statements are true about the primary key constraint in a table? (Choose two.)
A.It is not possible to disable the primary key constraint.
B.The primary key constraint can be imposed by combining more than one column.
C.The non-deferrable primary key constraint creates an unique index on the primary key column if it is not already indexed.
D.The primary key constraint can be referred by only one foreign key constraint.
E.It is possible to have more than one primary key constraint in a single table.
Correct Answer: BC
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 32
You are working on an online transaction processing (OLTP) system. You notice that a PL/SQL procedure got executed twice at 2:00 p.m. This has incorrectly updated the EMP_SAL table. How would you revert the table to its state at 2:00 p.m.?
A.Perform point-in-time recovery to 2:00 p.m. ActualTests.com
B.Issue the rollback statement with system change number (SCN).
C.Restore the entire database from the recent backup and open it.
D.Use Flashback Table feature to revert the changes.
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 33
While running the Oracle Universal Installer on a Unix platform to install Oracle Database 10g software, you are prompted to run orainstRoot.sh script. What does this script accomplish?
A.It creates the Inventory pointer file.
B.It modifies the Unix kernel parameters to match Oracle's requirement.
C.It creates the Oracle user for installation.
D.It creates the base directory.
E.It creates the pointer file.
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 34
You executed the following command to back up your control file:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
Which initialization parameter is used to specify the location of the trace file?
A.BACKUP_DUMP_DEST
B.USER_DUMP_DEST
C.TRACE_DUMP_DEST
D.CORE_DUMP_DEST
E.BACKGROUND_DUMP_DEST
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 35
You require the Oracle server to manage the undo segments and space among various active sessions automatically. You created an undo tablespace, UNDO_TBS1, in your database. Which two additional steps would you perform to achieve this? (Choose two.)
A.Create an initial undo segment in the undo tablespace.
B.Enable the retention guarantee for the undo tablespace.
C.Set the UNDO_TABLESPACE parameter to UNDO_TBS1.
D.Set the UNDO_MANAGEMENT initialization parameter to AUTO.
E.Set the UNDO_RETENTION parameter to 900 or more.
Correct Answer: CD
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 36
Examine the following commands executed in your database:
SQL> ALTER SESSION RECYCLEBIN=ON; Session altered SQL> CREATE TABLE emp TABLESPACE tbsfd AS SELECT * FROM hr.employees; Table created.
Further, you executed the following command to drop the table:
SQL> DROP TABLE emp; Table dropped.
What happens in this scenario?
A.The table is moved to the SYSTEM tablespace.
B.The table is removed from the database permanently.
C.The table is moved to the SYSAUX tablespace.
D.The table is renamed and remains in the TBSFD tablespace.
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 37
You perform differential incremental level 1 backups of your database on each working day and level 0 backup on Sundays. Which two statements are true about the differential incremental backups? (Choose two.)
A.The backup performed on each working day contains all the blocks that have changed since the last level 0 backup.
B.The backup performed on Sundays contains all the blocks that have changed since the last level 1 backup.
C.The backup performed on Sundays contains all the blocks that have ever been use in the ActualTests.com database.
D.The backup performed on each working day contains all the blocks that have changed since the last level 0 or level 1 backup.
Correct Answer: CD
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 38
Which two statements about Flashback Query are true? (Choose two.)
A.It helps in row-level recovery from user errors.
B.It is generated by using the redo log files.
C.It fails when undo data pertaining to the transaction is overwritten.
D.The database has to be opened with the resetlogs option after performing Flashback Query.
E.It can be performed to recover ALTER TABLE statements
Correct Answer: AC
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 39
You want to refer the employee personal information stored in operating system(OS) files in EMPLOYEE table. You plan to add a new column to EMPLOYEE table to achieve this. Which data type would you use for the new column?
A.CLOB
B.LONG RAW
C.BFILE
D.BLOB
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 40
A user complains that he gets the following error message repeatedly after executing some SQL statements.
The error message forces the user to log off from and log on to the database to continue his work.
ORA-02392: exceeded session limit on CPU usage, you are being logged off
Which action would you take to increase the session limit on CPU usage?
A.Modify the object privileges assigned to the user.
B.Modify the system privileges assigned to the users.
C.Modify the roles assigned to the users.
D.Modify the value for the RESOURCE_LIMIT parameter in the parameter file.
E.Modify the profile assigned to the user.
Correct Answer: E
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 41
You execute the following command to audit the database activities:
SQL> AUDIT DROP ANY TABLE BY scott BY SESSION WHENEVER SUCCESSFUL; What is the effect of this command?
A.One audit record is created for the whole session if user SCOTT successfully drops one or more tables in his session.
B.One audit record is generated for the session when SCOTT grants the DROP ANY TABLE privilege to other users in his session.
C.One audit record is created for each successful DROP TABLE command executed in the session of SCOTT.
D.One audit record is created for each successful DROP TABLE command executed by any user to drop tables owned by SCOTT.
E.One audit record is created for every session when any user successfully drops a table owned by SCOTT.
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 42
You execute the following command to audit the database activities:
SQL> AUDIT DROP ANY TABLE BY scott BY SESSION WHENEVER SUCCESSFUL;
What is the effect of this command?
A.One audit record is created for each successful DROP TABLE command executed in the session of SCOTT.
B.One audit record is created for each successful DROP TABLE command executed by any user to drop tables owned by SCOTT.
C.One audit record is created for the whole session if user SCOTT successfully drops one or more tables in his session.
D.One audit record is generated for the session when SCOTT grants the DROP ANY TABLE privilege to other users in his session.
E.One audit record is created for every session when any user successfully drops a table owned by SCOTT.
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 43
The SCOTT user has an index on the ITEM_DESC column of the ITEM table. As part of the year- ending task, SCOTT updates the ITEM_DESC column for most of the rows in the ITEM table. How does this change to the table affect the index?
A.An update in a leaf row takes place.
B.The index becomes invalid after the update.
C.The leaf block containing the row to be updated is marked as invalid.
D.A row in the leaf block of the index for the key value is deleted and inserted.
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 44
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 gets shut down automatically.
B.The database remains open.
C.The status of the database depends on the status of the USERS tablespace.
D.The database gets dismounted.
E.The database instance gets aborted.
F.The database becomes read only.
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 45
Which three descriptions are correct about the effects of the TRUNCATE command on a table? (Choose three.)
A.Delete triggers on the table are fired during the execution of the TRUNCATE command.
B.The high-water mark (HWM) is set to point to the first useable data block in the table segment.
C.The corresponding indexes for the table are also truncated.
D.Very little or no undo data is generated during the execution of the TRUNCATE command.
E.The child table is truncated when the TRUNCATE command is applied on the parent table.
Correct Answer: BCD
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 46
Your alert log file has the following information:
Tue May 25 17:43:38 2004 ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/ORCL/udump/orcl_ora_3173.trc. What would you do to ensure that database is still running correctly?
A.examine the trace file and kill the session that caused the deadlock
B.examine the trace file for details and remember that deadlocks are resolved automatically by Oracle database
C.examine the trace file for details and ask the user who caused the deadlock to roll back the transaction
D.examine the trace file for details and use the Undo Advisor to rollback the transaction that caused the deadlock
E.examine the trace file and kill both the sessions responsible for the deadlock
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 47
When you try to start the Database Control by using the emctl start dbconsole command the following error is displayed:
TZ set to America/New_york OC4J Configuration issue. /u01/app/oracle/product/10.1.0/db_1/oc4j/j2ee/OC4J_DBConsole_orcl.us.oracle.com not found.
Which two environment variables do you need to set appropriately to avoid such errors, and start the Database Control successfully? (Choose two.)
A.ORACLE_SID
B.ORACLE_BASE
C.NLS_LANG
D.ORACLE_HOME
E.LD_LIBRARY_PATH
Correct Answer: AD
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 48
Your database is functional with peak load for one hour. You want to preserve the performance statistics collected during this period so that they can be used for comparison while analyzing the performance of the database in the future. What action would you take to achieve this task?
A.Set the snapshot retention period in the Automatic Workload Repository (AWR) to zero to avoid the automatic purging of the snapshots.
B.Set the STATISTICS_LEVEL initialization parameter to ALL.
C.Create a baseline on a pair of snapshots that have the statistics of the peak-load period in the database.
D.Decrease the snapshot interval in the AWR to collect more number of snapshots during the peak-load period.
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 49
Your database is having two control files, three redo log file groups with two members in each group. Failure of which file would cause an instance to shut down?
A.one of the redo log members
B.any control file
C.loss of the initialization parameter file
D.any archive log file
E.any data file belonging to the default permanent tablespace
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 50
View the Exhibit.
The DBA creates a new user as follows:
SQL> CREATE USER manfus IDENTIFIED BY manfus TEMPORARY TABLESPACE temp; ActualTests.com User manfus creates a table as follows:
SQL> CREATE TABLE material(id NUMBER(3));
In which tablespace will the material table be created?
A.TEMP
B.SYSAUX
C.PROD
D.SYSTEM
E.USERS
Correct Answer: E
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 51
You are working on the 24X7 database with high transaction volume, to ensure faster instance recovery on your database you set the FAST_START_MTTR_TARGET initialization parameter to a very low value. What effect it will have on the database?
A.The overall database performance would be degraded. "Pass Any Exam. Any Time." - www.exammitra.com Oracle 1z0-042: Practice Exam
B.The database performance would be enhanced.
C.The mean time to recover (MTTR) would be increased.
D.The redo log files would be get filled more frequently.
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 52
Which two statements are true about simple views? (Choose two.)
A. Views can be created as read only.
B.Data manipulation language (DML) operations cannot be performed on views.
C.Views are data segments like tables.
D.Views can be created on the basis of more than one table.
Correct Answer: AD
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 53
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.124 MB (256 minus 132) of memory is available for use by all the manually sized components.
B.124 MB (256 minus 132) of memory is available for use by all the manually and automatically sized components.
C.The shared pool and the default buffer pool will not be sized smaller than 32 MB and 100 MB, respectively.
D.The shared pool and the default buffer pool will not be sized bigger than 32 MB and 100 MB, respectively.
E.The SGA_TARGET value cannot be sized smaller than 100 MB.
Correct Answer: BC
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 54
Which three statements are true regarding the logical structure of the Oracle database? (Choose three.)
A.A data block is the smallest unit of I/O for data files.
B.Each segment contains one or more extents.
C.Multiple tablespaces can share single data file.
D.Each data block in the database always corresponds to one OS block.
E.It is possible to have tablespaces of different block sizes in a database.
Correct Answer: ABE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 55
View the Exhibit and examine the output.
Which statement describes the conclusion?
A.The dictionary cache is consuming more space than the library cache.
B.Preparing indexes on the tables used in the SQL statements would improve the library cache performance.
C.The shared pool size should be increased to accommodate the SQL statements.
D.The users should use bind variables instead of literals in the query.
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 56
Which two statements about Flashback Query are true? (Choose two.)
A.The database has to be opened with the resetlogs option after performing Flashback Query.
B.It can be performed to recover ALTER TABLE statements
C.It fails when undo data pertaining to the transaction is overwritten.
D.It helps in row-level recovery from user errors.
E.It is generated by using the redo log files. "Pass Any Exam. Any Time." - www.actualtests.com 25 Oracle 1z0-042: Practice Exam
Correct Answer: CD
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 57
Which two are valid locking levels that are used by transactions in an Oracle database? (Choose two.)
A.object level
B.block level
C.schema level
D.row level
E.database level
Correct Answer: AD
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 58
Your database is in NOARCHIVELOG mode and a logswitch happens every 20 minutes. On Thursday morning, media failure resulted in corruption of a data file belonging to the TOOLS tablespace. Backups are taken on every Sunday. What would you do in this situation?
A.restore the data files from backup and perform cancel-based recovery
B.restore the data files from backup and perform a complete recovery
C.restore only the corrupted data file and perform tablespace recovery
D.recover using Oracle Flashback Database technology
E.restore the entire database from the most recent backup and start the instance and open the database
Correct Answer: E
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 59
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.The index becomes invalid when you make any updates.
B.The index will be updated automatically at commit.
C.A leaf row in the index will be deleted and inserted.
D.A leaf will be marked as invalid.
E.An update in a leaf row takes place.
"Pass Any Exam. Any Time." - www.actualtests.com 26
Oracle 1z0-042: Practice Exam
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 60
The session of user SCOTT receives the following error after executing an UPDATE command on the EMP table:
ERROR at line 1: ORA-00060: deadlock detected while waiting for resource
On investigation, you find that a session opened by user JIM has a transaction that caused the deadlock.
Which two statements are true regarding the session of SCOTT in this scenario? (Choose two.)
A.SCOTT has to reexecute the last command in the transaction after he commits the transaction.
B.SCOTT should perform a COMMIT or ROLLBACK to allow JIM to continue with his transaction.
C.It is terminated after receiving the error and JIM can continue with his transaction.
D.The transaction in it is terminated after receiving the error and JIM can continue with his transaction.
Correct Answer: AB
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 61
You want to create a new optimized database for your transactional production environment to be used by a financial application. While creating the database, you want the Oracle software to take care of all basic settings to optimize the database performance. Which method would you use to ActualTests.com achieve this objective?
A.Use Enterprise Manager to create a new database with the Online Transaction Processing (OLTP) option.
B.Use the CREATE DATABASE .. command to create the database with Oracle-managed files.
C.Use the CREATE DATABASE .. command to create the database with Automatic Storage Management (ASM) file system.
D.Use Database Configuration Assistant (DBCA) to create the database with Transaction Processing template.
E.Use the Database Configuration Assistant (DBCA) to create the database with Oracle-managed files.
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 62
The UNDO_RETENTION parameter in your database is set to 1000 and undo retention is not guaranteed. Which statement regarding retention of undo data is correct?
A.Undo data will be retained in the UNDO tablespace for 1,000 seconds, then it gets moved to the TEMPORARY tablespace to provide read consistency.
B.Undo data will be stored permanently after 1,000 seconds.
C.Undo data becomes obsolete after 1,000 seconds.
D.Undo data gets refreshed after every 1,000 seconds.
E.Committed undo data would be retained for 1,000 seconds if free undo space is available.
Correct Answer: E
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 63
Your database is open, and you plan to perform Recovery Manager (RMAN) backups. Which three statements are true about these backups? (Choose three.)
A.The backups would be possible only if the database is running in ARCHIVELOG mode.
B.The backups would be consistent.
C.The backups need not be restored during recovery in case of a media failure.
D.The backups need to be restored and database has to be recovered in case of a media failure.
E.The backups would be possible only if the database is running in NOARCHIVELOG mode.
F.The backups would be inconsistent.
Correct Answer: ADF
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 64
While setting up an Oracle database for one of your critical applications, you want to ensure that the database is backed up at regular intervals without your intervention. What should you do to achieve the objective?
A.schedule the database backup using DBMS_JOB package after creating the database
B.schedule the database backup using Database Configuration Assistant (DBCA) while creating the database
C.schedule the database backup using Recovery Manager (RMAN) commands after creating the database
D.configure the database to run in ARCHIVELOG mode
"Pass Any Exam. Any Time." - www.actualtests.com 28 Oracle
1z0-042: Practice Exam
E.configure the Flash Recovery Area to enable automatic database backup
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 65
You have a large amount of historical data in an operating system file. Some analysts in your organization need to query this data. The file is too large to load into your current database. Which is the most effective method to accomplish the task?
A.Load the data into your database by using the PARALLEL clause.
B.Upgrade the hardware/memory to accommodate the data.
C.Use an external table so you can have the metadata available in your database, but leave the data in the operating system files.
D.Give analysts DBA privilege, so that they can query DBA_EXTERNAL_TABLES.
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 66
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.No action required.The file would be created automatically by the instance.
D.Change the value for the BACKGROUND_DUMP_DEST parameter.
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 67
Users of HR schema complain about slower-than-normal performance. On investigation the DBA found that maintenance was recently performed on some of the tables. The DBA traced the query that takes longer than normal to execute.
View the Exhibit exhibit_before.
After the DBA resolves the problem, the query performs normally.
View the Exhibit exhibit_after.
"Pass Any Exam. Any Time." - www.actualtests.com 29
Oracle 1z0-042: Practice Exam
What action would the DBA have taken to resolve the performance problem?
A.moved the indexes associated with the EMPLOYEES table to the same tablespace where the EMPLOYEES table exists
B.moved the EMPLOYEES table in to a locally managed tablespace
C.moved the EMPLOYEES table to another location in the same tablespace ActualTests.com
D.analyzed the EMPLOYEES table to collect the current statistics
E.reorganized the associated indexes for the EMPLOYEES table that were in an unusable state
Correct Answer: E
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 68
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.
"Pass Any Exam. Any Time." - www.actualtests.com 30
Oracle 1z0-042: Practice Exam
Which three options would you choose? (Choose three.)
A.set PCTFREE and PCTUSED to 50
B.set segment space management to Automatic
C.specify extent allocation as Automatic
D.specify extent allocation as Uniform
E.use the RESIZE clause while creating the tablespace
F.create the tablespace as smallfile tablespace
G.create the tablespace as bigfile tablespace
Correct Answer: BDF
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 69
View the Exhibit to observe the roles assigned to the SCOTT user.
Which statement is true about the assignment of the SELECT_CATALOG_ROLE role to the SCOTT user?
A.The user needs to enable the role explicitly.
B.The user can start using the role immediately.
C.The user can grant the role to other users.
D.The user cannot use the role at all.
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 70
"Pass Any Exam. Any Time." - www.actualtests.com 31
Oracle 1z0-042: Practice Exam
Which two statement about Automatic Storage Management (ASM) are true? (Choose two.)
A.ASM can be used to store trace files, alert log files, and the server parameter file (SPFILE).
B.ASM supports the Oracle database and operating system files.
C.ASM provides automatic load balancing across all ASM disks.
D.ASM provides mirroring on file by file basis.
Correct Answer: CD
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 71
A constraint in a table is defined with the INITIALLY IMMEDIATE clause. You executed the ALTER TABLE command with the ENABLE VALIDATE option to enable the constraint that was disabled. What are the two effects of this command? (Choose two.)
A.It does not validate the existing data in the table.
B.It prevents insert, update, and delete operations on the table while the constraint is in the process of being enabled.
C.It enables the constraint to be enforced at the end of each transaction.
D.It fails if any existing row violates the constraint.
Correct Answer: BD
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 72
Which three pieces of information are to be mandatorily provided while creating a new listener using Enterprise Manager Database Control? (Choose three.)
A.the port used by the listener ActualTests.com
B.the database services to be registered with the listener
C.the server name where the listener runs
D.the log file and trace file destination for the listener
E.the protocol used by the listener
Correct Answer: ACE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 73
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:
"Pass Any Exam. Any Time." - www.actualtests.com 32
Oracle 1z0-042: Practice Exam
REMOTE_OS_AUTHENT = TRUE
What could have been the reason to set this parameter as TRUE?
A.to disable the administration of the operating system from a remote client
B.to allow the start up and shut down of the database from a remote client
C.to enable operating system authentication for a remote client
D.to restrict the scope of administration to identical operating systems
E.to enable the administration of the operating system from a remote client
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 74
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.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
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.create a database by using CREATE DATABASE .. command and manually copy the data
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 75
View the Exhibit.
Which statement causes more undo generation?
"Pass Any Exam. Any Time." - www.actualtests.com 33
Oracle 1z0-042: Practice Exam
A.DELETE FROM emp WHERE empno=7934;
B.UPDATE emp SET comm=400 WHERE empno=7844;
C.INSERT INTO emp VALUES(7999,'JHON','CLERK',7782,'10-MAY-83',1500,NULL,10);
D.SELECT * FROM emp;
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 76
Which step do you need to perform to enable a user with the SYSDBA privilege to log in as SYSDBA in iSQL*Plus? ActualTests.com
A.No special setup is needed for the user to connect as SYSDBA in iSQL*Plus.
B.Set up a user in the Oracle Application Server Containers for J2EE (OC4J) user manager, and grant the webDba role to the user.
C. The user must be listed in the password file for the authentication.
D.The user must be granted the database administrator (DBA) privilege.
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 77
Data files of which three tablespaces can be recovered by performing an open recovery? (Choose three.)
"Pass Any Exam. Any Time." - www.actualtests.com 34
Oracle 1z0-042: Practice Exam
A.INDEX
B.TEMP
C.SYSTEM
D.SYSAUX
E.UNDO
Correct Answer: ABD
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 78
Which two operations can be flashed back using the Flashback technology? (Choose two.)
A.ALTER TABLE EMPLOYEES DROP COLUMN DESIG_ID;
B.DROP USER SMITH;
C.DROP TABLE EMPLOYEES;
D.ALTER TABLE SALES_REP DROP PARTITION P1;
E.DROP TABLESPACE USERS;
Correct Answer: BC
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 79
Users of HR schema complain about slower-than-normal performance. On investigation the DBA found that maintenance was recently performed on some of the tables. The DBA traced the query that takes longer than normal to execute.
View the Exhibit exhibit_before.
ActualTests.com After the DBA resolves the problem, the query performs normally.
View the Exhibit exhibit_after.
What action would the DBA have taken to resolve the performance problem?
"Pass Any Exam. Any Time." - www.actualtests.com 35
Oracle 1z0-042: Practice Exam
A.analyzed the EMPLOYEES table to collect the current statistics
B.moved the EMPLOYEES table in to a locally managed tablespace
C.reorganized the associated indexes for the EMPLOYEES table that were in an unusable state
D.moved the EMPLOYEES table to another location in the same tablespace
E.moved the indexes associated with the EMPLOYEES table to the same tablespace where the EMPLOYEES table exists ActualTests.com
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 80
Your database is running in the ARCHIVELOG mode. You placed a tablespace, tbs_1, offline with the immediate option. Which statement is correct in this scenario?
A.The operation would fail if tbs_1 were a read/write tablespace.
B.The operation would fail if tbs_1 were the default tablespace for the database.
C.The operation would fail if tbs_1 were a read-only tablespace.
D.If the above operation were successful, media recovery would be required to bring the tablespace online.
"Pass Any Exam. Any Time." - www.actualtests.com 36
Oracle 1z0-042: Practice Exam E.If the above operation were successful, instance recovery would be required to bring the tablespace online.
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 81
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 TRUNCATE
B.BEFORE INSERT OR DELETE OR UPDATE
C.BEFORE CREATE
D.BEFORE SUSPEND
E.AFTER CREATE
F.AFTER INSERT OR DELETE OR UPDATE
G.AFTER SUSPEND
Correct Answer: G
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 82
Your database is started by using the server parameter file (SPFILE). You issued this command to change the value of the LOG_BUFFER initialization parameter:
ActualTests.com ALTER SYSTEM SET LOG_BUFFER=24M SCOPE=BOTH;
What would be the outcome of this command?
A.The command would return an error because LOG_BUFFER is a static parameter.
B.You need to restart the database so that parameter changes can come into effect.
C.The command would succeed only if initialization parameter LOG_ARCHIVE_MAX_PROCESS is set to value 2.
D.The parameter value would be changed and it would come into effect immediately.
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 83
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.Operating System (OS) script
B.Recovery Manager (RMAN) script
C.Enterprise Manager (EM) script
D.SQL script
E.PL/SQL script
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 84
You want the user APP_DBA to administer the Oracle database from a remote machine. APP_DBA is granted the SYSDBA privilege to perform administrative tasks on the database. Which file is used by the Oracle database server to authenticate APP_DBA?
A.listener controller file
B.password file
C.control file
D.control file and password file
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 85
You work in a data warehouse environment that involves the execution of complex queries. The ActualTests.com current content of the SQL cache holds the ideal workload for analysis. You want to analyze only few most resource-intensive statements. What would be your suggestion to receive recommendations on the efficient use of indexes and materialized views to improve query performance?
A.Run the Automatic Database Diagnostic Monitor (ADDM).
B.Run the SQL Access Advisor.
C.Run the SQL Tuning Advisor (STA).
D.Run the Automatic Workload Repository (AWR) report.
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 86
Which two statements are true about the Automatic Database Diagnostic Monitor (ADDM)? (Choose two.)
A.The ADDM analysis provides only the diagnostic information but does not provide recommendations.
B.The ADDM requires at least four AWR snapshots for analysis.
C.The results of the ADDM analysis are stored in the Automatic Workload Repository (AWR).
D.The ADDM calls other advisors if required, but does not provide recommendations about the advisors.
E.The ADDM runs after each AWR snapshot is collected.
Correct Answer: CE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 87
Which three pieces of information are considered while deciding the size of the undo tablespace in your database? (Choose three.)
A.undo blocks generated per second
B.the size of an undo block
C.the size of the database buffer cache
D.the size of the redo log files
E.the value of the UNDO_RETENTION parameter
Correct Answer: AB
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 88
ActualTests.com 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, perform media recovery and then instance recovery.
B.On startup, CKPT coordinates instance recovery.
C.Uncommitted changes will be rolled back after the database is opened.
D.On startup, use RMAN to perform instance recovery.
E.On startup, SMON coordinates instance recovery.
F.On startup, all the files will be synchronized and you get both committed and uncommitted data.
Correct Answer: CE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 89
User A executes the following command to update the TRANS table:
SQL> UPDATE B.trans SET tr_amt=tr_amt+500 WHERE c_code='C005';
Before user A issues a COMMIT or ROLLBACK command, user B executes the following command on the TRANS table:
SQl> ALTER TABLE trans MODIFY (tr_type VARCHAR2(3));
What would happen in this scenario?
A.The ALTER TABLE command fails due to the resource being busy.
B.The transaction for user A is rolled back.
C.The ALTER TABLE command modifies the column successfully.
D.The ALTER TABLE command waits until user A ends the transaction.
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 90
Your database is not configured for session failover. Your tnsnames.ora file contains the following details:
test.us.oracle.com=
(DESCRIPTION=
ActualTests.com
(ADDRESS_LIST=
(LOAD_BALANCE=off)
(FAILOVER=ON)
(ADDRESS=(PROTOCOL=tcp)(HOST=test1-server)(PORT=1521)) (ADDRESS=(PROTOCOL=tcp)
(HOST=test2-server)(PORT=1521))) (CONNECT_DATA=(SERVICE_NAME=test.us.oracle.com)))
Which feature is enabled in this case?
A.Connect-time failover
B.Instance failover
C.Database failover
D.Load balancing
"Pass Any Exam. Any Time." - www.actualtests.com 40
Oracle 1z0-042: Practice Exam
E.Transparent Application Failover (TAF)
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 91
You backed up the control file to trace. Which statement is true about the trace file generated?
A.The trace file contains the instructions to manually re-create the control file.
B.The trace file is a backup set created during the backup of the control file.
C.The trace file is an image copy of the control file created during the backup of the control file.
D.The trace file has a SQL script to re-create the control file.
E.The trace file is in binary format.
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 92
You are using flat files as the data source for one of your data warehousing applications. You plan to move the data from the flat file structures to an Oracle database to optimize the application performance. In your database you have clustered tables. While migrating the data, you want to have minimal impact on the database performance and optimize the data load operation. Which method would you use to load data into Oracle database?
A.use the external table population
B.use the conventional path data load of SQL*Loader utility
C.use the Oracle Data Pump export and import utility
D.use the direct path data load of Oracle export and import utility ActualTests.com
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 93
You execute the following set of commands to create a database user and to grant the system privileges in your production environment.
SQL> CREATE USER user01
IDENTIFIED BY oracle DEFAULT TABLESPACE tbs1 TEMPORARY TABLESPACE temp PROFILE default / "Pass Any Exam. Any Time." - www.actualtests.com 41 Oracle 1z0-042: Practice Exam
SQL> GRANT create session, create table TO user01;
While executing the command to create a table, the user gets the following error message and the CREATE TABLE.. command fails.
ERROR at line 1: ORA-01950: no privileges on tablespace
What could be the possible reason for this error message?
A.The tablespace TBS1 is full.
B.The user does not have sufficient system privileges to create table in the TBS1 tablespace.
C.The user does not have sufficient privileges to create table on the default permanent tablespace.
D.The user does not have quota on the TBS1 tablespace.
E.The user is not the owner of the SYSTEM tablespace.
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 94
You are using flat files as the data source for one of your data warehousing applications. You plan to move the data from the flat file structures to an Oracle database to optimize the application performance. In your database you have clustered tables. While migrating the data, you want to have minimal impact on the database performance and optimize the data load operation. Which method would you use to load data into Oracle database?
A.use the external table population
B.use the direct path data load of Oracle export and import utility ActualTests.com
C.use the Oracle Data Pump export and import utility
D.use the conventional path data load of SQL*Loader utility
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 95
User SCOTT wants to export his objects using Oracle Data Pump and executes the following command:
$ expdp scott/tiger
directory = EXPORT_DIR
dumpfile = scott.dmp
"Pass Any Exam. Any Time." - www.actualtests.com 42
Oracle 1z0-042: Practice Exam
include = table include = view:"like '%DEPARTMENTS%'" content = DATA_ONLY
Which task would the command accomplish?
A.Oracle Data Pump would export all of the table structures along with data and all the views.
B.Oracle Data Pump would export the table data and the view definitions where the view name contains a string named DEPARTMENTS.
C.Oracle Data Pump would export only the data of all of the tables and views.
D.Oracle Data Pump would export the table data and the view definitions with data where view name contains a string named DEPARTMENTS.
E.Oracle Data Pump would export all of the table structures and the view definitions with data where view name contains a string named DEPARTMENTS.
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 96
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.local naming method
B.host naming method
C.external naming method
D.directory naming method
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 97
View the Exhibit to observe the message received while trying to drop the SL_REP user in Oracle Enterprise Manager.
The SL_REP user owns objects and is currently connected to the database instance. What would happen if you click YES in the Exhibit?
"Pass Any Exam. Any Time." - www.actualtests.com 43
Oracle 1z0-042: Practice Exam
A.The drop operation waits until the session started by the user ends.
B.The user is dropped successfully, and the session started by the user is killed.
C.An error is returned, and the user is not dropped.
D.The user is dropped, and the objects owned by the user are transferred to the recycle bin.
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 98
You want to move all the objects of the APPS user in the test database to the DB_USER schema of the production database. Which option of IMPDP would you use to accomplish this task?
A.REMAP_TABLESPACE
B.FULL
C.SCHEMAS
D.REMAP_SCHEMA
E.REMAP_DATAFILES
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 99
Which two statements regarding the LOGGING clause of the CREATE TABLESPACE... statement are correct? (Choose two.)
A.This clause is not valid for a temporary or undo tablespace.
B.The tablespace-level logging attribute can be overridden by logging specifications at the table, index, materialized view, materialized view log, and partition levels.
C.If the tablespace is in the NOLOGGING mode, no operation on the tablespace will generate redo.
D.The tablespace will be in the NOLOGGING mode by default, if not specified while creating a tablespace.
"Pass Any Exam. Any Time." - www.actualtests.com 44
Oracle 1z0-042: Practice Exam
Correct Answer: AB
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 100
You work in a data warehouse environment that involves the execution of complex queries. The current content of the SQL cache holds the ideal workload for analysis. You want to analyze only few most resource-intensive statements. What would be your suggestion to receive recommendations on the efficient use of indexes and materialized views to improve query performance?
A.Run the Automatic Database Diagnostic Monitor (ADDM).
B.Run the SQL Tuning Advisor (STA).
C.Run the Automatic Workload Repository (AWR) report.
D.Run the SQL Access Advisor.
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 101 Which two statements are true about a bitmap index? (Choose two.)
A.It is recommended for the columns that have unique values.
B.It can be converted to a B-tree index by using the ALTER INDEX command.
C.It has a bitmap segment for each distinct value in the key column, containing a string of bits in which each bit represents the presence or absence of a key column value.
D.Updating the key column locks the whole bitmap segment that contains the bit for the key value to be updated.
Correct Answer: CD
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 102
You scheduled a backup by using the Schedule Backup option in Oracle Enterprise Manager. Which statement about the script created by Oracle Enterprise Manager is true?
A.It is a Recovery Manager (RMAN) script.
B.It is a PL/SQL file.
C.It is a SQL script.
D.It is an operating system (OS) file.
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 103
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.using anonymous PL/SQL blocks instead of subprograms
B.referring to views instead of tables inside the subprograms
C.creating PL/SQL packages to include interrelated subprograms
D.avoiding the use of cursors in the subprograms
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 104
Your database is running under automatic undo management and the UNDO_RETENTION parameter is set to 900 sec. You executed the following command to enable retention guarantee:
SQL> ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;
What effect would this statement have on the database?
A.The extents containing committed data in the undo tablespace are not overwritten until the instance is shut down. ActualTests.com
B.The extents in undo tablespace retain data until the next full database backup.
C.The extents containing committed data in the undo tablespace are never overwritten.
D.The extents which no longer contain uncommitted data in the undo tablespace are not overwritten for at least15 minutes.
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 105
You have a large amount of historical data in an operating system file. Some analysts in your organization need to query this data. The file is too large to load into your current database. Which is the most effective method to accomplish the task?
"Pass Any Exam. Any Time." - www.actualtests.com 46
Oracle 1z0-042: Practice Exam
A.Load the data into your database by using the PARALLEL clause.
B. Upgrade the hardware/memory to accomodate the data
C.Use an external table so you can have the metadata available in your database, but leave the data in the operating system files.
D.Give analysts DBA privilege, so that they can query DBA_EXTERNAL_TABLES.
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 106
According to your backup strategy, you performed an incremental level 0 backup of your database. Which statement regarding this backup is true?
A.The backup is similar to image copy.
B.The backup contains only unused data blocks.
C.The backup contains all used data blocks.
D.The backup contains all data blocks changed since the last incremental level 1 backup.
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 107
Which two statements about bigfile tablespaces are true? (Choose two.)
A.The bigfile tablespace have only one data file.
B.The bigfile tablespace can be converted to a smallfile tablespace.
C.The database can have only one bigfile tablespace.
D.The extent management is dictionary managed.
E.The segment space management is automatic.
Correct Answer: AE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 108
View the Exhibit and examine the output.
Which statement describes the conclusion?
"Pass Any Exam. Any Time." - www.actualtests.com 47
Oracle 1z0-042: Practice Exam
A.The users should use bind variables instead of literals in the query.
B.The shared pool size should be increased to accommodate the SQL statements.
C.Preparing indexes on the tables used in the SQL statements would improve the library cache performance.
D.The dictionary cache is consuming more space than the library cache.
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 109
View the Exhibit and analyze the CREATE TABLE statements used to create the ITEMS and ORDERS tables.
The ITEMS table has 50 rows and ORDERS table has 500 rows. Because you decide to not deal with the item code 188 in future, you execute the following command to remove the item: SQL> DELETE FROM items WHERE item_code=188;
What is the effect of this command?
A.The command fails with integrity violation error.
B.The row containing the value 188 corresponding to the ITEM_CODE column is deleted from the ITEMS table,
and the dependent rows in the ORDERS table are populated with NULL values in their ITEM_CODE columns.
C.The row containing the value 188 corresponding to the ITEM_CODE column is deleted from the ITEMS table,
and the foreign key constraint is disabled in the ORDERS table.
D.The row containing the value 188 corresponding to the ITEM_CODE column is deleted from the ITEMS table,
and the dependent rows in the ORDERS table are also deleted automatically.
"Pass Any Exam. Any Time." - www.actualtests.com 48
Oracle 1z0-042: Practice Exam
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 110
Which statement is true about the loss of system-critical data files in ARCHIVELOG mode?
A.The data files can be recovered till the last committed transaction.
B.The data files can be recovered to the time of the most recent backup.
C.The data files can be recovered without shutting down the database.
D.The data file is taken offline automatically; the database remains open.
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 111
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 Oracle server dynamically generates the necessary statistics on tables as part of query optimization.
C.The optimizer statistics are collected automatically.
D.The snapshots for the Automatic Workload Repository (AWR) are not generated automatically.
E.Snapshots cannot be collected manually by using DBMS_WORKLOAD_REPOSITORY PACKAGE.
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 112
You define an alert to be raised when the USERS tablespace usage has reached 80% of the total space. Which area would you refer to, in order to confirm that the alert has been raised due the event?
A.the Database Control Home page
B.the DBA_ALERTS view
C.the Database Control performance page
D.the alert.log file
E.the Database Control Maintenance page
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 113 View the Exhibit.
What would happen if you change the value of Desired Mean Time To Recover to 30?
A.The Redo Log Advisor would be disabled.
B.Automatic checkpoint tuning would be disabled.
C.The MTTR Advisor would be enabled.
D.The redo log from log buffers would be written to redo log files at a slower rate.
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 114
Your database is configured in NOARCHIVELOG mode. All the control files have been lost due to a hard disk failure but the data files are not lost. You have the closed whole database backup available to you. Which two statements are true in this scenario? (Choose two.)
A.The instance aborts. ActualTests.com
B.The database can be recovered by restoring the control files from the backup.
C.The database cannot be recovered.
D.The database can be restored till the point of the last closed whole database backup.
E.The database remains opened and you have to shut it down with the ABORT option.
Correct Answer: AD
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 115
On which three can you use Recovery Manager (RMAN) to perform incremental backup? (Choose three.)
"Pass Any Exam. Any Time." - www.actualtests.com 50
Oracle 1z0-042: Practice Exam
A.flashback log file
B.control files
C.whole database
D.parameter file
E.data files
F.archived log files
G.change tracking file
H.password file
I.tablespaces
Correct Answer: CEI
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 116
You notice this warning in the alert log file:
ORA-19815: WARNING: db_recovery_file_dest_size of 3221225472 bytes is 100.00% used, and has 0 remaining bytes available.
What would you do to reclaim the used space in the Flash Recovery Area?
A.Decrease the retention time for the files.
B.Manually delete all the archived log files from the Flash Recovery Area by using operating system (OS) commands.
C.Back up the Flash Recovery Area.
D.Increase the retention time for the files.
Correct Answer: AC
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 117 View the Exhibit.
Your Oracle 10g database has 6 tablespaces in which:
-TEMP is the default temporary tablespace-UNDOTBS1 is the default undo tablespace-USERS is the default permanent tablespace
In this database, which two tablespaces can be made read only? (Choose two.)
"Pass Any Exam. Any Time." - www.actualtests.com 51
Oracle 1z0-042: Practice Exam
A.SYSAUX
B.TEMP
C.UNDOTBS1
D.USERS
E.PROD
F.SYSTEM
Correct Answer: DE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 118
While setting up an Oracle database for one of your critical applications, you want to ensure that the database is backed up at regular intervals without your intervention. What should you do to achieve the objective?
A.schedule the database backup using DBMS_JOB package after creating the database
B.schedule the database backup using Recovery Manager (RMAN) commands after creating the database
C.configure the Flash Recovery Area to enable automatic database backup ActualTests.com
D.schedule the database backup using Database Configuration Assistant (DBCA) while creating the database
E.configure the database to run in ARCHIVELOG mode
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 119
Which two statements regarding archive log destinations are true? (Choose two.)
A.The archive log files can be written only to local destinations.
B.The archiving information can be traced to the alert log file whenever the archiving to a destination is successful.
"Pass Any Exam. Any Time." - www.actualtests.com 52
Oracle 1z0-042: Practice Exam
C.The archive logs must be written to all the destinations.
D.The number of archiving destinations must be equal to the number of archive processes (ARCn).
E.A maximum of 10 destinations can be specified.
Correct Answer: BE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 120
Which two statements are true regarding the SGA_TARGET initialization parameter? (Choose two.)
A.It can be increased up to the value of the SGA_MAX_SIZE parameter.
B.Increasing the value of SGA_TARGET up to the value of SGA_MAX_SIZE disables the automatic shared memory management feature.
C.Reducing the value of the SGA_TARGET parameter takes away memory from both autotuned and manually sized components.
D.Increasing the value of the SGA_TARGET parameter distributes the increased memory among all the autotuned components.
Correct Answer: AD
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 121
A user complains about getting this error after issuing a certain SQL statement:
ORA-02393: exceeded call limit on CPU usage
ActualTests.com Because of the error, the SQL statement gets aborted. What action would you take to increase the CPU usage limit in the subsequent sessions of the user?
A.Set the RESOURCE_LIMIT initialization parameter to FALSE.
B.Modify the resource limit in the profile used by the user.
C.Increase the value of the SESSION_CACHED_CURSORS initialization parameter.
D.Increase the value of the SESSION_MAX_OPEN_FILES initialization parameter.
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 122
You are working on an online transaction processing (OLTP) system. You notice that a PL/SQL procedure got executed twice at 2:00 p.m. This has incorrectly updated the EMP_SAL table. How
"Pass Any Exam. Any Time." - www.actualtests.com 53
Oracle 1z0-042: Practice Exam
would you revert the table to its state at 2:00 p.m.?
A.Perform point-in-time recovery to 2:00 p.m.
B.Restore the entire database from the recent backup and open it.
C.Issue the rollback statement with system change number (SCN).
D.Use Flashback Table feature to revert the changes.
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 123
Your database is started with SPFILE. You want the database instance to be dynamically registered with a listener L2 with the following details:
Protocol: TCP Host: indl151e Port: 1525
Which is the correct order of the steps that you would follow to achieve this?
1. Set the LOCAL_LISTENER parameter to L2 dynamically.
2. Make an entry for L2 in tnsnames.ora on the database server.
3. Restart L2.
4. Modify the listener.ora file to add the instance name in SID_LIST of L2.
A.1, 2, 4, 3
B.1, 2; 3 and 4 are not required.
C.1, 2, 3; 4 is not required.
D.2, 1; 3 and 4 are not required.
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 124
View the Exhibit to observe the privileges granted to the MGR_ROLE role. The SCOTT user has been granted the CONNECT and RESOURCE roles only.
The database administrator (DBA) grants MGR_ROLE to the SCOTT user by using this command:
SQL> GRANT MGR_ROLE TO SCOTT WITH ADMIN OPTION;
Which statement is true about the SCOTT user after he is granted this role?
"Pass Any Exam. Any Time." - www.actualtests.com 54
Oracle 1z0-042: Practice Exam
A.The SCOTT user can grant the MGR_ROLE role and the privileges in it to other users.
B.The SCOTT user can grant only the MGR_ROLE role to other users but not the privileges in it.
C.The SCOTT user can grant the privileges in the MGR_ROLE role to other users but cannot revoke privileges from them.
D.The SCOTT user can grant the privileges in the MGR_ROLE role to other users but not with ADMIN_OPTION.
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 125
Your tnsnames.ora file has the following entry for the service alias ORCL:
ORCL =
( DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.156.24.216)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED)
(SERVICE_NAME = orcl.oracle.com)
)
)
The TNSPING command executes successfully when tested with ORCL but you are not able to connect to the database instance with the following command:
SQL> CONNECT scott/tiger@orcl
What could be the reason for this?
"Pass Any Exam. Any Time." - www.actualtests.com 55
Oracle 1z0-042: Practice Exam
A.The listener is not running at the server.
B.The orcl.oracle.com database service is not registered with the listener.
C.The DEFAULT_DOMAIN parameter is set to a wrong value in the SQLNET.ORA file.
D.The TNS_ADMIN environmental variable is set to a wrong value.
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 126
A user wants to connect to the database instance from an application that is running on a remote machine. Which tools should the DBA use to establish the required configuration to ensure that the user is able to connect to the database instance? (Choose two.)
A.Oracle Net Manager
B.Oracle Universal Installer (OUI)
C.Oracle Enterprise Manager
D.Data Pump
E.Database Configuration Assistant (DBCA)
Correct Answer: AC
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 127
Your database is open and users are connected using the LISTENER listener. The new DBA of the system stops the listener by using the following command:
LSNRCTL> STOP
What would happen to the sessions that are presently connected to the database instance?
A.The sessions are not allowed to perform any operations till the listener is started.
B.The sessions are able to perform only queries.
C.The active transactions are rolled back and the sessions get terminated.
D.The sessions are not affected and continue to function normally.
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 128 View the Exhibit.
Which statement regarding the dept and emp tables is true? "Pass Any Exam. Any Time." -www.actualtests.com 56
Oracle 1z0-042: Practice Exam
A.When you delete a row from the emp table, automatically the corresponding rows are updated with null values in the dept table.
B.When you delete a row from the emp table, you would receive a constraint violation error.
C.When you delete a row from the dept table, you would receive a constraint violation error.
D.When you delete a row from the dept table, automatically the corresponding rows are deleted from the emp table.
E.When you delete a row from the emp table, automatically the corresponding rows are deleted from the dept table.
F.When you delete a row from the dept table, automatically the corresponding rows are updated ActualTests.com with null values in the emp table.
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 129
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
"Pass Any Exam. Any Time." - www.actualtests.com 57
Oracle 1z0-042: Practice Exam
What could have been the reason to set this parameter as TRUE?
A.to enable the administration of the operating system from a remote client
B.to enable operating system authentication for a remote client
C.to allow the start up and shut down of the database from a remote client
D.to restrict the scope of administration to identical operating systems
E.to disable the administration of the operating system from a remote client
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 130
Which two operations can be flashed back using the Flashback technology? (Choose two.)
A.DROP TABLE EMPLOYEES;
B.DROP USER SMITH;
C.ALTER TABLE EMPLOYEES DROP COLUMN DESIG_ID;
D.DROP TABLESPACE USERS;
E.ALTER TABLE SALES_REP DROP PARTITION P1;
Correct Answer: AB
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 131
Which two statements about Recovery Manager (RMAN) backups are
A.Online redo log files can be backed up.
B.RMAN backup can be taken only if the database is configured in ARCHIVELOG mode.
C.Only consistent database backups can be performed.
D.Archived redo log files are backed up.
E.Only used data blocks can be backed up as backup sets.
Correct Answer: DE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 132
Which two statements are true regarding a PL/SQL package body? (Choose two.)
A.It can be changed and recompiled without making the package specification invalid.
B.It cannot invoke subprograms defined in other packages.
C.It cannot be created without a package specification. "Pass Any Exam. Any Time." - www.actualtests.com 58
Oracle 1z0-042: Practice Exam
D.It can contain only the subprograms defined in the package specification.
Correct Answer: AC
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 133
You want to create a new optimized database for your transactional production environment to be used by a financial application. While creating the database, you want the Oracle software to take care of all basic settings to optimize the database performance. Which method would you use to achieve this objective?
A.Use Enterprise Manager to create a new database with the Online Transaction Processing (OLTP) option.
B.Use the CREATE DATABASE .. command to create the database with Automatic Storage Management (ASM) file system.
C.Use the Database Configuration Assistant (DBCA) to create the database with Oracle-managed files.
D.Use the CREATE DATABASE .. command to create the database with Oracle-managed files.
E.Use Database Configuration Assistant (DBCA) to create the database with Transaction Processing template.
Correct Answer: E
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 134
Which statement is true when you run the SQL Tuning Advisor with limited scope?
A. SQL structure analysis is not performed for the SQL statements.
B. Profile recommendations are not generated for the SQL statements.
C.The staleness and absence of the statistics are not checked for the objects in the query supplied to the SQL Tuning Advisor.
D.Access path analysis is not performed for the SQL statements.
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 135
You have to shut down the database instance with the ABORT option because of a hardware failure.
Which statement is true about the subsequent opening of the database?
A.The database would open normally.
"Pass Any Exam. Any Time." - www.actualtests.com 59
Oracle 1z0-042: Practice Exam
B.The database would open after automatically performing instance recovery.
C.The database would not open, and it would stop at mount stage.
D.The database would not open, and you have to perform database recovery to open it.
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 136
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 Grid Architecture
B.Optimal Flexible Architecture
C.Oracle database architecture
D.Oracle Managed Files
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 137
You want to set up the Oracle Database 10g installation to follow the French language, with France as the territory, and the date to be displayed in French for all users' sessions.Which ActualTests.com environmental variable would you set to achieve this objective?
A.NLS_DATE_LANGUAGE
B.NLS_LANG
C.NLS_NCHAR_CHARACTERSET
D.NLS_LANGUAGE
E.NLS_CHARACTERSET
F.NLS_TERRITORY
G.NLS_LENGTH_SEMANTICS
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 138
Your test database is running in NOARCHIVELOG mode. What are the implications of this?
A.You can perform closed database backups.
B.You can perform the backup of only the SYSTEM tablespace when the database is open.
C.You can perform open database backups.
D.You cannot perform schema-level logical backups.
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 139
Which two database operations can be performed at the MOUNT stage of database startup? (Choose two.)
A.configuring the database in ARCHIVELOG mode
B.multiplexing of control files
C.dropping the database user
D.renaming of data files
E.renaming of control files
Correct Answer: AD
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 140
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? ActualTests.com
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 with data files and then use the same template to create the database in the new location
D.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
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 141
"Pass Any Exam. Any Time." - www.actualtests.com 61
Oracle 1z0-042: Practice Exam
View the Exhibit.
Your Oracle 10g database has 6 tablespaces in which:
-TEMP is the default temporary tablespace- UNDOTBS1 is the default undo tablespace- USERS is the default permanent tablespace
In this database, which three tablespaces can be made offline? (Choose three.)
A.PROD
B.SYSAUX
C.USERS
D.SYSTEM
E.TEMP
F.UNDOTBS1
Correct Answer: ABC
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 142
While running the Oracle Universal Installer on a Unix platform to install Oracle Database 10g software, you are prompted to run orainstRoot.sh script.
What does this script accomplish?
A.It creates the pointer file.
B.It creates the Inventory pointer file.
C.It creates the Oracle user for installation.
D.It modifies the Unix kernel parameters to match Oracle's requirement.
E.It creates the base directory.
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 143
Because of a power outage, instance failure has occurred. From what point in the redo log does recovery begin and where does it end?
A.all redo logs before the point of last commit
B.beginning of redo log to end of redo log
C.beginning of redo log to checkpoint position
D.checkpoint position to end of redo log
E.current redo log and inactive redo log
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 144
Which is the correct description of the significance of the ORACLE_HOME environmental variable?
A.It specifies the base directory of Optimal Flexible Architecture (OFA).
B.It specifies the directory containing the Oracle-Managed Files.
C.It specifies the directory for database files, if not specified explicitly.
D.It specifies the directory containing the Oracle software.
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 145
You specified segment space management as automatic for a tablespace. What effect would this have on space management? ActualTests.com
A.The segment would be managed by the freelists.
B.Extents would be managed by the freelists.
C.Free space would be managed by the data dictionary.
D.The segment would be managed by bitmaps.
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 146
The junior DBA in your organization has accidentally deleted the alert log file. What will you do to create new alert log file?
"Pass Any Exam. Any Time." - www.actualtests.com 63
Oracle 1z0-042: Practice Exam
A.Change the value for the BACKGROUND_DUMP_DEST parameter.
B.You have to recover the alert log file from the valid backup.
C.No action required.The file would be created automatically by the instance.
D.Create the new text file file as ALERT.LOG.
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 147
What is the implication of setting the initialization parameter FAST_START_MTTR_TARGET to 0 in your database?
A.Checkpoint information would not be written to the alert log file.
B.Automatic tuning of checkpoint would be disabled.
C.Redo Log Advisor would be disabled.
D.MTTR Advisor would be disabled.
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 148
You suspect unauthorized data manipulation language (DML) operations on a particular table. You want to track users who are performing the transactions and the values used in the transactions. Also, you plan to transfer these values to another table for analysis. How would you achieve this?
A.by using anonymous PL/SQL blocks
B.by using triggers
C.by auditing all DML operations on the table
D.by using external tables ActualTests.com
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 149
You executed the following command to start the database:
SQL> STARTUP ORACLE instance started.
Total System Global Area 281018368 bytes Fixed Size 789000 bytes Variable Size 229635576 bytes
"Pass Any Exam. Any Time." - www.actualtests.com 64
Oracle 1z0-042: Practice Exam
Database Buffers 50331648 bytes
Redo Buffers 262144 bytes
ORA-00205: error in identifying controlfile,
check alert log for more info
Which view would you query at this stage to investigate this missing control file?
A.DBA_CONTROL_FILES
B.V$INSTANCE
C.V$CONTROLFILE
D.V$CONTROLFILE_RECORD_SECTION
E.V$DATABASE_PROPERTIES
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 150
The application workload on your database is same between 10 a.m. and 11 a.m. on weekdays. Suddenly you observe poor performance between 10 a.m. and 11 a.m. in the middle of the week. How would you identify the changes in configuration settings, workload profile, and statistics to diagnose the possible causes of the performance degradation?
A.by using the Automatic Workload Repository report
B.by using the SQL access advisor
C. by running the Automatic Database Diagnostic Monitor (ADDM)
D.by analyzing the output of the V$ACTIVE_SESSION_HISTORY view
E.by using the Automatic Workload Repository Compare Period report
Correct Answer: E
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 151
In your database, the Log Writer (LGWR) process is unable to write to a member of a current redo log group due to read/write failure. Which two effects would you see in your database? (Choose two.)
A.The database instance aborts.
B.The database operation temporarily halts till the member becomes available.
C.The member would be marked as STALE and an error message would be written to the alert log file and LGWR trace file.
D.The status of the group changes to INACTIVE and an error message would be written to the alert log file and LGWR trace file.
"Pass Any Exam. Any Time." - www.actualtests.com 65
Oracle 1z0-042: Practice Exam
E.Writing proceeds as normal. LGWR writes to the available members of a group and ignores the unavailable members.
Correct Answer: CE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 152
Your database is in NOARCHIVELOG mode. After which two operations you should take the backup of control file ? (Choose two.)
A.dropping a table from the database
B.adding a new user to the database
C.dropping a data file from a tablespace
D.dropping a user from the database
E.adding a new tablespace to the database
Correct Answer: CE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 153
View this parameter setting in your database:
DB_CREATE_FILE_DEST='D:\oracle\product\10.2.0\oradata\oracle'
You created a tablespace by using this command:
CREATE TABLESPACE USERS;
ActualTests.com Which two statements are true about the USERS tablespace? (Choose two.)
A.The tablespace has two data files.
B.Data files belonging to the USERS tablespace cannot be renamed.
C.The tablespace can be extended without specifying the data file.
D.An error is reported and tablespace creation fails.
E.Data files are created with names generated by the instance.
Correct Answer: CE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 154
The HR user creates a view with this command:
"Pass Any Exam. Any Time." - www.actualtests.com 66
Oracle 1z0-042: Practice Exam
SQL> CREATE VIEW emp_v AS SELECT * FROM scott.emp;
Now HR wants to grant the SELECT privilege on the EMP_V view to the JIM user. Which statement is true in this scenario?
A.SCOTT has to grant the SELECT privilege on the EMP table to JIM before this operation.
B.HR can grant the privilege to JIM but without GRANT OPTION.
C.HR can grant the privilege to JIM because HR is the owner of the view.
D.HR needs the SELECT privilege on the EMP table with GRANT OPTION from SCOTT for this operation.
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 155
You executed the following command to export the EMPLOYEES table from a remote machine:
$ EXPDP hr/hr@data.us.oracle.com DUMPFILE=my_dir:exp_hr.log LOGFILE=data_pump_dir:log_hr.log TABLES=employees
What would be the outcome of this command?
A.The command would execute successfully. But log file would not be created as DATA_PUMP_DIR directory is only accessible to user with SYSDBA privilege.
B.The command fails with an error because no absolute path is specified for log file and dumpfile.
C.The command would execute successfully and the export dump file would be created in the destination of the directory object MY_DIR. ActualTests.com
D.The command fails with an error because DATA_PUMP_DIR directory have higher precedence over the per-file directory.
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 156 Which three statements are correct about temporary tables? (Choose three.)
A.The data inserted into a temporary table in a session is available to other sessions.
B.Temporary tables are always created in a user's temporary tablespace.
C.Indexes and views can be created on temporary tables.
"Pass Any Exam. Any Time." - www.actualtests.com 67
Oracle 1z0-042: Practice Exam
D.Data manipulation language (DML) locks are never acquired on the data of temporary tables. E.Both the data and the structure of temporary tables can be exported.
Correct Answer: BCD
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 157
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.SELECT job FROM emp WHERE job='CLERK' FOR UPDATE OF empno;
B.DELETE FROM emp WHERE job='MANAGER';
C.LOCK TABLE emp IN EXCLUSIVE MODE;
D.LOCK TABLE emp IN SHARE MODE;
E.INSERT INTO emp(empno,ename) VALUES (1289,'Dick');
Correct Answer: ACD
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 158
In the middle of a transaction, a user session was abnormally terminated but the instance is still up and the database is open.
Which two statements are true in this scenario? (Choose two.) ActualTests.com
A.The alert log file gives detailed information about the failure.
B.Data modified by the transaction up to the last commit before the abnormal termination is retained in the database.
C.The transaction is rolled back by the next session that refers to any of the blocks updated by the failed transaction.
D.PMON rolls back the transaction and releases the locks.
E.SMON rolls back the transaction and releases the locks.
F.Event Viewer gives more details on the failure.
Correct Answer: BD
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 159
You created a response file and want to check it before starting installations in silent mode. You started installation of Oracle software in interactive mode by providing the response file. In the middle of the installation, you realize that the behavior of the Oracle Universal Installer (OUI) is not consistent with the response file. What action would you take to detect the cause of this behavior?
A.Refer to the contents of the oraInst.loc file to verify the steps performed by OUI.
B.Compare the contents of the installActions.log file with that of the response file.
C.Refer to the alert log file for information regarding the actions performed by OUI during installation.
D.Compare the contents of the install.log file with the response file.
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 160
In your Oracle 10g database , you have scheduled a job to update the optimizer statistics at 05:00 pm every Friday. The job has successfully completed. Which three pieces of information would you check to confirm that the statistics have been collected? (Choose three.)
A.last analyzed date
B.average row size
C.number of extents present in the table
D.size of table in bytes
E.size of table in database blocks
F.number of free blocks in the free list
Correct Answer: ABE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 161
View the Exhibit to examine the output of the DBA_OUTSTANDING_ALERT view.
After 30 minutes, you executed the following command:
SQL> SELECT reason,metric_value FROM dba_outstanding_alerts;
REASON METRIC_VALUE
------------------------------ ---------------------- Tablespace [TEST] is [28 perce 28.125 nt] full
"Pass Any Exam. Any Time." - www.actualtests.com 69
Oracle 1z0-042: Practice Exam
What could be the two reasons for the elimination of the other rows in the output? (Choose two.)
A.The threshold alerts are transferred to DBA_ALERT_HISTORY.
B.The non-threshold-based alerts are transferred to DBA_ALERT_HISTORY.
C.The threshold alerts related to database metrics are permanently stored in DBA_ALERT_HISTORY but not the threshold alerts related to instance metrics.
D.The threshold alert conditions are cleared.
Correct Answer: AD
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 162
View the Exhibit, and examine the alert messages.
You added space to the TEST tablespace to bring the space usage below the threshold value.
Which statement is true about the Tablespace Full alert?
A.The alert is automatically cleared and sent to the alert history.
B.The alert is cleared and transferred to the alert log file.
C.The alert appears in Oracle Enterprise Manager Database Control until it is manually cleared.
D.The alert is purged because it is a threshold alert.
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 163
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 database architecture
C.Optimal Flexible Architecture
D.Oracle Grid Architecture
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 164
The SAVE_AMT column in the ACCOUNTS table contains the balance details of customers in a bank. As part of the year-end tax and interest calculation process, all the rows in the table need to be accessed. The bank authorities want to track access to the rows containing balance amounts exceeding $200,000, and then send an alert message to the administrator. Which method would you suggest to the bank for achieving this task?
A.performing standard database auditing to audit SQL statements with granularity level set to ACCESS
B.implementing value-based auditing by using triggers ActualTests.com
C.performing standard database auditing to audit object privileges by setting the AUDIT_TRAIL parameter to EXTENDED
D.implementing fine-grained auditing with audit condition and event handler
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 165
You received complaints about the degradation of SQL query performance. You identified top SQL queries that consume time. What would be your next step to find out recommendations about statistics collection and restructuring of the SQL statement to improve query performance?
A. run Segment Advisor
"Pass Any Exam. Any Time." - www.actualtests.com 71
Oracle 1z0-042: Practice Exam
B.run the Automatic Workload Repository (AWR) report
C.run the Automatic Database Diagnostic Monitor (ADDM) on top SQL statements
D.run SQL Tuning Advisor on top SQL statements
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 166
You are working on a database created with the Oracle Database 10g software in which the initialization parameter COMPATIBLE is set to 10.0.0.
The hr_tbs tablespace in the database is created as follows:
CREATE TABLESPACE hr_tbs DATAFILE '/oracle/oradata/hr_tbs.dbf' SIZE 50M;
View the Exhibit to see the properties of the database.
Which statement is correct in this scenario?
A.All the tablespaces in the database must be of the smallfile tablespace type.
B.hr_tbs is a bigfile tablespace.
C.The tablespace type bigfile or smallfile must be mentioned in the command.
D.hr_tbs is a dictionary-managed tablespace.
E.All the tablespaces in the database will be locally managed by default.
Correct Answer: E
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 167
A user receives the following error while executing a query:
ORA-01555: snapshot too old
Which two options can be the solutions to avoid such errors in future? (Choose two.)
A.increase the size of the Database Buffer Cache
B.increase the size of the default temporary tablespace
C.increase the size of redo log files
D.increase the size of the undo tablespace
E.enable the retention guarantee for the undo tablespace
Correct Answer: DE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 168
What are the consequences of executing the SHUTDOWN ABORT command? (Choose two.)
.The database files are synchronized.
B.The database is closed, but the instance is still started.
C.The database undergoes automatic media recovery during the next startup.
D.Database buffers and redo buffers are not written to the disk.
E.Uncommitted changes are not rolled back.
Correct Answer: DE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 169
The employee IDs of employees who have made sales in the company are transferred from the EMPLOYEES table to the BONUS table with a default bonus value. Later, the human resources department decides to give bonuses to employees as per the following conditions:
1. Employees with a salary of $8,000 or less should receive a bonus.
2. Employees who have not made sales get a bonus of 1% of their salary.
3. Employees who already made sales get an increase in their bonus equal to 1% of their salary.
What would you do to implement these changes in one step?
A.Use SQL*Loader utility.
B.Use the MERGE statement.
C.Use a multitable insert operation.
D.Use a correlated UPDATE statement.
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 170
Your database instance is started using the server parameter file (SPFILE). Control files are multiplexed and stored on different disks. Because of a disk failure, you lost one of these control files. You replaced the damaged disk. What is the correct sequence of steps that you would follow to recover the control file?
1. Shut down the instance, if not already done.
2. Copy one of the remaining control files to a new location.
3. Change the value of the CONTROL_FILES initialization parameter to correspond to the new location of the control files.
4. Start up the database instance to the NOMOUNT stage.
"Pass Any Exam. Any Time." - www.actualtests.com 5
Oracle 1z0-042: Practice Exam
5. Recover the database to the point of failure of the control file.
6. Open the database.
A. 2,4,3,4,5
B. 5,2,3,4
C.4,5,6,2,3
D.1,2,4,3,5
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 171
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 STATISTICS_LEVEL initialization parameter was changed to BASIC and reset to TYPICAL before the last task.
C.The optimizer statistics was collected manually by using the DBMS_STATS package before the last task.
D.The optimizer statistics for few objects in the database were locked before the last task.
Correct Answer: B
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 172 Examine the following commands executed in your database:
SQL> ALTER SESSION RECYCLEBIN=ON;
"Pass Any Exam. Any Time." - www.actualtests.com 16
Oracle 1z0-042: Practice Exam
Session altered SQL> CREATE TABLE emp TABLESPACE tbsfd AS SELECT * FROM hr.employees; Table created.
Further, you executed the following command to drop the table:
SQL> DROP TABLE emp; Table dropped.
What happens in this scenario?
A.The table is moved to the SYSTEM tablespace.
B.The table is removed from the database permanently.
C.The table is moved to the SYSAUX tablespace.
D.The table is renamed and remains in the TBSFD tablespace.
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 173
You execute the following command to audit the database activities:
SQL> AUDIT DROP ANY TABLE BY scott BY SESSION WHENEVER SUCCESSFUL;
What is the effect of this command?
A.One audit record is created for each successful DROP TABLE command executed in the session of SCOTT.
B.One audit record is created for each successful DROP TABLE command executed by any user to drop tables owned by SCOTT.
C.One audit record is created for the whole session if user SCOTT successfully drops one or ActualTests.com more tables in his session.
D.One audit record is generated for the session when SCOTT grants the DROP ANY TABLE privilege to other users in his session.
E.One audit record is created for every session when any user successfully drops a table owned by SCOTT.
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 174
You are working on the 24X7 database with high transaction volume, to ensure faster instance recovery on your database you set the FAST_START_MTTR_TARGET initialization parameter to a very low value. What effect it will have on the database?
A.The overall database performance would be degraded.
"Pass Any Exam. Any Time." - www.actualtests.com 23
Oracle 1z0-042: Practice Exam
B.The database performance would be enhanced.
C. The mean time to recover (MTTR) would be increased.
D.The redo log files would be get filled more frequently.
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 175
You execute the following set of commands to create a database user and to grant the system privileges in your production environment.
SQL> CREATE USER user01
IDENTIFIED BY oracle
DEFAULT TABLESPACE tbs1
TEMPORARY TABLESPACE temp
PROFILE default
/
"Pass Any Exam. Any Time." - www.actualtests.com 41
Oracle 1z0-042: Practice Exam
SQL> GRANT create session, create table TO user01;
While executing the command to create a table, the user gets the following error message and the CREATE
TABLE.. command fails.
ERROR at line 1: ORA-01950: no privileges on tablespace
What could be the possible reason for this error message?
A.The tablespace TBS1 is full.
B.The user does not have sufficient system privileges to create table in the TBS1 tablespace.
C.The user does not have sufficient privileges to create table on the default permanent tablespace.
D.The user does not have quota on the TBS1 tablespace.
E.The user is not the owner of the SYSTEM tablespace.
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 176
You work in a data warehouse environment that involves the execution of complex queries. The current content of the SQL cache holds the ideal workload for analysis. You want to analyze only few most resource-intensive statements. What would be your suggestion to receive recommendations on the efficient use of indexes and materialized views to improve query performance?
A.Run the Automatic Database Diagnostic Monitor (ADDM).
B.Run the SQL Tuning Advisor (STA).
C.Run the Automatic Workload Repository (AWR) report.
D.Run the SQL Access Advisor.
Correct Answer: D
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 177
View the Exhibit and examine the output.
Which statement describes the conclusion?
"Pass Any Exam. Any Time." - www.actualtests.com 47
Oracle 1z0-042: Practice Exam
A.The users should use bind variables instead of literals in the query.
B.The shared pool size should be increased to accommodate the SQL statements.
C.Preparing indexes on the tables used in the SQL statements would improve the library cache performance.
D.The dictionary cache is consuming more space than the library cache.
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 178
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.a shareable table lock for the table containing the row
B.null lock on the table containing the row
C.exclusive table-level lock for the table containing the row
D.null lock on the row being updated
E.ROW SHARE lock for the row being updated
F.shared row-exclusive lock for the row being updated
G.ROW EXCLUSIVE lock for the row being updated
Correct Answer: AG
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 179
A constraint in a table is defined with the INITIALLY IMMEDIATE clause. You executed the ALTER TABLE command with the ENABLE VALIDATE option to enable the constraint that was disabled. What are the two effects of this command? (Choose two.)
A.It does not validate the existing data in the table.
B.It prevents insert, update, and delete operations on the table while the constraint is in the process of being enabled.
C.It enables the constraint to be enforced at the end of each transaction.
D.It fails if any existing row violates the constraint.
Correct Answer: BD
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 180
Your test database is running in NOARCHIVELOG mode. What are the implications of this?
A.You can perform closed database backups.
B.You can perform the backup of only the SYSTEM tablespace when the database is open.
C.You can perform open database backups.
D.You cannot perform schema-level logical backups.
Correct Answer: A
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 181
You execute the following command to audit the database activities:
SQL> AUDIT DROP ANY TABLE BY scott BY SESSION WHENEVER SUCCESSFUL;
What is the effect of this command?
A.One audit record is created for each successful DROP TABLE command executed in the session of SCOTT.
B.One audit record is created for each successful DROP TABLE command executed by any user to drop tables owned by SCOTT.
C.One audit record is created for the whole session if user SCOTT successfully drops one or ActualTests.com more tables in his session.
D.One audit record is generated for the session when SCOTT grants the DROP ANY TABLE privilege to other users in his session.
E.One audit record is created for every session when any user successfully drops a table owned by SCOTT.
Correct Answer: C
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
