Oracle 19c OCP 1Z0-082認證考試題庫（24-29）

24.Which three statements are true regarding the UNION and UNION ALL operators?

A.The number of columns selected by the first SELECT statement can be greater than the number selected in subsequent SELECT statements

B.Duplicates are eliminated automatically by the UNION ALL operator

C.The number of columns selected in each SELECT statement must be identical 

D.NULIS are not ignored during duplicate checking

E.The names of columns selected in each SELECT statement must be identical

F.The names of columns selected in each SELECT statement can be identical 

G.Duplicates can optionally be eliminated by the UNION operator

答案：CDF

25.The SALES table has columns PROD_ ID and QUANTITY_SOLD of data type  NUMBER. Which two queries execute successfully?

A.SELECT prod_id FROM sales WHERE quantity_sold >55000 AND COUNT(*)> 10 GROUP BY prod_id HAVING COUNT(*)>10:

B.SELECT COUNT (prod_id) FROM sales GROUP BY prod_id WHERE quantity_sold >55000 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 >55000 GROUP BY prod_id HAVING COUNT (*)>10;

E.SELECT COUNT (prod_id) FROM sales WHERE quantity_sold >55000 GROUP BY prod_id;

答案：DE

SQL> select count(EMPLOYEE_ID) from employees where salary>10000 group by EMPLOYEE_ID;

26.Which two statements are true about INTERVAL data types? 

A.INTERVAL YEAR TO MONTH columns support yearly intervals

B.The YEAR field in an INTERVAL YEAR TO MONTH column must be a positive value

C.The value in an INTERVAL DAY TO SECOND column can be copied into an INTERVAL YEAR TOMONTH column

D.INTERVAL YEAR TO MONTH columns only support monthly intervals within a single year E.INTERVAL DAY TO SECOND columns support fractions of seconds

F. INTERVAL YEAR TO MONTH columns only support monthly intervals within a range of years

答案：AE

27.Which two statements are true concerning logical and physical database structures?

A.A segment's blocks can be of different sizes.

B.A segment might have only one extent

C.All tablespaces may have one or more data files 

D.Segments can span multiple tablespaces

E.A segment can span multiple data files in some tablespaces

答案：BE

28.Which two statements are true about the Oracle join and ANSI join syntax?

A.The Oracle join syntax performs better than the SQL: 1999 compliant ANSI join syntax. B.The Oracle join syntax lacks the ability to do outer joins

C.The SQL: 1999 compliant ANSI join syntax supports creation of a Cartesian product of two tables

D.The Oracle join syntax supports creation of a Cartesian product of two tables

E.The Oracle join syntax performs less well than the SQL: 1999 compliant ANSI join syntax

答案：CD

29.Examine this command

SQL> ALTER TABLE ORDERS SHRINK SPACE COMPACT

Which two statements are true?

A.Queries and DML statements are allowed on ORDERs while the SHRINK is executing B.Dependent indexes become UNUSABLE

C.The SHRINK operation causes rows to be moved to empty space starting toward the end of the ORDERs segment

D.The SHRINK operation causes rows to be moved to empty space starting from the beginning of the ORDERs segment.

E.Only queries are allowed on ORDERs while the SHRINK is executing

F.The high-water mark(HWM)of ORDERs is adjusted

答案：AD

segment shrink 分為兩個階段：

1、資料重組（compact）：通過一系列insert、delete操作，將資料儘量排在段的前面，這個過程中需要在表上加RX鎖，及只需要移動的行上加鎖

2、2、HWM調整：第二階段是調整HWM位置，釋放空閒資料塊，此過程需要在表上加X鎖（獨享鎖，因此這個表都被鎖定，如果系統處在高峰期的時候，其他在此表的DML被掛起，會產生嚴重阻塞），會造成表上所有DML語句阻塞，系統忙時影響較大。

shrink space語句兩個階段都執行 。  shrink space compact語句只執行第一個階段。