谢太数据库: ocp

Showing posts with label ocp. Show all posts

Thursday, August 20, 2009

1z0-043 lesson 12 Automatic Storage Management

1z0-043 lesson 12

是否显示鄙人做的答案
是否显示标准答案

1.
QUESTION 14:
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 then 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 there is no disk by the name "'/devices/D*'"
C. The commnad 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 fisrt detaches all the member disks start with "D", and then reattaches them including the new disk.

A
见教材12-28

2.
QUESTION 16:
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 tow.)
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 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

BE
E不对, 应该是减少才对

BE
见教材12-23
重新平衡会导致额外的IO, 影响了数据库性能, 所以应该是减小ASM_POWER_LIMIT

3.
QUESTION 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 member 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.

D
不知道

A
这个跟ASM没关系

4.
QUESTION 37:
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

A
教材12-4
"This is done while the database is active."

5.
QUESTION 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 uilities to copy the files to the ASM instance

B
教材12-34

6.
QUESTION 81:
You have a disk group, DGROUP1, with three disks 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.

C
教材12-22, 缺省是两路镜像
PDF教材14-36

7.
QUESTION 91:
Consider the following command to add a new disk group called "tdgroupA" with two failover groups:

CREATE DISKGROUP tgroupA NORMAL REDUNDANCY
FAILOVERGROUP control01 DISK
'/devices/A1',
'/devices/A2',
'/devices/A3'
FAILOVERGROUP control02 DISK
'/devices/B1',
'/devices/B2',
'/devices/B3';

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?
A. This command would result in an error because a disk group can have only on 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 becuase 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 atteched to the new disk group tdgroupA.

B
不知道

B
教材12-27
FORCE indicates that a specified disk should be added to the specified disk group even though the disk is already formatted as a member of an ASM disk group.

8.
QUESTION 98:
Consider the following scenario:
You have a directory, data, under the disk group tdgroupA. You want to create an alias for on 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.

D
Oracle Database Administrator Guide Ch12 P25

PDF教材14-36

9.
QUESTION 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 for 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.

B
教材12-9
DB_UNIQUE_NAME specifies the service provider name for which this ASM instance manages disk groups. The default value of +ASM must be modified only if you run multiple ASM instances on the same node.

10.
QUESTION 129:
Consider the following command to create a tablespace in your production database(which is using an Automatic Storage Management[ASM] instance to manage the database files):

CREATE TABLESPACE user_tbsp
DATAFILE '+dgroup3(user_temp)/user_files/user_tbsp' SIZE 200M;

What would be the result of this command?
A. It would result in an error because the template cannot be used along with the disk group.
B. It would result in an error because the path cannot be specified while creating a tablespace in a disk group.
C. It would create a tablespace with a data file that has an alias, and its attributes are set by the user-defined template.
D. It would create a tablespace with a data file that does not have an alias, and its attributes are set by the user-defined template.

C

PDF教材14-33讲了一点

11.
QUESTION 134:
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 dose 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.

