转:1Z0-042记录(二)-CSDN博客

81. You want to refer the employee personal information stored in operating system(OS) files in EMPLOYEE

table. You plan to add a new column to EMPLOYEE table to achieve this.

Which data type would you use for the new column?

A) BLOB

B) CLOB

C) BFILE

D) LONG RAW

Editor’s notes:oracle has four big objects for lob type.it is blob，clob，nclob，bfile.

Blob Is used for storing image file.

Clob is used for storing large character data

Bfile is used for stroing external file for oracle.

Web site associated:

http://www.itpub.net/viewthread.php?tid=995557&extra=&page=1

82. You want to monitor and control the resource usage by sessions. You want to be warned automatically

when more than 100 sessions are opened with your database. What action would you take to achieve this?

A) Use the Database Resource Manager.

B) Set the limits in the profiles used by users.

C) Modify the SESSIONS initialization parameter.

D) Set the warning threshold for theCurrent Logons Countmetric.

Editor’s notes:Current Logons Count is system metric values of oracle. You can see this value by querying v$sysmetric.

SELECT * from v$sysmetric t WHERE t.METRIC_NAME='Current Logons Count';

83. In your database, the STATISTICS_LEVEL initialization parameter is set to BASIC. What is the impact of this setting?

A) The optimizer statistics are collected automatically.

B) Only the timed operating system (OS) statistics and plan execution statistics are collected.

C) The Oracle server dynamically generates the necessary statistics on tables as part of query optimization.

D) The snapshots for the Automatic Workload Repository (AWR) are not generated automatically.

E) Snapshots cannot be collected manually by using DBMS_WORKLOAD_REPOSITORY PACKAGE.

Editor’s notes:You can identify what have changed when you query the view(V$STATISTICS_LEVEL)

Answer B:plan execution statistics must be set when STATISTICS_LEVEL set ‘all’.

When the STATISTICS_LEVEL parameter is modified by ALTER SYSTEM, all advisories or statistics are dynamically turned on or off, depending on the new value of STATISTICS_LEVEL. When modified by ALTER SESSION, the following advisories or statistics are turned on or off in the local session only. Their system-wide state is not changed:

Timed statistics

Timed OS statistics

Plan execution statistics

88. Data files of which three tablespaces can be recovered by performing an open recovery? (Choose three.)

A) TEMP

B) UNDO

C) INDEX

D) SYSAUX

E) SYSTEM

Editor’s nots:temp tablespace does not need to recover.when you perform. open recovery, tablespace must be taken offline.So, except undo/system/temp tablespace ,all of other tablepsace can be recovered when database open.

95. Which two statements regarding archive log destinations are true? (Choose two.)

A) A maximum of 10 destinations can be specified.

B) The archive logs must be written to all the destinations.

C) The archive log files can be written only to local destinations.

D) The archiving information can be traced to the alert log file whenever the archiving to a destination is successful.

E) The number of archiving destinations must be equal to the number of archive processes

(ARCn).

Editor’s notes:The number of archiving processed must be relevant to the parameter value of LOG_ARCHIVE_MAX_PROCESSES.The default value of this parameter is 2.The actual number of archiver processes in use may vary subsequently based on archive workload. So,Answer E is worng.

103. Redo log files are not multiplexed in your database. Redo log blocks are corrupted in group 2, and

archiving has stopped. All the redo logs are filled and database activity is halted. Database writer has written

everything to disk. Which command would you execute to proceed further?

A) RECOVER LOGFILE BLOCK GROUP 2;

B) ALTER DATABASE DROP LOGFILE GROUP 2;

C) ALTER DATABASE CLEAR LOGFILE GROUP 2;

D) ALTER DATABASE RECOVER LOGFILE GROUP 2;

E) ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;

Editor’s notes:You can see the dedail description of this question in < Clearing a Redo Log File > of managing the redo log.

A redo log file might become corrupted while the database is open, and ultimately stop database activity because archiving cannot continue. In this situation the ALTER DATABASE CLEAR LOGFILE statement can be used to reinitialize the file without shutting down the database.

The following statement clears the log files in redo log group number 3:

ALTER DATABASE CLEAR LOGFILE GROUP 3;

This statement overcomes two situations where dropping redo logs is not possible:

·If there are only two log groups

·The corrupt redo log file belongs to the current group

