一起學(xué)OCP：082題庫(kù)及解析

• 第1題 BIGFILE
• 第2題 INTERSECT
• 第3題
• 第4題
• 第5題 temporary undo
• 第6題 INTERVAL
• 第7題
• 第8題
• 第9題 SAVEPOINT
• 第10題 shared server
• 第11題
• 第12題 自連接
• 第13題 &&
• 第14題 LIKE
• 第15題
• 第16題
• 第17題 INVISIBLE COLUMN
• 第18題 UNUSED
• 第19題
• 第20題

第1題 BIGFILE

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】

解析：注意創(chuàng)建的是bigfile表空間，意味著只會(huì)有一個(gè)數(shù)據(jù)文件。一般都是自動(dòng)擴(kuò)展。所以AC對(duì)；從很早的版本大概是9I開(kāi)始，ORACLE都是本地管理的了，所以D錯(cuò)。默認(rèn)塊大小一般是8K并且可以修改，所以E錯(cuò)。bigfile只是名字叫bigfile，并不意味著他一定會(huì)比其他表空間的數(shù)據(jù)文件大，所以B錯(cuò)（感覺(jué)這個(gè)選項(xiàng)有點(diǎn)像腦筋急轉(zhuǎn)彎了）

第2題 INTERSECT

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 查詢兩個(gè)結(jié)果集中的交集的數(shù)據(jù)，不會(huì)忽略空值

第3題

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】

第4題

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】

第5題 temporary undo

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開(kāi)始提供的一個(gè)新特性，ORACLE中的臨時(shí)表也會(huì)產(chǎn)生undo信息，在12C之前臨時(shí)表產(chǎn)生的UNDO信息是存儲(chǔ)Undo 表空間 和 Redo 日志文件，在12C及以后的版本是存儲(chǔ)在臨時(shí)表空間里的，不會(huì)再寫入undo表空間。因此選E.
相關(guān)知識(shí)點(diǎn)這里

第6題 INTERVAL

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】

解析:因?yàn)槭怯?jì)算未付款天數(shù)的利息，用B是合適的。
相關(guān)知識(shí)點(diǎn)這里

第7題

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允許兩個(gè)監(jiān)聽(tīng)公用一個(gè)端口，雖然這在實(shí)際應(yīng)用中沒(méi)有什么意義，但是確實(shí)允許。
A選項(xiàng)說(shuō)動(dòng)態(tài)注冊(cè)服務(wù)不能用于這個(gè)數(shù)據(jù)庫(kù)實(shí)例，明顯是錯(cuò)的。
B 選項(xiàng)我理解是LREG有前提，如果監(jiān)聽(tīng)未啟動(dòng)，它就不能動(dòng)態(tài)注冊(cè)服務(wù)。
C選項(xiàng)的內(nèi)容有點(diǎn)莫名其妙，動(dòng)態(tài)注冊(cè)跟是否在ora文件中定義監(jiān)聽(tīng)沒(méi)關(guān)系。
E選項(xiàng)同上，動(dòng)態(tài)注冊(cè)跟 CONNECT_DATA沒(méi)什么關(guān)系。

第8題

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選項(xiàng)沒(méi)考慮bigfile表空間，bigfile表空間只允許有一個(gè)數(shù)據(jù)文件；
B選項(xiàng)，不同的段可以分配到不同的表空間中(例如分區(qū)表），因此可以存放到不同的數(shù)據(jù)文件中，我覺(jué)得問(wèn)題出在那個(gè)must上
E選項(xiàng)錯(cuò)，因?yàn)槎沃衎lock size是一樣的。
F錯(cuò)，segment可以粗暴的理解成表，一般都說(shuō)段表，oracle中一個(gè)表不可能跨表空間存儲(chǔ)。但是可以跨數(shù)據(jù)文件存在。因?yàn)橐粋€(gè)表空間是可以包含多個(gè)數(shù)據(jù)文件的。

第9題 SAVEPOINT

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有個(gè)特性，只要發(fā)生commit，則事務(wù)中所有的savepoint都會(huì)消失。在oracle中，所有的ddl語(yǔ)句都會(huì)執(zhí)行commit，因此ddl后就不用再想著用savepoint了。DDL語(yǔ)句包括：create table\alter table\drop table \truncate table \create index\drop index，所以A,B錯(cuò)。事務(wù)中可以有多個(gè)SAVEPOINT，所以C錯(cuò)。

第10題 shared server

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中的服務(wù)器連接有專有服務(wù)器Dedicated Server和共享服務(wù)器Shared Server鏈接兩種。其中專有服務(wù)器鏈接就是一個(gè)會(huì)話進(jìn)程分配一個(gè)專有的服務(wù)器進(jìn)程，而在共享服務(wù)器模式下通過(guò)一調(diào)度服務(wù)來(lái)同時(shí)服務(wù)多個(gè)會(huì)話進(jìn)程，將請(qǐng)求放到請(qǐng)求隊(duì)列中，多個(gè)調(diào)度服務(wù)共用一個(gè)請(qǐng)求隊(duì)列，所以A對(duì)。每個(gè)調(diào)度服務(wù)有自己的響應(yīng)隊(duì)列，處理后的結(jié)果也會(huì)寫入各自的響應(yīng)隊(duì)列然后返回給客戶端。

