Oracle 1z0-071 2019.10最新题库&解答 21-25

QUESTION 21

choose two

In the PROMOTIONS table, the PROMO_BEGTN_DATE column is of datatype 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(PROMO_ BEGIN_DATE) - 5 will return number

B) TO_DATE(PROMO_BEGIN_DATE * 5) will return a date

C) PROMO_BEGIN_DATE - SYSDATE will return a number.

D) PROMO_BEGIN_DATE - 5 will return a date.

E) PROMO_BEGIN_DATE - SYSDATE will return an ERROR

Correct Answer: CD

考察转换函数，数值和字符，字符和日期可以互相转换，数值和日期不能转换，所以直接

排除A 选项不对，日期类型和数值不能做*运算

RR

有点四舍五入表示年的意思，具体的用法有那么一点点复杂。以s表示输入的年份最后两位，c表示当前的年份最后两位,其输出结果(新的年份前两位）可以用函数r=f(s,c)来表示,s2,c2分别表示s,c的前两位。

1）s=[0,49],c=[0,49],则r=c2

s=[0,49],c=[50,99],则 r=c2+1

s=[50,99],c=[0,49],则r=c2-1

s=[50,99],c=[50,99],则 r=c2

简而言之就是靠近当前年份原则，如果和当前年份同区域那么就一样，如果比当前区域大，那么就是当作是当前世纪前一世纪，否则就是下一个世纪。举例来说，以to_date为例子

SQL> select
to_date(‘89-01-01’,‘rr-mm-dd’) ,to_date(‘12-01-01’,‘rr-mm-dd’) FROM DUAL;

TO_DATE(‘89-01-01’,‘RR-MM-DD’)
TO_DATE(‘12-01-01’,‘RR-MM-DD’)

---

---

1989-01-01 2012-01-01

to_char( TO_DATE(‘49-01-01’,‘RR-MM-DD’),
‘YYYY-MM-DD’) ,to_char( TO_DATE(‘50-01-01’,‘RR-MM-DD’)

2049-01-01 1950-01-01

我想oracle会搞这个东东出来，估计有两个考虑一个是为了方便，一个是为了对付百年或者千年问题。

原文链接：https://blog.youkuaiyun.com/jinlong5200/article/details/3135949

QUESTION 22

Choose two

Which two statements are true about transactions in the
Oracle Database serve?

A) An uncommitted
transaction commits automatically if the user exits SQL*Plus

B) Data Manipulation Language (DML) statements always
start a new transaction.

C) A user can always see uncommitted updates made by the
same user in a different session.

D) A Data Definition Language (DDL) statement does a
commit automatically only for the data dictionary updates caused by the DDL

E) A session can
always see uncommitted updetes made by itself.

F) If a session has an uncommitted transaction, then a
DDL statement issue a COMMIT before starting a new transaction.

Correct Answer: AE

QUESTION 23

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

Correct Answer: BEF

    ALTER TABLE

table_name READ ONLY;

    ALTER TABLE

table_name READ WRITE;

解析：Set unused 操作是不允许的，可以在表可读可写状态时，置为不可用，然后在只读状态下再去删除

DROP
COLUMN

Error starting at line : 134 in command

ALTER TABLE TESTD2 DROP COLUMN COL2

Error report -

ORA-12081: update operation not allowed
on table “DCMSTEST”.“TESTD2”

12081. 00000 - “update operation not allowed on table
       “%s”.”%s""

*Cause: An attempt was made to update a read-only
materialized view.

*Action: No action required. Only Oracle is allowed
to update a

       read-only materialized view.

Error starting at line : 137 in command

Set UNUSED

ALTER TABLE TESTD2 SET UNUSED(COL2)

Error report -

ORA-12081: update operation not allowed
on table “DCMSTEST”.“TESTD2”

12081. 00000 - “update operation not allowed on table
       “%s”.”%s""

*Cause: An attempt was made to update a read-only
materialized view.

*Action: No action required. Only Oracle is allowed
to update a

       read-only materialized view.

TRUNCATE TABLE

ORA-12081: update operation not allowed
on table “HR”.“TEST1”

12081. 00000 - “update operation not allowed on table
       “%s”.”%s""

*Cause: An attempt was made to update a read-only
materialized view.

*Action: No action required. Only Oracle is allowed
to update a

QUESTION 24

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

Correct Answer: BCF

解析：D 会产生redo，可以通过查看统计信息，打开set autotrace on,然后对临时表执行dml

操作，会有redo size

20.3.2.4 Creating Private Temporary
Tables

Private temporary tables are temporary
database objects that are dropped at the end of a transaction or session.
Private temporary tables are stored in memory and each one is visible only to
the session that created it.

20.3.2.3.1 About Creating Global
Temporary Tables

The metadata of a global temporary table
is visible to multiple users and their sessions, but its content is local to a
session.

https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-tables.html#GUID-48131690-63ED-4F13-BF8C-4A292D679EBA

QUESTION 25

Choose three.

Which three statements are true about defining relations
between tables in a relational database?

A) Foreign key columns allow null values.

B) Unique key columns allow null values

C) Primary key columns allow null values.

D) Every primary or unique key value must refer to a
matching foreign key value.

E) Every foreign key value must refer to a matching
primary or unique key value.

Correct Answer: ABE

Primary key 不允许空值