D
Alias names(or just""aliases") are intended to provide a more use-friendly means of referring to ASM files, rather than using the system-generated filenames. You can create an alias for a file when you create it in the database, or you can add an alias to an existing file using the ADD ALIAS clause of the ALTER DISKGROUP statement. You can create an alias in any system-generated or user-created ASM directory. You cannot create an alias at the root level(+), however. The following statement adds a new alias name for a system-generated file name:

ALTER DISKGROUP dgroup1
ADD ALIAS '+dgroup1/mydir/second.dbf'
FOR '+dgroup1/sample/datafile/mytable.342.3';

REF.:Oracle(r) 10g Administrator Guide, 12-28

同第8题
PDF教材14-36

12.
QUESTION 152:
In your Automatic Storage Management(ASM) instance, one of the nonempty disk groups, DGROUP2, is no longer required and you want this disk group to be removed. You execute the following command to a achieve this objective:

DROP DISKGROUP dgroup1 EXCLUDING CONTENTS;

What would be the result of this command?
A. This command would result in an error because the disk group is not empty.
B. The command would distribute the contents of the specified disk group among all other disk groups and then drop the specified disk group.
C. The command would result in the contents being moved to the parent disk group and dropping of the disk group.
D. The command would result in the disk group being marked as INVALID because it cannot be dropped.
E. The command would drop the disk group, ignoring the EXCLUDING CONTENTS option.

A
有EXCLUDING这么用的吗?

A

有
Dropping Disk Groups
The default is EXCLUDING CONTENTS, which provides syntactic consistency and prevents you from dropping the disk group if it has any contents
DROP DISKGROUP
Specify EXCLUDING CONTENTS to ensure that Automatic Storage Management drops the disk group only when the disk group is empty. This is the default. If the disk group is not empty, an error will be returned.

13.
QUESTION 178:
You have an Automatic Storage Management(ASM) instance managing the database files of your production database. The database contains ASM files and non-ASM files. Using Recovery Manager(RMAN), you migrate the complete production database to use the ASM instance. Which statement is true?
A. RMAN would relocate all the data files to an ASM disk group and other files to an operating system location, defined using Oracle Managed Files(OMF).
B. RMAN would relocate the ASM files to an ASM disk group and the non-ASM files to an operating system location, defined using OMF.
C. RMAN would relocate all the database files to an ASM disk group.
D. RMAN would change the file definitions in the control file to use the ASM but would not relocate the database files physically.

C
教材12-34
Although files in a tablespace may be both ASM files and non-ASM files as a result of the tablespace history, RMAN commands enable non-ASM files to be relocated to an ASM disk group.

-----

14.
32. An ASM disk group can manage database files from how many different databases?
A. 1
B. 2
C. Limited only by disk space.
D. ASM disk groups manage tablespaces, not database files.

C
C. An ASM disk group can manage database files for essentially an unlimited number of different databases. Creating ASM disk groups is discussed in Chapter 9.

教材12-29
However, a disk group may contain files belonging to several databases, and a single database may use storage from multiple disk groups.

15.
33. High redundancy disk groups must have how many failure groups? (Choose the best answer.)
A. 1, because high redundancy disk groups rely on external RAID hardware or software
B. 2
C. Exactly 3
D. 3 or more

D
D. High redundancy disk groups require disks in at least three failure groups, but they can contain more if a higher level of redundancy or performance is desired. Controlling disk group redundancy is detailed in Chapter 9.

教材12-22
High redundancy:
Three-way mirroring
At least three failure groups

16.
34. Automatic Storage Management (ASM) disk group mirroring is done at which level?
A. Tablespace level
B. Extent level
C. Segment level
D. Datafile level

B
B. Disk group mirroring for ASM is done at the extent level. To learn about Automatic Storage Management mirroring, see Chapter 9.

说的不准确, 应该是以分配单元(AU)为单位

PDF教材14-21, (这个教材比较老, 所以说的不准确)
ASM does not mirror disks; rather, it mirrors extents.

教材12-20
ASM always spreads files evenly in 1 MB allocation unit (AU) chunks across all the disks in a disk group.
教材12-22
ASM does not mirror disks; rather, it mirrors allocation units.

17.
35. Identify two valid types of Oracle instances.
A. RMAN
B. DSKMGR
C. ASM
D. DBMS
E. RDBMS

CE
C, E. ASM instances manage ASM disk groups, and RDBMS instances are the traditional and only type of instance available before Oracle Database 10g. Configuring ASM and RDBMS instances for Automatic Storage Management is covered in Chapter 9.

教材12-9
INSTANCE_TYPE should be set to ASM for ASM instances.
教材12-10
INSTANCE_TYPE defaults to RDBMS and specifies that this instance is an RDBMS instance.

---

18.
1. Extents in an ASM file are allocated in units of which size?
A. 100KB
B. 10MB
C. 1MB
D. 64KB

C
忘了

C
C. ASM disks are partitioned in allocation units of one megabyte each.

教材12-20
ASM always spreads files evenly in 1 MB allocation unit (AU) chunks across all the disks in a disk group.

19.
2. To prevent connections to an ASM instance, you can use which of the following commands? (Choose the best answer.)
A. ALTER SYSTEM ENABLE RESTRICTED SESSION
B. SHUTDOWN IMMEDIATE
C. ALTER SYSTEM DISABLE CONNECTIONS
D. ALTER DATABASE ENABLE RESTRICTED SESSION

A
A. Similar to an RDBMS instance, you can use ALTER SYSTEM ENABLE RESTRICTED SESSION to prevent connections to the instance. While SHUTDOWN IMMEDIATE will prevent connections to the ASM instance, this is most likely overkill if all you want to do is temporarily prevent connections. Choices C and D are not valid commands and will generate an error message.

PDF教材14-8
Finally, you can prevent database instances from connecting to an ASM instance. When the command ALTER SYSTEM ENABLE RESTRICTED SESSION is issued to an ASM instance, database instances cannot connect to that ASM instance. Conversely, ALTER SYSTEM DISABLE RESTRICTED SESSION enables connections from database instances. This command permits an ASM instance to start up and mount disk groups for purposes of maintenance without allowing database instances to access the disk groups.

20.
3. Which initialization parameter in an ASM instance specifies the disk groups to be automatically mounted at instance startup?
A. ASM_DISKMOUNT
B. ASM_DISKGROUP
C. ASM_DISKSTRING
D. ASM_MOUNTGROUP

B
B. The initialization parameter ASM_DISKGROUP, valid only in an ASM instance, specifies the disk groups to be automatically mounted when the ASM instance starts. ASM_DISKSTRING is operating system dependent and restricts the file system devices that can be used to create disk groups. ASM_DISKMOUNT and ASM_MOUNTGROUP are not valid initialization parameters.

教材12-9
ASM_DISKGROUPS is the list of names of disk groups to be mounted by an ASM instance at startup, or when the ALTER DISKGROUP ALL MOUNT command is used.

21.
4. Which of the following command options is not valid for an ASM instance?
A. STARTUP OPEN
B. STARTUP NOMOUNT
C. STARTUP MOUNT
D. STARTUP OPEN RESTRICT
E. SHUTDOWN ABORT

A
D也不行?

A
A. An ASM instance can be started up and shut down in the same way that an RDBMS database can, except that an ASM instance cannot be in the OPEN state because it does not have a data dictionary or a control file.

为啥D可以?

22.
5. When an ASM instance receives a SHUTDOWN NORMAL command, what command does it pass on to all database instances that rely on the ASM instance’s disk groups?
A. TRANSACTIONAL
B. IMMEDIATE
C. ABORT
D. NORMAL
E. None of the above

D
D. When an ASM instance receives a SHUTDOWN command, it passes the same option (NORMAL, IMMEDIATE or TRANSACTIONAL) to all database instances that rely on the ASM instance for disk group services.

PDF教材14-18
Upon receiving the shutdown command, the ASM instance forwards the shutdown command with the same shutdown mode (NORMAL, IMMEDIATE, TRANSACTIONAL) to all database instances dependent on the ASM instance.

23.
6. When creating a disk group, what keyword must be specified if you need to reuse a disk that has previously been used as part of another disk group?
A. NOFORCE
B. REUSE
C. USE
D. FORCE
E. INCLUDING CONTENTS

D
D. You must use FORCE if the disk has previously been used as part of a disk group. If the disk has never been used as part of a disk group, using the FORCE keyword returns an error.

教材12-27
FORCE indicates that a specified disk should be added to the specified disk group even though the disk is already formatted as a member of an ASM disk group.

24.
7. Which of the following ASM file templates is not striped as fine?
A. FLASHBACK
B. ARCHIVELOG
C. CONTROLFILE
D. ONLINELOG

A
或者是B?

B
B. Files such as ARCHIVELOG files use coarse-grained striping. Fine striping stripes the files every 128KB while coarse striping stripes the files every 1MB. All file types with the exception of FLASHBACK, CONTROLFILE, and ONLINELOG are striped coarse.

见PDF教材14-35表格

25.
8. You want to migrate your database to ASM, so you’ve done a clean shutdown, made a closed backup of the entire database, noted the location of your control files and online redo log files, and changed your SPFILE to use OMF. The last step is running an RMAN script to do the conversion. Using the following steps, what is the correct order in which the following RMAN commands should be executed?

1. STARTUP NOMOUNT
2. ALTER DATABASE OPEN RESETLOGS
3. SQL "ALTER DATABASE RENAME 'logfile1 path' TO '+dgrp4 '"   # plus all other log files
4. SWITCH DATABASE TO COPY
5. BACKUP AS COPY DATABASE FORMAT '+dgrp4'
6. ALTER DATABASE MOUNT
7. RESTORE CONTROLFILE FROM 'controlfile_location'

A. 2, 5, 3, 1, 7, 6, 4
B. 1, 7, 6, 5, 4, 3, 2
C. 5, 1, 2, 7, 4, 6, 3
D. 7, 3, 1, 5, 6, 2, 4

B
B. After the RMAN script is run and the database is up and running successfully, you may delete the old database files.

教材12-34

26.
9. How can you reverse the effects of an ALTER DISKGROUP ... DROP DISK command if it has not yet completed?
A. Issue the ALTER DISKGROUP ... ADD DISK command.
B. Issue the ALTER DISKGROUP ... UNDROP DISKS command.
C. Issue the ALTER DISKGROUP ... DROP DISK CANCEL command.
D. Retrieve the disk from the recycle bin after the operation completes.

B
B. If the DROP DISK operation has not yet completed, you can cancel and roll back the entire DROP DISK operation by using ALTER DISKGROUP ... UNDROP DISKS, with the disk group still being continuously available to all users.

教材12-30
The third statement shows how to cancel a disk drop operation. The UNDROP command operates only on pending drops of disks; it has no effect on drops that have completed.

27.
10. To reference existing ASM files, you need to use a fully qualified ASM filename. Your development database has a disk group named DG2A, the database name is DEV19, and the ASM file that you want to reference is a datafile for the USERS02 tablespace. Which of the following is a valid ASM filename for this ASM file?
A. dev19/+DG2A/datafile/users02.701.2
B. +DG2A/dev19/datafile/users02.701.2
C. +DG2A/dev19/users02/datafile.701.2
D. +DG2A.701.2
E. +DG2A/datafile/dev19.users.02.701.2

B
好像D也是合法的?

B
B. A fully qualified existing ASM filename has the format +group/dbname/filetype/tag.file.incarnation. In this case, filetype is datafile, and tag is the tablespace name to which it belongs, or users02.

题目中问的是全称(fully qualified name), 所以不选D
PDF教材14-32

28.
11. Which background process coordinates the rebalance activity for disk groups?
A. ORBn
B. OSMB
C. RBAL
D. ASMn

C
C. RBAL coordinates rebalance activity for a disk group in an ASM instance, ORBn actually performs the extent movement in an ASM instance, and OSMB acts as a bridge between the ASM instance and the RDBMS instance. There is no such process name ASMn.

教材12-5
One coordinates rebalance activity for disk groups. It is called RBAL.

29.
12. On the development database rac0 there are six raw devices: /dev/raw/raw1 through /dev/raw/raw6. /dev/raw/raw1 and /dev/raw/raw2 are 8GB each, and the rest are 6GB each. An existing disk group +DATA1, of NORMAL REDUNDANCY, uses /dev/raw/raw1 and /dev/raw/raw2. Which series of the following commands will drop one of the failure groups for +DATA1, create a new disk group +DATA2 using two of the remaining four raw devices, and then cancel the drop operation from +DATA1?
A.

ALTER DISKGROUP DATA1 DROP DISK DATA1_0001;
CREATE DISKGROUP DATA2 NORMAL REDUNDANCY
  FAILGROUP DATA1A DISK '/dev/raw/raw3'
  FAILGROUP DATA1B DISK '/dev/raw/raw4';
ALTER DISKGROUP DATA1 UNDROP DISKS;

ALTER DISKGROUP DATA1 DROP DISK DATA1_0001;
CREATE DISKGROUP DATA2 HIGH REDUNDANCY
  FAILGROUP DATA1A DISK '/dev/raw/raw3'
  FAILGROUP DATA1B DISK '/dev/raw/raw4'
ALTER DISKGROUP DATA1 UNDROP DISKS;

ALTER DISKGROUP DATA1 DROP DISK DATA1_0001;
CREATE DISKGROUP DATA2 NORMAL REDUNDANCY
  FAILGROUP DATA1A DISK '/dev/raw/raw3'
  FAILGROUP DATA1B DISK '/dev/raw/raw4';
ALTER DISKGROUP DATA1 UNDROP DATA1_0001;

ALTER DISKGROUP DATA1 DROP DISK DATA1_0001
  ADD DISKGROUP DATA2 NORMAL REDUNDANCY
  FAILGROUP DATA1A DISK '/dev/raw/raw3'
  FAILGROUP DATA1B DISK '/dev/raw/raw4';
ALTER DISKGROUP DATA1 UNDROP DISKS;

A
题意不清,B也对?

A
A. Note that the UNDROP operation will cancel a drop operation in progress but cannot reverse a drop operation that has already completed. For HIGH REDUNDANCY, at least three failure groups must be specified. While you can combine a drop and add operation into one command, the command can reference only one disk group.

高冗余度至少需要3个硬盘, 所以不选B

30.
13. Which type of database file is spread across all disks in a disk group?
A. All types of files are spread across all disks in the disk group.
B. Datafiles
C. Redo log files
D. Archived redo log files
E. Control files

A
A. All types of database files are spread across all disks in the disk group to ensure redundancy unless the redundancy is set to EXTERNAL.

教材12-19
ASM files are always spread across all the ASM disks in the disk group.

31.
14. How can you reverse the effects of an ALTER DISKGROUP ... DROP DISK command if it has already completed?
A. Issue the ALTER DISKGROUP ... ADD DISK command.
B. Issue the ALTER DISKGROUP ... UNDROP DISKS command.
C. Issue the ALTER DISKGROUP ... DROP DISK CANCEL command.
D. Retrieve the disk from the recycle bin after the operation completes.

A
A. If the DROP DISK operation has already completed, you must use ALTER DISKGROUP ... ADD DISK to add the disk back to the disk group. In any case, the disk group is continuously available to all users.

教材12-30

32.
15. Which of the following ALTER DISKGROUP commands does not use V$ASM_OPERATION to record the status of the operation?
A. ADD DIRECTORY
B. DROP DISK
C. RESIZE DISK
D. REBALANCE
E. ADD FAILGROUP

A
A. The ADD DIRECTORY command does not use V$ASM_OPERATION to track its progress, because this operation adds only a small amount of metadata—a directory object—to the disk group and takes a minimal amount of time to complete.

教材14-29
The ALTER DISKGROUP DROP, RESIZE, and REBALANCE commands return before the operation is complete. To monitor progress of these long-running operations, you can query the V$ASM_OPERATION fixed view.

33.
16. If you use ALTER DISKGROUP ... ADD DISK and specify a wildcard for the discovery string, what happens to disks that are already a part of the same or another disk group?
A. The command fails unless you specify the FORCE option.
B. The command fails unless you specify the REUSE option.
C. The command must be reissued with a more specific discovery string.
D. The other disks already part of the disk group are ignored.

D
D. The ALTER DISKGROUP ... ADD DISK command adds all disks that match the discovery string but are not already part of the same or another disk group.

教材12-28
The second command adds A4 to the DGROUPA disk group. It ignores the other disks, even though they match the discovery string, because they are already part of the DGROUPA disk group.

34.
17. Choose the set of the following initialization parameters that is valid and recommended for an ASM instance.
A. INSTANCE_TYPE=RDBMS
ASM_POWER_LIMIT=2
LARGE_POOL_SIZE=8MB
DB_UNIQUE_NAME=+ASM
ASM_DISKGROUPS=DATA1,DATA2
B. INSTANCE_TYPE=ASM
ASM_POWER_LIMIT=2
LARGE_POOL_SIZE=8MB
DB_UNIQUE_NAME=+ASM
ASM_DISKGROUPS=DATA1,DATA2
C. INSTANCE_TYPE=ASM
ASM_POWER_LIMIT=15
LARGE_POOL_SIZE=8MB
DB_UNIQUE_NAME=+ASM
ASM_DISKGROUPS=DATA1,DATA2
D. INSTANCE_TYPE=ASM
ASM_POWER_LIMIT=2
LARGE_POOL_SIZE=4MB
DB_UNIQUE_NAME=+ASM
ASM_DISKGROUPS=DATA1,DATA2

B
B. The INSTANCE_TYPE for an ASM instance is ASM; otherwise, it is RDBMS, whether it uses ASM or not. The ASM_POWER_LIMIT command controls the speed of a disk group rebalance, but its maximum value is 11. For an ASM instance, the minimum recommended value for LARGE_POOL_SIZE is 8MB.

教材12-9

35.
18. Which of the following scenarios concerning ASM instance shutdown is correct?
A. When an ASM instance is shut down with NORMAL, IMMEDIATE, or TRANSACTIONAL, the same shutdown command is passed to the dependent instances and the ASM instance waits for all dependent instances to shut down before it shuts down.
B. When an ASM instance shuts down with NORMAL, an alert is sent to all dependent instances, notifying the DBA to shut down the dependent instances manually before the ASM instance shuts down.
C. When an ASM instance shuts down with the TRANSACTIONAL option, all dependent instances shut down with NORMAL, IMMEDIATE, or TRANSACTIONAL, depending on the dependent database’s default.
D. When an ASM instance is shut down with NORMAL, IMMEDIATE, or TRANSACTIONAL, the same shutdown command is passed to the dependent instances and the ASM instance does not wait for all dependent instances to shut down before it shuts down.
E. When an ASM instance shuts down with the IMMEDIATE option, the ASM instance shuts down immediately and all dependent instances shut down with ABORT.

A
A. When an ASM instance shuts down with NORMAL, IMMEDIATE, or TRANSACTIONAL, the same shutdown option is passed to all dependent instances and the ASM instance waits for the dependent instances to shut down before shutting itself down. If an ASM instance shuts down with ABORT, it immediately shuts down, the dependent instances lose their connection to the ASM instance, and as a result, they shut down with ABORT either before or after the ASM instance shuts down completely.

PDF教材14-18

36.
19. A database can create datafiles in how many different disk groups? (Choose the best answer.)
A. Each datafile in the database can reside in a different disk group.
B. One
C. Disk groups manage tablespaces, not datafiles.
D. A maximum of two, one for SYSTEM and SYSAUX and the other tablespaces in another disk group.

A
A. Each database datafile can reside in a different disk group; each disk group can also contain datafiles from other databases.

教材12-19

37.
20. ASM supports all of the following file types except for which of the following? (Choose all that apply.)
A. Database files
B. SPFILEs
C. Redo log files
D. Archived log files
E. RMAN backup sets
F. Password files
G. init.ora files

FGB

FG
F, G. ASM supports datafiles, log files, control files, archive logs, RMAN backup sets, SPFILEs, and other Oracle database file types, but not password files or init.ora files.

PDF教材14-34表格

-fin-

Tuesday, June 23, 2009

1z0-043 lesson 11 Managing Storage

1z0-043 lesson 11

大部分题和前一章重复了, 再做一遍

是否显示我的答案
是否显示书上的答案和我的注释

1.
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.

D

见STATISTICS_LEVEL(http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams211.htm#sthref640)

2.
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.

C

教材11-21
"A resumable statement that is suspended for the time-out interval (the default is 7,200 seconds (2 hours)) reactivates itself and returns the exception to the user."

3.
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.

D

教材11-14
"You cannot execute a shrink operation on segments managed by free lists. Segments in automatic segment-space managed tablespaces can be shrunk."

4.
59. 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.

A

教材11-11
"This information is aggregated every 10 minutes by the MMON process. An alert is triggered when the threshold for a tablespace has been reached or cleared. "

5.
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.

A

没找到

6.
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?

A. Memory Advisor
B. Segment Advisor
C. SQL Tuning Advisor
D. SQL Access Advisor
E. Automatic Database Diagnostic Monitor (ADDM)

B

教材11-19

7.
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

D

教材11-25

8.
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)

C

教材11-9
"Information gathered is stored in the Automatic Workload Repository (AWR) and is used to perform growth trend analysis and capacity planning of the database."

9.
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. 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

E

教材11-12
"The first phase does the compaction. During this phase, rows are moved to the left part of the segment as much as possible. Internally, rows are moved by packets to avoid locking issues."

10.
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.
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.

E

教材11-10
"When the tablespace limits are reached, an appropriate alert is raised."

11.
127. 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.

B
忘了, C?

A

教材11-14
However, the following objects stored in ASSM tablespaces cannot be shrunk:
Tables in clusters
Tables with LONG columns
Tables with on-commit materialized views
Tables with ROWID-based materialized views
IOT mapping tables
Tables with function-based indexes

见第9课(http://mrxiesdatabase.blogspot.com/2009/05/1z0-043-lesson-9.html)
----
PDF教材13-38
"To rebuild the mapping table, it must be dropped and re-created."
问题:
alter table xxx move nomapping;
alter table xxx move mapping table;
会锁表吗?
----

12.
142. View the Exhibit and examine the characteristics 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? Exhibit:

A. Segment Advisor
B. SQL Access Advisor
C. SQL Tuning Advisor
D. Memory Advisor
E. Automatic Database Diagnostic Monitor (ADDM)

A

教材11-15

13.
154. Exhibit: View the Exhibit and examine the properties of the USERS tablespace. You execute the following statement to shrink the TRANS table existing on the USERS tablespace:
SQL> ALTER TABLE trans SHRINK SPACE CASCADE;
Which objects would be affected by this command? (Choose all that apply.)

A. the materialized views based on the TRANS table
B. the materialized views log of the TRANS table
C. the TRANS table
D. the B*Tree indexes on the TRANS table
E. the large object (LOB) segments of the TRANS table

CD
又忘了

CD

ABE为何不对?

教材11-14
However, the following objects stored in ASSM tablespaces cannot be shrunk:
Tables in clusters
Tables with LONG columns
Tables with on-commit materialized views
Tables with ROWID-based materialized views
IOT mapping tables
Tables with function-based indexes

教材11-20
Use the ALTER command to invoke segment shrink on an object. The object’s type can be one of the following: table (heap- or index-organized), partition, subpartition, LOB (data and index segment), index, materialized view, or materialized view log.

===

14.
47. Consider the index HR.IDX_PK_EMP on the table HR.EMPLOYEES and the following ALTER INDEX command:
ALTER INDEX HR.IDX_PK_EMP COALESCE;
Which of the following commands accomplishes the same task as this command? (Choose the best answer.)
A. ALTER TABLE HR.EMPLOYEES SHRINK SPACE CASCADE;
B. ALTER TABLE HR.EMPLOYEES SHRINK SPACE;
C. ALTER TABLE HR.EMPLOYEES SHRINK SPACE COMPACT;

47. A. Using the CASCADE keyword in any segment shrink operation will shrink the free space in any dependent objects such as indexes. Chapter 13 discusses segment shrink functionality.

教材11-20
"If CASCADE is specified, the shrink behavior is cascaded to all the dependent segments that support a shrink operation, except materialized views, LOB indexes, and IOT (index-organized tables) mapping tables."

15.
50. Which of the following statements is not true about segment shrink operations?
A. The compaction phase of segment shrink is done online.
B. During the compaction phase, the entire segment is locked but only for a very short period of time.
C. When the second phase of segment shrink occurs, the high watermark (HWM) is adjusted.
D. User DML can block the progress of the compaction phase until the DML is committed or rolled back.
E. Using the COMPACT keyword, the movement of the HWM can occur later during non-peak hours by running the command without the COMPACT keyword.

50. B. During the compaction phase, locks are held only on individual rows, causing some minor serialization with concurrent DML operations. For more information about segment shrink, see Chapter 13.

教材11-12

===

16.
1. Which data dictionary view provides the recommended action, as a SQL statement, from the Segment Advisor?
A. DBA_ADVISOR_FINDINGS
B. DBA_ADVISOR_RECOMMENDATIONS
C. DBA_ADVISOR_ACTIONS
D. DBA_ADVISOR_RATIONALE

1. C. The data dictionary view DBA_ADVISOR_ACTIONS contains the SQL statement(s) that the Segment Advisor supplies to implement its recommendation for segment maintenance. DBA_ADVISOR_FINDINGS contains the results of the analysis, but no SQL. DBA_ADVISOR_RECOMMENDATIONS presents one or more findings and the benefits for performing the recommendation. DBA_ADVISOR_RATIONALE provides a more detailed set of reasons why the recommendation should be implemented, along with the impact of not performing the recommendation.

见第10课(http://mrxiesdatabase.blogspot.com/2009/06/1z0-043-lesson-10.html)
---
见
Viewing Segment Advisor Results(http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/schema.htm#sthref2119)
DBA_ADVISOR_RECOMMENDATIONS(http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_3014.htm)
DBA_ADVISOR_ACTIONS(http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_3003.htm)
---

17.
3. Consider the following scenario. The user SCOTT runs a query at 8:25 A.M. that receives an ORA-01555: Snapshot too old error after running for 15 minutes. An alert is sent to the DBA that the undo tablespace is incorrectly sized. At 10:15 A.M., the DBA checks the initialization parameter UNDO_RETENTION, and its value is 3600; the parameter is sized correctly. The DBA doubles the size of the undo tablespace by adding a second datafile. At 1:15 P.M., the user SCOTT runs the same query and once again receives an ORA-01555: Snapshot too old error. What happens next? (Choose the best answer.)
A. The DBA receives another alert indicating that the undo tablespace is still undersized.
B. The user SCOTT calls the DBA to report that the query is still failing.
C. The second datafile autoextends so that future queries will have enough undo space to complete when there is concurrent DML activity.
D. Resumable Space Allocation suspends the query until the DBA adds another datafile to the undo tablespace, and then the query runs to completion.

3. B. Even if the size of the undo tablespace is adjusted after an undo space problem, only one alert is sent for each 24-hour period. Therefore, the only way that the problem will be resolved promptly is for SCOTT to call the DBA, because the DBA will not receive another alert until the next day when another query fails.

见第10课(http://mrxiesdatabase.blogspot.com/2009/06/1z0-043-lesson-10.html)
---
...24小时内重复的报警只发一次, 不太合理呀. 只有undo报警才这么处理?

见
Viewing Information About Undo(http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/undo.htm#sthref1510)
In addition to the proactive undo space alerts, Oracle Database also provides alerts if your system has long-running queries that cause SNAPSHOT TOO OLD errors. To prevent excessive alerts, the long query alert is issued at most once every 24 hours. When the alert is generated, you can check the Undo Advisor Page of Enterprise Manager to get more information about the undo tablespace.---

18.
4. The background process __________ checks for tablespace threshold violation or clearance every __________ minutes.
A. MMON, 10
B. SMON, 10
C. TMON, 30
D. PMON, 15
E. MMON, 30

E
猜的

4. A. The new background process MMON checks for threshold violations every 10 minutes. An alert is triggered when the threshold is reached or is cleared.

教材11-11
"This information is aggregated every 10 minutes by the MMON process."

19.
6. Which of the following is not a benefit of segment shrink?
A. Full table scans will take less time.
B. Better index access takes place because of a smaller b-tree.
C. Space is freed up for other database objects.
D. All chained rows are fixed.
E. Space below the HWM is released, and the HWM is moved down.

6. D. While some chained rows may be fixed with segment shrink functionality, it is not guaranteed that all chained rows will be fixed, because not all blocks may be read in a segment shrink operation.

不是很理解

见第10课
---
D 见Space Reclamation(http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/mgmt_db.htm#sthref2240)
Although segment shrink reduces row chaining, and the Oracle Database recommends online redefinition to remove chained rows, the Segment Advisor actually detects certain chained rows that are above a threshold. For example, if a row size increases during an update such that it not longer fits into the block, then the Segment Advisor recommends that the segment be reorganized to improve I/O performance.
Note: The Segment Advisor does not detect chained rows created by inserts.
---

20.
9. In the following scenario, the DBA wants to reclaim a lot of wasted space in the HR.EMPLOYEES table by using the segment shrink functionality. Which of the following is the correct order of the steps?
1. ALTER TABLE HR.EMPLOYEES SHRINK SPACE;
2. ALTER TABLE HR.EMPLOYEES DISABLE ROW MOVEMENT;
3. ALTER TABLE HR.EMPLOYEES ENABLE ROW MOVEMENT;
4. ALTER TABLE HR.EMPLOYEES SHRINK SPACE COMPACT;
5. ALTER TABLE HR.EMPLOYEES SHRINK SPACE CASCADE;
A. 3, 4, 1, 5, 2
B. 4, 1, 3, 2, 5
C. 5, 2, 1, 3, 4
D. 4, 1, 2, 3, 5

9. A. While the segment shrink operation could combine steps 1 and 4, the impact to the users will most likely be lessened by performing two smaller operations instead of one by specifying SHRINK SPACE COMPACT before specifying SHRINK SPACE in a subsequent operation.

见教材11-12,11-20

21.
10. Which of the following calls to DBMS_SERVER_ALERT.SET_THRESHOLD will set the thresholds for the UNDOTBS1 tablespace to 60 percent and 90 percent? (Choose the best answer.)
A.

dbms_server_alert.set_threshold(
  dbms_server_alert.tablespace_pct_full,
  dbms_server_alert.operator_ge, 60,
  dbms_server_alert.operator_ge, 90,
  1, 1, null,
  dbms_server_alert.object_type_tablespace,
  null);

dbms_server_alert.set_threshold(
  dbms_server_alert.tablespace_pct_full,
  dbms_server_alert.operator_le, 60,
  dbms_server_alert.operator_le, 90,
  1, 1, null,
  dbms_server_alert.object_type_datafile,
  'UNDOTBS1');

dbms_server_alert.set_threshold(
  dbms_server_alert.tablespace_full,
  dbms_server_alert.operator_ge, 60,
  dbms_server_alert.operator_ge, 90,
  1, 1, null,
  dbms_server_alert.object_type_tablespace,
  'UNDOTBS1');

dbms_server_alert.set_threshold(
  dbms_server_alert.tablespace_pct_full,
  dbms_server_alert.operator_ge, 60,
  dbms_server_alert.operator_ge, 90,
  1, 1, null,
  dbms_server_alert.object_type_tablespace,
  'UNDOTBS1');

10. D. The call to DBMS_SERVER_ALERT.SET_THRESHOLD must specify the metric TABLESPACE_PCT_FULL, the two thresholds, an object type of tablespace, and the tablespace name itself. Specifying NULL for the tablespace name will set the threshold for all tablespaces, not just the UNDOTBS1 tablespace.

见第10课
---
见
94 DBMS_SERVER_ALERT(http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_salt.htm)
---

22.
11. Which of the following statements is not true about segment shrink operations? (Choose the best answer.)
A. Tables with ROWID-based materialized views are maintained.
B. Segment shrink is allowed only on segments whose space is automatically managed.
C. Heap-organized and index-organized tables can be shrunk.
D. ROW MOVEMENT must be enabled for heap-organized segments.
E. Chained rows may be repaired during a segment shrink operation.
F. Triggers are not fired during a segment shrink operation.

C
忘了 A?

11. A. Because the ROWIDs are changed with a segment shrink operation, tables with ROWID-based materialized views cannot be shrunk unless the materialized views are dropped and re-created after the segment shrink operation.

教材11-14
However, the following objects stored in ASSM tablespaces cannot be shrunk:
Tables in clusters
Tables with LONG columns
Tables with on-commit materialized views
Tables with ROWID-based materialized views
IOT mapping tables
Tables with function-based indexes

23.
12. Which of the following is not a feature of the Segment Advisor within EM Database Control?
A. Growth trend analysis
B. Segment Resource Estimation
C. Finding candidates for segment shrink
D. Finding table segments with chained rows

D
?

12. D. The Segment Advisor is not used to find tables with chained rows, but instead is used for finding segments that are good candidates for segment shrink or may be growing too fast.

有疑问

见第10课
---
segment advisor可以发现chained rows呀, 见上一道题
---

24.
14. Which of the following statements is not true about segment shrink operations in tablespaces with automatic segment space management?
A. Clustered tables cannot be shrunk.
B. LOB segments can be shrunk.
C. IOT mapping tables and overflow segments cannot be shrunk.
D. Tables with function-based indexes cannot be shrunk.
E. ROW MOVEMENT must be enabled for heap-based segments.

14. B. For segments in tablespaces with automatic segment space management, LOB segments cannot be shrunk. In addition, tables with LONG columns, on-commit materialized views, and ROWID-based materialized view cannot be shrunk. In all cases, shrink operations cannot be performed on segments managed by freelists.

有疑问, 可以收缩LOB类型的呀
教材11-20
"The object’s type can be one of the following: table (heap- or index-organized), partition, subpartition, LOB (data and index segment), index, materialized view, or materialized view log."

25.
15. Which of the following is a disadvantage of rebuilding an index instead of coalescing an index?
A. You temporarily need twice as much disk space to rebuild the index.
B. You cannot move the index to another tablespace.
C. The storage characteristics of the index cannot be changed.
D. The rebuild operation only coalesces index leaf blocks within each branch instead of re-creating the entire tree.

15. A. Whether you rebuild the index offline or online, you temporarily need twice as much disk space. If you rebuild the index online, you also need disk space to support a journal table to hold the intermediate changes to the index while the index is being rebuilt.

没找到

26.
18. Which of the following scenarios will never trigger Resumable Space Allocation? (Choose all that apply.)
A. SELECT * FROM HR.EMPLOYEES;
B. DELETE FROM HR.EMPLOYEES;
C. DROP TABLE HR.EMPLOYEES;
D. SELECT * FROM HR.DEPARTMENTS ORDER BY DEPARTMENT_NUMBER;
E. ALTER INDEX HR.EMP_NAME_IX REBUILD ONLINE;
F. ALTER INDEX HR.EMP_NAME_IX REBUILD;

18. A, C. Unless an ORDER BY clause forces a sort operation and uses up temporary space, a SELECT command will not otherwise trigger Resumable Space Allocation. Dropping a table does not use any additional disk space in a tablespace; it frees up disk space. Therefore, it will not trigger Resumable Space Allocation.

见第10课
---
A 查询可能发生ora-1555错误, 无法恢复
---

27.
19. An AFTER SUSPEND trigger can be defined at what level? (Choose all that apply.)
A. Instance
B. Schema
C. Table
D. Session
E. Database

19. B, E. The AFTER SUSPEND system-level trigger can be defined only at the database level or at the schema level.

教材11-25
"Users can register triggers for this event at both the database and schema level."

-fin-

Sunday, June 7, 2009

1z0-043 lesson 10 Managing Schema Objects

1z0-043 lesson 10

完成
有好多概念都不清楚, IOT, CLuster

是否显示我的答案
是否显示书上的答案和我的注释

1.
24. You are designing an application for a database 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?
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

D
概念不清

D

其它选项都不行

教材10-17

2.
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

BD

教材10-24

A 取消使用不就是no monitoring吗, alter index ... nomonitoring usage;

SQL> select * from v$object_usage;

no rows selected

SQL> alter index ind_t1_oid monitoring usage;

Index altered.

SQL> select * from v$object_usage;

INDEX_NAME                     TABLE_NAME                     MON USE
------------------------------ ------------------------------ --- ---
START_MONITORING    END_MONITORING
------------------- -------------------
IND_T1_OID                     T1                             YES NO
06/07/2009 05:04:56


SQL> select object_id from t1 where object_id=1;

no rows selected

SQL> select * from v$object_usage;

INDEX_NAME                     TABLE_NAME                     MON USE
------------------------------ ------------------------------ --- ---
START_MONITORING    END_MONITORING
------------------- -------------------
IND_T1_OID                     T1                             YES YES
06/07/2009 05:04:56


SQL> alter index ind_t1_oid nomonitoring usage;

Index altered.

SQL> select * from v$object_usage;

INDEX_NAME                     TABLE_NAME                     MON USE
------------------------------ ------------------------------ --- ---
START_MONITORING    END_MONITORING
------------------- -------------------
IND_T1_OID                     T1                             NO  YES
06/07/2009 05:04:56 06/07/2009 05:05:44


SQL>

C alter index即可

3.
35. While designing your database, you have created the EMPLOYEES table as an index-organized table(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.

B

PDF教材13-37

若要在IOT上建位图索引, 必须建映射表(mapping table)
映射表保存IOT的逻辑rowid, 位图索引指向的映射表, 这样建立起位图索引和IOT表的关系(好像是这个意思)

4.
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.

A
得试试

C

看错了

PDF教材13-7

5.
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?

D

PDF教材13-17
"Only segments in Automatic Segment Space Managed (ASSM) tablespaces can be shrunk"

6.
59. 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.

A
不知道

A

不会立即报错

7.
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

CE

ALTER INDEX ... REBUILD [ONLINE]也比re-create快, 但是索引是坏的, 只能重建
E. 如果强制打开了日志记录, NOLOGGING也不管用

8.
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.

A

不能这样设置

9.
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?

A. Memory Advisor
B. Segment Advisor
C. SQL Tuning Advisor
D. SQL Access Advisor
E. Automatic Database Diagnostic Monitor (ADDM)

B

教材10-22

10.
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 index.
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.

BE
好像做过,OCA的题?

BE

做过

11.
102. 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)

D
索引组织表的高可用性是什么来着?

D

教材10-10
"Have higher availability because table reorganization does not invalidate secondary indexes"

12.
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

D

PDF教材13-10
"When a resumable statement encounters a correctable error, the system internally generates the AFTER SUSPEND system event."

13.
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)

C
刚做过

C

无需解释

14.
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
E. Global temporary table
F. Index-organized table (IOT)

A

B 这个好像没什么关系
C 外部表不能被修改
D,F 聚簇表和索引组织表都不适用于关键字段频繁修改的情况.
E 临时表的作用范围只对当前会话

15.
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. 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

E

PDF教材13-22
"During the compaction phase, locks are held on individual rows containing the data. This causes concurrent DML operations such as updates and deletes to serialize on these locks."

16.
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.
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.

E

太容易了反而觉得有什么蹊跷

17.
127. 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.

B
不知道

A

答案错了,应该是B
PDF教材13-38
"To rebuild the mapping table, it must be dropped and re-created."
问题:
alter table xxx move nomapping;
alter table xxx move mapping table;
会锁表吗?

映射表也不能被收缩(shrinking), PDF教材13-17

18.
142. View the Exhibit and examine the characteristics 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? Exhibit:

A. Segment Advisor
B. SQL Access Advisor
C. SQL Tuning Advisor
D. Memory Advisor
E. Automatic Database Diagnostic Monitor (ADDM)

A
怎么都是一样的题,一样的图?

A

重复了

19.
149. You lost the index tablespace in your database. You decided to re-create the index tablespace and the indexes in the tablespace. What methods can you use to re-create the indexes? (Choose all that apply.)
A. SQL scripts
B. Recovery Manager (RMAN) script
C. Data Pump
D. SQL*Loader
E. Flashback database

ABC
没看懂

AC

破题, B为什么不行
A 肯定行
B 可以恢复表空间, 包括了其中的索引
C 可以从导出文件中导出建索引的SQL语句

20.
154. Exhibit: View the Exhibit and examine the properties of the USERS tablespace. You execute the following statement to shrink the TRANS table existing on the USERS tablespace:
SQL> ALTER TABLE trans SHRINK SPACE CASCADE;
Which objects would be affected by this command? (Choose all that apply.)

ABCDE
不会做.图不对题.

CD

PDF教材13-21
"If CASCADE is specified, the shrink behavior is cascaded to all the dependent segments that support a shrink operation, except materialized views, LOB indexes, IOT mapping tables, and overflow segments."

21.
182. In which two conditions are resumable statements suspended? (Choose two.)
A. when a user exceeds the space quota
B. when a user session is terminated
C. when a table that is being accessed by the current transaction is not found
D. when a user manually suspends the statement
E. when the maximum extents of a segment is reached

AE

PDF教材13-7
无法手工挂起一个会话

=====

22.
28. You create a new table, populate it, and build several indexes on the table. When you issue a query against the table, the optimizer does not choose to use the indexes. Why might this be?
A. The indexed columns are not used in the where clause of the query.
B. There are no statistics available for the table.
C. There is a small number of rows in the table.
D. The query contains a hint instructing the optimizer not to use the indexes.
E. All of the above.

28. E. Because you aren’t given enough information to determine the availability of statistics, nor are you privy to the query itself, you can’t rule out any valid possibilities. All of the choices listed are valid reasons why the optimizer might choose to not use an available index. See Chapter 8 for more information.

A. 没查询这个字段
B. 没有统计信息
C. 记录数很少, 无需使用索引
D. 提示强制改变了执行计划

23.
29. You are concerned about keeping statistics up-to-date for the NOODLE table, a table that is the target of frequent DML operations. In response, another DBA issues the command ALTER TABLE NOODLE MONITORING. What is the result of this action? (Choose the best answer.)
A. The action resolves the issue.
B. The action has no effect.
C. The action only partially resolves the issue.
D. The action has a detrimental effect regarding the issue.
E. None of the above.

29. B. The MONITORING option of the ALTER TABLE command is deprecated in Oracle Database 10g. While it will not produce an error, it is treated as no operation by Oracle. Automatic DML monitoring is used instead. See Chapter 8 for more information.

C 我记着我选C来着, 怎么成D了. 10G不用monitoring, 设不设置都一样

SQL> select monitoring from user_tables where table_name='T1';

MON
---
YES

SQL> alter table t1 nomonitoring;

Table altered.

SQL> select monitoring from user_tables where table_name='T1';

MON
---
YES

SQL>

见Automatically Collecting Statistics on Tables(http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tables.htm#sthref2270)
Formerly, you enabled DBMS_STATS to automatically gather statistics for a table by specifying the MONITORING keyword in the CREATE (or ALTER) TABLE statement. Starting with Oracle Database 10g, the MONITORING and NOMONITORING keywords have been deprecated and statistics are collected automatically. If you do specify these keywords, they are ignored.

D detrimental是有害的意思

24.
47. Consider the index HR.IDX_PK_EMP on the table HR.EMPLOYEES and the following ALTER INDEX command:
ALTER INDEX HR.IDX_PK_EMP COALESCE;
Which of the following commands accomplishes the same task as this command? (Choose the best answer.)
A. ALTER TABLE HR.EMPLOYEES SHRINK SPACE CASCADE;
B. ALTER TABLE HR.EMPLOYEES SHRINK SPACE;
C. ALTER TABLE HR.EMPLOYEES SHRINK SPACE COMPACT;
D. ALTER INDEX HR.IDX_PK_EMP REBUILD;

D
A也行?

25.
48. Which type of queue is supported by sorted hash clusters?
A. DEQUE
B. LIFO
C. FIFO
D. A queue represented by a two-way linked list

C
不会

48. C. Sorted hash clusters are similar to standard hash clusters, except that they store data sorted by non-primary key columns and make access by applications that use the rows in a first in, first out (FIFO) manner very efficiently; no sorting is required. Chapter 13 describes how sorted hash clusters are created and used.

教材10-18
Calls are stored as they are made and processed later in a “first-in, first-out” order when bills are generated for each originating telephone number.

26.
50. Which of the following statements is not true about segment shrink operations?
A. The compaction phase of segment shrink is done online.
B. During the compaction phase, the entire segment is locked but only for a very short period of time.
C. When the second phase of segment shrink occurs, the high watermark (HWM) is adjusted.
D. User DML can block the progress of the compaction phase until the DML is committed or rolled back.
E. Using the COMPACT keyword, the movement of the HWM can occur later during non-peak hours by running the command without the COMPACT keyword.

27.
51. What is the purpose of the overflow area for an index-organized table (IOT)?
A. The overflow area helps to reduce row chaining in the IOT.
B. The overflow area allows you to store some or all of the non-primary key data in an IOT row in another tablespace, improving performance.
C. The overflow area is used when there is a duplicate value for the primary key in the IOT.
D. The overflow area stores the index information for indexed columns that are not part of the primary key.

51. B. If an IOT row’s data exceeds the threshold of available space in a block, the row’s data will be dynamically and automatically moved to the overflow area. For more information about index-organized tables, see Chapter 13.

PDF教材13-33

=====

28.
1. Which data dictionary view provides the recommended action, as a SQL statement, from the Segment Advisor?
A. DBA_ADVISOR_FINDINGS
B. DBA_ADVISOR_RECOMMENDATIONS
C. DBA_ADVISOR_ACTIONS
D. DBA_ADVISOR_RATIONALE

B
不确定, 或者是A?

1. C. The data dictionary view DBA_ADVISOR_ACTIONS contains the SQL statement(s) that the Segment Advisor supplies to implement its recommendation for segment maintenance. DBA_ADVISOR_FINDINGS contains the results of the analysis, but no SQL. DBA_ADVISOR_RECOMMENDATIONS presents one or more findings and the benefits for performing the recommendation. DBA_ADVISOR_RATIONALE provides a more detailed set of reasons why the recommendation should be implemented, along with the impact of not performing the recommendation.

见
Viewing Segment Advisor Results(http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/schema.htm#sthref2119)
DBA_ADVISOR_RECOMMENDATIONS(http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_3014.htm)
DBA_ADVISOR_ACTIONS(http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_3003.htm)

29.
2. Which of the following statements is not true about sorted hash clusters?
A. The new access path is used regardless of the type of predicate in the WHERE clause.
B. You are allowed to create indexes on sorted hash clusters.
C. The cost-based optimizer must be used to take advantage of the new access path.
D. Additional sorts are not necessary if you access the cluster by one of the lists of hash key columns.
E. More than one table can be stored in a sorted hash cluster.

A
不懂

2. A. The new access path in a sorted hash cluster is used only if an equality predicate is used.

教材10-17
见
15.9 Using Hash Clusters for Performance(http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/data_acc.htm#i7690)

30.
3. Consider the following scenario. The user SCOTT runs a query at 8:25 A.M. that receives an ORA-01555: Snapshot too old error after running for 15 minutes. An alert is sent to the DBA that the undo tablespace is incorrectly sized. At 10:15 A.M., the DBA checks the initialization parameter UNDO_RETENTION, and its value is 3600; the parameter is sized correctly. The DBA doubles the size of the undo tablespace by adding a second datafile. At 1:15 P.M., the user SCOTT runs the same query and once again receives an ORA-01555: Snapshot too old error. What happens next? (Choose the best answer.)
A. The DBA receives another alert indicating that the undo tablespace is still undersized.
B. The user SCOTT calls the DBA to report that the query is still failing.
C. The second datafile autoextends so that future queries will have enough undo space to complete when there is concurrent DML activity.
D. Resumable Space Allocation suspends the query until the DBA adds another datafile to the undo tablespace, and then the query runs to completion.

D
什么意思

3. B. Even if the size of the undo tablespace is adjusted after an undo space problem, only one alert is sent for each 24-hour period. Therefore, the only way that the problem will be resolved promptly is for SCOTT to call the DBA, because the DBA will not receive another alert until the next day when another query fails.

考的不是resumable啊, 考的是OEM报警
像答案说的那样, 24小时内重复的报警只发一次, 不太合理呀. 只有undo报警才这么处理?

见
Viewing Information About Undo(http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/undo.htm#sthref1510)
In addition to the proactive undo space alerts, Oracle Database also provides alerts if your system has long-running queries that cause SNAPSHOT TOO OLD errors. To prevent excessive alerts, the long query alert is issued at most once every 24 hours. When the alert is generated, you can check the Undo Advisor Page of Enterprise Manager to get more information about the undo tablespace.

31.
4. The background process __________ checks for tablespace threshold violation or clearance every __________ minutes.
A. MMON, 10
B. SMON, 10
C. TMON, 30
D. PMON, 15
E. MMON, 30

A
不知道

4. A. The new background process MMON checks for threshold violations every 10 minutes. An alert is triggered when the threshold is reached or is cleared.

见Other Background Processes(http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/process.htm#sthref1626)

32.
5. Which of the following initialization parameters influences the recommended redo log file size provided by the Redo Logfile Size Advisor?
A. LOG_CHECKPOINT_INTERVAL
B. OPTIMAL_LOGFILE_SIZE
C. FAST_START_IO_TARGET
D. FAST_START_MTTR_TARGET
E. None of the above

5. D. FAST_START_MTTR_TARGET specifies the desired time, in seconds, for instance recovery after a crash or an instance failure. Therefore, the Redo Logfile Size Advisor uses this value to determine the optimal log file size. OPTIMAL_LOGFILE_SIZE is not an initialization parameter but a column in the view V$INSTANCE_RECOVERY. The initialization parameter FAST_START_IO_TARGET specifies recovery at the I/O level, and LOG_CHECKPOINT_INTERVAL specifies the frequency of checkpoints in terms of redo log file blocks used.

PDF教材13-5
This column shows the redo log file size (in megabytes) that is considered to be optimal based on the current FAST_START_MTTR_TARGET setting. It is recommended that you set all online redo log files to at least this value.

33.
6. Which of the following is not a benefit of segment shrink?
A. Full table scans will take less time.
B. Better index access takes place because of a smaller b-tree.
C. Space is freed up for other database objects.
D. All chained rows are fixed.
E. Space below the HWM is released, and the HWM is moved down.

B
不确定. D?

6. D. While some chained rows may be fixed with segment shrink functionality, it is not guaranteed that all chained rows will be fixed, because not all blocks may be read in a segment shrink operation.

B 也对. shrink索引
D 见Space Reclamation(http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/mgmt_db.htm#sthref2240)
Although segment shrink reduces row chaining, and the Oracle Database recommends online redefinition to remove chained rows, the Segment Advisor actually detects certain chained rows that are above a threshold. For example, if a row size increases during an update such that it not longer fits into the block, then the Segment Advisor recommends that the segment be reorganized to improve I/O performance.
Note: The Segment Advisor does not detect chained rows created by inserts.

34.
7. The EM Database Control Segment Resource Estimation feature uses all the following characteristics of the proposed table except for which one?
A. Column datatypes
B. PCTUSED
C. PCTFREE
D. Column sizes
E. Estimated number of rows

7. B. Only PCTFREE is used in the calculation, because it is the amount of space to leave free in the block for updates to existing rows. PCTUSED is not needed unless the segment space management is not AUTO. In addition, extent sizes calculated by this feature help assess the impact on the tablespace where this segment will be stored.

教材10-21
For example, the estimation of a table size is based on the following data: column data types, column sizes, and PCTFREE. Extent information is also used to calculate the space allocation impact on the currently selected tablespace.

35.
8. Which of the following is not a benefit of sorted hash clusters? (Choose the best answer.)
A. Rows within a given cluster key value are sorted by the sort key(s).
B. The ORDER BY clause is not required to retrieve rows in ascending or descending order of the sort key(s).
C. Cluster key values are hashed.
D. Rows selected by a cluster key value using an equality operator are returned in ascending or descending order.

B
不知道

8. C. While cluster key values in a sorted hash cluster are hashed, this is also true of regular hash clusters, and therefore is not a benefit unique to sorted hash clusters.

B 教材10-17
Within each list, the rows are stored in the order specified by the sort key columns defined by the corresponding sorted hash cluster. This is also the default return order when querying the table by using the hash key columns in the predicate.

36.
9. In the following scenario, the DBA wants to reclaim a lot of wasted space in the HR.EMPLOYEES table by using the segment shrink functionality. Which of the following is the correct order of the steps?
1. ALTER TABLE HR.EMPLOYEES SHRINK SPACE;
2. ALTER TABLE HR.EMPLOYEES DISABLE ROW MOVEMENT;
3. ALTER TABLE HR.EMPLOYEES ENABLE ROW MOVEMENT;
4. ALTER TABLE HR.EMPLOYEES SHRINK SPACE COMPACT;
5. ALTER TABLE HR.EMPLOYEES SHRINK SPACE CASCADE;
A. 3, 4, 1, 5, 2
B. 4, 1, 3, 2, 5
C. 5, 2, 1, 3, 4
D. 4, 1, 2, 3, 5

A
第5步有疑问

37.
10. Which of the following calls to DBMS_SERVER_ALERT.SET_THRESHOLD will set the thresholds for the UNDOTBS1 tablespace to 60 percent and 90 percent? (Choose the best answer.)
A. dbms_server_alert.set_threshold(
dbms_server_alert.tablespace_pct_full,
dbms_server_alert.operator_ge, 60,
dbms_server_alert.operator_ge, 90,
1, 1, null,
dbms_server_alert.object_type_tablespace,
null);
B. dbms_server_alert.set_threshold(
dbms_server_alert.tablespace_pct_full,
dbms_server_alert.operator_le, 60,
dbms_server_alert.operator_le, 90,
1, 1, null,
dbms_server_alert.object_type_datafile,
'UNDOTBS1');
C. dbms_server_alert.set_threshold(
dbms_server_alert.tablespace_full,
dbms_server_alert.operator_ge, 60,
dbms_server_alert.operator_ge, 90,
1, 1, null,
dbms_server_alert.object_type_tablespace,
'UNDOTBS1');
D. dbms_server_alert.set_threshold(
dbms_server_alert.tablespace_pct_full,
dbms_server_alert.operator_ge, 60,
dbms_server_alert.operator_ge, 90,
1, 1, null,
dbms_server_alert.object_type_tablespace,
'UNDOTBS1');

D
猜的

10. D. The call to DBMS_SERVER_ALERT.SET_THRESHOLD must specify the metric TABLESPACE_ CT_FULL, the two thresholds, an object type of tablespace, and the tablespace name itself. Specifying NULL for the tablespace name will set the threshold for all tablespaces, not just the UNDOTBS1 tablespace.

见
94 DBMS_SERVER_ALERT(http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_salt.htm)

38.
11. Which of the following statements is not true about segment shrink operations? (Choose the best sanswer.)
A. Tables with ROWID-based materialized views are maintained.
B. Segment shrink is allowed only on segments whose space is automatically managed.
C. Heap-organized and index-organized tables can be shrunk.
D. ROW MOVEMENT must be enabled for heap-organized segments.
E. Chained rows may be repaired during a segment shrink operation.
F. Triggers are not fired during a segment shrink operation.

E
不知道

11. A. Because the ROWIDs are changed with a segment shrink operation, tables with ROWID-based materialized views cannot be shrunk unless the materialized views are dropped and re-created after the segment shrink operation.

PDF教材13-17
E 见Space Reclamation(http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/mgmt_db.htm#sthref2240)
Although segment shrink reduces row chaining, and the Oracle Database recommends online redefinition to remove chained rows, the Segment Advisor actually detects certain chained rows that are above a threshold. For example, if a row size increases during an update such that it not longer fits into the block, then the Segment Advisor recommends that the segment be reorganized to improve I/O performance.
Note: The Segment Advisor does not detect chained rows created by inserts.

Using the Segment Advisor(http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/schema.htm#sthref2102)
Note: The Segment Advisor flags only the type of row chaining that results from updates that increase row length.

39.
12. Which of the following is not a feature of the Segment Advisor within EM Database Control?
A. Growth trend analysis
B. Segment Resource Estimation
C. Finding candidates for segment shrink
D. Finding table segments with chained rows

D
估计是

12. D. The Segment Advisor is not used to find tables with chained rows, but instead is used for finding segments that are good candidates for segment shrink or may be growing too fast.

segment advisor可以发现chained rows呀, 见上一道题

40.
13. Which of the following conditions will trigger an additional sort on a sorted hash cluster? (Choose two.)
A. The ORDER BY clause specifies non-sort columns that are not indexed.
B. An ORDER BY clause is used in the query, although the sort may still fit in memory.
C. The cost-based optimizer is in effect.
D. The ORDER BY clause is omitted, and the WHERE clause does not reference the cluster key.
E. The ORDER BY clause specifies trailing sort columns.

A
不知道

13. A, E. If a query on a sorted hash cluster retrieves rows and an ORDER BY clause specifies either non-sort columns or a suffix of the sort columns, additional sorting is required, assuming that indexes are not defined on the columns in the ORDER BY clause.

E是说, ORDER BY字段的顺序跟sorted hash cluster指定的顺序不一样?

41.
14. Which of the following statements is not true about segment shrink operations in tablespaces with automatic segment space management?
A. Clustered tables cannot be shrunk.
B. LOB segments can be shrunk.
C. IOT mapping tables and overflow segments cannot be shrunk.
D. Tables with function-based indexes cannot be shrunk.
E. ROW MOVEMENT must be enabled for heap-based segments.

A
不知道

42.
15. Which of the following is a disadvantage of rebuilding an index instead of coalescing an index?
A. You temporarily need twice as much disk space to rebuild the index.
B. You cannot move the index to another tablespace.
C. The storage characteristics of the index cannot be changed.
D. The rebuild operation only coalesces index leaf blocks within each branch instead of re-creating the entire tree.

43.
16. Which of the following commands adds a member /logs/redo22.log to redo log file group 2?
A. ALTER DATABASE ADD LOGFILE ‘/logs/redo22.log’ TO GROUP 2;
B. ALTER DATABASE ADD LOGFILE MEMBER ‘/logs/redo22.log’ TO GROUP 2;
C. ALTER DATABASE ADD MEMBER ‘/logs/redo22.log’ TO GROUP 2;
D. ALTER DATABASE ADD LOGFILE ‘/logs/redo22.log’;

16. B. When adding log file members, specify the group number or specify all the existing group members.

嗯

44.
17. Which of the following is not a benefit of index clusters?
A. The tables in the cluster are always queried together.
B. Queries with an equivalence operator will perform better.
C. The tables in the cluster have little or no DML activity.
D. The child tables have roughly the same number of rows for each parent key in the parent table.

D
就这句最看不懂

17. B. Hash clusters, not index clusters, use a hashing function to find a row in a cluster and perform best for equivalence queries.

不懂. index cluster指的是cluster上建的索引?
都不懂

45.
18. Which of the following scenarios will never trigger Resumable Space Allocation? (Choose all that apply.)
A. SELECT * FROM HR.EMPLOYEES;
B. DELETE FROM HR.EMPLOYEES;
C. DROP TABLE HR.EMPLOYEES;
D. SELECT * FROM HR.DEPARTMENTS ORDER BY DEPARTMENT_NUMBER;
E. ALTER INDEX HR.EMP_NAME_IX REBUILD ONLINE;
F. ALTER INDEX HR.EMP_NAME_IX REBUILD;

AC
A应该不行吧

46.
19. An AFTER SUSPEND trigger can be defined at what level? (Choose all that apply.)
A. Instance
B. Schema
C. Table
D. Session
E. Database

19. B, E. The AFTER SUSPEND system-level trigger can be defined only at the database level or at the schema level.

多选
见Notifying Users: The AFTER SUSPEND System Event and Trigger(http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/schema.htm#sthref2075)
When a resumable statement encounter a correctable error, the system internally generates the AFTER SUSPEND system event. Users can register triggers for this event at both the database and schema level. If a user registers a trigger to handle this system event, the trigger is executed after a SQL statement has been suspended.

47.
20. Which of the following statements is not true about index-organized tables?
A. An index-organized table can have additional indexes defined on other columns in the table.
B. An index-organized table has both physical and logical ROWIDs.
C. An index-organized table without secondary indexes is stored in a single segment.
D. The space requirements for an index-organized table are reduced in part because the data is stored in the same segment as the index, and therefore no physical ROWID is required.

20. B. An index-organized table (IOT) does not have a physical ROWID; instead, a logical ROWID is constructed based on the value of the primary key.

C overflow就是另一个segment

=====

-fin-

谢太数据库

谢太的最爱

Blog Archive

Thursday, August 20, 2009

1z0-043 lesson 12 Automatic Storage Management

Tuesday, June 23, 2009

1z0-043 lesson 11 Managing Storage

Sunday, June 7, 2009

1z0-043 lesson 10 Managing Schema Objects

Followers

谢太数据库

订阅谢太数据库

谢太的最爱

Blog Archive

Thursday, August 20, 2009

1z0-043 lesson 12 Automatic Storage Management

Tuesday, June 23, 2009

1z0-043 lesson 11 Managing Storage

Sunday, June 7, 2009

1z0-043 lesson 10 Managing Schema Objects

Followers