Oracle 19C OCP-总结——082-高频易错题

一、关于views

68.Which two statements are true about views used for viewing tablespace and datafile information? (Choose two.)

A . Tablespace free space can be viewed in V$TABLESPACE
B . V$TABLESPACE displays information that is contained in the controlfile about tablespaces
C . V$TABLESPACE displays information about tablespaces contained in the data dictionary
D . Tablespace free space can be viewed in DBA_TABLESPACES
E . A datafile can be renamed when the database is in MOUNT state and the new file name is displayed when querying DBA_DATA_FILES after the database is opened

标准答案 :BE

132.Choose three. Which three statements are true about views in an Oracle Database?

A . Rows inserted into a table using a view are retained in the table if the view is dropped.
B . A view can be created that refers to a non-existent table in its defining query.
C . Views have no object number.
D . Views have no segment.
E . A SELECT statement cannot contain a WHERE clause when querying a view containing a WHERE clause in its defining query.
F . Views can join tables only if they belong to the same schema.

标准答案:ABD

167.choose two Which two statements are true about views?

A . Views can be updated without the need to re-grant privileges on the view.
B . Views can be indexed.
C . The WITH CHECK clause prevents certain rows from being updated or inserted in the underlying table through the view.
D . The WITH CHECK clause prevents certain rows from being displayed when querying the view.
E . Tables in the defining query of a view must always exist in order to create the view.
标准答案 :AC

视图没有段，有对象编号，不能被索引，

二、关于index

195.Choose three. Which three statements are true about indexes and their administration in an Oracle database?

A . The same table column can be part of a unique and non-unique index.
B . If a query filters on an indexed column then it will always be used during execution of the query.
C . A descending index is a type of function-based index.--降序索引是一种基于函数的索引。
D . An INVISIBLE index is not maintained when DML is performed on its underlying table. DML INVISIBLE
E . An UNUSABLE index is maintained when DML is performed on its underlying table. DML UNUSABLE
F . An index can be created as part of a CREATE TABLE statement. CREATE TABLE
标准答案 :ACF

索引默认都是升序；

在 CREATE 语句中如果加上主键约束或者唯一约束，则会自动创建索引。

只要索引的基表在物理上被移动了就会失效,比如 move 到一个表空间，就是其中一种原因。

create index 索引名 on 表名 (字段名 desc);

--唯一索引的一部分 SQL> create unique index e_u_name on emp (ename,sal); 
--非唯一索引的一部分 SQL> create index e_u2_name on emp (ename,mgr);

202.Choose two. You have been granted the CREATE ANY INDEX system privilege. You want to create an index, owned by you, on a table in another schema to which you have been granted only the SELECT privilege. Which two are true?

A . You must have either the UNLIMITED TABLESPACE system privilege, or sufficient quota on the tablespace where the index will be created.
B . You additionally require the INSERT, UPDATE, DELETE, and INDEX privileges on the table.
C . You do not require any further object privileges on the table.
D . You additionally require only the INDEX privilege on the table.
E . The owner of the table must also have the CREATE ANY INDEX system privilege.
F . The owner of the table must have either the UNLIMITED TABLESPACE system privilege, or sufficient quota on the tablespace where the index will be created.
标准答案 :AC
试题解析 :
解析：实验证明，帮别人创建索引时，由于该索引属于创建人，所以创建人需要有相应的空间来存放索引；只要有该表 select 的权限，然后有 select any table 的权限就可以创建其它用户表的索引了

174.Choose two. Which two statements are true regarding indexes?

A . The RECYCLE BIN never contains indexes. RECYCLE BIN
B . An update to a table can result in updates to any or all of the table's indexes.
C . A non-unique index can be altered to be unique.
D . An update to a table can result in no updates to any of the table's indexes.
E . table belonging to one user cannot have an index that belongs to a different user.
标准答案 :BD
试题解析 :
解析：索引应该会被放到回收站，只不过我们看不到而已，通过实验发现表被闪回后，原来的索引的名字都被改成跟回收站的名字一样；一个 update 操作如果修改了带有索引的列，则就会同时更新，如果没有就不会更新

