Oracle.1Z0-052-0

一、源题
QUESTION 1
The instance abnormally terminates because of a power outage. Which statement is true about redo log files during instance recovery?
A. Inactive and current redo log files are required to accomplish recovery
B. Online and archived redo files are required to accomplish instance recovery
C. All redo log entries after the last checkpoint are applied from redo log files to data files
D. All redo log entries recorded in the current log file until the checkpoint position are applied to data files
Correct Answer: C
二、题目翻译
实例因为断电异常中止。哪些是关于redo日志在实例恢复期间的正确描述?
A. Inactive和current状态的redo日志需要完成恢复
B. Online和Archived日志需要实例恢复
C. 最后一个checkpoint之后的所有redo信息都要应用到数据文件
D. 所有记录在current状态日志中直到检查点位置的redo信息应用于数据文件
答案:C
三、题目解析
实例恢复是进程应用记录在online redo log的信息去重建最近一次检查点的之后的变更。
参考官方文档:
http://docs.oracle.com/cd/E11882_01/server.112/e40540/startup.htm#CNCPT1301
这个是属于实例恢复,实例恢复不需要DBA的干预。
Oracle DB 会自动从实例故障中进行恢复。实例所需要做的就是正常启动。如果Oracle
Restart 已启用并且配置为监视该数据库,则该启动操作会自动进行。实例会装载控制文件,
然后尝试打开数据文件。如果实例发现数据文件在关闭期间尚未同步,则会使用重做日志
组中包含的信息将数据文件前滚到关闭时的状态,此时利用的重做日志信息即是自最后一次检查点以来所有的重做信息。然后,将打开数据库,并回退所有未提交的事务处理。

一、源题
QUESTION 2
Examine the command: SQL>ALTER USR skd ACCOUNT LOCK; Which two statements are true after the command is executed? (Choose two.)
A. The SKD user cannot log in to the database instance
B. The objects owned by the SKD user are not accessible to any user
C. The other users can access the objects owned by the SKD user, on which they have access
D. The password for the SKD user expires and the user is forced to change the password at the next log in
Correct Answer: AC
二、题目翻译
检查命令:
SQL>ALTER USR skd ACCOUNT LOCK;
哪两个语句是正确的命令执行之后?(选择两个)
A. SKD用户不能登录数据库实例
B. SKD拥有的对象不能被任何用户访问
C. 访问过SKD的用户,可以访问SKD用户拥有的对象
D. SKD用户的密码过期并且下次登录时强制更改密码

答案:AC
三、题目解析
四、测试

--创建两个测试用户SKD和SKD2
SQL> create user SKD identified by oracle;

User created.

SQL> create user SKD2 identified by oracle;

User created.
SQL> grant dba to skd;

Grant succeeded.
--SKD用户创建测试表
SQL> conn skd/oracle
Connected.
SQL>  create table object as select * from dba_objects;

Table created.
--使用sys用户给SKD上锁
SQL> conn / as sysdba;
Connected.
SQL>  alter user SKD account lock;

User altered.
--尝试登录SKD用户
SQL> conn skd/oracle
ERROR:
ORA-28000: the account is locked


Warning: You are no longer connected to ORACLE.
--所以A对,D错
SQL> conn / as sysdba;
Connected.
SQL> grant dba to skd2;

Grant succeeded.
--使用SKD2用户尝试访问SKD用户的object表
SQL> conn skd2/oracle
Connected.
SQL> select owner from SKD.object where rownum < 2;

OWNER
------------------------------
SYS
--故B错,C对

一、源题
QUESTION 3
For which database operation would you need the database to be in the MOUNT state?
A. Renaming the control files
B. Re-creating the control files
C. Dropping a user in your database
D. Dropping a tablespace in your database
E. Configuring the database instance to operate in the ARCHIVELOG or NOARCHIVELOG modes
Correct Answer: E
二、题目翻译
数据库在mount状态期间能做什么操作?
A. 重命名控制文件
B. 重建控制文件
C. 删除用户
D. 删除表空间
E. 配置数据库为ARCHIVELOG或NOARCHIVELOG模式

答案:E
三、题目解析
ACD需要在数据库处于open状态
B需要数据库处于nomount状态
数据库在mount期间可以执行以下操作:

  • 启用或禁用redo日志归档选项
  • 执行完整的数据库恢复

参考文档:
http://docs.oracle.com/cd/E11882_01/server.112/e25494/start.htm#ADMIN11142

一、源题
QUESTION 4
The job to gather optimizer statistics for objects runs as part of the automatic maintenance window in your database instance. At a certain point of time, the maintenance window closes before the statistics are gathered for all objects. Which statement is true in this scenario?
A. The statistics collection continues until all objects are processed.
B. The job is terminated and the statistics collected are restored to a time before the job started.
C. This produces an error and the statistics collected are locked until the next time that the maintenance window is opened.
D. The job is terminated and the statistics for the remaining objects are collected the next time that the
maintenance window is opened.
Correct Answer: D
二、题目翻译
收集优化器统计信息的作业作为你数据库实例自动维护窗口的一部分。在一个确定的时间点,自动维护窗口关闭在所有的对象的统计信息收集完成之前。哪句描述是正确的在以下场景?
A. 统计信息收集继续直到所有的对象处理
B. 作业中止并且统计信息收集还原到作业开始的时间点
C. 产生一个错误并且统计信息收集锁住直到下次维护窗口打开
D. 作业中止并且剩余对象的统计信息继续收集在下次维护窗口打开的时候

答案:D
三、题目解析
一个作业在你的数据库实例的自动维护窗口中为一些对象正在搜集优化统计信息,在一个特定的时间点,所有对象的统计信息还没有搜集完,维护窗口就关闭了,此时,这个作业被终止,剩下的统计信息的搜集将在下次维护窗口打开的时候继续搜集。

参考官方文档:
http://docs.oracle.com/cd/E11882_01/server.112/e25494/tasks.htm#ADMIN0235

一、源题
QUESTION 5
You want to move all objects of the APPS user in the test database to the DB_USR schema of the production database. Which option of IMPDP would you use to accomplish this task?
A. FULL
B. SCHEMAS
C. TRANSFORM
D. REMAP_SCHEMA
E. REMAP_TABLESPACE
Correct Answer: D
二、题目翻译
你想移动测试库中APPS用户的所有对象到生产数据库的DB_USR用户。哪个IMPDP的选项可以完成这个任务?
A. FULL
B. SCHEMAS
C. TRANSFORM
D. REMAP_SCHEMA
E. REMAP_TABLESPACE

答案:D
三、题目解析
A是全库导入
B是导入某个用户
C是去掉表空间和存储子句
D是将一个用户导入另一个用户
E 是将数据从一个表空间导入另一个表空间

参考官方文档:
http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm#SUTIL903

