You execute this command: CREATE BIGFILE TABLESPACE big_tbs DATAFILE ‘/u01/oracle/data/big_f1.dbf’ SIZE 20G; sufficient storage is available in filesyatem /u01. 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 dictonary-managed tablespace by default E) It will always have a 32K block size 正确答案【AC】

解析：注意创建的是bigfile表空间，意味着只会有一个数据文件。一般都是自动扩展。所以AC对；从很早的版本大概是9I开始，ORACLE都是本地管理的了，所以D错。默认块大小一般是8K并且可以修改，所以E错。bigfile只是名字叫bigfile，并不意味着他一定会比其他表空间的数据文件大，所以B错（感觉这个选项有点像脑筋急转弯了）

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 正确答案【B】

解析：INTERSECT 查询两个结果集中的交集的数据，不会忽略空值

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. 正确答案【CDF】

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 正确答案【CE】

A database is configured to use automatic undo management with temporary undo enabled. An UPDATE is executed on a temporary table. Where is the UNOD stored

A) in the undo tablespace B) in the SYSAUX tablespace C) in the SGA D) in the PGA E) in the temporary tablespace 正确答案【E】

解析：temporary undo是从12C开始提供的一个新特性，ORACLE中的临时表也会产生undo信息，在12C之前临时表产生的UNDO信息是存储Undo 表空间 和 Redo 日志文件，在12C及以后的版本是存储在临时表空间里的，不会再写入undo表空间。因此选E. 相关知识点这里

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 正确答案：【B】

解析:因为是计算未付款天数的利息，用B是合适的。 相关知识点这里

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: LISTENER_1= (ADDRESS= (PROTOCOL=TCP) (HOST=host1.abc.com) (PORT=1521) ) 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 dataase instances E) The definition for LISTENER_1 requires a CONNECT_DATA section to enable dynamic service Registration 正确答案【D】

解析：oracle允许两个监听公用一个端口，虽然这在实际应用中没有什么意义，但是确实允许。 A选项说动态注册服务不能用于这个数据库实例，明显是错的。 B 选项我理解是LREG有前提，如果监听未启动，它就不能动态注册服务。 C选项的内容有点莫名其妙，动态注册跟是否在ora文件中定义监听没关系。 E选项同上，动态注册跟 CONNECT_DATA没什么关系。

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 extends 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 tablespaces; 正确答案【CDF】

解析：A选项没考虑bigfile表空间，bigfile表空间只允许有一个数据文件； B选项，不同的段可以分配到不同的表空间中(例如分区表），因此可以存放到不同的数据文件中，我觉得问题出在那个must上 E选项错，因为段中block size是一样的。 F错，segment可以粗暴的理解成表，一般都说段表，oracle中一个表不可能跨表空间存储。但是可以跨数据文件存在。因为一个表空间是可以包含多个数据文件的。

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 正确答案：【DE】

解析：savepoint有个特性，只要发生commit，则事务中所有的savepoint都会消失。在oracle中，所有的ddl语句都会执行commit，因此ddl后就不用再想着用savepoint了。DDL语句包括：create table\alter table\drop table \truncate table \create index\drop index，所以A,B错。事务中可以有多个SAVEPOINT，所以C错。

Which three functions are performed by dispatchers in a shared server configuration ? (Choose three.)

A) writing inbound request to the common request 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 正确答案【ADF】

解析：ORACLE中的服务器连接有专有服务器Dedicated Server和共享服务器Shared Server