1z0-043
Oracle Database 10g: Administration II
QUESTION NO: 1
You observe that a database performance has degraded over a period of time. While investigating the
reason, you find that the size of the database buffer cache is not large enough to cache all the needed data
blocks.
Which advisory component would you refer to, in order to determine that required size of the database
buffer cache?
A. Memory Advisor
B. Segment Advisor
C. SQL Tuning Advisor
D. SQL Access Advisor
E. Automatic Database Diagnostic Monitor (ADDM)
Answer: A
QUESTION NO: 2
Exhibit
1z0- 043
- 4 -
In your production database, the total waits and the time waited for log file parallel write are
significantly high. While investigating the reason, you find that there are three redo log groups with two
members in each group, and all redo log members are places on a single physical disk.
What action would you take to minimize the waits?
A. start the log writer slave processes
B. increase the number of redo log files
C. increase the size of the redo log buffer
D. place the redo log files on the different disks
E. increase the number of log writer processes
Answer: D
QUESTION NO: 3
One of the tablespaces is read-only in your database. The loss of all control files forced you to re-create
the control file.
Which operation do you need to perform after re-creating the control file and opening the database?
A. drop and re-create the read-only tablespaces
B. rename the read-only data files to their correct file names
C. change the tablespace status from read/write to read-only
D. re-create the read-only tablespace because it is automatically removed
Answer: B
QUESTION NO: 4
You have set some of the initialization parameters as:
DB_BLOCK_SIZE = 8KB
SGA_MAX_SIZE = 2GB
SGA_TARGET = 0
SHARED_POOL_SIZE = 120MB
DB_CACHE_SIZE = 896MB
STREAM_POOL_SIZE = 0
LARGE_POOL_SIZE = 110 MB
1z0- 043
- 5 -
Which two statements are correct? (Choose two.)
A. You cannot set a value for the DB_8K_CACHE_SIZE parameter.
B. If you increase the size of the large pool to 120 MB, then the memory allocated to the shared poll will be
reduced to 110 MB.
C. If the value for SGA_TARGET is changed to 1 GB and SHARED_POOL_SIZE is set to 120 MB, then
memory cannot be taken from the shared pool, even if the shared pool has free space available.
D. If an application attempts to allocate more than 120 MB from the shared pool and free space is available
in the buffer pool, then the free space from the buffer pool is allocated to the shared pool.
Answer: A, C
QUESTION NO: 5
You are performing a block media recovery on the tools01.dbf data file in the SALES database
using RMAN. Which two statements are correct in this scenario? (Choose two.)
A. You must ensure that the SALES database is mounted or open.
B. You must restore a backup control file to perform a block media recovery.
C. You must take the tools01.dbf data file offline before you start a block media recovery.
D. You must put the database in NOARCHIVELOG mode to perform a block media recovery.
E. You can perform only a complete media recovery of individual blocks, point-in-time recovery of
individual data blocks is not supported.
Answer: A, E
QUESTION NO: 6
Exhibit
1z0- 043
- 6 -
You executed the following command to perform a backup of the USERS tablespace:
RMAN > BACKUP TABLESPACE USERS;
Which type of backup would this command perform?
A. backup set
B. image copy
C. incremental backup
D. None; the user receives an error indicating that the backup type must be specified.
Answer: A
QUESTION NO: 7
In your database, online redo log files are multiplexed and one of the members in a group is lost due to
media failure?
How would you recover the lost redo log member?
A. import the database from the last export
B. restore all the members in the group from the last backup
C. drop the lost member from the database and then add a new member to the group
D. restore all the database files from the backup and then perform a complete recovery
E. restore all the database files from the backup and then perform an incomplete recovery
Answer: C
1z0- 043
- 7 -
QUESTION NO: 8
You are using Oracle Database 10g. Which statement regarding an incomplete recovery is true?
A. You do not need to restore all the data files.
B. You do not need to open the database with the RESETLOGS operation
C. You do not need to perform a full backup after the RESETLOGS operation.
D. You do not need to recover all the data files to the same system change number (SCN).
Answer: C
QUESTION NO: 9
Exhibit
View the Exhibit and examine the Resource Manager settings for the groups at different levels.
Which two effects would be the result of this setting? (Choose two.)
A. The members of LOW_GROUP would get more priority than those of OTHER_GROUPS.
B. The members of SYS_GROUP would get most of the CPU allocation at level 1.
C. The members of LOW_GROUP would get most of the CPU allocation because it has CPU allocation at
two different levels.
D. The members of LOW_GROUP would get no CPU allocation at level1 if the members of
OTHER_GROUPS are using all the CPU at level 2.
E. The members of SYS_GROUP would get no CPU allocation at level 1 if the members of
OTHER_GROUPS are using all the CPU at level 2.
1z0- 043
- 8 -
Answer: A, B
QUESTION NO: 10
The current time zone for one of the user sessions is set to the database local time zone. For one
application, the user session requires the time zone to be set to the local operating system time zone
without affecting other user sessions.
Which two solutions could the user implement to achieve this objective? (Choose two.)
A. use the ALTER SYSTEM command to change the time zone
B. use the ALTER SESSION command to change the time zone
C. use the ALTER DATABASE command to change the time zone
D. set the value for the operating system variable ORA_SDTZ on the client machine
E. set the value for the operating system variable ORA_SDTZ on the database server machine
Answer: B, D
QUESTION NO: 11
You work as a database administrator at TestKing.com. Your database is open and running in
ARCHIVELOG mode. You take RMAN full backups every Sunday night. On Monday morning, while
querying the user1.employees table, you receive the following error message:
01578: ORACLE data block corrupted (file # 5, block # 51) ORA-01110: data
file 5:
./u01/app/oracle/oradata/orcl/example01.dbf.
You need to rectify the corruption while ensuring the following:
The data file should remain online.
The mean time to recover (MTTR) should be minimal.
You are not using a backup control file and all the archived logs are accessible. Which option would you
choose?
A. flash back the corrupted blocks
B. use the DBMS_REPAIR package
C. use the RMAN TSPITR command
D. use the RMAN BLOCKRECOVER command
1z0- 043
- 9 -
E. use the RESTORE DATABASE and RECOVER DATABASE commands
F. investigate the time at which the corruption occurred and perform a point-in-time recovery
Answer: D
QUESTION NO: 12
You are working on an Oracle Database 10g database. You enabled the Flashback Database feature.
Which two statements regarding flashback logs are true? (Choose two.)
A. Flashback logs are not archived.
B. Flashback logs are maintained in redo log files.
C. Flashback logs are maintained in the Flash Recovery Area.
D. Flashback logs are used to maintain Flashback Database related errors.
E. Flashback logs need to be cleared manually after you disable Flashback Database.
Answer: A, C
QUESTION NO: 13
You executed the following query:
SELECT operation, undo_sql, table_name
FROM flashback_transaction_query;
Which statement is correct regarding the query output?
A. It would return information regarding only the last committed transaction.
B. It would return only the active transactions in all the undo segments in the database.
C. It would return only the committed transactions in all the undo segments in the database.
D. It would return both active and committed transactions in all the undo segments in the database.
E. It would return information regarding the transactions that began and were committed in the last 30
minutes.
Answer: D
QUESTION NO: 14
1z0- 043
- 10 -
Consider the following configuration:
/devices/D1 is a member of disk group dgroupA.
/devices/D2 is a member of disk group dgroupA.
/devices/D3 is a member of disk group dgroupA.
You plan to add a new disk, /devices/D4, to the disk group dgroupA.
You execute the following command:
SQL> ALTER DISKGROUP dgroupA ADD DISK ./devices/D*.;
Which task would be accomplished by the command?
A. The command adds the new disk, D4, to the disk group.
B. The command would result in an error because the is no disk by the name ../devices/D*..
C. The command will be ignored because disks starting with .D. are already members of the disk group.
D. The command would result in an error because no wildcard characters can be used in the disk name.
E. The command first detaches all the member disks starting with .D., and the reattaches them including
the new disk.
Answer: A
QUESTION NO: 15
You work as a database administrator at TestKing.com. In your production database there is a job,
CALC_STAT, which has been scheduled to run every Friday at 5.00 p.m. CALC_STAT updates the
optimizer statistics for the objects owned by the APPS schema. You want the task to be generic, thereby
allowing users to modify the attributes of the task at run time without affecting the original task.
Which component of Oracle Scheduler must you define to achieve this?
A. Window
B. Program
C. Job class
D. Window group
Answer: B
QUESTION NO: 16
1z0- 043
- 11 -
Immediately after adding a new disk to or removing an existing disk from an Automatic Storage
Management (ASM) instance, you find that the performance of the database decreases initially, until the
addition or removal process is completed. Performance then gradually returns to normal levels.
Which two activities could you perform to maintain a consistent performance of the database while
adding or removing disks? (Choose two.)
A. increase the number of checkpoint processes
B. define the POWER option while adding or removing the disks
C. increase the number of DBWR processes by setting up a higher value for DB_WRITER_PROCESSES
D. increase the number of slave database writer processes by setting up a higher value for
DBWR_IO_SLAVES
E. increase the number of ASM Rebalance processes by setting up a higher value for
ASM_POWER_LIMIT during the disk addition or removal
Answer: B, E
QUESTION NO: 17
The current time is 12:00 noon. You want to recover the USERS tablespace from a failure that occurred
at 11:50 a.m. You discover that the only member of an unarchived redo log group containing information
from 11:40 a.m. onwards is corrupt.
With reference to this scenario, if you are not using Recovery Manager (RMAN), which recovery method
would you use?
A. time-based recovery
B. log sequence recovery
C. cancel-based recovery
D. change-based recovery
Answer: C
QUESTION NO: 18
You lost the PRODSTD tablespace, which was read/write. The tablespace was read-only when the last
backup was performed. How would you recover the tablespace?
A. restore the tablespace from the backup; there is no need to apply the redo information
B. restore the full database to recover the data up to the point when you performed the backup
1z0- 043
- 12 -
C. restore the tablespace from the backup and then perform a recovery using the backup control file
D. restore the tablespace from the backup and then recover the tablespace; all the redo information from the
point when the tablespace was made read/write is applied
Answer: D
QUESTION NO: 19
You are using an Automatic Storage Management (ASM) instance to manage the files of your production
database. You have two disk groups, DG1 and DG2 with one device each.
In the parameter file of the production database, the following parameters have been specified:
DB_CREATE_ONLINE_LOG_DEST_1 = .+dg1.
DB_CREATE_ONLINE_LOG_DEST_2 = .+dg2.
What would be the impact of this setting?
A. When a new log group is added, it would have one member in each disk group.
B. When a new log group is added, it would have two members in each disk group.
C. When a new tablespace is added, it would have one data file in each disk group.
D. When a new log file is added, it would have one member spread across the disk groups.
Answer: A
QUESTION NO: 20
An RMAN backup job fails, displaying the following error:
ORA-19809: limit exceeded for recovery files
Your analysis shows that the Flash Recovery area is full. Which two actions could you take to resolve the
error and avoid this error in the future? (Choose two.)
A. take the backup of the flash recovery area less frequently
B. increase the value of the UNDO_RETENTION initialization parameter
C. change the value of the WORKAREA_SIZE_POLICY initialization parameter to AUTO
D. change the backup retention policy to retain the backups for a smaller period of time
E. increase the value of the DB_RECOVERY_FILE_DEST_SIZE initialization parameter
F. increase the value of the CONTROL_FILE_RECORD_KEEP_TIME initialization parameter
1z0- 043
- 13 -
Answer: D, E
QUESTION NO: 21
Your database operates in ARCHIVELOG mode. User-managed consistent backups are performed every
Sunday night.
On Tuesday, client A drops a table at 8:00 a.m. as follows:
SQL> DROP TABLE cust_ord PURGE;
Table dropped.
Client B executes a query at 9:00 a.m. on the same table as follows:
SQL> SELECT * FROM cust_ord;
SELECT * FROM cust_ord
*
ERROR at line 1:
ORA-00942: table or view does not exist
Client B needs the dropped table and reports the problem to you.
With reference to this scenario, which action should you take?
A. retrieve the table by using the flashback feature
B. restore all the data files from last Sunday.s backup and then perform a time-based recovery
C. restore all the data files from last Sunday.s backup and then perform a log sequence recovery
D. restore all the data files from last Sunday.s backup and then perform a cancel-based recovery
E. restore from last Sunday.s backup only data files that belong to the tablespace in which the table was
stored and then perform a complete recovery
Answer: E
QUESTION NO: 22
You performed an incomplete recovery on your Oracle Database 10g database using the following
command:
1z0- 043
- 14 -
SQL> RECOVER DATABASE
2 UNTIL TIME .2004-08-05:12:10:03.
3 USING BACKUP CONTROLFILE;
In which situation would the above recovery be required?
A. A user table was lost at the specified time.
B. A redo log file was lost at the specified time.
C. The backup control file is on a faster disk than the current one.
D. The current control file has a different path compared with the intended time of recovery.
E. The current control file does not match the physical structure of the database at the intended time of
recovery.
Answer: E
QUESTION NO: 23
While creating a job class using the DBMS_SCHEDULER package, you set the logging level to
LOGGING_RUNS. What would be the impact of this setting?
A. Oracle Scheduler would write detailed information to the job log for each run of each job in the job
class.
B. Oracle Scheduler would write detailed information to the job log for the first run of each job in the job
class.
C. Oracle Scheduler would write detailed information to the job log for each run of only the first job in the
job class.
D. Oracle Scheduler would write detailed information for all operations performed on all jobs in the job
class.
Answer: A
QUESTION NO: 24
You are designing an application for TestKing.com and you have been asked to design a database table to
facilitate monthly bill generation. The bill would include details of customer calls, listed in chronological
order.
Which method would you follow to achieve this objective without increasing the overhead of sorting the
rows?
1z0- 043
- 15 -
A. create a hash cluster to store the data
B. create an index cluster to store the data
C. create a partitioned table to store the data
D. create a sorted hash cluster to store the data
E. create a heap table with rowid to store the data
Answer: D
QUESTION NO: 25
You set the recovery window to seven days and the backup optimization to ON using the CONFIGURE
command of Recovery Manager (RMAN). The most recent backup of the TOOLS tablespace to disk was
taken on January 3. The TOOLS tablespace is read-only.
On February 21, when you execute a command to back up all the tablespaces to disk, you find that
RMAN backs up the TOOLS tablespace also, even though the contents of the tablespace have not changed
after the backup on January 3.
Because there are no changes made to the TOOLS tablespace, you decide that the tablespace should not be
backed up by RMAN. What can you do to skip backing up the TOOLS tablespace without changing the
current backup optimization setting?
A. configure a default device for RMAN backups
B. temporarily disable the retention policy for RMAN backups
C. configure automatic channel allocation for RMAN backups
D. use the CONFIGURE command to reconfigure the recovery window to 60 days
Answer: B
QUESTION NO: 26
You want to configure the Flashback Database feature and retain flashback logs for three days.
The steps used in this process are:
A. Set the retention target:
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320;
B. Enable Flashback Database:
SQL> ALTER DATABASE FLASHBACK ON;
1z0- 043
- 16 -
C. Mount the database:
SQL> STARTUP MOUNT EXCLUSIVE;
D. Shut down the instance:
SQL> SHUTDOWN IMMEDIATE;
E. Open the database:
SQL> ALTER DATABASE OPEN;
In what sequence would you need to perform these steps to enable the Flashback Database feature?
A. D, C, A, D, E
B. D, A, C, B, E
C. B, D, C, E, A
D. D, B, E, C, A
Answer: A
QUESTION NO: 27
In which scenarios would you rebuild an index? (Choose all that apply.)
A. when you need to disable the index usage
B. when you need to change storage options
C. when you need to enable index monitoring
D. when you need to move the index to another tablespace
Answer: B, D
QUESTION NO: 28
You enabled Automatic Shared Memory Management. The initialization parameters are set as shown
below:
SGA_TARGET= 10GB
SGA_MAX_SIZE = 14GB
STREAMS_POOL_SIZE = 1GB
SHARED_POOL_SIZE = 3GB
Which two statements are correct in this scenario? (Choose two.)
1z0- 043
- 17 -
A. A maximum of 3 GB can be allocated to shared pool.
B. The value for SGA_TARGET can be increased up to a maximum of 15 GB.
C. A total of 14 GB memory will be allocated to the automatically tuned memory components.
D. Increasing the value for SGA_TARGET will automatically increase the memory allocated for
STREAMS_POOL_SIZE.
E. Increasing the value for SGA_TARGET to 12 GB will automatically increase the memory allocated to
autotuned parameters.
F. Reducing the value for SGA_TARGET to 9 GB will automatically decrease the memory allocated to
shared pool from 3 GB to 2 GB.
Answer: B, E
QUESTION NO: 29
Which statement correctly describes the change tracking writer (CTWR) background process?
A. It keeps track of changes in the background processes.
B. It records the system change number (SCN) in the control file.
C. It writes audit records for user changes to the change tracking file.
D. It records any changes to the listened configuration in the listener.ora file.
E. It records any changes to the database parameters in the change tracking file.
F. It records the physical location of all the changes that are made to the database in the change tracking
file.
Answer: D
QUESTION NO: 30
You work as a database administrator at TestKing.com. In your production database, you observe that
users. transactions are consuming a lot of resource and you plan to impose resource restrictions by using
Resource Manager.
For which three resources can you enable usage restrictions by using Resource Manager? (Choose three.)
A. CPU usage
B. degree of parallelism
C. number of open cursors
D. number of sorts performed
E. idle time for blocking sessions
1z0- 043
- 18 -
F. number of disk I/O operations performed
G. number of memory I/O operations performed
Answer: C, D, E
QUESTION NO: 31
You have specified the warning and critical threshold values of an application tablespace to be 60% and
70% respectively. From the tablespace space usage metrics, you find that the actual space usage has
reached the specified warning threshold value, but no alerts have been generated.
What could be the reason for this?
A. The EVENT parameter was not set.
B. The SQL_TRACE parameter is set to FALSE.
C. The Enterprise Manager Grid Control is not used.
D. The STATISTICS_LEVEL parameter is set to BASIC.
E. The TIMED_STATISTICS parameter is set to FALSE.
Answer: D
QUESTION NO: 32
You executed the following command in Recovery Manager (RMAN) against your Oracle 10g database:
RMAN> REPORT NEED BACKUP days 3;
What would be the output of this command?
A. a list of files that require a backup within three days
B. a list of files that have not been backed up for three days
C. a list of files that RMAN recommends be backed up only once in every three days, based on low
volatility
D. a list of files for which a backup has already been performed and may need a backup after three days
Answer: B
1z0- 043
- 19 -
QUESTION NO: 33
Your database operates in ARCHIVELOG mode. The redo log files are not multiplexed and one of the
online redo logs is missing. The missing redo log sequence, 230, is not archived and it contained
information from 10:35 a.m. onwards.
The current time is 11:00 a.m. Because of a disk crash, you executed the following command to perform
an incomplete recovery:
RMAN> RUN {
2> SET UNTIL SEQUENCE 230 THREAD 1;
3> ALTER DATABASE MOUNT;
4> RESTORE DATABASE;
5> RECOVER DATABASE;
6> ALTER DATABASE OPEN RESETLOGS;
7> };
With reference to this scenario, which statement is true?
A. RMAN recovers up to log sequence 230, but not including 230.
B. RMAN returns an error because the log sequence number mentioned in the command should be 229.
C. RMAN returns an error because the log sequence number mentioned in the command may never be
missing.
D. RMAN recovers up to and including log sequence 229 but then returns an error because log sequence
230 is missing.
Answer: A
QUESTION NO: 34
In your database, all the tablespaces are locally managed. You started Recovery Manager (RMAN) using
recovery catalog and restored the control file by using the following command:
RMAN> RESTORE CONTROLFILE;
Which two operations do you need to perform after restoring the control file from backup? (Choose two.)
A. shut down and restart the instance
B. add new tempfiles to the temporary tablespaces after recovery
C. perform a media recovery and open the database with the RESETLOGS option
D. perform a media recovery and bring the database to NOARCHIVELOG mode
1z0- 043
- 20 -
Answer: B, C
QUESTION NO: 35
While designing your database, you have created the EMPLOYEES table as an index-organized (IOT).
You want to create a bitmap index on the JOD_ID column to make queries faster.
Which task must have been completed so that you are able to create the bitmap index?
A. A primary key must have been created.
B. A mapping table must have been created.
C. An overflow tablespace must have been specified.
D. The PCTTHRESHOLD option must have been specified.
Answer: B
QUESTION NO: 36
Exhibit
One of the important tables in the USERS tablespace was dropped and purged from the recycle bin at
9:00 a.m. You noticed this at 11:00 a.m. and you want to perform an incomplete recovery to recover the
table.
Which statement is true?
A. You must recover all data files to the required system change number (SCN).
B. You must recover all data files, except those that are offline, to the required SCN.
1z0- 043
- 21 -
C. You must recover all data files belonging to the USERS tablespace to the required SCN.
D. You cannot recover all data files because segment space management is performed manually for the
undo tablespace.
Answer: B
QUESTION NO: 37
You work as a database administrator at TestKing.com. Your production database uses an Automatic
Storage Management (ASM) instance to manage its files. You want to add a new disk group to the ASM
instance to manage the increased data load.
What action would you perform to include the new disk group in the ASM instance without causing any
impact on the currently connected users?
A. mount the new disk group in the ASM instance
B. restart the ASM instance and the production database instance
C. register the new disk groups in the production database instance
D. restart the ASM instance without restarting the production database instance
E. include the new disk group in the ASM_DISKSTRING parameter in the parameter file and restart the
ASM instance
Answer: A
QUESTION NO: 38
The DB_BLOCK_CHECKING initialization parameter is set to TRUE. What would be the result of this
setting on the data blocks being written to the datafiles, every time the DBWn writes?
A. The Oracle database will check all data blocks by going through the data on each block, making sure the
data is self-consistent.
B. The DBWn and the direct loader will calculate a checksum and store it in the cache header of every data
block when writing it to disk.
C. The Oracle database will check data blocks belonging to the SYSTEM tablespace only, by going through
the data on each block, making sure the data is self-consistent.
D. The Oracle database will check data blocks belonging to the SYSAUX tablespace only, by going through
the data on each block, making sure the data is self-consistent.
E. The Oracle database will check data blocks in the SYSTEM and SYSAUX tablespaces only, by going
through the data on each block, making sure the data is self-consistent.
1z0- 043
- 22 -
Answer: A
QUESTION NO: 39
In your production database you want to use an Automatic Storage Management (ASM) instance to
manage the database files.
Which option would you use to migrate the database files from a non-ASM instance to an ASM instance?
A. Oracle Migration Assistant
B. Recovery Manager (RMAN)
C. Oracle Data Pump Export and Import
D. conventional Oracle Export and Import
E. operating system utilities to copy the files to the ASM instance
Answer: B
QUESTION NO: 40
In the parameter file of your production database, the CONTROL_FILE_RECORD_KEEP_TIME
parameter is set to 31. What would be the impact of this setting on the backup that is being performed by
using Recovery Manager (RMAN)?
A. The backup would be retained on the media for 31 days.
B. The backup would be maintained in the recovery catalog for 31 days.
C. The backup metadata would be retained in the control file for 31 days.
D. The backup metadata would be maintained in the recovery catalog for 31 days.
E. Automatic backups of the control file will be retained in the flash recovery area for 31 days.
Answer: C
QUESTION NO: 41
You decided to change the location of the flash recovery area. You executed the following command:
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST=.+disk1. SCOPE=BOTH SID=.*.;
Which statement is true?
1z0- 043
- 23 -
A. The location can be changed only if the flash recovery area is empty.
B. After executing the command, all new flash recovery area files will be created in the new location.
C. The command will generate an error because you cannot change the flash recovery area while the
database is open.
D. All the existing files in the flash recovery area will be moved to the new location and all new Flash
Recovery Area files will be created in the new location.
Answer: B
QUESTION NO: 42
Exhibit
A user has inserted wrong department data in the DEPT3 table in the USERS tablespace. You use the
Flashback Table functionality to rectify the erroneous inserts. While performing the recovery, you choose
2004343 as the Flashback SCN.
Which two statements are correct in this scenario? (Choose two.)
A. Only the row with DEPARTMENT_ID 290 would be flashed back.
B. The rows with DEPARTMENT_ID 290 and 300 would be flashed back.
C. The rows with DEPARTMENT_ID 290 and 280 would be flashed back.
D. You would have taken the USERS tablespace offline before starting the Flashback Table operation.
E. You would have enabled row movement for the DEPT3 table before starting the Flashback Table
operation.
Answer: B, E
QUESTION NO: 43
Using Oracle Scheduler you have scheduled two jobs, JOB_A and JOB_B, to run at 9:00 p.m. every
Friday. You want both the jobs to use a single resource plan, WEEKEND_PLAN.
Which task must have already been performed to enable you to achieve this objective?
1z0- 043
- 24 -
A. A window must have been created with the WEEKEND_PLAN resource plan.
B. A program must have been created with the WEEKEND_PLAN resource plan.
C. A job class must have been created with the WEEKEND_PLAN resource plan.
D. A window group must have been created with the WEEKEND_PLAN resource plan.
Answer: A
QUESTION NO: 44
Your database is running on the automatic Program Global Area (PGA) memory management and
Shared Memory Management mode. You want to increase the memory available for the SQL work areas.
What would you do?
A. modify the HASH_AREA_SIZE initialization parameter
B. modify the PGA_AGGREGATE_TARGET initialization parameter
C. modify the WORK_AREASIZE_POLICY initialization parameter
D. increase the value of the SGA_TARGET initialization parameter
E. increase the value of the SGA_MAX_SIZE initialization parameter
F. increase the value of the SORT_AREA_SIZE initialization parameter
Answer: B
QUESTION NO: 45
On Tuesday, a junior DBA dropped an important application user account, whose schema has important
tables. You are asked to recover all the objects in the schema. On investigation, you find that the user
account was dropped at 11:00 a.m. and Sunday.s backup is the most recent backup.
Which flashback feature would you use?
A. Flashback Drop
B. Flashback Table
C. Flashback Database
D. Flashback Version Query
E. Flashback Transaction Query
Answer: C
1z0- 043
- 25 -
QUESTION NO: 46
A redo log file is corrupted while the database is open; as a consequence, database operations are stopped
because archiving cannot continue.
What would you do to solve the problem?
A. clear the redo log group
B. perform redo log file import
C. perform an incomplete recovery
D. perform a redo log recovery using Recovery Manager (RMAN)
E. shut down the database and open the database in the NOARCHIVELOG mode
Answer: A
QUESTION NO: 47
You are working on an Oracle Database 10g database. Because of data loss, you decided to perform a
Flashback Database operation using the following command:
SQL> FLASHBACK DATABASE TO TIMESTAMP(SYSDATE ?5/24);
Which two statements are true? (Choose two.)
A. SYSDATE should not be used with TIMESTAMP.
B. The database must have multiplexed redo log files.
C. The database must be in the MOUNT state to execute the command.
D. The database must be opened with the RESETLOGS option after the flashback operation.
E. The entire database needs to be restored from the most recent backup before the flashback operation.
Answer: A, B
QUESTION NO: 48
You work as a database administrator at TestKing.com. In your test database, you find that a user.s
session is executing a lot of SQL statements, resulting in the generation of a large number of trace files.
While investigating the reason, you find that SQL trace has been enabled at the instance level.
1z0- 043
- 26 -
You want to disable SQL trace, remotely, only for that user session to reduce the volume of trace data
being generated.
How do you achieve this objective?
A. by setting the SQL_TRACE parameter to FALSE in the parameter file
B. by using DBMS_MONITOR.SESSION_TRACE_DISABLE to disable the tracing for the user session
C. by setting the SQL_TRACE parameter to FALSE by using the ALTER SYSTEM command in the user
session
D. by setting the SQL_TRACE parameter to FALSE by using the ALTER SESSION command in the user
session
Answer: B
QUESTION NO: 49
You execute the following command to enable a session in resumable mode:
SQL> ALTER SESSION ENABLE RESUMABLE TIMEOUT 60;
What is the impact of a timeout on the statements being suspended?
A. The statements remain suspended for at least 60 seconds.
B. The statements are suspended for 60 seconds and then they are executed.
C. The suspended statements error out if the problem is not rectified within 60 seconds.
D. The statements are automatically suspended 60 seconds after an error is received, and then attempt to
execute normally again.
Answer: C
QUESTION NO: 50
You are managing an Oracle Database 10g database that uses Oracle Managed Files (OMF). You
enabled the block change tracking feature for the database.
Which statement regarding the change tracking file is true?
A. One block change tracking file is created for each data file. By default, the file is created in
DB_CREATE_FILE_DEST.
1z0- 043
- 27 -
B. One block change tracking file is created for each data file. By default, the file is created in
BACKGROUND_DUMP_DEST.
C. One block change tracking file is created for the database. By default, the file is created in
DB_CREATE_FILE_DEST.
D. One block change tracking file is created for the database. By default, the file is created in
BACKGROUND_DUMP_DEST.
Answer: A
QUESTION NO: 51
You want to use the SQL Tuning Advisor to generate recommendations for badly written SQL
statements in your development environment. Which three sources can you select for the advisor to
analyze? (Choose three.)
A. Top SQL
B. snapshots
C. SQL Tuning sets
D. index access path
E. optimizer statistics
F. materialized view logs
Answer: A, C, D
QUESTION NO: 52
The loss of which two types of files may require a recovery with the RESETLOGS option? (Choose two.)
A. control files
B. password file
C. archived log files
D. system-critical data files for which all the redo entries are present
E. non-system-critical data files for which all the redo entries are present
Answer: A, D
QUESTION NO: 53
1z0- 043
- 28 -
There was media failure and you need to check the data files for any block corruption. Which option
would you use to create a report on any corruptions found within the database?
A. the DBNEWID utility
B. the DBVERIFY utility
C. the ANALYZE command
D. the RMAN REPORT command
E. the RMAN CROSSCHECK command
F. the CHECK_OBJECT procedure of the DBMS_REPAIR package
Answer: B
QUESTION NO: 54
You find that the execution time of reports in your datawarehouse application is significantly high. You
suspect the lack of indexes to be the reason for the degradation in performance.
Which advisory component would you refer to, in order to determine the appropriate indexes?
A. Memory Advisor
B. Segment Advisor
C. SQL Access Advisor
D. Automatic Workload Repository (AWR)
E. Automatic Database Diagnostic Monitor (ADDM)
Answer: C
QUESTION NO: 55
You have created a resource plan, PROD_DB_PLAN, with the following consumer groups with the
respective plan directives:
Consumer group SALES with CPU_P1 as 60 and DEGREE_OF_PARALLELISM as 4.
Consumer group MARKET with CPU_P1 as 20.
Consumer group DEV with CPU_P1 as 20.
Consumer group OTHERS with CPU_P1 as 0 and CPU_P2 as 100.
Which two statements are correct in this scenario? (Choose two.)
1z0- 043
- 29 -
A. The maximum degree of parallelism for the members of the consumer groups is 4.
B. The CPU allocation will always be equal for the consumer groups MARKET and DEV.
C. The OTHERS consumer group would get 100% of the CPU if there are unused level 1 CPU resources.
D. The SALES and MARKET consumer groups would get 100% of the CPU if there are unused level 2 CPU
resources.
E. The maximum degree of parallelism for the members of the consumer group SALES is 4 with no
restriction for the remaining groups.
Answer: C, E
QUESTION NO: 56
A user executes a query on the EMP table, which contains thousands of rows, to get details about
employees in one of the departments. The user receives the following error:
SQL> SELECT ename FROM emp WHERE deptno=10 ORDER BY sal;
SELECT ename FROM emp WHERE deptno=10 ORDER BY sal
ERROR at line 1:
ORA-01157: cannot identify/lock data file 201 . see DBWR trace file
ORA-01110: data file 201: ./u01/app/oracle/oradata/orcl/temp01.dbf.
What is a possible reason?
A. The tempfile belonging to the default temporary tablespace is missing.
B. The database is opened in restricted mode, so queries are not allowed
C. The default temporary tablespace required to execute the query is offline.
D. The default temporary tablespace required to execute the query is dropped.
E. The default temporary tablespace required to execute the query is read-only.
Answer: A
QUESTION NO: 57
You executed the following command in Recovery Manager (RMAN):
RMAN> RESTORE CONTROLFILE;
Which operation must you perform before this command is executed?
1z0- 043
- 30 -
A. back up the control file to trace
B. bring database to the MOUNT state
C. open a connection to the RMAN recovery catalog, which contains the RMAN metadata for the target
database
D. set the database ID (DBID), but only if the DB_NAME parameter associated with the target database is
unique in the recovery catalog
Answer: C
QUESTION NO: 58
Exhibit
Using Database Control, you have scheduled a job to shrink the TRANS table residing on the TT
tablespace. The job would run at 5:00 p.m. every Friday.
When you examine the space usage of the table after the completion of the job, you find that the table has
not been shrunk.
What could have been the reason for this?
A. The tablespace that contains the TRANS table is online.
B. The tablespace that contains the TRANS table is permanent.
C. The tablespace that contains the TRANS table is locally managed.
D. The segment space management of the tablespace that contains the TRANS table is manual in nature.
Answer: D
QUESTION NO: 59
1z0- 043
- 31 -
The warning and critical threshold values have been set to 85% and 97%, respectively, for one of the
tablespaces. The current tablespace space usage is 54%. You modify the warning threshold to be 50 %
and critical threshold to be 53% in Database Control.
Which statement is true?
A. The new setting would be applied but no alerts would be raised immediately.
B. The new setting would be applied and an alert would be raised immediately.
C. The new setting would be ignored because the tablespace space usage is more than the specified
threshold value.
D. The new setting would cause an error because the tablespace space usage is more than the specified
threshold value.
Answer: A
QUESTION NO: 60
You defined the Recovery Manager (RMAN) retention policy to recovery window of 7 days by executing
the following command:
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
When a backup is performed, what would be the status of this backup after seven days?
A. The backup would be marked as invalid.
B. The backup would be marked as obsolete.
C. The backup would be removed from the media.
D. The backup would be removed from the RMAN repository.
Answer: B
QUESTION NO: 61
You work as a database administrator at TestKing.com. You are working in an online transaction
processing (OLTP) environment. You realize that the salary for an employee, John, has been accidentally
modified in the EMPLOYEES table. Two days ago, the data was in the correct state. Flashback logs
generated during last two days are available in the flash recovery area.
Which option would you choose to bring the data to the correct state while ensuring that no other data in
the same table is affected?
1z0- 043
- 32 -
A. perform point-in-time recovery
B. perform a Flashback Table operation to restore the table to the state it was in two days ago
C. perform a Flashback Database operation to restore the database to the state it was in two days ago
D. perform Flashback Versions Query and Flashback Transaction Query to determine all the necessary
undo SQL statements, and then use them for recovery
Answer: D
QUESTION NO: 62
When performing a backup using Recovery Manager (RMAN), which four types of files can be backed
up with the RMAN BACKUP command? (Choose four.)
A. data file
B. password file
C. archivelog file
D. temporary file
E. online redo log file
F. the current control file
G. the tnsnames.ora file
H. current server parameter file
Answer: A, E, F, H
QUESTION NO: 63
You were recently hired by TestKing.com as a database administrator. You are asked to find out whether
the currently functional listener process, named L1, has been password protected or not. You are not
familiar with the file system of the new organization.
How would you accomplish the task?
A. by using the STATUS command of the Listener control utility
B. by using the STATUS L1 command of the Listener control utility
C. by using the SHOW RULES command of the Listener control utility
D. by using the SET PASSWORD command of the Listener control utility
E. by searching for the PASSWORD_LISTENER entry in the listener.ora file
1z0- 043
- 33 -
Answer: B
QUESTION NO: 64
These are the details about V$FLASHBACK_DATABASE_STAT:
SQL> DESC v$FLASHBACK_DATABASE_STAT
Name Null? Type
------------- -------- --------------
BEGIN_TIME DATE
END_TIME DATE
FLASHBACK_DATA NUMBER
DB_DATA NUMBER
REDO_DATA NUMBER
ESTIMATED_FLASHBACK_SIZE NUMBER
Which two statements regarding the V$FLASHBACK_DATABASE_STAT view are true? (Choose two.)
A. BEGIN_TIME is the time at which Flashback logging is enabled.
B. END_TIME is the time at which the query is executed on the view.
C. REDO_DATA is the number of bytes of redo data written during the interval.
D. This view contains information about flashback data pertaining to the last 24 hours.
E. FLASHBACK_DATA is the amount of flashback data generated since the database was opened.
Answer: B, E
QUESTION NO: 65
You lost a temporary file that belongs to the default temporary tablespace in your database. From the
options provided, which approach would you take to solve the problem?
A. flash back the database
B. import the temporary tablespace from the last export
C. restore all the data files and temporary files from the last full database backup and perform a recovery
D. not perform a recovery, but create a new temporary tablespace, make it the default temporary tablespace
and then drop the old tablespace
1z0- 043
- 34 -
Answer: D
QUESTION NO: 66
Which two statements are correct regarding the Oracle Flashback Drop feature? (Choose two.)
A. Recycle bin exists for the tables only in non-SYSTEM, locally managed tablespaces.
B. You can flash back a dropped table provided row movement has been enabled on the table.
C. If you drop an index before dropping its associated table, then the recovery of the index is not supported
when you flash back the dropped table.
D. When you execute the DROP TABLESPACE? INCLUDING CONTENTS command, the objects in the
tablespace are places in the recycle bin.
E. When a dropped table is moved to the recycle bin, only the table is renamed to a system-generated
name; its associated objects and constraints are not renamed.
F. If you drop a table that is protected by the recycle bin, then associated bitmap-joined indexes and
materialized view logs are also stored in the recycle bin.
Answer: A, C
QUESTION NO: 67
You want to create a consumer group, GROUP1, and you execute the following command in the
command-line interface:
SQL> EXEC DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP
(CONSUMER_GROUP =>.group1., COMMENT => .New Group.);
This command errors out displaying the following message:
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at .SYS.DBMS_RMIN., line 115
ORA-06512: at SYS.DBMS_RESOURCE_MANAGER., line 108
ORA-06512: at line 1
What action would you take to overcome this error?
A. grant SYSDBA to the user
B. grant SYSOPER to the user
C. grant the RESOURCE role to the user
D. use the GRANT command to grant the ADMINISTER_RESOURCE_MANAGER privilege to the use
1z0- 043
- 35 -
E. grant the ADMINISTER_RESOURCE_MANAGER privilege to the user by using the
DBMS_RESOURCE_MANAGER_PRIVS package
Answer: E
QUESTION NO: 68
You noticed that the index tablespace in your database requires a recovery. However, instead of
performing a media recovery, you decided to re-create the indexes in a new tablespace.
Which two options would you use to reduce the time it takes to re-create the indexes? (Choose two.)
A. ONLINE
B. REVERSE
C. PARALLEL
D. COMPRESS
E. NOLOGGING
F. COMPUTE STATISTICS
Answer: C, E
QUESTION NO: 69
In the parameter file of your production database, the FAST_START_MTTR_TARGET parameter is set to
300 to optimize instance recovery. While observing the performance of the database during instance
recovery, you find that the redo log files are not sized properly to support this activity.
Which two sources could you use to determine the optimal size of the redo log files? (Choose two.)
A. the V$LOG view
B. the V$DBFILE view
C. the V$LOGFILE view
D. the V$INSTANCE_RECOVERY view
E. Oracle Enterprise Manager Database Control
Answer: D, E
1z0- 043
- 36 -
QUESTION NO: 70
Why would you use the following FLASHBACK TABLE command?
FLASHBACK TABLE emp TO TIMESTAMP (.11:45...hh12:mi.);
A. to undo the changes made to the EMP table since the specified time
B. to restore the EMP table that was wrongly dropped from the database
C. to view the transactions that have modified the EMP table since the specified time
D. to view the changes made to the EMP table for one or more rows since the specified time
Answer: A
QUESTION NO: 71
What effect should the loss of an index tablespace have on an Oracle instance that is running?
A. instance hangs
B. instance gets aborted
C. instance continues running
D. instance gets restarted automatically
Answer: C
QUESTION NO: 72
By using the transaction identifier provided by ______ for a particular row change, you can use the
Flashback Transaction Query to see the operation performed by the transaction.
A. Flashback Table
B. Flashback Database
C. Flashback Version Query
D. The RMAN REPORT command
E. The DBA_PENDING_TRANSACTIONS view
Answer: C
1z0- 043
- 37 -
QUESTION NO: 73
You work as a database administrator at TestKing.com. Manually, you set the consumer group of all of
the newly created users to MYDB_GRP. You want the users to be able to change their consumer groups as
per the application requirement.
What was the first step that was needed in the process to achieve this objective?
A. The user must have been granted the DBA role.
B. The user must have been granted the switch privilege as a part of a role.
C. The user must have been granted the Resource Manager administrator privilege.
D. The user must have been granted the switch privilege by using the
DBMS_RESOURCE_MANAGER_PRIVS package.
Answer: D
QUESTION NO: 74
While setting up the threshold for tablespace space usage metrics for one of your tablespaces, you define
the critical threshold as 60% and warning threshold as 75%. What would be the result of this setting?
A. It would result in an error because the warning threshold cannot be more than the critical threshold.
B. The warning threshold is ignored and alerts would be generated when space usage exceeds the critical
threshold.
C. The critical threshold is ignored and alerts would be generated when space usage exceeds the warning
threshold.
D. Both threshold values are used and alerts are generated when space usage exceeds the respective
threshold values.
Answer: A
QUESTION NO: 75
You have created a resource plan, DB_PLAN, using
DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN and you want to impose a restriction on
utilization of resources by the database users. Which step must be performed before you can start using
the resource plan?
A. assign users to consumer groups
B. set the resource plan for the instance
C. configure a simple resource plan and specify plan directives
1z0- 043
- 38 -
D. configure a complex resource plan and specify plan directives
Answer: A
QUESTION NO: 76
Exhibit
Viewing the Exhibit and examine the Flashback Database architecture.
Identify the missing component (shown with a .?`.= in the Flashback Database architecture.
A. DBWn
B. RVWR
C. ARCH
D. RECO
E. TRWR
Answer: B
1z0- 043
- 39 -
QUESTION NO: 77
Case 1:
1. Shut down the database and perform a backup.
2. Restore all the data files.
3. Mount the database.
4. Recover the database.
5. Without applying all the redo log files, open the database using the RESETLOGS option.
6. Back up the database.
Case 2:
1. Shut down the database and perform a backup.
2. Restore only the data files of the tablespace where user error damaged the data.
3. Mount the database.
4. Recover the database.
5. Open the database with the RESETLOGS option.
6. Back up the database.
Case 3:
1. Shut down the database.
2. Restore the data files.
3. Open the database.
4. Perform recovery to the current point in time.
Which case has the correct steps for an incomplete recovery?
A. Only case 1
B. Only case 2
C. Only case 3
D. Case 1 and 2
E. Case 2 and 3
F. Case 1 and 3
G. All cases, case 1, case 2, case 3
Answer: A
QUESTION NO: 78
You work as a database administrator at TestKing.com. You observe that the senior database
administrator always uses the following command when connecting to Recovery Manager (RMAN), and
then starts executing commands to backup the database files:
1z0- 043
- 40 -
% rman target /
What would be the effect of this command?
A. RMAN would use the current operating system location to place the backup.
B. RMAN would use the control file of the target database to maintain the backup.
C. RMAN would use the recovery catalog of the target database to maintain the backup.
D. RMAN would use the control file of the target database to maintain the backup metadata.
E. RMAN would use the recovery catalog of the target database to maintain the backup metadata.
Answer: D
QUESTION NO: 79
You disabled the Flashback Database feature by using the following command:
SQL> ALTER DATABASE FLASHBACK OFF;
What would be the effect of this command on the existing flashback logs?
A. Flashback logs are not deleted.
B. Flashback logs are deleted automatically.
C. Flashback logs are deleted only if you are using Recovery Manager (RMAN)
D. Flashback logs are deleted only if you are using Oracle Manager Files (OFM)
E. Flashback logs are deleted only if you are using Automatic Storage Management (ASM)
Answer: B
QUESTION NO: 80
You work as a database administrator at TestKing.com. The production database has been functional for
the last seven days. Because of application requirements, some of the initialization parameters were
changed during run time without any comments. You have asked to find out the value of the parameters
when the instance was started.
Which source would you use to locate this information?
A. Fixed views
B. The alert log file
1z0- 043
- 41 -
C. The parameter file
D. The server parameter file
E. Dynamic performance views
Answer: B
QUESTION NO: 81
You have a disk group, DGROUP1, with three disk and NORMAL redundancy. You execute the following
command to create a template for the disk group:
ALTER DISKGROUP dgroup1
ADD TEMPLATE my_temp
ATTRIBUTES (MIRROR FINE);
Which statement is true?
A. When a file is created in DGROUP1 with the template, it would have three-way mirroring.
B. When a file is created in DGROUP1, the MY_TEMP template becomes the default template.
C. When a file is created in DGROUP1 with the template, it would have two-way mirroring and file striping.
D. When a file is created in DGROUP1 with the template, it would have three-way mirroring and file
striping.
E. When a file is created in DGROUP1 with the template, it would have two-way mirroring but no file
striping.
Answer: C
QUESTION NO: 82
You lost the index tablespace in your database. You are not able to use tablespace point-in-time recovery
on the index tablespace. What could be the reason for this?
A. The index tablespace contains bitmap indexes.
B. The index tablespace contains more than one data file.
C. The index tablespace supports only complete recovery.
D. The index tablespace is not a dictionary-managed tablespace.
E. There is a dependency relationship between a table and its indexes.
Answer: E
1z0- 043
- 42 -
QUESTION NO: 83
For the V$SESSION_LONGOPS view, you find that some of the database users have long-running queries
that consume a lot of CPU time. This causes performance problems for other users in the database, who
have much shorter queries.
You would like to make sure that the users with large queries do not use all the CPU time, but you still do
not want to terminate them with an error message.
Which method would you follow to achieve this?
A. Set the CPU time per call in the users profile.
B. Set the CPU time per session in the users profile.
C. Set the CPU levels for the user.s group using Resource Manager.
D. Set the TIMED_STATISTICS parameter to TRUE in the parameter file.
Answer: C
QUESTION NO: 84
Exhibit:
View the Exhibit and examine the characteristic of the USERS tablespace.
You observe that a large volume of inserts and deletes are happening on the TRANS table in the USERS
tablespace and you suspect that the TRANS table is fragmented.
Which advisory component would you refer to, in order to find information about table fragmentation?
1z0- 043
- 43 -
A. Memory Advisor
B. Segment Advisor
C. SLQ Tuning Advisor
D. SLQ Access Advisor
E. Automatic Database Diagnostic Monitor (ADDM)
Answer: B
QUESTION NO: 85
A data file become corrupted in your database due to bad sectors on the disk. Because of corruption, you
lost all the important tables in that data file.
Which method would you use for recovery?
A. Flash back all the tables in the data file, one by one.
B. Restore the data file to a new location and perform a media recovery.
C. Flash back the database, there is no need to restore the data file.
D. Restore the data file from the most recent backup and flash the database.
Answer: B
QUESTION NO: 86
For which two SQL statements can you use the Flashback Table feature to revert a table to its previous
state? (Choose two)
A. UPDATE TABLE
B. CREATE CLUSTER
C. TRUNCATE TABLE
D. ALTER TABLE MOVE
E. INSERT INTO...VALUES
F. ALTER TABLE...DROP COLUMN
G. ALTER TABLE...DROP PARTITION
Answer: A, E
1z0- 043
- 44 -
QUESTION NO: 87
What purpose would you achieve by enabling the block change tracking feature?
A. Eliminate the necessity for backups.
B. Perform optimized image copy backups.
C. Perform optimized incremental backups.
D. Enable checkpoint (CKPT) to perform checkpointing at every block change.
E. Enable database writer (DBWn) to write changed blocks to data files faster.
Answer: C
QUESTION NO: 88
You execute the following RMAN command in the order shown below:
BACKUP VALIDATE DATABASE;
BLOCKRECOVER CORRUPTION LIST;
What will these commands do?
A. Create a backup of the database and recover all corrupted blocks found in the backup.
B. Run a backup validation and list all the logically corrupt blocks as well as physically corrupt blocks in
the database.
C. Run a backup validation to populate V$COPY_CORRUPTION view, and then list any corrupt blocks
recorded in the view.
D. Run a backup validation to populate V$DATABASE_BLOCK_CORRUPTION view, and then repair any
corrupt blocks recorded in the view.
E. Run a backup validation, repair any corrupt blocks found during the validation process, and then update
V$DATABASE_BLOCK_CORRUPTION view to indicate which corrupt blocks have been repaired.
Answer: D
QUESTION NO: 89
In Recovery Manager (RMAN), you have set control file autobackup to ON by using the following
command:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
1z0- 043
- 45 -
Which two events would cause the control file to be backed up automatically? (Choose two)
A. A tablespace is taken offline.
B. The database instance is restarted.
C. A new data file is added to an existing tablespace.
D. A successful backup is recorded in the RMAN repository.
E. The RMAN connection is disconnected from the target database.
Answer: D, E
QUESTION NO: 90
You are working on an Oracle Database 10g database. For which purposes would you use the Flashback
Transaction Query feature? (Choose all that apply)
A. To recover a dropped table.
B. To recover a dropped schema.
C. To recover data from a truncated table.
D. To view changes made by all the transactions during a given period of time.
E. To view changes made by a single transaction during a given period of time.
Answer: A, C, E
QUESTION NO: 91
Consider the following command to add a new disk group called .tdgroupA. with two failover groups:
CREATE DISKGROUP tdgroupA NORMAL REDUNDANCY
FAILOVERGROUP control01 DISK
./devices/A1.,
./devices/A2.,
./devices/A3.
FAILOVERGROUP control02 DISK
./devices/B1.,
./devices/B2.,
./devices/B3.m
The disk ./devices/A1. is currently a member disk of a disk group by the name .tdgroup1..
Which task would be accomplished by the command?
1z0- 043
- 46 -
A. This command would result in an error because a disk group can have only one failover group.
B. This command would result in an error because the /devices/A1 disk is a member of another disk
group tdgroup1.
C. A new disk group called tdgroupA will be added with two failover groups and the /devices/A1
disk will get reattached to the new disk group without being detached from the existing one.
D. A new disk group called tdgroupA will be added with two failover groups and the /devices/A1
disk will be ignored for the new disk group because it is a member of an existing disk group
tdgroup1.
E. A new disk group called tdgroupA will be added with two failover groups and the /devices/A1
disk gets detached from the existing disk group tdgroup1 and attached to the new disk group
tdgroupA.
Answer: B
QUESTION NO: 92
You work as a database administrator at TestKing.com. Your business has departmental reports that are
generated every day. Each department must use the same set of queries, but access a different subset of
data in the tables, depending on which department generates the report. The format of the reports is
being developed; currently, the format changes daily.
How would you configure the database to ensure that each department generates its report (based on its
target data) using the most recent report format every day?
A. By having each user run the report generation procedure at the scheduled time, supplying the necessary
input variables.
B. By creating a program using DBMS_JOB that accepts one or more variables, and creating a job that
calls this program using DBMS_JOB.
C. By having each user schedule a job using DBMS_JOB that accepts one or more input variables and calls
a procedure that generates the report.
D. By having each user create a job using DBMS_SCHEDULER that includes all the information and
commands necessary to generate the report.
E. By creating a program using DBMS_SCHEDULER that accepts one or more variables, and creating a job
that calls this program using DBMS_SCHEDULER.
Answer: E
1z0- 043
- 47 -
QUESTION NO: 93
You execute the following command to create two consumer groups, FIN_GROUP1 and PAY_GROUP1 for
a plan, PROD_PLAN:
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN (SIMPLE_PLAN => .prod_plan.,
CONSUMER_GROUP1 => .fin_group1.,
GROUP1_CPU => 80,
CONSUMER_GROUP2 => .pay_group1.,
GROUP2_CPU => 20);
END;
Which three statements are true? (Choose three)
A. SYS_GROUP gets 100% CPU resources at level 1.
B. FIN_GROUP1 gets 80% CPU resources at level 1.
C. FIN_GROUP1 gets 80% CPU resources at level 2.
D. PAY_GROUP1 gets 20% CPU resources at level 1.
E. PAY_GROUP1 gets 20% CPU resources at level 2.
F. OTHER_GROUP gets 100% CPU resources at level 1.
G. OTHER_GROUP gets 100% CPU resources at level 2.
Answer: B, D, F
QUESTION NO: 92
You have configured Automatic Shared Memory Management. Which four memory structures would be
automatically tuned? (Choose four)
A. log buffer
B. Java pool
C. Large pool
D. Fixed SGA
E. Shared pool
F. Streams pool
G. Keep buffer cache
H. Database buffer cache
Answer: A, B, C, H
1z0- 043
- 48 -
QUESTION NO: 93
The DB_BLOCK_CHECKING initialization parameter is set to FALSE. What level of block checking would
be performed?
A. The Oracle database will not perform block checking for any of the data blocks.
B. The Oracle database will perform block checking for the default permanent tablespace only.
C. The Oracle database will perform block checking for the data blocks in all user tablespaces.
D. The Oracle database will perform block checking for the data blocks in the SYSTEM tablespace only.
E. The Oracle database will perform block checking for the data blocks in the SYSTEM and SYSAUX
tablespaces.
Answer: D
QUESTION NO: 94
Which statement is true regarding the creation of nested plans using Resource Manager?
A. Only one nested subplan is allowed.
B. The plans can be nested up to four levels.
C. Resource Manager does not support nested plans.
D. Nested plans control only the degree of parallelism but not the CPU.
E. Each nested plan gets a proportion of the CPU resources assigned to its parent group.
Answer: E
QUESTION NO: 95
You want your listener to be password protected to prevent it from being shut down. You want to
accomplish this task while the listener is functional. Which method could you use to achieve this
objective?
A. Use the CHANGE_PASSWORD command of the Listener control utility.
B. Use the SET PASSWORD and SAVE_CONFIG commands of the Listener control utility.
C. Use the CHANGE_PASSWORD and SAVE_CONFIG commands of the Listener control utility.
D. Manually modify the listener.ora file to include the password for the listener and restart the
listener.
Answer: C
1z0- 043
- 49 -
QUESTION NO: 96
You need to check the EMP_EAST partition in the EMPLOYEES table for physical corruptions. You also
need to verify that the rows belong to the correct partition. Which option could you use?
A. LogMiner
B. The DBNEWID utility
C. The DBVERIFY utility
D. The ANALYZE command
E. The RMAN REPORT command
F. The RMAN CROSSCHECK command.
G. The RMAN BLOCKRECOVER command.
Answer: D
QUESTION NO: 97
Users in your production database complain that they are getting the following error message while
trying to insert rows into the ORDERS table:
ERROR at line 1:
ORA-01654: unable to extend index USERS.ORDERS_IND by 8 in tablespace
INDEXES
While investigating, you find that the INDEXES tablespace has run out of space and there is no more free
space on the disk where the data files are available.
Which two actions could you perform to overcome this error without affecting the queries that are
currently being executed? (Choose two)
A. Drop and re-create the inbox.
B. Coalesce the ORDERS_IND index.
C. Coalesce the INDEXES tablespace.
D. Drop and re-create the ORDERS table.
E. Rebuild the index online and move it to another tablespace.
Answer: B, E
1z0- 043
- 50 -
QUESTION NO: 98
Consider the following scenario:
You have a directory, data, under the disk group tdgroupA. You want to create an alias for one of the
data files and you execute the following command:
ALTER DISKGROUP tdgroupA
ADD ALIAS .+tdgroupA/data/datafile.dbf.
FOR .+tdgroupA.231.45678.;
Which task would be accomplished by the command?
A. The command drops the file +tdgroupA.231.45678.
B. The command physically relocated the file to +tdgroupA/data and renames the file to
datafile.dbf.
C. The command creates a copy of the +tdgroupA.231.45678 file and places it in
+tdgroupA/data after renaming the file to datafile.dbf.
D. The command creates an alias, datafile.dbf, and places it in +tdgroupA/data and does not
remove the tdgroupA 231.45678 file.
E. The command creates a file, datafile.dbf, in +tdgroupA/data and removed the references for
+tdgroupA.231.45678 from the data dictionary views.
Answer: E
QUESTION NO: 99
You lost a data file that belongs to an index tablespace in your database, which operates in ARCHIVELOG
mode. Loss of the data file resulted in increased response time on your queries.
Which two options would you use to solve this problem? (Choose two)
A. Restore the lost data file from the backup, and then flash back the database.
B. Restore the data file pertaining to index tablespace, and then recover the tablespace.
C. Restore all the data files, and then perform an incomplete recovery to get the tablespace back.
D. Restore all the data files, and then perform an incomplete recovery using the backup control file.
E. Drop and re-create the index tablespace, and then re-create all of the indexes in that tablespace.
Answer: B, E
1z0- 043
- 51 -
QUESTION NO: 100
You work as a database administrator at TestKing.com. You database operates in ARCHIVELOG mode
and user-managed consistent backups are performed every Sunday night. On Tuesday, at 9:00 a.m. the
current log sequence number was 369. Also, on Tuesday you lost the data file belonging to the SYSTEM
tablespace and an achivelog file (sequence number 356) that contained redo entries between 8:00 a.m.
and 8.30 a.m.
With reference to this scenario, what would you do to recover the database?
A. Restore all the data files from last Sunday.s backup, and then perform a time-based recovery.
B. Restore all the data files from last Sunday.s backup, and then perform a cancel-based recovery.
C. Restore all the data files from last Sunday.s backup, and then perform a change-based recovery.
D. Restore only data files that belong to the SYSTEM tablespace from last Sunday.s backup, and then
perform a complete recovery.
Answer: B
QUESTION NO: 101
You executed the following query in your database:
SELECT oldest_flashback_scn, oldest_flashback_time
FROM V$FLASHBACK_DATABASE_LOG;
What would you determine from the output?
A. The time when the last flashback operation in your database was performed.
B. The time when the first flashback operation in your database was performed.
C. A list of flashback operations performed in your database using SCN and time.
D. The approximate time and the lowest system change number (SCN) to which you can flash back your
database.
Answer: D
QUESTION NO: 102
1z0- 043
- 52 -
While designing the database for one of your online transaction processing (OLTP) applications, you
want to achieve the following:
a) high availability of data
b) faster primary key access to the table data
c) compact storage for the table
Which type of tables would you use to achieve these objectives?
A. heap tables
B. object tables
C. partitioned tables
D. index-organized tables (IOTs)
Answer: D
QUESTION NO: 103
You are using Oracle Database 10g. The log LOG_ARCHIVE_FORMAT parameter is set to
.LOG%t_%_s_%r.dbf..
Why is %r used in the file name format?
A. To uniquely identify the archived log files with the restore operation.
B. To uniquely identify the archived log files with the redo log group number.
C. To uniquely identify the archived log files for each incarnation of the database.
D. To uniquely identify the archived log files with the number of recovery operations performed.
Answer: C
QUESTION NO: 104
Exhibit:
1z0- 043
- 53 -
Exhibit:
1z0- 043
- 54 -
View the Exhibits.
You performed operations on the DEPT4 table as shown in the Exhibit. When you perform the Flashback
Versions Query, you find that the first two updates are not listed.
What could be the reason?
A. The row movement is not enabled on the table.
B. The first two updates were not explicitly committed.
C. The Flashback Versions Query lists only the most recent update.
D. The Flashback Versions Query stops producing rows after it encounters a time in the past when the
table structure was changed.
Answer: D
QUESTION NO: 105
1z0- 043
- 55 -
Exhibit:
View the Exhibit.
You have more than one table in the recycle bin having the same original name, DEPT2. You do not have
any table with the nameDEPT2 in your schema. You executed the following command:
PURGE TABLE dept2;
Which statement is correct in this scenario?
A. All the tables having the same original name as DEPT2 will be purged from the recycle bin.
B. The table with dropscn = 1928151 (oldest dropscn) will be purged from the recycle bin.
C. The table with dropscn = 1937123 (most recent dropscn) will be purged from the recycle bin.
D. None of the tables will be purged because there are multiple entries with the same original name in the
recycle bin.
Answer: B
QUESTION NO: 106
Which type of PL/SQL construct would you use to automatically correct the error resulting from a
statement that was suspended due to a space-related problem?
A. functions
B. package
C. procedure
D. database trigger
E. anonymous PL/SQL block
Answer: D
1z0- 043
- 56 -
QUESTION NO: 107
In an Oracle 10g database, the in-memory statistics are gathered at regular intervals and used to perform
growth-trend analysis and capacity planning of the database. Which component stores these statistics?
A. Recovery catalog
B. Oracle Enterprise Manager Repository
C. Automatic Workload Repository (AWR)
D. Oracle 10g Enterprise Manager Grid Control
E. Automatic Database Diagnostic Monitor (ADDM)
Answer: C
QUESTION NO: 108
You have been assigned to manage a set of databases. The previous DBA did not leave you notes
regarding the structure of each of the databases. While analyzing an instance, you notice that the system
identifier (SID) for the instance is set to .+ASM..
What is the purpose of this instance?
A. This instance is being used to manage the operating system files.
B. This instance is being used to manage the files of other databases.
C. This instance is being used to manage the instances of other databases.
D. This instance is being used to manage the background processes of other instances.
Answer: B
QUESTION NO: 109
In one of your online transaction processing (OLTP) applications, most users frequently modify the
values, including the key values, of the application tables. Some users generate application reports by
using multiple application tables.
What is the best table structure that you can use to gain optimal performance?
A. Heap table
B. Object table
C. External table
D. Clustered table
1z0- 043
- 57 -
E. Global temporary table
F. Index-organized table (IOT)
Answer: A
QUESTION NO: 110
Your database is functioning in NOARCHIVELOG mode. Your database contains 15 tablespaces. You want
to use Recovery Manager (RMAN) to perform backups.
Which two backups would you be able to perform when the database is being accessed by users? (Choose
two)
A. Backup of offline tablespaces.
B. Backup of read-only tablespaces.
C. Backup of system-critical tablespaces.
D. Backup of online, locally managed tablespaces.
E. Backup of read/write dictionary-managed tablespaces.
Answer: A, B
QUESTION NO: 111
You work as a database administrator at TestKing.com. You are working in an online transaction
processing (OLTP) environment. You used the FLASHBACK TABLE command to flash back the
CUSTOMERS table. Before executing the FLASHBACK TABLE command, the System Change Number
(SCN) was 663571. After flashing back the CUSTOMERS table, you realize that the table is not in the
correct state and the resultant changes are not what you had desired. So, you need to reverse the effects
of the FLASHBACK TABLE command while ensuring that:
a) No other user data in the database is affected.
b) The operation takes the minimum possible time
Which option would you choose?
A. Use ROLLBACK command with SCN 663571.
B. Perform Flashback Transaction Query with SCN 663571.
C. Execute the FLASHBACK DATABASE statement to retrieve the CUSTOMERS table as it was at SCN
663571.
1z0- 043
- 58 -
D. Execute another FLASHBACK TABLE statement to retrieve the CUSTOMERS table as it was at SCN
663571.
Answer: D
QUESTION NO: 112
Because of hardware failure, you decided to drop a redo log member from the database.
Which condition should be met to drop a redo log file?
A. The redo log file should belong to an active group.
B. The redo log file should belong to an inactive group.
C. The redo log file should belong to the current group.
D. The redo log file can be dropped only if all the transactions are stopped.
E. Before a redo log file is dropped, it should be deleted from the operating system (OS).
Answer: B
QUESTION NO: 113
In your production database, you have multiple resource plan directives that refer to the customer group
DB_GRP. Which two statements are correct? (Choose two)
A. The parallel degree limit for DB_GRP will be the minimum of all the incoming values.
B. The parallel degree limit for DB_GRP will be the maximum of all the incoming values.
C. The maximum estimated execution time for DB_GRP would be the most nonrestrictive of all incoming
values.
D. If a session switched from the DB_GRP consumer group to another consumer group because it exceeded
the prescribed switch time, then that session would not execute if the active session pool for the new
consumer group was full.
E. Is a session switched from the DB_GRP consumer group to another consumer group because it exceeded
the prescribed switch time, then that session would execute even if the active session pool for the new
consumer group was full.
Answer: A, E
1z0- 043
- 59 -
QUESTION NO: 114
You work as a database administrator at TestKing.com. By mistake, you ran the batch job (for updating
the BILL_DETAILS table) twice. You are not sure which rows in the BILL_DETAILS table were
affected. You need to identify:
a) A list of changes made along with the transaction identifier of each change.
b) The necessary SQL statements to undo the erroneous changes.
Which option would you choose?
A. RMAN only.
B. Flashback Table only.
C. Flashback Version Query only.
D. Flashback Database and Flashback Transaction Query.
E. Flashback Version Query and Flashback Transaction Query.
Answer: E
QUESTION NO: 115
You used the following command in Recovery Manager (RMAN) as part of the recovery process:
RESTORE CONTROLFILE FROM AUTOBACKUP;
How does RMAN find the control file autobackup? (Choose all the apply)
A. By using the trace file.
B. By using the alert log file.
C. By using the database ID.
D. By using the server parameter file.
E. By using the V$CONTROLFILE view.
F. By using the autobackup format configuration setting.
Answer: C, D
QUESTION NO: 116
In one of your online transaction processing (OLTP) applications, users are manipulating and querying a
database table simultaneously. From the Segment Advisor, you find that one of the tables is highly
fragmented and you want to shrink the table immediately without affecting the currently active queries.
1z0- 043
- 60 -
Which option would you use with the ALTER TABLE command to achieve this objective?
A. REBUILD
B. CASCADE
C. TRUNCATE
D. ROW MOVEMENT
E. SHRINK SPACE COMPACT
F. SHRINK SPACE CASCADE
Answer: E
QUESTION NO: 117
You work as a database administrator at TestKing.com. You are working in a dedicated server
environment. Your database is running in the automatic Program Global Area (PGA) memory
management mode. Which two statements are correct in this scenario? (Choose two)
A. The WORK_AREASIZE_POLICY initialization parameter cannot be set to AUTO.
B. The Oracle database automatically controls the amount of PGA memory allotted to SQL work areas.
C. Setting the value of the SGA_TARGET initialization parameter to 0 will disable the automatic PGA
memory management.
D. The SORT_AREA_SIZE parameter is ignored by all the sessions running in the automatic PGA
memory management mode.
Answer: B, D
QUESTION NO: 118
Users are performing a large volume of inserts and deletes on the application tables in the APPS
tablespace. You observe that there are several warning alerts being generated for the APPS tablespace
space usage metrics. Currently, the warning threshold for the tablespace usage metrics is set to 70%.
To make the generated alerts more useful as a problem identification tool, you want to reduce the
frequency of alert generation for the tablespace usage metrics for the APPS tablespace.
What should you do?
A. Disable SQL tracing for the APPS tablespace.
B. Disable logging attributes for the APPS tablespace.
1z0- 043
- 61 -
C. Modify the tablespace to be a dictionary-managed tablespace.
D. Increase the critical threshold value for the tablespace space usage metrics for the APPS tablespace.
E. Increase the warning threshold value for the tablespace space usage metric for the APPS tablespace.
Answer: E
QUESTION NO: 119
Using Oracle Scheduler you have scheduled two jobs, JOB_A and JOB_B, to run at 9:00 p.m. every
Friday. You want both the jobs to use a single resource plan, WEEKEND_PLAN.
Which task must have already been performed to enable you to achieve this objective?
A. A window must have been created with the WEEKEND_PLAN resource plan.
B. A program must have been created with the WEEKEND_PLAN resource plan.
C. A job class must have been created with the WEEKEND_PLAN resource plan.
D. A windows group must have been created with the WEEKEND_PLAN resource plan.
Answer: A
QUESTION NO: 120
What are the two advantages of RMAN Block Media Recovery (BMR) over file-level recovery? (Choose
two)
A. BMR lowers the mean time to recover (MTTR).
B. BMR supports point-in-time recovery of individual data blocks.
C. BMR enables you to use incremental backups for block recovery.
D. BMR enables recovery even when the database is not mounted or open.
E. BMR enables you to use proxy backups to perform block media recovery.
F. BMR enables increased availability of data during recovery because the data file requires a recovery can
remain online.
Answer: D, E
QUESTION NO: 121
1z0- 043
- 62 -
While creating a scheduler window using the DBMS_SCHEDULER package, a user logged in as DBMGR
gets the error message insufficient privileges?
Which two commands would you use to resolve this error? (Choose two)
A. GRANT dba TO dbmgr;
B. GRANT connect TO dbmgr;
C. GRANT resource TO dbmgr;
D. GRANT hs_admin_role TO dmbgr;
E. GRANT manage scheduler TO dbmgr;
Answer: A, E
QUESTION NO: 122
You have set the value of the NLS_TIMESTAMP_TZ_FORMAT parameter in the parameter file to YYYYMM-
DD. The default format of which two data types would be affected by this setting? (Choose two)
A. DATE
B. TIMESTAMP
C. INTERVAL YEAR TO MONTH
D. INTERVAL DAY TO SECOND
E. TIMESTAMP WITH LOCAL TIME ZONE
Answer: B, E
QUESTION NO: 123
For an incomplete recovery, which four backup types can be used by Recovery Manager (RMAN) to
restore data files? (Choose four)
A. RMAN image copies.
B. RMAN database backups.
C. RMAN tablespace backups.
D. User-managed backups placed in the flash recovery area.
E. User-managed backups that have been cataloged with RMAN.
F. User-managed data file backups for which the pull path name is specified.
1z0- 043
- 63 -
Answer: B, C, D, F
QUESTION NO: 124
You are using Oracle Database 10g. You performed an incomplete recovery of your database and opened
the database with the RESETLOGS option.
What is the effect of opening the database with the RESETLOGS option? (Choose two)
A. This operation resets the SCN for the database.
B. This operation creates a new incarnation of the database.
C. This operation moves all the redo log files to a different location.
D. This operation deletes the old redo log files and creates new redo log files.
E. This operation updates all current datafiles and online redo logs and all subsequent archived redo logs
with a new RESETLOGS SCN and time stamp.
Answer: B, E
QUESTION NO: 125
Which statement is true while creating subplans using Resource Manager?
A. The subplan must belong to the consumer group, SYS_GROUP.
B. The total CPU usage at any given level must be less than 100%.
C. The subplans can be used to restrict the degree of parallelism for a user.
D. You can use a resource plan instead of a consumer group to implement a priority ranking within the
plan.
Answer: A
QUESTION NO: 126
Your database is functioning in ARCHIVELOG mode. In which two situations would you perform a
cancel-based recovery? (Choose two)
A. You find that one of the redo log members in each redo log group is lost.
B. You find that a data file that belongs to the USERS tablespace is damaged.
C. You find that a data file that belongs to the SYSTEM tablespace is damaged.
D. You find that the current redo log group is damaged and is not available for recovery.
1z0- 043
- 64 -
E. You realized while performing a recovery that an archived redo log file needed for recovery is lost.
Answer: D, E
QUESTION NO: 127
You work as a database administrator at TestKing.com. In your test database, you have created the
ORDERS table as an index-organized table (IOT). To facilitate faster querying, you have created a
mapping table and a bitmap index on the ORDER_FILLED column. You observe that the query
performance degrees when users perform a large volume of transactions.
While investigating the reason, you find that the mapping table segment is fragmented, leading to poor
performance. Which option would you use to defragment the mapping table without affecting the
original table data?
A. Export and import the mapping table.
B. Drop and re-create the mapping table.
C. Truncate the mapping table and reinsert the values.
D. Use the ALTER TABLE .. REBUILD command to defragment the mapping table.
Answer: A
Oracle Database 10g: Administration II
QUESTION NO: 1
You observe that a database performance has degraded over a period of time. While investigating the
reason, you find that the size of the database buffer cache is not large enough to cache all the needed data
blocks.
Which advisory component would you refer to, in order to determine that required size of the database
buffer cache?
A. Memory Advisor
B. Segment Advisor
C. SQL Tuning Advisor
D. SQL Access Advisor
E. Automatic Database Diagnostic Monitor (ADDM)
Answer: A
QUESTION NO: 2
Exhibit
1z0- 043
- 4 -
In your production database, the total waits and the time waited for log file parallel write are
significantly high. While investigating the reason, you find that there are three redo log groups with two
members in each group, and all redo log members are places on a single physical disk.
What action would you take to minimize the waits?
A. start the log writer slave processes
B. increase the number of redo log files
C. increase the size of the redo log buffer
D. place the redo log files on the different disks
E. increase the number of log writer processes
Answer: D
QUESTION NO: 3
One of the tablespaces is read-only in your database. The loss of all control files forced you to re-create
the control file.
Which operation do you need to perform after re-creating the control file and opening the database?
A. drop and re-create the read-only tablespaces
B. rename the read-only data files to their correct file names
C. change the tablespace status from read/write to read-only
D. re-create the read-only tablespace because it is automatically removed
Answer: B
QUESTION NO: 4
You have set some of the initialization parameters as:
DB_BLOCK_SIZE = 8KB
SGA_MAX_SIZE = 2GB
SGA_TARGET = 0
SHARED_POOL_SIZE = 120MB
DB_CACHE_SIZE = 896MB
STREAM_POOL_SIZE = 0
LARGE_POOL_SIZE = 110 MB
1z0- 043
- 5 -
Which two statements are correct? (Choose two.)
A. You cannot set a value for the DB_8K_CACHE_SIZE parameter.
B. If you increase the size of the large pool to 120 MB, then the memory allocated to the shared poll will be
reduced to 110 MB.
C. If the value for SGA_TARGET is changed to 1 GB and SHARED_POOL_SIZE is set to 120 MB, then
memory cannot be taken from the shared pool, even if the shared pool has free space available.
D. If an application attempts to allocate more than 120 MB from the shared pool and free space is available
in the buffer pool, then the free space from the buffer pool is allocated to the shared pool.
Answer: A, C
QUESTION NO: 5
You are performing a block media recovery on the tools01.dbf data file in the SALES database
using RMAN. Which two statements are correct in this scenario? (Choose two.)
A. You must ensure that the SALES database is mounted or open.
B. You must restore a backup control file to perform a block media recovery.
C. You must take the tools01.dbf data file offline before you start a block media recovery.
D. You must put the database in NOARCHIVELOG mode to perform a block media recovery.
E. You can perform only a complete media recovery of individual blocks, point-in-time recovery of
individual data blocks is not supported.
Answer: A, E
QUESTION NO: 6
Exhibit
1z0- 043
- 6 -
You executed the following command to perform a backup of the USERS tablespace:
RMAN > BACKUP TABLESPACE USERS;
Which type of backup would this command perform?
A. backup set
B. image copy
C. incremental backup
D. None; the user receives an error indicating that the backup type must be specified.
Answer: A
QUESTION NO: 7
In your database, online redo log files are multiplexed and one of the members in a group is lost due to
media failure?
How would you recover the lost redo log member?
A. import the database from the last export
B. restore all the members in the group from the last backup
C. drop the lost member from the database and then add a new member to the group
D. restore all the database files from the backup and then perform a complete recovery
E. restore all the database files from the backup and then perform an incomplete recovery
Answer: C
1z0- 043
- 7 -
QUESTION NO: 8
You are using Oracle Database 10g. Which statement regarding an incomplete recovery is true?
A. You do not need to restore all the data files.
B. You do not need to open the database with the RESETLOGS operation
C. You do not need to perform a full backup after the RESETLOGS operation.
D. You do not need to recover all the data files to the same system change number (SCN).
Answer: C
QUESTION NO: 9
Exhibit
View the Exhibit and examine the Resource Manager settings for the groups at different levels.
Which two effects would be the result of this setting? (Choose two.)
A. The members of LOW_GROUP would get more priority than those of OTHER_GROUPS.
B. The members of SYS_GROUP would get most of the CPU allocation at level 1.
C. The members of LOW_GROUP would get most of the CPU allocation because it has CPU allocation at
two different levels.
D. The members of LOW_GROUP would get no CPU allocation at level1 if the members of
OTHER_GROUPS are using all the CPU at level 2.
E. The members of SYS_GROUP would get no CPU allocation at level 1 if the members of
OTHER_GROUPS are using all the CPU at level 2.
1z0- 043
- 8 -
Answer: A, B
QUESTION NO: 10
The current time zone for one of the user sessions is set to the database local time zone. For one
application, the user session requires the time zone to be set to the local operating system time zone
without affecting other user sessions.
Which two solutions could the user implement to achieve this objective? (Choose two.)
A. use the ALTER SYSTEM command to change the time zone
B. use the ALTER SESSION command to change the time zone
C. use the ALTER DATABASE command to change the time zone
D. set the value for the operating system variable ORA_SDTZ on the client machine
E. set the value for the operating system variable ORA_SDTZ on the database server machine
Answer: B, D
QUESTION NO: 11
You work as a database administrator at TestKing.com. Your database is open and running in
ARCHIVELOG mode. You take RMAN full backups every Sunday night. On Monday morning, while
querying the user1.employees table, you receive the following error message:
01578: ORACLE data block corrupted (file # 5, block # 51) ORA-01110: data
file 5:
./u01/app/oracle/oradata/orcl/example01.dbf.
You need to rectify the corruption while ensuring the following:
The data file should remain online.
The mean time to recover (MTTR) should be minimal.
You are not using a backup control file and all the archived logs are accessible. Which option would you
choose?
A. flash back the corrupted blocks
B. use the DBMS_REPAIR package
C. use the RMAN TSPITR command
D. use the RMAN BLOCKRECOVER command
1z0- 043
- 9 -
E. use the RESTORE DATABASE and RECOVER DATABASE commands
F. investigate the time at which the corruption occurred and perform a point-in-time recovery
Answer: D
QUESTION NO: 12
You are working on an Oracle Database 10g database. You enabled the Flashback Database feature.
Which two statements regarding flashback logs are true? (Choose two.)
A. Flashback logs are not archived.
B. Flashback logs are maintained in redo log files.
C. Flashback logs are maintained in the Flash Recovery Area.
D. Flashback logs are used to maintain Flashback Database related errors.
E. Flashback logs need to be cleared manually after you disable Flashback Database.
Answer: A, C
QUESTION NO: 13
You executed the following query:
SELECT operation, undo_sql, table_name
FROM flashback_transaction_query;
Which statement is correct regarding the query output?
A. It would return information regarding only the last committed transaction.
B. It would return only the active transactions in all the undo segments in the database.
C. It would return only the committed transactions in all the undo segments in the database.
D. It would return both active and committed transactions in all the undo segments in the database.
E. It would return information regarding the transactions that began and were committed in the last 30
minutes.
Answer: D
QUESTION NO: 14
1z0- 043
- 10 -
Consider the following configuration:
/devices/D1 is a member of disk group dgroupA.
/devices/D2 is a member of disk group dgroupA.
/devices/D3 is a member of disk group dgroupA.
You plan to add a new disk, /devices/D4, to the disk group dgroupA.
You execute the following command:
SQL> ALTER DISKGROUP dgroupA ADD DISK ./devices/D*.;
Which task would be accomplished by the command?
A. The command adds the new disk, D4, to the disk group.
B. The command would result in an error because the is no disk by the name ../devices/D*..
C. The command will be ignored because disks starting with .D. are already members of the disk group.
D. The command would result in an error because no wildcard characters can be used in the disk name.
E. The command first detaches all the member disks starting with .D., and the reattaches them including
the new disk.
Answer: A
QUESTION NO: 15
You work as a database administrator at TestKing.com. In your production database there is a job,
CALC_STAT, which has been scheduled to run every Friday at 5.00 p.m. CALC_STAT updates the
optimizer statistics for the objects owned by the APPS schema. You want the task to be generic, thereby
allowing users to modify the attributes of the task at run time without affecting the original task.
Which component of Oracle Scheduler must you define to achieve this?
A. Window
B. Program
C. Job class
D. Window group
Answer: B
QUESTION NO: 16
1z0- 043
- 11 -
Immediately after adding a new disk to or removing an existing disk from an Automatic Storage
Management (ASM) instance, you find that the performance of the database decreases initially, until the
addition or removal process is completed. Performance then gradually returns to normal levels.
Which two activities could you perform to maintain a consistent performance of the database while
adding or removing disks? (Choose two.)
A. increase the number of checkpoint processes
B. define the POWER option while adding or removing the disks
C. increase the number of DBWR processes by setting up a higher value for DB_WRITER_PROCESSES
D. increase the number of slave database writer processes by setting up a higher value for
DBWR_IO_SLAVES
E. increase the number of ASM Rebalance processes by setting up a higher value for
ASM_POWER_LIMIT during the disk addition or removal
Answer: B, E
QUESTION NO: 17
The current time is 12:00 noon. You want to recover the USERS tablespace from a failure that occurred
at 11:50 a.m. You discover that the only member of an unarchived redo log group containing information
from 11:40 a.m. onwards is corrupt.
With reference to this scenario, if you are not using Recovery Manager (RMAN), which recovery method
would you use?
A. time-based recovery
B. log sequence recovery
C. cancel-based recovery
D. change-based recovery
Answer: C
QUESTION NO: 18
You lost the PRODSTD tablespace, which was read/write. The tablespace was read-only when the last
backup was performed. How would you recover the tablespace?
A. restore the tablespace from the backup; there is no need to apply the redo information
B. restore the full database to recover the data up to the point when you performed the backup
1z0- 043
- 12 -
C. restore the tablespace from the backup and then perform a recovery using the backup control file
D. restore the tablespace from the backup and then recover the tablespace; all the redo information from the
point when the tablespace was made read/write is applied
Answer: D
QUESTION NO: 19
You are using an Automatic Storage Management (ASM) instance to manage the files of your production
database. You have two disk groups, DG1 and DG2 with one device each.
In the parameter file of the production database, the following parameters have been specified:
DB_CREATE_ONLINE_LOG_DEST_1 = .+dg1.
DB_CREATE_ONLINE_LOG_DEST_2 = .+dg2.
What would be the impact of this setting?
A. When a new log group is added, it would have one member in each disk group.
B. When a new log group is added, it would have two members in each disk group.
C. When a new tablespace is added, it would have one data file in each disk group.
D. When a new log file is added, it would have one member spread across the disk groups.
Answer: A
QUESTION NO: 20
An RMAN backup job fails, displaying the following error:
ORA-19809: limit exceeded for recovery files
Your analysis shows that the Flash Recovery area is full. Which two actions could you take to resolve the
error and avoid this error in the future? (Choose two.)
A. take the backup of the flash recovery area less frequently
B. increase the value of the UNDO_RETENTION initialization parameter
C. change the value of the WORKAREA_SIZE_POLICY initialization parameter to AUTO
D. change the backup retention policy to retain the backups for a smaller period of time
E. increase the value of the DB_RECOVERY_FILE_DEST_SIZE initialization parameter
F. increase the value of the CONTROL_FILE_RECORD_KEEP_TIME initialization parameter
1z0- 043
- 13 -
Answer: D, E
QUESTION NO: 21
Your database operates in ARCHIVELOG mode. User-managed consistent backups are performed every
Sunday night.
On Tuesday, client A drops a table at 8:00 a.m. as follows:
SQL> DROP TABLE cust_ord PURGE;
Table dropped.
Client B executes a query at 9:00 a.m. on the same table as follows:
SQL> SELECT * FROM cust_ord;
SELECT * FROM cust_ord
*
ERROR at line 1:
ORA-00942: table or view does not exist
Client B needs the dropped table and reports the problem to you.
With reference to this scenario, which action should you take?
A. retrieve the table by using the flashback feature
B. restore all the data files from last Sunday.s backup and then perform a time-based recovery
C. restore all the data files from last Sunday.s backup and then perform a log sequence recovery
D. restore all the data files from last Sunday.s backup and then perform a cancel-based recovery
E. restore from last Sunday.s backup only data files that belong to the tablespace in which the table was
stored and then perform a complete recovery
Answer: E
QUESTION NO: 22
You performed an incomplete recovery on your Oracle Database 10g database using the following
command:
1z0- 043
- 14 -
SQL> RECOVER DATABASE
2 UNTIL TIME .2004-08-05:12:10:03.
3 USING BACKUP CONTROLFILE;
In which situation would the above recovery be required?
A. A user table was lost at the specified time.
B. A redo log file was lost at the specified time.
C. The backup control file is on a faster disk than the current one.
D. The current control file has a different path compared with the intended time of recovery.
E. The current control file does not match the physical structure of the database at the intended time of
recovery.
Answer: E
QUESTION NO: 23
While creating a job class using the DBMS_SCHEDULER package, you set the logging level to
LOGGING_RUNS. What would be the impact of this setting?
A. Oracle Scheduler would write detailed information to the job log for each run of each job in the job
class.
B. Oracle Scheduler would write detailed information to the job log for the first run of each job in the job
class.
C. Oracle Scheduler would write detailed information to the job log for each run of only the first job in the
job class.
D. Oracle Scheduler would write detailed information for all operations performed on all jobs in the job
class.
Answer: A
QUESTION NO: 24
You are designing an application for TestKing.com and you have been asked to design a database table to
facilitate monthly bill generation. The bill would include details of customer calls, listed in chronological
order.
Which method would you follow to achieve this objective without increasing the overhead of sorting the
rows?
1z0- 043
- 15 -
A. create a hash cluster to store the data
B. create an index cluster to store the data
C. create a partitioned table to store the data
D. create a sorted hash cluster to store the data
E. create a heap table with rowid to store the data
Answer: D
QUESTION NO: 25
You set the recovery window to seven days and the backup optimization to ON using the CONFIGURE
command of Recovery Manager (RMAN). The most recent backup of the TOOLS tablespace to disk was
taken on January 3. The TOOLS tablespace is read-only.
On February 21, when you execute a command to back up all the tablespaces to disk, you find that
RMAN backs up the TOOLS tablespace also, even though the contents of the tablespace have not changed
after the backup on January 3.
Because there are no changes made to the TOOLS tablespace, you decide that the tablespace should not be
backed up by RMAN. What can you do to skip backing up the TOOLS tablespace without changing the
current backup optimization setting?
A. configure a default device for RMAN backups
B. temporarily disable the retention policy for RMAN backups
C. configure automatic channel allocation for RMAN backups
D. use the CONFIGURE command to reconfigure the recovery window to 60 days
Answer: B
QUESTION NO: 26
You want to configure the Flashback Database feature and retain flashback logs for three days.
The steps used in this process are:
A. Set the retention target:
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320;
B. Enable Flashback Database:
SQL> ALTER DATABASE FLASHBACK ON;
1z0- 043
- 16 -
C. Mount the database:
SQL> STARTUP MOUNT EXCLUSIVE;
D. Shut down the instance:
SQL> SHUTDOWN IMMEDIATE;
E. Open the database:
SQL> ALTER DATABASE OPEN;
In what sequence would you need to perform these steps to enable the Flashback Database feature?
A. D, C, A, D, E
B. D, A, C, B, E
C. B, D, C, E, A
D. D, B, E, C, A
Answer: A
QUESTION NO: 27
In which scenarios would you rebuild an index? (Choose all that apply.)
A. when you need to disable the index usage
B. when you need to change storage options
C. when you need to enable index monitoring
D. when you need to move the index to another tablespace
Answer: B, D
QUESTION NO: 28
You enabled Automatic Shared Memory Management. The initialization parameters are set as shown
below:
SGA_TARGET= 10GB
SGA_MAX_SIZE = 14GB
STREAMS_POOL_SIZE = 1GB
SHARED_POOL_SIZE = 3GB
Which two statements are correct in this scenario? (Choose two.)
1z0- 043
- 17 -
A. A maximum of 3 GB can be allocated to shared pool.
B. The value for SGA_TARGET can be increased up to a maximum of 15 GB.
C. A total of 14 GB memory will be allocated to the automatically tuned memory components.
D. Increasing the value for SGA_TARGET will automatically increase the memory allocated for
STREAMS_POOL_SIZE.
E. Increasing the value for SGA_TARGET to 12 GB will automatically increase the memory allocated to
autotuned parameters.
F. Reducing the value for SGA_TARGET to 9 GB will automatically decrease the memory allocated to
shared pool from 3 GB to 2 GB.
Answer: B, E
QUESTION NO: 29
Which statement correctly describes the change tracking writer (CTWR) background process?
A. It keeps track of changes in the background processes.
B. It records the system change number (SCN) in the control file.
C. It writes audit records for user changes to the change tracking file.
D. It records any changes to the listened configuration in the listener.ora file.
E. It records any changes to the database parameters in the change tracking file.
F. It records the physical location of all the changes that are made to the database in the change tracking
file.
Answer: D
QUESTION NO: 30
You work as a database administrator at TestKing.com. In your production database, you observe that
users. transactions are consuming a lot of resource and you plan to impose resource restrictions by using
Resource Manager.
For which three resources can you enable usage restrictions by using Resource Manager? (Choose three.)
A. CPU usage
B. degree of parallelism
C. number of open cursors
D. number of sorts performed
E. idle time for blocking sessions
1z0- 043
- 18 -
F. number of disk I/O operations performed
G. number of memory I/O operations performed
Answer: C, D, E
QUESTION NO: 31
You have specified the warning and critical threshold values of an application tablespace to be 60% and
70% respectively. From the tablespace space usage metrics, you find that the actual space usage has
reached the specified warning threshold value, but no alerts have been generated.
What could be the reason for this?
A. The EVENT parameter was not set.
B. The SQL_TRACE parameter is set to FALSE.
C. The Enterprise Manager Grid Control is not used.
D. The STATISTICS_LEVEL parameter is set to BASIC.
E. The TIMED_STATISTICS parameter is set to FALSE.
Answer: D
QUESTION NO: 32
You executed the following command in Recovery Manager (RMAN) against your Oracle 10g database:
RMAN> REPORT NEED BACKUP days 3;
What would be the output of this command?
A. a list of files that require a backup within three days
B. a list of files that have not been backed up for three days
C. a list of files that RMAN recommends be backed up only once in every three days, based on low
volatility
D. a list of files for which a backup has already been performed and may need a backup after three days
Answer: B
1z0- 043
- 19 -
QUESTION NO: 33
Your database operates in ARCHIVELOG mode. The redo log files are not multiplexed and one of the
online redo logs is missing. The missing redo log sequence, 230, is not archived and it contained
information from 10:35 a.m. onwards.
The current time is 11:00 a.m. Because of a disk crash, you executed the following command to perform
an incomplete recovery:
RMAN> RUN {
2> SET UNTIL SEQUENCE 230 THREAD 1;
3> ALTER DATABASE MOUNT;
4> RESTORE DATABASE;
5> RECOVER DATABASE;
6> ALTER DATABASE OPEN RESETLOGS;
7> };
With reference to this scenario, which statement is true?
A. RMAN recovers up to log sequence 230, but not including 230.
B. RMAN returns an error because the log sequence number mentioned in the command should be 229.
C. RMAN returns an error because the log sequence number mentioned in the command may never be
missing.
D. RMAN recovers up to and including log sequence 229 but then returns an error because log sequence
230 is missing.
Answer: A
QUESTION NO: 34
In your database, all the tablespaces are locally managed. You started Recovery Manager (RMAN) using
recovery catalog and restored the control file by using the following command:
RMAN> RESTORE CONTROLFILE;
Which two operations do you need to perform after restoring the control file from backup? (Choose two.)
A. shut down and restart the instance
B. add new tempfiles to the temporary tablespaces after recovery
C. perform a media recovery and open the database with the RESETLOGS option
D. perform a media recovery and bring the database to NOARCHIVELOG mode
1z0- 043
- 20 -
Answer: B, C
QUESTION NO: 35
While designing your database, you have created the EMPLOYEES table as an index-organized (IOT).
You want to create a bitmap index on the JOD_ID column to make queries faster.
Which task must have been completed so that you are able to create the bitmap index?
A. A primary key must have been created.
B. A mapping table must have been created.
C. An overflow tablespace must have been specified.
D. The PCTTHRESHOLD option must have been specified.
Answer: B
QUESTION NO: 36
Exhibit
One of the important tables in the USERS tablespace was dropped and purged from the recycle bin at
9:00 a.m. You noticed this at 11:00 a.m. and you want to perform an incomplete recovery to recover the
table.
Which statement is true?
A. You must recover all data files to the required system change number (SCN).
B. You must recover all data files, except those that are offline, to the required SCN.
1z0- 043
- 21 -
C. You must recover all data files belonging to the USERS tablespace to the required SCN.
D. You cannot recover all data files because segment space management is performed manually for the
undo tablespace.
Answer: B
QUESTION NO: 37
You work as a database administrator at TestKing.com. Your production database uses an Automatic
Storage Management (ASM) instance to manage its files. You want to add a new disk group to the ASM
instance to manage the increased data load.
What action would you perform to include the new disk group in the ASM instance without causing any
impact on the currently connected users?
A. mount the new disk group in the ASM instance
B. restart the ASM instance and the production database instance
C. register the new disk groups in the production database instance
D. restart the ASM instance without restarting the production database instance
E. include the new disk group in the ASM_DISKSTRING parameter in the parameter file and restart the
ASM instance
Answer: A
QUESTION NO: 38
The DB_BLOCK_CHECKING initialization parameter is set to TRUE. What would be the result of this
setting on the data blocks being written to the datafiles, every time the DBWn writes?
A. The Oracle database will check all data blocks by going through the data on each block, making sure the
data is self-consistent.
B. The DBWn and the direct loader will calculate a checksum and store it in the cache header of every data
block when writing it to disk.
C. The Oracle database will check data blocks belonging to the SYSTEM tablespace only, by going through
the data on each block, making sure the data is self-consistent.
D. The Oracle database will check data blocks belonging to the SYSAUX tablespace only, by going through
the data on each block, making sure the data is self-consistent.
E. The Oracle database will check data blocks in the SYSTEM and SYSAUX tablespaces only, by going
through the data on each block, making sure the data is self-consistent.
1z0- 043
- 22 -
Answer: A
QUESTION NO: 39
In your production database you want to use an Automatic Storage Management (ASM) instance to
manage the database files.
Which option would you use to migrate the database files from a non-ASM instance to an ASM instance?
A. Oracle Migration Assistant
B. Recovery Manager (RMAN)
C. Oracle Data Pump Export and Import
D. conventional Oracle Export and Import
E. operating system utilities to copy the files to the ASM instance
Answer: B
QUESTION NO: 40
In the parameter file of your production database, the CONTROL_FILE_RECORD_KEEP_TIME
parameter is set to 31. What would be the impact of this setting on the backup that is being performed by
using Recovery Manager (RMAN)?
A. The backup would be retained on the media for 31 days.
B. The backup would be maintained in the recovery catalog for 31 days.
C. The backup metadata would be retained in the control file for 31 days.
D. The backup metadata would be maintained in the recovery catalog for 31 days.
E. Automatic backups of the control file will be retained in the flash recovery area for 31 days.
Answer: C
QUESTION NO: 41
You decided to change the location of the flash recovery area. You executed the following command:
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST=.+disk1. SCOPE=BOTH SID=.*.;
Which statement is true?
1z0- 043
- 23 -
A. The location can be changed only if the flash recovery area is empty.
B. After executing the command, all new flash recovery area files will be created in the new location.
C. The command will generate an error because you cannot change the flash recovery area while the
database is open.
D. All the existing files in the flash recovery area will be moved to the new location and all new Flash
Recovery Area files will be created in the new location.
Answer: B
QUESTION NO: 42
Exhibit
A user has inserted wrong department data in the DEPT3 table in the USERS tablespace. You use the
Flashback Table functionality to rectify the erroneous inserts. While performing the recovery, you choose
2004343 as the Flashback SCN.
Which two statements are correct in this scenario? (Choose two.)
A. Only the row with DEPARTMENT_ID 290 would be flashed back.
B. The rows with DEPARTMENT_ID 290 and 300 would be flashed back.
C. The rows with DEPARTMENT_ID 290 and 280 would be flashed back.
D. You would have taken the USERS tablespace offline before starting the Flashback Table operation.
E. You would have enabled row movement for the DEPT3 table before starting the Flashback Table
operation.
Answer: B, E
QUESTION NO: 43
Using Oracle Scheduler you have scheduled two jobs, JOB_A and JOB_B, to run at 9:00 p.m. every
Friday. You want both the jobs to use a single resource plan, WEEKEND_PLAN.
Which task must have already been performed to enable you to achieve this objective?
1z0- 043
- 24 -
A. A window must have been created with the WEEKEND_PLAN resource plan.
B. A program must have been created with the WEEKEND_PLAN resource plan.
C. A job class must have been created with the WEEKEND_PLAN resource plan.
D. A window group must have been created with the WEEKEND_PLAN resource plan.
Answer: A
QUESTION NO: 44
Your database is running on the automatic Program Global Area (PGA) memory management and
Shared Memory Management mode. You want to increase the memory available for the SQL work areas.
What would you do?
A. modify the HASH_AREA_SIZE initialization parameter
B. modify the PGA_AGGREGATE_TARGET initialization parameter
C. modify the WORK_AREASIZE_POLICY initialization parameter
D. increase the value of the SGA_TARGET initialization parameter
E. increase the value of the SGA_MAX_SIZE initialization parameter
F. increase the value of the SORT_AREA_SIZE initialization parameter
Answer: B
QUESTION NO: 45
On Tuesday, a junior DBA dropped an important application user account, whose schema has important
tables. You are asked to recover all the objects in the schema. On investigation, you find that the user
account was dropped at 11:00 a.m. and Sunday.s backup is the most recent backup.
Which flashback feature would you use?
A. Flashback Drop
B. Flashback Table
C. Flashback Database
D. Flashback Version Query
E. Flashback Transaction Query
Answer: C
1z0- 043
- 25 -
QUESTION NO: 46
A redo log file is corrupted while the database is open; as a consequence, database operations are stopped
because archiving cannot continue.
What would you do to solve the problem?
A. clear the redo log group
B. perform redo log file import
C. perform an incomplete recovery
D. perform a redo log recovery using Recovery Manager (RMAN)
E. shut down the database and open the database in the NOARCHIVELOG mode
Answer: A
QUESTION NO: 47
You are working on an Oracle Database 10g database. Because of data loss, you decided to perform a
Flashback Database operation using the following command:
SQL> FLASHBACK DATABASE TO TIMESTAMP(SYSDATE ?5/24);
Which two statements are true? (Choose two.)
A. SYSDATE should not be used with TIMESTAMP.
B. The database must have multiplexed redo log files.
C. The database must be in the MOUNT state to execute the command.
D. The database must be opened with the RESETLOGS option after the flashback operation.
E. The entire database needs to be restored from the most recent backup before the flashback operation.
Answer: A, B
QUESTION NO: 48
You work as a database administrator at TestKing.com. In your test database, you find that a user.s
session is executing a lot of SQL statements, resulting in the generation of a large number of trace files.
While investigating the reason, you find that SQL trace has been enabled at the instance level.
1z0- 043
- 26 -
You want to disable SQL trace, remotely, only for that user session to reduce the volume of trace data
being generated.
How do you achieve this objective?
A. by setting the SQL_TRACE parameter to FALSE in the parameter file
B. by using DBMS_MONITOR.SESSION_TRACE_DISABLE to disable the tracing for the user session
C. by setting the SQL_TRACE parameter to FALSE by using the ALTER SYSTEM command in the user
session
D. by setting the SQL_TRACE parameter to FALSE by using the ALTER SESSION command in the user
session
Answer: B
QUESTION NO: 49
You execute the following command to enable a session in resumable mode:
SQL> ALTER SESSION ENABLE RESUMABLE TIMEOUT 60;
What is the impact of a timeout on the statements being suspended?
A. The statements remain suspended for at least 60 seconds.
B. The statements are suspended for 60 seconds and then they are executed.
C. The suspended statements error out if the problem is not rectified within 60 seconds.
D. The statements are automatically suspended 60 seconds after an error is received, and then attempt to
execute normally again.
Answer: C
QUESTION NO: 50
You are managing an Oracle Database 10g database that uses Oracle Managed Files (OMF). You
enabled the block change tracking feature for the database.
Which statement regarding the change tracking file is true?
A. One block change tracking file is created for each data file. By default, the file is created in
DB_CREATE_FILE_DEST.
1z0- 043
- 27 -
B. One block change tracking file is created for each data file. By default, the file is created in
BACKGROUND_DUMP_DEST.
C. One block change tracking file is created for the database. By default, the file is created in
DB_CREATE_FILE_DEST.
D. One block change tracking file is created for the database. By default, the file is created in
BACKGROUND_DUMP_DEST.
Answer: A
QUESTION NO: 51
You want to use the SQL Tuning Advisor to generate recommendations for badly written SQL
statements in your development environment. Which three sources can you select for the advisor to
analyze? (Choose three.)
A. Top SQL
B. snapshots
C. SQL Tuning sets
D. index access path
E. optimizer statistics
F. materialized view logs
Answer: A, C, D
QUESTION NO: 52
The loss of which two types of files may require a recovery with the RESETLOGS option? (Choose two.)
A. control files
B. password file
C. archived log files
D. system-critical data files for which all the redo entries are present
E. non-system-critical data files for which all the redo entries are present
Answer: A, D
QUESTION NO: 53
1z0- 043
- 28 -
There was media failure and you need to check the data files for any block corruption. Which option
would you use to create a report on any corruptions found within the database?
A. the DBNEWID utility
B. the DBVERIFY utility
C. the ANALYZE command
D. the RMAN REPORT command
E. the RMAN CROSSCHECK command
F. the CHECK_OBJECT procedure of the DBMS_REPAIR package
Answer: B
QUESTION NO: 54
You find that the execution time of reports in your datawarehouse application is significantly high. You
suspect the lack of indexes to be the reason for the degradation in performance.
Which advisory component would you refer to, in order to determine the appropriate indexes?
A. Memory Advisor
B. Segment Advisor
C. SQL Access Advisor
D. Automatic Workload Repository (AWR)
E. Automatic Database Diagnostic Monitor (ADDM)
Answer: C
QUESTION NO: 55
You have created a resource plan, PROD_DB_PLAN, with the following consumer groups with the
respective plan directives:
Consumer group SALES with CPU_P1 as 60 and DEGREE_OF_PARALLELISM as 4.
Consumer group MARKET with CPU_P1 as 20.
Consumer group DEV with CPU_P1 as 20.
Consumer group OTHERS with CPU_P1 as 0 and CPU_P2 as 100.
Which two statements are correct in this scenario? (Choose two.)
1z0- 043
- 29 -
A. The maximum degree of parallelism for the members of the consumer groups is 4.
B. The CPU allocation will always be equal for the consumer groups MARKET and DEV.
C. The OTHERS consumer group would get 100% of the CPU if there are unused level 1 CPU resources.
D. The SALES and MARKET consumer groups would get 100% of the CPU if there are unused level 2 CPU
resources.
E. The maximum degree of parallelism for the members of the consumer group SALES is 4 with no
restriction for the remaining groups.
Answer: C, E
QUESTION NO: 56
A user executes a query on the EMP table, which contains thousands of rows, to get details about
employees in one of the departments. The user receives the following error:
SQL> SELECT ename FROM emp WHERE deptno=10 ORDER BY sal;
SELECT ename FROM emp WHERE deptno=10 ORDER BY sal
ERROR at line 1:
ORA-01157: cannot identify/lock data file 201 . see DBWR trace file
ORA-01110: data file 201: ./u01/app/oracle/oradata/orcl/temp01.dbf.
What is a possible reason?
A. The tempfile belonging to the default temporary tablespace is missing.
B. The database is opened in restricted mode, so queries are not allowed
C. The default temporary tablespace required to execute the query is offline.
D. The default temporary tablespace required to execute the query is dropped.
E. The default temporary tablespace required to execute the query is read-only.
Answer: A
QUESTION NO: 57
You executed the following command in Recovery Manager (RMAN):
RMAN> RESTORE CONTROLFILE;
Which operation must you perform before this command is executed?
1z0- 043
- 30 -
A. back up the control file to trace
B. bring database to the MOUNT state
C. open a connection to the RMAN recovery catalog, which contains the RMAN metadata for the target
database
D. set the database ID (DBID), but only if the DB_NAME parameter associated with the target database is
unique in the recovery catalog
Answer: C
QUESTION NO: 58
Exhibit
Using Database Control, you have scheduled a job to shrink the TRANS table residing on the TT
tablespace. The job would run at 5:00 p.m. every Friday.
When you examine the space usage of the table after the completion of the job, you find that the table has
not been shrunk.
What could have been the reason for this?
A. The tablespace that contains the TRANS table is online.
B. The tablespace that contains the TRANS table is permanent.
C. The tablespace that contains the TRANS table is locally managed.
D. The segment space management of the tablespace that contains the TRANS table is manual in nature.
Answer: D
QUESTION NO: 59
1z0- 043
- 31 -
The warning and critical threshold values have been set to 85% and 97%, respectively, for one of the
tablespaces. The current tablespace space usage is 54%. You modify the warning threshold to be 50 %
and critical threshold to be 53% in Database Control.
Which statement is true?
A. The new setting would be applied but no alerts would be raised immediately.
B. The new setting would be applied and an alert would be raised immediately.
C. The new setting would be ignored because the tablespace space usage is more than the specified
threshold value.
D. The new setting would cause an error because the tablespace space usage is more than the specified
threshold value.
Answer: A
QUESTION NO: 60
You defined the Recovery Manager (RMAN) retention policy to recovery window of 7 days by executing
the following command:
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
When a backup is performed, what would be the status of this backup after seven days?
A. The backup would be marked as invalid.
B. The backup would be marked as obsolete.
C. The backup would be removed from the media.
D. The backup would be removed from the RMAN repository.
Answer: B
QUESTION NO: 61
You work as a database administrator at TestKing.com. You are working in an online transaction
processing (OLTP) environment. You realize that the salary for an employee, John, has been accidentally
modified in the EMPLOYEES table. Two days ago, the data was in the correct state. Flashback logs
generated during last two days are available in the flash recovery area.
Which option would you choose to bring the data to the correct state while ensuring that no other data in
the same table is affected?
1z0- 043
- 32 -
A. perform point-in-time recovery
B. perform a Flashback Table operation to restore the table to the state it was in two days ago
C. perform a Flashback Database operation to restore the database to the state it was in two days ago
D. perform Flashback Versions Query and Flashback Transaction Query to determine all the necessary
undo SQL statements, and then use them for recovery
Answer: D
QUESTION NO: 62
When performing a backup using Recovery Manager (RMAN), which four types of files can be backed
up with the RMAN BACKUP command? (Choose four.)
A. data file
B. password file
C. archivelog file
D. temporary file
E. online redo log file
F. the current control file
G. the tnsnames.ora file
H. current server parameter file
Answer: A, E, F, H
QUESTION NO: 63
You were recently hired by TestKing.com as a database administrator. You are asked to find out whether
the currently functional listener process, named L1, has been password protected or not. You are not
familiar with the file system of the new organization.
How would you accomplish the task?
A. by using the STATUS command of the Listener control utility
B. by using the STATUS L1 command of the Listener control utility
C. by using the SHOW RULES command of the Listener control utility
D. by using the SET PASSWORD command of the Listener control utility
E. by searching for the PASSWORD_LISTENER entry in the listener.ora file
1z0- 043
- 33 -
Answer: B
QUESTION NO: 64
These are the details about V$FLASHBACK_DATABASE_STAT:
SQL> DESC v$FLASHBACK_DATABASE_STAT
Name Null? Type
------------- -------- --------------
BEGIN_TIME DATE
END_TIME DATE
FLASHBACK_DATA NUMBER
DB_DATA NUMBER
REDO_DATA NUMBER
ESTIMATED_FLASHBACK_SIZE NUMBER
Which two statements regarding the V$FLASHBACK_DATABASE_STAT view are true? (Choose two.)
A. BEGIN_TIME is the time at which Flashback logging is enabled.
B. END_TIME is the time at which the query is executed on the view.
C. REDO_DATA is the number of bytes of redo data written during the interval.
D. This view contains information about flashback data pertaining to the last 24 hours.
E. FLASHBACK_DATA is the amount of flashback data generated since the database was opened.
Answer: B, E
QUESTION NO: 65
You lost a temporary file that belongs to the default temporary tablespace in your database. From the
options provided, which approach would you take to solve the problem?
A. flash back the database
B. import the temporary tablespace from the last export
C. restore all the data files and temporary files from the last full database backup and perform a recovery
D. not perform a recovery, but create a new temporary tablespace, make it the default temporary tablespace
and then drop the old tablespace
1z0- 043
- 34 -
Answer: D
QUESTION NO: 66
Which two statements are correct regarding the Oracle Flashback Drop feature? (Choose two.)
A. Recycle bin exists for the tables only in non-SYSTEM, locally managed tablespaces.
B. You can flash back a dropped table provided row movement has been enabled on the table.
C. If you drop an index before dropping its associated table, then the recovery of the index is not supported
when you flash back the dropped table.
D. When you execute the DROP TABLESPACE? INCLUDING CONTENTS command, the objects in the
tablespace are places in the recycle bin.
E. When a dropped table is moved to the recycle bin, only the table is renamed to a system-generated
name; its associated objects and constraints are not renamed.
F. If you drop a table that is protected by the recycle bin, then associated bitmap-joined indexes and
materialized view logs are also stored in the recycle bin.
Answer: A, C
QUESTION NO: 67
You want to create a consumer group, GROUP1, and you execute the following command in the
command-line interface:
SQL> EXEC DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP
(CONSUMER_GROUP =>.group1., COMMENT => .New Group.);
This command errors out displaying the following message:
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at .SYS.DBMS_RMIN., line 115
ORA-06512: at SYS.DBMS_RESOURCE_MANAGER., line 108
ORA-06512: at line 1
What action would you take to overcome this error?
A. grant SYSDBA to the user
B. grant SYSOPER to the user
C. grant the RESOURCE role to the user
D. use the GRANT command to grant the ADMINISTER_RESOURCE_MANAGER privilege to the use
1z0- 043
- 35 -
E. grant the ADMINISTER_RESOURCE_MANAGER privilege to the user by using the
DBMS_RESOURCE_MANAGER_PRIVS package
Answer: E
QUESTION NO: 68
You noticed that the index tablespace in your database requires a recovery. However, instead of
performing a media recovery, you decided to re-create the indexes in a new tablespace.
Which two options would you use to reduce the time it takes to re-create the indexes? (Choose two.)
A. ONLINE
B. REVERSE
C. PARALLEL
D. COMPRESS
E. NOLOGGING
F. COMPUTE STATISTICS
Answer: C, E
QUESTION NO: 69
In the parameter file of your production database, the FAST_START_MTTR_TARGET parameter is set to
300 to optimize instance recovery. While observing the performance of the database during instance
recovery, you find that the redo log files are not sized properly to support this activity.
Which two sources could you use to determine the optimal size of the redo log files? (Choose two.)
A. the V$LOG view
B. the V$DBFILE view
C. the V$LOGFILE view
D. the V$INSTANCE_RECOVERY view
E. Oracle Enterprise Manager Database Control
Answer: D, E
1z0- 043
- 36 -
QUESTION NO: 70
Why would you use the following FLASHBACK TABLE command?
FLASHBACK TABLE emp TO TIMESTAMP (.11:45...hh12:mi.);
A. to undo the changes made to the EMP table since the specified time
B. to restore the EMP table that was wrongly dropped from the database
C. to view the transactions that have modified the EMP table since the specified time
D. to view the changes made to the EMP table for one or more rows since the specified time
Answer: A
QUESTION NO: 71
What effect should the loss of an index tablespace have on an Oracle instance that is running?
A. instance hangs
B. instance gets aborted
C. instance continues running
D. instance gets restarted automatically
Answer: C
QUESTION NO: 72
By using the transaction identifier provided by ______ for a particular row change, you can use the
Flashback Transaction Query to see the operation performed by the transaction.
A. Flashback Table
B. Flashback Database
C. Flashback Version Query
D. The RMAN REPORT command
E. The DBA_PENDING_TRANSACTIONS view
Answer: C
1z0- 043
- 37 -
QUESTION NO: 73
You work as a database administrator at TestKing.com. Manually, you set the consumer group of all of
the newly created users to MYDB_GRP. You want the users to be able to change their consumer groups as
per the application requirement.
What was the first step that was needed in the process to achieve this objective?
A. The user must have been granted the DBA role.
B. The user must have been granted the switch privilege as a part of a role.
C. The user must have been granted the Resource Manager administrator privilege.
D. The user must have been granted the switch privilege by using the
DBMS_RESOURCE_MANAGER_PRIVS package.
Answer: D
QUESTION NO: 74
While setting up the threshold for tablespace space usage metrics for one of your tablespaces, you define
the critical threshold as 60% and warning threshold as 75%. What would be the result of this setting?
A. It would result in an error because the warning threshold cannot be more than the critical threshold.
B. The warning threshold is ignored and alerts would be generated when space usage exceeds the critical
threshold.
C. The critical threshold is ignored and alerts would be generated when space usage exceeds the warning
threshold.
D. Both threshold values are used and alerts are generated when space usage exceeds the respective
threshold values.
Answer: A
QUESTION NO: 75
You have created a resource plan, DB_PLAN, using
DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN and you want to impose a restriction on
utilization of resources by the database users. Which step must be performed before you can start using
the resource plan?
A. assign users to consumer groups
B. set the resource plan for the instance
C. configure a simple resource plan and specify plan directives
1z0- 043
- 38 -
D. configure a complex resource plan and specify plan directives
Answer: A
QUESTION NO: 76
Exhibit
Viewing the Exhibit and examine the Flashback Database architecture.
Identify the missing component (shown with a .?`.= in the Flashback Database architecture.
A. DBWn
B. RVWR
C. ARCH
D. RECO
E. TRWR
Answer: B
1z0- 043
- 39 -
QUESTION NO: 77
Case 1:
1. Shut down the database and perform a backup.
2. Restore all the data files.
3. Mount the database.
4. Recover the database.
5. Without applying all the redo log files, open the database using the RESETLOGS option.
6. Back up the database.
Case 2:
1. Shut down the database and perform a backup.
2. Restore only the data files of the tablespace where user error damaged the data.
3. Mount the database.
4. Recover the database.
5. Open the database with the RESETLOGS option.
6. Back up the database.
Case 3:
1. Shut down the database.
2. Restore the data files.
3. Open the database.
4. Perform recovery to the current point in time.
Which case has the correct steps for an incomplete recovery?
A. Only case 1
B. Only case 2
C. Only case 3
D. Case 1 and 2
E. Case 2 and 3
F. Case 1 and 3
G. All cases, case 1, case 2, case 3
Answer: A
QUESTION NO: 78
You work as a database administrator at TestKing.com. You observe that the senior database
administrator always uses the following command when connecting to Recovery Manager (RMAN), and
then starts executing commands to backup the database files:
1z0- 043
- 40 -
% rman target /
What would be the effect of this command?
A. RMAN would use the current operating system location to place the backup.
B. RMAN would use the control file of the target database to maintain the backup.
C. RMAN would use the recovery catalog of the target database to maintain the backup.
D. RMAN would use the control file of the target database to maintain the backup metadata.
E. RMAN would use the recovery catalog of the target database to maintain the backup metadata.
Answer: D
QUESTION NO: 79
You disabled the Flashback Database feature by using the following command:
SQL> ALTER DATABASE FLASHBACK OFF;
What would be the effect of this command on the existing flashback logs?
A. Flashback logs are not deleted.
B. Flashback logs are deleted automatically.
C. Flashback logs are deleted only if you are using Recovery Manager (RMAN)
D. Flashback logs are deleted only if you are using Oracle Manager Files (OFM)
E. Flashback logs are deleted only if you are using Automatic Storage Management (ASM)
Answer: B
QUESTION NO: 80
You work as a database administrator at TestKing.com. The production database has been functional for
the last seven days. Because of application requirements, some of the initialization parameters were
changed during run time without any comments. You have asked to find out the value of the parameters
when the instance was started.
Which source would you use to locate this information?
A. Fixed views
B. The alert log file
1z0- 043
- 41 -
C. The parameter file
D. The server parameter file
E. Dynamic performance views
Answer: B
QUESTION NO: 81
You have a disk group, DGROUP1, with three disk and NORMAL redundancy. You execute the following
command to create a template for the disk group:
ALTER DISKGROUP dgroup1
ADD TEMPLATE my_temp
ATTRIBUTES (MIRROR FINE);
Which statement is true?
A. When a file is created in DGROUP1 with the template, it would have three-way mirroring.
B. When a file is created in DGROUP1, the MY_TEMP template becomes the default template.
C. When a file is created in DGROUP1 with the template, it would have two-way mirroring and file striping.
D. When a file is created in DGROUP1 with the template, it would have three-way mirroring and file
striping.
E. When a file is created in DGROUP1 with the template, it would have two-way mirroring but no file
striping.
Answer: C
QUESTION NO: 82
You lost the index tablespace in your database. You are not able to use tablespace point-in-time recovery
on the index tablespace. What could be the reason for this?
A. The index tablespace contains bitmap indexes.
B. The index tablespace contains more than one data file.
C. The index tablespace supports only complete recovery.
D. The index tablespace is not a dictionary-managed tablespace.
E. There is a dependency relationship between a table and its indexes.
Answer: E
1z0- 043
- 42 -
QUESTION NO: 83
For the V$SESSION_LONGOPS view, you find that some of the database users have long-running queries
that consume a lot of CPU time. This causes performance problems for other users in the database, who
have much shorter queries.
You would like to make sure that the users with large queries do not use all the CPU time, but you still do
not want to terminate them with an error message.
Which method would you follow to achieve this?
A. Set the CPU time per call in the users profile.
B. Set the CPU time per session in the users profile.
C. Set the CPU levels for the user.s group using Resource Manager.
D. Set the TIMED_STATISTICS parameter to TRUE in the parameter file.
Answer: C
QUESTION NO: 84
Exhibit:
View the Exhibit and examine the characteristic of the USERS tablespace.
You observe that a large volume of inserts and deletes are happening on the TRANS table in the USERS
tablespace and you suspect that the TRANS table is fragmented.
Which advisory component would you refer to, in order to find information about table fragmentation?
1z0- 043
- 43 -
A. Memory Advisor
B. Segment Advisor
C. SLQ Tuning Advisor
D. SLQ Access Advisor
E. Automatic Database Diagnostic Monitor (ADDM)
Answer: B
QUESTION NO: 85
A data file become corrupted in your database due to bad sectors on the disk. Because of corruption, you
lost all the important tables in that data file.
Which method would you use for recovery?
A. Flash back all the tables in the data file, one by one.
B. Restore the data file to a new location and perform a media recovery.
C. Flash back the database, there is no need to restore the data file.
D. Restore the data file from the most recent backup and flash the database.
Answer: B
QUESTION NO: 86
For which two SQL statements can you use the Flashback Table feature to revert a table to its previous
state? (Choose two)
A. UPDATE TABLE
B. CREATE CLUSTER
C. TRUNCATE TABLE
D. ALTER TABLE MOVE
E. INSERT INTO...VALUES
F. ALTER TABLE...DROP COLUMN
G. ALTER TABLE...DROP PARTITION
Answer: A, E
1z0- 043
- 44 -
QUESTION NO: 87
What purpose would you achieve by enabling the block change tracking feature?
A. Eliminate the necessity for backups.
B. Perform optimized image copy backups.
C. Perform optimized incremental backups.
D. Enable checkpoint (CKPT) to perform checkpointing at every block change.
E. Enable database writer (DBWn) to write changed blocks to data files faster.
Answer: C
QUESTION NO: 88
You execute the following RMAN command in the order shown below:
BACKUP VALIDATE DATABASE;
BLOCKRECOVER CORRUPTION LIST;
What will these commands do?
A. Create a backup of the database and recover all corrupted blocks found in the backup.
B. Run a backup validation and list all the logically corrupt blocks as well as physically corrupt blocks in
the database.
C. Run a backup validation to populate V$COPY_CORRUPTION view, and then list any corrupt blocks
recorded in the view.
D. Run a backup validation to populate V$DATABASE_BLOCK_CORRUPTION view, and then repair any
corrupt blocks recorded in the view.
E. Run a backup validation, repair any corrupt blocks found during the validation process, and then update
V$DATABASE_BLOCK_CORRUPTION view to indicate which corrupt blocks have been repaired.
Answer: D
QUESTION NO: 89
In Recovery Manager (RMAN), you have set control file autobackup to ON by using the following
command:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
1z0- 043
- 45 -
Which two events would cause the control file to be backed up automatically? (Choose two)
A. A tablespace is taken offline.
B. The database instance is restarted.
C. A new data file is added to an existing tablespace.
D. A successful backup is recorded in the RMAN repository.
E. The RMAN connection is disconnected from the target database.
Answer: D, E
QUESTION NO: 90
You are working on an Oracle Database 10g database. For which purposes would you use the Flashback
Transaction Query feature? (Choose all that apply)
A. To recover a dropped table.
B. To recover a dropped schema.
C. To recover data from a truncated table.
D. To view changes made by all the transactions during a given period of time.
E. To view changes made by a single transaction during a given period of time.
Answer: A, C, E
QUESTION NO: 91
Consider the following command to add a new disk group called .tdgroupA. with two failover groups:
CREATE DISKGROUP tdgroupA NORMAL REDUNDANCY
FAILOVERGROUP control01 DISK
./devices/A1.,
./devices/A2.,
./devices/A3.
FAILOVERGROUP control02 DISK
./devices/B1.,
./devices/B2.,
./devices/B3.m
The disk ./devices/A1. is currently a member disk of a disk group by the name .tdgroup1..
Which task would be accomplished by the command?
1z0- 043
- 46 -
A. This command would result in an error because a disk group can have only one failover group.
B. This command would result in an error because the /devices/A1 disk is a member of another disk
group tdgroup1.
C. A new disk group called tdgroupA will be added with two failover groups and the /devices/A1
disk will get reattached to the new disk group without being detached from the existing one.
D. A new disk group called tdgroupA will be added with two failover groups and the /devices/A1
disk will be ignored for the new disk group because it is a member of an existing disk group
tdgroup1.
E. A new disk group called tdgroupA will be added with two failover groups and the /devices/A1
disk gets detached from the existing disk group tdgroup1 and attached to the new disk group
tdgroupA.
Answer: B
QUESTION NO: 92
You work as a database administrator at TestKing.com. Your business has departmental reports that are
generated every day. Each department must use the same set of queries, but access a different subset of
data in the tables, depending on which department generates the report. The format of the reports is
being developed; currently, the format changes daily.
How would you configure the database to ensure that each department generates its report (based on its
target data) using the most recent report format every day?
A. By having each user run the report generation procedure at the scheduled time, supplying the necessary
input variables.
B. By creating a program using DBMS_JOB that accepts one or more variables, and creating a job that
calls this program using DBMS_JOB.
C. By having each user schedule a job using DBMS_JOB that accepts one or more input variables and calls
a procedure that generates the report.
D. By having each user create a job using DBMS_SCHEDULER that includes all the information and
commands necessary to generate the report.
E. By creating a program using DBMS_SCHEDULER that accepts one or more variables, and creating a job
that calls this program using DBMS_SCHEDULER.
Answer: E
1z0- 043
- 47 -
QUESTION NO: 93
You execute the following command to create two consumer groups, FIN_GROUP1 and PAY_GROUP1 for
a plan, PROD_PLAN:
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN (SIMPLE_PLAN => .prod_plan.,
CONSUMER_GROUP1 => .fin_group1.,
GROUP1_CPU => 80,
CONSUMER_GROUP2 => .pay_group1.,
GROUP2_CPU => 20);
END;
Which three statements are true? (Choose three)
A. SYS_GROUP gets 100% CPU resources at level 1.
B. FIN_GROUP1 gets 80% CPU resources at level 1.
C. FIN_GROUP1 gets 80% CPU resources at level 2.
D. PAY_GROUP1 gets 20% CPU resources at level 1.
E. PAY_GROUP1 gets 20% CPU resources at level 2.
F. OTHER_GROUP gets 100% CPU resources at level 1.
G. OTHER_GROUP gets 100% CPU resources at level 2.
Answer: B, D, F
QUESTION NO: 92
You have configured Automatic Shared Memory Management. Which four memory structures would be
automatically tuned? (Choose four)
A. log buffer
B. Java pool
C. Large pool
D. Fixed SGA
E. Shared pool
F. Streams pool
G. Keep buffer cache
H. Database buffer cache
Answer: A, B, C, H
1z0- 043
- 48 -
QUESTION NO: 93
The DB_BLOCK_CHECKING initialization parameter is set to FALSE. What level of block checking would
be performed?
A. The Oracle database will not perform block checking for any of the data blocks.
B. The Oracle database will perform block checking for the default permanent tablespace only.
C. The Oracle database will perform block checking for the data blocks in all user tablespaces.
D. The Oracle database will perform block checking for the data blocks in the SYSTEM tablespace only.
E. The Oracle database will perform block checking for the data blocks in the SYSTEM and SYSAUX
tablespaces.
Answer: D
QUESTION NO: 94
Which statement is true regarding the creation of nested plans using Resource Manager?
A. Only one nested subplan is allowed.
B. The plans can be nested up to four levels.
C. Resource Manager does not support nested plans.
D. Nested plans control only the degree of parallelism but not the CPU.
E. Each nested plan gets a proportion of the CPU resources assigned to its parent group.
Answer: E
QUESTION NO: 95
You want your listener to be password protected to prevent it from being shut down. You want to
accomplish this task while the listener is functional. Which method could you use to achieve this
objective?
A. Use the CHANGE_PASSWORD command of the Listener control utility.
B. Use the SET PASSWORD and SAVE_CONFIG commands of the Listener control utility.
C. Use the CHANGE_PASSWORD and SAVE_CONFIG commands of the Listener control utility.
D. Manually modify the listener.ora file to include the password for the listener and restart the
listener.
Answer: C
1z0- 043
- 49 -
QUESTION NO: 96
You need to check the EMP_EAST partition in the EMPLOYEES table for physical corruptions. You also
need to verify that the rows belong to the correct partition. Which option could you use?
A. LogMiner
B. The DBNEWID utility
C. The DBVERIFY utility
D. The ANALYZE command
E. The RMAN REPORT command
F. The RMAN CROSSCHECK command.
G. The RMAN BLOCKRECOVER command.
Answer: D
QUESTION NO: 97
Users in your production database complain that they are getting the following error message while
trying to insert rows into the ORDERS table:
ERROR at line 1:
ORA-01654: unable to extend index USERS.ORDERS_IND by 8 in tablespace
INDEXES
While investigating, you find that the INDEXES tablespace has run out of space and there is no more free
space on the disk where the data files are available.
Which two actions could you perform to overcome this error without affecting the queries that are
currently being executed? (Choose two)
A. Drop and re-create the inbox.
B. Coalesce the ORDERS_IND index.
C. Coalesce the INDEXES tablespace.
D. Drop and re-create the ORDERS table.
E. Rebuild the index online and move it to another tablespace.
Answer: B, E
1z0- 043
- 50 -
QUESTION NO: 98
Consider the following scenario:
You have a directory, data, under the disk group tdgroupA. You want to create an alias for one of the
data files and you execute the following command:
ALTER DISKGROUP tdgroupA
ADD ALIAS .+tdgroupA/data/datafile.dbf.
FOR .+tdgroupA.231.45678.;
Which task would be accomplished by the command?
A. The command drops the file +tdgroupA.231.45678.
B. The command physically relocated the file to +tdgroupA/data and renames the file to
datafile.dbf.
C. The command creates a copy of the +tdgroupA.231.45678 file and places it in
+tdgroupA/data after renaming the file to datafile.dbf.
D. The command creates an alias, datafile.dbf, and places it in +tdgroupA/data and does not
remove the tdgroupA 231.45678 file.
E. The command creates a file, datafile.dbf, in +tdgroupA/data and removed the references for
+tdgroupA.231.45678 from the data dictionary views.
Answer: E
QUESTION NO: 99
You lost a data file that belongs to an index tablespace in your database, which operates in ARCHIVELOG
mode. Loss of the data file resulted in increased response time on your queries.
Which two options would you use to solve this problem? (Choose two)
A. Restore the lost data file from the backup, and then flash back the database.
B. Restore the data file pertaining to index tablespace, and then recover the tablespace.
C. Restore all the data files, and then perform an incomplete recovery to get the tablespace back.
D. Restore all the data files, and then perform an incomplete recovery using the backup control file.
E. Drop and re-create the index tablespace, and then re-create all of the indexes in that tablespace.
Answer: B, E
1z0- 043
- 51 -
QUESTION NO: 100
You work as a database administrator at TestKing.com. You database operates in ARCHIVELOG mode
and user-managed consistent backups are performed every Sunday night. On Tuesday, at 9:00 a.m. the
current log sequence number was 369. Also, on Tuesday you lost the data file belonging to the SYSTEM
tablespace and an achivelog file (sequence number 356) that contained redo entries between 8:00 a.m.
and 8.30 a.m.
With reference to this scenario, what would you do to recover the database?
A. Restore all the data files from last Sunday.s backup, and then perform a time-based recovery.
B. Restore all the data files from last Sunday.s backup, and then perform a cancel-based recovery.
C. Restore all the data files from last Sunday.s backup, and then perform a change-based recovery.
D. Restore only data files that belong to the SYSTEM tablespace from last Sunday.s backup, and then
perform a complete recovery.
Answer: B
QUESTION NO: 101
You executed the following query in your database:
SELECT oldest_flashback_scn, oldest_flashback_time
FROM V$FLASHBACK_DATABASE_LOG;
What would you determine from the output?
A. The time when the last flashback operation in your database was performed.
B. The time when the first flashback operation in your database was performed.
C. A list of flashback operations performed in your database using SCN and time.
D. The approximate time and the lowest system change number (SCN) to which you can flash back your
database.
Answer: D
QUESTION NO: 102
1z0- 043
- 52 -
While designing the database for one of your online transaction processing (OLTP) applications, you
want to achieve the following:
a) high availability of data
b) faster primary key access to the table data
c) compact storage for the table
Which type of tables would you use to achieve these objectives?
A. heap tables
B. object tables
C. partitioned tables
D. index-organized tables (IOTs)
Answer: D
QUESTION NO: 103
You are using Oracle Database 10g. The log LOG_ARCHIVE_FORMAT parameter is set to
.LOG%t_%_s_%r.dbf..
Why is %r used in the file name format?
A. To uniquely identify the archived log files with the restore operation.
B. To uniquely identify the archived log files with the redo log group number.
C. To uniquely identify the archived log files for each incarnation of the database.
D. To uniquely identify the archived log files with the number of recovery operations performed.
Answer: C
QUESTION NO: 104
Exhibit:
1z0- 043
- 53 -
Exhibit:
1z0- 043
- 54 -
View the Exhibits.
You performed operations on the DEPT4 table as shown in the Exhibit. When you perform the Flashback
Versions Query, you find that the first two updates are not listed.
What could be the reason?
A. The row movement is not enabled on the table.
B. The first two updates were not explicitly committed.
C. The Flashback Versions Query lists only the most recent update.
D. The Flashback Versions Query stops producing rows after it encounters a time in the past when the
table structure was changed.
Answer: D
QUESTION NO: 105
1z0- 043
- 55 -
Exhibit:
View the Exhibit.
You have more than one table in the recycle bin having the same original name, DEPT2. You do not have
any table with the nameDEPT2 in your schema. You executed the following command:
PURGE TABLE dept2;
Which statement is correct in this scenario?
A. All the tables having the same original name as DEPT2 will be purged from the recycle bin.
B. The table with dropscn = 1928151 (oldest dropscn) will be purged from the recycle bin.
C. The table with dropscn = 1937123 (most recent dropscn) will be purged from the recycle bin.
D. None of the tables will be purged because there are multiple entries with the same original name in the
recycle bin.
Answer: B
QUESTION NO: 106
Which type of PL/SQL construct would you use to automatically correct the error resulting from a
statement that was suspended due to a space-related problem?
A. functions
B. package
C. procedure
D. database trigger
E. anonymous PL/SQL block
Answer: D
1z0- 043
- 56 -
QUESTION NO: 107
In an Oracle 10g database, the in-memory statistics are gathered at regular intervals and used to perform
growth-trend analysis and capacity planning of the database. Which component stores these statistics?
A. Recovery catalog
B. Oracle Enterprise Manager Repository
C. Automatic Workload Repository (AWR)
D. Oracle 10g Enterprise Manager Grid Control
E. Automatic Database Diagnostic Monitor (ADDM)
Answer: C
QUESTION NO: 108
You have been assigned to manage a set of databases. The previous DBA did not leave you notes
regarding the structure of each of the databases. While analyzing an instance, you notice that the system
identifier (SID) for the instance is set to .+ASM..
What is the purpose of this instance?
A. This instance is being used to manage the operating system files.
B. This instance is being used to manage the files of other databases.
C. This instance is being used to manage the instances of other databases.
D. This instance is being used to manage the background processes of other instances.
Answer: B
QUESTION NO: 109
In one of your online transaction processing (OLTP) applications, most users frequently modify the
values, including the key values, of the application tables. Some users generate application reports by
using multiple application tables.
What is the best table structure that you can use to gain optimal performance?
A. Heap table
B. Object table
C. External table
D. Clustered table
1z0- 043
- 57 -
E. Global temporary table
F. Index-organized table (IOT)
Answer: A
QUESTION NO: 110
Your database is functioning in NOARCHIVELOG mode. Your database contains 15 tablespaces. You want
to use Recovery Manager (RMAN) to perform backups.
Which two backups would you be able to perform when the database is being accessed by users? (Choose
two)
A. Backup of offline tablespaces.
B. Backup of read-only tablespaces.
C. Backup of system-critical tablespaces.
D. Backup of online, locally managed tablespaces.
E. Backup of read/write dictionary-managed tablespaces.
Answer: A, B
QUESTION NO: 111
You work as a database administrator at TestKing.com. You are working in an online transaction
processing (OLTP) environment. You used the FLASHBACK TABLE command to flash back the
CUSTOMERS table. Before executing the FLASHBACK TABLE command, the System Change Number
(SCN) was 663571. After flashing back the CUSTOMERS table, you realize that the table is not in the
correct state and the resultant changes are not what you had desired. So, you need to reverse the effects
of the FLASHBACK TABLE command while ensuring that:
a) No other user data in the database is affected.
b) The operation takes the minimum possible time
Which option would you choose?
A. Use ROLLBACK command with SCN 663571.
B. Perform Flashback Transaction Query with SCN 663571.
C. Execute the FLASHBACK DATABASE statement to retrieve the CUSTOMERS table as it was at SCN
663571.
1z0- 043
- 58 -
D. Execute another FLASHBACK TABLE statement to retrieve the CUSTOMERS table as it was at SCN
663571.
Answer: D
QUESTION NO: 112
Because of hardware failure, you decided to drop a redo log member from the database.
Which condition should be met to drop a redo log file?
A. The redo log file should belong to an active group.
B. The redo log file should belong to an inactive group.
C. The redo log file should belong to the current group.
D. The redo log file can be dropped only if all the transactions are stopped.
E. Before a redo log file is dropped, it should be deleted from the operating system (OS).
Answer: B
QUESTION NO: 113
In your production database, you have multiple resource plan directives that refer to the customer group
DB_GRP. Which two statements are correct? (Choose two)
A. The parallel degree limit for DB_GRP will be the minimum of all the incoming values.
B. The parallel degree limit for DB_GRP will be the maximum of all the incoming values.
C. The maximum estimated execution time for DB_GRP would be the most nonrestrictive of all incoming
values.
D. If a session switched from the DB_GRP consumer group to another consumer group because it exceeded
the prescribed switch time, then that session would not execute if the active session pool for the new
consumer group was full.
E. Is a session switched from the DB_GRP consumer group to another consumer group because it exceeded
the prescribed switch time, then that session would execute even if the active session pool for the new
consumer group was full.
Answer: A, E
1z0- 043
- 59 -
QUESTION NO: 114
You work as a database administrator at TestKing.com. By mistake, you ran the batch job (for updating
the BILL_DETAILS table) twice. You are not sure which rows in the BILL_DETAILS table were
affected. You need to identify:
a) A list of changes made along with the transaction identifier of each change.
b) The necessary SQL statements to undo the erroneous changes.
Which option would you choose?
A. RMAN only.
B. Flashback Table only.
C. Flashback Version Query only.
D. Flashback Database and Flashback Transaction Query.
E. Flashback Version Query and Flashback Transaction Query.
Answer: E
QUESTION NO: 115
You used the following command in Recovery Manager (RMAN) as part of the recovery process:
RESTORE CONTROLFILE FROM AUTOBACKUP;
How does RMAN find the control file autobackup? (Choose all the apply)
A. By using the trace file.
B. By using the alert log file.
C. By using the database ID.
D. By using the server parameter file.
E. By using the V$CONTROLFILE view.
F. By using the autobackup format configuration setting.
Answer: C, D
QUESTION NO: 116
In one of your online transaction processing (OLTP) applications, users are manipulating and querying a
database table simultaneously. From the Segment Advisor, you find that one of the tables is highly
fragmented and you want to shrink the table immediately without affecting the currently active queries.
1z0- 043
- 60 -
Which option would you use with the ALTER TABLE command to achieve this objective?
A. REBUILD
B. CASCADE
C. TRUNCATE
D. ROW MOVEMENT
E. SHRINK SPACE COMPACT
F. SHRINK SPACE CASCADE
Answer: E
QUESTION NO: 117
You work as a database administrator at TestKing.com. You are working in a dedicated server
environment. Your database is running in the automatic Program Global Area (PGA) memory
management mode. Which two statements are correct in this scenario? (Choose two)
A. The WORK_AREASIZE_POLICY initialization parameter cannot be set to AUTO.
B. The Oracle database automatically controls the amount of PGA memory allotted to SQL work areas.
C. Setting the value of the SGA_TARGET initialization parameter to 0 will disable the automatic PGA
memory management.
D. The SORT_AREA_SIZE parameter is ignored by all the sessions running in the automatic PGA
memory management mode.
Answer: B, D
QUESTION NO: 118
Users are performing a large volume of inserts and deletes on the application tables in the APPS
tablespace. You observe that there are several warning alerts being generated for the APPS tablespace
space usage metrics. Currently, the warning threshold for the tablespace usage metrics is set to 70%.
To make the generated alerts more useful as a problem identification tool, you want to reduce the
frequency of alert generation for the tablespace usage metrics for the APPS tablespace.
What should you do?
A. Disable SQL tracing for the APPS tablespace.
B. Disable logging attributes for the APPS tablespace.
1z0- 043
- 61 -
C. Modify the tablespace to be a dictionary-managed tablespace.
D. Increase the critical threshold value for the tablespace space usage metrics for the APPS tablespace.
E. Increase the warning threshold value for the tablespace space usage metric for the APPS tablespace.
Answer: E
QUESTION NO: 119
Using Oracle Scheduler you have scheduled two jobs, JOB_A and JOB_B, to run at 9:00 p.m. every
Friday. You want both the jobs to use a single resource plan, WEEKEND_PLAN.
Which task must have already been performed to enable you to achieve this objective?
A. A window must have been created with the WEEKEND_PLAN resource plan.
B. A program must have been created with the WEEKEND_PLAN resource plan.
C. A job class must have been created with the WEEKEND_PLAN resource plan.
D. A windows group must have been created with the WEEKEND_PLAN resource plan.
Answer: A
QUESTION NO: 120
What are the two advantages of RMAN Block Media Recovery (BMR) over file-level recovery? (Choose
two)
A. BMR lowers the mean time to recover (MTTR).
B. BMR supports point-in-time recovery of individual data blocks.
C. BMR enables you to use incremental backups for block recovery.
D. BMR enables recovery even when the database is not mounted or open.
E. BMR enables you to use proxy backups to perform block media recovery.
F. BMR enables increased availability of data during recovery because the data file requires a recovery can
remain online.
Answer: D, E
QUESTION NO: 121
1z0- 043
- 62 -
While creating a scheduler window using the DBMS_SCHEDULER package, a user logged in as DBMGR
gets the error message insufficient privileges?
Which two commands would you use to resolve this error? (Choose two)
A. GRANT dba TO dbmgr;
B. GRANT connect TO dbmgr;
C. GRANT resource TO dbmgr;
D. GRANT hs_admin_role TO dmbgr;
E. GRANT manage scheduler TO dbmgr;
Answer: A, E
QUESTION NO: 122
You have set the value of the NLS_TIMESTAMP_TZ_FORMAT parameter in the parameter file to YYYYMM-
DD. The default format of which two data types would be affected by this setting? (Choose two)
A. DATE
B. TIMESTAMP
C. INTERVAL YEAR TO MONTH
D. INTERVAL DAY TO SECOND
E. TIMESTAMP WITH LOCAL TIME ZONE
Answer: B, E
QUESTION NO: 123
For an incomplete recovery, which four backup types can be used by Recovery Manager (RMAN) to
restore data files? (Choose four)
A. RMAN image copies.
B. RMAN database backups.
C. RMAN tablespace backups.
D. User-managed backups placed in the flash recovery area.
E. User-managed backups that have been cataloged with RMAN.
F. User-managed data file backups for which the pull path name is specified.
1z0- 043
- 63 -
Answer: B, C, D, F
QUESTION NO: 124
You are using Oracle Database 10g. You performed an incomplete recovery of your database and opened
the database with the RESETLOGS option.
What is the effect of opening the database with the RESETLOGS option? (Choose two)
A. This operation resets the SCN for the database.
B. This operation creates a new incarnation of the database.
C. This operation moves all the redo log files to a different location.
D. This operation deletes the old redo log files and creates new redo log files.
E. This operation updates all current datafiles and online redo logs and all subsequent archived redo logs
with a new RESETLOGS SCN and time stamp.
Answer: B, E
QUESTION NO: 125
Which statement is true while creating subplans using Resource Manager?
A. The subplan must belong to the consumer group, SYS_GROUP.
B. The total CPU usage at any given level must be less than 100%.
C. The subplans can be used to restrict the degree of parallelism for a user.
D. You can use a resource plan instead of a consumer group to implement a priority ranking within the
plan.
Answer: A
QUESTION NO: 126
Your database is functioning in ARCHIVELOG mode. In which two situations would you perform a
cancel-based recovery? (Choose two)
A. You find that one of the redo log members in each redo log group is lost.
B. You find that a data file that belongs to the USERS tablespace is damaged.
C. You find that a data file that belongs to the SYSTEM tablespace is damaged.
D. You find that the current redo log group is damaged and is not available for recovery.
1z0- 043
- 64 -
E. You realized while performing a recovery that an archived redo log file needed for recovery is lost.
Answer: D, E
QUESTION NO: 127
You work as a database administrator at TestKing.com. In your test database, you have created the
ORDERS table as an index-organized table (IOT). To facilitate faster querying, you have created a
mapping table and a bitmap index on the ORDER_FILLED column. You observe that the query
performance degrees when users perform a large volume of transactions.
While investigating the reason, you find that the mapping table segment is fragmented, leading to poor
performance. Which option would you use to defragment the mapping table without affecting the
original table data?
A. Export and import the mapping table.
B. Drop and re-create the mapping table.
C. Truncate the mapping table and reinsert the values.
D. Use the ALTER TABLE .. REBUILD command to defragment the mapping table.
Answer: A
No comments:
Post a Comment