一、源题
QUESTION 6
You executed the following command to create a password file in the database server: $ orapwd file = orapworcl entries = 5 ignorecase=N
Which statement describes the purpose of the above password file?
A. It records usernames and passwords of users when granted the DBA role
B. It contains usernames and passwords of users for whom auditing is enabled
C. It is used by Oracle to authenticate users for remote database administrator
D. It records usernames and passwords of all users when they are added to OSDBA or OSOPER operating groups
Correct Answer: C
二、题目翻译
执行下面命令创建一个密码文件
$ orapwd file = orapworcl entries = 5 ignorecase=N
哪些描述是正确的关于上面的密码文件?
A. 当授予DBA角色的时候记录用户的用户名和密码
B. 包含启用了审计的用户的用户名和密码
C. Oracle用来对远程的数据库管理员进行身份验证
D. 用来记录加入OSDBA或OSOPER组的所有用户的用户名和密码

答案:C
三、题目解析
Oracle的口令文件的作用是存放所有sysdba或者sysoper权限连接数据库的用户的口令。
是否使用口令文件,是通过oracle提供的一个参数remote_login_passwordfile来控制的

SQL> show parameter remote_login_passwordfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE

remote_login_passwordfile有none,shared,exclusive 3个值,none表示不使用口令文件, exclusive表示实例独占使用口令文件,也就是各自实例使用单独的口令文件,shared表示多个实例共享一个口令文件。

参考官方文档:
http://docs.oracle.com/cd/E11882_01/server.112/e25494/dba.htm#ADMIN11060

一、源题
QUESTION 7
Which two statements are true regarding the MRKT tablespace? (Choose two.)

SQL> CREATE BIGFILE TABLESPACE MRKT 2 DATAFILE '/u01/app/oracle/oradata/orcl/mrkt.dbf' 
size 10M 
LOGGING 3 
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; 
Tablespace created. 
SQL> ALTER DATABASE DEFAULT TABLESPACE MRKT; 
Database altered.

A. No more data files can be added to the tablespace.
B. Segment space is managed by free lists in the tablespace.
C. A user created without being assigned a default tablespace uses this tablespace.
D. The tablespace can be dropped with the current setting with segments present in it.
Correct Answer: AC
二、题目翻译
哪两句关于MRKT表空间的描述是正确的?(选择两个)

SQL> CREATE BIGFILE TABLESPACE MRKT 2 DATAFILE '/u01/app/oracle/oradata/orcl/mrkt.dbf' size 10M LOGGING 3 EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
Tablespace created. 
SQL> ALTER DATABASE DEFAULT TABLESPACE MRKT;
Database altered.

A. 不能再添加数据文件到表空间
B. 段空间在表空间中是自由列表管理
C. 创建用户不指定默认表空间将使用这个表空间
D. 表空间可以删除在当前的配置下

答案:AC
三、题目解析
BIGFILE只能有一个数据文件,A对
ALTER DATABASE DEFAULT TABLESPACE MRKT;将默认表空间修改成MRKT,C对
创建MRKT的时候,Oracle自动创建本地管理表空间和自动段空间管理(ASSM)。B错
默认表空间不能直接drop,D错

一、源题
QUESTION 8
View the Exhibit and examine the privileges granted to the MGR_ROLE role. The user SKD has been granted the CONNECT and RESOURCE roles only. The database administrator (DBA) grants MGR_ROLE to the user SKD by executing the command: SQL> GRANT MGR_ROLE TO SKD WITH ADMIN OPTION; Which statement is true about the user SKD after he/she is granted this role?

image.png

A. The user SKD can grant only the MGR_ROLE role to other users, but not the privileges in it
B. The user SKD can revoke the MGR_ROLE only from the users for whom he/she is the grantor
C. The user SKD can grant the privileges in the MGR_ROLE role to other users but not with ADMIN OPTION
D. The user SKD can grant the privileges in the MGR_ROLE role to other users, but cannot revoke privileges from them
Correct Answer: A
二、题目翻译
观察下面的表并检查赋予MGR_ROLE角色的权限。用户SKD只被授予CONNECT和RESOURCE角色。DBA授权MGR_ROLE给SKD用户使用下面命令:
SQL> GRANT MGR_ROLE TO SKD WITH ADMIN OPTION;
下面哪些描述是正确的关于SKD被授予这个角色之后?
A. SKD只能授予其他用户MGR_ROLE角色,不能是角色里的权限
B. SKD只可以从它授予的用户的MGR_ROLE角色
C. SKD可以授予其他用户MGR_ROLE角色中的权限不使用ADMIN选项
D. SKD可以授权其他用户MGR_ROLE角色中的权限,但是不能收回权限

答案:A
三、题目解析
四、测试

SQL> create user SKD identified by SKD;

User created.

SQL> grant connect,resource to SKD;

Grant succeeded.

SQL> create role MGR_ROLE;

Role created.

SQL>  grant create role to MGR_ROLE;

Grant succeeded.

SQL>  grant create user to MGR_ROLE;

Grant succeeded.

SQL> grant select any table to MGR_ROLE;

Grant succeeded.
--查dba_sys_privs、dba_role_privs,dba_tab_privs三个视图看用户到底有哪些权限
SQL> select * from dba_role_privs where  grantee='MGR_ROLE';

no rows selected

SQL> set linesize 200
SQL> select * from role_sys_privs where role='MGR_ROLE'; 

ROLE                           PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
MGR_ROLE                       SELECT ANY TABLE                         NO
MGR_ROLE                       CREATE ROLE                              NO
MGR_ROLE                       CREATE USER                              NO

SQL> select * from role_tab_privs where role='MGR_ROLE'; 

no rows selected
--WITH ADMIN OPTION的意思是被授予该权限的用户有权将某个权限(如MGR_ROLE)授予其他用户或角色,取消是不级联的。
SQL> GRANT MGR_ROLE TO SKD WITH ADMIN OPTION;

Grant succeeded.

SQL>  create user user1 identified by test1;

User created.

SQL> grant create session to user1;

Grant succeeded.

SQL> conn SKD/SKD
Connected.

SQL> grant MGR_ROLE to user1;

Grant succeeded.

SQL> grant create user to user1;
grant create user to user1
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL>  revoke MGR_ROLE from user1;

Revoke succeeded.
--A正确,可以授权给MGR_ROLE给别的用户,但不能把MGR_ROLE里面的权限如create user授权给别的用户。
--B错误,可以级联回收,可以回收不是SKD自己授予的。
--C错,错误。The user SKD can grant the privileges in the MGR_ROLE role to other users。根据A答案,SKD不能将MGR_ROLE角色里的权限给别的用户。
D,也可以回收权限。

一、源题
QUESTION 9
After performing a clean shut down of the database instance for maintenance, you mount the database and then execute a command to open the database: SQL> ALTER DATABASE OPEN; Which two statements are true? (Choose two.)
A. The online redo log files and online data files are opened
B. All the online data files headers are checked for consistency
C. Instance recovery is performed before opening the database
D. The path and existence of all the log file members are checked
Correct Answer: AB
二、题目翻译
执行一次干净的关闭数据库之后,你mount数据库,然后执行下面命令:
SQL> ALTER DATABASE OPEN;
哪两个说法是正确的?(选择两个)
A. 在线redo log和在线数据文件被打开
B. 所有的在线数据文件头检查一致性
C. 实例恢复执行在数据库打开之前
D. 检查所有日志文件成员的路径和存在