第11題

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 &&
正確答案【CD】

解析：事實(shí)上大部分的SQL命令都可以同時(shí)在SQL* PLUS和SQL DEVELOP上執(zhí)行，只所以說(shuō)大部分，只是因?yàn)楣俜轿臋n也沒(méi)有特別說(shuō)明有哪些命令不能在SQL DEVELOP上執(zhí)行。所以C對(duì)；SET VERIFY ON的作用及就是校驗(yàn)環(huán)境變量，必須設(shè)置DEFINE后才能顯示&&和&開(kāi)頭的變量，所以D對(duì)E錯(cuò)。

第12題 自連接

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 equal join
D) It must be an inner join
E) The ON clause can be used
F) It can be an outer join
正確答案：【BEF】

解析：自連接查詢必須用不同的別名，否則無(wú)法區(qū)分變量，可以用ON連接但是不是必須；可以是外連接。所以BEF對(duì)。CD選項(xiàng)的在于不是must，而應(yīng)該是can。在復(fù)習(xí)時(shí)容易產(chǎn)生一種帶must選項(xiàng)就是錯(cuò)誤的印象，這個(gè)印象很危險(xiǎn)，還是建議弄懂題意，吃透答案。

第13題 &&

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 WEHRE ‘&&condition’ = ‘&cond’;
E) SELECT &&col1,&&col2 FROM & table WHERE &&condition.
正確答案【B】

解析：題目中要求第一次執(zhí)行時(shí)只要求輸入表名參數(shù)，所以B正確。(only prompts for the table name the first time)。所以復(fù)習(xí)考試不能只是單純的背題庫(kù)，一定要吃透題目和答案

第14題 LIKE

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_%’
正確答案【A】

解析：寫過(guò)代碼的同學(xué)應(yīng)該都知道選A，以D開(kāi)頭的城市名稱必須至少兩個(gè)字符，必然是like ‘D_%’

第15題

Examine this command:
ALTER DATABASE MOVE DATAFILE ‘/u01/sales1.dbf’ TO ‘/u01/sales01.dbf’ REUSE;
Which tow statements are true?(Choose two.)

A) DML may be performed on tables with one or more extents in this data file during the executiong 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_DES
E) The file is renamed and stored in the same location
正確答案【AE】

解析：從oracle12C開(kāi)始，ORACLE支持在線移動(dòng)數(shù)據(jù)文件，這里的在線，意味著不用設(shè)置表空間為只讀，也不影響數(shù)據(jù)文件里的表的DML語(yǔ)句，所以A對(duì)BC錯(cuò)；reuse參數(shù)表明重用原來(lái)的數(shù)據(jù)文件，只是改了名字。所以E對(duì)D錯(cuò)。

第16題

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
正確答案【ADF】

解析：當(dāng)使用CASCADE參數(shù)時(shí)會(huì)同時(shí)刪除逐漸和外鍵，所以A對(duì)， Partition key不能刪除，所以D對(duì)，ORACLE中表的有1000列的限制，即使一個(gè)列被設(shè)置為unuse也會(huì)算到里面，所以F對(duì)；設(shè)置一個(gè)列為unuse只是隱藏，但是仍然是物理存在的，所以BC錯(cuò)，列即使被設(shè)置為unuse，也是可以被恢復(fù)的，所以E錯(cuò)。

第17題 INVISIBLE COLUMN

Examine the following command
CREATE TABLE (
prod id number(4),
Pord_name varchar2(20),
Category_id number(30),
Quantity_on_hand number(3) INVISIBLE
);
Which three statments 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.
正確答案【ABE】

解析：可隱藏列，在select和desc都看不到該列，隱藏列可以增加主鍵約束，設(shè)置為隱藏后也可以重新設(shè)置可見(jiàn)，所以ABE對(duì)DC錯(cuò)。網(wǎng)上有些文章說(shuō)用DESC可以看到隱藏列，我自己在19C下試了不行。愿意求證的可以自己嘗試一下。

第18題 UNUSED

Evaluate the following ALTER TABLE statement:
ALTER TABLE orders SET UNUSED (order_date);
Which statment 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 ORDER table
D) The DESCRIBE command would display the order_date column
正確答案【A】

解析：還是unused列題，看來(lái)Oracle真的很喜歡這個(gè)unused，選項(xiàng)A是可以的，可以添加一個(gè)order_data列，unused不要求列為空，設(shè)置為UNUSED后列可見(jiàn)，所以BD錯(cuò)。選項(xiàng)C完全沒(méi)搞懂要說(shuō)的是什么，unused列是可以恢復(fù)的，但不是通過(guò)ROLLBACK.

第19題

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

解析：列一旦被設(shè)置為 unused 以后，基于該列的約束、索引都被刪除，同時(shí)相關(guān)的視圖被無(wú)法訪問(wèn)，但是列的數(shù)據(jù)需要 drop column 時(shí)才會(huì)被刪除。（來(lái)源CUUG）

第20題

Examine the structure of the DEPARTMENTS table,

You execute the following command:
SQL> ALTER TABLE departments SET UNUSED (country);
Which two statements are true?(choose two)

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.
正確答案【AC】

總結(jié)

以上是生活随笔為你收集整理的一起学OCP：oracle-082题库及解析(1-20)的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。