oracle 042(101-130)

101. Your alert log file has the following information:

Tue May 25 17:43: 38 2004

ORA-00060: Deadlock detected. More info in file

/u01/app/oracle/admin/ORCL/udump/orcl_ora_3173.trc.

What would you do to ensure that database is still running correctly?

A) examine the trace file and kill the session that caused the deadlock

B) examine the trace file and kill both the sessions responsible for the deadlock

C) examine the trace file for details and ask the user who caused the deadlock to rollback the transaction

D) examine the trace file for details and remember that deadlocks are resolved automatically by Oracle database

E) examine the trace file for details and use the Undo Advisor to rollback the transaction that caused the deadlock

答案：D

解析：

ORA-00060: deadlock detected while waiting for resource

Cause: Transactions deadlocked one another while waiting for resources.

Action: Look at the trace file to see the transactions and resources involved. Retry if necessary.

在alert中看到了死锁，没关系，oracle会自动处理

102. Which two are valid locking levels that are used by transactions in an Oracle database? (Choose two.)

A) row level

B) block level

C) object level

D) schema level

E) database level

答案：AC

解析：事务级别的锁定:行, 对象

ORACLE 锁机制

http://blog.csdn.net/tianlesoftware/archive/2009/10/20/4696896.aspx

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;

答案：E

解析：

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. Youwill have to drop the tablespace or perform. an incomplete recovery. Note that tablespaces taken offline normal do not require recovery。

104. In the middle of a transaction, a user session was abnormally terminated but the instance is still up and

the database is open. Which two statements are true in this scenario? (Choose two.)

A) Event Viewer gives more details on the failure.

B) The alert log file gives detailed information about the failure.

C) PMON rolls back the transaction and releases the locks.

D) SMON rolls back the transaction and releases the locks.

E) The transaction is rolled back by the next session that refers to any of the blocks updated by the failed transaction.

F) Data modified by the transaction up to the last commit before the abnormal termination is retained in the database.

答案：CF

解析：当在一个transaction中时，会话不正常结束，这时PMON会自动将没有提交的数据rollback，commited的脏数据放到datafile中

F的意思如果一个SESSION,前面有COMMIT的部分是不回滚的。只有还未COMMIT部分被回滚了

PMON：进程监控进程

1: 进程负责在反常中断的连接之后的清理工作。

2: PMON监控其他oracle后台进程，如果有必要（和有可能）重新启动他们

3: 使用Oracle TNS监听器登记

105. The ST_INFO master table has millions of rows that are updated very rarely. It has a STATE_CODE column

that contains the value for 25 states. The table is frequently queried on the STATE_CODE column. Which type

of index would you suggest to improve the query performance?

A) B-tree

B) bitmap

C) reverse key

D) function based

答案：B

解析：

位图索引的优势

表具有数百万行且键列的基数较低时，也就是列的独特值极少时。

经常使用涉及 OR 运算符的多个 WHERE 条件组合进行查询时。

键列上存在只读活动或较少更新活动时。

106.You scheduled a backup by using the Schedule Backup option in Oracle Enterprise Manager. Which statement about the script created by Oracle Enterprise Manager is true?

A) It is a SQL script.

B) It is a PL/SQL file.

C) It is an operating system (OS) file.

D) It is a Recovery Manager (RMAN) script.

答案：D

解析：在em中使用backup scheduler采用的是RMAN脚本

107.View the Exhibit to see the source and target databases.

You have created a database link, devdb.us.oracle.com, between the databases PRODDB and DEVDB. You

want to import schema objects of the HR user using Oracle Data Pump from the development database,

DEVDB, to the production database, PRODDB. You execute the following command on the target database

server:

$impdp system/manager directory = DB_DATA

dumpfile = schemas.dat

schemas = hr

flashback_time = 2004-02-03 09: 00

The command fails, displaying the following error:

ORA-39001: invalid argument value

