OCP-052-91

91. Examine these facts about a database.
1. The database default tabpespace is USERS.
2. DEFERRED_SEGMENT_CREATION is TRUE.
3. The default tablespace of USER1 is tbs1.
4. USER1 has only these privileges:
•
CREATE SESSION
•
CREATE TABLE
•
UNLIMITED quota on tbs1
Examine these commands executed by USER1:
SQL> CREATE TABLE emp (eno NUMBER, ename VARCHAR2(20)) TABLESPACE TBS1;
Table created.
SQL> CREATE INDEX emp_inx ON emo(eno) TABLESPACE USERS;
Index created.
SQL> INSERT INTO emp VALUES (NULL,’Alan’);
What will be the outcome of the INSERT operation and why?
A) It will fail because an indexed column cannot have NULL values.
B) A row will be inserted into EMP and an index entry will be made into EMP_IDX.
C) It will fail because USER1 has no quota on USERS.
D) A row will be inserted into EMP and an index entry will be inserted into a virtual column of EMP
because USER1 has no quota an USERS.
E) A row will be inserted into EMP but no index entry will be made into EXP_IDX.
Answer：C

实验步骤：

SQL> show parameter defe

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE
SQL> 
创建用户，指定默认表空间
SQL> create user user1 identified by user1 default tablespace tbs1;

User created.

授予相应权限

SQL> grant create session to user1;

Grant succeeded.

SQL> grant create table to user1;

Grant succeeded.
SQL> alter user user1 quota unlimited on tbs1;

User altered.

使用user1用户登录数据库
SQL> conn user1/user1
Connected.
创建表，指定表空间tbs1
SQL> cREATE TABLE emp (eno NUMBER, ename VARCHAR2(20)) TABLESPACE tbs1;

Table created.

创建索引，默认表空间users

SQL> CREATE INDEX emp_inx ON emp(eno) TABLESPACE USERS

Index created.

插入数据时报错提示没有权限，由此可以排除答案B\DE

SQL> insert into emp values(null,'alan');
insert into emp values(null,'alan')
            *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

以下步骤验证数据插入失败原因，用户user1所创建的index没有写入users表空间数据的权限，而不是null和index的原因，排除答案A
SQL> drop index emp_inx;

Index dropped.

SQL> insert into emp values(null,'alan');

1 row created.

SQL> rollback
  2  ;

Rollback complete.

SQL> drop index emp_inx;
drop index emp_inx
           *
ERROR at line 1:
ORA-01418: specified index does not exist

SQL> CREATE INDEX emp_inx ON emp(eno) TABLESPACE ceshi;

Index created.

SQL> insert into emp values(null,'alan');

1 row created.

SQL>