答案:AB
三、题目解析
打开数据库过程包括执行以下任务:
• 打开数据文件
• 打开联机重做日志文件
如果尝试打开数据库时任一数据文件或联机重做日志文件不存在,则Oracle 服务器返回错误。
在最后这个阶段,Oracle 服务器会验证是否可以打开所有数据文件和联机重做日志文件,还会检查数据库的一致性。如有必要,系统监视器(SMON) 后台进程将启动实例恢复。

一、源题
QUESTION 10
Examine the command that is used to create a table: SQL> CREATE TABLE orders ( oid NUMBER(6) PRIMARY KEY, odate DATE, ccode NUMBER (6), oamt NUMBER(10,2) ) TABLESPACE users; Which two statements are true about the effect of the above command? (Choose two.)
A. A CHECK constraint is created on the OID column.
B. A NOT NULL constraint is created on the OID column.
C. The ORDERS table is the only object created in the USERS tablespace.
D. The ORDERS table and a unique index are created in the USERS tablespace.
E. The ORDERS table is created in the USERS tablespace and a unique index is created on the OID column in the SYSTEM tablespace.
Correct Answer: BD
二、题目翻译
执行下面命令创建一个表:
SQL> CREATE TABLE orders ( oid NUMBER(6) PRIMARY KEY,
odate DATE, ccode NUMBER (6),
oamt NUMBER(10,2) )
TABLESPACE users;
哪两句是描述正确的(选择两个)?
A. CHECK约束被创建在OID列上
B. NOT NULL约束被创建在OID列上
C. ORDERS表是唯一创建在USERS表空间上的对象
D. ORDERS表和唯一索引被创建在USERS表空间
E. ORDERS表被创建在USERS表空间,唯一索引被创建OID列在SYSTEM表空间中

答案:BD
三、题目解析
加在OID列上的是主键约束,包含唯一约束和非空约束,A错,B对
因为OID上有主键索引,所以会自动创建唯一索引,也在USERS表空间,CE错,D对

一、源题
QUESTION 11
Which two statements are true about Shared SQL Area and Private SQL Area? (Choose two.)
A. Shared SQL Area will be allocated in the shared pool
B. Shared SQL Area will be allocated when a session starts
C. Shared SQL Area will be allocated in the large pool always
D. The whole of Private SQL Area will be allocated in the Program Global Area (PGA) always
E. Shared SQL Area and Private SQL Area will be allocated in the PGA or large pool
F. The number of Private SQL Area allocations is dependent on the OPEN_CURSORS parameter
Correct Answer: AF
二、题目翻译
哪两句是正确的关于共享SQL区和私有SQL区?(选择2个)
A. 共享SQL区被分配在共享池
B. 共享SQL区被分配当启动会话时
C. 共享SQL区总是被分配到大池
D. 私有SQL区总是被分配到PGA
E. 共享SQL区和私有SQL区被分配到PGA或者大池
F. 私有SQL区分配取决于参数OPEN_CURSORS

答案:AF
Shared SQL Area存储着SQL的执行计划等信息,可以被多个session共享,该component存储在SGA中
Private SQL Area 包括绑定变量等信息,每个session的这类信息都不一样,为session所独有(PGA)。且在Private SQL Area 存储一个指向shared SQL area ( SGA )中共享SQL的指针,与OPEN_CURSORS参数相关。

一、源题
QUESTION 12
Which three statements are correct about temporary tables? (Choose three.)
A. Indexes and views can be created on temporary tables
B. Both the data and structure of temporary tables can be exported
C. Temporary tables are always created in a user's temporary tablespace
D. The data inserted into a temporary table in a session is available to other sessions
E. Data Manipulation Language (DML) locks are never acquired on the data of temporary tables
Correct Answer: ACE
二、题目翻译
哪三句话是正确的关于临时表?(选择3个)
A. 索引和视图可以被创建在临时表
B. 临时的数据和表结构都能被导出
C. 临时表总是创建在临时表空间
D. 在一个会话中插入数据到临时表对另一个会话可用
E. 临时表不需要DML锁

答案:ACE
三、题目解析
在临时表中可以创建索引、视图及触发器,还可以使用“Export and Import(导出和导入)”或“Data Pump(数据泵)”导出和导入临时表的定义。但是,即使使用了ROWS 选项,也不会导出数据。
A,可以对临时表创建索引,视图,触发器,正确
B,可以用 export 和 import 工具导入导出表的定义和数据,错误,不能导出数据。
C,一个用户的临时表就放在当前用户的临时表空间中,创建临时表后并不产生任何 segments分配,与普通表不同,正确。
D,被插入临时表的数据能被其他会话访问,错误。临时表中的数据是基于一个会话或者一个事务的,其他的会话不能访问到。
E,在临时表中,DML锁永远不需要,正确。
临时表可专用于事务处理,也可专用于会话。对于专用于事务处理的临时表而言,数据存在于事务处理期间;对于专用于会话的临时表而言,数据存在于会话期间。在这两种情况下,会话插入的数据专用于会话。每个会话仅可查看和修改自己的数据。因此,临时表的数据从不会获得DML 锁。

参考官方文档:
http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables.htm#ADMIN11633

一、源题
QUESTION 13
Which two kinds of failures make the Data Recovery Advisor (DRA) generate a manual checklist? (Choose two.)
A. Failure when no standby database is configured
B. Failure because a data file is renamed accidentally
C. Failure that requires no archive logs to be applied for recovery
D. Failure due to loss of connectivity-for example, an unplugged disk cable
Correct Answer: BD
二、题目翻译
哪两种故障导致DRA产生手动清单?(选择2个)
A. 当没有配置备用数据库时发生故障
B. 因为数据文件意外的重命名导致的故障
C. 因为没有归档日志应用于恢复产生的故障
D. 由于丢失连接导致的故障,例如没插电缆的磁盘
答案:BD
三、题目解析
DRA会显示两种类型的故障
 需要人工干预的故障,例如,未插入磁盘电缆的连接故障
 可以通过撤销以前的错误操作快速修复的故障,例如:错误的重命名了数据文件

参考官方文档
http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmrepai.htm#BRADV246

一、源题
QUESTION 14
Which two statements correctly describe the relation between a data file and the logical database structures? (Choose two)
A. An extent cannot spread across data files.
B. A segment cannot spread across data files.
C. A data file can belong to only one tablespace.
D. A data file can have only one segment created in it.
E. A data block can spread across multiple data files as it can consist of multiple operating system (OS) blocks.
Correct Answer: AC
二、题目翻译
哪两句是正确的关于描述数据文件和数据库逻辑结构关系?(选择2个)
A. extent不能跨数据文件
B. segment不能跨数据文件
C. 一个数据文件只能属于一个表空间
D. 一个数据文件只能创建一个段
E. 一个数据块可以跨多个数据文件,因为它由多个操作系统块组成

答案:AC
三、题目解析
Extent不能跨数据文件,segment可以,A错,B对
每个表空间都可以有一个或多个数据文件,但一个数据文件只能属于一个表空间,c对
一个数据文件可以有多个段,并且段能跨数据文件,D错
数据块不能跨数据文件,E错