ORA-39000: bad dump file specification

ORA-31640: unable to open dump file "/home/oracle/schema/schemas.dat" for read

ORA-27037: unable to obtain file status

What would you do to overcome the error?

A) remove the dumpfile option in the command

B) remove the flashback_time option in the command

C) add the user, SYSTEM, to the schemas option in the command

D) add network_link = devdb.us.oracle.com option in the command

E) remove the schemas option and add the network_link = devdb.us.oracle.com option in the command

F) remove the dumpfile option and add the network_link = devdb.us.oracle.com option in the command

答案：F

解析：通过db_link导schema,不需要dumpfile

示例：http://space.itpub.net/785478/viewspace-570901

108. Your database instance is started using the server parameter file (SPFILE). Control files are multiplexed

and stored on different disks. Because of a disk failure, you lost one of these control files. You replaced the

damaged disk. What is the correct sequence of steps that you would follow to recover the control file?

1. shut down the instance, if not already done.

2. Copy one of the remaining control files to a new location.

3. Change the value of the CONTROL_FILES initialization parameter to correspond to the new location of the

control files.

4. Start up the database instance to the NOMOUNT stage.

5. Recover the database to the point of failure of the control file.

6. Open the database.

A) 5,2,3,4

B) 1, 2,4,3,5

C) 2, 4,3,4,5

D) 4, 5,6,2,3

答案:B

解析：用排除法：必须先关闭数据库,才能复制控制文件

109. You plan to use static database registration for a new listener when you create it.What could be the two

reasons for this? (Choose two.)

A) More than one database is to be registered with the listener.

B) The users will connect the database by using the host naming method.

C) The Oracle Enterprise Manager is to be used to monitor an Oracle9i database.

D) The database that is to be registered with the listener is configured in shared server mode.

E) The listener is not configured on the default port of 1521 and the instance is not configured to register with a nondefault port.

答案：CE

解析：因为想用监听器连接用户和旧版本的数据库，就必须要静态注册数据库，而C选项是连9I，所以这个选项对了。 E选项说监听器不在1521端口，而实例默认是在这个端口找监听器。选项又说实例“is not configured to register with a nondefault port”，也就是说没有用动态注册去指定不在默认端口的监听器，所以这时就用静态注册了，即将实例的信息直接写在listener.ora中。

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.

答案:C

解析：如果选中“Limited（有限制）”选项，SQL 优化指导会根据统计信息检查、访问路径分析和 SQL 结构分析来生成建议案。

使用“Limited（限制）”选项不会生成SQL 概要文件建议（SQL Profile recommendations）。

SQL 概要文件：ATO 会验证它自身的估计值并收集辅助信息以消除估计错误。它使用辅助信息构建SQL 概要文件并提出创建SQL 概要文件的建议。创建 SQL 概要文件后，查询优化程序可使用这个文件生成合理优化的计划。

111. Your database is running under automatic undo management and the UNDO_RETENTION parameter is

set to 900 sec. You executed the following command to enable retention guarantee:

SQL> ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

What effect would this statement have on the database?

A) The extents in undo tablespace retain data until the next full database backup.

B) The extents containing committed data in the undo tablespace are never overwritten.

C) The extents which no longer contain uncommitted data in the undo tablespace are not overwritten for at least15 minutes.

D) The extents containing committed data in the undo tablespace are not overwritten until the instance is shut down.

答案;C

解析：UNDO_RETENTION=900s 在undo表空间里的extent不包括没有committed的数据，将会被保留15分钟

112. Your database is in NOARCHIVELOG mode and a logswitch happens every 20 minutes. On Thursday

morning, media failure resulted in corruption of a data file belonging to the TOOLS tablespace. Backups are

taken on every Sunday. What would you do in this situation?

A) recover using Oracle Flashback Database technology

B) restore the data files from backup and perform a complete recovery

C) restore only the corrupted data file and perform tablespace recovery

