ORACLE-OCP-19C—IZ082-292道题

1Z0-082 考试通过分数 60%
1Z0-083 考试通过分数 57%

Q1.You issued this command:
DROP TABLE hr employees;
Which three statements are true?
A)Sequences used to populate columns in the HR.EMPLOYEES table are dropped.
B)Synonyms for HR. EMPLOYEES are dropped.
C)Views referencing HR.EMPLOYEES are dropped.
D) The HR.EMPLOYEES table may be moved to the recycle bin.
E) All constraints defined on HR. EMPLOYEES are dropped.
F) All indexes defined on HR. EMPLOYEES are dropped.
Answer:DEF
Q2.Which three statements are true about the naming methods and their features supported by
Oracle database used to resolve connection information?
A) A client can connect to an Orade database instance even if no client side network admin has
been configured.
B) Directory Naming requires setting the TNS ADMIN environment variable on the client side.
C) Directory Naming can be usedif Connect-Time Failover is required.
D) Easy Connect supports TCP/IP and SSL.
E) Local Naming requires setting the TNS_ADMIN environment variable on the cdient side.
F) Local naming can be used if Connect-Time Failover is required.
Answer:AEF
Q3.Which three statements are true about multiple row subqueries?
A) They can contain GROUP BY clauses.
B) They can return multiple columns.
C) Two or more values are always returned from the subquery.
D) They cannot contain a subquery.
E) They can contain HAVING clauses.
Answer:ACE
Q4.
Examine the description of the books_transactions table:
Name null? Type
------------------------------------------------ ------------------ ----------------------------------------
TRANSACTION _ID NOT NULL VARCHAR2(6)
TRANSACTION_TYPE VARCHAR2(3)
BORROWED_ DATE DATE
BOOK_ID VARCHAR2(6)
MEMBER_ID VARCHAR2(6)
Examine this partial sQL statement:
SELECT * FRoM books _transactions

 

Which two WHERE conditions give the same result?
A)WHERE borrowed_date= SYSDATE AND (transaction_type='RM' AND (member_id ='A101' OR
member_id="A102'));
B)WHERE borrowed_date = SYSDATE AND transaction_type ='RM' OR member_id IN
('A101','A102');
C)WHERE borrowed_date = SYSDATE AND (transaction_type = 'RM' OR member_id IN ('A101'，
'A102'));
D)WHERE borrowed_date = SYSDATE AND (transaction_type='RM' AND member_id = 'A101 ' OR
member_id='A102') ;
E)WHERE (borrowed_date = SYSDATE AND transaction type = 'RM') OR member_id IN ('A101'，
'A102') ;
Answer:BE
Q5.Which two statements are true about the Oracle join and ANSI join syntax?
A) The Oracle join syntax lacks the ability to do outer joins.
B) The Oracle join syntax performs less well than the SQL:1999 compliant ANSI join syntax.
C)The SQL:1999 compliant ANSI join syntax supports creation of a Cartesian product of two
tables.
D)The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax.
E)The Oracle join syntax supports creation of a Cartesian product of two tables.
Answer:CE
Q6.

Which two statements are true?
A) PRODUCT_PRICE can be used in an arithmetic expression even if it has no value stored in it.
B) EXPIRY_DATE contains the SYSDATE by default if no date is assigned to it.
C) PRODUCT_ID can be assigned the PRIMARY KEY constraint.
D) PRODUCT_PRICE contains the value zero by default if no value is assigned to it.
E) EXPIRY_DATE cannot be used in arithmetic expressions.
F) PRODUCT_NAME cannot contain duplicate values.
Answer:AC

Q7.

 

Which three statements are true?
A) There is no row containing fountain pen.
B) There is no row containing penci1.
C) There is no row containing pen.
D) The code for pen is 1.
E) The code for pen is 10.
F) The code for fountain pen is 3.
Answer:BEF
Q8.Which two statements are true about Oracle synonyms?
A) Any user can create a PUBLIC synonym.
B) A synonym has an object number.
C) A synonym can have a synonym.
D) AlI private synonym names must be unique in the database.
E) A synonym can be created on an object in a package.
Answer:BC
Q9.What is true about non-equijoin statement performance?
A) The Oracle join syntax performs less well than the SQI:1999 compliant ANSI join syntax.
B) The BETWEEN condition used with an non-equijoin always performs better than when using
the >= and <= conditions.
C) The BETWEEN condition used with an non-equijoin sometimes performs better than using
the >= and <= conditions.
D) The join syntax used makes no difference to performance.
E) The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax.
Answer:D
Q10.The SALES table has columns PROD ID and QUANTITY_ SOLD of data type NUMBER.

 

Which two queries execute successfully?
A) SELECT COUNT (prod_id) FROM sales GROUP BY prod id WHERE quantity_ sold > 55000;
B) SELECT COUNT (prod_id) FROM sales WHERE quantity sold > 55000 GROUP BY prod_ id;
C) SELECT prod_id FROM Sales WHERE quantity sold > 55000 AND COUNT(*)> 10 GROUP BY
COUNT(*)>10;
D) SELECT prod id FROM sales WHERE quantity_sold > SS000 AND COUNT(*)>10 GROUP BY
Prod_id HAVING COUNT(*)>10;
E) SELECTprod_id FROM sales WHERE quantity_sold>55000 GROUR BY prod_id HAVING
COUNT(*)>10;
Answer:BE
Q11.

NLS_DATE FORMAT is set to DD-MON-YY.
Which query requires explicit data type conversion?
A) SELECT salary+ '120.50' FROM employees;
B) SELECT join_date 11 ' ' 11 salary FROM employees;
C) SELECT join_date FROM employees WHERE join_date > '10-02-2018';
D) SELECT join_date + '20' FROM employees;
E) SELECT SUBSTR(join_date, 1, 2) - 10 FROM employees;
Answer:C
Q12.Examine this command:
ALTER DATABASE MOVE DATAPILE '/u01/sales01.dbf' '/u02/sales02.dbf';
Which two statements are true?
A) Tables with one or more extents in this data file may be queried during the execution of this
command.
B) DML may be performed on tables with one or more extents in this data file during the
execution of this command.
C) Compressed objects in SAIES01 . DBF will be uncompressed in SALES02.DBP after the move.
D) The "ro" clause containing the new file name must be specified even if Oracle Managed Files
(OMF) is used.
E) It overwrites any existing file with the name BALESO2.DBF in /u02 by default.
Answer:AB
Q13.Which two statements are true about INTERVAL data types?
A) The value in an INTERVAL DAY TO SECOND column can be copied into an INTERVAL YEAR TO
MONTH column.

 

B) INTERVAL YEAR TO MONTH columns only support monthly intervals within a single year.
C) INTERVAL YEAR TOMONTH columns only support monthly intervals within a range of years.
D) INTERVAL YEAR TO MONTH columns support yearly intervals.
E) INTERVAL DAY TO SECOND columns support fractions of seconds.
F) The YEAR field in an INTERVAL YEAR TO MONTH column must be a positive value.
Answer:DE
Q14.Which two statements are true about substitution variables?
A) A substitution variable used to prompt for a column name must be enclosed in single
quotation marks.
B) A substitution variable prefixed with && prompts only once for a value in a session unless it is
set to undefined in the session.
C) A substitution variable can be used with any clause in a select statement.
D) A substitution variable can be used only in a SELECT statement:
E) A substitution variable prefixed with & always prompts only once for a value in a session.
F) A substitution variable used to prompt for a column name must be enclosed in doule
quotation marks.
Answer:BC
Q15.Which three statements are true about sequences in a single instance Oracle database?
A) A sequence can only be dropped by a DBA.
B) A sequence number that was allocated can be rolled back if a transaction fails.
C) A sequence's unallocated cached values are lost if the instance shuts down.
D) Two or more tables cannot have keys generated from the same sequence.
E) Sequences can always have gaps.
F) A sequence can issue duplicate values.
Answer:CEF
Q16.Which two statements are true about the tools used to configure Oracle Net Services?
A)Oracle Net Manager can be used to centrally configure listeners on any database server target.
B)Enterprise Manager Cloud Control can be used to centrally configure net service names for any
database server target.
C)The Oracle Net Configuration Assistant is only used when running the Oracle installer.
D) Oracle Net Manager can be used to locally configure naming methods on a database server.
E)The lsncti utlity requires a listener.ora file to existUefore it is tarted.
Answer:BD
Q17.

 

Which two clauses must you add to the query?
A) ORDER BY last_name DESC, city ASC
B) ORDER BY 1, LNAME DESC
C) WHERE city ='%AN%'
D) WHERE city LIKE '%AN%’
E) WHERE city IN ('%AN%')
F) ORDER BY 1,2
Answer:BD
Q18.Which is the default column or columns for sorting output from compound queries using SET
operators such as INTERSECT in a SQL statement?
A) the first NUMBER column in the first SELECT of the compound query
B) the first column in the first SELECT of the compound query
C) the first NUMBER or VARCHR2 column in the last SELECE of the compound query
D) the first VARCHAR2 column in the first SELECT of the compound query
E) the first column in the last SELECT of the compound query
Answer:B
Q19.Which two statements are true about single row functions?
A) MOD : returns the remainder of a division operation.
B) CONCAT : can be used to combine any number of values.
C) TRUNC : can be used only with NUMBER data types.
D) FLOOR: returns the smallest integer greater than or equal to a specified number.
E) CEIL : can be used for positive and negative numbers.
Answer:AE
解析：题目考的还是单行函数，但是答案不一样了。floor 是向下取整，所以描述的是返
回小于或等于指定数字的最大整数。如果是 ceil 返回的就是大于或等于指定数字的最小整
数。所以答案 B 如果是描述 ceil 函数就是对的。
A、 MOD：返回除法运算的商
B、 FLOOR：返回小于或等于指定数字的最小整数
C、 TRUNC：可用于任意数量组合的值
D、 CONCAT:可用于组合任意数量的值

 

E、 CEIL：可用于正数和负数
FLOOR 是向下取整，正负整数都支持 floor(9.999999) = 9.0 CEIL 是先上取整 正负整数都支
持 ceil(9.999999) = 10 Which three
Q20.

SELECT cust last_name,cust_credit limit FROM customers
Which two WHERE conditions give the required result?
A)WHERE UPPER(cust_last_name) IN ('A%', 'B%') AND cust_credit_limit < 1000;
B)WHERE (INITCAP(cust_last_name) LIKE 'AB' OR INITCAP(cust_last_name) LIKE 'B%') AND
cust_credit_limit < 1000;
C) WHERE UPPER(cust_last_name) BETWEEN UPPER('A8' AND 'B%') AND ROUND
(cust_credit_limit) < 1000;
D) WHERE (UPPER(cust_last_name) LIKE 'AB' OR UPPER(cust_last_name) LIKE 'B%') AND
ROUND(cust_credit_limit) < 1000;
E) WHERE (UPPER(cust_last_name) LIKE INITCAP('A') OR UPPER(cust_last_name) LIKE
INITCAP('B') ) AND ROUND(cust_credit_limit) < ROUND(1000);
Answer:BD
Q21.In the PROMOTIONS table, the PROMO BEGIN DATE column is of data type DATE and the
default date format is DD-MON-RR.
Which two statements are true about expressions using PROMO BEGIN DATE contained in a
query?
A)TO_NUMBER(PROMOBEGINDATE) - 5 will return a number.
B)PROMO_BEGINDATE - SYSDATE will return an error.
C)RROMO_BEGINDATE - 5 will return a date.
D)PROMO_BEGINDATET - SYSDATE will return a number.
E)TO_DATE(RROMO _BECIN_ DATES* 5) will return a date.
Answer:CD
Q22.Which two queries execute successfully?
A) SELECT NULLIF (100,'A') FROM DUAL;
B) SELECT NULLIF (100,100) FROM DUAL;
C) SELECT COALESCE(100, NULL, 200) FROM DUAL;
D) SELECT NULLIF (NULL, 100) FROM DUAL;
E) SELECT COALESCE(100,'A') FROM DUAL;
Answer:BC

 

Q23.The CUSTOMERS table has a CUST LAST NAME column of data type VARCHAR2.
The table has two rows whose CUST LAST NAME values are Anderson and Ausson.
Which query produces output for CUST LAST NAME containing oder for the first row and Aus for
the second?
A) SELECT REPLACE (SUBSTR(Cust. last name, -3)， An', 'O') FROM customers;
B) SELECT REPLACE (REPLACE (cust last name,'son',' ' ), 'An', 'O') FROM customers ;
C) SELECT INITCAP (EPLACE (TRIM('son' FROM cust_ last_ name), 'An', 'o')) FROM customers;
D)SELECT REPLACE (TRIM (TRAILING 'son' FROM cust_last_ name)，'AN' , 'O' ROM customers ;
Answer:B
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:AB
Q25.

Which three statements are true?
A) A customer can exist in many countries.
B) The statement will fail because a subquery may not be contained in a VALUES clause.
C)The statement will execute successfully and a new row will be inserted into the SALES table.
D) The statement will fail if a row already exists in the SALES table for product 23.
E) The SALES table has five foreign keys.
F) A product can have a different unit price at different times.
Answer:CEF
Q26.

 

Which two statements are true?
A) Statement 1 returns only one row of output.
B) Both the statements give the same output.
C) Statement 2 may return multiple rows of out put.
D) Both statements will return NULL if either UNIT_ PRICE or QUANTITY contains NULL.
E) Statement 2 returns only one row of output.
Answer:AC
Q27.Which three statements are true about roles?
A) Roles may be granted to roles.
B) The SET ROLE statement can enable one or more roles for a session.
C) All roles granted to a user are set on by default when the user logs in.
D) Roles must be password protected.
E) The SET ROLE statement can disable one or more roles for a session.
F) Object privileges may not be granted to roles.
Answer:ABC
Q28.Which three statements are true about Data Manipulation Language (DML)?
A) INSERT statements can insert NULLS explicitly into a column.
B) DELETE statements can remove multiple rows based on multiple conditions.
C) DML statements require a primary key be defined on a table.
D) INSERT INTO...SELECT..FROM statements automatically commit.
E) UPDATE statements can have different subqueries to specify the values for each updated
column.
Answer:ABE
Q29.Examine the description of the PRODUCTS table:

Which query is valid?

 

A) SELECT prod_id, MAX (AVG(cost)) FROM products GROUP BY prod_id;
B) SELECT prod_id, AVG (MAX(cost)) FROM products GROUP BY prod_id;
C) SELECT prod_id, release_date, SUM (cost) FROM products GROUP BY prod_id,release_date;
D)SELECT prod_id, release_date, SUM(cost) FROM products GROUP BY prod_id;
Answer：C 解析：答案 C 比较简单，根据规则，没有出现在分组函数中的列必须出现在 group
b 子句中
Q30.Which two statements are true about INTERVAL data types?
A) The value in an INTERVAL DAY TO SECOND column can be copied into an INTERVAL YEAR TO
MONTH column.
B) The YEAR field in an INTERVAL YEAR To MONTH column must be a positive value.
C) INTERVAL YEAR TO MONTH columns only support monthly intervals within a single year.
D) INTERVAL YEAR TO MONTH columns only support monthly intervals within a range of years.
E) INTERVAL DAY TO SECOND columns support factions of seconds.
F) INTERVAL YEAR TO MONTH columns support yearly intervals.
Answer:AE
Q31.Which two statements are true about Enterprise Manager (EM) Express?
A) You can shut down a database instance using EM Express.
B) You cannot start up a database instance using EM Express.
C) By default, EM Express is available for a database after database creation using DBCA.
D) EM Express uses a separate repository database to store target database metadata.
E) You can use a single instance of EM Express to manage multiple databases running on the
same server.
Answer:BC
解析：EM Express 简化版的 em，只要用存储过程设置了端口号就可以访问。
Q32.The ORDERS table has a column ORDER_DATE of data type DATE.
The default display format for a date is DD-MON-RR.
Which two WHERE conditions demonstrate the correct usage of conversion functions?
A) WHERE order_date > TO_CHAR (ADD_MONTHS(SYSDATE, 6), 'MON DD YYYY')
B) WHERE TO_CHAR(order_date, 'MON DD YYYY') = 'JAN 20 2019'
C) WHERE order_date > TO_DATE ('JUL 10 2018’，'MON DD YYYY ‘)
D) WHERE order_date > TO_DATE (ADD MONTHS (SYSDATE, 6), 'MON DD YY’)
E) WHERE order_date IN (TO_DATE('oct 21 2018’，'Mon DD YYYY’), TO_CHAR('Nov 212018'，'Mon
DD YYYY'))
Answer:BC
Q33.Which three statements are true regarding the UNION and UNION ALL operators?
A) Duplicates are eliminated automatially by the UNION ALL operator.
B) The names of columns selected in each SELECT statement must be identical.
C) The number of columns selected in each SELECT statement must be identical.
D) NULLS are not ignored during duplicate checking.

 

E) The number of columns selected by the first SELECT statement can be greater than the number
selected in subsequent SELECT statements.
F) Duplicates can optionally be eliminated by the UNION operator.
G) The names of columns selected in each SELECT Statement can be identical.
Answer:CDG
变种题和 33 题相似：
Which two statements are true regarding the UNION and UNION ALL operators?
A) NULLS are not ignored during duplicate checking
B) The output is sorted by the UNION ALL operator.
C) The number of columns selected in each SELECT statement must be identical
D) Duplicates are eliminated automatially by the UNION ALL operator.
E) E)The names of columns selected in each SELECT Statement can be identical.
Answer:AC
解析：有关 null 的处理方式在本次题库中多次出现，比如在:intersect 和 union 的操作经过
使用证明，null 值的行是不会被汇率的，会被计算在内。

Q34.Examine the description of the PRODUCT STATUS table:

The STATUS column contains the values 'IN STOCK' or 'OUT OF STOCK' for each row.
Which two queries will execute successfully?
A) SELECT prod_id || q' ('s not available) ' FROM product_status WHERE status = 'OUT OF STOCK
1 ';
B) SELECT prod_id|| q' (s' not available) ' "CURRENT AVAILABILITY" FROM product_ status WHERE
status=' OUT OF STOCK ';
C) SELECT prod_id|| q' (s' not available) ' 'CURRENT AVAILABILITT FROM product_status WHERE
status OUT OF STOCK';
D) SELECT prod_id|| q” ' s not available” ROM product_status WHERE status = ‘OUT OF STOCK';
E) SELECT prod_id q’s not available" FROM product_status WHERE status = ‘ OUT OF STOCK';
F) SELECT prod_id “CURRENT AVAILABILITY” ||q’ ( ‘s not available)’ FROM product_status
WHERE statue = ‘ OUT OF STOCK';
Answer:AB
Q35.Examine this description of the BOOKS table containing 100 rows:

 

A) The second ROLLBACK command restores the 100 rows that were in the table originally.
B) The first ROLLBACK command restores the 100 rows that were in the table originally
C) The second ROLLBACK command restores the row that was inserted
D) The first ROLLBACK command restores the row that was inserted.
E)The first ROLLBACK command leaves the table's 100 original rows locked.
F) The second ROLLBACX command rolls back the ROLLBACK TO EAVEPOINT a command
G) The first ROLLRACK command leaves the inserted row locked.
Answer:BDG
Q36.Which two statements are true about UNDO and UNDO tablespaces?
A) UNDO segments are owned by SYSBACKUP.
B) There can be only one UNDO tablespace created in a database.
C) An instance will crash if the active undo tablespace is lost.
D) UNDO segments are owned by SYSTEL.
E) An UNDO tablespace may be owned by only one instance.
Answer:CE
Q37.Which three statements are true about data block storage in an Oracle Database?
A) A data block header is of a fixed length.
B) Row data is stored starting at the end of the block.
C) A block header contains a row directory pointing to all rows in the block.
D) A table block must always contain row data.
E) An index block can contain row data.
Answer:CDE
解析：这道题考的理论概念比较深，A 答案正确是因为有索引组织部（IOT），块头的尺寸
不是固定的，是会增长的

Q38.While one of your databases was in mount state, the datafiles were renamed because they
had been moved to a new file system.The database was then opened.
Which two statements are true?
A) DBA_DATA_FILES displays the original name for the data files.
B) V$DATAFILE displays the new names for the data files.
C) DBA_DATA _FILES displays both the new name and the old name for the data files.
D) DBA_DATA_FILES displays the new name for the data files.

 

E) DBA_DATA_FILES must be resynchronized manually with the control file an order to have it
display the new file names.
Answer:BD
解析：数据文件改名字，数据字典和动态性能视图会自动更新

Q39.You execute this command:

1. Which two statements are true about the BIG_TBS tablespace? (Choose two.)
A)AUTOEXTEND is possible for the datafile
B)It must be bigger than the largest SMALLFILE tablespace
C)Additional data files may not be added
D)It will be a dictionary-managed tablespace by default
E)It will always have a 32K block size
Answer: AC
解析：bigfile 表空间只允许一个数据文件，一般都是自动扩展
Q40.Which statement is true about the INTERSECT operator used in compound queries?
A)Multiple INTERSECT operators are not possible in the same SQL statement
B)It processes NULLs in the selected columns
C)INTERSECT is of lower precedence than UNION or UNION ALL
D)It ignores NULLs
Answer: B
(解析：为了符合新出现的 SQL 标准，Oracle 的未来版本将赋予 INTERSECT 运算符比其他
set 运算符更高的优先级。因此，在使用 INTERSECT 运算符和其他集合运算符的查询中，
应该使用括号来指定求值顺序。INTERSECT 不会忽略空值。)
Q41.Which three statements are true about advanced connection options supported by Oracle
Net for connection to Oracle Database instances? (Choose three.)
A. Connect Time Failover requires the use of Transparent Application Failover (TAF)
B. Source Routing requires the use of a name server
C. Source Routing enables the use of Connection Manager (CMAN) which enables network traffic
to be routed through a firewall
D. Load Balancing can balance the number of connections to dispatchers when using a Shared
Server configuration
E. Load Balancing requires the use of a name server
F. Connect Time Failover requires the connect string to have two or more listener addresses
configured
Answer: CDF
Reference: https://docs.oracle.com/cd/E11882_01/network.112/e41945/advcfg.htm#NETAG013
解析：

 

sales.us.example.com=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=sales1-server)(PORT=1521))
(ADDRESS=(PROTOCOL=tcp)(HOST=sales2-server)(PORT=1521))) (CONNECT_DATA=
(SERVICE_NAME=sales.us.example.com)))
不能使用连接时故障转移或客户端负载平衡设置源路由。源路由按顺序连接到列表中的每个
地址，而连接时间故障转移和客户端负载平衡则从列表中选择一个地址。
连接负载平衡特性通过平衡多个分派器之间的活动连接数来提高连接性能。在 Oracle Real
Application Clusters（Oracle RAC）环境中，连接池负载平衡还可以平衡多个实例之间的活动
连接数。

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
（解析：dual 只是一个为了语法完整设计的虚拟的表，这些函数都可以基于某张表进行执
行。CURRENT_TIMESTAMP 返回的信息是最多的，包含时区信息。）
SQL> select CURRENT_TIMESTAMP from dual;
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
14-JUL-20 02.52.00.783461 PM +08:00
SQL> select sysdate from dual;
SYSDATE
--------------------
14-JUL-2020 15:23:32
SQL> select sysdate from emp;
SYSDATE
--------------------
14-JUL-2020 15:24:31
Q43.You have been tasked to create a table for a banking application. One of the columns must
meet three requirements: Be stored in a format supporting date arithmetic without using
conversion functions Store a loan period of up to 10 years Be used for calculating interest for the
number of days the loan remains unpaid Which data type should you use?
A. INTERVAL YEAR TO MONTH

 

B. INTERVAL DAY TO SECOND
C. TIMESTAMP WITH LOCAL TIMEZONE
D. TIMESTAMP
E. TIMESTAMP WITH TIMEZONE
Answer: B
（解析：这是一个很经典的应用，如果有相关的银行业务，需要计算贷款未付天数的利息，
那么选择 INTERVAL DAY TO SECOND 数据类似是非常合适的，在 10g-19c 版本中都出现过该
题目，有些 ocp 的题库是跟版本没有关系的，是有关应用的内容。）
Q44.In the spfile of a single instance database, LOCAL_LISTENER is set to LISTENER_1. The
TNSNAMES.ORA file in $ORACLE_HOME/network/admin in the database home contains:

Which statement is true?
A. Dynamic service registration cannot be used for this database instance
B. The LREG process registers services dynamically with the LISTENER_1 listener
C. LISTENER_1 must also be defined in the LISTENER.ORA file to enable dynamic service
registration
D. There are two listeners named LISTENER and LISTENER_1 running simultaneously using port
1521 on the same host as the database instances
E. The definition for LISTENER_1 requires a CONNECT_DATA section to enable dynamic service
Registration
Answer: D
（解析：实验发现当前有两个监听启动，而且大家都是使用 1521 端口，不需要在 listener.ora
添加内容。
这道题是第一次出现，我们原来配置的都是会默认端口，没有这种配置方式，感觉没有实际
的意义。）
通过下面命令可以查看到有两个监听，而且可以直接用 lsnrctl start 来一起启动它。 lsnrctl
status
lsnrctl status listener_1
Q45.Which three statements are true concerning logical and physical database structures?
(Choose three.)
A. All tablespaces may have one or more data files
B. The extents of a segment must always reside in the same datafile
C. A smallfile tablespace might be bigger than a bigfile tablespace
D. A segment can span multiple data files in some tablespaces
E. A segment’s blocks can be of different sizes
F. A segment might have only one extent

 

G. Segments can span multiple tablespsaces
Answer: CDF
（解析：这是相同的提问，不同的答案的另外一种考法。Smallfile 表空间不是说表空间尺寸
一定小于 bigfile 表空间。A 答案跟 bigfile 表空间有冲突。）
Q46.Which two statements are true regarding a SAVEPOINT? (Choose two.)
A. Rolling back to a SAVEPOINT can undo a CREATE INDEX statement
B. Rolling back to a SAVEPOINT can undo a TRUNCATE statement
C. Only one SAVEPOINT may be issued in a transaction
D. A SAVEPOINT does not issue a COMMIT
E. Rolling back to a SAVEPOINT can undo a DELETE statement
Answer: DE
（解析：SAVEPOINT 是一个很重要的考点，不涉及到版本问题，以前还出现过不同的考试
方法。）

Q47.Which three functions are performed by dispatchers in a shared server configuration?
(Choose three.)
A. writing inbound request to the common request queue from all shared server connections
B. checking for outbound shared server responses on the common outbound response queue
C. receiving inbound requests from processes using shared server connections
D. sending each connection input request to the appropriate shared server input queue
E. broadcasting shared server session responses back to requesters on all connections
F. sending shared server session responses back to requesters on the appropriate connection
Answer: ADF
解析：A：从所有共享服务器连接将入站请求写入公共请求队列
B: 检查公用出站响应队列上的出站共享服务器响应队列
C：从使用共享服务器连接的进程接收入站请求
D：将每个连接输入请求发送到相应的共享服务器输入队列
E：在所有连接上将共享服务器会话响应广播回请求者
F:将共享服务器会话响应发送回相应连接上的请求者
Q48.Which two statements are true about the SET VERIFY ON command? (Choose two.)
A. It can be used only in SQL*Plus
B. It displays values for variables used only in the WHERE clause of a query
C. It can be used in SQL Developer and SQL*Plus
D. It displays values for variables created by the DEFINE command
E. It displays values for variables prefixed with &&
Answer: CD
（解析：实验证明这个命令在 sql developer 上也可以使用，使用 define 变量定义的值，需
要 define 命令才能够显示。设置 SET VERIFY ON 应该是显示变量替换的过程，同时显示值。
SQL> DEFINE deptname = sales
SQL> DEFINE deptname
DEFINE DEPTNAME = "sales" (CHAR)

 