参考官方文档:
http://docs.oracle.com/cd/E11882_01/server.112/e40540/logical.htm#CNCPT004

一、源题
QUESTION 15
Which two statements are true regarding a tablespace? (Choose two.)
A. It can span multiple databases
B. It can consist of multiple data files
C. It can contain blocks of different files
D. It can contains segments of different sizes
E. It can contains a part of nonpartitioned segment
Correct Answer: BD
二、题目翻译
哪两句关于表空间的描述是正确的?(选择两个)
A. 可以跨多个数据库
B. 可以由多个数据文件组成
C. 可以包含不同大小的数据块
D. 可以包含不同大小的段
E. 可以包含非分区段的一部分

答案:BD
三、题目解析
表空间在物理上将所有逻辑结构的数据都存储在表空间中。
表空间不能跨数据库,A错
每个表空间都可以有一个或多个数据文件,但一个数据文件只能属于一个表空间,B对
数据库的数据块大小是固定的,C错
段是由一系列的区组成的,而区又是由一系列的块组成的,块的大小是固定的,所以段的大小不固定,D对
非分区段能跨数据文件,但是不能跨表空间,E错
参考官方文档:
http://docs.oracle.com/cd/E11882_01/server.112/e40540/logical.htm#CNCPT004

一、源题
QUESTION 16
Which two statements are true regarding Oracle Data Pump? (Choose two.)
A. EXPDP and IMPDP are the client components of Oracle Data Pump
B. DBMS_DATAPUMP PL/SQL packages can be used indedendently of the DATA Pump clients
C. Oracle Data Pump export and import operations can be performed only by users with the SYSDBA
privilege
D. Oracle Data Pump imports can be done from the export files generated in the Original Export Release 9.x
E. EXPDP and IMPDP use the procedures provided by DBMS_METADATA to execute export and import commands
Correct Answer: AB
二、题目翻译
哪些是正确的关于Oracle Data Pump?(选择两个)
A. EXPDP和IMPDP是Oracle Data Pump的客户端组件
B. DBMS_DATAPUMP PL/SQL可以独立Data Pump client之外使用
C. Oracle Data Pump的导入和导出操作只能由有SYSDBA权限的用户执行
D. Oracle Data Pump可以导入由9.x版本的Original Export产生的导出文件
E. EXPDP和IMPDP使用DBMS_METADATA执行导出和导入命令

答案:AB
三、题目解析
Data Pump包含三个部分:DBMS_DATAPUMP,DBMS_METADATA,,客户端命令行EXPDP和IMPDP,所以A正确
DBMS_DATAPUMP和DBMS_METADATA都可以独立于Data Pump client使用,B正确

参考官方文档:
http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_overview.htm#SUTIL801

一、源题
QUESTION 17
Identify the two situations in which the alert log file is updated with details. (Choose two.)
A. Running a query on a table returns "ORA-600: Internal Error"
B. Inserting a value in a table returns "ORA-01722: Invalid Number" "First Test, First Pass" - www.lead2pass.com 8
Oracle 1Z0-052 Exam
C. Creating a table returns "ORA-00955: name is already used by an existing object'
D. Inserting a value in a table returns "ORA-00001: unique constraint (SYS.PK_TECHP) violated."
E. Rebuilding an index using ALTER INDEX ... REBUILD fails with an error "ORA-01578: ORACLE data block corrupted (file # 14, block @ 50)."
F. Rebuilding an index using ALTER INDEX .... REBUILD fails with an error "ORA-01578: ORACLE data block corrupted (file #14, block #50)."
Correct Answer: AE
二、题目翻译
鉴别两种情况下告警日志更新细节。(选择两个)
A. 在一个表运行查询返回”ORA-600:Internal Error”
B. 在一个表插入值返回”ORA-01722: Invalid Number”
C. 创建一个表返回”ORA-00955:name is already used by an existing object”
D. 在一个表插入值返回"ORA-00001: unique constraint (SYS.PK_TECHP) violated."
E. 重建索引使用ALTER INDEX ... REBUILD失败,报"ORA-01578: ORACLE data block corrupted (file # 14, block @ 50)."
F. 重建索引使用ALTER INDEX ... REBUILD失败,报"ORA-01578: ORACLE data block corrupted (file # 14, block # 50)."

答案:AF
三、题目解析
每个数据库都有一个alert_<sid >.log文件。此文件位于数据库所在的服务器中,如果设置了$ORACLE_BASE,则此文件默认存储在$ORACLE_BASE/diag/rdbms/<db_name>/<SID>/trace中。
数据库预警文件是按时间顺序列出消息的日志文件,例如:
• 启动时使用的任何非默认初始化参数
• 已发生的所有内部错误( ORA-600) 、块损坏错误( ORA-1578 ) 和死锁错误( ORA-60 )
• 管理操作,如SQL 语句CREATE 、ALTER、DROP DATABASE 和TABLESPACE,以及Enterprise Manager 或SQL*Plus 语句STARTUP、SHUTDOWN 、ARCHIVE LOG和RECOVER
• 与共享服务器和分派程序进程的功能相关的多个消息和错误
• 自动刷新实体化视图时发生的错误

一、源题
QUESTION 18
Which two statements are true about alerts? (Choose two.)
A. Clearing an alert sends the alert to the alert history
B. Response actions cannot be specified with server-generated alerts
C. The nonthreshold alerts appear in the DBA_OUTSTANDING_ALERTS view
D. Server-generated alerts notify the problems that cannot be resolved automatically and require administrators to be notified
Correct Answer: AD
二、题目翻译
哪两个语句是正确的关于告警日志?(选择两个)
A. 清除告警日志发送告警到告警历史
B. 响应动作不会产生指定的服务器告警
C. 阈值警报出现在视图DBA_OUTSTANDING_ALERTS中
D. 服务器告警通知不能自动解决和需要管理员注意的问题

答案:AD
三、题目解析
告警有两种,一种是trace和alert文件,一种是Server-Generated Alerts
这道题应该说的是第二种情况
Server-Generated Alerts是数据库产生的一种告警,它可能包括解决这个告警的方法,当告警清除后还会生成通知消息,当问题出现或者没有匹配到期望的阈值的时候就会自动生成告警,这些阈值都是可以定义的,比如
1.每秒的物理读
2.每秒的用户提交
3.sql服务响应时间
一些没有基于阈值的比如
1.快照太旧
2.恢复会话挂起
3.恢复区的使用
A:正确,当告警清除后会从DBA_OUTSTANDING_ALERTS删除,并且在DBA_ALERT_HISTORY中添加
B:错误,会产生
C:错误,一些没有基于阈值的也会警报
D:正确,发现的问题不会自动处理,需要DBA人工干预
参考官方文档:
http://docs.oracle.com/cd/E11882_01/server.112/e25494/monitoring.htm#ADMIN11247