D) restore the data files from backup and perform cancel-based recovery

E) restore the entire database from the most recent backup and start the instance and open the database

答案：E

解析：在非归档模式下,只能恢复到最近备份的那一刻

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

A. backup set only

B. image copy only

C. only user-managed backup

D. both image copy and backup set

E. only incremental image copy backup

答案：A

解析：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.

114. View this parameter setting in your database:

DB_CREATE_FILE_DEST='D: \oracle\product\10.2.0\oradata\oracle'

You created a tablespace by using this command:

CREATE TABLESPACE USERS;

Which two statements are true about the USERS tablespace? (Choose two.)

A) The tablespace has two data files.

B) An error is reported and tablespace creation fails.

C) Data files are created with names generated by the instance.

D) The tablespace can be extended without specifying the data file.

E) Data files belonging to the USERS tablespace cannot be rename

答案：CD

解析：

当指定了该参数DB_CREATE_FILE_DEST='D:\oracle\product\10.2.0\oradata\oracle'后，当你再创建表空间时，数据文件会自动被创建到该文件下，同时数据文件的名字会由实例自动生成。

这就是Oracle Managed Files (OMF),DB_CREATE_FILE_DEST 定义数据文件和临时文件的默认文件系统目录的位置,DB_CREATE_ONLINE_LOG_DEST_n 定义重做日志文件和控制文件的创建位置DB_RECOVERY_FILE_DEST 定义 RMAN 备份位置.

115.Which two operations can be flashed back using the Flashback technology? (Choose two.)

A) DROP USER SMITH;

B) DROP TABLE EMPLOYEES;

C) DROP TABLESPACE USERS;

D) ALTER TABLE SALES_REP DROP PARTITION P1;

E) ALTER TABLE EMPLOYEES DROP COLUMN DESIG_ID;

答案：AB

解析：在FLASHBACK DATABASE的时候,是可以恢复删除的非系统用户的 B是flash drop table

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.

答案:B

解析：

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

extent management 有两种方式 extent management local (LMT); extent management dictionary (DMT)默认的是local

extent是本地管理，位图将用来记录空闲和占用区域

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;

117. Which three statements are true regarding the logical structure of the Oracle database? (Choose three.)

A) Each segment contains one or more extents.

B) Multiple tablespaces can share single data file.

C) A data block is the smallest unit of I/O for data files.

D) It is possible to have tablespaces of different block sizes in a database.

E) Each data block in the database always corresponds to one OS block.

答案：ACD

解析：区间(extent)是oracle的最小空间分配单元,而block是oracle的最小io操作单元。

Oracle以区间为单位将空间分配给对象段,而段内则是以block为单位进行空间使用和管理.

表空间(tableSpace) 段(segment) 盘区(extent) 块(block) 关系

http://blog.csdn.net/tianlesoftware/archive/2009/12/13/4962476.aspx

118. Users of HR schema complain about slower-than-normal performance. On investigation the DBA found

that maintenance was recently performed on some of the tables. The DBA traced the query that takes longer than normal to execute.

View the Exhibit exhibit_before.

After the DBA resolves the problem, the query performs normally.

View the Exhibit exhibit_after.

What action would the DBA have taken to resolve the performance problem?

A) analyzed the EMPLOYEES table to collect the current statistics

B) moved the EMPLOYEES table in to a locally managed tablespace

C) moved the EMPLOYEES table to another location in the same tablespace

D) reorganized the associated indexes for the EMPLOYEES table that were in an unusable state

E) moved the indexes associated with the EMPLOYEES table to the same tablespace where the EMPLOYEES table exists

答案：D

解析：从第一张图看到同样的查询走了一个全表扫描。所以我们有理由相信，索引无效了导致执行计划有问题了。
第二张图应该就是dba重建了该索引后，同样的sql走了索引。
an unusable state表示索引的状态。在user_indexes的status字段中体现。