SQL> SET VERIFY on
SQL> select * from dept where dname ='&deptname'; old 1: select * from dept where dname
='&deptname' new 1: select * from dept where dname ='sales'
Q49.Which three statements are true about a self-join? (Choose three.)
A. The ON clause must be used
B. The query must use two different aliases for the table
C. It must be an equi join
D. It must be an inner join
E. The ON clause can be used
F. It can be an outer join
Answer: BEF
（解析：自连接其实就应该普通的多表连接，但是必须要给同一张表起一个别名，由于
oracle 支持（+）这个语法，所以可以不使用 on 子句，既然是多表连接，就允许外连接、
内连接、等连接，非等连接的方式。）
下面的几个语句都可以执行成功，我们无线了解其意义，单从语法上去分析：外连接；
select e.ename,e2.ename from emp e left join emp e2 on e.empno=e2.empno;
不用 on 子句：
select e.ename,e2.ename from emp e ,emp e2 where e.empno=e2.empno(+);
非等连接：
select e.ename,e2.ename from emp e ,emp e2 where e.empno between e2.empno and
e2.empno;
Q50.You want to write a query that prompts for two column names and the WHERE condition
each time it is executed in a session but only prompts for the table name the first time it is
executed.
The variables used in your query are never undefined in your session.
Which query can be used?
A. SELECT &&col1, &&col2 FROM &table WHERE &&condition = &&cond;
B. SELECT &col1, &col2 FROM &&table WHERE &condition;
C. SELECT &col1, &col2 FROM “&table”WHERE &condition;
D. SELECT ‘&&col1’, ‘&&col2’FROM &table WHERE ‘&&condition’= ‘&cond’;
E. SELECT &&col1, &&col2 FROM &table WHERE &&condition;
Answer: B

（解析：题目的意思是使用一个变量的值第一次输入以后，后面引用的话就不用输入了，那
么可以使用&&符号来使用原来变量的值。）
Q51.Examine the description of the CUSTOMERS table:

 

You want to display details of all customers who reside in cities starting with the letter D followed
by at least two characters. Which query can be used?
A. SELECT * FROM customers WHERE city LIKE ‘D_%’;
B. SELECT * FROM customers WHERE city = ‘%D_’;
C. SELECT * FROM customers WHERE city LIKE ‘D_’;
D. SELECT * FROM customers WHERE city = ‘D_%’;
Answer: A
（解析：此题关键的地方是首字母 D 打头，必须至少两个字符，那么就必须要 D_%，而不
能用 D%，这个可能导致城市的名字只有一个 D 的行也会被查出。该题型容易变题。）
Q52.Examine this command:

Which two statements are true? (Choose two.)
A.DML may be performed on tables with one or more extents in this data file during the
execution of this command.
B.The tablespace containing SALES1.DBF must be altered READ ONLY before executing the
command.
C.The tablespace containing SALES1.DBF must be altered OFFLINE before executing the
command.
D.If Oracle Managed Files (OMF) is used, then the file is renamed but moved to
DB_CREATE_FILE_DEST.
E. The file is renamed and stored in the same location
Answer: AE
（解析：12c 支持在线移动数据文件，所以不需要把数据文件变成只读状态，该命令用到了
reuse 选项，而且路径没有变化，所以只是改了名字。）
Q53.Which three statements are true about dropping and unused columns in an Oracle database?
(Choose three.)
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
Answer: ADF
解析：被设置为 unused 列应该还算在 1000 个列的限制里面。
Q54.Examine the following command:
CREATE TABLE PRODUCTS

(

prod_id number(4),
Prod_name varchar2 (20),
Category_id number(30),
Quantity_on_hand number (3) INVISIBLE
);
Which three statements are true about using an invisible column in the PRODUCTS table?
A. The %ROWTYPE attribute declarations in PL/SQL to access a row will not display the invisible
column in the output.
B. The DESCRIBE commands in SQL *Plus will not display the invisible column in the output.
C. Referential integrity constraint cannot be set on the invisible column.
D. The invisible column cannot be made visible and can only be marked as unused.
E. A primary key constraint can be added on the invisible column.
Answer: ABE
解析：一般来说,看不见列帮助迁移和发展在线应用 程序。一个用例可能是一个应用程序, 该
应用程序可以查询一个三列的表 SELECT *声明。第四列添加到表会破坏应用程序,预计三列
的数据。添加第四个无形的列使应 用程序正常运转。开发人员可以改变应用程序来处理第
四列,并使列可见当应用程序上线。
Q55.Evaluate the following ALTER TABLE statement:
ALTER TABLE orders SET UNUSED (order_date);
Which statement is true?
A. After executing the ALTER TABLE command, you can add a new column called ORDER_DATE to
the ORDERS table.
B. The ORDER_DATE column should be empty for the ALTER TABLE command to execute
succsessfully.
C. ROLLBACK can be used to get back the ORDER_DATE column in the ORDERS table.
D. The DESCRIBE command would still display the ORDER_DATE column.
Answer: A
解析：经过实验，可以立刻添加一个列，名字可以是被 unused 设置的列。
Q56.choose the best answer Evaluate the following SQL statement:
ALTER TABLE hr.emp SET UNUSED (mgr_id);
Which statement is true regarding the effect of the above SQL statement?

 

A)Any views created on the EMP table that include the MGR_ID column would be automatically
modified and remain valid.
B)Any constraints defined on the MGR_ID column would be removed by the above command.
C)Any synonym existing on the EMP table would have to be re-created.
D)Any index created on the MGR in column would continue to exist until the DROP UNUSED
COLUMNS command is executed.
Answer:B
注意：列一旦被设置为 unused 以后，基于该列的约束、索引都被删除，同时相关的视图
被无法访问，但是列的数据需要 drop column 时才会被删除
Q57.choose two:
Examine the structure of the DEPARTMENTS table

You execute the following command:
SQL> ALTER TABLE departments SET UNUSED (country);
Which two statements are true?
A)A new column, COUNTRY, can be added to the DEPARTMENTS table after executing the
command.
B)Indexes created on the COUNTRY column exist until the DROP UNUSED COLUMNS command is
executed.
C)Unique key constraints defined on the COUNTRY column are removed.
D) Synonyms existing on the DEPARTMENTS table would have to be re-created.
E)Views created on the DEPARTMENTS table that include the COUNTRY column are automatically
modified and remain valid.
Answer:AC
解析：列一旦被设置为 unused 以后，基于该列的约束、索引都被删除，同时相关的视图被
无法访问。经过实验，可以马上往该表添加名字一样的列。）
Choose three
Examine this description of the PRODUCTS table:
Name NULL? TYPE
------------------------------------------------------------------------
PROD_ID NOT NULL VARCHAR2(6)
QUANTITY NUMBER(8,2)
PRICE NUMBER(10.2)
EXPIRY_DATE DATE
Rows exist in this table with data in all the columns. You put the PRODUCTS table in read-only
mode.

 

Q58.Choose three Examine this description of the PRODUCTS table:
Name NULL? TYPE
-------------------- ------------------------ ----------------------------
PROD_ID NOT NULL VARCHAR2(6)
QUANTITY NUMBER(8,2)
PRICE NUMBER(10.2)
EXPIRY_DATE DATE
Rows exist in this table with data in all the columns.
You put the PRODUCTS table in read-only mode. Which three commands execute successfully on
PRODUCTS?
A) ALTER TAELE products DROP COLUMN expiry_date;
B) CREATE INDEX price_idx on products (price);
C) ALTER TABLE products SET UNUSED(expiry_date);
D) TRUNCATE TABLE products;
E) ALTER TABLE products DROP UNUSED COLUMNS
F) DROP TABLE products
Answer:BEF
解析：这道题有点深度，只读表如何能够还能够被操作，就看所做的操作有没有修改表数据
块的内容，如果有，则不能操作。 创建索引显然是可以的，只涉及到读。 drop 表很多人
不理解，因为 drop 表只修改数据字典，不会对表的数据进行操作。 set unused 发现表在
只读的时候是不行的，有点意外。 而 drop unused column 却是可以，更加意外。
Q59.Choose three Which three are true about dropping columns from a table?
A) A column can be removed only if it contains no data.
B) A column drop is implidtly committed.
C) A column that is referenced by another column in any other table cannot be dropped.
D) A column must be set as unused before it is dropped from a table.
E) A primary key column cannot be dropped.
F) Multiple columns can be dropped simultaneously using the ALTER TABLE command.
Answer:BCF
解析：该题的答案涉及到 unused 相关的内容，其实跟 unused 涉及不深。
Q60.Which two statements are true regarding Oracle database space management within blocks
managed by Automatic Segment Space Management (ASSM)? (Choose two.)
A. PCTFREE defaults to 10% for all blocks in all segments for all compression methods
B. ASSM assigns blocks to one of four fullness categories based on what percentage of the block
is allocated for rows
C. Update operations always attempt to find blocks with free space appropriate to the length of
the row being updated
D. Insert operations always attempt to find blocks with free space appropriate to the length of
the row being inserted

 

E. A block will always be eligible for inserts if the row is short enough to fit into the block
Answer: AD
解析：答案 A 意思是对于所有压缩方法的所有段中的所有块，PCTFREE 默认为 10%，ASSM
表空间中的表的 pctfree 默认为 10%，经过实验发现压缩的表的 pctfree 也是一样，针对
所有压缩方法的所有段的所有块；C 答案描述了 Oracle 如何更新块中的行，我们通过 bbed
工具分析发现，如果修改的列的值长度与原来的一样或者小，那么就在原列的位置上修改，
如果长远原来的列，则在本块中的 free 空间寻找可用的空间存放新列的值，如果 free 空间
不够，则会出现行迁移现象。E 答案错误的地方是当块中的 free 空间低于设置的值，则不
会 insert 新行。
Q61.The INVOICE table has a QTY_SOLD column of data type NUMBER and an INVOICE_DATE
column of data type DATE. NLS_DATE_FORMAT is set to DD-MON-RR.
Which two are true about data type conversions involving these columns in query expressions?
(Choose two.)
A)CONCAT (qty_sold, invoice_date) : requires explicit conversion
B)invoice_date = ‘15-march-2019’ : uses implicit conversion
C)invoie_date > ‘01-02-2019’: uses implicit conversion
D)qty_sold BETWEEN ‘101’ AND ’110’ : uses implicit conversion
E)qty_sold = ‘0554982’ uses implicit conversion
Answer: BD
解析：实验证明 A 答案不需要格式转换就可以；C 答案需要显式转换；E 答案应该也是隐
式转换

Q62.Which three are types of segments in an Oracle Database? (Choose three.)
A)undo
B)index
C)stored procedures
D)sequences
E)tables
F)clusters
Answer: BEF
解析：答案 BEF 都是不同段的类型，其实 undo 也是属于段，我们这道题不选择它，是因
为它跟其它的段类型不属于同一类

Q63.Which two statements are true about the results of using the INTERSECT operator in
compound queries? (Choose two.)
A)Column names in each SELECT in the compound query can be different
B)The number of columns in each SELECT in the compound query can be different
C)Reversing the order of the intersected tables can sometimes affect the output
D)INTERSECT returns rows common to both sides of the compound query
E)INTERSECT ignores NULLs
Answer: AD
解析：复合查询的列名可以不一样，但是个数和数据类型必须一样；INTERSECT 获得是交集，

 

所以 INTERSECT 返回复合查询两边共有的行，答案 D 正确。
Q64.Which statement is true regarding the INTERSECT operator?
A)The names of columns in all SELECT statements must be identical.
B)It ignores NULL values.
C)Reversing the order of the intersected tables alters the result.
D)The number of columns and data types must be identical for all SELECT statements in the query.
（查询中所有 SELECT 语句的列数和数据类型必须相同）
Answer: D
解析：和 UNION 指令类似，INTERSECT 也是对两个 SQL 语句所产生的结果做处理的。不
同的地方是， UNION 基本上是一个 OR (如果这个值存在于第一句或是第二句，它就会被
选出)，而 INTERSECT 则比较像 AND ( 这个值要存在于第一句和第二句才会被选出)。
UNION 是联合集，而 INTERSECT 是交集。
Q65.Which two statements are true about single row functions? (Choose two.)
A)MOD: returns the quotient of a division operation
B)FLOOR: returns the smallest integer greater than or equal to a specified number
C)TRUNC: can be used to combine any number of values
D)CONCAT: can be used to combine any number of values
E)CEIL: can be used for positive and negative numbers
Answer: CE
解析：题目考的还是单行函数，但是答案不一样了。floor 是向下取整，所以描述的是返
回小于或等于指定数字的最大整数。如果是 ceil 返回的就是大于或等于指定数字的最小整
数。所以答案 B 如果是描述 ceil 函数就是对的。
A、 MOD：返回除法运算的商
B、 FLOOR：返回小于或等于指定数字的最小整数
C、 TRUNC：可用于任意数量组合的值
D、 CONCAT:可用于组合任意数量的值
E、 CEIL：可用于正数和负数
FLOOR 是向下取整，正负整数都支持 floor(9.999999) = 9.0 CEIL 是向上取整 正负整数都支
持 ceil(9.999999) = 10 Which three
Q66.Which three statements are true about the Oracle Data Dictionary? (Choose three.)
A)Data dictionary views are created by joins of dictionary base tables and DBA defined tables
B)The data dictionary is created and maintained by the database administrator
C)Views with the same name but different prefixes, such as CDB, DBA, ALL and USER, reference
the same base tables from the data dictionary
D)Base tables can be queried directly
E)It is owned by the SYSTEM user
F)Usernames of all users including database administrators are stored in the data dictionary
Answer: CDF
解析：该题考的是数据字典概念，属于通用类，跟版本无关。

 

Q67.In one of your databases, the user HR has the password HRMGR.
You want to connect to a database instance whose listener listens on port 1531 by using this
statement:
CONNECT HR/HRMGR@orcl No name server is used.
Which statement is true about ORCL?
A)It must be the value of the SERVICE_NAMES parameter on the client side
B)It must resolve to a valid connect descriptor in the server’s tnsnames.ora file
C)It must resolve to a valid connect descriptor in the client’s tnsnames.ora file
D)It must be the name of the database to whose instance HR wishes to connect
E)It must be the name of the server running the database to whose instance HR wishes to
connect
Answer: C
解析：使用连接别名，那么这个别名必须是在客户端定义的，指定了连接的数据库信息。

Q68.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
Answer: BE
解析：答案 E 很有深度，前面半句话容易理解，后面半句话意义很深，数据字典必须要在
数据库打开的情况下才能够查询

Q69.Examine the description of the PROMOTIONS table:

You want to display the unique promotion costs in each promotion category.
Which two queries can be used? (Choose two.)
A)SELECT promo_cost, promo_category FROM promotions ORDER BY 1;
B)SELECT DISTINCT promo_cost || ’ in ‘ || DISTINCT promo_category FROM promotions
ORDER BY 1;
C)SELECT DISTINCT promo_category || ‘ has ‘ || promo_cost AS COSTS FROM promotions
ORDER BY 1;
D)SELECT promo_category, DISTINCT promo_cost FROM promotions ORDER BY 2;
E)SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;
Answer: CE
解析：CE 两个都能够查询出正确的结果，其它的错误在语法上，distinct 只能在 select 子

 

句第一个位置使用，不能有两个 distinct 语句。
Q70.Examine the description of the PROMOTIONS table:

Management requires a report of unique promotion costs in each promotion category.
Which query would satisfy this requirement?
A)SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1
B)SELECT promo_category, DISTINCT promo_cost FROM promotions
C)SELECT DISTINCT promo_cost, promo_category FROM promotions
D)SELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;
Answer: A
解析：AC 两个都能够查询出正确的结果，但是题目要的是最佳答案，基于题目的要求， A
答案可以提供更加具有可读性的结果

Q71.In the ORCL database, UNDOTBS1 is the active undo tablespace with these properties:
1. A size of 100 MB
2. AUTOEXTEND is off
3. UNDO_RETENTION is set to 15 minutes
4. It has RETENTION GUARANTEE UNDOTBS1 fills with uncommitted undo 10 minutes after the
database opens.
What will happen when the next update is attempted by any transaction?
A)It succeeds and the generated undo is stored in SYSTEM.
B)It fails and returns the error message “ORA-30036: unable to extend segment by 8 in undo
tablespace ‘UNDOTBS1’ “.
C)It succeeds and the least recently written undo block of UNDOTBS1 is overwritten by the
generated undo.
D)It succeeds and the generated undo is stored in SYSAUX.
E)It succeeds and the least recently read undo block of UNDOTBS1 is overwritten by the
generated undo.
Answer: B
解析：根据题目的意思，提交的 undo 数据保留 15 分钟，然后数据库刚打开 10 分钟，
而且现在 undo 表空间都满了，数据文件不是自动扩展，所以报错
Q72.Which two statements are true about the rules of precedence for operators? (Choose two.)
A)The concatenation operator || is always evaluated before addition and subtraction in an
expression
B)NULLS influence the precedence of operators in an expression
C)The + binary operator has the highest precedence in an expression in a SQL statement
D)Arithmetic operators with equal precedence are evaluated from left to right within an

 

expression
E)Multiple parentheses can be used to override the default precedence of operators in an
expression
Answer: DE
解析：本题的难点在于英文的理解
A、 连接运算符| |始终在表达式中的加减运算之前求值
B、 空值影响表达式中运算符的优先级
C、 在 SQL 语句的表达式中，+二进制运算符的优先级最高
D、 具有相等优先级的算术运算符在表达式中从左到右求值写表达式中运算符的默认优先
级

Q73.In one of your databases, user KING is:
1. Not a DBA user
2. An operating system (OS) user
Examine this command and its output:

What must you do so that KING is authenticated by the OS when connecting to the database
instance?
A)Set OS_AUTHEN_PREFIX to OPS$
B)Have the OS administrator add KING to the OSDBA group
C)Grant DBA to KING
D)Unset REMOTE_LOGIN_PASSWORD FILE
E)Alter user KING to be IDENTIFIED EXTERNALLY
Answer: E
解析：
Creating a New User: Operating System Authentication Operating system authentication
1、使用 IDENTIFIED EXTERNALLY 选项创建用户：.
CREATE USER postgres
IDENTIFIED EXTERNALLY
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE temp QUOTA
15m ON data;
Grant create session to postgres;
2、OS_AUTHENT_PREFIX 默认的值是 OPS$ ，如果不需要前缀，则使用如下命令： alter
system set os_authent_prefix='' scope=spfile; 重启实例
3 、3 、以 postgre 用户登录到系统，设置环境变量后就可以登录： export
ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1/ export
PATH=$ORACLE_HOME/bin:$PATH export
ORACLE_SID=PROD1
$ sqlplus /

 

）

Q74.View the Exhibits and examine the structure of the COSTS and PROMOTIONS tables.
You want to display PROD_IDS whose promotion cost is less than the highest cost PROD_ID in a
promotion time interval.
Examine this SQL statement:

What will be the result?
A)It gives an error because the ALL keyword is not valid
B)It gives an error because the GROUP BY clause is not valid
C)It executes successfully but does not give the required result
D)It executes successfully and gives the required result
Answer: D

 

解析：查看查询语句，先查出间隔时期内的最大 promotion cost，然后条件中用到<all，查
出满足条件的 promo_id，最后进行匹配。
Q75.Which two statements are true about trace files produced by the Oracle Database server?
(Choose two.)
A)They can be written by server processes
B)Trace files are written to the Fast Recovery Area (FRA)
C)They can be written by background processes
D)All trace files contain error information that require contacting Oracle Support
E)Trace file names are based on the database name concatenated with a sequential number
Answer: AC
解析：后台进程和 server 进程都会产生跟踪文件，但是产生的文件作用不一样，server 产
生的是用户相关的跟踪文件。
跟踪文件的命名习惯除了以数据库名字做为名字的组成部分以为，还会以不同后台进程产生
的进程名做为名字的组成部分。这些跟踪文件中的错误大部分是提供给 dba 做参考的。
比如：
PROD1_mmon_26871.trc
PROD1_mmon_27948.trc
PROD1_dbw0_28855.trc）
Q76.You need to calculate the number of days from 1st January 2019 until today. Dates are stored
in the default format of DD-MON-RR.
Which two queries give the required output? (Choose two.)
A)SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') -'01-JAN-2019' FROM DUAL;
B)SELECT ROUND(SYSDATE – '01-JAN-2019') FROM DUAL;
C)SELECT ROUND(SYSDATE - TO_DATE('01/JANUARY/2019')) FROM DUAL;
D)SELECT TO_DATE(SYSDATE, 'DD/MONTH/YYYY') – '01/JANUARY/2019' FROM DUAL;
E)SELECT SYSDATE - TO_DATE('01-JANUARY-2019') FROM DUAL;
Answer: CE
解析：这道题需要注意的地方是默认的日期格式与答案里面的不一样，所以需要转换函数进
行转换，否则都会失败。

Q77.Which two statements are true about the DUAL table? (Choose two.)
A)It can be accessed only by the SYS user
B)It consists of a single row and single column of VARCHAR2 data type
C)It can display multiple rows but only a single column
D)It can be used to display only constants or pseudo columns
E)It can be accessed by any user who has the SELECT privilege in any schema
F)It can display multiple rows and columns
Answer: EF
（解析：dual 只是为了满足语法上的需要而设计的一个虚拟表。） SQL> select
user,sysdate,sum(900) from dual;
USER SYSDATE SUM(900) ------------------------------ ---------

 

---------- SCOTT 25-JUL-20 900
A、 它只能由 SYS 用户访问
B、 它由 VARCHAR2 数据类型的单行和单列组成
C、 它可以显示多行，但只能显示一列
D、 它可用于仅显示常量或伪列
E、 在任何模式中具有 SELECT 特权的任何用户都可以访问它
F、 它可以显示多行和多列
select * from dual connect by 0 + level <= 10;
DU
--
Q78.Which four statements are true regarding primary and foreign key constraints and the effect
they can have on table data? (Choose four.)
A)A table can have only one primary key but multiple foreign keys
B)A table can have only one primary key and foreign key
C)The foreign key columns and parent table primary key columns must have the same names
D)It is possible for child rows that have a foreign key to remain in the child table at the time the
parent row is deleted
E)It is possible for child rows that have a foreign key to be deleted automatically from the child
table at the time the parent row is deleted
F)Only the primary key can be defined at the column and table level
G)Primary key and foreign key constraints can be defined at both the column and table level
Answer: ADEG
解析：关于主键和外键约束以及它们对表数据的影响，哪四个语句是正确的（解析：该
题考的是主外键约束，概念不难，D 答案可能有点不理解，其实可以 通过 disable 外键约
束来实现，因为题目的意思是有可能会出现这种情况。

Q79.Examine the description of the EMPLOYEES table:

Which query is valid?
A)SELECT dept_id, AVG(MAX(salary)) FROM employees GROUP BY dept_id;
B)SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id;
C)SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id, join_date;
D)SELECT dept_id, MAX(AVG(salary)) FROM employees GROUP BY dept_id;
Answer: C
解析：本道题考的是分组函数的语法规则，没有出现在分组函数中的列必须要出现在 group
by 子句中，但是 AD 答案不能执行，因为这里用到了嵌套函数。

 

Q80.What is true about non-equijoin statement performance? (Choose two.)
A)Table aliases can improve performance
B)The BETWEEN condition always performs better than using the >= and <= conditions
C)The join syntax used makes no difference to performance
D)The BETWEEN condition always performs less well than using the >= and <= conditions
E)The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax
Answer: AE
解析：多表连接使用别名可以提高查询性能，使用 oracle 的 join 语法更适合 oracle 的运
行环境

Q81.In your data center, Oracle Managed Files (OMF) is used for all databases. All tablespaces are
smallfile tablespaces.
SALES_Q1 is a permanent user-defined tablespace in the SALES database.
Examine this command which is about to be issued by a DBA logged in to the SALES database:
ALTER TABLESPACE sales_q1 ADD DATAFILE;
Which are two actions, either one of which you could take to ensure that the command executes
successfully? (Choose two.)
A)Ensure that DB_RECOVERY_FILE_DEST and DB_CREATE_FILE_DEST each specify with at least 50
Mb of available space.
B)Specify a path in the DATAFILE clause of the command specifying a location with at least 100M
of available space.
C)Ensure that DB_CREATE_FILE_DEST specifies a location with at least 100 Mb of available space.
D)Add the AUTOEXTEND ON clause with NEXT set to 100M.
E)Ensure that DB_RECOVERY_FILE_DEST and DB_CREATE_FILE_DEST each specify locations with
at least 50 Mb of available space.
Answer: CD
解析：经过实验，OMF 管理的数据文件，它的名字由 oracle 自动起名；而且默认是 100M，
自动扩展

Q82.Choose two. Examine this parameter:
NAME TYPE VALUE
-------------------------- ------------ ---------------------------------------------
db_create_file_dest string /u01/app/oracle/oradata/prod/
Now examine this statement :
SQL> CREATE TABLESPACE data_olap;
Tablespace is created.
Which two are true about this tablespace and its attributes?
A)The datafile is created with a name generated by the Oracle Instance.
B)The datafile size is 100M.
C)The tablespace is created with manual segment space management.

 

D)The datafile that is created is not auto extensible.
E)The tablespace is a dictionary managed tablespace.
Answer:AB
解析：OMF 管理的数据文件，它的名字由 oracle 自动起名；而且默认是 100M，自动扩展。
Q83.Which two statements are true about Oracle Managed Files (OMF)?
A)OMF cannot be used in a database that already has data files created with userspecified
directions.
B)The file system directions that are specified by OMF parameters are created automatically.
C)OMF can be used with ASM disk groups, as well as with raw devices, for better file
management.
D)OMF automatically creates unique file names for tablespaces and control files.
E)OMF may affect the location of the redo log files and archived log files.
Answer: CD
解析：OMF 管理的数据文件，它的名字由 oracle 自动起名；而且默认是 100M，自动扩展。
Q84.Examine this command and some partial output:

Why does the DB01.abc.com service show unknown status?
A)The service DB01.abc.com is dynamically registered
B)The LOCAL_LISTENER database parameter is not set to a service name that refers to
LISTENER_1
C)The service DB01.abc.com is statically registered
D)The listener is not listening on the default port 1521
E)The SID_LIST_LISTENER section is not contained in the LISTENER.ORA file
Answer: C
解析：如果看到实例的状态是 unknow，则表明该监听是静态注册的静态。必备常识
Q85.Which three statements are true about the tools used to configure Oracle Net Services?
(Choose three.)
A)The Oracle Net Configuration Assistant is only used when running the Oracle installer
B)Oracle Net Manager can be used to centrally configure listeners on any database server target
C)The lsnrctl utility requires a listener.ora file to exist before it is started
D)Oracle Net Manager can be used to locally configure naming methods on a database server
E)Enterprise Manager Cloud Control can be used to centrally configure listeners on any managed
database server
F)Enterprise Manager Cloud Control can be used to centrally configure net service names for any
database server target

 