一、源题
QUESTION 19
Examine the following steps performed on a database instance:

  1. The DBA grants the CREATE TABLE system privilege to the SKD user with ADMIN OPTION
  2. The SKD user creates a table
  3. The SKD user grants the CREATE TABLE system privilege to the HR user
  4. The HR user creates a table
  5. The DBA revokes the CREATE TABLE system privilege from SKD
    Which statement is true after step 5 is performed?
    A. The table created by SKD is not accessible and SKD cannot create new tables
    B. The tables created by SKD and HR remain, but both cannot create new tables
    C. The table created by HR remains and HR still has the CREATE TABLE system privilege
    D. The table created by HR remains and HR can grant the CREATE TABLE system privilege to other
    users
    Correct Answer: C
    二、题目翻译
    检查下面在数据库实例执行的步骤:
  6.    DBA授予SKD用户CREATE TABLE权限使用ADMIN OPTION
    
  7.    SKD用户创建一个表
    
  8.    SKD用户授予CREATE TABLE系统权限给HR用户
    
  9.    HR用户创建一个表
    
  10.    DBA收回SKD的CREATE TABLE系统权限
    

哪些语句是正确的上述5步执行之后?
A. SKD创建的表是不可访问的并且SKD不能创建新表
B. SKD和HR创建的表仍然在,但都不能创建新表
C. HR创建的表存在且HR仍然有CREATE TABLE系统权限
D. HR创建的表存在且HR可以授予其它用户CREATE TABLE系统权限
三、题目解析
答案:C
SKD创建的表是可以访问的,但是不能创建新表,A错
系统权限的收回不是级联的,所以不会影响HR用户的权限,HR可以创建新表,B错,C对
SKD授予HR权限时没有使用with admin option,故HR用户不能授予其他用户权限,D错

参考官方文档:
http://docs.oracle.com/cd/E11882_01/network.112/e36292/authorization.htm

一、源题
QUESTION 20
You execute this command to drop the ITEM table, which has the primary key referred in the ORDERS table: SQL> DROP TABLE scott.item CASCADE CONSTRAINTS PURGE; Which two statements are true about the effect of the command? (Choose two.)
A. No flashback is possible to bring back the ITEM table.
B. The ORDERS table is dropped along with the ITEM table.
C. The dependent referential integrity constraints in the ORDERS table are disabled.
D. The dependent referential integrity constraints in the ORDERS table are removed.
E. The table definition of the ITEM table and associated indexes are placed in the recycle bin.
Correct Answer: AD
二、题目翻译
执行下面命令删除ITEM表,主键被ORDERS表参照:
SQL> DROP TABLE scott.item CASCADE CONSTRAINTS PURGE;
哪些语句是正确的关于上述命令的影响?(选择两个)
A. 不能使用flashback找回ITEM表
B. ORDERS表随ITEM表一起删除
C. ORDERS表的参照性完整约束失效
D. ORDERS表的参照性完整约束删除
E. ITEM的表定义和相关的索引被放到回收站
三、题目解析
答案:AD
由于使用了PURGE,不会讲表和相关的对象放入回收站,因此flashback不能用,A对,E错
指定CASCADE CONSTRAINTS 语句是指删除所有的参照完整性约束包括主键约束和唯一性约束。
如果不指定此语句,则参照完整性约束还存在,drop table时会返回一个错误。C错,D对
B错误,如果想实现这个需要级联删除,
参考文档:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9003.htm#SQLRF01806
四、测试

SQL> conn scott/oracle
SQL> drop table emp1;
SQL> create table emp1 as select * from emp;
SQL> create table dept1 as select * from dept;
----使用了on delete cascade语句,用于删除父表一条记录的时候,子表记录也要删除
SQL> alter table emp1 add constraint fk_deptno1 foreign key (deptno) references dept1(deptno) on delete cascade;
SQL> alter table emp1 drop constraint fk_deptno1;
----这里使用了on delete set null语句,当父表删除的时候,子表记录就会设置为空
SQL> alter table emp1 add constraint fk_deptno1 foreign key (deptno) references dept1(deptno) on delete set null;
SQL> drop table emp1 cascade constraints purge;

一、源题
QUESTION 21
View the Exhibit and examine the privileges granted to the SL_REP user. The EMP table is owned by the SCOTT user. The SL_REP user executes the following command: SQL> GRANT SELECT ON scott.emp TO hr;

image.png

Which statement describes the outcome of the command?
A. The command executes successfully
B. The command produces an error because the EMP table is owned by SCOTT
C. The command produces an error because SL_REP has the GRANT ANY OBJECT PRIVILEGE without ADMIN_OPTION
D. The command produces an error because SL_REP does not have the SELECT privilege with GRANT_OPTION on the EMP table
Correct Answer: A
二、题目翻译
查看下图并检查授予SL_REP用户的权限。EMP表的拥有者是SCOTT用户。SL_REP用户执行下面的命令:
SQL> GRANT SELECT ON scott.emp TO hr;
哪些关于输出结果的命令是正确的?
A. 命令执行成功
B. 命令产生一个错误因为EMP表的拥有者是SCOTT
C. 命令产生一个错误因为SL_REP用户GRANT ANY OBJECT PRIVILEGE没使用ADMIN_OPTION
D. 命令产生一个错误因为SL_REP没有SELECT权限在EMP表上

答案:A
三、题目解析
A对,因为SL_REP用户拥有GRANT ANY OBJECT PRIVILEGE权限,所以可以授予别的用户SELECT权限
四、测试

SQL> conn / as sysdba
Connected.
--1、创建SL_REP用户
SQL> create user sl_rep identified by oracle;

User created.
--2、授予相应的权限
SQL> grant connect,resource to sl_rep;

Grant succeeded.

SQL> grant grant any object privilege to sl_rep;

Grant succeeded.
--3、执行上面的命令
SQL> conn sl_rep/oracle
Connected.
SQL> grant select on scott.emp to hr;

Grant succeeded.

一、源题
QUESTION 22
You executed this command to create a temporary table:
SQL> CREATE GLOBAL TEMPORARY TABLE report_work_area ( startdate DATE, enddate DATE, class CHAR(20) ) ON COMMIT PRESERVE ROWS;
Which statement is true about the rows inserted into the REPORT_WORK_AREA table during a transaction?
A. The rows stay in the table only until session termination
B. The rows stay in the table only until the next transaction starts on the table
C. The rows are visible to all current sessions after the transaction in committed
D. The rows stay available for subsequent sessions after the transaction is committed
Correct Answer: A
二、题目翻译
执行下面命令创建一个临时表:
SQL> CREATE GLOBAL TEMPORARY TABLE report_work_area
( startdate DATE,
enddate DATE,
class CHAR(20)
) ON COMMIT PRESERVE ROWS;
哪些语句是正确的关于一个事务中,在表REPORT_WORK_AREA中插入行?
A. 表中的行保持直到会话中止
B. 表中的行保持直到下个事务开始
C. 事务提交后行是可见的对所有当前会话来说
D. 事务提交后行依旧可用对后续会话

答案:A
三、题目解析
因为使用了ON COMMIT PRESERVE ROWS;即指定插入行的生存期为会话的持续时间
ON COMMIT DELETE ROWS :指定插入行的生存期仅为事务处理的持续时间