If the corrupt redo log file has not been archived, use the UNARCHIVED keyword in the statement.

ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;

This statement clears the corrupted redo logs and avoids archiving them. The cleared redo logs are available for use even though they were not archived.

If you clear a log file that is needed for recovery of a backup, then you can no longer recover from that backup. The database writes a message in the alert log describing the backups from which you cannot recover.

Note:

If you clear an unarchived redo log file, you should make another backup of the database.

If you want to clear an unarchived redo log that is needed to bring an offline tablespace online, use the UNRECOVERABLE DATAFILE clause in the ALTER DATABASE CLEAR LOGFILE statement.

If you clear a redo log needed to bring an offline tablespace online, you will not be able to bring the tablespace online again. You will have to drop the tablespace or perform. an incomplete recovery. Note that tablespaces taken offline normal do not require recovery.

110. Which statement is true when you run the SQL Tuning Advisor with limited scope?

A) Access path analysis is not performed for the SQL statements.

B) SQL structure analysis is not performed for the SQL statements.

C) SQL Profile recommendations are not generated for the SQL statements.

D) The staleness and absence of the statistics are not checked for the objects in the query supplied to the SQL Tuning Advisor.

Editor’s notes:You can see the detail explaination of sql tuning advisor inb12411- 12 Automatic SQL Tuning

SQL Tuning Advisor provides options to manage the scope and duration of a tuning task. The scope of a tuning task can be set to limited or comprehensive.

If the limited option is chosen, the SQL Tuning Advisor produces recommendations based on statistics checks, access path analysis, and SQL structure analysis. SQL Profile recommendations are not generated.

If the comprehensive option is selected, the SQL Tuning Advisor carries out all the analysis it performs under limited scope plus SQL Profiling. With the comprehensive option you can also specify a time limit for the tuning task, which by default is 30 minutes.

The example is :

dbms_sqltune.create_tuning_task (

sql_text => l_sql,

user_name => 'SYS',

scope => 'COMPREHENSIVE',

time_limit => 120,

task_name => 'TEST'

);

113. You want to perform. a backup of your database to tape. Which backup format can you use?

A. backup set only

B.image copyonly

C. only user-managed backup

D. both image copy and backup set

E. only incremental image copy backup

Editor’s notes:image copy is just like os copy command. It copy thesingle datafile, archived redo log file, or control file.The difference of image copy and backup set shows fllowed:

1，在存储到磁盘上之前,backup set可以利用oracle自带的二进制压缩算法对数据文件和归档日志进行压缩，而image copy不行。

2，backup set不会包括空闲块，如果一个块从未被写入数据，则rman在进行的时候，会忽略这些空块，而image copy则不会，所以采用backup set通常比image copy要快而且占用空间更小。

3，增量备份可以通过backup set实现，不能通过image copy实现。

4，如果你为磁带安装了rman驱动的话，可以直接备份backup set到磁带，而image copy不能直接备份到磁带。

5，两者都可以通过rman转储，但backup set不能通过操作系统级别来生成和转储。

6，rman可以检测2者的是否损坏（corruption），通过操作系统工具进行image copy备份的时候，不能检查corruption.

proxy copy can be backuped into tape.But proxy copy can backup controlfile and spfile.

116. You specified extent management as local for a tablespace. How will it affect space management in the tablespace?

A) All the extents will be of the same size.

B) Bitmap will be used to record free and allocated extents.

C) Free extents will be managed by the data dictionary tables.

D) The tablespace will be system managed and the users cannot specify the extent size.

Editor’s notes:

Answer A:lmt makes the extents auto allocate.the minimum value is 8K,and the largest is 64K

Answer D:when you create tablesapce,you can configure the uniform. extent by using this statement:

CREATE TABLESPACE lmtbsb DATAFILE 'G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\TEMP2.DBF' SIZE25M

EXTENT MANAGEMENT LOCAL UNIFORM. SIZE 128K;

The above statement use lmt and assm.So, as you see, users can specify the extent size.

120. While planning to manage more than one database in your system, you perform. the following activities:

1. Organize different categories of files into independent subdirectories.

2. Use consistent naming convention for database files.

3. Separate administrative information pertaining to each database.

Which option corresponds to the type of activities performed by you?

A) Oracle Managed Files

B) Oracle Grid Architecture

C) Optimal Flexible Architecture

D) Oracle database architecture