119. You have to shut down the database instance with the ABORT option because of a hardware failure.

Which statement is true about the subsequent opening of the database?

A) The database would open normally.

B) The database would not open, and it would stop at mount stage.

C) The database would open after automatically performing instance recovery.

D) The database would not open, and you have to perform database recovery to open it.

答案：C

解析：如果实例异常关闭（宕机，shutdown abort），并且数据文件，控制文件，联机日志都没有丢失。在下次启动时，要利用联机日志的内容进行恢复，这种恢复就是实例恢复（Instance Recovery）。

Instance Recovery 主要包括3个阶段：

1） 根据联机日志内容进行Rollover。(前滚)

2） 打开数据库，提供服务

3） SMON 或者用户进程进行Rollback。(回滚)

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

答案：C

解析：Flexible 灵活的 architecture 体系结构

Oracle Grid Architecture is used for rac

Oracle的最佳灵活体系结构（Optimal Flexible Architecture，简称OFA），是指Oracle软件数据库文件及文件夹的命名约和存储位置规则。

使用最佳灵活体系结构，能够简化数据库系统的管理工作，使数据库管理员更加容易地定位文件或添加文件；还可以将Oracle系统的执行文件、管理文件、数据文件分别存储到不同的硬盘上，从而有效地使用用户系统中的所有存储空间、克服某个（些）硬盘存储空间的限制，合理分配存储负担，降低单个硬盘存储速度方面的瓶颈，提高整个系统的存储效率。

OFA的核心是一个命名机制

1、linux/unix下面对于mount点的命名采用/pm的方式

p：字符常量，通常是u m：固定长度，通常是2

例如/u01、/u02

2、BASE目录通常是/pm/h/u

h：常量，通常是app u：目录所有者，通常是oracle

例如/u01/app/oracle

3、ORACLE_HOME目录通常是/pm/h/u/product/V

V：版本号，例如9.2.0、10.2.0

新增的一些组件，例如asm、db_1都在这个目录下面

例如/u01/app/oracle/product/10.2.0

4、数据库特定的管理文件，例如启动参数文件、转储文件

/pm/h/u/admin/d/a

d表示数据库的名字 a表示子目录的名称，例如参数文件是pfile，用户目录是udump

例如/u01/app/oracle/admin/ora10g/pfile

5、关键文件通常是/pm/q/d

q通常是oradata 例如/u02/oradata/ora10g

控制文件通常是controln.ctl，n表示数据位数，通常是2

例如control01.ctl、control02.ctl

redon.log，n通常是2，例如redo01.log、redo02.log

数据文件tn.dbf，t表示表空间的名字，n通常是2

例如users01.dbf、users02.dbf等

121. What is the implication of setting the initialization parameter FAST_START_MTTR_TARGET to 0 in your database?

A) MTTR Advisor would be disabled.

B) Redo Log Advisor would be disabled.

C) Automatic tuning of checkpoint would be disabled.

D) Checkpoint information would not be written to the alert log file.

答案：C

解析：

FAST_START_MTTR_TARGET=0 不使用检查点自动调整，但是数据库还是会根据负载自动调整checkpoint发生的频率
FAST_START_MTTR_TARGET=大于0的某个值 以这个值为准
FAST_START_MTTR_TARGET不设置 使用检查点自动调整

http://www.eygle.com/archives/2006/04/oracle10gr2_auto_tune_checkpint.html

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

答案：B

解析：v$Logfile.status has four values.:

INVALID - File is inaccessible

STALE - File's contents are incomplete

DELETED - File is no longer used

null - File is in use

123. You require the Oracle server to manage the undo segments and space among various active sessions

automatically. You created an undo tablespace, UNDO_TBS1, in your database. Which two additional steps

would you perform to achieve this? (Choose two.)

A) Set the UNDO_RETENTION parameter to 900 or more.

B) Create an initial undo segment in the undo tablespace.

C) Enable the retention guarantee for the undo tablespace.

