Exam Code: 1Z0-040
Exam Name: Oracle Database 10g: New Features for Administrators
You executed the following command to drop a user: DROP USER scott CASCADE; Which two statements regarding the above command are correct? (Choose two.)
A. All the objects of scott are moved to the Recycle Bin.
B. Any objects in the Recycle Bin belonging to scott are purged.
C. All the objects owned by scott are permanently dropped from the database.
D. All the objects of scott in the Recycle Bin must be purged before executing the DROP command.
E. Any objects in the Recycle Bin belonging to scott will not be affected by the above DROP command.
You used the following command to perform backup: RMAN> BACKUP AS COPY DURATION 4:00 PARTIAL MINIMIZE LOAD DATABASE; Which recommendation regarding the above command is true?
A. It is recommended that you do not use the above command in the RUN block.
B. It is recommended that you do not use the MINIMIZE LOAD option with tape.
C. It is recommended that you use the MINIMIZE LOAD option only when you perform tablespace backup.
D. It is recommended that you use the DURATION and MINIMIZE LOAD options when you perform backup to backup sets.
You enabled Flashback Database with the following command: ALTER DATABASE FLASHBACK ON; Which view would you query to determine whether the Flashback Database has been enabled?
Which method would you use to undo the changes made by a particular transaction without affecting the changes made by other transactions?
A. point-in-time recovery
B. manually enter the appropriate data again
C. execute the ROLLBACK command with transaction number
D. flashback the database to before the transaction was committed
E. determine all the necessary undo SQL statements from FLASHBACK_TRANSACTION_QUERY and use them for recovery
You have a materialized view called emp_mv on the emp table. You want to stop the following query from executing if it does not rewrite: SELECT deptno,sum(sal)FROM emp GROUP BY deptno; Which statement would you use?
A. SELECT / *+ USE_CONCAT */ deptno,sum(sal)FROM emp GROUP BY deptno;
B. SELECT /*+ NO_EXPAND * /deptno,sum(sal)FROM emp GROUP BY deptno;
C. SELECT /*+ NO_REWRITE */ deptno,sum(sal)FROM emp GROUP BY deptno;
D. SELECT /*+ REWRITE_OR_ERROR */ deptno,sum(sal)FROM emp GROUP BY deptno;
E. SELECT /*+ NO_QUERY_TRANSFORMATION */ deptno,sum(sal)FROM emp GROUP BY deptno;
Identify four uses of the Oracle Scheduler. (Choose four.)
A. Enables you to set idle time limits for a resource plan.
B. Enables you to schedule job execution based on time.
C. Enables you to execute jobs in a clustered environment.
D. Enables you to assign priorities to the consumer groups.
E. Enables you to map a consumer group to an Oracle user.
F. Enables you to create a job that makes use of saved programs and schedules.
G. Enables you to periodically execute operating system script files located on the same server as the database.
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 and 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 there is no disk by the name ” /devices/D* “.
C. The command would result in an error because no wildcard characters can be used in the disk name.
D. The command will be ignored because disks starting with “D” are already members of the disk group.
E. The command first detaches all the member disks starting with “D” and then reattaches all of them including the new disk.
You are using SQL Tuning Advisor (STA) to tune SQL workload in your database. Which four types of recommendations do you get from Automatic Tuning Optimizer (ATO) via the STA? (Choose four.)
A. recommendation to create indexes
B. recommendation to use stored outlines
C. recommendation to create a SQL profile
D. recommendation to gather relevant statistics
E. recommendation to denormalize the schema
F. recommendation to restructure SQL statements
Which three files can be managed using an ASM Instance? (Choose three.)
A. Data files
B. Audit files
C. Trace files
D. Export files
E. Alert log files
F. Archived log files
G. Change tracking files
Character large object (CLOB) data is represented in Oracle database 10 g as the _____ character set.
F. same character set as the database character set
Which two statements regarding the Flashback Table feature are correct? (Choose two.)
A. Flashback Table can be performed on system tables.
B. Flashback Table operation does not shrink the segments.
C. Flashback Table uses log mining to extract SQL_REDO and SQL_UNDO statements.
D. Flashback Table operation acquires exclusive data manipulation language ( DML ) locks.
While observing database statistics, you find that the Automatic Workload Repository (AWR) snapshots are being purged after each day. You want the snapshots to be purged on a weekly basis. Which two options would you use to increase the purge interval? (Choose two.)
A. Enterprise Manager
You performed an incomplete recovery and opened the database with the RESETLOGS option. The LOG_ARCHIVE_FORMAT parameter is set to ‘ora_%t_%s_%r.log’. Which statement regarding the archived redo log files, created in an earlier incarnation of the database, is true?
A. The archived redo log files cannot be used.
B. The archived redo log files will be overwritten.
C. The archived redo log files are deleted automatically.
D. The archived redo log files should be moved to some other location.
E. The archived redo log files are still maintained because the file names are unique.
Which three actions are required to configure the Flashback Database? (Choose three.)
A. set Flash Recovery Area
B. enable Flashback logging
C. create FLASHBACK tablespace
D. create a user called flashoper
E. start the database in the ARCHIVELOG mode
F. start the database in the NOARCHIVELOG mode
You are using disk-based backups and the disk space in the backup destination is limited. A normal complete backupset will not fit onto the disk. You need to perform a full database backup in the same disk. Which two commands in this scenario are correct? (Choose two.)
A. RMAN> BACKUP DATABASE;
B. RMAN> BACKUP AS BACKUPSET DATABASE;
C. RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE;
D. RMAN> BACKUP DURATION 2:00 PARTIAL MINIMIZE TIME DATABASE;
E. RMAN> BACKUP DURATION 1:00 PARTIAL FILESPERSET 1 DATABASE;
F. RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;
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 relocates 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 a synonym, 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 removes the references for +tdgroupA.231.45678 from the data dictionary views.
You have a partitioned table called employee in USERS1 tablespace. You executed the following statement on the partition emp_d3 of the table: ALTER TABLE employee MOVE PARTITION emp_d3 TABLESPACE USERS2; Which two statements regarding the local index partition on the emp_d3 partition are correct? (Choose two.)
A. The local index partition cannot be rebuilt.
B. The local index partition remains USABLE.
C. The local index partition becomes UNUSABLE.
D. The local index partition must be dropped and re-created manually.
E. The local index partition gets dropped automatically when you move a partition of the table.
F. F.The local index partition will be rebuilt automatically if you use the UPDATE INDEX clause in the above command.
You created a locally managed tablespace by using the following SQL statement: CREATE TABLESPACE hr_tbs DATAFILE ‘data.dat’ SIZE 1M REUSE AUTOEXTEND ON MAXSIZE 100M; The warning and critical tablespace usage threshold values are set to 85% and 97%, respectively. Which two statements regarding the threshold-based alerts generation for the HR_TBS tablespace are correct? (Choose two.)
A. A critical alert will be generated when 97 MB space is used.
B. A critical alert will be generated when 970 KB space is used.
C. A warning alert will be generated when 85 MB space is used.
D. A warning alert will be generated when 850 KB space is used.
E. A critical alert will be generated when 970 KB is left as free space.
F. A warning alert will be generated when 850 KB is left as free space.
G. No critical alert will be generated because it is a small file tablespace.
H. No warning alert will be generated because the tablespace is autoextensible.