Editor’s notes:The OFA standard is a set of configuration guidelines created to ensure fast, reliable Oracle databases that require little maintenance. This appendix contains the following sections:

OFA is designed to:

·Organize large amounts of complicated software and data on disk, to avoid device bottlenecks and poor performance

·Facilitate routine administrative tasks such as software and data backup, which are often vulnerable to data corruption

·Facilitate switching between multiple Oracle databases

·Adequately manage and administer database growth

·Help eliminate fragmentation of free space in the data dictionary, isolate other fragmentation, and minimize resource contention

Oracle Grid Architecture is used for rac

122. One of the redo log members in your database is lost. You queried V$logfile for further details. What would be the

member's status?

A. LOST

B. INVALID

C. EXPIRED

D. DELETED

E. CORRUPT

F. UNKNOWN

G. OBSOLETE

Editor’s notes:v$Logfile.status has four values.they are:

INVALID - File is inaccessible

STALE - File's contents are incomplete

DELETED - File is no longer used

null - File is in use

124.You want to set up the Oracle Database10ginstallation to follow the French language, withFranceas the territory, and the date to be displayed in French for all users' sessions. Which environmental variable would you set to achieve this objective?

A) NLS_LANG

B) NLS_LANGUAGE

C) NLS_TERRITORY

D) NLS_CHARACTERSET

E) NLS_DATE_LANGUAGE

F) NLS_LENGTH_SEMANTICS

G) NLS_NCHAR_CHARACTERSET

Editor’s notes:nls_lang is os parameter name.It consist of three different values:

NLS_LANG参数由以下部分组成:

NLS_LANG=_.

NLS_LANG各部分含义如下:

LANGUAGE指定:

-Oracle消息使用的语言

-日期中月份和日显示

TERRITORY指定

-货币和数字格式

-地区和计算星期及日期的习惯

CHARACTERSET:

-控制客户端应用程序使用的字符集

这三个部分可从nls_database_parameter视图中得到：

select * from nls_database_parameters where parameter in ('NLS_LANGUAGE', 'NLS_TERRITORY','NLS_CHARACTERSET');

web site associated:

http://junmail.**.com/blog/137996

http://topic.csdn.net/u/20090513/17/54cf483c-161e-4cfb-be5b-d0d779aa095e.html

http://www.blogjava.net/pengpenglin/archive/2008/04/23/195096.html

128. In the PROD database you have granted the RESUMABLE system privilege to the CONNECT role.

Resumable space operation has been enabled for all user sessions. You want users NOT to be aware of any

kind of space-related problems while performing transactions. Instead, you want the problem to be resolved

by a database trigger automatically. Which combination of triggering time and event would you use to achieve

this objective?

A) AFTER CREATE

B) AFTER SUSPEND

C) BEFORE CREATE

D) AFTER TRUNCATE

E) BEFORE SUSPEND

F) AFTER INSERT OR DELETE OR UPDATE

G) BEFORE INSERT OR DELETE OR UPDATE

Editor’s notes:There are four types of triggers.

Row Triggers and Statement Triggers

BEFORE and AFTER Triggers

INSTEAD OF Triggers ---used for views

Triggers on System Events and User Events

You can see the The detail description in <Oracle9i Database Concepts Part Number A96524-01>

This question is explain in this web site:

http://yangtingkun.itpub.net/post/468/14699

131. Which two statements are true regarding the SGA_TARGET initialization parameter? (Choose two.)

A) It can be increased up to the value of the SGA_MAX_SIZE parameter.

B) Increasing the value of SGA_TARGET up to the value of SGA_MAX_SIZE disables the automatic shared memory management feature.

C) Reducing the value of the SGA_TARGET parameter takes away memory from both autotuned and manually sized components.

D) Increasing the value of the SGA_TARGET parameter distributes the increased memory among all the autotuned components.

Editor’s notes:Dynamic Modification of SGA_TARGET

The SGA_TARGET parameter can be increased up to the value specified for the SGA_MAX_SIZE parameter, and it can also be reduced. If you reduce the value of SGA_TARGET, the system identifies one or more automatically tuned components for which to release memory. You can reduce SGA_TARGET until one or more automatically tuned components reach their minimum size. Oracle Database determines the minimum allowable value for SGA_TARGET taking into account several factors, including values set for the automatically sized components, manually sized components that use SGA_TARGET space, and number of CPUs.