Answer: DEF
解析：Oracle Net Manager 可以用来配置监听和 tns，但是不提供集中管理功能，同时 emcc
也可以用来配置监听

Q86.Which two statements are true regarding the UNION and UNION ALL operators? (Choose
two.)
A)Duplicates are eliminated automatically by the UNION ALL operator
B)The number of columns selected in each SELECT statement must be identical
C)The names of columns selected in each SELECT statement must be identical
D)The output is sorted by the UNION ALL operator
E)NULLS are not ignored during duplicate checking
Answer: BE
解析：空值不会忽略
SQL> select comm from emp
2 union
3 select comm from emp;
COMM
----------
0
300
500
1400
SQL> select comm from emp;
列名不需要一样，但是数据类型必须一样：SQL> select ename from emp
2 union
3 select job from emp;
ENAME
----------
ADAMS
ALLEN
ANALYST
SQL> select ename from emp
2 union
3 select sal from emp; select
ename from emp
*
ERROR at line 1:
变种题和 86 题相似：Which three statements are true regarding the UNION and UNION ALL
operators?
A) Duplicates are eliminated automatically by the UNION ALL operator.
B) The names of columns selected in each SELECT statement can be identical.
C) NULLS are not ignored during duplicate checking.
D) The number of columns selected in each SELECT statement must be identical.

 

E) Duplicates can optionally be eliminated by the UNION operator.
F) The names of columns selected in each SELECT statement must be identical.
G) The number of columns selected by the first SELECT statement can be greater than the
numberselected in subsequent SELECT statements
Anser：BCD
Q87.Which two statements are true about the Automatic Diagnostic Repository (ADR)? (Choose
two.)
A)The ADR base defaults to $ORACLE_HOME/rdbms/admin if neither DIAGNOSTIC_DEST or
ORACLE_BASE is set
B)The ADR base defaults to $ORACLE_HOME/dbs if the DIAGNOSTIC_DEST parameter and the
ORACLE_BASE environment variable are not set
C)It supports diagnostics for Automatic Storage Management (ASM)
D)It supports diagnostics for Oracle Clusterware
E)It is held inside an Oracle database schema
Answer: CD
解析： 从 Oracle Database 11gR1 开始，数据库、自动存储管理(ASM)、集群就绪服务(CRS)
和其它 Oracle 产品或组件将所有诊断数据都存储在 ADR 中。每种产品的每个实例都将诊
断数据存储在自己的 ADR 主目录下。例如，在具有共享存储和 ASM 的 Real Application
Clusters 环境中，每个数据库实例和每个 ASM 实例在 ADR 中都有一个主目录。 ADR 的
统一目录结构对各种产品和实例使用一致的诊断数据格式，而且还有一组统一的工具，使客
户和 Oracle 技术支持可以相互关联并分析多个实例的诊断数据。
从 Oracle Database 11gR1 开始，将忽略传统的…_DUMP_DEST 初始化参数。ADR 根目 录
又称为 ADR 基目录，其位置由 DIAGNOSTIC_DEST 初始化参数设定。如果省略此参数或将
其保留为空，数据库将在启动时按如下方式设置 DIAGNOSTIC_DEST：如果已设置了环境变
量 ORACLE_BASE，则将 DIAGNOSTIC_DEST 设置为$ORACLE_BASE。如果未设置环境变量
ORACLE_BASE，则将 DIAGNOSTIC_DEST 设置为$ORACLE_HOME/log。
Q88.Examine this command:
SQL> ALTER SYSTEM SET ENABLE_DDL_LOGGING=TRUE;
Which two statements are true?
A)All DDL statements are logged in to a text file in Automatic Diagnostic Repository (ADR) home.
B)The Alert Log still contains some DDL statements.
C)Some data definition language (DDL) statements are written to the control file.
D)All data definition language (DDL) statements are written to the control file.
E)Some DDL statements are written to a text file in the ADR home.
F)Some DDL statements are written to an XML file in the ADR home.
Answer:AF
解析：打开 DDL 跟踪后，要通过 adrci 命令的 show log 才能够查看 DDL 日志，sys 用 户
的 DDL 语句也会被纪录。
Q89.Examine the following command;
ALTER SYSTEM SET enable_ddl_logging = TRUE;

 

Which statement is true?
A)Only the data definition language (DDL) commands that resulted in errors are logged in the
alert log file.
B)All DDL commands are logged in the alert log file.
C)All DDL commands are logged in a different log file that contains DDL statements and their
execution dates.
D)Only DDL commands that resulted in the creation of new segments are logged.
E)All DDL commands are logged in XML format in the alert directory under the Automatic
Diagnostic Repository (ADR) home.
Answer: C
解析:打开 DDL 跟踪后，要通过 adrci 命令的 show log 才能够查看 DDL 日志目录，sys 用
户的 DDL 语句也会被纪录。
adrci> show log
ADR Home = /u01/app/oracle/diag/rdbms/orcl/ORCL，在该目录下有个 log 目录，里面有
ddl_ORCL.log 文本跟踪日志，同时在 log 目录下的 ddl 目录中有 log.xml 日 志文件。所以
C 答案正确，存放在不同的日志文件里面，意味着文本和 xml 文件中。
Q90.You want to apply the principle of Least Privilege in all your live databases.
One of your requirements is to revoke unnecessary privileges from all users who have them using
Privilege Analysis.
Which three types of analyses can be done using the DBMS_PRIVILEGE_CAPTURE package?
(Choose three.)
A)analysis of all privileges used by all users including administrative users in the database
B)analysis of all privileges used by all users but excluding administrative users in the database
C)analysis of privileges that a user has on their own schema objects that they did not use
D)analysis of privileges that a user has on their own schema objects that they did use
E)analysis of privileges granted directly to a role that are then used by a user who has been
granted that role
F)analysis of privileges granted indirectly to a role that are then used by a user who has been
granted that role
Answer: BEF
解析：经过不断验证和筛选，BEF 答案比较正确，该工具只分析系统权限，不分析对象权限。
Oracle 12c 提供了一个有力的工具 DBMS_PRIVILEGE_CAPTURE，可以通过建立分析策略，对
分配给用户的权限跟踪、分析、生成使用报告，从而对用户在应用中所有使用的权限和未使
用的权限有一个清晰的掌控。
实验证明，管理员用户的权限使用不分析，分析的是哪些用户曾经用过的权限和通过角色给
与的权限。

Q91.Which three statements are true about undo segments and the use of undo by transactions
in an Oracle database instance? (Choose three.)
A)An undo segment may be used by multiple transactions simultaneously
B)Undo segments can wrap around to the first extent when a transaction fills the last extend of

 

the undo segment
C)Undo segments have a minimum of three extents
D)Undo segments can extend when a transaction fills the last extent of the undo segment
E)A single transaction may use multiple undo segments simultaneously
F)Undo segments must be stored in a BIGFILE tablespace
G)Undo segments must be stored in a SMALLFILE tablespace
Answer: ACD
解析：这道题比较有深度，是对 undo 使用的一个全面的描述；答案 B 中描述的如果是 undo
段中间的区如果写满了，会发生 wrap around（缠绕）的现象，但是最后一个区是不会的，
只会造成区扩展；答案 C 经过测试，发现初始化时分配 2 个区，但是其它的答案明显不对；
答案有点意外的是 G，通过实验发现能够创建 bigfile 的 undo 表空间。
Q92.choose one Examine the parameters for a database instance: NAME TYPE VALUE
temp_undo_enabled boolean TRUE undo_ management string AUTO undo _retention Integer
900 undo _tablespace string UNDOTBS1 Your database has three undo tablespaces and the
default undo tablespace is not autoextensible.
Resumable space allocation is not enabled for any sessions in the database instance.
What is the effect on new transactions when all undo space in the default undo tablespace is in
use by active transactions?
A)Transactions wait until space becomes available in UNDOTBS1.
B)Transactions write their undo in a temporary tablespace.
C)Transactions fail.
D)Transactions write their undo in the SYSTEM undo segment.
Answer:C
解析：该题故意弄了几个参数的值，特别是 temp_undo_enabled，然后告诉我们 Resumable
space 功能没有打开，以及默认 undo 表空间都用完了，那么新的事务就会失败。
Q93.One of your databases supports an OLTP workload. The default undo tablespace is fixed size
with:
1. RETENTION NOGUARANTEE
2. undo_retention is 12 minutes User scott get this error after a query on the sales table has run
for more than 15 minutes: ORA- 01555: snapshot too old .
Which three factors taken separately or in some combination might be the cause?
A)A committed delete to the sales table was made more than 12 minutes before the query
began.
B)An uncommitted delete to the sales table was made more than 12 minutes before the query
began.
C)A committed update to the sales table was made more than 12 minutes before the query
began.
D)An uncommitted update to the sales table was made more than 12 minutes before the query
began.
E)An update was made to the sales table before the query began.
F)An update to the SALES table was committed after the query began.

 

G)An update was made to the sales table after the query began.
Answer:EFG
解析：题目的意思是：哪三个因素单独或结合在一起可能是原因？快照太老是因为一个长时
间运行的查询语句，所要查询的对 象在 undo 段找不到而产生的，找不到的原因是事务已
经提交了，原来使用的 undo 段被覆盖。
Q94.Which two statements are true about Enterprise Manager Database Express? (Choose two.)
A)It is available only when the database is open
B)It can be used to perform database recovery
C)The same port number can be used for Database Express configurations for databases on
different hosts
D)It can be used to switch a database into ARCHIVELOGMODE
E)The same port number can be used for multiple Database Express configurations for multiple
databases on the same host
Answer: AC
解析：Express 的功能消减了很多，因为要执行一个存储过程才能够启动它，所以需要在数
据库 open 的情况下运行，不同主机上的 Express 端口可以是一样的，但是一台主机上不
同数据库的 Express 端口就不能一样了
Q95.Table EMPLOYEES contains columns including EMPLOYEE_ID, JOB_ID and SALARY. Only the
EMPLOYEE_ID column is indexed. Rows exist for employees 100 and 200. Examine this statement:

Which two statements are true? (Choose two.)
A)Employee 100 will have SALARY set to the same value as the SALARY of employee 200
B)Employee 200 will have JOB_ID set to the same value as the JOB_ID of employee 100
C)Employee 200 will have SALARY set to the same value as the SALARY of employee 100
D)Employee 100 will have JOB_ID set to the same value as the JOB_ID of employee 200
E)Employees 100 and 200 will have the same JOB_ID as before the update command
F)Employees 100 and 200 will have the same SALARY as before the update command
Answer: AD
解析：因为是以 id=100 为条件，所以描述的时候应该说 100 的客户将跟 200 的客户有相
同的 salary 和 job_id。
Q96.Which two are true about a SQL statement using SET operators such as UNION? (Choose
two.)
A)The data type group of each column returned by the second query must match the data type
group of the corresponding column returned by the first query.
B)The number, but not names, of columns must be identical for all SELECT statements in the

 

query.
C)The data type of each column returned by the second query must exactly match the data type
of the corresponding column returned by the first query.
D)The names and number of columns must be identical for all SELECT statements in the query.
E)The data type of each column returned by the second query must be implicitly convertible to
the data type of the corresponding column returned by the first query.
Answer: AB
解析：union 操作中所有查询的列数必须一样，数据类型必须一致，名字可以不一样
Q97.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
解析：注意有些时间类型的函数受到会话的时间区域设置影响，比如答案 B，通过测试，答
案 E 正确。）很多同仁在工作时，不会特别关注时区问题，即使用默认的时间配置，而在
处理实际数据的时候才发现，操作时间或者系统显示时间与本地时间相差了几个小时，
在我大中国，一般就是相差 8 个小时。只要你遇到了反馈时间与实际时间相差 8 个小时的
情况，你就往时区 方面去考虑,这个方向基本上是正确的。
Q98.choose three Which three statements are true about time zones,date data types,and time
stamp data types in an oracle database?
A)The DBTIMEZONE function can return an offset from Universal Coordinated Time(UTC).
B)A TIMESTAMP data type column contains information about year,month,and day.
C)A TIMESTAMP WITH LOCAL TIMEZONE data type column is stored in the database using the
time zone of the session that inserted the row.
D)The SESSIONTIMEZONE function can return an offset from Universal Coordinated Time(UTC).
E)The CURRENT_TIMESTAMP function returns data without time zone information.
Answer:BCD
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

 

解析：
DBTIMEZONE：是数据库服务器所在的时区。
SESSIONTIMEZONE ：是你的会话的时区。 例如：数据库服务器是放在英国(+00:00 时
区)，而你在中国(+08:00)访问数据库，则 SESSIONTIMEZONE 就是+08:00,DBTIMEZONE 就是
+00:00
db 是数据库的，session 是针对当前会话的，因为时区在会话级可以改变 以下是测试结果：
SQL> alter session set time_zone='+00:00'
SQL> select SYSTIMESTAMP from dual;
SYSTIMESTAMP
----------------------------------------
27-JUL-20 12.53.54.730359 AM -04:00
SQL> select CURRENT_TIMESTAMP from dual;
CURRENT_TIMESTAMP
------------------------------------------------------------------------------
27-JUL-20 04.54.15.696145 AM +00:00
SQL> select CURRENT_DATE from dual;
CURRENT_D
-----------------------
27-JUL-20

Q100.Which three statements are true about inner and outer joins? (Choose three.)
A)A full outer join returns matched and unmatched rows
B)An inner join returns matched rows
C)Outer joins can only be used between two tables per query
D)A full outer join must use Oracle syntax
E) Outer joins can be used when there are multiple join conditions on two tables
F)A left or right outer join returns only unmatched rows
Answer: ABE
解析：full outer join 只能用 ANSI 的语法，ORACLE 语法不支持，同时返回匹配的和不匹 配
的行；外部连接可以在多张表上执行，所以 B 答案错；同时外部连接可以有多个限制条件。
Q101.Examine this description of the TRANSACTIONS table:

Which two SQL statements execute successfully? (Choose two.)
A)SELECT customer_id AS “CUSTOMER-ID”, transaction_date AS DATE, amount + 100 “DUES”
FROM transactions;
B)SELECT customer_id AS “CUSTOMER-ID”, transaction_date AS “DATE”, amount +100 DUES

 

FROM transactions;
C)SELECT customer_id AS CUSTOMER-ID, transaction_date AS TRANS_DATE, amount +100“DUES
AMOUNT” FROM transactions;
D)SELECT customer_id CUSTID, transaction_date TRANS_DATE, amount + 100 DUES FROM
transactions;
E)SELECT customer_id AS ‘CUSTOMER-ID’, transaction_date AS DATE, amount + 100‘DUES
AMOUNT’ FROM transactions;
Answer: BD
解析：A 答案错在别名用了 date，而没有加双引号；C 答案错在别名中带有-，而没有加双
引号；E 答案错在别名不是用双引号。
Q102.The CUSTOMERS table has a CUST_CREDIT_LIMIT column of data type NUMBER.
Which two queries execute successfully? (Choose two.)
A)SELECT NVL(cust_credit_linit * .15, ‘Not Available’) FROM customers;
B)SELECT NVL2(cust_credit_linit * .15, ‘Not Available’) FROM customers;
C)SELECT NVL(TO_CHAR(cust_credit_limit * .15), ‘Not Available’) FROM customers;
D)SELECT TO_CHAR(NVL(cust_credit_limit * .15, ‘Not Available’)) FROM customers;
E)SELECT NVL2(cust_credit_limit, TO_CHAR(cust_credit_limit * .15), ‘Not Available ’) FROM
customers;
Answer: CE
解析:经典考题， NVL2 函数的格式如下：NVL2(expr1,expr2, expr3) 含义是：如果第一个参
数为空，则返回第三个参数；如果第一个参数不为空那么显示第二个参数的值。由于第三个
参数返回的是字符型，所以第二个参数也得是字符型，故而要用转换函数。

Q103.View the Exhibit and examine the structure of the CUSTOMERS table.

Using the CUSTOMERS table, you must generate a report that displays a credit limit increase of
15% for all customers.
Customers with no credit limit should have “Not Available” displayed.
Which SQL statement would produce the required result?
A)SELECT NVL (TO_CHAR(cust_credit_limit*.15), ‘Not Available’) “NEW CREDIT ” FROM
customers

 

B)SELECT TO_CHAR(NVL(cust_credit_limit*.15), ‘Not Available’)) “NEW CREDIT ” FROM
customers
C)SELECT NVL (cust_credit_limit*.15, ‘Not Available’) “NEW CREDIT” FROM customers
D)SELECT NVL (cust_credit_limit, ‘Not Available’)*.15 “NEW CREDIT” FROM customers
Answer: A
解析：注意 nvl 函数返回的数据类型，如果是字符型，则第一个表达式的数据类型也要为
字符型

Q104.Which statement is true about aggregate functions?
A)Aggregate functions can be nested to any number of levels
B)The AVG function implicitly converts NULLS to zero
C)Aggregate functions can be used in any clause of a SELECT statement
D)The MAX and MIN functions can be used on columns with character data types
Answer: D
解析：max 和 min 可以用在数字、字符、和日期型的数据类型上。）
分组函数不能嵌套太多层

Q105.Which two statements are true regarding the WHERE and HAVING clauses in a SELECT
statement? (Choose two.)
A)The WHERE and HAVING clauses can be used in the same statement only if they are applied to
different columns in the table.
B)The aggregate functions and columns used in the HAVING clause must be specified in the
SELECT list of the query.
C)The WHERE clause can be used to exclude rows after dividing them into groups.
D)The HAVING clause can be used with aggregate functions in subqueries.
E)The WHERE clause can be used to exclude rows before dividing them into groups.
Answer: DE
解析：where 和 having 子句中用到的列没有相关联性，所以 A 答案不对；还有注意一点
是先 where 子句对数据进行筛选，然后再进行分组，这样子可以减少数据的处理。
‘
Q106.Your database instance was shut down normally and then started in NOMOUNT state. You
then execute this command: ALTER DATABASE MOUNT;
Which two actions are performed? (Choose two.)
A)The online redo logs are opened
B)The online data files are opened
C)The alert log records the execution details
D)The Oracle background processes are started
E)The initialization parameter file is read
F)The control file is read
Answer: CF
解析：该题目 C 答案好像是对的，但是它说的是记录执行的详细信息；E 答案好像也是对
的，但是它是在实例启动的时候读到，现在这个是 mount 操作，实例早就起来了。

 

Q107.Which two are true about shrinking a segment online? (Choose two.)
A)It is not possible to shrink either indexes or Index Organized Tables (IOTs)
B)It always eliminates all migrated rows if any exist in the table
C)To shrink a table it must have a PRIMARY KEY constraint
D)To shrink a table it must have a UNIQUE KEY constraint
E)To shrink a table it must have row movement enabled
F)It must be in a tablespace that uses Automatic Segment Space Management (ASSM)
Answer: EF
解析：对表进行 shrink 操作时，要求行移动打开，而且表空间必须是 assm，否则在操作的
时候报错如下：
SQL> alter table emp10 shrink space; alter
table emp10 shrink space
*
ERROR at line 1:
ORA-10635: Invalid segment or tablespace type
Q108.Examine the details of the uncompressed, non-partitioned heap table CITIES.
Name Null? Type
CITYID NOT NULL NUMBER (4)
CITY_NAME VARCHAR2 (50)
Examine the command: SQL>ALTER TABLE cities SHRINK SPACE COMPACT;
What must you do before executing it?
A)Ensure there are no pending transactions on the table.
B)Disable all indexes on the table .
C)Ensure free space that is approximately equal to the space used by the table should be
available.
D)Enable row movement is enabled.
Answer:D
解析：想要 shrik 表的空间，因为要涉及到行的移动，所以要先把行移动功能打开
Q109.You want to reduce fragmentation and reclaim unused space for the sales table but not its
dependent objects. During this operation, you want to ensure the following:
i. Long-running queries are not affected.
ii. No extra space is used.
iii. Data manipulation language (DML) operations on the table succeed at all times throughout
the process iv. Unused space is reclaimed both above and below the high water mark.
Which alter TABLE option would you recommend?
A)DEALLOCATE UNUSED
B)SHRINK SPACE CASCADE
C)SHRINK SPACE COMPACT
D)ROW STORE COMPRESS BASIC

 

Answer: C
解析：如果在释放表碎片的释放不影响当前的操作，那么应该在 shrink 的时候加上
compact 选项，等业务空闲的时候再 DEALLOCATE UNUSE，所以 shrink space 可以分为两部
分进行操作。

Q110.Which four actions are possible during an Online Datafile Move operation?
A)Creating and dropping tables in the datafile being moved
B)Performing file shrink of the data file being moved
C)Querying tables in the datafile being moved
D)Performing Block Media Recovery for a data block in the datafile being moved
E)Flashing back the database
F)Executing DML statements on objects stored in the datafile being moved
Answer: ACDF
解析：实验证明答案 E 不行；D 答案可以；在线对数据文件进行迁移，意思是迁移过程中
包含的对象还可 以做 ddl 和 dml 操作，但是对数据文件进行恢复操作和收缩大小的操作
肯定是不行的，报错：ORA-63000: 不允 许该操作: 正在移动数据文件

Q111.Examine these commands: [oracle@host01 ~]$ sqlplus u1/oracle
SQL> SELECT * FROM emp;
ENO ENAME DN
----------- -------------- -----------------------------------
1 Alan 2
2 Ben 2
SQL> exit
[oracle@host01 ~]$ cat emp.dat
1, Alan, 2
3, Cur1, 4
4, Bob, 4
[oracle@host01 ~]$ sqlldr u1/oracle TABLE=emp
Which two statements are true about the sqlldr execution? (Choose two.)
A)It overwrites data in EMP with data in EMP.DAT
B)It uses the database buffer cache to load data
C)It generates a log that contains control file entries, which can be used with normal SQL*Loader
operations
D)It generates a sql script that it uses to load data from EMP.DAT to EMP
E)It appends data from EMP.DAT to EMP
Answer: CE
解析：实验证明，在当前路径下产生一个日志，里面有保护 sqlldr 控制文件的内容，看来
是 oracle 自动产生控制文件，方便我们使用，而不是省略控制文件。

 

Q112.A script abc.sql must be executed to perform a job. A database user HR, who is defined in
this database, executes this command: $ sqlplus hr/hr@orcl @abc.sql
What will happen upon execution?
A)The command succeeds and HR will be connected to the orcl and abc.sql databases
B)The command succeeds and HR will be connected to the orcl database instance, and the
abc.sql script will be executed
C)The command fails because the script must refer to the full path name
D)The command fails and reports an error because @ is used twice
Answer: B
解析：实验证明该脚本能够执行成功，oracle 提供了更好的命令行接口
Q113.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
Answer: BCD
解析：只要有 create any index 的权限，就可以为其它用户的表建索引，但是索引是属于创
建者；如果是不回表扫描，那么就只是访问索引；有索引的列被 upate 操作时，oracle 总
是自动更新索引

Q114.Which three statements are true about Oracle synonyms? (Choose three.)
A)A synonym cannot be created for a PL/SQL package
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
Answer: BCD
解析：同义词可以在表、存储过程、序列上创建。如果是 public 同义词，则对所以的用户
都可以访问。创建不同的同义词都需要授予不同的权限，只有属主和有 drop public synonym
权限的用户才能够删除 public 同义词
Q115.Which two tasks can be performed on an external table?
A)partitioning the table
B)creating an invisible index
C)updating the table by using an update statement
D)creating a public synonym
E)creating a view
Answer: DE

 

解析：外部表就只能创建视图和同义词，其它的更新操作都不允许

Q116.Choose the best answer. Examine these commands:
CONNECT / AS SYSDBA
SQL> CREATE DIRECTORY dumpdir AS ‘/u01/app’;
Directory created.
SQL> GRANT READ,WRITE ON DIRECTORY dumpdir TO PUBLIC;
Grant succeeded.
SQL> GRANT USER TEST IDENTIFIED BY test;
User created.
SQL> GRANT CREATE SESSION,RESOURCE,UNLIMITED TABLESPACE TO test;
Grant succeeded.
SQL> CONN test/test.
Connected.
SQL> CREATE TABLE test_employees (id NUMBER(3), name VARCHAR2(20), salary NUMBER(7));
Table created.
SQL> CREATE SYNONYM emp FOR test_employees;
Synonym create.
Now examine this command: $ expdp test/test DIRECTORY=dumpdir DUMPFILE=test_emp.dmp
LOGFILE=test.log TABLES=emp Which is true?
A)It performs the export successfully.
B)It throws an error as the table test_employees is empty and CONTENT=data_only is specified.
C)It throws an error as the QUERY parameter is not supported on an empty table.
D)It throws an error as Data Pump cannot find a table called TEST.EMP.
Answer:D
解析：之前以为可以通过同义词导出表，结果实验发现不能，为什么 oracle 要这么设计呢?，
这道题说了半天就是证明不能通过同义词来导表。

Q117.You issue the following command to drop the PRODUCTS table:
SQL > DROP TABLE products;
Which three statements are true about the implication of this command?
A)All data along with the table structure is deleted.
B)A pending transaction in the session is committed.
C)All indexes on the table remain but they are invalidated.
D)All views and synonyms on the table remain but they are invalidated.
E)All data in the table is deleted but the table structure remains.
Answer: ABD
解析：由于题目没有说回收站的事情，所以按照表被真正删除来处理。注意答案
B，它说的是跟 drop 操作同一个会话里面的事务会被提交
Q118.View the Exhibit and examine the structure of the PRODUCTS table. Which two tasks
require subqueries? (Choose two.)

 

A)Display the number of products whose PROD_LIST_PRICE is more than the average
PROD_LIST_PRICE
B)Display suppliers whose PROD_LIST_PRICE is less than 1000
C)Display products whose PROD_MIN_PRICE is more than the average PROD_LIST_PRICE of all
products, and whose status is orderable
D)Display the total number of products supplied by supplier 102 which have a product status of
obsolete
E)Display the minimum PROD_LIST_PRICE for each product status
Answer: AC
解析：什么时候用子查询，注意条件语句中进行比较的值如果涉及分组函数的值，则需要子
查询。

Q119.The SALES_Q1 and USERS tablespaces exist in one of your databases and TEMP is a
temporary tablespace. Segment creation is not deferred.
You execute this command:

Which three statements must be true so that the SALES user can create tables in SALES_Q1?
(Choose three.)
A)The sales user must have a quota on the TEMP tablespace
B)The sales user must have a quota on the SALES_Q1 tablespace to hold the initial extends of all
tables they plan to create in their schema
C)The sales user must have been granted the CREATE SESSION privilege
D)The sales user must have their quota on the users tablespace removed
E)The sales user must have a quota on the SALES_Q1 tablespace to hold all the rows to be
inserted into any table in their schema

 

F)The sales user must have been granted the CREATE TABLE privilege
Answer: BCF
解析：答案 C 与 F 比较简单，这里为什么选择 B 而不选择 E，因为题目是只要建表成功
就行，所以分配的 quata 只要够分配初始化区就可以了，这个答案有点深度。
Q120.Which three statements are true about table data storage in an Oracle Database? (Choose
three.)
A)Data block headers contain their own Data Block Address (DBA)
B)A table row piece can be chained across several database blocks
C)Multiple row pieces from the same row may be stored in different database blocks
D)Multiple row pieces from the same row may be stored in the same block
E)Data block free space is always contiguous in the middle of the block
F)Index block free space is always contiguous in the middle of the block
Answer: ABD
解析：该题难度比较大，块头都记录了块的 id 号；一行能够链接到其它块（行迁移）；同
一行的多个片段可以存放在同一个块，因为 update 造成的；由于有行迁移现象，所以答案
C 是错误的
Q121.Examine the description of the BOOKS table:

The table has 100 rows. Examine this sequence of statements issued in a new session:
INSERT INTO books VALUES (‘ADV112’, ‘Adventures of Tom Sawyer’, NULL, NULL);
SAVEPOINT a;
DELETE FROM books;
ROLLBACK TO SAVEPOINT a;
ROLLBACK;
Which two statements are true? (Choose two.)
A)The second ROLLBACK command does nothing
B)The second ROLLBACK command replays the delete
C)The first ROLLBACK command restores the 101 rows that were deleted, leaving the inserted
row still to be committed
D)The second ROLLBACK command undoes the insert
E)The first ROLLBACK command restores the 101 rows that were deleted and commits the
inserted row
Answer: CD
解析：有关 savepoint 与 rollback 组合的考题很多，这个只能根据实际情况去判断。第一
个 rollback 是回滚了 delete 操作，第二 rollback 是把所有的操作都回滚，剩下的就只有
insert 操作被回滚了。

 

Q122.Which three statements are true about external tables in Oracle 18c and later releases?
(Choose three.)
A)External table files can be used for other external tables in a different database
B)The ORACLE_LOADER access driver can be used to unload data from a database into an
external table
C)The ORACLE_DATAPUMP access driver can be used to unload data from a database into an
external table
D)They cannot be partitioned
E)The ORACLE_DATAPUMP access driver can be used to load data into a database from an
external table
F)They support UPDATES but not INSERTS and DELETES
Answer: ACD
解析：C 答案的意思是把数据库里面的数据取出（unload 这个词有多种意思，这里理解为
取出）然后放到外部表文件中。

Q123.Which three statements are true about the Oracle join and ANSI join syntax? (Choose
three.)
A)The Oracle join syntax supports creation of a Cartesian product of two tables
B)The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax
C)The SQL:1999 compliant ANSI join syntax supports natural joins
D)The SQL:1999 compliant ANSI join syntax supports creation of a Cartesian product of two
tables
E)The Oracle join syntax only supports right outer joins
F)The Oracle join syntax supports natural joins
G)The Oracle join syntax performs less well than the SQL:1999 compliant ANSI join syntax
Answer: BCD
解析：ANSI 可以明确写出两张表查询时得出笛卡尔集
123 变种题？
A) The SQL:1999 compliant ANSI Join syntax supports creation of a Cartesian product of two
tables.
B) The Orade join syntax performs less well than the SQL:1999 compliant ANST join syntax.
C) The SQL:1999 compliant ANSI join syntax supports natural Joins.
D) The Orade join syntax perfoms better than the SQL:1999 compliant ANSI join syntax.
E) The Orade join syntax supports creation of a Cartesian product of two tables.
F) The Oracle join syntax suports natural joins.
G) The Orade join syntax only supports right outer joins.
解析：所谓的 ANSI Join 就是在连接的时候用文字来表示，而不是用字符来表示，比如左 外
连接用 left join 来表示。Oracle 语法不支持专门生成笛卡尔集的语法（在 ANSI 语法中有
专门的语句来生成笛卡尔集：CROSS JOIN），Oracle 肯定觉得自己的语法要比 ANSI 的好，
所以选择 D；

 

Q124.Which two tasks can you perform using DBCA for databases? (Choose two.)
A)Configure a nonstandard block size for a new database
B)Register a new database with an available Enterprise Manager Management server
C)Change the standard block size of an existing database
D)Configure incremental backups for a new database
E)Enable flashback database for an existing database
Answer: BD
解析：DBCA 能够为已存在的数据库所做的操作有限，只能在创建新数据库的时候做一些设
置。但是使用 dbca 无法做上面的操作。

Q125.You plan to create a database by using the Database Configuration Assistant (DBCA), with t
he following specifications: Applications will connect to the database via a middle tier. The
number of concurrent user connections will be high. The database will have mixed workload,
with t he execution of complex BI queries scheduled at night. Which DBCA option must you
choose to create the database?
A)a General Purpose database template with default memory allocation
B)a Data Warehouse database template, with t he dedicated server mode option and AMM
enabled
C)a General Purpose database template, with t he shared server mode option and Automatic
Memory Management (AMM) enabled
D)a default database configuration
Answer: C
解析：根据题目要求配置共享服务器连接的数据库模板。

Q126.Which two options can be configured for an existing database by using the Database
Configuration Assistant (DBCA)?
A)Database Resident Connection Pooling
B)Oracle Suggested Backup Strategy
C)Database Vault in ORACLE_HOME
D)Non default block size tablespaces
E)Configure Label Security
Answer: CE
解析：这道题比较偏门，没有什么技术含量，使用 dbca 对已经存在的数据库进行修改时可
以修改的内容就一目了 然。

Q127.You execute this query: SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE), ‘MON’), ‘dd
“Monday for” fmMonthrrrr’) What is the result?
A)It executes successfully but does not return any result
B)It returns the date for the first Monday of the next month
C)It generates an error
D)It returns the date for the last Monday of the current month

 

Answer: B
解析：函数里面求当前月的最后一天的下一个周一，所以结果为下一个月的第一个周一。

Q128.Examine this command:
CREATE UNDO TABLESPACE undotbs01 DATAFILE ‘undotbs_01.dbf’ SIZE 100M AUTOEXTEND
ON;
Which two actions must you take to ensure UNDOTBS01 is used as the default UNDO tablespace?
(Choose two.)
A)Add the SEGMENT SPACE MANAGEMENT AUTO clause
B)Set UNDO_TABLESPACE to UNDOTBS01
C)Add the NOLOGGING clause
D)Make certain that the database operates in automatic undo management mode
E)Add the ONLINE clause
Answer: BD
解析：想要把一个 undo 表空间变成默认使用的，必须选择 undo 表空间为自动管理
Q129.Which three statements are true about views in an Oracle database? (Choose three.)
A)Views can be updated without the need to re-grant privileges on the view
B)Tables in the defining query of a view must always exist in order to create the view
C)The WITH CHECK clause prevents certain rows from being displayed when querying the view
D)Data Manipulation Language (DML) can always be used on views
E)Inserting one or more rows using a view whose defining query contains a GROUP BY clause will
cause an error
F)Deleting one or more rows using a view whose defining query contains a GROUP BY clause will
cause an error
G)The WITH CHECK clause prevents certain rows from being updated or inserted
Answer:AEFG
解析：复杂视图不允许 dml 操作，G 答案的意思是 WITH CHECK 子句禁止更新或插入
某些行，这里的某些行指的是：指定只能插入或更新视图可访问的行

Q130.In the SALES database, DEFERRED_SEGMENT_CREATION is TRUE. Examine this command:
SQL> CREATE TABLE T1(c1 INT PRIMARY KEY, c2 CLOB);
Which segment or segments, if any, are created as a result of executing the command?
A)T1, an index segment for the primary key, a LOB segment, and a lobindex segment
B)no segments are created
C)T1 only
D)T1 and an index segment created for the primary key only
E)T1, an index segment for the primary key, and a LOB segment only
Answer: B
解析：经过实验，因为段创建延迟设置为 true，建表时只要没有数据，段是不会创建的。
Q131.Choose the best answer. Which statement is true about database links?
A)A public database link can be created only by sys.

 

B)A database link created in a database allows a connection from that database's instance to the
target database's instance, but not vice versa.
C)A public database link can be used by a user connected to the local database instance to
connect to any schema in the remote database instance.
D)Private database link creation requires the same user to exist in both the local and the remote
databases.
E)A database link can be created only between two Oracle databases.
Answer:E
解析：一个 DBLINK 只能在两个数据库之间创建。
A 答案明显错误。
B 答案错是因为一个 dblink 只能从 A 到 B，不能使用它从 B 到 A。
C 答案错是一个从本地登录的用户不能用 public dblink 从一个数据库连接到远程数据库的
任何一个模式，因为连接的要么是固定用户，要么是当前连接的用户。
D 答案错是因为创建私有的 Dblink 时目标数据库可以没有该用户，可以创建成功，但是不
能访问。

Q131.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.
Answer:ABD
解析：有个视图的考点很多，这是最新出现的，不难。

Q133.Choose two.
Which two tasks can be performed in the NOMOUNT state?
A)full database recovery
B)re-creating control files
C)renaming data files .
D)creating a database

 

E)enabling online redo log archiving
Answer:BD
解析：考点简单，概念题

Q134.Choose three.
Which three statements are true about dynamic performance views?
A)They are owned by the SYSTEM user.
B)V$FIXED_TABLE can be queried to display the name of all dynamic performance views.
C)Data displayed by querying dynamic performance views is derived from metadata in the data
dictionary.
D)Data displayed by querying dynamic performance views is derived from memory structures.
E)They can be queried only when the database is open.
F)Read consistency is not guaranteed.
Answer:BDF
解析：动态性能视图的内容是来源于内存与控制文件，在 mount 状态下可以访问所以的
视图，由于不断的更新，所以不保证读一致性，这个是经典的答案。

Q135.Choose two. Which two are benefits of external tables?
A)The results of a complex join or aggregating function or both can be unloaded to a file for
transportation to another database.
B)They can be queried while the database is in the MOUNT state like dynamic performance
views.
C)They support DELETES which transparently deletes records in the file system as if they were
table rows.
D)They support UPDATES which transparently updates records in the file system as if they were
table rows.
E)They can be queried, transformed, and joined with other tables without having to load the data
first.
Answer:AE
解析：外部表可以做任何的查询操作，好处是不需要加载到数据库中，但是不支持任何的
dml 操作。
Q136.Choose two. DATADIR1 and DATADIR2 are database directory objects. Examine this
command:
[orac1e@host01 ~] expdp system/oracle \
> FULL=Y \
> DUMPFILE=datadir1:full%U.dmp, datadir2:full%U.dmp \
> FILESIZE=2G \ > PARALLEL=4 \
> LOGFILE=datadir1:/expfull.1og \
> JOB_NAME=expfull
Which two statements are true about the expdp operation?
A)It starts only when four worker processes are available.

 

B)It fails if the total size of the dump file is more than 2GB.
C)It creates dump files only for objects in the SYSTEM schema.
D)It creates dump files for the entire database.
E)It creates a master table to store details of the export operation.
Answer:DE
解析：导出时指定并行 4 个进程，导出的是整个数据库，同时在多个路径下创建 dump
文件，每个文件的大小不能超过 2G，超过的会创建新的 dump 文件。
Q137.Choose three. Which three actions are ways to apply the principle of least privilege?
A)revoking execute privilege on UTL_SMTP, UTL_TCP, UTL_HTTP, and UTL_FILE from the PUBLIC
user
B)setting the REMOTE_OS_AUTHENT parameter to true
C)using Access Control Lists (ACLs)
D)enabling Unified Auditing
E)revoking execute privilege on UTL_SMTP, UTL_TCP, UTL_HTTP, and UTL_FILE from the SYSTEM
user
F)setting the 07_DICTIONARY_ACCESSIBILITY parameter to true
Answer:ACD
解析：题目要求应用最低权限原则
在 Oracle11g 中，Oracle 在安全方面有了很多的改进，而在网络权限控制方面，也有一个
新的概念提出来，叫做 ACL(Access Control List)， 这是一种细粒度的权限控制。在 ACL 之
前，我们对于有一些程序包，例如 UTL_MAIL, UTL_SMTP 等这些包，你可以利用这些包连接
到外部的主机，而默认情况下，这些包都是都是赋予了 public 角色，所以可能会导致利用
这些 PL/SQL 程序包的恶意工具，所以 Oracle 提出了一个新的概念来解决这个问题，那就
是 ACL。
在开始展开 ACL 之前，首先明确一些概念。 ACL 这个概念不是 Oracle 首先提出来
的，在很多地方的权限管理都用到了 ACL，甚至在操作系统上也使用了 ACL。一般，我们在
做权限管理时，牵扯到一个问题，就是谁要在什么对象上作什么？这个正是对应了我们 ACL
中的概念。Principal will have what privileges on what object.谁就是 principal, 什么对象就是我
们的 object， 做什么就是我们的 privilege。那么如果有了这种细粒度的权限控制，我们就
可以定义我们哪些用户拥有哪个远程主机的什么权限了。有了这个概念我们就可以看看如何
使用 ACL 了。我们主要使用的是
DBMS_NETWORK_ACL_ADMIN 这个自带的包来完成。
Q138.Choose two. Which two statements are true about the configuration and use of
UNDO_RETENTION with no GUARANTEED RETENTION?
A)Unexpired UNDO is always retained.
B)UNDO_RETENTION specifies for how long Oracle attempts to keep expired and unexpired
UNDO.
C)UNDO_RETENTION specifies how long all types of UNDO are retained.
D)Active UNDO is always retained.
E)UNDO_RETENTION specifies for how long Oracle attempts to keep unexpired UNDO.
Answer:DE

 

解析：注意 undo 段的 undo 类型：
ACTIVE：总是保留
Unexpired：看保留的时间设置与 undo 表空间的保证参数设置 Expired：不保留
Q139.Choose two. Which two statements are true about a full outer join?
A)It returns only unmatched rows from both tables being joined.
B)It returns matched and unmatched rows from both tables being joined.
C)The Oracle join operator (+) must be used on both sides of the join condition in the WHERE
clause.
D)It includes rows that are returned by an inner join.
E)It includes rows that are returned by a Cartesian product.
Answer:BD
解析:full 连接返回匹配的和不匹配的行，同时包含内连接的结果；Oracle 语法不支持
full 连接。
Q140.Choose two. Examine these SQL statements which execute successfully:
CREATE TABLE emp (
emp_no NUMBER(2) CONSTRAINT emp_emp_no_pk PRIMARY KEY,
ename VARCHAR2 (15) ,
salary NUMBER(8,2),
mgr_no NUMBER(2)) ;
ALTER TABLE emp ADD CONSTRAINT emp_mgr_fk FOREIGN KEY (mgr_no) REFERENCES emp
(emp_no) ON DELETE SET NULL;
ALTER TABLE emp DISABLE CONSTRAINT emp_emp_no_pk CASCADE;
ALTER TABLE emp ENABLE CONSTRAINT emp_emp_no_pk;
Which two statements are true after execution?
A)The primary key constraint will be enabled and DEFERRED.
B)The primary key constraint will be enabled and IMMEDIATE .
C)The foreign key constraint will be enabled and IMMEDIATE .
D)The foreign key constraint will be disabled.
E)The foreign key constraint will be enabled and DEFERRED.
Answer:BD
解析：通过实验，把主键约束失效会导致外键约束也失效，但是不会因为主键约束生效
而生效，跟 ON DELETE SET NULL 没有关系。
Q141.Choose three. Which three statements are true about row chaining and row migration
done by Oracle database block space management?
A)Update statements can result in one or more migrated rows.
B)Insert statements can result in a migrated row.
C)A migrated row results in an update to any index on the table to point the rowid in the index to
the row's new location.
D)Row pieces of a chained row must always reside in different blocks.
E)Insert statements can result in a chained row.

 

F)Update statements can result in one or more chained rows.
Answer:AEF
解析：update 会造成行迁移和行链接，insert 会造成行链接不会造成行迁移；行链接的原
因是一行的数据太大导致一个数据块都存放不下整行的数据。

Q142.Choose two. Which two statements are true regarding Oracle database space management
within blocks managed by Automatic Segment Space Management (ASSM)?
A)Update operations always relocate rows into blocks with free space appropriate to the length
of the row being updated.
B)Insert operations always insert new rows into blocks with free space appropriate to the length
of the row being inserted.
C)PCTFREE defaults to 10% for all blocks in all segments for all compression methods.
D)The first block with enough free space to accommodate a row being inserted will always be
used for that row.
E)ASSM assigns blocks to one of four fullness categories based on what percentage of the block is
allocated for rows.
Answer:BC
解析：ASSM 管理的表空间在 insert 操作的时候总是找一个块能够放下整行的数据。基
本压缩和高级压缩的段默认的 pctfree 都是 10%，其它的 hcc 压缩方式的段无法测试。
Q143.Choose two. Which two statements are true about the PMON background process?
A)It registers database services with all local and remote listeners known to the database
instance.
B)It frees resources held by abnormally terminated processes.
C)It frees unused temporary segments.
D)It rolls back transactions when a process fails
E)It records checkpoint information in the control file.
Answer:BD
解析：PMON 就是为客户服务的，代表了客户进程，如果进程断了，则做一些清理的工
作，答案 BD 是肯定的
Q144.Choose two. A script abc.sql must be executed to perform a certain task. User HR password
HR exists in the target database and the account is unlocked.
The TNSNAMES.ORA file is up to date.
Examine this command attempted by the user: $ sqlplus hr/hr@orc1 @abc
What will happen and why?
A)The command succeeds and HR will be connected to the orcl database instance, and the abc
script will be executed.
B)The command fails because the script must refer to the full path name.
C)The command succeeds and HR will be connected to the orcl database and after logging out to
the abc database.
D)The command succeeds and HR will be connected to the orcl database instance, and the abc
script will be executed.

 

E)The command fails and reports an error because @ is used twice.
Answer:AD
解析：实验证明登录到数据库，然后执行成功。并且继续留在数据库中。AD 两个答案完
全一样

Q145.Choose two. Which two are true about global temporary tables?
A)Backup and recovery operations are available for these tables.
B)Their data is always stored in the default temporary tablespace of the user who created them.
C)If the ON COMMIT clause is transaction specific, all rows in the table are deleted after each
COMMIT or ROLLBACK.
D)Indexes can be created on them.
E)They can be created only by a user with the DBA role, but can be accessed by all users who can
create a session,
F)If the ON COMMIT clause is session specific, the table is dropped when the session is
terminated.
Answer:CD
解析：有关临时表的考点出现比较多。不管是基于那种级别，临时表是不会因为会话结束而
被删除，数据会被删除，但是保留表结构。以下是临时表的特点：
1. 创建 SQL 语句
CREATE GLOBAL TEMPORARY TABLE tablename (columns) [ ON COMMIT PRESERVE | DELETE
ROWS ]
SQL> create global temporary table emp_temp(eno number) on commit delete rows;
-- transaction level duration，事务级别，此为默认选项
SQL> create global temporary table emp_temp(eno number) on commit preserve rows; --session
level duration，会话级别
2.隔离性：
数据只在会话或者事务级别可见。不同用户可以使用同一个临时表，但是看到的都是各自的
数据。
3. 表上可以创建索引、视图、触发器等对象。
4. 索引只有在临时表是 empty 时可创建。
5. 临时表不产生数据的 redo，但是会生成 undo 的 redo。
6. 临时表目前只支持 GLOBAL 的，所以创建语句为 create global temporary table XXX。
7. 使用 truncate 只对当前会话有效。
8. 不能 export/import 表上的数据，只能导入导出表定义。
9.临时段在第一次 insert 或 CATS 时产生
Q146.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 tigger can be created on a GLOBAL TEMPORARY TABLE

 

Answer:BCF
解析：全局临时表跟普通表一样，可以创建索引、视图、触发器等等，dml 操作同样也会产
生 redo。临时表创建的目的就是为了测试，如果很多操作不支持，那就失去了测试的意义
Q147.Choose two. Which two statements are true about GLOBAL TEMPORARY TABLES?
A)GLOBAL TEMPORARY TABLE space allocation occurs at session start.
B)A GLOBAL TEMPORARY TABLE'S definition is available to multiple sessions.
C)A DELETE command on a GLOBAL TEMPORARY TABLE cannot be rolled back.
D)A TRUNCATE command issued in a session causes all rows in a GLOBAL TEMPORARY TABLE for
the issuing session to be deleted.
E)GLOBAL TEMPORARY TABLE rows inserted by a session are available to any other session whose
user has been granted select on the table.
Answer:BD
解析：经过实验，发现全局临时表的定义对于其它会话来说是可见的；如果是使用 ON
COMMIT PRESERVE ROWS 选项创建的全局临时表，则需要在 drop 前需要先 truncate。 E
答案错，不同会话插入到该表只有各自会话看到；
C 答案错，如果在创建全局临时表时指定了 ON COMMIT PRESERVE ROWS，则可以回滚
delete 操作，否则不可以。
Q148.Choose two. You execute these commands successfully:
CREATE GLOBAL TEMPORARY TABLE invoices_gtt
( customer_id INTEGER,
invoice_total NUMBER(10,2)
) ON COMMIT PRESERVE ROWS;
INSERT INTO invoices_gtt VALUES (1, 100) ;
COMMIT;
Which two are true?
A)To drop the table in this session, you must first truncate it.
B)When you terminate your session, the row will be deleted.
C)Other sessions can view the committed row.
D)You can add a foreign key to the table.
E)You can add a column to the table in this session.
Answer:AB
解析：经过试验，AB 答案是对的，特别是 A 答案出人意料之外，可能是以前用的少的原
因，注意创建临时表时 ON COMMIT PRESERVE ROWS 的意思是事物完成后保留数据，默
认是提交后行就删除。
注意：如果没有 ON COMMIT PRESERVE ROWS 选项，那么在临时表上是可以创建约束和
添加列以及索引。但是一旦临时表有数据就无法进行上述的操作。所以该题很容易变题形成
新的考题

Q149.choose three which three statements are true about GLOBAL TEMPORARY TABLES?
A)A DELETE comand on GLOBAL TEMPORARY TABLE cannot be rolled back
B)GLOBAL TEMPORARY TABLE rows inserted by a session are available to any other session whose

 

user has been granted selected on the table
C)Any GLOBAL TEMPORARY TABLE rows exsting at session termination will be deleted
D)A GLOBAL TEMPORARY TABLE's definition is available to multiple sessions;
E)GLOBAL TEMPORARY TABLE space allocation occurs at session start.
F)A TRUNCATE command issues in a session causes all rows in a GLOBAL TEMPORARY TABLE for
the issuing session to be deleted.
Answer:CDF
解析：全局临时表的数据在事务完成时自动删除，或者会话结束的时候；临时表的结构能够
被其它会话看到，但是各自会话的表数据只能被自己的会话看到。临时表是不分配永久性存
储空间，而是存放在临时表空间中，所以答案 E 错误
Q150.Choose the best answer Examine these statements and results:
SQL> SELECT COUNT(*) FROM emp;
COUNT (*)
--------------------
14
SQL> CREATE GLOBAL TEMPORARY TABLE t_emp AS SELECT * FROM emp;
Table created.
SQL> INSERT INTO t_emp SELECT * FROM emp;
14 rows created.
SQL> COMMIT;
Commit complete.
SQL> INSERT INTO t_emp SELECT * FROM emp;
14 rows created.
SQL> SELECT COUNT(*) FROM t_emp;
How many rows are retrieved by the last query?
A) 0
B) 14
C) 28
D) 42
Answer:B
解析：因为是普通的临时表，所以在事务结束的时候会删除数据。之前有一个相关的考题，
但是有不同的选项。

Q151.Choose two. Which two statements are true about the Oracle Data Dictionary?
A)All data dictionary view join base tables to dynamic performance views.
B)Data dictionary base tables can be queried directly.
C)It is owned by the SYS user.
D)It is owned by the SYSTEM user.
E)Data dictionary views are always created with queries that join two or more base tables.
Answer:BC
解析：数据字典基表可以直接查询，并且属于基表，只能够在数据库 open 的时候看
到。

 

Q152.Choose two. You execute this command:
CREATE SMALLPILE TABLESPACE sales DATAFILE '/u01/app/oracle/sales01.dbf ' SIZE 5G SEGMENT
SPACE MANAGEMENT AUTO;
Which two statements are true about the SALES tablespace?
A)It is a locally managed tablespace.
B)It must be smaller than the smallest BIGPILE tablespace.
C)Free space is managed using freelists.
D)It uses the database default blocksize.
E)Any data files added to the tablespace must have a size of 5 gigabytes.
Answer:AD
解析：虽然该题是新出现，但是概念比较简单

Q153.Choose two. One of your database instances was shut down normally and then started in
NOMOUNT state.
You then executed this command: ALTER DATABASE MOUNT ;
Which two of these actions are performed?
A)Online redo logs are opened.
B)Oracle background processes are started.
C)The initialization parameter file is read.
D)Control files are read.
E)Online data files are opened.
F)Oracle shared memory structures are allocated.
G)The alert log has instance startup details written to it.
Answer:DG
解析：该题比以往题目多了个答案 G，如果有 oracle 的基本理论知识，应该知道告警
日志写的内容。
Q154.Choose two. Which two statements are true about advanced connection options supported
by Oracle Net for connection to Oracle Database instances?
A)Source Routing requires the use of encrypted connections.
B)Connect Time Failover requires the use of Transparent Application Failover (TAF).
C)Connect Time Failover requires the connect string to have two or more listener addresses
configured.
D)Load Balancing requires the use of a name server.
E)Load Balancing can balance the number of connection to dispatchers when using a Shared
Server configuration.
Answer:CE
解析：Net Manager 可以配置一个 tns 有多个连接 ip 与端口。

 

Q155.Choose two. Which two statements are true about external tables in Oracle 18c and later
releases?
A)The ORACLE_LOADER access driver can be used to unload data from a database into an
external table.
B)External table files can be used for other external tables in a different database.
C)They cannot be partitioned.
D)The ORACLE_DATAPUMP access driver can be used to unload data from a database into an
external table.
E)They support UPDATES but not INSERTS and DELETES.
Answer:BD
解析：基于 datadump 引擎的外部表可以产生外部文件并且可以给其它的外部表使用。
Oracle 数据 DATAPUMP 访问驱动程序可用于将数据从数据库卸载到外部表中。外部表从
12.2 版本后就支持分区。
Q156.Choose two. Which two statements are true about single-row functions?
A)They return a single result row per table.
B)They can be used only in the WHERE clause of a SELECT statement.
C)They can accept only one argument.
D)The argument can be a column name, variable, literal or an expression.
E)The data type returned can be different from the data type of the argument.
Answer:DE
解析：DE 答案是单行函数最经典的正确答案，在很多题目都出现，一定要记住。
Q157.Choose three. Which three statements are true regarding single row subqueries?
A)They can be used in the WHERE clause.
B)They must be placed on the right side of the comparison operator or condition.
C)They must return a row to prevent errors in the SQL statement.
D)They must be placed on the left side of the comparison operator or condition.
E)A SQL statement may have multiple single row subquery blocks.

 

F)They can be used in the HAVING clause.
Answer:AEF
解析：单行子查询概念比较简单，注意答案 BD，单行子查询可以放在比较操作符的两边，
以前有过类似的题目。答案 F 比较出人意外，居然可以使用。
Q158.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.
Answer:BDE
解析：从单行函数的定义和语法可以判断答案 BDE 是对的。答案 B 只要符合规律，可以
嵌套任何层次。) 单行函数的定义如下：操作数据项接收参数并返回一个值在返回的每一行
上进行处理每一行都有一个处理结果可能要修改数据类型可以进行嵌套
语法：function_name (column|expression, [arg1, arg2,...])
可以有多个参数：
CONCAT(Good, String)