D) Set the UNDO_TABLESPACE parameter to UNDO_TBS1.

E) Set the UNDO_MANAGEMENT initialization parameter to AUTO.

答案：DE

解析：你建立了一个undo表空间，你希望你的oracle能自动管理，这时你需要设置UNDO_TABLESPACE的参数是你刚才建立表空间的名字，然后设置UNDO_MANAGEMENT为AUTO

124.You want to set up the Oracle Database 10g installation to follow the French language, with France as the

territory, and the date to be displayed in French for all users' sessions. Which 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

答案：A

解析：

nls_lang is os parameter name.It consist of three different values:

NLS_LANG参数由以下部分组成:

NLS_LANG=<Language>_<Territory>.<Clients Characterset>

NLS_LANG各部分含义如下:

LANGUAGE指定:

-Oracle消息使用的语言

-日期中月份和日显示

TERRITORY指定

-货币和数字格式

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

CHARACTERSET:

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

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

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

125. The HR user owns the EMP table. The HR user grants privileges to the SCOTT user by using this

command:

SQL> GRANT SELECT, INSERT, UPDATE ON emp TO scott WITH GRANT OPTION;

The SCOTT user executes this command to grant privileges to the JIM user:

SQL> GRANT SELECT, INSERT, UPDATE ON hr.emp TO jim;

Now the HR user decides to revoke privileges from JIM by using this command:

SQL> REVOKE SELECT, INSERT, UPDATE ON emp FROM jim;

Which statement is true after HR issues the REVOKE command?

A) The command succeeds and privileges are revoked from JIM.

B) The command fails because SCOTT still has privileges with him.

C) The command fails because HR cannot revoke the privileges from JIM.

D) Only HR can perform SELECT, INSERT, and UPDATE operations on the EMP table.

答案：C

解析：授予他人的对象权限在被授予者向第三人授予该权限时，最初授权者无法取消第三人的对象权限。但是如果最初授予的是系统权限，则最初授权人可以取消第三人的系统权限。

126. You define an alert to be raised when the USERS tablespace usage has reached 80% of the total space.

Which area would you refer to, in order to confirm that the alert has been raised due the event?

A) the alert.log file

B) the DBA_ALERTS view

C) the Database Control Home page

D) the Database Control performance page

E) the Database Control Maintenance page

答案：C

解析：EM首页上alert警告里有

127. A user wants to connect to the database instance from an application that is running on a remote machine.

Which tools should the DBA use to establish the required configuration to ensure that the user is able to

connect to the database instance? (Choose two.)

A) Data Pump

B) Oracle Net Manager

C) Oracle Enterprise Manager

D) Oracle Universal Installer (OUI)

E) Database Configuration Assistant (DBCA)

答案：BC

解析：可以使用EM和ORACLE NET MANAGER是用户从远程连接到数据库实例

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

答案：B

解析：

Resumable 可恢复的

Oracle提供了一种 方法，当对数据库执行操作时，出现分配存储空间失败的错误时，Oracle不是简单的返回错误信息，并回滚整个事务，而是将执行的语句置于悬挂状态，等待一段时间，在等待时间内，如果问题得到解决，则语句会继续执行下去，如果问题一直无法解决，则会报错并回滚。产生SUSPEND的前提是当前的session处于ENABLE RESUMABLE状态。而且发出的语句遇到下面三种错误：空闲空间不足、达到最大的MAXEXTENTS和达到用户的空间QUOTA限制。

下列语句可能被SUSPEND：

SELECT语句（使用临时空间超过空间限制）；DML语句；IMP/EXP；DDL语句。包括CREATE TABLE AS SELECT、CREATE INDEX、ALTER INDEX和CREATE MATERIALIZED等几种语句。

ALTER SESSION ENABLE RESUMABLE和ALER SESSION DISABLE RESUMABLE分别用来启用和禁用RESUMABLE模式。在ENABLE RESUMABLE时还可以通过使用TIMEOUT语句指明SUSPEND的时间。