The change in the amount of physical memory consumed when SGA_TARGET is modified depends on the operating system. On some UNIX platforms that do not support dynamic shared memory, the physical memory in use by the SGA is equal to the value of the SGA_MAX_SIZE parameter. On such platforms, there is no real benefit in setting SGA_TARGET to a value smaller than SGA_MAX_SIZE. Therefore, setting SGA_MAX_SIZE on those platforms is not recommended.

On other platforms, such as Solaris and Windows, the physical memory consumed by the SGA is equal to the value of SGA_TARGET.

When SGA_TARGET is resized, the only components affected are the automatically tuned components for which you have not set a minimum value in their corresponding initialization parameter. Any manually configured components remain unaffected.

145. In your Oracle10gdatabase, you have scheduled a job to update the optimizer statistics at 05:00 pm

every Friday. The job has successfully completed. Which three pieces of information would you check to

confirm that the statistics have been collected? (Choose three.)

A) average row size

B) last analyzed date

C)size of table in bytes

D) size of table in database blocks

E) number of free blocks in the free list

F) number of extents present in the table

Editor’s notes:I am not sure why answer C is worng.I think that in ,It has told that table statistics contains three part of component:

·Number of rows

·Number of blocks

·Average row length

So, size of tablein bytesis not correct.

http://download.oracle.com/docs/cd/E11882_01/server.112/e10821/stats.htm#PFGRF94712

16.5.1Verifying Optimizer Statistics

http://download.oracle.com/docs/cd/E11882_01/server.112/e10821/sql_overview.htm#PFGRF94815

146. View the Exhibit to observe the roles assigned to the SCOTT user.

Which statement is true about the assignment of the SELECT_CATALOG_ROLE role to the SCOTT user?

A) The user cannot use the role at all.

B) The user can grant the role to other users.

C) The user needs to enable the role explicitly.

D) The user can start using the role immediately.

Editor’s notes:the knowlage of role is what you must learn.You must see the oracle release for that.

Oracle9i Database Administrator's GuidePart Number A96521-01Managing User Privileges and Roles

Oracle® Database SQL Reference10gRelease 1 (10.1)Part Number B10759-01SET ROLE

Specifying Default Roles

When a user logs on, Oracle enables all privileges granted explicitly to the user and all privileges in the user's default roles.

A user's list of default roles can be set and altered using the ALTER USER statement. The ALTER USER statement allows you to specify roles that are to be enabled when a user connects to the database, without requiring the user to specify the roles' passwords. The user must have already been directly granted the roles with a GRANT statement. You cannot specify as a default role any role managed by an external service including a directory service (external roles or global roles).

The following example establishes default roles for user jane:

ALTER USER jane DEFAULT ROLE payclerk, pettycash;

You cannot set a user's default roles in the CREATE USER statement. When you first create a user, the user's default role setting is ALL, which causes all roles subsequently granted to the user to be default roles. Use the ALTER USER statement to limit the user's default roles.

If you create a role,but you does grant the role to user whih no default role.When the user log on,it can not ues the privilege of the role except the user enable the role after it log on;

This is my test:

1)All system privileges and schema object privileges that permit a user to perform. a DDL operation are usable when received through a role.(通过角色得到的DDL权限在DDL操作中是可用的)

2)All system privileges and object privileges that allow a user to perform. a DML operation that is required to issuse a DDL statement are not usable when received through a role.(通过角色得到的DML权限在DDL操作中不可用). And this may explain why you grant select on table to role. And then you grant role to ueer.But when you create procedure included the statement ”select count(*) from table ”, it get error.

156.You want to be notified when the space usage of an existing critical tablespace has reached 75% of the

allocated space. Which option would you use to achieve this?

A. run a procedure to check the tablespace space usage

B. define the warning threshold for the tablespace at 75% in Database Control

C. create a trigger to be executed when tablespace space usage reaches 75%

D. submit a job by using DBMS_JOB package to check the free space in the tablespace at regular intervals

E. define the warning threshold to be 75% for the tablespace by ALTER TABLESPACE .. ADD THRESHOLD command

Editor’s notes:system trigger contains:

System events such as startup, shutdown, and error messages

User events such as logon and logoff