Q159.Choose two. Which two statements are true about the Automatic Diagnostic Repository
(ADR)?
A)It supports diagnostics for Automatic Storage Management (ASM).
B)It supports diagnostics for Oracle Clusterware.
C)It is held inside an Oracle database schema.
D)It supports diagnostics for Oracle based applications.
E)It is held in an Oracle database data dictionary.
Answer:AB
解析： 从 Oracle Database 11gR1 开始，数据库、自动存储管理(ASM)、集群就绪服务(CRS)
和其它 Oracle 产品或组件将所有诊断数据都存储在 ADR 中。每种产品的每个实例都将诊
断数据存储在自己的 ADR 主目录下。例如，在具有共享存储和 ASM 的 Real Application
Clusters 环境中，每个数据库实例和每个 ASM 实例在 ADR 中都有一个主目录。 ADR 的
统一目录结构对各种产品和实例使用一致的诊断数据格式，而且还有一组统一的工具，使客
户和 Oracle 技术支持可以相互关联并分析多个实例的诊断数据
Q160.Which two statements are true about the Automatic Diagnostic Repository (ADR)?
A)The ADR base is shared across multiple instances.
B)The ADR base keeps all diagnostic information in binary format.
C)The ADR can be used to store statspack snapshots to diagnose database performance issues.
D)The ADR can be used for problem diagnosis even when the database instance is down.
E)The ADR is used to store Automatic Workload Repository (AWR) snapshots.
Answer: AD

 

解析：ADR 的基本目录是可以共享的，能够都不设置，默认会用到 ORACLE_BASE 目
录，而这个变量对应所以的实例都是一样的。ADR 提供诊断信息，并不依赖数据库启 动。
AWR 快照是存放在 sysaux 表空间中。
Q161.Choose two. Which are two of the account management capabilities that can be configured
using Oracle profiles?
A)the maximum amount of CPU time allowed for a user's sessions before their account is locked
B)the number of days for which an account may be inactive before it is locked
C)the maximum number of sessions permitted for a user before the password must be changed.
D)the ability to prevent a password from ever being reused
E)the number of days for which an account may be logged in to one or more sessions before it is
locked
Answer:BD
解析：虽然题目只要我们选择 2 个答案，但是根据查询结果可以看出 profile 能够做的事
情。

Q162.Which two statements are true about SQL*Loader Express Mode in an Oracle 12c
database?
A)It can load data in parallel.
B)No data file needs to be specified.
C)It loads data faster than conventional SQL*Loader.
D)lt loads data more efficiently than conventional SQL*Loader.
E)It requires Enterprise Manager Express to be configured.
Answer:AB
解析：12C 中的 SQL*Loader 新增加了 Express Mode，借助这个特性，可以在最小化配置
的情况下加载数据（比如无需要 创建 Control file)。查看导入日志，发现使用并行导入。
sqlldr study/study@pdb1 TABLE=test
（1）这里有个需要注意的地方，上面的命令行中的表名大小写一定要和操作系统上对应的
文件名大小写一样，比如 TABLE=TEST 那么对应的存放数据文件要是 TEST.dat, 如果
TABLE=test,那么对应的存放数据文件要是 test.dat (文件扩展名 必须是.dat)
（2）数据文件必须是逗号分隔的 SQL*Loader 和 external tables 支持的格式
Q163.On your Oracle 19c database, you invoke SQL*Loader to load data into the employees table
in the hr schema by issuing the command:
$> sqlldr hr/hr@pdb table=employees
Which two statements are true about the command?
A)It succeeds with default settings if the employees table exists in the hr schema.
B)It fails because no SQL*Loader data file location is specified.
C)It fails if the hr user does not have the create any directory privilege.
D)It fails because no SQL*Loader control file location is specified.
E)It succeeds and creates the employees table in the HR schema.
Answer: AC
解析：sqlldr hr/hr table=employees 能够执行，会默认找 employees.dat 文件，然后临时创

 

建一个 directory，如果没有权限，会报如下错误 ：SQL*Loader-816: error creating temporary
directory object SYS_SQLLDR_XT_TMPDIR_00000 for file employees.dat，该题目在 062 中出现
过。

Q164.choose three In an Oracle 12c database, you attempt to load data into the HR.EMPLOYEES
table in a pluggable database (PDB): $> sqlldr hr/hr@pdb table=employees Which three are true?
A)It creates the HR.EMPLOYEES table in the PDB and loads data into it if the table does not exist
in the PDB to which HR connects.
B)It loads data into HR.EMPLOYEES if the table exists in CDB$ROOT.
C)It succeeds if HR is a local user in the PDB to which HR connects.
D)It succeeds if HR is a common user.
E)It loads data into HR.EMPLOYEES if the table exists in the PDB to which HR connects.
Answer:CDE
解析：看命令就是连接到 pdb 中，可以是本地用户和通用用户，导入数据到 pdb 中的本
地和通用用户中。，注意通用用户不一定都要有 c##前缀。
Q165.Choose two. Which two statements are true about table data storage in an Oracle
Database?
A)Data block free space is always contiguous in the middle of the block.
B)A table row can be chained across several database blocks.
C)Multiple row pieces from the same row may be stored in different database blocks.
D)Index block free space is always contiguous in the middle of the block.
E)Multiple row pieces from the same row must be stored in different database blocks.
Answer:BC 解析：行链接会导致一行存放在多个数据块中；一行的多个片段可以存放在同一
个块中也可能存放在不同的数据块中，我们用 bbed 工具实验发现，我们修改一行中的某
个列值，如果长度大于原来的列，则该列值会放在同一个块的 free 空间中，如果 free 空
间不够，则发生行迁移，如果一个块的空间放不下一行的数据，则发生行链接。这道题考的
深度很深。

Q166.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)A view must only refer to tables in its defining query.
D)The WITH CHECK clause prevents certain rows from being displayed when querying the view.
E)The WITH CHECK clause prevents certain rows from being updated or inserted in the underlying
table through the view.
Answer:AE
解析：经过实验，视图是不能创建索引的；with check 子句是预防通过视图对基表进行
update 和 insert，该答案出现的机率很大
Q167.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.
Answer:AC
解析： AC 两个答案出现的机率很大，特别是 C 答案，很多人都弄不清楚其意义。） OR
REPLACE re-creates the view if it already exists
FORCE creates the view regardless of whether or not the base tables exist
WITH CHECK OPTION specifies that only rows accessible to the view can be inserted or
updated
WITH READ ONLY ensures that no DML operations can be performed on this view
Q168.Choose two. Which two statements are true about undo segments and the use of undo by
transactions in an Oracle database instance?
A)A single transaction may use multiple undo segments simultaneously.
B)Undo segments can wrap around to the first extent when a transaction fills the last extend of
the undo segment.
C)Undo segments can extend when a transaction fills the last extent of the undo segment.
D)Undo segments can be stored in the SYSTEM tablespace.
E)Undo segments can be stored in the SYSAUX tablespace.
Answer:CD
解析：UNDO 段是可以扩展的；在 system 表空间有个 undo 段，专门给 system 表空间
的对象使用，这个答案比较有深度。

Q169.Choose two. 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.
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
解析：所有的数据库的时间都是来源于服务器系统的时间；current_date 返回的日期和时
间会根据不同会话的位置时区显示。

Q170.Choose two. Which two statements are true about the tools used to configure Oracle Net
Services?
A)The lsnrctl utility requires a listener.ora file to exist before it is started.
B)Enterprise Manager Cloud Control can be used to centrally configure net service names for any
database server target.

 

C)The Oracle Net Configuration Assistant is only used when running the Oracle installer.
D)Oracle Net Manager can be used to locally configure naming methods on a database server.
E)Oracle Net Manager can be used to centrally configure listeners on any database server target.
Answer:BD
解析：从 oracle 10g 开始，可以在没有监听配置文件的情况下启动默认监听；emcc 可以
配置目标数据库的网络服务名；net 管理器只能配置本地的监听和 naming methods。
Q171.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 CREATE TABLE AS SELECT statement followed by a SELECT FOR
UPDATE statement
B)after successfully executing a COMMIT or ROLLBACK followed by a DML statement
C)after successfully executing a TRUNCATE statement followed by a DML statement
D)after successfully executing a DML statement following a failed DML statement
E)after successfully executing a COMMIT or ROLLBACK followed by a SELECT statement
F)after successfully executing a CREATE TABLE statement followed by a CREATE INDEX statement
Answer:ABC
解析：本道题是新题，考的是什么时候会产生一个新的事务，答案就是在完成上一个事
务后再产生新的事务。实验证明 select for update 也是新事务的开始。是一种新型的考法。
Q172.Choose the best answer. What is true about non-equijoin statement performance?
A)The BETWEEN condition used with an non-equijoin always performs better than when using
the >= and <= conditions.
B)The join syntax used makes no difference to performance.
C)The BETWEEN condition used with an non- equijoin sometimes performs better than using
the >= and <= conditions.
D)The Oracle join syntax performs less well than the SQL:1999 compliant ANSI join syntax.
E)The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax.
Answer:E
解析：此道题的答案跟 80 道题有相似之处，但是答案略微不同，注意变化。
Q173.Choose two. You want to apply the principle of Least Privilege in all your live databases.
One of your requirements is to revoke unnecessary privileges from all users who have them using
Privilege Analysis.
Which two are types of an alyses that can bed one using the DBMS_PRIVILEGE_CAPTURE
package?
A)analysis of privileges granted indirectly to a role that are then used by a user who has been

 

granted that role
B)analysis of privileges that a user has on other schema's objects
C)analysis of privileges that a user has on their own schema objects
D)analysis of privileges granted directly to a role that are then used by a user who has been
granted that role
E)analysis of all privileges used by the SYS user.
Answer:AD
解析：实验发现只能分析系统权限，不能分析授权的对象权限。）非直接给的 role 权
限指的是通过 role1 把权限授权给 role2，实验发现也可以分析该权限。
实验证明可以分析所有给予它的权限，如果是 select 自己的表是不会分析权限，也就是在
自己对象上操作的固有的权限是不分析的。

Q174.Choose two. Which two statements are true regarding indexes?
A)The RECYCLE BIN never contains indexes.
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.
Answer:BD
解析：索引应该会被放到回收站，只不过我们看不到而已，通过实验发现表被闪回后，
原来的索引的名字都被改成跟回收站的名字一样；一个 update 操作如果修改了带有索引的
列，则就会同时更新，如果没有就不会更新。

Q175.Choose two. Which two statements are true concerning logical and physical database
structures?
A)A segment might have only one extent.
B)A segment's blocks can be of different sizes.
C)Segments can span multiple tablespaces.
D)All tablespaces may have one or more data files.
E)A segment can span multiple data files in some tablespaces.
Answer:AE
解析：有关段和区和数据文件的关系每个版本都会出现。

Q176.Choose two Which two statements are true about the Oracle join and ANSI join syntax?
A)The Oracle join syntax performs less well than the SQL: 1999 compliant ANSI join syntax.
B)The Oracle join syntax supports creation of a Cartesian product of two tables.
C)The Oracle join syntax lacks the ability to do outer joins.
D)The SQL:1999 compliant ANSI join syntax supports creation of a Cartesian product of two
tables.
E)The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax.
Answer:DE
解析：有关 oracle 语法和 ANSI 语法的题考也是每次必考，oracle 总得宣传一下自己的
好处。

 

Q177.Choose three.
Which three relationships between instances and Oracle databases are possible without using
Multi-tenant?
A)one instance on one server that has no database mounted
B)one instance on one server mounting and opening one database
C)one instance on one server mounting and opening multiple databases
D)two or more instances on separate servers all mounting and opening the same database
E)one instance on one server mounting multiple databases
Answer:ABD
解析：实例和数据库的关系比较简单，答案 D 指的是 rac 环境。

变种题？和 177 相似
Which three instance situations are possible with the Oracle Database server without multitenant?
(Choose three.)
A. two or more instances on separate servers all associated with one database
B. one instance on one server associated with one database
C. one instance on one server associated with two or more databases on the same server
D. one instance on one server not associated with any database
E. one instance on one server associated with two or more databases on separate servers
Answer:ABD
（解析：A 的情况适合于 RAC 环境；B 的情况适合于单实例；D 的情况针对实例可以和数
据库不关联。）

Q178.Choose two. Examine the description of the SALES table:
Name
-------------------------------
PRODUCT_ID
CUSTOMER_ID
TIME_ID
CHANNEL_ID
PROMO_ID
QUANTITY_SOLD
PRICE
AMOUNT_SOLD
Examine this statement:
CREATE TABLE sales1
(prod_id, cust_id,
quantity_sold, price
) AS
SELECT product_id, customer_id, quantity_sold, price FROM sales WHERE 1 = 1;

 

Which two statements are true?
A)SALES1 is created with 55,000 rows.
B)SALES1 is created with no rows.
C)SALES1 has NOT NULL constraints on any selected columns which had those constraints in the
SALES table.
D)SALES1 has PRIMARY KEY and UNIQUE constraints on any selected columns which had those
constraints in the SALES table.
E)SALES1 is created with 1 row.
Answer:AC
解析:where 1=1 就是指满足条件；通过子查询创建的表，只有 not null 约束能够继承过
来，其它的约束都不行。

Q179.Choose two. Examine this query: SELECT * FROM bricks , colors;
Which two statements are true?
A)You can add a WHERE clause with filtering criteria.
B)It returns the same rows as SELECT * FROM bricks CROSS JOIN colors;.
C)You can add a USING clause with a join condition.
D)It returns the number of rows in BRICKS plus the number of rows in COLORS.
E)You can add an ON clause with a join condition.
Answer:AB
解析：经过测试，可以带上 where 子句；该语句就是交叉连接，产生笛卡儿积。
Q180.Choose two. Which two are true about unused columns?
A)You can query the data dictionary to see the names of unused columns.
B)CASCADE CONSTRAINTS must be specified when setting a column to unused if that column is
referenced in a constraint on another column.
C)Setting an indexed column to unused results in an error.
D)If you set all the columns of a table to unused, the table is automatically dropped.
E)You can specify multiple column names in an ALTER TABLE... SET UNUSED statement.
Answer:BE
解析：不能把表的所有列设置为 unused；可以同时设置多个列 unused。
SQL> ALTER TABLE emp2 SET UNUSED (ename,comm);
SQL> alter table emp2 set unused column sal;
Q181.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.
Answer:AC
解析：注意虚拟列与隐含列的区别。

 

Oracle 11G 在表中引入了虚拟列，虚拟列是一个表达式，在运行时计算，不存储在数据库中，
不能更新虚拟列的值。
定义一个虚拟列的语法：column_name [datatype] [GENERATED ALWAYS] AS [expression]
[VIRTUAL]
1.虚拟列可以用在 select，update，delete 语句的 where 条件中，但是不能用于 DML 语句
2. 可以基于虚拟列来做分区
3. 可以在虚拟列上建索引，oracle 的函数索引就类似。
4. 可以在虚拟列上建约束

Q182.Choose two. Which two are SQL features?
A)providing graphical capabilities
B)providing variable definition capabilities
C)providing database transaction control
D)processing sets of data
E)providing update capabilities for data in external files
Answer:CD
解析：SQL 语句提供数据处理和事务控制功能；变量的定义属于运行环境提供的功能。
Q183.Choose the best answer. Examine this statement:
SELECT 1 AS id, 'John' AS first_name, NULL AS commission FROM DUAL
INTERSECT
SELECT 1, 'John' ,NULL FROM DUAL ORDER BY 3;
What is returned upon execution?
A) 2 rows
B) 1 row.
C) 0 rows
D) an error
Answer:B
解析：经过实验，发现答案 B 正确，本题让人感到混乱的就是第一条语句，其 实就是多了
别名的定义

Q184.Choose two. Examine the description of the CUSTOMERS table:
Name Null? Type
------------------- ---------------------- ----------------------
CUSTOMER_ID NOT NULL NUMBER(38)
CUSTOMER_NAME NOT NULL VARCHAR2 (100)
INSERT_DATE NOT NULL DATE
Which two statements will do an implicit conversion?
A) SELECT * FROM customers WHERE customer_id = '0001' ;
B) SELECT * FROM customers WHERE TO_CHAR (customer_id) = '0001';
C) SELECT * FROM customers WHERE customer_id = 0001;

 

D) SELECT * FROM customers WHERE insert_date = '01-JAN-19';
E) SELECT * FROM customers WHERE insert_date = DATE '2019-01-01' ;
Answer:AD
解析：隐式转换注意日期默认格式

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
解析：该题还有其它的变种题考法。

Q186.Choose two. Examine the description of the PRODUCTS table which contains data:
Name Null? Type
------------------------- ---------------- --------------------------
PROD_ID NOT NULL NUMBER (2)
PROD_NAME NOT NULL VARCHAR2 (20)
EXPIRY_DATE DATE
Which two are true?
A)The PROD_NAME column cannot have a DEFAULT clause added to it.
B)The EXPIRY_DATE column data type can be changed to TIMESTAMP.
C)The PROD_ID column can be renamed.
D)The PROD_ID column data type can be changed to VARCHAR2 (2).
E)The EXPIRY_DATE column cannot be dropped.
Answer:BC
解析：实验证明答案 BC 是对的
Q187.Choose two. Which two are true about transactions in the Oracle Database?
A)An uncommitted transaction is automatically committed when the user exits SQL*Plus.
B)A DDL statement issued by a session with an uncommitted transaction automatically commits
that transaction.
C)DDL statements automatically commit only data dictionary updates caused by executing the
DDL.
D)A session can see uncommitted updates made by the same user in a different session.
E)DML statements always start new transactions.
Answer:AB
解析：正常退出 sqlplus 默认提交；ddl 执行会默认提交。
Q188.Choose the best answer. Examine these statements:

 

CREATE TABLE alter_test ( c1 VARCHAR2 (10), c2 NUMBER(10)) ;
INSERT INTO alter_test VALUES ('123', 123) ;
COMMIT ;
Which is true about modifying the columns in ALTER_TEST?
A)C1 can be changed to NUMBER (10) but c2 cannot be changed to VARCHAR2 (10).
B)C1 can be changed to VARCHAR2 (5) and C2 can be changed to NUMBER(12,2).
C)C1 can be changed to NUMBER (10) and C2 can be changed to VARCHAR2 (10).
D)C2 can be changed to VARCHAR2 (10) but C1 cannot be changed to NUMBER (10).
E)C2 can be changed to NUMBER(5) but C1 cannot be changed to VARCHAR2 (5).
Answer:B 解析：数据类型改变必须是相似的，但是长度是可以改变的。
Q189.Choose the best answer. Examine this statement which returns the name of each employee
and their manager:
SELECT e.last_name AS emp, m.1ast_name AS mgr FROM employees e JOIN managers m ON
e.manager_id = m.employee_id ORDER BY emp;
You want to extend the query to include employees with no manager.
What must you add before JOIN to do this?
A)LEFT OUTER
B)RIGHT OUTER
C)CROSS
D)FULL OUTER
Answer:A
解析：列出所有的员工名，即使没有管理员，由于 employees 表在前面，所以用左外连接。
Q190.Choose the best answer. Examine this statement, which executes successfully:
SELECT d.department_name, ROUND (AVG (NULLIF (e.salary,0))) AS avgsal, MAX(e.salary) AS
maxsal FROM employees e JOIN departments d ON (e.department_id = d.department_id)
GROUP BY d. department_name ORDER BY 2;
In which order are the rows displayed?
A)sorted by DEPARTMENT_NAME
B)sorted by MAXSAL
C)sorted by DEPARTMENT_NAME and AVGSAL
D)sorted by DEPARTMENT_NAME and MAXSAL
E)sorted by AVGSAL
Answer:E 解析:经过测试，答案 E 是正确，其实这道题不难，order by 2，即按照第二列进 行
排序，也就是 avgsal。
Q191.Choose the best answer. You have the privileges to create any type of synonym.
Which statement will create a synonym called EMP for the HCM.EMPLOYEE_RECORDS table that
is accessible to all users?
A)CREATE SYNONYM PUBLIC.emp FOR hcm.employee_records;
B)CREATE PUBLIC SYNONYM. emp FOR hcm . employee_records;
C)CREATE GLOBAL SYNONYM.emp FOR hcm.employee_records;

 

D)CREATE SYNONYM SYS.emp FOR hcm.employee_records;
E)CREATE SYNONYM emp FOR hcm.employee_records ;
Answer:B
解析：大家都可以访问的同义词就是 public 同义词，没有 global 同义词。
Q192.Choose two Examine the description of the EMPLOYEES
table:
Name Null? Type
----------------------- ----------------------- ----------------------------- -
EMP_ID NOT NULL NUMBER
EMP_NAME DEPT_ID VARCHAR2 (40)
SALARY NUMBER(8,2)
HIRE_DATE DATE
NLS_DATE_FORMAT is DD-MON-RR.
Which two queries will execute successfully?
A)SELECT dept_id, MAX(SUM(salary)) FROM employees GROUP BY dept_id;
B)SELECT AVG (MAX(salary)) FROM employees GROUP BY salary;
C)SELECT dept_id, AVG(MAX(salary)) FROM employees GROUP BY dept_id, salary;
D)SELECT dept_id, SUM(salary) FROM employees WHERE hire_date > '01-JAN-19' GROUP BY
dept_id;
E)SELECT dept_id, AVG(MAX(salary)) FROM employees GROUP BY dept_id HAVING hire_date >
'01-JAN-19';
Answer:BD
解析：经过测试，BD 正确，其它错误的都是嵌套了分组函数，因为嵌套一个分组函数等于
要以里面的列做为分组，最后外面还以另外一个列为分组列，所以不支持。

Q193.Choose the best answer. Examine the description of the EMPLOYEES table:
Name Null? Type
-------------------------- ------------------- ---------------------
EMPLOYEE_ID NOT NULL NUMBER(38)
SALARY NOT NULL NUMBER (38)
DEPARTMENT_ID NOT NULL NUMBER(38)
Which statement increases each employee's SALARY by the minimum SALARY for their
DEPARTMENT_ID?
A)UPDATE employees e1 SET salary = salary + (SELECT MIN (salary) FROM employees e2) ;
B)UPDATE employees e1 SET salary = (SELECT e2.salary + MIN(e2. salary) FROM employees e2
WHERE e1. department_id = e2.department_id GROUP BY e2. department_id) ;
C)UPDATE employees e1 SET salary = salary + (SELECT MIN(e1.salary) FROM employees e2
WHERE e1. department_id = e2. department_id) ;
D)UPDATE employees e1 SET salary = (SELECT e1.salary + MIN(e2.salary) FROM employees e2
WHERE e1. department_id = e2. department_id) ;
Answer:D

 

解析：题意为：哪个报表将每个员工的工资增加到其部门 ID 的最低工资，所以需要用到关
联子查询。
经过测试，答案 D 正确。最关键的地方是 e1.salary + MIN(e2.salary)，而不是 e2.salary +
MIN(e2. salary)，如果用 e2.salary 则查询需要用 group by 子句，则不能达到题目要求。
Q194.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
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
Answer:CDF
解析：正确的答案是前面先要完成事务，然后紧跟着一个 dml 操作，select for update 也
是一个新事务的开始。

Q195.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.
E)An UNUSABLE index is maintained when DML is performed on its underlying table.
F)An index can be created as part of a CREATE TABLE statement.
Answer: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);

 

Q196.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
Answer:A
（解析：注意这里把时区改成-5:00 ，即-05:00 是西五区时区，所以换算成时间就是答案 A
的值。） SQL> ALTER SESSION SET NLS_DATE_FORMAT = DD -MON-YYYY HH24:MI:SS ;
会话已更改。
SQL> SELECT DBTIMEZONE, SYSDATE FROM DUAL;
DBTIMEZONE SYSDATE
------------ -------------------------
+00:00 24-9 月 -2020 23:11:07
SQL> select LOCALTIMESTAMP from dual;
LOCALTIMESTAMP
---------------------------------------------------------------------------
24-9 月 -20 11.11.21.619000 下午
SQL> ALTER SESSION SET TIME_ZONE = -5:00 ;
会话已更改。
SQL> select LOCALTIMESTAMP from dual;
LOCALTIMESTAMP
---------------------------------------------------------------------------
24-9 月 -20 10.12.10.686000 上午

Q197.Choose the best answer. Examine the data in the PRODUCTS table:
PROD_ID PROD_NAME PROD_LIST CATEGORY_ID
------------------- -------------------------- ------------------- --------------------
101 Plate 10 1
102 Cup 20 1
103 Saucer 20 1
104 Knife 30 1
105 Fork 30 1

 

Examine these queries:
1. SELECT prod_name, prod_list FROM products WHERE prod_list = ANY (10, 20) AND category_id
= 1;
2. SELECT prod_name, prod_list FROM products WHERE prod_list IN (10, 20) AND category_id =
1;
3. SELECT prod_name, prod_list FROM products WHERE prod_list = ALL (10, 20) AND category_id
= 1;
Which queries generate the same output?
A)2 and 3
B)1 and 2
C)1 and 3
D)1, 2, and 3
Answer:B 解析：本题 1 和 3 都执行错误，所以也算是相同的输出。单行比较操作符用在
了 多行子查询上

Q198.Choose two. Which two are true?
A)LAST_DAY returns the date of the last day of the month for the date argument passed to the
function.
B)CEIL returns the largest integer less than or equal to a specified number.
C)LAST_DAY returns the date of the last day of the current month only.
D)LAST_DAY returns the date of the last day of the previous month only.
E)ADD_MONTHS adds a number of calendar months to a date.
F)CEIL requires an argument which is a numeric data type.
Answer:AE
解析：本题考的是函数的功能，类似的题目比较难，需要了解这些参数的功能
Q199.Choose two. Which two are true about scalar subquery expressions?
A)You must enclose them in parentheses.
B)You cannot correlate them with a table in the parent statement.
C)They can return at most one row.
D)They can return two columns.
E)You can use them as a default value for a column.
Answer:AE
解析：标量子查询是 Oracle 的一个很好的特性，也是 Oracle 的 SQL 实现如何真正与其他
所有实现区分开来的另一个例子。标量子查询允许您嵌入返回标量值的
SQL 语句“"SELECTing a SELECT"”。
因此，如下面所见，不再需要 PL/SQL 函数来发出游标并返回值，您可以直接将其嵌入到
SELECT 语句中。
那么，这会带来什么好处呢？好吧，有很多，从简化光标到减少上下文切换。
https://www.oratechinfo.co.uk/scalar_subqueries.html
Q200.Choose two. Examine the description of the ORDER_ITEMS table:
Name Null? Type
------------------------- --------- ---------------------------

 