113.Which three statements are true regarding indexes? (Choose three.)
A . A UNIQUE index can be altered to be non-unique
B . A SELECT statement can access one or more indices without accessing any tables 
C . A table belonging to one user can have an index that belongs to a different user
D . An update to a table can result in updates to any or all of the table's indexes
E . When a table is dropped and is moved to the RECYCLE BIN, all indexes built on that table are permanently dropped
F . An update to a table can result in no updates to any of the table's indexes
标准答案 :BCD

三、关于synonyms

8.Which two statements are true about Oracle synonyms?

A . Any user can create a PUBLIC synonym. PUBLIC
B . A synonym has an object number.
C . A synonym can have a synonym.
D . All private synonym names must be unique in the database.
E . A synonym can be created on an object in a package.

标准答案：BC

114.Which three statements are true about Oracle synonyms?

A . A synonym cannot be created for a PL/SQL package PL/SQL
B . A synonym can be available to all users
C . A SEQUENCE can have a synonym
D . A synonym created by one user can refer to an object belonging to another user
E . Any user can drop a PUBLIC synonym PUBLIC
标准答案 : BCD
试题解析 :
解析：同义词可以在表、存储过程、序列上创建。

如果是 public 同义词，则对所以的用户都可以访问。创建不同的同义词都需要授予不同的权限，只有属主和有 drop public synonym权限的用户才能够删除 public 同义词.

四、关于Enterprise Manager Cloud Control

237.Choose three. Which three statements are true about Enterprise Manager Cloud Control?

A . It is available to manage a database only when that database is open.
B . It is integrated with My Oracle Support.
C . It provides management for Oracle middleware.
D . It provides management for Oracle-engineered systems.
E . It uses a web-based console built into the Oracle database using XML DB.

标准答案 :BCD

五、关于UNUSED

53.Which three statements are true about dropping and unused columns in an Oracle database? (Choose three.) Oracle

A . A primary key column referenced by another column as a foreign key can be dropped if using the CASCADE option.
B . An UNUSED column's space is reclaimed automatically when the block containing that column is next queried.
C . An UNUSED column's space is reclaimed automatically when the row containing that column is next queried.
D . Partition key columns cannot be dropped.
E . A DROP COLUMN command can be rolled back.
F . A column that is set to UNUSED still counts towards the limit of 1000 columns per table.
标准答案 :ADF
试题解析 :解析：被设置为 unused 列应该还算在 1000 个列的限制里面。

六、关于date and timestamp

196.Choose the best answer. Examine these statements which execute successfully:
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS' ;
ALTER SESSION SET TIME_ZONE = '-5:00' ;
SELECT DBTIMEZONE, SYSDATE FROM DUAL;
Examine the result:
DBTIMEZONE SYSDATE
------------- ------------------------------------------
+00.00 11- JUL-2019 11:00:00
If LOCALTIMESTAMP was selected at the same time, what would it return?

A . 11-JUL-2019 6.00.00.00000000 AM
B . 11-JUL-2019 11.00.00.00000000 AM
C . 11-JUL-2019 11.00.00.0000000 AM -05:00
D . 11-JUL-2019 6.00.00.00000000 AM -05:00
标准答案 :
A

七、关于single-row functions

158.Choose three. Which three statements are true about single-row functions?

A . They can accept only one argument.
B . They can be nested to any level.
C . They return a single result row per table.
D . The data type returned can be different from the data type of the argument.
E . The argument can be a column name, variable, literal or an expression.
F . They can be used only in the WHERE clause of a SELECT statement.
标准答案 :BDE

从单行函数的定义和语法可以判断答案 BDE 是对的。答案 B 只要符合规律，可以嵌套任何层次。) 单行函数的定义如下：操作数据项接收参数并返回一个值在返回的每一行上进行处理每一行都有一个处理结果可能要修改数据类型可以进行嵌套 语法：function_name (column|expression, [arg1, arg2,...]) 可以有多个参数：CONCAT(Good, String)

八、关于non-equijoins

