402.While querying the EMPLOYEES table,you receive an ORA-01578 message indicating block corruption in File# 1201 and Block# 1968. You analyze the table and the corruption is verified. Which RMAN command do you use to perform BMR and repair the corrupt block? B. RECOVER CORRUPTION LIST; C. RECOVER DATAFILE 1201 BLOCK 1968; 关于RMAN的块恢复。Block是oracle数据库最小恢复单位。鉴于此，BMR可以大大缩短恢复的时间，另外，在进行BMR时不需要停掉数据库，可以直接在数据库open状态对数据文件进行恢复，只是需要恢复的块被锁定无法访问，可以说对应用的影响非常小 单个坏块 29. --下面使用blockrecover来恢复坏块 30. RMAN> blockrecover datafile 6 block 130; 31. 32. Starting recover at 2013/08/28 17:22:25 33. using target database control file instead of recovery catalog 34. allocated channel: ORA_DISK_1 35. channel ORA_DISK_1: SID=24 device type=DISK 36. 37. channel ORA_DISK_1: restoring block(s) channel ORA_DISK_1: specifying block(s) 多个坏块 72. --下面直接使用blockrecover corruption list来恢复，如下所有刚刚被校验的坏块都会被恢复 73. RMAN> blockrecover corruption list; 74. 75. Starting recover at 2013/08/29 10:05:24 76. using channel ORA_DISK_1 77. 78. channel ORA_DISK_1: restoring block(s) 79. channel ORA_DISK_1: specifying block(s) to restore from backup set 80. restoring blocks of datafile 00006 403.To view the results of the most recent Automatic sql Tuning Advisor task,which sequence should you follow? A. EM Database home page,Software and Support,sql Advisors,Automatic sql Tuning Advisor B. EM Database home page,Advisor Central,Automatic sql Tuning Advisor C. EM Database home page,Support Workbench,Automatic sql Tuning Advisor D. Either B or C E. All of the above Answer: D 404.When creating a sql tuning set,which of the following steps allows the DBA to reduce the size of the sql set by selecting specific operators and values? D. Filter options Answer: D Creating a sql Tuning Set: Filter Options After the load method is selected,you can apply filters to reduce the scope of the sql statements found in the sql tuning set. While using filters is optional,it can be very beneficial due to the following: 405.To view the results of a manual sql Tuning Advisor task,which steps should the DBA take? A. From the Advisor Central home page,select the tuning task from the Advisor Tasks section. 406.Which of these appropriately describes恰当的描述 the results of a manual sql Tuning Advisor task? A. A list of sql statements and recommendations for tuning 407.Which of the following is a potential performance tuning recommendation from the sql Access Advisor? A. Create new indexes. B. Modify existing indexes. C. Implement partitioning on a nonpartitioned table. D. Create materialized views. E. All of the above Answer: E 408.Which statement most accurately describes the implementation of a sql Access Advisor recommendation? B. Individual sql Access Advisor recommendations can be scheduled for implementation. 单独的sql 访问顾问推荐，能够作为执行计划 Implementing the sql Access Advisor Recommendations A sql Access Advisor recommendation can range from a simple suggestion to a complex solution that requires partitioning a set of existing base tables and implementing a set of database objects such as indexes,materialized views,and materialized view logs. You can select the recommendations for implementation and schedule when the job should be executed. To implement the sql Access Advisor recommendations: 409.Which of the following represents the correct sequence of events for Database Replay? C. Capture,preprocess,replay,analyze 410.Which of these recommendations should be followed before capturing a workload? (Choose all that apply.) B. Make sure the replay and capture databases are similar in data content. C. Perform a clean shutdown and restart of the capture database before beginning a workload capture. 411.Which is true concerning Database Replay in an Oracle Real Application Cluster (RAC) database? E. None of the above. Answer: E RAC上的数据库回放 412.Performance divergence分歧 indicated in the Workload Replay report is most likely due to what? Performance divergence indicated in the Workload Replay report is most likely due to what? A. DML and sql statement results that do not match between the capture and replay systems B. When errors that occur in the capture system don‘t occur in the replay system C. Top sql statements D. Infrastructure or system-configuration differences E. Time-of-day differences between capture and replay systems Answer: D 在负载重放报告中显示性能偏离最有可能的原因是什么？ A.DML和sql语句的结果在捕获和重放系统之间不匹配 B.当捕获系统中所发生的错误不发生在重放系统 D.基础架构或系统配置差异 E.一天时间捕获和重放系统之间的差异 Answer: D 选项A不正确; 选项A不正确; DML和sql的结 果驱动数据分歧。 选项B不正确，因为发生错误分歧，而不是性能分歧，捕获系统中发生的错误时，不发生 在重播系统。 在捕获和重放系统排名在前的sql语句应该具有相同的行为，除非有数据分歧问题，所以C是 不正确的。 E是不正确的，因为一天的时间应该没有影响捕获和重放系统之间的差异，有可能是其他的工作 负载上运行捕获和重放系统有一个时间的天的趋势，可能会影响性能，但是这是一个无关的变量，而不是一 个原因。 413.Which of the following Oracle features is enabled by setting a nonzero value for the MEMORY_TARGET initialization parameter? D. Automatic Memory Management 414.By setting the value of MEMORY_TARGET to zero and setting the value of SGA_TARGET to a nonzero value,you will enable which of the following memory-management options? C. Automatic Shared Memory Management 415.For Oracle 11g,Oracle strongly recommends that you configure your database to use which of the following memory-management features? D. Automatic Memory Management 416.To manually configure the SGA components using Oracle Enterprise Manager Memory Advisor,you can set values for which of the following initialization parameters? (Choose all that apply.) A. DB_CACHE_SIZE B. SHARED_POOL_SIZE C. LARGE_POOL_SIZE D. JAVA_POOL_SIZ 417.When manually configuring the SGA,which of the following parameter changes requires an instance restart to take effect? E. SGA_MAX_SIZE 418.Using Oracle Enterprise Manager to set SGA pool values manually,for which of the following pools does Oracle EM offer advice to set the value appropriately? (Choose all that apply.) A. DB_CACHE_SIZE B. SHARED_POOL_SIZE 419.In Oracle 11g,by default which one of the following conditions implicitly enables Automatic PGA Memory Management? B. Configuring Automatic Shared Memory Management 420.Automatic PGA Memory Management eliminates消除 the need to manually configure which of the following initialization parameters? (Choose all that apply.) A. SORT_AREA_SIZE B. HASH_AREA_SIZE C. BITMAP_MERGE_AREA_SIZE D. CREATE_BITMAP_AREA_SIZE E. PGA_AGGREGATE_TARGET Answer: ABCD ABCD四者都是PGA的组成部分 PGA的一大部分被分配给Work Area，用来执行如下操作： 　　　　a.基于操作符的排序，group by、order by、rollup和窗口函数。 　　　　参数为sort_area_size 　　　　b.hash散列连接， 　　　　参数为hash_area_size 　　　　c.位图合并， 　　　　参数为bitmap_merge_area_size 　　　　d.位图创建， 　　　　参数为create_bitmap_area_size 　　　　e.批量装载操作使用的写缓存 （五）自动PGA管理 设置PGA_AGGREGATE_TARGET为非0，则启用PGA自动管理，并忽略所有*_area_size的设置。如sort_area_size,hash_area_size等。 默认为启用PGA的自动管理，Oracle根据SGA的20%来动态调整PGA中专用与Work Area部分的内存大小，最小为10MB。 用于实例中各活动工作区（work area）的PGA总量，为PGA_AGGREGATE_TARGET减去其他组件分配的PGA内存。得到的结果，按照特定需求动态分配给对应的工作区。 1）设置PGA_AGGREGATE_TARGET大小的步骤 a.设置PGA_AGGREGATE_TARGET为SGA的20%，对于DSS系统，此值可能过低。 b.运行典型的负载，通过oracle收集的pga统计信息来调整PGA_AGGREGATE_TARGET的值。 c.根据oracle的pga建议调整PGA_AGGREGATE_TARGET大小。 2）禁用自动pga管理 为向后兼容，设置PGA_AGGREGATE_TARGET为0，即禁用pga的自动管理。可使用关联的*_area_size参数调整对应工作区的最大大小。 bitmap_merge_area_size create_bitmap_area_size hash_area_size sort_area_size 421.When tuning Automatic PGA Memory Management,which of the following views will provide the information specified? A. The V$PGA_TARGET_ADVICE view shows the predicted cache hit-ratio improvement if you increase PGA_AGGREGATE_TARGET. 10g Oracle给出了一系列的自动优化的建议，PGA分配多大能给系统带来最大的性能？V$PGA_TARGET_ADVICE视图给出了很好的“预测”！ 看一下这个视图能给我们带来什么样的信息（视图中每个列都很有帮助）： sys@ora10g> SELECT pga_target_for_estimate / 1024 / 1024 "PGA(MB)",2 pga_target_factor,3 estd_pga_cache_hit_percentage,4 estd_overalloc_count 5 FROM v$pga_target_advice; PGA(MB) PGA_TARGET_FACTOR ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT ---------- ----------------- ----------------------------- -------------------- 10 .5 34 13 15 .75 34 13 20 1 100 13 24 1.2 100 13 28 1.4 100 13 32 1.6 100 3 36 1.8 100 0 40 2 100 0 60 3 100 0 80 4 100 0 120 6 100 0 160 8 100 0 12 rows selected. 通过上面的数据可以得到如下的结论： 1.第一列表示不同的PGA的具体值 2.第二列PGA_TARGET_FACTOR为“1”表示当前的pga_aggregate_target设置大小（其他数值都是以这个数据为基础的倍数），我这里是20M，通过pga_aggregate_target参数可以确认一下 sys@ora10g> show parameter pga_aggregate_target; NAME TYPE VALUE ----------------------- -------------------- ----------- pga_aggregate_target big integer 20M 3.第三列表示PGA的估算得到的Cache命中率的百分比 目前系统如果PGA为20M的时候，就可以达到100%的命中率 4.第四列如果为“0”表示可以消除PGA的过载 从上面的数据中可以得到，当PGA为36M的时候，可以消除PGA的过载。 5.综合以上的结论，我们最终可以将PGA的大小设置为36M。 sys@ora10g> alter system set pga_aggregate_target=36m; System altered. 6.调整后，再次查询一下v$pga_target_advice视图得到如下的建议信息，可以看到基本上已经满足现在的系统需求。 sec@ora10g> SELECT pga_target_for_estimate / 1024 / 1024 "PGA(MB)",2 pga_target_factor,3 estd_pga_cache_hit_percentage,4 estd_overalloc_count 5 FROM v$pga_target_advice; PGA(MB) PGA_TARGET_FACTOR ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT ---------- ----------------- ----------------------------- -------------------- 18 .5 94 2 27 .75 94 2 36 1 100 0 43.1992188 1.2 100 0 50.3994141 1.4 100 0 57.5996094 1.6 100 0 64.7998047 1.8 100 0 72 2 100 0 108 3 100 0 144 4 100 0 216 6 100 0 288 8 100 0 12 rows selected. 恭喜你，到这里，您一定已经会“看”这个v$pga_target_advice视图了。 422.To enable resumable space allocation for the instance,which of the following initialization parameters should you set to a nonzero value? D. RESUMABLE_TIMEOUT 一个SESSION在空间不够的情况下会出错误信息而且回滚，如果有这个参数，可以给你resumable_timeout的时间让你来补救 然后现在这时的终端命令就是在screen环境中，然后运行sqlplus，如果这时ssh断了，下次再ssh到数据库主机中可以运行screen -x osdba来进行到先前的screen的session中。 而当我们执行一个长时间的sql，有可能会出现表空间不足导致这个sql执行出错，这时可以在sql中设置： alter session enable resumable; alter session set resumable_timeout=72000; 这样设置后，如果出现表空间不足导致这个sql无法执行下去时，这个sql会挂住，加完空间后这个操作会继续，这样可以防止sql运行了一半而失败。 同时可以在dba_resumable视图中查看该会话的状态: select user_id,session_id,status,start_time,suspend_time,sql_text,error_number,error_msg from dba_resumable; 423.Which of the following describes how a distributed resumable transaction behaves? C. The resumable setting on the initiating session controls only that part of the transaction that occurs within the local instance; remote resumable settings determine the behavior of the distributed parts of the transaction. 下列哪项描述分布式可恢复事务行为？ A.发起会话的可恢复设置决定整个分布式事务的可恢复的条件。 B.发起实例的可恢复设置决定整个分布式事务的可恢复的条件。 C.发起会话的可恢复设置仅控制发生在本地实例的事务部分;远程可恢复设置确定事务的分布式 部分的行为。 Answer: C 分布式事务的远程可恢复暂停初始化参数应用到远程事务部分，适用于远程会话可恢复设置。另外，本地可恢复设置并不适用于分布式事务 的远程部分 424.Which of these components correctly identify the unique value of the NAME column in the DBA_RESUMABLE view? D. Username,session ID,instance number RESUMABLE_NAME Default: 'User USERNAME (USERID),Session SESSIONID,Instance INSTANCEID' The value for this parameter identifies the statement that is resumable. This value is a user-defined text string that is inserted in either the USER_RESUMABLE orDBA_RESUMABLE view to help you identify a specific resumable statement that has been suspended. This parameter is ignored unless the RESUMABLE parameter is set to true to enable resumable space allocation. 425.Which of the following are included in a transportable tablespace set? 可传输表空间集合(Choose all that apply.) A. The datafiles that make up a self-contained group of tablespaces required for copy C. An export of the tablespace Metadata 426.The following query will provide what information about transportable tablespaces for the current database? (Choose all that apply.) select d.platform_name ―Source‖,t.platform_name “ Compatible Targets‖,endian_format from v$transportable_platform t,v$database d where t.endian_format = (select endian_format from v$transportable_platform t,v$database d where d.platform_name =t.platform_name); A. The list of target platforms having the same endian字节存储顺序 format as the source database C. The list of target platforms that will not require endian conversion 下面的查询将提供关于“用于当前数据库的传输表空间” 什么样的信息？ A.具有和源数据库相同的尾数格式的目标平台的列表 C.不需要尾数转换的目标平台列表 SQL查询返回目标平台的列表，该平台在源和目标数据库之间有相同尾数格式且不需要RMAN转换。 427.When exporting Metadata for the transportable tablespaces,what is the correct next step after confirming endian format? 确定字节存储格式之后的步骤 B. Determine if the transportable set is self-contained. 确定表空间是自包含的 428.Which of the following are prerequisite先决条件 steps to transport a database? (Choose all that apply.) A. Query the V$TRANSPORTABLE_PLATFORMS view in the source database to determine if the intended destination is listed. 平台列表 B. Verify that there are no restrictions or limitations that the source or destination database may encounter. 限制条件 D. Determine if you will perform the conversion on the source or destination platform. 确定是否需要转换 429.Which of the following supplied functions is used to identify external tables,directories,and BFILES? B. DBMS_TDB.CHECK_EXTERNAL 对于在源平台执行转换操作而言，通常步骤如下： ·启动数据库到read only模式。 ·DBMS_TDB.CHECK_DB检查数据库是否可被传输 ·DBMS_TDB.CHECK_EXTERNAL检查外部对象 ·执行CONVERT DATABASE命令，例如： RMAN> convert database new database 'jsstts' 2> transport script 'e:\oratmp\script\ts.sql' 3> to platform 'Linux IA (32-bit)' 4> db_file_name_convert 'E:\ora10g\oradata\jssweb' 'e:\oratmp\oradata'; 得到下列文件： Ø 转换后的数据文件：已转换的待传输文件，本例中保存至：e:\oratmp\oradata'，文件名与源库中相同。 Ø transport script：在目标平台执行的创建数据库的脚本，本例中名为ts.sql，该文件内容仅供参考，你需要再根据实际情况修改其中的参数值。 Ø 客户端初始化参数文件：文件名及路径包含在CONVERT DATABASE的输出内容中，在输出结果的最后。文件中的参数值多继承自源库，你可以根据需要进行适当修改。 转换完成(此时可将源库置为read-write了)，然后将文件复制到目标数据库，在目标平台执行transport script，创建数据库即可。注意，如果前一步操作生成的文件复制到目标平台后路径发生修改，务必修改transport script文件中对应的参数值。 430.Which of the following is a prerequisite for running DBMS_TDB.CHECK_DB to a successful completion? C. The database must open in read-only mode 执行DBMS_TDB.CHECK_DB检查数据库是否满足迁移到目标平台的条件，是否有其他影响迁移的因素 431.Which of the following options describes Segment Shrink? A. Reclaims改造 space above and below the high-water mark高水位线 without using additional space 432.For which of the following can you use Segment Shrink? (Choose all that apply.) A. Heap tables C. Indexes D. Partitions and subpartitions 压缩段-shrink segment 限制条件： 1、不能对cluster、clustered table 或者任何有LONG列的对象使用这个语句 2、压缩段@R_301_472@有函数索引、位图链接索引的表 3、这语句不能压缩二级索引表的映射表，即使设置了CASCADE 4、不能对压缩表使用该语句 5、不能压缩on commit 类型的物化视图的主表，rowid物化视图必须在压缩操作之后重建 详解： oracle10g开始提供shrink的命令，要求表空间是自动段空间管理（ASSM），降低HWM。 segment shrink 分为两个阶段： 1、数据重组（compact）：通过一系列insert、delete操作，将数据尽量排在段的前面，这个过程中需要在表上加RX锁，及只需要移动的行上加锁。由于涉及到rowid的改变，需要enable row movement。同时要distable基于rowid的triggers，这个过程对业务影响比较小（--由于采用compact，只有涉及移动的行才加锁，所以不会锁定整个表，其他的DML操作有部分可以进行，进而减小系统高峰期的性能开销） 2、HWM调整：第二阶段是调整HWM位置，释放空闲数据块，此过程需要在表上加X锁（独享锁，因此这个表都被锁定，如果系统处在高峰期的时候，其他在此表的DML被挂起，会产生严重阻塞），会造成表上所有DML语句阻塞，系统忙时影响较大， 锁的内容参考：http://docs.oracle.com/cd/B19306_01/server.102/b14220/consist.htm shrink space语句两个阶段都执行 shrink space compact语句只执行第一个阶段。 在业务繁忙的时候，可以先执行shrink space compact重组数据，然后不忙的时候执行shrink space降低HWM释放空闲数据块。 shrink必须开启对象的row movement功能，alter table table_name enable row movement.但是要注意，该语句会造成引用table_name的对象（如存储过程、包、试图等）变为无效，执行完最好由utlrp.sql来编译无效对象。 语法： alter table shrink space[|cpmpact|cascade]; alter table shrink space compcat ..; 把块中的数据堆到一起，但会保持high water mark alter table shrink space ..;收缩表，降低high water mark alter table shrink space cascade..;搜索表，降低high water mark，并相关索引也要收缩。 alter index indexname shrink space ..;收缩索引 在线段收缩支持：LOB段，IOT溢出段； 使用在线段收缩回收HWM以下碎片的空闲空间，段收缩的好处： 更好的缓存使用率，提高OLTP的性能； ?减少全表扫描的数量，提高DSS的性能； 段收缩是在线的，索引在段收缩期间维护，不要求额外的磁盘空间。 段收缩收回HWM以下和以上的未使用空间，而空间释放仅仅释放高水位以上的未使用空间。默认情况下，段收缩压缩段，调整高水位，释放空间 433.When shrinking a table segment,you choose to shrink all the indexes for that table using the SHRINK SPACE command. Which clause should you use? D. CASCADE 434.Which of these represent the main components of Database Resource Manager? (Choose all that apply.) A. Resource consumer groups B. Resource plans D. Resource-plan directives Resource Manager 提供了一种优化数据库资源分配的有效办法，相对于在OS层面进行资源分配，Resource Manager 拥有更精细的控制粒度，以及更小的资源消耗，并且解决了一些OS层面无法解决的资源分配问题。 . Resource consumer group (资源用户组) 资源管理器将资源统一分配给资源用户组，而不是某一个具体的会话，当会话建立时会根据自身属性映射到不同的用户组，并从中申请系统资源 . Resource plan (资源计划) 一个数据库可以有多个Resource Plan,但是同一时刻只能有一个Plan状态为Active（由初始化参数 resource_manager_plan 决定），关联多个 Resource Plan directive . Resource plan directive (资源计划指令) 创建一个计划指令后就决定了系统资源如何分配给一个资源用户组 4 group_or_subplan => 'GROUP_OLAP',--可以指定用户组，也可以指定Sub Plan，和上一个参数构成了 Plan Diretive 的唯一属性 5 comment => 'Directives for OLAP consumer group...',6 active_sess_pool_p1 => 5,--最大活跃会话数，达到该限制后其他会话进入 Inactive session queue 7 queueing_p1 => 10,--在 Inactive session queue 的等待超时时间 8 parallel_degree_limit_p1 => 2,--最大并行度 9 switch_group => 'KILL_SESSION',--在满足指定条件后，对会话所采取的操作 10 switch_time => 3,--执行时间限制，会话操作执行时间超过该值后，即被采取上一步的相应操作（second） 11 switch_estimate => FALSE,--如果设置为 TRUE,在操作执行前Oracle先估算执行时间以决定是否对其切换用户组 12 max_est_exec_time => 5,--最大估算执行时间，优化器对操作时间进行估算，如果超过该值则直接返回“ORA-07455” 13 undo_pool => NULL,--uncommited undo segments 限制（KB） 14 max_idle_time => 300,--会话最大空闲时间 15 max_idle_blocker_time => 3,--Blocker会话的最大空闲时间 16 mgmt_p1 => 30,--cpu使用限制（Level 1） 17 mgmt_p2 => 0); --cpu使用限制（Level 2） 18 END; 435.Every resource plan must contain an allocation分配定位 to which consumer group? E. OTHER_GROUPS 每一个资源计划必须包含一个分配给哪个消费者组？ 436.Which DBMS_RESOURCE_MANAGER procedure prioritizes consumer-group mappings? B. SET_MAPPING_PRIORITY 根据会话的登录和运行属性建立该会话和资源用户组的映射关系，可以使用过程 SET_INITIAL_CONSUMER_GROUP 通过“Username”设置用户的初始用户资源组，该过程在11gR1被弃用后，改为使用过程 SET_CONSUMER_GROUP_MAPPING 通过“Session Attribute”来映射用户组，这里所说的会话属性包括： oracle_user,client_os_user,client_program,client_machine,module_name ... 当会话的多个属性映射发生冲突时，会根据每个属性的优先级来决定最终映射关系，使用这个过程来调整属性的优先级：SET_CONSUMER_GROUP_MAPPING_PRI 437.Within a resource-plan definition,what differentiates a top-level plan from a subplan? 顶级计划和子计划没有区别 C. There is no difference in the resource-plan definition. 438.When setting arguments for a job,which procedure do you use for types that cannot be implicitly converted to and from a VARCHAR2 datatype? C. SET_JOB_ANYDATA_VALUE 439.Which DBMS_SCHEDULER procedures can be used to enable a program? (Choose all that apply.) A. ENABLE D. SET_ATTRIBUTE,job会被创建在当前的schema下，并且是没有激活的; 如果要使job一创建 就自动激活，需要显式的设置enabled 属性为true,DBMS_SCHEDULER是Oracle 10G中新增的一个包，与老版本的dbms_job包相比，dbms_scheduler有很多新特性，我将通过一系列的文章来介绍一下如何使用这个包. part 1 1. 创建job job是什么呢? 简单的说就是计划(schedule)加上任务说明. 另外还有一些必须的参数. 这里提到的"任务"可以是数据库内部的存储过程,匿名的PL/sql块,也可以是操作系统级别的脚本. 可以有两种方式来定义"计划": 1) 使用DBMS_SCHDULER.CREATE_SCHEDULE 定义一个计划; 2) 调用DBMS_SCHDULER.CREATE_JOBE过程直接指定 (下面会详细说明) 在创建一个计划时，你至少需要指定下面的属性，它们是job运行所必须的: 开始时间 (start_time); 重复频率 (repeat_interval); 结束时间 (end_time) 另外，对于一个job而言，还有很多的附加参数: job_class job_priority auto_drop restartable max_runs max_failures schedule_limit logging_level 440.Which of the following is not a valid calendaring Syntax element? A. FREQ B. BYHOUR C. RUNDATE D. INTERVAL E. BYMINUTE Answer: C 日历表达式基本分为三部分: 第一部分是频率，也就是"FREQ"这个关键字， 它是必须指定的; 第二部分是时间间隔，也就是"INTERVAL"这个关键字， 取值范围是1-999. 它是可选的参数; 最后一部分是附加的参数,可用于 精确地指定日期和时间,它也是可选的参数,例如下面这些值都是合法的: BYMONTH,BYWEEKNO,BYYEARDAY,BYMONTHDAY,BYDAY BYHOUR,BYMINUTE,BYSECOND repeat_interval => 'FREQ=HOURLY; INTERVAL=2' 每隔2小时运行一次job repeat_interval => 'FREQ=DAILY' 每天运行一次job repeat_interval => 'FREQ=WEEKLY; BYDAY=MON,WED,FRI" 每周的1,3,5运行job repeat_interval => 'FREQ=YEARLY; BYMONTH=MAR,JUN,SEP,DEC; BYMONTHDAY=30' 每年的3,6,9,12月的30号运行job 441.Which Scheduler view(s) can be queried to see which jobs are currently executing? (Choose all that apply.) B. DBA_SCHEDULER_RUNNING_JOBS D. DBA_SCHEDULER_JOBS Q1:怎么从数据库中查询job的属性 ? A1: 有两种方法: 1) 查询(DBA|ALL|USER)_SCHEDULER_JOBS 视图 (提示: 根据用户权限的不同，选择性的查询 DBA|ALL|USER视图) 2) 调用DBMS_SCHEDULER包中的GET_ATTRIBUTE 过程 442.A schedule defined entirely完全 within在。。内 the confines 范围of a Scheduler job object is known as a (n) B. Inline schedule 计划 443.Which DBMS_SCHEDULER procedure(s) can be used to alter an existing job? (Choose all that apply.) A. SET_ATTRIBUTE_NULL E. SET_ATTRIBUTE Answer: AE 444.What is the default value for the ENABLED attribute of a job or program when it is created? B. FALSE 3: 你至少需要create_job这个系统权限。如果用户拥有create any job这个权限，它可以创建属主为任何用户(SYS用户除外)的job. 缺省情况下,job会被创建在当前的schema下，并且是没有激活的; 如果要使job一创建 就自动激活，需要显式的设置enabled 属性为true 445.To set the history retention period保存期 for either window logging or job logging individually,which parameters of the SET_SCHEDULER_ATTRIBUTE procedure need to be used? (Choose all that apply.) A. LOG_HISTORY D. WHICH_LOG 446.Consider the following code snippet: BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE ( name =>,lne_job1,attribute =>,job_priority,value => 1); END; / If this code were executed,which of the following statements would be true? A. The priority of the lne_job1 job would be set to 1. 447.Which of the following calendaring Syntax expressions would evaluate to the last day of every month? B. FREQ = MONTHLY; BYMONTHDAY = -1 每个月最后一天 448.Which of the following tasks is not performed by the job coordinator?协调员 A. Update job log when a job completes 《Administrator's Guide》 28.4.2 Job Coordinator actions The job coordinator: 1. Controls and spawns the job slaves 2. Queries the job table 3. Picks up jobs from the job table on a regular basis and places them in a memory cache. This improves performance by reducing trips to the disk 4. Takes jobs from the memory cache and passes them to job slaves for execution 5. Cleans up the job slave pool when slaves are no longer needed 6. Goes to sleep when no jobs are scheduled 7. Wakes up when a new job is about to be executed or a job was created using the CREATE_JOB procedure 8. Upon database,startup after an abnormal database shutdown,recovers any jobs that were running. You do not need to set the time that the job coordinator checks the job table; the system chooses the time frame automatically. One job coordinator is used per instance. This is also the case in Oracle RAC environments. Scheduler主要包含以下几个组件： 1）Job Table：存放所有Job的信息，可通过视图*_scheduler_jobs查看 2）Job Coordinator：它是一个或多个后台进程(cjqNNN)，用于控制和启动Job Slave 3）Job Slave（JS）：真正执行Job的进程 449.Which of the following objects can be directly referenced by a window object? (Choose all that apply.) A. Schedule object D. Resource plan 450.Which of the following are valid program types for a lightweight job? (Choose all that apply.) A. PLsql_BLOCK B. EXECUTABLE C. JAVA_STORED_PROCEDURE D. STORED_PROCEDURE E. EXTERNAL Answer: AD 451.Which of the following is not a valid setting for the PROGRAM_TYPE parameter in a program object or the JOB_TYPE parameter in a job object? B. JAVA_STORED_PROCEDURE 452.Which of the following Scheduler elements encourage object reuse? (Choose all that apply.) A. Schedule objects B. Program arguments D. Job arguments 453.What is the danger associated with stopping a running job by using the STOP_JOB procedure? D. The job may leave data in an inconsistent state. 454.If a job references参考 a schedule that has been disabled,what will be the result? E. A schedule object cannot be disabled. 455.When a job exceeds the date specified in its END_DATE attribute,which of the following will happen? (Choose all that apply.) A. The job will be dropped automatically if the value of the AUTO_DROP attribute is TRUE. B. The job will only be disabled if the value of the AUTO_DROP attribute is FALSE. E. The STATE column of the job table will be set to COMPLETED for the job. 456.Which of the following is true about job chains? B. They are used to implement dependency依赖 scheduling 457.If two windows overlap,which window attribute will determine whether one should be chosen over the other? A. WINDOW_PRIORITY 458.Globalization support is implemented through the text- and character-processing functions provided by which Oracle feature? B. NLSRTL Oracle NLS Runtime Library(NLSRTL)：与oracle的字符集文件以及字符支持有关。 Oracle的全球支持是通过Oracle NLS Runtime Library(NLSRTL)来实施的， NLS运行库通过独立的函数来完成运行时与语言相关的转换及控制。 459.What elements of globalization can be explicitly defined using the NLS_LANG environment variable? (Choose all that apply.) A. NLS_LANGUAGE 语言 B. NLS_SORT C. NLS_CALENDAR D. NLS_CHARACTERSET 字符集 E. NLS_TERRITORY 地域 Answer: ADE 460.Given two different character sets (A and B),which of the following must be true for A to be considered a strict严格的 superset 超集 of B? (Choose all that apply.) A. A must contain all of the characters defined in B.C. The encoded values in A must match the encoded values in B for all characters defined in B. 鉴于两种不同的字符集（ A和B），关于A被认为是B的严格超集这一点，以下哪一个一定是真实的？ A. A一定包含所有B中定义的字符 C.对B中定义的所有字符来说， A中的编码值必须匹配B的编码值 一个严格的超集，必须包含在其他的字符集，并匹配这些字符的编码值的所有字符 461.The NLS_SORT parameter sets the default sort method for which of the following operations?(Choose all that apply.) A. WHERE clause D. NLSSORT function NLS_SORT和NLS_COMP是Oracle两个初始化参数。顾名思义，NLS_SORT和NLS_COMP是设置排序和比较的方式。通过设置这两个参数可以实现自定义的排序和比较的方式。设置这两个参数的值可以在数据库创建的时候指定，也可以通过Alter Session语句来修改。如果是在创建数据库的时候设定，那么是不能再修改的，并在所有的Session中起作用。但是我们也通过Alter Session命令临时改变这两个参数的值，在会话中临时使用一种特定的排序和比较的方式。 462.Which view shows all valid values for the NLS_LANGUAGE,NLS_SORT,NLS_TERRITORY,and NLS_CHARACTERSET parameters? E. V$NLS_VALID_VALUES Answer: E V$NLS_VALID_VALUES lists all valid values for NLS parameters. Column Datatype Description PARAMETER VARCHAR2(64) Parameter name (LANGUAGE | SORT | TERRITORY | CHARACTERSET) VALUE VARCHAR2(64) NLS parameter value ISDEPRECATED VARCHAR2(5) Indicates whether the parameter has been deprecated (TRUE) or not (FALSE 463.Which of the following datatypes store time-zone information in the database? C. TIMESTAMP WITH TIME ZONE 包含时区信息 464.Which of the following are valid settings for the NLS_COMP parameter? (Choose all that apply.) B. ANSI C. BINARY NLS_COMP specifies the collation排序规则 behavior of the database session.数据库会话中 Values: ○ BINARY Normally,comparisons in the WHERE clause and in PL/sql blocks is binary unless you specify the NLSSORT function. ○ LINGUISTIC Comparisons for all sql operations in the WHERE clause and in PL/sql blocks should use the linguistic sort specified in the NLS_SORT parameter. To improve the performance,you can also define a linguistic index on the column for which you want linguistic comparisons. ○ ANSI A setting of ANSI is for backwards compatibility; in general,you should set NLS_COMP to LINGUISTIC 465.NLS parameters can be set using the five methods listed. Put the methods in order from highest to lowest according to Oracles order of precedence: NLS参数的优先级 A. Default setting B. Client environment variable C. Explicit ALTER SESSION statement D. Inside sql function E. Server initialization parameter 默认肯定最低 F. b,d,e,a,c G. e,b,c,d H. d,a I. a,e J. d,e Answer: C 　一:介绍一个概念: 　　NLS　 ‘National Language Support (NLS)’ 从概念我们看出这个参数实际上定义了数据库的存放数据的语言环境，当我们设定一种nls的时候实际上我们是为oracle在存放数据时指定了他的语种所特 有的一些表达形式，比如我们选择chinese,那么它的中文字符如何存放,按什么规则排序，货币如何表示，日期格式也就被设定了。 　　二：如何查询我们的数据库中到底使用的是什么字符集 　　sql> select * from V$NLS_PARAMETERS; 　　NLS_LANGUAGE　　　　　　　　　　　　　　　　　　　　 SIMPLIFIED CHINESE 　　NLS_TERRITORY　　　　　　　　　　　　　　　　　　　　　　　　　　CHINA 　　NLS_CURRENCY　　　　　　　　　　　　　　　　　　　　　　　　　　 RMB 　　NLS_ISO_CURRENCY　　　　　　　　　　　　　　　　　　　　　　　　 CHINA 　　NLS_NUMERIC_CHARACTERS　　　　　　　　　　　　　　　　　　　　　 .,　　NLS_CALENDAR　　　　　　　　　　　　　　　　　　　　　　　　　GREGORIAN 　　NLS_DATE_FORMAT　　　　　　　　　　　　　　　　　　　　　　 DD-MON-RR 　　NLS_DATE_LANGUAGE　　　　　　　　　　　　　　　　　 SIMPLIFIED CHINESE 　　NLS_CHARACTERSET　　　　　　　　　　　　　　　　　　　　　　 ZHS16GBK 　　NLS_SORT　　　　　　　　　　　　　　　　　　　　　　　　　　　　 BINARY 　　NLS_TIME_FORMAT　　　　　　　　　　　　　　　　　　　　　　HH.MI.SSXFF AM 　　NLS_TIMESTAMP_FORMAT　　　　　　　　　　　　 DD-MON-RR HH.MI.SSXFF AM 　　NLS_TIME_TZ_FORMAT　　　　　　　　　　　　　　　　　　HH.MI.SSXFF AMTZR 　　NLS_TIMESTAMP_TZ_FORMAT　　　　　　　　　DD-MON-RR HH.MI.SSXFF AM TZR 　　NLS_DUAL_CURRENCY　　　　　　　　　　　　　　　　　　　　　　　　RMB 　　NLS_NCHAR_CHARACTERSET　　　　　　　　　　　　　　　　　　AL16UTF16 　　NLS_COMP　　　　　　　　　　　　　　　　　　　　　　　　　　　　 BINARY 　　NLS_LENGTH_SEMANTICS　　　　　　　　　　　　　　　　　　　　　　 BYTE 　　NLS_NCHAR_CONV_EXCP　　　　　　　　　　　　　　　　　　　　　　　FALSE 　　其中nls_language表示了中文显示方式，nls_characterset是字符集设定，另外date,time等为日期时间的格式，currency是货币格式 466.What can you determine about the following linguistic sorts based only on their names? 1. GERMAN 2. FRENCH_M A. 1 is a monolingual单语言的 sort. 1是monolingual collations。 2应该是multilingual collations。多语言的 (2)在10G中,NLS_COMP多了一个参数值: Ø BINARY:按字符的二进制值比较 Ø ANSI:按字符的美国标准编码值进行比较 Ø LINGUISTIC:当设定此值后,WHERE条件的=,LIKE,IN的比较取决于NLS_SORT参数值,举例:当我们设定NLS_COMP=LINGUISTIC,NLS_SORT=BINARY_CI,则表示WHERE条件中字符串的比较是按BINARY_CI来进行比较的(大小写不敏感),所以在10G中,做到了真正的大小写敏感. 467.In a database with the database character set of US7ASCII and a national character set of UTF-8,which datatypes would be capable of storing Unicode data by default? C. NVARCHAR2 NVARCHAR2 Data Type The NVARCHAR2 data type is a Unicode-only data type. When you create a table with an NVARCHAR2 column,you supply the maximum number of characters it can hold. Oracle subsequently stores each value in the column exactly as you specify it,provided the value does not exceed the maximum length of the column.The maximum length of the column is determined by the national character set definition. Width specifications of character data type NVARCHAR2 refer to the number of characters. The maximum column size allowed is 4000 bytes. 468.Automatic data conversion will occur if which of the following happens? B. The client and server character sets are not the same,and the database character set is not a strict superset of the client character set. 469.Which of the following NLS_SORT parameter values would result in case-insensitive and accent-insensitive binary sorts? 下列nls_sort参数值将导致大小写忽略和口音忽略二进制排序？ B. NLS_SORT = BINARY_AI Oracle数据库中文拼音排序问题，NLS_SORT设置 Oracle9i之前，中文是按照二进制编码进行排序的。在oracle9i中新增了按照拼音、部首、笔画排序功能。 1、设置NLS_SORT参数值 SCHINESE_RADICAL_M 按照部首（第一顺序）、笔划（第二顺序）排序 SCHINESE_STROKE_M 按照笔划（第一顺序）、部首（第二顺序）排序 SCHINESE_PINYIN_M 按照拼音排序 BINARY_CI 忽略大小写的排序 2、Session级别的设置，修改ORACLE字段的默认排序方式： 按拼音：alter session set nls_sort = SCHINESE_PINYIN_M; 按笔画：alter session set nls_sort = SCHINESE_STROKE_M; 按偏旁：alter session set nls_sort = SCHINESE_RADICAL_M； 忽略大小写：alter session set nls_sort = BINARY_CI； The NLS_SORT attribute indicates which collating sequence to use for linguistic comparisons. It accepts the values listed in "Supported Linguistic Sorts." All these values can be modified to do case-insensitive sorts by appending _CI to the value. To perform accent-insensitive and case-insensitive sorts,append _AI to the value. 470.Which NLS parameter can be used to change the default Oracle sort method from binary to linguistic for the sql SELECT statement? A. NLS_LANG B. NLS_COMP C. NLS_SORT D. None of the above Answer: D 471.Which of the following would be affected by setting NLS_LENGTH_SEMANTICS=CHAR? E. CHAR columns Answer: E 初始化参数NLS_LENGTH_SEMANTICS用于指定CHAR列或VARCHAR2列的长度定义方式，默认值为BYTE。当设置该参数为BYTE时，定义CHAR列或VARCHAR2列采用字节长度方式；当设置该参数为CHAR时，定义CHAR列或VARCHAR2列采用字符个数方式。需要注意，设置该参数对于已存在参数没有作用 该初始化参数是动态参数，可以使用ALTER SESSION或ALTER SYSTEM命令进行修改。示例如下： sql>ALTER SESSION SET nls_length_semantics=char; sql>CREATE TABLE t1(cola VACHAR2(4)); sql>INSERT INTO t1 VALUES(‘北京’); sql>INSERT INTO t1 VALUES(‘BEIJING’); 上例将报错ORA-12899。 472.Which is not a valid locale definition file type? C. Calendar 哪个不是有效地本地文件类型 When you choose New Language,New Territory,New Character Set,or New Linguistic Sort,the first tab page that you see is labeled General. ClickShow Existing Definitions to see the Existing Definitions dialog Box. 473.How many different calendars does Oracle 11g support? B. 7 474.Which NLS parameter directly governs控制 linguistic searches? B. NLS_SORT NLS_SORT specifies the collating sequence for character value comparison in varIoUs sql operators and clauses,for example,ORDER BY,GROUP BY,comparison conditions (=,<>,<=,>=),IN,BETWEEN,MIN/MAX,GREATEST/LEAST,and INSTR. Which NLS parameterdirectly governs统治，管理 linguistic searches搜寻? 哪一个NLS参数直接管理语言搜索？ 475.Case-insensitive sorts are always accent insensitive by default. 不区分大小写 B. False 476.What is the name of the file that identifies the set of available locale definitions? 识别有效的地点定义集的文件名 C. lx1boot.nlb 区域定义文件：Local definition files----语言、区域、字符集、语言排序 模块化设计（每一文件仅包含一种特定区域类型相关的数据）的好处： 仅使用所需的地区集，不会在不必要的地区浪费内存； 地区定义可以混合和匹配使用； 在不影响任何其他文件的情况下，可以修改地区文件； 在不影响现存文件的情况下可以生产新的地区文件。 命名规则：lx10000.nlb lx30000.nlb Lx --第1.2位 前缀 T 第3位 地区类型：0=语言、1=区域、2=字符集、3=语言排序 Nnn 4-7位 目标ID（in Hex） .nlb 8-11位 扩展名 477.Which of the following is not a valid linguistic sort element? A. Accent口音 expansion 阐述 478.Identify the two direct sources from where sql plans can be loaded into the sql plan baselines. (Choose two.) A. Cursor cache C. sql Tuning Set 为了从AWR加载执行计划，首先要将存储在AWR快照中的计划加载到sql Tuning Set中，然后再从sql Tuning Set加载到sql Plan Baseline中 479.Which two statements about workload capture and replay operations are true? (Choose two.) C. Restart the database in RESTRICTED mode before beginning workload capture to enable a clean state for capturing the workload. D. The application state of the capture system must be identical to与。。相同 the application state of the replay system when the workload replay begins. Answer: CD 480.View the Exhibit exhibit1 to examine the series of sql commands. View the Exhibit exhibit2 to examine the plans available in the sql plan baseline. The baseline in the first row of the Exhibit is created when OPTIMIZER_MODE was set to FIRST_ROWS. Which statement is true if the sql query in exhibit1 is executed again when the value of OPTIMIZER_MODE is set to FIRST_ROWS? B. The optimizer uses the plan in the second row of the exhibit2 because it is an accepted plan. 481.Which statement is true about a running session that belongs to the online transaction processing (OLTP) group? D. It switches to the LOW_GROUP consumer group if the session exceeds 10000 I/O requests or 2500 MB of data transfer for queries,but not for data manipulation language (DML) operations. Answer: D 关于属于联机事务处理（ OLTP）组正在运行的会话，哪种说法是真实的？ D.如果会话超过10000 I / O请求或2500 MB的数据查询传输，但不用于数据操纵语言(DML)操作，它切换到低组的消费群体 解析： DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE switch_for_call,决定是否执行一个操作,因为switch_time,switch_io_megabytes,或switch_io_reqs参数,在顶级调用结束之后consumer group是否还原到原来的consumer group. 482.USER_DATA is a nonencrypted tablespace that contains a set of tables with data. You want to convert all existing data in the USER_DATA tablespace and the new data into the encrypted format. Which methods would you use to achieve this? (Choose all that apply.) 加密数据的方法 A. Use Data Pump to transfer the existing data to a new encrypted tablespace. B. Use ALTER TABLE MOVE to transfer the existing data to a new encrypted tablespace. C. Use CREATE TABLE AS SELECT to transfer the existing data to a new encrypted tablespace. （483）Evaluate the following block of code: C. It creates an access control list (ACL) with the user ACCT_MGR who gets the CONNECT and RESOLVE privileges. 创建访问控制列表(ACL)。ACL 是在XML 文件中保存的用户和权限的列表。以acl参数命名的XML 文档与XML DB中的/sys/acl/文件夹相关。在题中例中，ACCT_MGR被授予connect权限。ACL 中的用户名区分大小写，而且必须与会话的用户名相匹配。只有resolve和connect权限，connect权限包含resolve权限。可选参数可以指定这些权限的开始和结束时间戳。要将更多用户和权限添加到此ACL 中，可使用ADD_PRIVILEGE过程。 1、Oracle允许使用几个PL/sql API(UTL_TCP， UTL_SMTP， UTL_MAIL， UTL_HTTP和 UTL_INADDR)访问外部网络服务，这些API都使用TCP协议。 　　2、在Oracle 10g是通过一个基于用户是否被授予执行某个包的许可的 on/off开关来实现的，Oracle 11g引入了细粒度访问网络服务. 　　3、通过在XML DB 数据库中使用访问控制列表(ACL)来实现，允许控制哪个用户能够访问哪个网络资源，而不关心包的授权。 1. 创建访问控制列表 DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl => 'www.xml',description => 'WWW ACL',principal => 'HR',-- 赋予权限给哪个用户 is_grant => true,-- true表示授予权限 false表示取消权限 privilege => 'connect'); 2. 使用ADD_PRIVILEGE存储过程将其他的用户或角色添加到访问控制列表中，它的参数与CREATE_ACL存储过程的参数类似， 省略了DESCRIPTION参数，同时增加了POSITION参数，它用于设置优先顺序。 DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'www.xml',principal => 'HR',is_grant => true,privilege => 'resolve'); 3.使用ASSIGN_ACL存储过程给网络分配访问控制列表 DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => 'www.xml',host => '*.qq.com'); --主机名,域名,ip地址或分配的子网，主机名大小写敏感，ip地址和域名允许使用通配符　　 4.UNASSIGN_ACL存储过程允许你手动删除访问控制列表，它使用的参数与ASSIGN_ACL存储过程相同，使用NULL参数作为通配符。 DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL(host => 'www.qq.com'); 5.删除上面的控制列表 DBMS_NETWORK_ACL_ADMIN.drop_acl ( acl => 'www.xml'); 6. 查询创建的ACL信息 SELECT host,lower_port,upper_port,acl,DECODE( DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid,'HR','connect'),1,'GRANTED','DENIED',NULL) privilege FROM dba_network_acls 484.Which steps are mandatory to enable Direct NFS? 1. Mount all required file systems using the kernel NFS driver. 3. Replace the ODM library libodm11.so_stub with libodm11.so. B. 1 and 3 485.You want to back up your 100-GB database on a remote tape device. You are required to ensure that minimum network bandwidth is consumed while transferring the backups to the tape device. The current consumption of your cpu is approximately 40 percent. Which type of backup should you perform? 当前消耗40% C. compressed backup 486.You execute the following Recovery Manager (RMAN) commands in (he following order: BACKUP VALIDATE验证 DATABAE RECOVER CORRUPTION LIST; Which (two tasks are performed by these commands? (Choose two.) D. Discover any corrupt blocks that are viewable with the VSDATABASE_BI-OCK_CORBUPTION view. E. Repair all corrupted blocks that have been logged in the VSDATABASE_BLOCK_ CORRUPTION Answer: DE (backup validate check logical database;这个命令不会真正的产生数据库的备份，只是检查数据库的数据块，并把它记录到V$DATABASE_BIOCK_CORBUPTION当中； RECOVER CORRUPTION LIST;就是修复记录在V$DATABASE_BIOCK_CORBUPTION视图中的数据块 487.When executing a sql workload,you choose to generate execution plans only,without collecting execution statistics. Which two statements describe the implications of this? (Choose two.) A. It produces less accurate不精确 results of the comparison analysis. C. It shortens缩短 the time of execution and reduces the impact on system resources. 执行一个sql工作量的时候，你选择生成执行计划，没有收集 执行统计,哪两个语句描述这一个影响？ A.它产生的比较分析结果不太准确。 C.它缩短了执行时间，减少了对系统资源的影响 488.You are managing an Oracle Database 11g database with the ASM storage. The database is having big file tablespaces. You want files to open faster and less memory to be used in the shared pool to manage the extent maps. What configuration would you effect to achieve your objective? (Choose all that apply.) A. Set the ASM compatibility attribute for the ASM disk group to 11.1.0. D. Set the COMPATIBLE initialization parameter for the database instance to 11.1.0. Answer: AD 489.For which two database objects can the VERSIONS clause of the Flashback Versions Query be used?(Choose two.) 闪回版本查询 C.heap tables F.index-organized tables (IOT) Answer: CF 490.View the Exhibit. Examine the following command that is executed for the TRANSPORT table in the SH schema: sql> SELECT DBMS_STATS.CREATE_EXTENDED_STATS('sh','customers_obe','(country_id,cust_state_province)') FROM dual; Which statement describes the significance of this command? B. It creates a virtual hidden column in the CUSTOMERS_OBE table. oracle_优化器之多列统计信息.Extended Stats oracle 11g 收集统计信息的新特点介绍 2014-09-27 10:49:58 来源:思科华为 作者: 【大 中 小】 浏览:4次 评论:0条 　　对组合列创建统计信息 　　--检查表结构 　　select * from scott.emp where 1=2; 　　--创建虚拟列 会输出虚拟列名 　　select dbms_stats.create_extended_stats('scott','emp','(empno,JOB)') from dual; 　　select * from dba_tab_cols where column_name like 'SYS_STU%'; 　　--查看列名 　　select * from dba_tab_cols a where a.column_name='SYS_STU9D8L7V4NR#823O9R1EKCA6C'; 　　--查看列组合的统计信息 　　select * from dba_stat_extensions where extension_name='SYS_STU9D8L7V4NR#823O9R1EKCA6C'; 　　--对组合列收集直方图，则可以如下所示： 　　exec dbms_stats.gather_table_stats('scott',method_opt=>'for columns (empno,JOB) size AUTO'); 您看到此篇文章时的感受是： 491.Examine the exhibit to view the parameters set in your parameter file. (Click the Exhibit(s) button.) You restart the instance. To what value will the MEMORY_MAX_TARGET parameter be set by default? D. 600M Answer: D 492.You installed Oracle Database 11g and are performing a manual upgrade of the Oracle9i database. As a part of the upgrade process,you execute the following script: sql>@utlu111i.sql Which statement about the execution of this script is true? C. It must be executed from the environment of the database that is being upgraded. 数据库环境升级 升级前检查 升级 信息 升级信息脚本:utlu111i.sql 存储位置:$OH_11g/rdbms/admin 运行在源数据库环境中 检查所有 init 参数,并对过期的,不适当的参数显示警告 检查: 组件 表空间 SYSAUX 字符集 时区文件版本 493.Which three statements about performance analysis by sql Performance Analyzer are true? (Choose three.) A. It detects changes in sql execution plans. B. It produces results that can be used to create the sql plan baseline. D. It generates recommendations to run sql Tuning Advisor to tune regressed sqls. 494.Which statement about Automatic Memory Management with Oracle 11g is true? B. Oracle can reallocate memory between the SGA and PGA automatically as needed. 495.Which statement is true regarding the VALIDATE DATABASE command? A. It checks the database for intrablock corruptions only. 11g rman中增加validate来检查数据库状况 可以针对全库、控制文件、参数文件等进行验证 [oracle@localhost 2011_11_08]$ rman target / Recovery Manager: Release 11.2.0.1.0 - Production on Tue Nov 8 11:08:39 2011 Copyright (c) 1982,2009,Oracle and/or its affiliates. All rights reserved. connected to target database: DB11 (DBID=1298141643) --全库验证 --结果包括所有文件的状态、是否有坏块、空块、使用量、以及scn --同时还有文件中数据和索引各占用的块等信息 --全库验证的结果默认包含参数文件和控制文件 RMAN> validate database; Starting validate at 08-NOV-11 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=41 device type=DISK channel ORA_DISK_1: starting validation of datafile 496.Which two statements are true regarding an Automatic Storage Management (ASM) instance? (Choose two.) C. Automatic Memory Management is enabled in an ASM instance even when the MEMORY_TARGET parameter is not set explicitly 自动内存管理 即使 。。没有指定 D. An RDBMS instance gets connected to an ASM instance using ASMB as a foreground process when the database instance is started rdms实例用后台进程asmb连接asm实例 Answer: CD ASM实例的描述 497.You are managing the APPPROD database as a DBA. You plan to duplicate this database in the same system with the name DUPDB. You issued the following RMAN commands to create a duplicate database: RMAN> CONNECT target sys/sys@APPPROD RMAN> DUPLICATE TARGET DATABASE TO dupdb FROM ACTIVE DATABASE DB_FILE_NAME_CONVERT '/oracle/oradata/prod/','/scratch/oracle/oradata/dupdb/' SPILE PARAMETER_VALUE_CONVERT '/oracle/oradata/prod/','/scratch/oracle/oradata/dupdb/' SET SGA_MAX_SIZE = '300M' SET SGA_TARGET = '250M' SET LOG_FILE_NAME_CONVERT '/oracle/oradata/prod/redo/','/scratch/oracle/oradata/dupdb/redo/'; Which three are the prerequisites for the successful execution of the above command? (Choose three.) B. The target database should be in ARCHIVELOG mode if it is open. C. RMAN should be connected to both the instances as SYSDBA. E. The password file must exist for the source database and have the same SYS user password as the target. Answer: BCE 通过rman复制库 归档模式 rman连接两个实例 密码文件必须存在并且相同 498.The database Is configured in ARCHIVELOG mode. The database needs to be up 24 X 7. You want to perform user managed backup for the data files of the HR_DATA tablespace. To accomplish the task,you issued the following command: sql> ALTER TABLESPACE hi_data BEGIN BACKUP; Which two statements are true in this scenario? (Choose two.) A. No transaction on the tablespace is allowed but you can perform queries C. The checkpoint change number is frozen in headers of the data files until the file is removed from backup mode. 事务指允许查询 检查点号冻结 数据文件头 直到解除备份模式 499.Which statement is true regarding the retention policy保存策略 for the incidents事件 accumulated 累积in the Automatic Diagnostic Repository (ADR)? D. The default setting is for one year after which the incident Metadata is purged from the ADR and the files are retained for one month. 元数据保存一年 文件保存一个月 Answer: D 500.You opened the encryption wallet and then issued the following command: sql>CREATE TABLESPACE securespace DATAFILE '/home/user/oradata/secure01.dbf' SIZE 150M ENCRYPTION USING '3DES168' DEFAULT STORAGE(ENCRYPT); Then you closed the wallet. Later,you issued the following command to create the EMPLOYEES table in the SECURESPACE tablespace and you use the NO SALT option for the EMPID column. What is the outcome? B. It generates an error because the wallet is closed. 501.Note the output of the following query; sql> SELECT flashback_archieve_name,status FROM dba_flashback_archieve; FLASHBACK_ARCHIEVE_NAME STATUS FLA1 You executed the following command to enable Flashback Data Archive on the EXCHANGB_PATE table: ALTER TABLE exchange_rate FLASHBACK ARCHIEVE; What is the outcome of this command? 闪回数据归档 A. The table uses the default Flashback Data Archive.