ORDER_ID NUMBER(38)
PRODUCT_ID NUMBER(38)
QUANTITY NUMBER(38)
UNIT_PRICE NUMBER(10,2)
Examine this incomplete query:
SELECT DISTINCT quantity * unit_price total_paid FROM order_items ORDER BY ;
Which two can replace so the query completes successfully?
A)product_id
B)quantity, unit_price
C)quantity
D)total_paid
E)quantity * unit_price
Answer:DE
解析：本题是新类型的题目，考的是 distinct 操作的排序表达式，实验发现，如果不加 distinct
关键字，可以用表中的任意列做为排序列；但是如果加了 distinct，则出现排序的列必须是
在 select 子句中出现，如果是表达式，则用表达式排序，比如答案 E。）
Q201.Choose two. Examine the ORDER_ITEMS table:
Name Null? Type
------------------- ----------------- ----------------------------------------
ORDER_ID NOT NULL NUMBER (38)
PRODUCT_ID NOT NULL NUMBER (38)
QUANTITY NOT NULL NUMBER (38)
Which two queries return rows where QUANTITY is a multiple of ten?
A)SELECT * FROM order_items WHERE quantity / 10 = TRUNC (quantity) ;
B)SELECT * FROM order_items WHERE quantity = ROUND (quantity, 1) ;
C)SELECT * FROM order_items WHERE MOD (quantity, 10) = 0;
D)SELECT * FROM order_items WHERE quantity = TRUNC (quantity, -1) ;
E)SELECT * FROM order_items WHERE FLOOR (quantity / 10) =TRUNC (quantity / 10);
Answer:CD
解析：题目求 QUANTITY 是 10 的倍数，那么可以把该列的值除以 10，如果余数为 0 则
为 10 的倍数；第二种办法就是把该列的值截断到左边第一位，如果等于
原数，则说明该数左边第一位为 0，则能够被 10 整除
Q202.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.
Answer:AC
解析：实验证明，帮别人创建索引时，由于该索引属于创建人，所以创建人需要有相应
的空间来存放索引；只要有该表 select 的权限，然后有 select any table 的权
限就可以创建其它用户表的索引了

Q203.Choose the best answer. Examine this statement which executes successfully:
INSERT ALL WHEN SAL > 20000 THEN
INTO special_sal VALUES (EMP_ID, SAL)
ELSE
INTO sal_history VALUES (EMP_ID, HIREDATE, SAL)
INTO mgr_history VALUES (EMP_ID, MGR ,SAL)
SELECT employee_id EMP_ID , hire_date HIREDATE, salary SAL, manager_id MGR
FROM employees WHERE employee_id < 125;
. Which is true?
A)Only if the salary is 20000 or less and the employee id is 125 or higher, insert EMPLOYEE_ID,
MANAGER_ID, and SALARY into the MGR_HISTORY table.
B)Only if the salary is 20000 or less and the employee id is less than 125, insert EMPLOYEE_ID,
MANAGER_ID, and SALARY into the MGR_HISTORY table.
C)Regardless of salary and employee id, insert EMPLOYEE_ID, MANAGER_ID, and SALARY into the
MGR_HISTORY table.
D)Regardless of salary, only if the employee id is less than 125, insert EMPLOYEE_ID,
MANAGER_ID, and SALARY into the MGR_HISTORY table.
Answer:B
解析：注意题目判断是 SAL > 20000，那么小于或者等于 20000 的会被插入到
sal_history 和 mgr_history 表，最后看子查询的条件是 employee_id < 125 的行才能够被查
询出来，所以选择答案 B。这样子的考题费脑。
Q204.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.
Answer:CE
解析：非等连接 oracle 语法支持。 join...using:用于两表有同名字段但数据类型不同，或者
使用多个同名字段中的某一
个做等值连接
join...on :最为灵活，可以指明连接的条件。

 

Q205.Choose two. Which two object privileges can be restricted to a subset of columns in a
table?
A)ALTER
B)UPDATE
C)DELETE
D)INSERT
E)INDEX
Answer:BD
解析：对题目的意思不是理解得很清楚，根据以往的考题选择 BD 两个答案。
Q206.Choose two. Which two are true about the USING clause when joining tables?
A)It can never be used with a natural join.
B)It is used to specify an explicit join condition involving operators.
C)It can never be used with a full outer join. .
D)All column names in a USING clause must be qualified with a table name or table alias.
E)It is used to specify an equijoin of columns that have the same name in both tables.
Answer:AE
解析：内连接的 using 用法，using 只能用在 equiue join ，而且必须列的名字要 相同，类
型可以不同，但是可以隐式转过去

Q207.Choose three. Which three statements are true about Oracle Managed Files (OMF)?
A)If DB_RECOVERY_FILE_DEST is specified but DB_CREATE_ONLINE_LOG_DEST_n is not, the redo
logs and control files are placed in DB_RECOVERY_FILE_DEST by default.
B)If DB_RECOVERY_FILE_DEST is specified, at least two different locations must be specified for
DB_CREATE_ONLINE_LOG_DEST_n.
C)If only DB_CREATE_FILE_DEST is specified, only data files and temp files are Oracle managed.
D)If DB_CREATE_ONLINE_LOG_DEST_1 is specified but DB_CREATE_FILE_DEST is not, new data
files and temp files are stored in DB_CREATE_ONLINE_LOG_DEST_1 by default.
E)If DB_CREATE_FILE_DEST is specified but DB_CREATE_ONLINE_LOG_DEST_n is not, new redo
logs and control files are stored in DB_CREATE_FILE_DEST by default.
F)If only DB_CREATE_ONLINE_LOG_DEST_1 is specified, only redo logs and control files are Oracle
managed.
Answer:AEF
解析：这个考题把 OMF 管理的几个变化都考到了，感觉 082 考试的题目现在都往具体的
细节上发展。上面答案几个测试，AEF 正确。
Q208.Choose two. Which two statements are true about segment types in an Oracle Database?
A)Index segments always have two or more extents.
B)Undo segments are only stored in an undo tablespace.
C)Temporary segments are only stored in a temporary tablespace.
D)Cluster segments may contain data from multiple tables.
E)Table segments always have two or more extents.

 

Answer:CD
解析：这道题很有深度，A 答案给人带来疑惑，因为平时没有关注索引段的分配规则；系
统 undo 段是放在 system 表空间，这个概念非常重要，多个地方会考到，同时 undo 段也
会放在临时表空间。

Q209.Choose three. Which three statements are true about the Oracle Database server during
and immediately after SHUTDOWN TRANSACTIONAL?
A)Uncommitted transactions are allowed to continue to the next ROLLBACK.
B)New connection requests made to the database instance are refused.
C)Instance recovery occurs at the next restart.
D)Instance recovery occurs automatically during the shutdown.
E)Sessions remain connected even if they are not running a transaction.
F)Uncommitted transactions are allowed to continue to the next COMMIT .
Answer:ABF
解析：基于事务的关闭的特点就是会等待事务完成后关库

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.
Answer:ABCG
解析：该题考的是有个体现架构的知识点，答案 F 看起来是对的，但是并不是整个数据字
典都存放在共享池，而是访问过的数据字典会放在共享池。

Q211.Choose two. Examine this command:
ALTER DATABASE MOVE DATAFILE '/u01/sales01.dbf ' TO '/u02/sales02.dbf ' ;
Which two statements are true?
A)Compressed objects in SALES01.DBF will be uncompressed in SALES02.DBF after the move.
B)DML may be performed on tables with one or more extents in this data file during the
execution of this command.
C)Tables with one or more extents in this data file may be queried during the execution of this
command.
D)The "TO" clause containing the new file name must be specified even if Oracle Managed Files
(OMF) is used.
E)It overwrites any existing file with the name SALES02.DBF in /u02 by default.
Answer:BC
解析：从 12c 版本开始，就支持在线移动数据库，该题是一种变题，答案换了一种形式。

 

D 答案经过测试是错的，只要使用 OMF 方式，就不需要指定数据文件路径。
Q212.Choose the best answer. Your database instance is started with an SPFILE. A PFILE is also
available. You execute this command:
ALTER SYSTEM SET DB_CACHE_SIZE=100K;
Where is the value changed?
A)in the SPFILE and PFILE
B)in the SPFILE and in memory
C)only in memory
D)only in the SPFILE
E)in the SPFILE, PFILE, and memory
Answer:B
解析：该题属于最基本的基础题。

Q213.Choose two. Which two Oracle database space management features require the use of
locally managed tablespaces?
A)Automatic data file extension (AUTOEXTEND)
B)Online segment shrink
C)Free space management with bitmaps
D)Oracle Managed Files (OMF)
E)Server-generated tablespace space alerts
Answer:BC
解析：如果需要在线 shrink 空间，则需要本地管理的表空间，这是经典的答案，本地管理 的
表空间使用位图

Q214.Choose the best answer. Which compression method is recommended for Direct-Path
Insert operations?
A)COLUMN STORE COMPRESS ADVANCED
B)ROW STORE COMPRESS BASIC
C)COLUMN STORE COMPRESS BASIC
D)ROW STORE COMPRESS ADVANCED
Answer:D
解析：各种压缩技术优缺点：
基本表压缩适合 direct path 插入和受限数据类型 sql 高级行压缩支持所有的数据类型和
所有 sql 混合列压缩适合不频繁的 update 归档压缩仅适合 direct path inserted，传统的插
入和更新也是支持的，不过得使用 ADO
（automatic data optimization)策略移动行达到要求的混合列压缩级别
高级行压缩是理想压缩方式，其他级别都是冷门压缩）
（1） 可以将 insert 数据跳过 buffer_cahce，省掉了 buffer block 的格式化与 DBWR 操
作，直接从 PGA 写入磁盘
（2） 不检查表中现有的 block 是否有空闲空间，直接在表的高水位线（HWM）以上插入
（3） 如果在数据库处于非归档模式下，或者是数据就处于归档模式，表级处于 nologging
状态下，只有少量的空间信息 redo 写入、不写入数据 undo（因为要回滚时可以直接回退

 

到高水线即可，而不需要针对 insert 生成 delete 的回滚记录），所以在特定的情况下，直
接路径（direct-path）的 insert 方式，在性能上远远快于常规的串行插入方式。
语法：
INSERT /*+ APPEND */ INTO new_object SELECT * FROM dba_objects;
Q215.Choose three. Which three statements are true about the naming methods and their
features supported by Oracle database used to resolve connection information?
A)Directory Naming can be used if Connect-Time Failover is required.
B)A client can connect to an Oracle database instance even if no client side network admin has
been configured.
C)Directory Naming requires setting the TNS_ADMIN environment variable on the client side.
D)Local Naming requires setting the TNS_ADMIN environment variable on the client side.
E)Easy Connect supports TCP/IP and SSL.
F)Local naming can be used if Connect-Time Failover is required.
Answer:BDF
解析：TNS_ADMIN 告诉 sqlplus 在哪里找到 tnsnames.ora 文件
Q216.Choose three. Which three statements are true about using SQL* Plus?
A)It can run Recovery Manager (RMAN) commands.
B)It can run scripts passed to it by a shell script.
C)It can run scripts entered at the SQL prompt.
D)It has its own commands that are separate from any SQL statements.
E)It has both command-line and graphical user interfaces (GUI).
F)It must be downloaded from the Oracle Technology Network (OTN).
Answer:BCD
解析：sqlplus 下面有自己的命令，区别于 sql 命令。
Q217.Choose three. Which three activities are recorded in the database alert log?
A)session logins and logouts
B)deadlock errors
C)block corruption errors
D)non-default database parameters
E)Data Definition Language (DDL) statements
Answer:BCD
解析：告警日志记录数据类型的考题出现很多，主要记录数据库结构的变化，块损坏，
著名的 600 错误，还有死锁会写入告警日志
Q218.Choose two. You must create a tablespace of non-standard block size in a new file system
and plan to use this command:
CREATE TABLESPACE ns_tbs DATAFILE '/u02/oracle/data/nstbs_f01. dbf ' SIZE 100G BLOCKSIZE
32K;
The standard block size is 8k but other non-standard block sizes will also be used.
Which two are requirements for this command to succeed?

 

A)DB_32K_CACHE_SIZE must be set to a value that can be accommodated in the SGA.
B)DB_32K_CACHE_SIZE must be less than DB_CACHE_SIZE.
C)DB_32K_CACHE_SIZE should be set to a value greater than DB_CACHE_SIZE.
D)The operating system must use a 32k block si
E)The /u02 file system must have at least 100g space for the datafile.
F)DB_CACHE_SIZE must be set to a size that is smaller than DB_32K_CACHE_SIZE.
Answer:AE
解析：非标准块表空间创建之前首先要分配内存空间。

Q219.Choose three. Which three statements are true about connection strings and service
names used to connect to an Oracle database instance?
A)A connection string must include the SID of a database instance.
B)A single connection string can refer to multiple database instances.
C)A connection string including a service name must be defined in the tnsnames.ora file.
D)A service name is created by a listener.
E)Different connection strings in the same tnsnames.ora file can contain the same service name,
host and port parameters.
F)A single database instance can support connections for multiple service names.
Answer:CEF
解析：该题属于体现架构类型，有配置监听和 tns 经验的同学应该都能够判断。D 答案好
像是对的，其实 service 名字可以自定义，用静态注册。
Q220.Choose two. Examine this command:
SQL> ALTER TABLE ORDERS SHRINK SPACE COMPACT
Which two statements are true?
A)The high-water mark (HWM) of ORDERS is adjusted.
B)Only queries are allowed on ORDERS while the SHRINK is executing.
C)Dependent indexes become UNUSABLE.
D)The SHRINK operation causes rows to be moved to empty space starting from the beginning of
the ORDERS segment.
E)Queries and DML statements are allowed on ORDERS while the SHRINK is executing.
F)The SHRINK operation causes rows to be moved to empty space starting toward the end of the
ORDERS segment.
Answer:EF
解析：如果在释放表碎片的释放不影响当前的操作，那么应该在 shrink 的时候加上 compact
选项，但是高水位不会发生变化。等业务空闲的时候再 DEALLOCATE UNUSE，所以 shrink
space 可以分为两部分进行操作。移动数据的时候应该是把数据从末尾移动到段空间前面的
区，这样子才能够降低高水位。
COMPACT 子句允许将收缩段操作分为两个阶段。指定 COMPACT 时，Oracle 数据库会对段
空间进行碎片整理并压缩表行，但会将高水位线的重置和空间的释放推迟到将来某个时间。
如果长时间运行的查询可能跨越操作并尝试从已回收的块中读取，则此选项非常有用。碎片
整理和压缩结果保存到磁盘上，因此在第二阶段不必重新进行数据移动。您可以在非高峰时
段重新发出 SHRINK SPACE 子句而不使用 COMPACT 子句来完成第二阶段。)
CASCADE 子句将段收缩操作扩展到对象的所有从属段。例如，如果在收缩表段时指定

 

CASCADE，则表的所有索引也将收缩。（不需要指定 CASCADE 来缩小分区表的分区。）要
查看给定对象的依赖段列表，可以运行 DBMS_SPACE 包的
Q221.Choose two. Which two statements are true about Database Instances and Real Application
Clusters (RAC)?
A)A RAC database can have instances on separate servers.
B)A RAC database must have two or more instances.
C)A RAC database must have three or more instances.
D)A RAC database can have one instance.
E)Two RAC databases can share their instances.
Answer:AD
解析：RAC 数据库可以有一个或者多个实例；不同的实例运行在不同的服务器上
Q222.Choose three. Which three statements are true about Deferred Segment Creation in Oracle
databases?
A)It is supported for Index Organized Tables (IOTS) contained in locally managed tablespaces.
B)It is supported for sys-owned tables contained in locally managed tablespaces.
C)Sessions may dynamically switch back and forth from DEFERRED to IMMEDIATE segment
creation.
D)It is the default behavior for tables and indexes.
E)Indexes inherit the DEFERRED or IMMEDIATE segment creation attribute from their parent
table.
Answer:ACE
解 析 ： 经 过 实 验 ， A 是 对 的 ， C 答 案 可 以 通 过 alter session 命 令 改 变
deferred_segment_creation 参数；E 答案平时很少有人会注意那么细，经过实验，发现确实
是继承父表的延迟特性。
SQL> create table tt1 (id number) SEGMENT CREATION DEFERRED;
SQL> create index tt1_id_ind on tt1 (id);
SQL> select SEGMENT_NAME,BYTES,SEGMENT_TYPE from user_segments; 没有看到分配空间
给表和索引段。

Q223.Choose three. Which three statements are true about the Automatic Diagnostic Repository
(ADR)?
A)It is a file-based repository held outside any database.
B)It is held inside an Oracle database schema.
C)The ADR base is specified in the DIAGNOSTIC_DEST database parameter.
D)It is only used for Oracle Database diagnostic information.
E)It can be used for problem diagnosis of a database when that database's instance is down.
Answer:ACE 解析：ADR 的数据是存放在数据库外面的，无需数据库启动就能够诊断；C 答
案是经典答案

Q224.Choose two. Which two statements are true about User Authentication in an Oracle
Database?

 

A)Password File authentication is supported for any type of database user.
B)REMOTE_LOGIN_PASSWORDFILE must be set to exclusive to permit password changes for
system-privileged administrative users.
C)Operating System authentication may be used for system-privileged administrative users.
D)Password authentication must be used for systemprivileged administrative users.
E)Password File authentication must be used for system-privileged administrative users.
Answer:BC
解析：密码文件就是给那些有特权用户使用的，但是通过操作系统验证方式也支持。

Q225.Choose the best answer. Examine the description of the SALES1 table:
Name Null Type
------------------- ---------- -----------------------------------------
SALES_ID
STORE_ID
ITEMS_ID
QUANTITY
SALES_DATE
SALES2 is a table with the same description as SALES1.
Some sales data is contained erroneously in both tables.
You must display rows from SALES1 and SALES2 and wish to see the duplicates too.
Which set operator generates the required output?
A)SUBTRACT
B)UNION ALL
C)INTERSECT
D)UNION
E)MINUS
Answer:B
解析：能够看到重复的行，选择答案 B
Q226.Choose three. Which three resource limits can be configured using Oracle profiles?
A)limiting connect time for user sessions
B)limiting CPU used by a user
C)limiting idle time for user sessions only when blocking other sessions
D)limiting idle time for user sessions only when not blocking other sessions
E)limiting idle time for user sessions
F)limiting CPU used by a session
Answer:AEF
解析：这是有关 profile 的比较简单的问法

 

Q227.Choose two. Which two statements are true about unexpired undo?
A)It is required when a transaction issues a ROLLBACK statement.
B)It is the first type of UNDO overwritten when trying to avoid out-of-space errors.
C)It may be used to support flashback queries.
D)It is retained if possible for the time specified in UNDO_ RETENTION.
E)It consists of UNDO only for uncommitted transactions.
Answer:CD
解析：该题的重点是要区分活动的和非活动的 undo，unexpired 是属于非活动的一种。
Q228.Choose three. Which three are benefits of using temp UNDO when performing DML on
global temporary tables?
A)It reduces I/Os to the SYSTEM tablespace.
B)It reduces the amount of redo generated.
C)It reduces the amount of UNDO stored in the UNDO tablespace.
D)It permits DML on Global temporary tables even if the database is opened read only.
E)It reduces I/Os to the SYSAUX tablespace.
Answer:BCD
解析：答案 BC 是容易判断的，但是答案 D 描述的不太完整，这个操作只能允许在物理
standby 数据库上操作，如果是主库只读打开，是不允许进行 dml 操作，这个可能也为备
库进行 dml 测试提供一个方便。

Q229.Choose two. You start your database instance in NOMOUNT state.
Which two actions are performed?
A)The control files are opened.
B)Memory is allocated for the SGA.
C)YS can access the database.
D)All required background processes are started.
E)The consistency of the database is checked.
Answer:BD
Q230.Choose three. Which three statements are true about UNDO and REDO?
A)UNDO is used for some flashback operations.
B)Both REDO and UNDO can be multiplexed.
C)UNDO is used for read consistency.
D)REDO is used for ROLLBACK.
E)REDO is used for read consistency.
F)REDO is used for instance recovery.
Answer:ACF
解析：有关 undo 和 redo 的考点概念比较简单。
Q231.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.
Answer:ABC
解析：经过实验，resumable_timeout 参数不能在会话级修改，所以针对的是所有的会话。
Q233.Choose three. Which three statements are true about Private Temporary Tables (PTTs) in an
Oracle Database?
A)Concurrent sessions may have PTTS with the same name and the same column definitions.
B)It is possible to preserve the private table definition but remove the rows when a session with a
PTT issues a COMMIT.
C)Creating a PTT is DDL and commits any active transaction for the session.
D)Concurrent sessions may have PTTS with the same name but different column definitions.
E)Concurrent sessions may have PTTS with different names but same column definitions.
Answer:ABC
解析：18C 中出现了一种新的临时表，称为私有临时表，它们是在事务或会话结束时丢弃的
临时数据库对象。私有临时表存储在内存中，每个临时表只对创建它的会话可见。
B 答案是指提交事务的时候数据可能删除，但是表定义还在，因为是各个会话私有的，所以
可以取相同的表名和机构

Q233.Choose the best answer. Why would you use the TNSPING hr command?
A)to verify if a requested service is available
B)to determine if the listener for the HR net service name can be reached
C)to verify if the HR database instance is up
D)to check and list multiple ORACLE_HOME locations on the server
E)to validate the network connectivity between a client and the database instance
Answer:B
解析：AB 两个答案都对，但是 B 答案描述更加完整
Q234.Choose two. Which two statements are true about the UNLIMITED TABLESPACE system
privilege and space quotas?
A)It is overridden by a space quota specified for the user.
B)It allows a user to have unlimited space in any tablespace in the database.
C)By default, users have no quota on their default permanent tablespace.
D)It allows a user to have unlimited space only in their default permanent tablespace.
E)It allows a role to have unlimited space in any tablespace in the database.
Answer:BE
解析：unlimited tablespace 的权限覆盖分配的 quota，可以把给权限分配给用户和角色。

 

Q235.Choose three. Which three files are used by conventional path SQL*Loader when the TABLE
option is not specified?
A)password files
B)dump files
C)input files
D)control files
E)bad files
Answer:CDE
解析：传统 sqlldr 需要控制文件、数据源、bad 文件。
Q236.Choose four. Which four activities can be performed using SQL Developer?
A)It must be configured to allow SQLcl to be used.
B)It can be used to develop server-side java programs.
C)It can be used to back up and recover databases using RMAN.
D)It can be used to administer database security.
E)It can run SQL statements and SQL scripts.
F)It can be used to debug PL/SQL programs.
G)It can be used to develop client-side java programs.
Answer:ADEF
解析：该工具不是用来 java 开发的，带有一些管理功能，比如安全和数据库导出迁移以及
数据库复制等等，但是不能用 rman 进行备份和恢复。考外围的应用程序，有点超出范围。
Q237.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.
Answer:BCD
解析：EMCC 里面有 OMS 接口，可以下载补丁等等；可以管理中间件和数据库系统
Q238.Choose three. Which three are types of segments in an Oracle Database?
A)tables
B)stored procedures
C)sequences
D)views
E)undo
F)clusters
Answer:AEF
解析：该题以前有类似的，但是当时有更多的选择，没有选择 undo 段，现在只有三种
类型的段，所以选择 undo 段。
Q239.Choose two. Which two statements are true regarding indexes?
A)A table belonging to one user cannot have an index that belongs to a different user.

 

B)When a table is dropped and is moved to the RECYCLE BIN, all indexes built on that table are
permanently dropped.
C)An update to a table can result in updates to any or all of the table's indexes.
D)An update to a table can result in no updates to any of the table's indexes.
E)A UNIQUE index can be altered to be non-unique.
Answer:CD
解析：有关索引的考题很多，曾经 12C 的 071 题库中是 6 选 3
Q240.Choose two. Which two are true?
A)INSTR finds the offset within a character string, starting from position 0.
B)FLOOR returns the largest integer less than or equal to a specified number.
C)CONCAT joins two or more character strings together.
D)INSTR finds the offset within a string of a single character only.
E)FLOOR returns the largest positive integer less than or equal to a specified number.
F)CONCAT joins two character strings together.
Answer:BF
注意：：floor 是向下取整，取接近指定数字的最大值，以前的答案是返回最小的整数，答
案有变化。
Q241.An Oracle Database session has an uncommitted transaction in progress which updated
5000 rows in a table.
In which three situations does the transaction complete thereby committing the updates?
A)when a DBA issues a successful SHUTDOWN IMMEDIATE statement and the user the issues a
COMMIT.
B)when a CREATE TABLE AS SELECT statement is issued in the same session but fails with a systax
error.
C)when a CREATE INDEX statement is executed successfully in the same session.
D)when a COMMIT statement is issued by the same user from another session in the same
database instance.
E)when a DBA issues a successful SHUTDOWN TRANSACTIONAL statement and the user then
issues a COMMIT.
F)when the session logs out successfully.
Answer:CEF
Q242.Which tow statements are true about the ORDER BY clause?
A)Column aliases can be used in the ORDER BY clause.
B)In a character sort,the values are case-sensitive.
C)Only columns that are specified in the SELECT list can be used in the ORDER BY clasuse.
D)NULLS are not included in the sort operation
E)Numeric values are displayed in descending order if they have decimal positions.
Answer:AB
Q243.Which three instance situations are possible with the Oracle Database server without
multi- tenant? (Choose three.)
A. two or more instances on separate servers all associated with one database

 

B. one instance on one server associated with one database
C. one instance on one server associated with two or more databases on the same server
D. one instance on one server not associated with any database
E. one instance on one server associated with two or more databases on separate servers
Answer: ABD
Q244.Which two statements are true about the ORDER BY clause when used with a SQL
statement containing a SET operator such as UNION? (Choose two.)
A. Column positions must be used in the ORDER BY clause
B. Only column names from the first SELECT statement in the compound query are recognized
C. The first column in the first SELECT of the compound query with the UNION operator is used by
default to sort output in the absence of an ORDER BY clause
D. Each SELECT statement in the compound query must have its own ORDER BY clause
E. Each SELECT statement in the compound query can have its own ORDER BY clause
Answer: BC
解析：
（Using the ORDER BY Clause in Set Operations - The ORDER BY clause can appear only once at
the end of the compound query.
- Component queries cannot have individual ORDER BY clauses.
- The ORDER BY clause recognizes only the columns of the first SELECT query.
- By default, the first column of the first SELECT query is used to sort the output in an
ascending order）
Q245.Evaluate the following SQL statement: SQL>
SELECT cust_id, cust_last_name "Last name" FROM customers WHERE country_id = 10
UNION
SELECT cust_id CUST_NO, cust_last_name FROM customers WHERE country_id = 30 ;
Identify three ORDER BY clauses either one of which can complete the query.
A. ORDER BY "Last name"
B. ORDER BY 2, cust_id
C. ORDER BY CUST_NO
D. ORDER BY 2, 1
E. ORDER BY "CUST_NO"
Answer: ABD

Q246.Which four account management capabilities can be configured using Oracle profiles?
(Choose four.)
A. the number of hours for which an account is locked after the configured number of login
attempts has been reached
B. the number of days for which an account may be inactive before it is locked
C. the maximum amount of CPU time allowed for a user’s sessions before their account is
locked
D. the ability to prevent a password from ever being reused

 

E. the number of password changes required within a period of time before a password can be
reused
F. the number of days for which an account is locked after the configured number of login
attempts has been reached
G. the maximum number of sessions permitted for a user before the account is locked
Answer: BDEF
解析：使用 profile 可以实现密码安全的管理，以及其它的资源管理，这是个很重要的考点
方向。