参考官方文档:
http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables.htm#ADMIN11633
四、测试

--即指定插入行的生存期为会话的持续时间
SQL>  CREATE GLOBAL TEMPORARY TABLE report_work_area
  2  ( startdate DATE, 
  3  enddate DATE, 
  4  class CHAR(20) 
  5  ) ON COMMIT PRESERVE ROWS;

Table created.

SQL> insert into report_work_area values ('23-jan-2007','25-may-2008','一班');

1 row created.

SQL> insert into report_work_area values ('23-oct-2007','25-nov-2008','一班');

1 row created.
--提交了事务,还有记录
SQL> commit;

Commit complete.

SQL> select * from report_work_area;

STARTDATE          ENDDATE            CLASS
------------------ ------------------ --------------------
23-JAN-07          25-MAY-08          一班
23-OCT-07          25-NOV-08          一班
--删除之前的表report_work_area
SQL> drop table report_work_area;

Table dropped.
--指定插入行的生存期仅为事务处理的持续时间
SQL> CREATE GLOBAL TEMPORARY TABLE report_work_area
( startdate DATE, 
  3  enddate DATE, 
  4  class CHAR(20) 
  5  ) ON COMMIT delete rows;

Table created.

SQL> insert into report_work_area values ('23-oct-2007','25-nov-2008','一班');

1 row created.

SQL> insert into report_work_area values ('23-jan-2007','25-may-2008','一班');

1 row created.
--提交了事务,记录没有了,因为只持续到事务结束
SQL> commit;

Commit complete.

SQL> select * from report_work_area;

no rows selected

一、源题
QUESTION 23
You want to access employee details contained in flat files as part of the EMPLOYEE table. You plan to add a new column to the EMPLOYEE table to achieve this. Which data types would you use for the new column?
A. CLOB
B. BLOB
C. BFILE
D. LONG RAW
Correct Answer: C
答案:C
二、题目翻译
你想把包含在平面文件中的员工详细信息作为EMPLOYEE表的一部分。你打算添加一个新的列实现。哪种数据类型你需要使用在新的列?
A. CLOB
B. BLOB
C. BFILE
D. LONG RAW
答案:C
三、题目解析
CLOB字符型大对象。一般存储大数量文本信息。存储单字节,固定宽度的数据。
BLOB二进制大对象。存储在数据库里的大对象,一般是图像声音等文件。
BFILE二进制文件,存储在数据库外的操作系统文件,只读的。把此文件当二进制处理。
LONG RAW 为未加工的可变长二进制数据,最长2G(不建议使用)

一、源题
QUESTION 24
The HR user creates a stand-alone procedure as follows and grants the EXECUTE privilege on the procedure to many database users:

CREATE OR REPLACE PROCEDURE create_dept ( v_deptno NUMBER, v_dname VARCHAR2, v_mgr NUMBER, v_loc NUMBER) 
BEGIN
 INSERT INTO hr.departments VALUES (v_deptno, v_dname, v_mgr, v_loc); 
END; 

The users having permission to execute the procedure are able to insert records into the DEPARTMENTS table even though they do not have the INSERT privilege on the table. You want only those users who have privileges on the DEPARTMENTS table to be able to execute the procedure successfully. What would you suggest to the PL/SQL developers to achieve this?
A. Create the procedure with definer's right.
B. Create the procedure with invoker's right.
C. Grant the EXECUTE privilege with GRANT OPTION on the procedure to selected users.
D. Create the procedure as part of a PL/SQL package and grant the EXECUTE privilege on the package to selected users.
Correct Answer: B
二、题目翻译
HR用户创建一个标准存储过程并授予EXECUTE权限给数据库用户:
CREATE OR REPLACE PROCEDURE create_dept ( v_deptno NUMBER, v_dname VARCHAR2, v_mgr NUMBER, v_loc NUMBER)
BEGIN
INSERT INTO hr.departments VALUES (v_deptno, v_dname, v_mgr, v_loc);
END;

用户拥有权限执行过程也能够插入记录到DEPARTMENTS表,即使它没有INSERT表的权限。你想拥有DEPARTMENTS表权限的用户能成功执行过程。你建议PL/SQL开发人员如何实现?
A. 创建过程使用definer身份
B. 创建过程使用invoker身份
C. 使用GRANT OPTION授予EXECUTE权限给选择的用户
D. 创建过程作为包的一部分并且授予package EXECUTE权限给选择的用户

答案:B
三、题目解析
A: 错误,应创建调用者权限,而不是定义者权限
B: 正确
C: 错误,默认是以定义者权限创建存储过程,那么有execute权限就可以向部门表insert数据 不能实现需求grant option权限是指可以把存储过程权限授权给其它人
D: 错误,存储过程可单独创建,不需要放到包里,这样也不能实现题里的需求

一、源题
QUESTION 25
View the Exhibit to examine the details for an incident. Which statement is true regarding the status of the incident?

image.png

A. The DBA is working on the incident and prefers that the incident be kept in the ADR
B. The incident is now in the Done state and the ADR can select the incident to be purged
C. The incident has been newly created and is in the process of collecting diagnostic information
D. The data collection for the incident is complete and the incident can be packaged and sent to Oracle Support
Correct Answer: D
二、题目翻译
查看图表检查incident的详细信息。哪些语句是正确的关于incident的状态?
A. DBA正在处理incident并且更喜欢将incident保存在ADR中
B. Incident现在处于Done状态并且ADR可以选择清理incident
C. Incident被新建并且正在收集诊断信息
D. Incident收集信息已经完成并且可以被打包发送给Oracle Support

答案:D
三、题目解析
使用Enterprise Manager 支持工作台,可以通过执行以下常规步骤来调查、报告并(在某些情况下)解决问题:

  1. 在Enterprise Manager 的“Database(数据库)”主页上,复查严重错误预警。选择一个预警查看详细资料。
  2. 分析问题详细信息并查看针对问题所记录的所有意外事件的列表。可显示自动运行的所有健康状况检查的查找结果。
  3. (可选)运行附加健康状况检查并调用SQL Test Case Builder,该程序将收集与SQL 问题相关的所有必需数据,并且以Oracle 技术支持能够再现该问题的方式对这些信息进行打包。
  4. 使用My Oracle Support 创建一个服务请求并(可选)随问题信息一起记录服务请求号。
  5. 调用意外事件打包服务,该服务可以对收集到的有关某个问题的所有诊断数据进行打包,可以选择将这些数据上载到Oracle 技术支持。上载之前可以对数据进行编辑,删除敏感信息。
  6. 可以在支持工作台中维护一份服务请求的活动日志。可运行Oracle 指导以帮助修复SQL 故障或损坏的数据。
  7. 为一个、一些或所有意外事件设置状态,以便关闭问题

参考官方文档:
http://docs.oracle.com/cd/E11882_01/server.112/e25494/diag.htm#ADMIN12484

http://docs.oracle.com/cd/E11882_01/server.112/e10897/problems.htm#ADMQS12110