158. Exhibit:View the Exhibit to see the information on existing tablespaces of the PROD database. You observe that a large volume of INSERTs and DELETEs are happening on the TRANS table residing in the USERS tablespace, and you suspect that the TRANS table is fragmented. Which advisory component would you refer to, in order to find the information about table fragmentation?

A. Memory Advisor

B. Segment Advisor

C. SQL Tuning and Access Advisors

D. Automatic Workload Repository (AWR)

E. Automatic Database Diagnostic Monitor (ADDM)

Editor’s notes:witch advisor that oracle can use is display byDBA_ADVISOR_DEFINITIONS. Segment advisor is belonged to it. You can use dbms_advisor package to create a task for segment advisor.the method is showed followed:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2049277600346543592

161. Your database is not configured for session failover. Your tnsnames.ora file contains the following

details:

test.us.oracle.com=

(DESCRIPTION=

(ADDRESS_LIST=

(LOAD_BALANCE=off)

(FAILOVER=ON)

(ADDRESS= (PROTOCOL=tcp) (HOST=test1-server) (PORT=1521))

(ADDRESS= (PROTOCOL=tcp) (HOST=test2-server) (PORT=1521)))

(CONNECT_DATA= (SERVICE_NAME=test.us.oracle.com)))

Which feature is enabled in this case?

A) Load balancing

B) Instance failover

C) Database failover

D) Connect-time failover

E) Transparent Application Failover (TAF)

Editor’s notes:what’s the difference between connect_time failover and taf? See fllowed:

FAILOVER (Connect-Time Failover):

The default value is “on” and in tnsnames.ora, “FAILOVER=ON” will make this usable.

Taf:

(FAILOVER_MODE=

(TYPE=select)

(METHOD=basic))))

This is what the taf’s content.

165. Your database is functional with peak load for one hour. You want to preserve the performance statistics collected during this period so that they can be used for comparison while analyzing the performance of the database in the future. What action would you take to achieve this task?

A) Set the STATISTICS_LEVEL initialization parameter to ALL.

B) Create a baseline on a pair of snapshots that have the statistics of the peak-load period in the database.

C) Decrease the snapshot interval in the AWR to collect more number of snapshots during the peak-load period.

D) Set the snapshot retention period in the Automatic Workload Repository (AWR) to zero to avoid the automatic purging of the snapshots.

Editor’s notes:what is the baseline?

Abaseline contains performance data from a specific time period that is preserved for comparison with other similar workload periods when performance problems occur. The snapshots contained in a baseline are excluded from the automatic AWR purging process and are retained indefinitely.

awr baseline在我看来就是保留下一段时间的snapshot不被删除，比如说每天peak time，然后在将来run awrrpt.sql来分析这段snapshots。

How to create and drop baseline?

Create baseline:

BEGIN

DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (start_snap_id => 270,

end_snap_id => 280, baseline_name => 'peak baseline',

dbid => 3310949047, expiration => 30);

END;

Drop baseline:

BEGIN

DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (baseline_name => 'peak baseline',

cascade => FALSE, dbid => 3310949047);

END;

After you create baseline,you can see the information by querying the view “dba_hist_baseline”.

166. Which three statements are true regarding the fine-grained auditing (FGA)? (Choose three.)

A) FGA is possible on SELECT statements only.

B) The audit trail for FGA is stored in the FGA_LOG$ table.

C) The audit trail for FGA is stored in the AUD_LOG$ table.

D) FGA enables a SQL predicate to define when to audit an event.

E) FGA audits DELETE statements only when audit columns are specified.

F) FGA includes the SQL statement used by the user as part of the audit event entry.

Editor’s notes:Fine grained auditing extends Oracle standard auditing capabilities by allowing the user to audit actions based on user-defined predicates. It is independant of the AUDIT_TRAIL parameter setting and all audit records are stored in the FGA_LOG$ table, rather than the AUD$ table. The following example illustrates how fine grained auditing is used.

使用FGA策略实现精细审计的时候，不需要激活数据库审计，并且oracle会自动将审计结果放到数据字典表FGA_LOG$中。

174. You work as a database administrator for Supportcenter.cn. You decided to manage client and server

connections using Local Naming method in Supportcenter.cn. When you try to connect to the database you get the

following error:

ERROR:

ORA-12154:TNS:couldnot resolve service name

Which network configuration files would you look into to resolve this error?（Choose two）

A. snmp.ora

B. names.ora

C.spfile.ora

D.sqlnet.ora

E.listener.ora