Q247. Which statement is true about profiles?（ choose one）
A) Resource limits specified in a profile assigned to a user are always enabled.
B) A profile can be assigned only to one user.
C) A user can exist without any profile.
D) Password management using profiles is always enabled.
Answer:D
解析：profile 里面的密码管理总是 enable，而资源限制要把 resource_limit 参数变成 true。
Q248.choose one Which statement is true about the DEFAULT profile?
A) It can be dropped and recreated.
B) It must be explicitly assigned to a user.
C) The values assigned to the resource limits and password parameters in the default profile can
be altered.
D) A different DEFAULT profile can be created for each user in a database.
Answer:C
解析：默认的 profile 是不允许删除的，但是可以修改里面的设置
Q249.Choose three.
Examine these statements:
ALTER SYSTEM SET RESOURCE_LIMIT=FALSE SCOPE=BOTH;
System altered.
CREATE PROFILE dev_users LIMIT SESSIONS_PER_USER 3 IDLE_TIME 10 FAILED LOGIN ATTEMPTS
3 PASSWORD_VERIFY_FUNCTION ora12c_verify_function;
Profile altered.
ALTER USER hr PROFILE dev_users;
User altered.
Which three are true about the effects of executing these statements?
A) User HR is permitted to create more than three concurrent sessions.
B) The password complexity will not enforced on the HR account.
C) Password complexity checking is enforced for user HR.
D) The HR account will get locked by Oracle after ten consecutive failed login attempts.
E) The HR account will be locked after three consecutive failed login attempts.
F) User HR sessions will terminate after ten minutes of continuous inactivity.

 

Answer:ACE
解析：有个 profile 的考点一直是 ocp 考试中的内容，概念简单明了。但是题目中把
RESOURCE_LIMIT 设置成 false，意味着资源管理不生效，那么 SESSIONS_PER_USER 和
IDLE_TIME 参数属于 kenerl 类型，所以设置无法生效，该题带有一定的隐秘性。）
1* select PROFILE,RESOURCE_NAME,RESOURCE_TYPE,limit from
dba_profiles order by PROFILE;
PROFILE RESOURCE_NAME RESOURCE_T LIMIT

Q250.You are administering a database and you receive a requirement to apply the following
restrictions:
1. A connection must be terminated after four unsuccessful login attempts by user.
2. A user should not be able to create more than four simultaneous sessions.
3. User session must be terminated after 15 minutes of inactivity.
4. Users must be prompted to change their passwords every 15 days.
How would you accomplish these requirements?
A. by granting a secure application role to the users
B. by creating and assigning a profile to the users and setting the REMOTE_OS_AUTHENT
parameter to FALSE
C. By creating and assigning a profile to the users and setting the FAILED_LOGIN_ATTEMPTS
parameter to 4
D. By Implementing Fine-Grained Auditing (FGA) REMOTE_LOGIN_PASSWORD_FILE parameter to
NONE.
E. By implementing the database resource Manager plan and setting the
SEC_MAX_FAILED_LOGIN_ATTEMPTS parameters to 4.
Answer: C
解析：上面的要求，通过 profile 能够实现，注意资源管理器的功能
Q251.In your database, you want to ensure that idle sessions that are blocking active are
automatically terminated after a specified period of time. How would you accomplish this?
A. Setting a metric threshold
B. Implementing Database Resource Manager
C. Enabling resumable timeout for user sessions
D. Decreasing the value of the IDLE_TIME resource limit in the default profile
Answer: B
解析：D 答案是不对的，IDLE_TIME 参数是指定超过了该时间不管有没有阻塞别人的会话请
求会话都会被 kill，但是题目的前提是是阻塞了别人的会话，所有只有通过资源管理器才能
够实现这一要求。

 

Q252.Which three statements are true about single-row functions? (Choose three.)
A. They can be used only in the WHERE clause of a SELECT statement
B. The argument can be a column name, variable, literal or an expression
C. The data type returned can be different from the data type of the argument
D. They can be nested to any level
E. They can accept only one argument
F. They return a single result row per table
Answer: BCD
解析：Oracle 的转换函数也属于单行函数，所以 C 答案是对的；它们可以接受列名、表达
式、变量名或用户提供的常量作为参数，所以 B 答案准确。
Q253.Which three statements are true regarding single-row functions? (Choose three.)
A. The data type returned, can be different from the data type of the argument that is
referenced.
B. They can return multiple values of more than one data type.
C. They can accept only one argument.
D. They can be nested up to only two levels.
E. They can be used in SELECT, WHERE, and ORDER BY clauses.
F. They can accept column names, expressions, variable names, or a user-supplied constants as
arguments.
Answer: AEF
解析：Oracle 的转换函数也属于单行函数，所以 A 答案是对的；E 答案对，可以在这些子
句使用函数；它们可以接受列名、表达式、变量名或用户提供的常量作为参数，所以 F 答
案准确。

Q254.Which two statements are true regarding single row functions? (Choose two.)
A. MOD : returns the quotient of a division.
B. TRUNC : can be used with NUMBER and DATE values.
C. CONCAT : can be used to combine any number of values.
D. SYSDATE : returns the database server current date and time.
E. INSTR : can be used to find only the first occurrence of a character in a string.
F. TRIM : can be used to remove all the occurrences of a character from a string.
Answer: BD
解析：了解各个函数的功能
A：返回除法的商
C：可用于组合任意数量的值
E: 只能用于查找字符串中第一个出现的字符
F：可用于从字符串中删除所有出现的字符
Q255.You want to use table compression suitable for OLTP that will:
1. Compress rows for all DML statements on that table
2. Minimize the overheads associated with compression

 

Which compression option is best suited for this?
A. COLUMN STORE COMPRESS FOR QUERY LOW
B. ROW STORE COMPRESS BASIC
C. COLUMN STORE COMPRESS FOR ARCHIVE LOW
D. COLUMN STORE COMPRESS FOR ARCHIVE HIGH
E. ROW STORE COMPRESS ADVANCED
Answer: E
解析：根据题目的要求，要满足 OLTP 的应用压缩，性价比最好的就是答案 E 的方式。
Q256.Which two statements are true about space-saving features in an Oracle Database?
(Choose two.)
A)Private Temporary Tables (PTTS) store metadata in memory only
B)An index created with the UNUSABLE attribute has no segment
C)If they exist for a session, Private Temporary Tables (PTTs) are always dropped at the next
COMMIT OR ROLLBACK statement
D)An index that is altered to be UNUSABLE will retain its segment
E)A table that is truncated will always have its segment removed
Answer: AB
解析：18C 中出现了一种新的临时表，称为私有临时表，它们是在事务或会话结束时丢弃的
临时数据库对象。私有临时表存储在内存中，每个临时表只对创建它的会话可见。
当索引不可用(unusable)时, Oracle 内部会把该索引元数据（即创建语句包含的基本信息) 与
真实物理数据之间的对应关系撕裂，相关数据块可被重用（经过实验，ORACLE 会把索引段
删除，但是 sys 用户索引除外）。索引段是否已创建 (dba_indexes.segment_created)这一属
性，会变为 no(正常情况下，应为 yes). unusable 索引，想要被重新使用，只有一种方法:
rebuild.
当然还可以先 drop 再 create.

Q257.Which two statements are true about the PMON background process? (Choose two.)
A)It registers database services with all local and remote listeners known to the database
instance
B)It frees resources held by abnormally terminated processes
C)It records checkpoint information in the control file
D)It frees unused temporary segments
E)It kills sessions that exceed idle time
Answer: BE
解析：从 11g 开始，实例注册到监听的事情就不是由 pmon 负责了，B 答案是很肯定的，
E 答案没有明确的文档描述
Q258.The EMPLOYEES table contains columns EMP_ID of data type NUMBER and HIRE_DATE of
data type DATE. You want to display the date of the first Monday after the completion of six
months since hiring. The NLS_TERRITORY parameter is set to AMERICA in the session and,
therefore, Sunday is the first day on the wee. Which query can be used?

 

A)SELECT emp_id, ADD_MONTHS(hire_date, 6), NEXT_DAY(‘MONDAY’) FROM employees;
B)SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), ‘MONDAY’) FROM employees;
C)SELECT emp_id, NEXT_DAY(MONTHS_BETWEEN(hire_date, SYSDATE), 6) FROM employees;
D)SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 1) FROM employees;
Answer: B
解析：这道题考的是 NEXT_DAY 函数的使用方式，属于通类型（跟数据库版本没有关系）
Q259.The ORCL database has RESUMABLE__TIMEOUT = 7200 and
DEFERRED_SEGMENT_CREATION = FALSE User U1 has a 1 MB quota in tablespace DATA. U1
executes this command:
SQL> CREATE TABLE t1 AS (SELECT object_name, sharing, created FROM dba_objects);
U1 complains that the command is taking too long to execute. In the alert log, the database
administrator (DBA) finds this: 2017-03-06T12:15:17.183438+05:30 statement in resumable
session ‘User U1(136), Session 1, Instance 1’ was suspended due to ORA- 01536: space quota
exceeded for tablespace ‘DATA’
Which are three actions any one of which the DBA could take to resume the session? (Choose
three.)
A)Add a data file to DATA
B)Drop other U1 objects in DATA
C)Increase U1’s quota sufficiently in DATA
D)Set DEFERRED_SEGMENT_CREATION to TRUE
E)Grant UNLIMITED TABLESPACE to U1
F)Set AUTOEXTEND ON for data files in DATA
Answer: BCE
解析：命令是基于子查询创建了一张表，所以答案 D 是没有作用的，不是涉及到是否延迟
创建段的问题，因为有 insert 操作了；根据题目报错的信息，应该是表空间的配额不够了，
那么解决方案就是分配更多的空间给 u1 用户
Q260.In which three situations does a new transaction always start? (Choose three.)
A)when issuing a SELECT FOR UPDATE statement after a CREATE TABLE AS SELECT statement was
issued in the same session
B)when issuing a TRUNCATE statement after a SELECT statement was issued in the same session
C)when issuing a CREATE TABLE statement after a SELECT statement was issued in the same
session
D)when issuing the first Data Manipulation Language (DML) statement after a COMMIT OR
ROLLBACK statement was issued in the same session
E)when issuing a CREATE INDEX statement after a CREATE TABLE statement completed
successfully in the same session
F)when issuing a DML statement after a DML statement failed in the same session
Answer: ADF
解析：题目说的是新事务开始，那就意味着前面的操作需要提交，在进行新的 dml 操作之
前。
Q261.Examine the description of the SALES1 table: SALES2 is a table with the same description as

 

SALES1. Some sales data is duplicated in both tables.
You want to display the rows from the SALES1 table which are not present in the SALES2 table.
Which set operator generates the required output?
A)INTERSECT
B)UNION ALL
C)UNION
D)SUBTRACT
E)MINUS
Answer: E
解析：题目要求取两表间的差异，所以用 MINUS 操作
Q262.Your database instance is started with a PFILE. Examine these parameters: You want to
increase the size of the buffer cache. Free memory is available to increase the size of the buffer
cache.
You execute the command:
SQL> ALTER SYSTEM SET DB_CACHE_SIZE=1024M;
What is the outcome?
A)he value is changed only in the PFILE and takes effect at the next instance startup
B)The value is changed for the current instance and in the PFILE
C)It fails because the SCOPE clause is missing
D)Change is applied to the current instance, but does not persist after instance restart
Answer: D
解析：这道题是传统的考点，考的是内存修改的生效范围，如果是 pfile 文件，则不会动态
修改，所以下一次启动还是原来的值。

Q263.Which three statements are true about pfiles, spfiles or both?
A)All spfile parameters can be modified successfully with the scope=both clause.
B)A spfile can be created by an idle instance.
C)spfiles and pfiles may both be edited with an O/S editing utility.
D)Some spfile parameters can be modified successfully with the scope=memory clause.
E)A pfile can be created by an idle instance.
F)All spfile parameters can be modified successfully with the scope=memory clause.
Answer:BDE
解析：spfile 和 pfile 可以互相转换，而且不需要实例启动，参数分为静态参数和动态参数，
只有动态参数才能够马上修改生效。

Q264.Which four statements are true about database instance behavior?
A)Redo log files can be renamed in mount state.
B)Datafiles can be renamed in mount state.
C)All dynamic performance views (v$ views) return data when queried from a session connected
to an Instance in nomount state.
D)An spfile can be updated when connected to an idle instance.
E)The consistency of redo logs and data files is checked when mounting the database.

 

F)An idle instance is created when a startup nomount is successful.
Answer:ABDF
解析：答案 AB 是没有问题的，但是这道题的 an idle instance 指的是什么不明确，如果一
个实例没有启动，那么我们连接的 时候会告诉我们：Connected to an idle instance.，所以这
里不清楚实例启动后是不是算是 idle instance，但是从另外一个角度，如果只是启动实例，
没有跟数据库扯上关系，那么也算是空闲实例。

Q265.Your database instance is started by using a server parameter file (SPFILE).
You execute the following command to change the value of the LOG_BUFFER initialization
parameter: ALTER SYSTEM SET LOG_BUFFER=32M;
What is the outcome of this command?
A)The parameter value is changed and it comes into effect as soon as space becomes available in
the SGA.
B)The parameter value is changed and it comes into effect at the next instance startup.
C)It returns an error because SCOPE should be set to MEMORY.
D)It returns an error because the value of this parameter cannot be changed dynamically.
Answer:D
解析：本考点考的是哪些参数是静态参数。

Q266.A PFILE exists for your database. You want to increase the value of the PROCESSES
parameter from 300 to 400 and make it persistent for your database instance.
You plan to use the ALTER SYSTEM command. Which two methods can be used?
A)Edit the PFILE manually, re-create the SPFILE from the edited PFILE, and restart the database
instance by using the SPFILE.
B)Use the PFILE to STARTUP MOUNT the database instance and then execute the ALTER SYSTEM
SET PROCESSES=400 command.
C)Create an SPFILE, start the database instance by using the SPFILE, and then execute the ALTER
SYSTEM SET PROCESSES=400 SCOPE=SPFILE command.
D)Create an SPFILE, STARTUP MOUNT the database instance by using the SPFILE, and then
execute the ALTER SYSTEM SET PROCESSES=400 command.
E)Create an SPFILE, start the database instance by using the SPFILE, and then execute the ALTER
SYSTEM SET PROCESSES=400 SCOPE=DEFERRED command.
F)Start the database instance by using the PFILE, and then execute the ALTER SYSTEM SET
PROCESSES=400 SCOPE=BOTH command.
Answer:AC
解析：修改静态参数的值，如果是 pfile 则修改 pfile 文件，然后基于 pfile 创建一个 spfile，
最后 以 spfile 启动即可；如果是 spfile 则修改时加上 scope＝spfile，重启实例即可。
这道题如果能够理解，说明对参数修改的方式就掌握了。

Q267.Choose the best answer. Examine this scenario: connect / as sysdba Connected.
SHUTDOWN TRANSACTIONAL Database closed. Database dismounted. ORACLE instance shut
down. STARTUP Now examine these steps:
1. Oracle instance is started.

 

2. The control file is read.
3. Instance recovery is started.
4. Datafiles and redo log files are opened.
5. The spfile or pfile is read.
6. The database is available for use.
7. The database is mounted.
8. Instance recovery is completed.
Identify the correct steps and the order they will be executed in following this command:
A)5,2,1,7,4,6
B)5,1,2,7,4,3,8,6
C)5,2,1,4,7,6
D)5,1,2,7,4,3,6,8
E)5,1,2,7,4,6
F)5,1,3,2,7,4,8,6
Answer:E
解析：本题考点是数据库正常关闭后的启动顺序，概念比较简单。

Q268.Choose three Examine this command.
STARTUP PFILE= 'D: \oracle12c\oracle\admin\orcl\pfile\pfileinitorcl.ora ORACLE instance started.
Total system Global Area 5117049968 bytes Fixed Size 9038960 bytes Variable Size Database
Buffers Redo Buffers Database mounted. Database opened.
Now examine these statements.
SHOW PARAMETER cursor_sharing;
TYPE VALUE NAME
------------------- --------- ------------
cursor_sharing string EXACT
ALTER SYSTEM SET cursor_sharing=FORCE;
Which three are true about the result of processing the last statement?
A)The parameter is modified in memory and in the parameter file.
B)The modification does not persist across a SHUTDOWN TRANSACTIONAL command.
C)The parameter is modified in memory only.
D)The modification persists across a SHUTDOWN IMMEDIATE command.
E)The modification persists across instance checkpoints.
F)The parameter is modified in the parameter file only.
Answer:BCE
解析：注意启动的时候用的是 pfile，所以这个参数写不到参数文件中，因此重启以后该值 就
恢复到原来的值；答案 E 只是做了给检查点，并没有重启实例，所以该值会一直保留。
Persists 的意思是保持的意思。
Q269.Choose the best answer. All of your database instances use SPFILES.
You executed this command in one of them:
ALTER SYSTEM SET MEMORY_ MAX_ TARGET=1024M;
Which statement is true?

 

A)The command returns an error.
B)The parameter is modified in the parameter file only.
C)The parameter is modified in memory and in the parameter file.
D)The parameter is modified in memory only.
Answer:A
解析:该参数是静态参数，所以必须要加 scope=spfile 选项
Q270.Which three Oracle database space management features will work with both Dictionary
and Locally managed tablespaces? (Choose three.)
A)Oracle Managed Files (OMF)
B)Online table segment shrink
C)Online index segment shrink
D)Automatic data file extension (AUTOEXTEND)
E)Capacity planning growth reports based on historical data in the Automatic Workload
Repository (AWR)
Answer: ADE
解析：这道题的考点比较特殊，一般很少会去关注这些特性，这道题用的是排除法，以为答
案 BC 比较明确，如果需要在线 shrink 空间，则需要本地管理的表空间。
Q271.You execute this command: During the export operation, you detach from the job by using
CTRL+C and then execute this command:
Export> STOP_JOB=immediate Are you sure you wish to stop the job ([yes]/no): yes
Which two statements are true about the job? (Choose two.)
A)You can no longer monitor it
B)You can reattach to it and monitor it
C)It is paused and can be resumed
D)It continues to run in the background
E)It terminates
Answer: BC
解析：经过测试，stop 是暂时停止 job，后面可以继续进行，如果是 kill_job，则是停止 job，
导出任务就无法再继续。

Q272.In one of your databases, you create a user, HR, and then execute this command: GRANT
CREATE SESSION TO hr WITH ADMIN OPTION; Which three actions can HR perform? (Choose
three.)
A)Revoke the CREATE SESSION privilege from other users
B)Revoke the CREATE SESSION privilege from user HR
C)Log in to the database instance
D)Grant the CREATE SESSION privilege with ADMIN OPTION to other users
E)Execute DDL statements in the HR schema
F)Execute DML statements in the HR schema
Answer: ABD
解析：这道题考的是 WITH ADMIN OPTION 的权限，实验证明拥有该权限的可以把其它用户、

 

或者自己、以及 sys 用户的 CREATE SESSION 权限给撤除，没有深入的研究，很难选择正确
答案

Q273.Which two statements are true about the WHERE and HAVING clauses in a SELECT
statement? (Choose two.)
A)Aggregating functions and columns used in HAVING clauses must be specified in the SELECT list
of a query
B)WHERE and HAVING clauses can be used in the same statement only if applied to different
table columns
C)The HAVING clause can be used with aggregating functions in subqueries
D)The WHERE clause can be used to exclude rows before dividing them into groups
E)The WHERE clause can be used to exclude rows after dividing them into groups
Answer: CD
解析：having 子句就是用在条件中有聚组函数；where 子句会在数据输入到聚组函数处理
前先过滤数据，以此减少数据的处理量

Q274.Choose two. Which two statements are true about UNDO and REDO?
A)The generation of REDO generates UNDO.
B)DML modifies Oracle database objects and generates UNDO and REDO.
C)The generation of UNDO generates REDO.
D)DML modifies Oracle database objects and only generates UNDO.
E)DML modifies Oracle database objects and only generates REDO.
Answer:BC
解析：产生 redo 的不一定产生 undo，比如 ddl 操作；但是产生 undo 的肯定有 redo。
Q275.The SCOTT/TIGER user exists in two databases, BOSTON_DB and DALLAS_DB, in two
different locations. Each database has a tnsnames.ora file defining DALLAS_DB as a service name.
Examine this command: CREATE DATABASE LINK dblink1 CONNECT TO scott IDENTIFIED BY tiger
USING ‘dallas_db’;
How do you execute the command so that only SCOTT in BOSTON_DB can access the SCOTT
schema in DALLAS_DB?
A)as SCOTT in DALLAS_DB
B)as SCOTT in BOSTON_DB
C)as SCOTT in BOSTON_DB and SYS in DALLAS_DB
D)as SYS in both the databases
E)as SCOTT in both the databases
Answer: B
解析：题目要求只能 BOSTON_DB 数据库的 scott 用户访问对方数据库，则就要在
BOSTON_DB 数据库上以 scott 用户来创建，这样子其它用户就没有使用 dblink 的权限。
Q276.Which three statements are true about the DESCRIBE command? (Choose three.)
A)It displays the PRIMARY KEY constraint for any column or columns that have that constraint
B)It can be used from SQL Developer

 

C)It displays the NOT NULL constraint for any columns that have that constraint
D)It can be used to display the structure of an existing view
E)It displays all constraints that are defined for each column
F)It can be used only from SQL *Plus
Answer: BCD
解析：desc 命令可以显示表和视图的结构，包含 not null 约束，而且可以在 sql*plus 和
developer 上显示
Q277.Examine the description of the CUSTOMERS table:

For customers whose income level has a value, you want to display the first name and due
amount as 5% of their credit limit.
Customers whose due amount is null should not be displayed.
Which query should be used?
A)SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT FROM customers WHERE
cust_incoms_level IS NOT NULL AND due_amount IS NOT NULL;
B)SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT FROM customers WHERE
cust_income_level != NULL AND cust_credit_level !=NULL;
C)SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT FROM customers WHERE
cust_income_level <> NULL AND due_amount <> NULL;
D)SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT FROM customers WHERE
cust_income_level != NULL AND due_amount !=NULL;
E)SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT FROM customers WHERE
cust_income_level IS NOT NULL AND cust_credit_limit IS NOT NULL;
Answer:AE
解析：题目要求对于收入水平有值的客户，您希望将名字和到期金额显示为其信用额度
的 5%。不应显示到期金额为空的客户。别名不能当作条件列，所以 a 答案错误，但是可
以当作排序列

Q278.Choose two. Which two statements are true about Enterprise Manager Database Express?
A)The same port number can be used for Database Express configurations for databases on
different hosts.
B)It can be used to switch a database into ARCHIVELOGMODE.
C)It can be used to perform database recovery when the database is mounted.
D)The same port number can be used for multiple Database Express configurations for multiple
databases on the same host.
E)It can be used to perform database recovery when the database is opened.
F)It is available only when the database is open.
Answer:AF

 

解析：12c 的 EMDE 就是一个阉割版的 oem，很多功能都没有，但是使用起来很方
便，只能做为简单的监视作用。

Q279.Choose two. Examine the description of the EMPLOYEES table:
Name Null? Type
------------------------ ----------------------- -------------------
EMPLOYEE_ID NOT NULL NUMBER (3)
FIRST_NAME NOT NULL VARCHAR2 (15)
LAST_NAME VARCHAR2 (15)
SALARY NUMBER(6,2)
Which two queries will result in an error?
A)SELECT first_name last_name FROM employees;
B)SELECT first_name, last_name FROM employees;
C)SELECT last_name, 12 * salary AS annual_salary FROM employees WHERE annual_salary >
100000 ORDER BY 12 * salary;
D)SELECT last_name, 12 * salary AS annual_salary FROM employees WHERE annual_salary >
100000 ORDER BY annual_salary;
E)SELECT last_name, 12 * salary AS annual_salary FROM employees WHERE 12 * salary > 100000
ORDER BY annual_salary;
F)SELECT last_name, 12 * salary AS annual_salary FROM employees WHERE 12 * salary > 100000
ORDER BY 12 * salary;
Answer:CD
解析：注意别名不能当作条件列，但是可以用在排序上。

Q280.A user establishes a connection to a database instance by using an Oracle Net connection.
You want to ensure the following:
1.The user account must be locked after five unsuccessful login attempts.
2.Data read per session must be limited for the user.
3.The user cannot have more than three simultaneous sessions.
4. The user must have a maximum of 10 minutes session idle time before being logged off
automatically.
How would you accomplish this?
A. by granting a secure application role to the user
B. by implementing Database Resource Manager
C. by using Oracle Label Security options
D. by assigning a profile to the user
Answer: D
解析：这里要弄清楚 profile 和资源管理器的内容。
Q281.Which three statements are ture about a dedicated server configuration?
A)A dedicated server process can be spawned by the listener when using local clients.
B)A dedicated server process can be spawned without a listener when using local clients.

 

C)The DBA configures the maximum number of dedicated server processes that can share the
same dispatcher process.
D)A dedicated server process may be reused by a new session after the session using that process
terminates.
E)A dedicated server process communicates directly with a client or middle tier process once the
session is established.
F)Each dedicated server process has its own dispatcher process.
Answer: ABF
Q282.Which three statements are true about an SPFILE?
A)It can be used to create a pfile.
B)It can be created by SYS from an idle instance.
C)It contains initialization parameters whose values can be changed using the ALTER SYSTEM
command.
D)It must exist for a database instance to start.
E)It contains only static initialization parameters.
Answer:ACE
Q283.The PRODUCT_INFORMATION table has a UNIT_PRICE column of data type NUMBER(8,2).
Evaluate this SQL statement：
SELECT TO_CHAR(unit_price,’$9,999’) FROM product_information;
Which two statements are true about the output?
A)A row whose UNIT_PRICE column contains the value 10235.99 will be displayed as $1,023.
B)A row whose UNIT_PRICE column contains the value 1023.99 will be displayed as $1,024.
C)A row whose UNIT_PRICE column contains the value 10235.99 will be displayed as #######.
D)A row whose UNIT_PRICE column contains the value 1023.99 will be displayed as $1,023.
E)A row whose UNIT_PRICE column contains the value 10235.99 will be displayed as $1,0236.
Answer:BC
284. A database is configured to use automatic undo management with temporary undo enabled.
An UPDATE is executed on a temporary table. Where is the UNDO stored?
A. in the undo tablespace
B. in the SYSAUX tablespace
C. in the SGA
D. in the PGA
E. in the temporary tablespace
Answer: E
（解析：temporary undo 是 12c 的一个新特性，目的是为了减少对正常 undo 表空间的使
用造成的影响。）

285. Evaluate these commands which execute successfully:

 

Which two statements are true about the ORD_ITEMS table and the ORD_SEQ sequence?
(Choose two.)
A. If sequence ORD_SEQ is dropped then the default value for column ORD_NO will be NULL
for rows inserted into ORD_ITEMS
B. Any user inserting rows into table ORD_ITEMS must have been granted access to sequence
ORD_SEQ
C. Column ORD_NO gets the next number from sequence ORD_SEQ whenever a row is
inserted into ORD_ITEMS and no explicit value is given for ORD_NO
D. Sequence ORD_SEQ cycles back to 1 after every 5000 numbers and can cycle 20 times
E. Sequence ORD_SEQ is guaranteed not to generate duplicate numbers
Answer: BC
解析：定义表时列的默认值是来自于序列，所以 insert 的时候即使没有显式插入该列，也
会自动插入该列的值，如果其它用户想插入该列的值，则需要有访问该序列的权限。

新增加题：
286. Choose the best answer. Examine this schema information:
1. EMPLOYEES.DEPARTMENT_IDhas a foreign key referencing DEPARTMENTS.DEPARTMENT_ID.
2. EMP_VIEW is based on the EMPLOYEES and DEPARTMENTS tables.
3. EMP_VIEW has columns EMPLOYEE_ID, EMPLOYEE_NAME and DEPARTMENT_NAME.
You must add a new column, MANAGER_ID, from the EMPLOYEES table, to the view, showing
each employees manager.
Which statement will do this?
A) CREATE OR REPLACE VIEW emp_view AS
SELECT employee_id, employee_name, department_name, manager_id FROM employees e,
departments d
WHERE e.department_id = d.department_id;
B) ALTER VIEW emp_view ADD (employee.manager_id) ;

 