一、源题
QUESTION 26
What can you achieve by implementing reverse key index?
A. Reverse the bytes of each column indexed including the row ID
B. Store a bitmap for each key value instead of a list of row IDs in the leaf node
C. Prevent contention on the highest leaf block when using sequences to generate keys
D. Remove repeated key values from the index to fit more index entries in a given amount of disk space
Correct Answer: C
二、题目翻译
你可以通过反向键索引实现什么?
A. 反转每个索引列的字节包括row ID
B. 存储每个键值的位图代替叶节点的一系统row ID
C. 避免争用最高的叶子块当使用序列产生键值时
D. 删除重复的索引键值适合更多的索引条目在给定数量的磁盘空间

答案:C
三、题目解析
A改变的是字符顺序,不是列顺序
B描述的是bitmap index
C正确
D Remove repeated key values 可以尝试用函数索引 substr()达到

参考官方文档:
http://docs.oracle.com/cd/E11882_01/server.112/e40540/indexiot.htm#CNCPT1181

一、源题
QUESTION 27
You executed the following command to perform a backup of the USERS tablespace:
SQL> ALTER TABLESPACE users BEGIN BACKUP;
ALTER TABLESPACE users BEGIN BACKUP * ERROR at line 1: ORA-01123: cannot start online backup; media recovery not enabled
What could be the reason for this error?
A. The MTTR Advisor is disabled.
B. The database is in NOARCHIVELOG mode.
C. The tablespace is already in backup mode.
D. The Flash Recovery Area is not configured.
Correct Answer: B
二、题目翻译
执行下面的命令备份USERS表空间
SQL> ALTER TABLESPACE users BEGIN BACKUP;
ALTER TABLESPACE users BEGIN BACKUP

ERROR at line 1:
ORA-01123: cannot start online backup;
media recovery not enabled
什么导致整个错误
A. MTTR Advisor禁用
B. 数据库处于NOARCHIVELOG模式
C. 表空间已经处于备份模式
D. Flash Recovery Area没有配置

答案:B
三、题目解析
对数据库进行热备,必须处于归档模式。
出现这个报错是因为数据库没有开归档,所以B对

一、源题
QUESTION 28
Which statements listed below describe the data dictionary views?

  1. These are stored in the SYSTEM tablespace
  2. These are the based on the virtual tables
  3. These are owned by the SYS user
  4. These can be queried by a normal user only if O7_DICTIONARY_ACCESSIBLILITY parameter is set to TRUE
  5. The V$FIXED_TABLE view can be queried to list the names of these views
    A. 1 and 3
    B. 2,3 and 5
    C. 1,2, and 5
    D. 2,3,4 and 5
    Correct Answer: A
    二、题目翻译
    下列哪些语句描述的是数据字典视图?
  6.    存储在SYSTEM表空间
    
  7.    基于虚拟表
    
  8.    SYS用户拥有
    
  9.    可以被正常的用户查询,只要O7_DICTIONARY_ACCESSIBLILITY参数设置为TRUE
    
  10.    V$FIXED_TABLE可以被查询列出这些视图的名字
    

A. 1和3
B. 2,3和5
C. 1,2和5
D. 2,3,4和5

答案:A
三、题目解析
1,3它们都被存在system表空间以及owner都是sys用户
2动态性能视图以基于数据库服务器内部的内存结构构建的虚拟表为基础
4 O7_DICTIONARY_ACCESSIBILTY设置为TRUE,则如果用户授予了如select any table等权限,即使不是dba或sysdba用户也可以访问数据字典。设置为FLASE,则普通用户不能访问
5 V$FIXED_TABLE只能查询动能性能视图所有的名称。

SQL> show parameter DICTIONARY

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY          boolean     FALSE
SQL> select * from V$FIXED_TABLE where rownum<5;

NAME                            OBJECT_ID TYPE   TABLE_NUM
------------------------------ ---------- ----- ----------
X$KQFTA                        4294950912 TABLE          0
X$KQFVI                        4294950913 TABLE          1
X$KQFVT                        4294951149 TABLE          2
X$KQFDT                        4294950914 TABLE          3

SQL> select * from V$FIXED_TABLE where rownum<10;

NAME                            OBJECT_ID TYPE   TABLE_NUM
------------------------------ ---------- ----- ----------
X$KQFTA                        4294950912 TABLE          0
X$KQFVI                        4294950913 TABLE          1
X$KQFVT                        4294951149 TABLE          2
X$KQFDT                        4294950914 TABLE          3
X$KQFCO                        4294951036 TABLE          4
X$KQFOPT                       4294952712 TABLE          5
X$KYWMPCTAB                    4294952922 TABLE          6
X$KYWMWRCTAB                   4294953009 TABLE          7
X$KYWMCLTAB                    4294952923 TABLE          8

9 rows selected.

一、源题
QUESTION 29
View the Exhibit to examine the error that occurred during the database startup. You opened an RMAN session for the database. To repair the failure, you executed the following command as the first RMAN command:
RMAN> REPAIR FAILURE;

image.png

Which statement describes the consequence of this command?
A. The command performs the recovery and closes the failure
B. The command only displays the advice and the RMAN script required for recovery
C. The command executes the RMAN script to repair the failure and remove the entry from the Automatic Diagnostic Repository (ADR)
D. The command produces an error because the ADVISE FAILURE command was not executed before the REPAIR FAILURE command
Correct Answer: D
二、题目翻译
查看图表查看数据库启动期间发生的错误。你打开一个数据库的RMAN会话。去修复这个故障,你执行下面的命令作为第一个RMAN命令:
RMAN> REPAIR FAILURE;
哪个语句描述命令的结果?

A. 命令执行recovery并且关闭故障
B. 命令只显示建议和恢复需要的RMAN脚本
C. 命令执行RMAN脚本修复故障并从ADR删除信息
D. 命令产生一个错误因为ADVISE FAILURE命令没有在REPAIR FAILURE之前执行

答案:D
三、题目解析
执行步骤
list failure
advise failure
repair failure
change failure
ADR(automatic diagnostic repository) 自动诊断存储库,ADR提供了大量新的信息以及新的工具以方便数据库管理

一、源题
QUESTION 30
You execute this command to drop the ITEM table, which has the primary key referred in the ORDERS table:
SQL> DROP TABLE scott.item CASCADE CONSTRAINTS PURGE;
Which two statements are true about the effect of the command? (Choose two.)
A. No flashback is possible to bring back the ITEM table
B. The ORDERS table is dropped along with the ITEM table
C. The dependent referential integrity constraints in the ORDERS table are disabled
D. The dependent referential integrity constraints in the ORDERS table are removed
E. The table definition of the ITEM table and associated indexes are placed in the recycle bin
Correct Answer: AD

一、源题
QUESTION 31
You plan to move data from a flat file to a table in your database. You decide to use SQL*Loader direct path load method to perform this task. The table in which you plan to load data in an important table having various integrity constraint defined on it. Which constraints will remain enabled by default during this operation? (Choose all that apply.)
A. CHECK
B. UNIQUE
C. NOT NULL
D. PRIMARY KEY
E. FOREIGN KEY