204.Choose two. Which two statements are true regarding non-equijoins?
A . The USING clause can be used.
B . Table aliases must be used.
C . The Oracle join syntax can be used.
D . The SQL:1999 compliant ANSI join syntax must be used.
E . The ON clause can be used.
标准答案 :CE

解析：非等连接 oracle 语法支持。 join...using:用于两表有同名字段但数据类型不同，或者使用多个同名字段中的某一个做等值连接 join...on :最为灵活，可以指明连接的条件

九、关于GLOBAL TEMPORARY TABLES

146.Choose three. Which three statements are true about GLOBAL TEMPORARY TABLES?

A . A GLOBAL TEMPORARY TABLE cannot have PUBLIC SYNONYM.
B . A GLOBAL TEMPORARY TABLE can have multiple indexes.
C . A GLOBAL TEMPORARY TABLE can be referenced in the defining query of a view.
D . Data Manipulation Language (DML) on GLOBAL TEMPORARY TABLES generates no REDO.
E . A GLOBAL TEMPORARY TABLE can have only one index.
F . A trigger can be created on a GLOBAL TEMPORARY TABLE.
标准答案 :BCF
试题解析 :
解析：全局临时表跟普通表一样，可以创建索引、视图、触发器等等，dml 操作同样也会产生 redo。临时表创建的目的就是为了测试，如果很多操作不支持，那就失去了测试的意义

十、关于Resumable Space Allocation 

231.Choose three. Which three statements are true about Resumable Space Allocation in Oracle databases?

A . All sessions must have the same timeout value when waiting for resumable space allocations.
B . A user's session may be suspended and resumed multiple times.
C . A user's session may be suspended even if the user has the UNLIMITED TABLESPACE system privilege.
D . Resumable space allocation is only possible with locally managed tablespaces.
E . The AFTER SUSPEND event trigger can itself be suspended due to space conditions.
F . Resumable space allocation may be enabled for some sessions and not others.
标准答案 :ABC
试题解析 :
解析：经过实验，resumable_timeout 参数不能在会话级修改，所以针对的是所有的会话。

十一、关于virtual columns

181.Choose two. Which two are true about virtual columns?

A . They can be referenced in the WHERE clause of an UPDATE or DELETE statement.
B . They cannot have a data type explicitly specified.
C . They can be indexed.
D . They can be referenced in the SET clause of an UPDATE statement as the name of the column to be updated.
E . They can be referenced in the column expression of another virtual column.
标准答案 :AC
试题解析 :
解析：注意虚拟列与隐含列的区别。
Oracle 11G 在表中引入了虚拟列，虚拟列是一个表达式，在运行时计算，不存储在数据库中， 不能更新虚拟列的值。
定 义 一 个 虚 拟 列 的 语 法 ：
column_name [datatype] [GENERATED ALWAYS] AS [expression] [VIRTUAL]
1.虚拟列可以用在 select，update，delete 语句的 where 条件中，但是不能用于 DML 语句
2. 可以基于虚拟列来做分区
3. 可以在虚拟列上建索引，oracle 的函数索引就类似。
4. 可以在虚拟列上建约束

十二、关于transaction

一个新事务的开始：就是前面先要完成事务，然后紧跟着一个 dml 操作（select for update 也是一个新事务的开始。）

194.Choose three. You currently have an active transaction in your session and have been granted SELECT access to V$TRANSACTION.
Executing: SELECT xid, status FROM v$transaction; . in your session returns:
XID STATUS
------------------------------- -------------
0A0007000A070000 ACTIVE
In which three situations will re-executing this query still return a row but with a different XID,indicating a new transaction has started?

A . after successfully executing a DML statement following a failed DML statement DML
B . after successfully executing a CREATE TABLE statement followed by a CREATE INDEX statement.
C . after successfully executing a TRUNCATE statement followed by a DML statement.
D . after successfully executing a CREATE TABLE AS SELECT statement followed by a SELECT FOR UPDATE statement.
E . after successfully executing a COMMIT or ROLLBACK followed by a SELECT statement.
F . after successfully executing a COMMIT or ROLLBACK followed by a DML statement.
标准答案 :CDF