C) ALTER VIEW emp_view MODIFY (
SELECT employee_id, employee_name, department_name, manager_id FROM employees e,
departments d
WHERE e. department_id = d. department_id) ;
D) ALTER VIEW emp_view ADD (SELECT manager_id FROM employees) ;
Answer：A
解析：视图不能进行 alter，只能重建
287.Choose two.
Examine the description of the CUSTOMERS table:
CUSTOMER_ID CUSTOMER_NAME
--------------------------------------------
10 MARK
20 Mandy
30 Mary
40 MARVIN 20

Which two SELECT statements will return these results: CUSTOMER_NAME
-------------------------
Mandy
Mary
A) SELECT customer_name FROM customers WHERE customer_name LIKE Ma* ;
B) SELECT customer_name FROM customers WHERE customer_name LIKE %a% ;
C) SELECT customer_name FROM customers WHERE UPPER (customer_name) LIKE MA% ;
D) SELECT customer_name FROM customers WHERE customer_name LIKE *Ma*;
E) SELECT customer_name FROM customers WHERE customer_name LIKE Ma% ;
F) SELECT customer_name FROM customers WHERE UPPER (customer_name) LIKE MA* ;
G) SELECT customer_name FROM customers WHERE customer_name = *Ma*;
Answer：BE
解析：注意通配符的用法

287.Choose the best answer.
You need to allow user ANDREW to:
1. Modify the TITLE and ADDRESS columns of your CUSTOMERS table.
2. GRANT that permission to other users.
Which statement will do this?
A) GRANT UPDATE ON customers. title, customers.address TO andrew;
B) GRANT UPDATE (title, address) ON customers TO andrew WITH GRANT OPTION;
C) GRANT UPDATE ON customers. title, customers.address TO andrew WITH ADMIN OPTION;
D) GRANT UPDATE ON customers. title, customers.address TO andrew WITH GRANT OPTION;
E) GRANT UPDATE (title, address) ON customers TO andrew;
F) GRANT UPDATE (title, address) ON customers TO andrew WITH ADMIN OPTION

 

Answer：B
解析：对象权限允许授权只能用 WITH GRANT OPTION

288.
Choose the best answer.
Which statement is true about TRUNCATE and DELETE?
A) You can never TRUNCATE a table if foreign key constraints would be violated.
B) You can DELETE rows from a table with referential integrity constraints.
C) For tables with multiple indexes and triggers, DELETE is faster than TRUNCATE.
D) For large tables, DELETE is faster than TRUNCATE.
Answer：B
解析：假如在定义外键约束时加上 on delete cascade，则 delete 父表的行时可以删除，即
使有完整性约束。

289.Choose two.
Which two are true about constraints?
A) All constraints can be defined at the table or column level.
B) A constraint can be disabled even if the constrained column contains data.
C) A column with a FOREIGN KEY constraint can never contain a NULL value.
D) A column with a UNIQUE constraint can contain a NULL value.
E) Constraints are enforced only during INSERT operations.
Answer：BD
290.Choose three.
Which three are true about privileges?
A) All schema objects have associated object privileges.
B) Schema owners can grant any object privilege on any object in their schema to any other
user or role.
C) A combination of object and system privileges can be granted to a role.
D) Object privileges granted on a table automatically apply to all synonyms for that table.
E) Only users with the GRANT ANY PRIVILEGE privilege can grant and revoke system privileges
from other users.
F) Only users with the DBA role can create roles.
Answer：ABD
解析：

 

不能结合在一起授权：
SQL> grant select any table,select on emp to r1; grant select any table,select on emp to r1
scott 用户：
SQL> create synonym e1 for emp;
SQL> grant select on emp to hr;
Hr 用户可以通过同义词进行查询，所以答案 D 正确：
SQL> select count(*) from scott.emp；
COUNT(*)
----------
14
SQL> select count(*) from scott.e1;
COUNT(*)
----------
14
291.Choose two.
Which two statements are true about dropping views?
A) Read only views cannot be dropped.
B) Views referencing a dropped view become invalid.
C) CASCADE CONSTRAINTS must be specified when referential integrity constraints on
otherobjects refer to primary or unique keys in the view to be dropped.
D) Data selected by a views defining query is deleted from its underlying tables when the view
isdropped.
E) The creator of a view to be dropped must have the DROP ANY VIEW privilege.
解析：只读视图可以删除；删除视图基表中的数据不会删除；删除视图只要是属主就可以
删除，没必要必须是 any，实验证明可以在参考一张视图再创建视图。
292.Choose two.
Examine these statements: CREATE
TABLE dept ( deptno NUMBER
PRIMARY KEY, dname
VARCHAR2 (10), mgr NUMBER,
CONSTRAINT dept_fkey FOREIGN KEY (mgr) REFERENCES emp (empno)) ;
CREATE TABLE emp( empno NUMBER PRIMARY KEY, ename VARCHAR2
(10), deptno NUMBER,
CONSTRAINT emp_fkey FOREIGN KEY (deptno) REFERENCES dept (deptno) DISABLE) ; ALTER
TABLE emp MODIFY CONSTRAINT emp_fkey ENABLE ;
Which two are true?
A) The MGR column in the DEPT table will not be able to contain NULL values.
B) Both foreign key constraint definitions must be removed from the CREATE TABLE statements,
and be added with ALTER TABLE statements once both tables are created, for the two CREATE
TABLE statements to execute successfully in the order shown.
C) The CREATE TABLE EMP statement must precede the CREATE TABLE DEPT statement for all

 

three statements to execute successfully.
D) The DEPTNO column in the EMP table will be able to contain NULL values.
E) All three statements execute successfully in the order shown.
F) The DEPT_FKEY constraint definition must be removed from the CREATE TABLE DEPT
statement, and be added with an ALTER TABLE statement once both tables are created, for the
two CREATE TABLE statements to execute successfully in the order shown
Answer：C D
解析：这是最近出现率很高的考题，必考。
C 答案是最好的，只要调整一下执行顺序，而不需要先建表再增加约束。 外键约束可以是
空值。
293.Choose the best answer.
Table HR. EMPLOYEES contains a row where the EMPLOYEE_ID is 109. User ALICE has no
privileges to access HR . EMPLOYEES.
User ALICE starts a session.
User HR starts a session and successfully executes these statements:
GRANT DELETE ON employees TO alice;
UPDATE employees SET salary = 24000 WHERE employee_id = 109;
In her existing session ALICE then executes:
DELETE FROM hr.employees WHERE employee_id = 109;
What is the result?
A) The DELETE command will immediately delete the row.
B) The DELETE command will immediately return an error.
C) The DELETE command will wait for HRS transaction to end then delete the row.
D) The DELETE command will wait for HRS transaction to end then return an error.
Answer：C
解析：Hr 用户修改了行数据，改行就被锁住了，其它事务只有等待
294. Which three statements about roles are true?
A) Roles are assigned to roles using the ALTER ROLE statement.
B) A role is a named group of related privileges that can only be assigned to a user.
C) A single role can be assigned to multiple users.
D) A single user can be assigned multiple roles.
E) Privileges are assigned to a role using the ALTER ROLE statement.
F) Privileges are assigned to a role using the GRANT statement.
G) Roles are assigned to users using the ALTER USER statement.
Answer：CDF
295.Choose three.
Which three actions can you perform only with system privileges?
A) Access flat files via a database, which are stored in an operating system directory.
B) Execute a procedure in another schema.
C) Log in to a database instance.
D) Create stored procedures, functions and packages.
E) Use the WITH GRANT OPTION clause.

 

F) Query any table in a database.
Answer：CDF
解析：登录数据库需要 create session 的系统权限；创建存储过程和包需要 createprocedure
系统权限；查询任何的表需要 select any table 权限。
296.Choose two.
Which two statements are true about an Oracle database?
A) A NUMBER column without data has a zero value.
B) A table can have multiple foreign keys.
C) A VARCHAR2 column without data has a NULL value.
D) A table can have multiple primary keys.
E) A column definition can specify multiple data types.
Answer：BC
297.
Choose the best answer.
Examine the description of the EMPLOYEES table:
Name Null? Type
----------------------- ------------------------
EMPLOYEE_ID NOT NULL NUMBER(4)
LAST_NAME VARCHAR2 (10)
HIRE_DATE DATE SALARY NUMBER(6,2) Examine these requirements:
1. Display the last name, date of hire and the number of years of service for each employee.
2. If the employee has been employed 5 or more years but less than 10, display "5+ years of
service".
3. If the employee has been employed 10 or more years but less than 15, display "10+ years of
service".
4. If the employee has been employed 15 or more years, display "15+ years of service".
5. If none of these conditions matches, display "<5 years of service".
6. Sort the results by the HIRE_DATE column.
Which statement satisfies all the requirements?
A) SELECT last_name, hire_date,
(CASE WHEN (SYSDATE - TO_YMINTERVAL(15 -0)) >= hire_date THEN 15+ years of
service
WHEN (SYSDATE - TO_YMINTERVAL( 10 -0))>= hire_date THEN 10+ years of service WHEN
(SYSDATE - TO_YMINTERVAL(5 -0))>= hire_date THEN 5+ years of service ELSE <5 years of
service
END) AS years
FROM employees
ORDER BY hire_date;
B) SELECT last_name, hire_date,
(CASE WHEN (SYSDATE - hire_date) >= TO_YMINTERVAL(5 -0) THEN 5+ years of service

 

WHEN (SYSDATE - hire_date) >= TO_YMINTERVAL(10 -0) THEN 10+ years of service WHEN
(SYSDATE - hire_date) >= TO_YMINTERVAL(15 -0) THEN 15+ years of service ELSE <5 years of
service
END) AS years
FROM employees
ORDER BY hire_date;
C) SELECT last_name, hire_date;
(CASE WHEN (SYSDATE - TO_YMINTERVAL(5 -0))>= hire_date THEN 5+ years of service WHEN
(SYSDATE - TO_YMINTERVAL(10 -0)) >= hire_date THEN 10+ years of service WHEN (SYSDATE -
TO_YMINTERVAL(15 -0)) >= hire_date THE N 15+ years of service ELSE <5 years of service
END) AS years
FROM employees
ORDER BY hire_date;
D) SELECT last_name, hire_date,
(CASE WHEN (SYSDATE - hire_date) >= TO_YMINTERVAL(15 -0) THEN 15+ years of
service
WHEN (SYSDATE - hire_date) >= TO_YMINTERVAL( 10 -0) THEN 10+ years of service
WHEN (SYSDATE - hire_date) >= TO_YMINTERVAL(5 -0 ) THEN 5+ years of service ELSE <5
years of service
END) AS years
FROM employees
ORDER BY hire_date;
Anser：A
(解析：使用 SYSDATE - hire_date 得出的结果是 number 型，所以 BD 答案都会报错； AC
的语法都对，但是 C 的判断顺序是错误的，先从小到大进行判断，会导致第一个条件都满
足，后面的行就无法判断，所以要先从大到小判断才可以。
298.Choose three.
Which three statements are true about external tables?
A) They can be used in queries containing joins.
B) Their metadata is stored in the database.
C) They can be temporary tables.
D) They can be indexed.
E) They can be used in queries containing sorts.
F) DML statements can modify them.
Answer：ABE
解析：可以外部表之间可以进行连接查询。您还可以为外部表创建视图和同义词。但是，无
法在外部表上创建 DML 操作（更新、插入或删除），也无法创建索引。使用外部表卸载数
据时，根据 SELECT 语句中的数据类型自动创建元数据。
299.Choose two.
Which two will execute successfully?
A) SELECT NVL(DATE, SYSDATE) FROM DUAL;

 

B) SELECT COALESCE (DATE, SYSDATE) FROM DUAL;
C) SELECT COALESCE (DATE, SYSDATE) FROM (SELECT NULL AS "DATE" FROM DUAL) ;
D) SELECT COALESCE (0, SYSDATE) FROM DUAL;
E) SELECT NVL(DATE ,200) FROM (SELECT NULL AS "DATE" FROM DUAL) ;
Answer：AE
解析：注意函数中的数据类型要一致。
300.Choose two.
Which two statements are true about the data dictionary?
A) The data dictionary does not store metadata in tables.
B) The data dictionary is accessible when the database is closed.
C) Views with the prefix ALL_ , DBA_ and USER_ are not all available for every type of
metadata.
D) Views with the prefix ALL_ display metadata for objects to which the current user has
access.
E) Views with the prefix DBA_ display only metadata for objects in the SYS schema.
Answer：CD
解析：所有的数据字典都存放在数据字典基表中；
C 的答案的意思是不同的前缀的数据字典里面包含的每种数据类型不是都是可用的，比如
有 dba_profiles，但是就没有 user_profiles.
301.Choose two.
Which two are true about external tables that use the ORACLE_DATAPUMP access driver?
A) When creating an external table, data can be selected only from a table whose rows are
stored in database blocks.
B) Creating an external table creates a directory object.
C) When creating an external table, data can be selected from another external table or from a
table whose rows are stored in database blocks.
D) Creating an external table creates a dump file that can be used only by an external table in
the same database.
E)Creating an external table creates a dump file that can be used by an external table in the
same or a different database
Answer：BE
（解析：创建一个外部表就是创建一个目录对象，外部表使用的 dump 文件可以给其它的
外部表使用。）
https://docs.oracle.com/cd/E24693_01/server.11203/e22490/et_concepts.htm
302.Choose the best answer.
User HR has CREATE SESSION, CREATE ANY TABLE and UNLIMITED TABLESPACE
privileges.
User SCOTT has CREATE SESSION, CREATE TABLE and UNLIMITED TABLESPACE
privileges.
HR successfully executes this statement:

 

CREATE TABLE scott. products
( prod_id NUMBER(2) , prod_name
VARCHAR2 (20)); HR attempts to
execute:
1. INSERT INTO scott.products VALUES (1, LAPTOP) ; SCOTT attempts to execute:
2. SELECT * FROM products;
3. INSERT INTO scott. products VALUES (2, HDD);
4. CREATE SYNONYM prod FOR products;
Which will execute successfully?
A) 1 only
B) 1, 2 and 3
C) 2, 3 and 4
D) 2 and 3 only
Answer：C
（解析：因为 hr 没有 insert 的权限，scott 用户没有 create synonym 的权限。）
303.Choose two.
Which two are true about using constraints?
A) PRIMARY KEY and FOREIGN KEY constraints can be specified at the column and at the table
level.
B) A table can have multiple PRIMARY KEY and multiple FOREIGN KEY constraints.
C) A table can have only one PRIMARY KEY but may have multiple FOREIGN KEY constraints.
D) NOT NULL can be specified at the column and at the table level.
E) A FOREIGN KEY column in a child table and the referenced PRIMARY KEY column in the
parent table must have the same names.
F) A table can have only one PRIMARY KEY and one FOREIGN KEY constraint.
Answer：AC
解析：答案 AC 是最基本的创建约束的规则。本题出现的频率很高。
304.Choose two.
Examine the description of the BOOKS table:
Name Type Null?
------------------------------------------------------------------
BOOK_ID NOT NULL NUMBER(4)
BOOK_TITLE VARCHAR2 (250)
PRICE NUMBER(5,2)
PURCHASE_DATE DATE AUTHOR_NAME VARCHAR2 (30)
Examine these requirements:
1. Display book titles for books purchased before January 17, 2007 costing less than 500 or
more than 1000.
2. Sort the titles by date of purchase, starting with the most recently purchased book.
Which two queries can be used?
A) SELECT book_title
FROM books

 

WHERE (price < 500 OR price > 1000) AND
(purchase_date < 17 -JAN-2007)
ORDER BY purchase_date DESC;
B) SELECT book_title
FROM books
WHERE (price < 500 OR > 1000)
AND (purchase_date < 17 -JAN-2007)
ORDER BY purchase_date DESC;
C) SELECT book_title
FROM books
WHERE (price IN (500, 1000) )
AND (purchase_date < 17 -JAN-2007)
ORDER BY purchase_date ASC;
D) SELECT book_title
FROM books
WHERE (price NOT BETWEEN 500 AND 1000)
AND (purchase_date < 17-JAN-2007) ORADER BY
purchase_date DESC;
E) SELECT book_title
FROM books
WHERE (price BETWEEN 500 AND 1000)
AND (purchase_date < 17 -JAN- 2007)
ORDER BY purchase_date;
Answer：AD
解析：需要进行倒序排序，B 语法错误，CE 条件错误。
305. 这题和 174 题都是类似的题目。
Which two statements are true regarding indexes? Choose two
A) When a table is dropped and is moved to the RECYCLE BIN, all indexes built on that table are
permanently dropped.
B) A UNIQUE index can be altered to be non-unique.
C) An update to a table can result in updates to any or all of the tables indexes.
D) An update to a table can result in no updates to any of the tables indexes.
E) A table belonging to one user cannot have an index that belongs to a different user.
Answer：CD
306.Choose two.
Examine the description of the EMPLOYEES table:
Name Null? Type
-----------------------------------------------------------------------
EMPLOYEE_NAME NOT NULL VARCHAR2 (5)
HIRE_DATE DATE
SALARY NUMBER(7,2)

 

he session time zone is the same as the database server.
Which two statements will list only the employees who have been working with the company for
more than five years?
A) SELECT employee_name FROM employees WHERE (CURRENT_DATE - hire_date)/12>5;
B) SELECT employee_name FROM employees WHERE (SYSDATE - hire_date) / 12 > 5;
C) SELECT employee_name FROM employees WHERE (SYSDATE - hire_date) / 365 > 5;
D) SELECT employee_name FROM employees WHERE (SYSTIMESTAMP - hire_date) / 365 >
INTERVAL 1825 DAY;
E) SELECT employee_name FROM employees WHERE (SYSTIMESTAMP - hire_date) / 12 >
INTERVAL 5 YEAR;
F) SELECT employee_name FROM employees WHERE (CURRENT_DATE -
hire_date)/365>5;
Answer：CF
解析：日期相减之后的结果是天数，所以求年的话需要除以 365 天， D：验证：提示 “无
效的关系运算符”，E：验证：提示“无效的关系运算符”，hire_date 是日期型，跟时间戳有
区别，不能相减。
307.Choose the best answer.
Examine the data in the EMPLOYEES table:
EMPLOYEE ID LAST_NAME MONTHLY_SALARY MONTHLY_COMMISSION_PCT
------------------ ------------------- --------------------------- ------------------------------------------
101 Kochhar 24000 <null>
102 emst 17000 .5
103 Rajs 21000 .2
104 Lorentz 25000 <null>105
12000 <null>
Which statement will compute the total annual compensation for each employee?
A) SELECT last_name, (monthly_salary * 12) + (monthly_salary * 12 * NVL
(monthly_commission_pct, 0)) AS annual_comp FROM employees;
B) SELECT last_name, (monthly_salary + monthly_commission_pct) * 12 AS annual_comp
FROM employees;
C) SELECT last_name, (monthly_salary * 12) + (monthly_salary * 12 *
monthly_commission_pct) As annual_comp
FROM employees
C) SELECT last_name, (monthly_salary * 12) + (monthly_commission_pct * 12) AS
annual_comp FROM employees;
Answer：A （解析：注意非空处理）
308.Choose two.
Which two are true about queries using set operators (UNION, UNION ALL, INTERSECT and
MINUS)?
A) None of the set operators can be used when selecting CLOB columns.
B) The FOR UPDATE clause cannot be specified.

 

C) The name of each column in the first SELECT list must match the name of the corresponding
column in each subsequent SELECT list.
D) Each SELECT statement in the query can have an ORDER BY clause.
E) There must be an equal number of columns in each SELECT list.
解析：使用 union all 可以支持 clob。For update 不能用在这些集合操作上
309. Which two join conditions in a FROM clause are non-equijoins?
A) table1 JOIN table2 ON (table1.column BETWEEN table2.column1 AND table2.column2)
B) table1 JOIN table2 ON (table1.column = table2.column) WHERE table2.column LIKE A%
C) table1 NATURAL JOIN table2
D) table1 JOIN table2 ON (table1.column >= table2. column)
E) table1 JOIN table2 USING (column1, column2)
Answer：AD （解析：这道题分析哪些 sql 是非等连接。）
310.Choose two.
Examine the description of the EMPLOYEES table:
Name null? Type
-------------------------------------------------------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
SALARY NUMBER(8,2)
DEPARTMENT_ID NUMBER(4)
Which two queries return the highest salary in the table?
A) SELECT MAX (salary) FROM
employees
GROUP BY department_id;
B) SELECT MAX (salary)
FROM employees;
C) SELECT MAX (salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) = MAX (MAX (salary) ) ;
D) SELECT department_id, MAX
(salary)
FROM employees
GROUP BY department_id;
E) SELECT MAX (MAX (salary))
FROM employees
GROUP BY department_id;
Answer：BE
（解析：题目求的是最高工资，A：分组了； C：这里不能嵌套着用； D：分组了）
311.Choose the best answer.
Which statement is true about using functions in WHERE and HAVING?
A) Using aggregate functions in the HAVING clause requires a subquery.
B) Using single-row functions in the WHERE clause requires a subquery.
C) Using single-row functions in the HAVING clause requires a subquery.

 

D) Using aggregate functions in the WHERE clause requires a subquery.
Answer：D
解析：having 子句用到聚组函数不需要子查询；但是 where 子句中如果需要聚组函数，则
需要在子查询中调用
312.Choose two.
orcl.dmp contains a full export of the ORCL database.
This command is executed to load data from orcl.dmp into the TESTDB database: [oracle@host01
~] impdp system/oracle \
-> SCHEMAS = sh,oe \
-> REMAP_SCHEMA = sh:hr \
-> DUMPFILE = orcl.dmp \
-> EXCLUDE = index \
-> TABLE_EXISTS_ACTION = replace \
-> LOGFILE = impdp.1og
Which two statements are true?
A) It skips only tables that exist in both ORCL:SH and TESTDB:HR.
B) It drops and re-creates tables that exist in both ORCL:SH and TESTDB:HR.
C) It skips only indexes that exist in both ORCL:SH and TESTDB:HR.
D) It drops and re-creates indexes that exist in both ORCL:SH and TESTDB:HR.
E) It skips all indexes of ORCL:SH and ORCL:OE.
Answer：BC
解析：因为是 replace，所以 oracle 会 drop 再重建表，索引是排除的，所以跳过。
313.Choose two.
TBS1 is a permanent user-defined tablespace.
Oracle Managed Files (OMF) is not used in this database.
Examine this command:
DROP TABLESPACE tbs1;
Which two will ensure that TBS1 is dropped and its datafiles are deleted from the file system？
A) TBS1 must not contain any temp files.
B) TBS1 must be altered READ ONLY.
C) There must be no uncommitted transactions involving segments stored in TBS1.
D) TBS1 must be READ WRITE.
E) The INCLUDING CONTENTS AND DATAFILES clause must be added to the command.
Answer：CE
解析：要能够 drop 表空间，必须保证当前的表空间没有被占用的锁，如果要与数据文件一
同删除，则需要加上 including datafiles 子句。
314.Choose three.
Which three statements are true about a dedicated server configuration?
A) A dedicated server process can be spawned without a listener when using local clients.
B) A dedicated server process may be reused by a new session after the session using that
process terminates.
C) Each dedicated server process has its own dispatcher process.
D) A dedicated server process can be spawned by the listener when using local clients.

 

E) A dedicated server process communicates directly with a client or middle tier process once
the session is established.
F) The DBA configures the maximum number of dedicated server processes that can share the
same dispatcher process.
Answer：ADE 解析：有个专用服务器连接的特点
315.Choose two.
Which two statements are true about time zones, date data types, and timestamp data types in
an Oracle database?
A) The DATE datatype returns data with 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) The CURRENT_TIMESTAMP function returns data without time zone information.
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：BE
316. Which two actions can you perform using DBCA for an existing database?
A) Create nonstandard block size tablespaces.
B) Create a template that can be used to clone the database.
C) Change the server mode from dedicated to shared, and vice versa.
D) Change the character set.
E) Create an additional listener.
Answer：BC 答案 BC 可以通过实验测试。
317.Choose the best answer.
As the DBA, you execute this command:
GRANT CREATE VIEW TO usr1 WITH ADMIN OPTION; USR1 then executes this command:
GRANT CREATE VIEW TO usr2 WITH ADMIN OPTION; USR2 then executes this command:
GRANT CREATE VIEW TO usr3;
Which statement is true?
A) USR1 can revoke the CREATE VIEW privilege from USR3.
B) The DBA can revoke only ADMIN OPTION from USR1.
C) When the DBA revokes the CREATE VIEW privilege from USR1, it is revoked from USR2 but
not USR3.
D) When the DBA revokes the CREATE VIEW privilege from USR1, it is neither revoked from
USR2 nor USR3.
E) When the DBA revokes the CREATE VIEW privilege from USR1, it is revoked from USR2 and
USR3.
F) When USR1 revokes the CREATE VIEW privilege from USR2, it is revoked from USR3.
Anser：D（A）
解析：AD 答案都对，题目要求最好的答案，选择 D 是因为它描述的比较全面，A 答案太
简单。
318.Choose three.
Examine the description of the PRODUCTS table:

 

NAME NULL TYPE
------------------------------------------------------------------------------------
PRODUCT_ID NOT NULL NUMBER (2)
PRODUCT_NAME VARCHAR2 (10)
UNIT_PRICE NUMBER(3)
SURCHARGE VARCHAR2 (2)
EXPIRY_DATE DATE
DELIVERY_DATE DATE
Which three queries use valid expressions?
A) SELECT product_id, expiry_date * 2 FROM products;
B) SELECT product_id, unit_ price, unit_ price + surcharge FROM products;
C) SELECT product_id, (unit_ price * 0.15 / (4.75 + 552.25)) FROM products;
D) SELECT product_id, unit_ price || 5 "Discount"，unit_ price + surcharge - discount FROM
products;
E) SELECT product_id, (expiry_date - delivery_date) * 2 FROM products;
F) SELECT product_id, unit_ price, 5 "Discount", unit_price + surcharge - discount
FROMproducts;
Anser：CEF 解析：跟题库中的 12C 中 071 题目答案相同。
320. Which two statements are true about the count function?
A) A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a
WHERE clause.
B) COUNT (*) returns the number of rows in a table including duplicate rows and rows
containing NULLs in a column.
C) It can only be used for NUMBER data types
D) COUNT (DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates
and NULLs in the INV_AMT column.
E) COUNT (inv_amt) returns the number of rows in a table including rows with NULL in the
INV_AMT column.
Anser：BD
321. Which two statements are true about a self join?
A) It can be a left outer join.
B) It can be an inner join
C) It must be a full outer join
D) The join key column must have an index
E) It must be an equijoin
Anser：BE

 

322.
You must determine if any customers' details have been entered more than once using a
different CUSTNO, by listing all duplicate names.
Which two methods can you use to get the required result?
A) subquery
B) FULL OUTER JOIN with self join
C) LEFT OUTER JOIN with self join
D) self join
E) RIGHT OUTER JOIN with self join
Anser：AD
323.