二、题目翻译
你打算从平面文件移动数据到你的数据库。你决定使用SQL*Loader直接路径加载方法来执行该任务。你打算加载数据的重要表上定义的有各种完整性约束。哪些约束将默认启动在这个操作期间?(选择所有适用的)
A. CHECK
B. UNIQUE
C. NOT NULL
D. PRIMARY KEY
E. FOREIGN KEY

答案:BCD
三、题目解析


image.png

一、源题
QUESTION 32
What is the effect of this command?
SQL> AUDIT DROP ANY TABLE BY scott BY SESSION WHENEVER SUCCESSFUL;
A. One audit record is created for every successful DROP TABLE command executed in the session of SCOTT
B. One audit record is generated for the session when SCOTT grants the DROP ANY TABLE privilege to other users
C. One audit record is created for the whole session if user SCOTT successfully drops one or more tables in his session
D. One audit record is created for every session of any other user in which a table owned by SCOTT is dropped successfully
E. One audit record is created for every successful DROP TABLE command executed by any user to drop tables owned by SCOTT
Correct Answer: C
三、题目解析
指定审计选项
SQL 语句审计:AUDIT table;
上面显示的语句可审计影响表的任何数据定义语言(DDL) 语句,包括CREATE TABLE、DROP TABLE和TRUNCATE TABLE等。
也可按用户名或者按成功或失败来设置SQL 语句审计的重点:
SQL> AUDIT TABLE BY hr WHENEVER NOT SUCCESSFUL;
系统权限审计:可用来审计行使的任何系统权限(如DROP ANY TABLE)。可按用户名或者成功或失败设置审计的重点。
默认情况下,审计设置为BY ACCESS。
每次行使经审计的系统权限时,都会生成一条审计记录。
可选择使用BY SESSION子句将这些记录组成一组,以便每个会话只生成一条记录。
(这样,如果一个用户针对另一个用户的表发出了多条更新语句,则只收集一条审计记录。)
使用BY SESSION子句来限制由于审计系统权限而对性能和存储产生的影响。
系统权限审计(非重点和重点):
AUDIT select any table, create any trigger;
AUDIT select any table BY hr BY SESSION;
对象权限审计:可用来审计关于表、视图、过程、序列、目录和用户定义数据类型的操作。
这种审计类型可按成功或失败设置审计的重点,而且可以按会话或访问权限分组。
与系统权限审计不同,默认情况下,对象权限审计按会话分组。
如果要为每个操作分别生成一条审计线索记录,必须显式指定BY ACCESS。
对象权限审计(非重点和重点):
AUDIT ALL on hr.employees;
AUDIT UPDATE,DELETE on hr.employees BY ACCESS;

一、源题
QUESTION 33
User A executes the following command to drop a large table in your database: SQL> DROP TABLE trans;
While the drop table operation is in progress; user B executes the following command on the same table;
SQL> DELETE FROM trans WHERE tr_type='SL';
Which statement is true regarding the DELETE command?
A. It fails to delete the records because the records are locked in the SHARE mode
B. It deletes the rows successfully because the table is locked in the SHARE mod
C. It fails to delete the records because the table is locked in EXCLUSIVE mode
D. It deletes the rows successfully because the table is locked in SHARE ROW EXCLUSIVE mode
Correct Answer: C
二、题目翻译
用户A执行下面的命令删除数据库中的大表:
SQL> DROP TABLE trans;
当删除表操作正在进行时;用户B执行下面的命令在相同的表;
SQL> DELETE FROM trans WHERE tr_type='SL';
哪些语句是正确的关于DELETE命令?
A. 删除记录失败因为记录被锁处于SHARE模式
B. 删除行成功因为表被锁处于SHARE模式
C. 删除记录失败因为表被锁处于EXCLUSIVE模式
D. 删除行成功因为表被锁处于SHARE ROW EXCLUSIVE模式

答案:C
三、题目解析
当执行alter table,drop table,drop index,truncate table,lock exclusive的时候会造成exclusive锁,因此B错,C对
当执行lock share row exclusive的时候会造成共享排他锁,排除D
当执行insert,update,delete,lock row share的时候会造成行级排他锁,在提交前不允许做dml操作,因此排除A

参考官方文档:
http://docs.oracle.com/cd/E11882_01/server.112/e40540/consist.htm#CNCPT1339

一、源题
QUESTION 34
In which situation may the UNDO_RETENTION parameter be ignored, even if it is set to a value?
A. When the data file of the undo tablespace is autoextensible
B. When there are more than one undo tablespace available in the database
C. When the undo tablespace is of a fixed size and retention guarantee is not enabled
D. When the undo tablespace is autoextensible and retention guarantee is not enabled
Correct Answer: C
二、题目翻译
在什么情况下UNDO_RETENTION参数即使设置了,也不起作用?
A. 当undo表空间的数据文件是自动扩展的时候
B. 当数据库有不只一个undo表空间可用的时候
C. 当undo表空间是固定尺寸且retention guarantee没有启用的时候
D. 当undo表空间是自动扩展且retention guarantee没有启用的时候

答案:C
三、题目解析
此题考的是UNDO_RETENTION 即使设置了值,在哪些情况下会忽略。
在固定大小的还原表空间,且没有设置retention guarantee 强制保留,UNDO_RETENTION 就会被忽略。如果设置了retention guarantee ,即使是固定大小的表空间,也不会忽略UNDO_RETENTION,但有可能出现事务失败。

参考官方文档:
http://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams265.htm#REFRN10225

一、源题
QUESTION 35
You perform differential incremental level 1 backups of your database on each working day and level 0 backup on Sunday to tape: Which two statements are true about differential incremental backups? (Choose two.)
A. The backup performed on Sundays contains all the blocks that have ever been used in the database
B. The backup performed on Sundays contains all the blocks that have changed since the last level 1 backup
C. The backup performed on each working day contains all the blocks that have changed since the last level 0 backup
D. The backup performed on Monday contains all the blocks that have changed since the level 0 backup and every other working day contains all the blocks that have changed since the level 1 backup
Correct Answer: AD
二、题目翻译
你在你的数据库工作日执行level 1差异增量备份,在周日执行level 0差异增量备份到磁带上;
哪两句是正确的关于差异增量备份?(选择两个)

A. 每周日执行的备份包括数据库使用过的所有的块
B. 每周日执行的备份包括所有自从上次level 1备份改变的块
C. 工作日执行的备份包括所有自从上次level 0备份改变的块
D. 周一执行的备份包括自从level 0备份改变的块和其它工作日自从level 1备份改变的块

答案:AD
三、题目解析
执行增量备份操作时,首先需要的是增量基本备份(incremental base backup),即level 0,今后所有的增量备份都是基于这个基本备份的。
0级增量备份会备份所有曾经在此数据库中使用的块。
从星期一到星期六的每一天,1级增量备份备份最近1或0级增量备份以来所有更改过的块。周一备份周日0级备份以来变化的块,周二备份周一1级备份改变的块,等等。

参考官方文档:
http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmcncpt.htm#sthref707

推荐阅读更多精彩内容