Oracle提供了 DBMS_RESUMABLE包和AFTER SUSPEND触发器来管理并解决SUSPEND状态。当语句进行SUSPEND状态，不会把错误信息返回给执行语句的SESSION，但是会在警告日志 alert.log中记录错误，并会触发AFTER SUSPEND触发器（如果建立了的话）。这时也可以通过查询视图USER_RESUMABLE和DBA_RESUMABLE查询到处于SUSPEND状 态的语句。

试验如下

session1： SQL> CREATE TABLESPACE lh DATAFILE 'E:\ORACLE\PRODUCT\DATA\lh01.DBF' SIZE 3M;

SQL> create or replace trigger trigg_system after suspend on database begin dbms_resumable.set_timeout(10); end;

Sql> create table t_resumable tablespace lh as select *from dba_objects;

SQL> insert into t_resumable select *from t_resumable;

ORA-01653: 表 SYS.T_RESUMABLE 无法通过 128 (在表空间 LH 中) 扩展

SQL> alter session enable resumable;

注意到过了10秒之后才出错

--修改一下timeout时间（10秒有些短，来不及修改lh01.DBF的autoextend属性），单位是秒 SQL> create or replace trigger trigg_system after suspend on database begin dbms_resumable.set_timeout(40); end;

SQL> insert into t_resumable select *from t_resumable;

这时不报错了！

在另外一个session里修改

SQL> alter database datafile 2 autoextend on;

Sql>drop trigger trigg_system;

If a user registers a trigger to handle this system event, the trigger is executed after a SQL statement has been suspended.说明E错

129.View the Exhibit and examine the output.

Which statement describes the conclusion?

A) The users should use bind variables instead of literals in the query.

B) The dictionary cache is consuming more space than the library cache.

C) The shared pool size should be increased to accommodate the SQL statements.

D) Preparing indexes on the tables used in the SQL statements would improve the library cache performance.

答案：A

解析：查询了多个单一变量，可以使用绑定变量

130. In your database, the snapshot interval is set to 10 minutes for the Automatic Workload Repository (AWR).

The database instance is running for the past 30 days.

View the Exhibit to examine the Automatic Database Diagnostic Monitor (ADDM) task.

The ADDM task is performed every 10 minutes, except the last task.

What could be the reason for this?

A) The snapshot retention period for the AWR was increased before the last task.

B) The optimizer statistics for few objects in the database were locked before the last task.

C) The STATISTICS_LEVEL initialization parameter was changed to BASIC and reset to TYPICAL before the last task.

D) The optimizer statistics was collected manually by using the DBMS_STATS package before the last task.

答案：C

解析：statistics_level 参数是oracle9.2开始引入的一个控制系统统计参数收集的一个开关.一共有三个值:basic,typical,all.支持alter session,alter system 动态修改.如果要用statspack或者AWR收集系统性能统计数据.那么这个参数的值必须为typical或all.通常all是一个全面收集,包括 OS以及sql执行路径方面的一些统计信息,除非遇见非常严重的性能问题或在一些特殊的性能挣断方面才会用到statistics_level=all, 平常statistics_level=typeical已经足够诊断99%的性能问题了.

alter system set statistics_level=basic;

alter system set statistics_level=typical;

alter system set statistics_level=all;

or

alter session set statistics_level=basic;

alter session set statistics_level=typical;

alter session set statistics_level=all;

statistics_level=basic的情况下,oracle关闭了所有性能数据的收集,也就是如果要关闭AWR或statspack收集,只要设置alter system set statistics_level=basic;就行了;statistics_level=typical的时候,除了plan_executetion_statistics和OS Statistics不能收集外,其他的都可以收集,如要要收集这个两项,必须设置statistics_level=all;如果当statistics_level=ALL,系统收集所有的统计信息.