十三、关于Oracle Serverarchitecture

Q210.Choose four. Which four statements are true about the Oracle Server architecture?

A . A person or program can have more than one session with an instance by logging in with different usernames.
B . A person or program can have more than one session with an instance by logging in with the same username.
C . A connection represents the state of a user's login to an instance.
D . Each server process and background process has their own Program Global Area (PGA).
E . The buffer cache and the redo log buffer are held in the large pool.
F . The entire data dictionary is always cached in the shared pool to improve performance.
G . A session represents the state of a user's login to an instance. 会话表示用户登录实例的状态
标准答案 :ABCG

十四、时间函数

Q24.Which two statements are true about time zones, date data types, and time stamp data types in an Oracle database?
A) The DBTIME ZONE function can return an offset from Universal Coordinated Time (UTC).
B) The SESSIONTIMEZONE function can return an offset from Universal Coordinated Time (UTC).
C) The CURRENT_TIMESTAMP function returns data without time zone information.
D) The DATE data type returns data with time zone information.
E) A TIMESTAMP WITH LOCAL TIMEZONE data type column is stored in the database using the time zone of the session that inserted the row.
Answer:BE

Q97=Q98.Which three statements are true about time zones, date data types, and timestamp data types in an Oracle database? (Choose three.)
A)The CURRENT_TIMESTAMP function returns data without time zone information
B)A TIMESTAMP WITH LOCAL TIMEZONE data type column is stored in the database using the time zone of the session that inserted the row
C)A TIMESTAMP data type column contains information about year, month, and day
D)The DBTIMEZONE function can return an offset from Universal Coordinated Time (UTC)
E)The SESSIONTIMEZONE function can return an offset from Universal Coordinated Time (UTC)

Answer: BCE

Q99.Choose three. Which three statements are true about CURRENT_TIMESTAMP?
A)The value varies depending on the setting of SESSIONTIMEZONE.
B)The date is in the time zone of DBTIMEZONE.
C)It returns a value of data type TIMESTAMP.
D)The time is in the time zone of DBTIMEZONE.
E)It returns the same date as CURRENT_DATE.
F)It always returns the same value as SYSTIMESTAMP.
G)It returns the same time as CURRENT_DATE.
Answer:ACE

Q185.Choose two. Which two statements are true about CURRENT_TIMESTAMP?

A)The time is in the time zone of DBTIMEZONE.
B)It always returns the same value as SYSTIMESTAMP.
C)The date is in the time zone of DBTIMEZONE.
D)The value varies depending on the setting of SESSIONTIMEZONE.
E)It returns the same date as CURRENT_DATE.
F)It returns a value of data type TIMESTAMP.
Answer:DF
解析：该题还有其它的变种题考法。

Q169.Which two statements are true about date/time functions in a session where NLS_DATE_FORMAT is set to DD-MON-YYYY HH24:MI:SS?
A)SYSDATE can be queried only from the DUAL table.
B)SYSDATE and CURRENT_DATE return the current date and time set for the operating system of the client
C)CURRENT_TIMESTAMP returns the same date and time as SYSDATE with additional details of fractional seconds.如果没有下面题E的选项，就选这个。
D)CURRENT_DATE returns the current date and time as per the session time zone.
E)SYSDATE can be used in expressions only if the default date format is DD-MONRR.

F)CURRENT_TIMESTAMP returns the same date as CURRENT_DATE .
Answer:CD

Q42.Which two statements are true about date/time functions in a session where NLS_DATE_FORMAT is set to DD-MON-YYYY HH24:MI:SS? (Choose two.)
A. CURRENT_TIMESTAMP returns the same date and time as SYSDATE with additional details of fractional seconds
B. SYSDATE can be queried only from the DUAL table
C. CURRENT_DATE returns the current date and time as per the session time zone
D. SYSDATE can be used in expressions only if the default date format is DD-MON-RR
E. SYSDATE and CURRENT_DATE return the current date and time set for the operating system of the database server
F. CURRENT_TIMESTAMP returns the same date as CURRENT_DATE
Answer: CE