OCP课程15：SQL之管理模式对象

stonebox · 发表于 2015-12-14 12:51:03

1、使用alter table语句

使用alter table语句可以：

增加列
修改列
删除列
停用列
重命名列
增加约束

（1）增加列

不能为新增加的列指定位置，只能位于最后
如果其他列有数据，强制增加非空列必须指定默认值
如果其他列没有数据（空表），可以增加不指定默认值的非空列

语法：

登录/注册后可看大图

例子：为表dept80增加一个字段job_id

SQL> conn hr/hr

Connected.

SQL> create table dept80(employee_id,last_name,annsal,hire_date) as

2 select employee_id,last_name,salary*12,hire_date

3 from employees where department_id=80;

Table created.

SQL> alter table dept80 add(job_id varchar2(9));

Table altered.

例子：为表dept80增加一个非空字段first_name报错

SQL> alter table dept80 add(first_name varchar2(20) not null);

alter table dept80 add(first_name varchar2(20) not null)

*

ERROR at line 1:

ORA-01758: table must be empty to add mandatory (NOT NULL) column

例子：为表dept80增加一个非空字段first_name，默认值为aa

SQL> alter table dept80 add(first_name varchar2(20) default 'aa' not null);

Table altered.

（2）修改列

可以对列的数据类型，长度和默认值进行修改
对已有数据的列进行类型修改，必须要可以转换才行
对列长度的修改不能小于已有内容的长度
针对默认值的修改只影响后续插入的值

语法：

登录/注册后可看大图

例子：修改表dept80中last_name的列长度为30

SQL> alter table dept80 modify(last_name varchar2(30));

Table altered.

例子：修改表dept80中last_name的类型为date报错，类型为char可以

SQL> alter table dept80 modify(last_name date);

alter table dept80 modify(last_name date)

*

ERROR at line 1:

ORA-01439: column to be modified must be empty to change datatype

SQL> alter table dept80 modify(last_name char(30));

Table altered.

例子：修改表dept80中first_name的长度为1报错

SQL> alter table dept80 modify(first_name varchar2(1));

alter table dept80 modify(first_name varchar2(1))

*

ERROR at line 1:

ORA-01401: inserted value too large for column

（3）删除列

drop的列可以包含数据，也可以不包含数据
最后一列不能被drop
drop的列不能恢复
drop父键列需要使用cascade constraints
包含大量数据的列，建议使用unused
分区表的分区键列和索引组织表的主键列不能被drop

语法：

登录/注册后可看大图

例子：删除表dept80的一个字段

SQL> alter table dept80 drop column job_id;

Table altered.

例子：删除表dept80的多个字段

SQL> alter table dept80 drop(first_name,last_name);

Table altered.

一次删除多个字段，只能使用括号这种方式

例子：创建一个表dept3，为列department_id增加主键，然后删除列department_id

SQL> create table dept3 as select * from departments;

Table created.

SQL> alter table dept3 add constraint dept_dt_pk primary key(department_id);

Table altered.

SQL> alter table dept3 drop(department_id);

Table altered.

（4）set unused选项

Oracle删除字段后会回收空间，但是如果表很大，那么删除回收就很慢，可以用set unused这个选项，不马上回收空间，只是标记这个字段不可用了，达到快速屏蔽掉某个字段的目的，后续在业务比较闲的时候可以使用drop unused columns去回收空间。

在将字段set unused后，select查询和desc都将看不到该字段，不会进入回收站，可以重新增加一个名称和类型一样的字段，通过USER_UNUSED_COL_TAB数据字典视图查询set unused信息。

set unused 后该字段的索引、约束都会被立即删除，含该字段的视图,状态变为不可用,必须修改这个视图后才可使用。

语法：

登录/注册后可看大图

例子：将表dept80中的字段annsal设置为不可用，通过数据字典视图查询信息并使用drop unused columns删除

SQL> desc dept80;

Name Null? Type

----------------------------------------- -------- ----------------------------

EMPLOYEE_ID NUMBER(6)

ANNSAL NUMBER

HIRE_DATE NOT NULL DATE

SQL> alter table dept80 set unused(annsal);

Table altered.

SQL> desc dept80;

Name Null? Type

----------------------------------------- -------- ----------------------------

EMPLOYEE_ID NUMBER(6)

HIRE_DATE NOT NULL DATE

SQL> select table_name,column_name from user_tab_columns where table_name='DEPT80';

TABLE_NAME COLUMN_NAME

------------------------------ ------------------------------

DEPT80 EMPLOYEE_ID

DEPT80 HIRE_DATE

SQL> select * from user_unused_col_tabs;

TABLE_NAME COUNT

------------------------------ ----------

DEPT80 1

SQL> alter table dept80 drop unused columns;

Table altered.

SQL> select * from user_unused_col_tabs;

no rows selected

（5）重命名列

例子：重命名表dept80的列employee_id为emp_id

SQL> alter table dept80 rename column employee_id to empid;

Table altered.

2、管理约束

使用alter table语句可以：

增加或者删除约束
启用或者禁用约束
使用modify子句在字段级别增加not null约束

（1）增加约束

增加约束的语法：

登录/注册后可看大图

例子：为表emp2增加主键和外键约束

SQL> create table emp2 as select * from employees;

Table created.

在列级增加主键约束

SQL> alter table emp2 modify employee_id primary key;

Table altered.

在列级增加主键约束并指定约束名字

SQL> create table dept4 as select * from departments;

Table created.

SQL> alter table dept4 modify(constraint dept_dt_pk primary key(department_id));

Table altered.

SQL> alter table dept4 drop constraint dept_dt_pk;

Table altered.

另外一种方式

SQL> alter table dept4 modify(department_id constraint dept_dt_pk primary key);

Table altered.

在表级增加外键约束

SQL> alter table emp2 add constraint emp_mgr_fk

2 foreign key(manager_id) references emp2(employee_id);

Table altered.

例子：为表emp2增加非空约束

SQL> alter table emp2 add constraint emp_salary_nn not null(salary);

alter table emp2 add constraint emp_salary_nn not null(salary)

*

ERROR at line 1:

ORA-00904: : invalid identifier

非空约束只能在列级增加

SQL> alter table emp2 modify(salary constraint emp_salary_nn not null);

Table altered.

（2）删除约束

删除一个有外键引用的字段，需要加上cascade constraints，才能删掉，对应的外键约束都会被删掉，但是子表的数据不会受到影响，要注意和前面删除记录用的on delete cascade区分开来
如果是多字段的联合约束，在删除一个字段的时候，加了cascade constraints，也会把多字段约束删掉
删除字段加cascade constraints，只删除约束加cascade

例子：删除约束

SQL> alter table emp2 drop constraint emp_mgr_fk;

Table altered.

例子：级联删除主键外键约束

SQL> drop table dept2;

Table dropped.

SQL> create table dept2 as select * from departments;

Table created.

SQL> alter table dept2 add constraint dept_dt_pk primary key(department_id);

Table altered.

SQL> alter table emp2 add constraint emp2_dt_fk foreign key(department_id) references dept2(department_id);

Table altered.

SQL> alter table dept2 drop primary key;

alter table dept2 drop primary key

*

ERROR at line 1:

ORA-02273: this unique/primary key is referenced by some foreign keys

SQL> alter table dept2 drop primary key cascade;

Table altered.

SQL> drop table dept2;

Table dropped.

例子：使用 cascade constraint选项可以删除主键列及其外键约束

SQL> conn hr/hr

Connected.

SQL> drop table dept3;

Table dropped.

SQL> drop table dept4;

Table dropped.

SQL> create table dept2 as select * from departments;

Table created.

在表级增加主键约束

SQL> alter table dept2 add constraint dept_dt_pk primary key(department_id);

Table altered.

在表emp2上面增加参考表dept2列department_id的外键

SQL> alter table emp2 add constraint emp_dt_fk foreign key(department_id) references dept2(department_id);

Table altered.

直接删除表dept2的父键列department_id报错

SQL> alter table dept2 drop(department_id);

alter table dept2 drop(department_id)

*

ERROR at line 1:

ORA-12992: cannot drop parent key column

加上cascade constraint就可以删除了

SQL> alter table dept2 drop(department_id) cascade constraint;

Table altered.

这两个表中列department_id上面的约束都被删掉了

SQL> select constraint_name,table_name,column_name from user_cons_columns where (table_name='EMP2' or table_name='DEPT2') and column_name='DEPARTMENT_ID';

no rows selected

（3）停用和启用约束

停用约束：

可以在create table或者alter table中使用disable语句停用约束
使用cascade关键字停用依赖的约束
停用unique或者primary key约束会移除unique index

启用约束：

可以在create table或者alter table中使用enable语句启用约束
启用unique或者primary key约束会自动创建unique index
启用使用cascade选项停用的主键约束，不会同时启用依赖该主键约束的外键约束
启用unique或者primary key约束需要有对这个表创建index的权限

例子：停用约束和启用约束

SQL> alter table emp2 add constraint emp2_dept2_fk foreign key(department_id) references dept2(department_id);

Table altered.

SQL> select constraint_name,table_name,status from user_constraints where constraint_name='EMP2_DEPT2_FK';

CONSTRAINT_NAME TABLE_NAME STATUS

------------------------------ ------------------------------ --------

EMP2_DEPT2_FK EMP2 ENABLED

SQL> alter table emp2 disable constraint emp2_dept2_fk;

Table altered.

SQL> select constraint_name,table_name,status from user_constraints where constraint_name='EMP2_DEPT2_FK';

CONSTRAINT_NAME TABLE_NAME STATUS

------------------------------ ------------------------------ --------

EMP2_DEPT2_FK EMP2 DISABLED

SQL> alter table emp2 enable constraint emp2_dept2_fk;

Table altered.

SQL> select constraint_name,table_name,status from user_constraints where constraint_name='EMP2_DEPT2_FK';

CONSTRAINT_NAME TABLE_NAME STATUS

------------------------------ ------------------------------ --------

EMP2_DEPT2_FK EMP2 ENABLED

例子：当父键使用cascade进行disable后，子健也会被disable，但是当父键重新enable后，子健不会自动enable，只能手动enable。同时在启用唯一键或者主键的时候，会自动创建唯一索引，禁用的时候，会自动删除唯一索引

SQL> select constraint_name,table_name,status from user_constraints where constraint_name like '%DEPT%' and (table_name='EMP2' or table_name='DEPT2');

CONSTRAINT_NAME TABLE_NAME STATUS

------------------------------ ------------------------------ --------

DEPT2_ID_PK DEPT2 ENABLED

EMP2_DEPT2_FK EMP2 ENABLED

SQL> select index_name,uniqueness from user_indexes where table_name='DEPT2';

INDEX_NAME UNIQUENES

------------------------------ ---------

DEPT2_ID_PK UNIQUE

SQL> alter table dept2 disable constraint dept2_id_pk;

alter table dept2 disable constraint dept2_id_pk

*

ERROR at line 1:

ORA-02297: cannot disable constraint (HR.DEPT2_ID_PK) - dependencies exist

SQL> alter table dept2 disable constraint dept2_id_pk cascade;

Table altered.

SQL> select constraint_name,table_name,status from user_constraints where constraInt_name like '%DEPT%' and (table_name='EMP2' or table_name='DEPT2');

CONSTRAINT_NAME TABLE_NAME STATUS

------------------------------ ------------------------------ --------

DEPT2_ID_PK DEPT2 DISABLED

EMP2_DEPT2_FK EMP2 DISABLED

SQL> select index_name,uniqueness from user_indexes where table_name='DEPT2';

no rows selected

SQL> alter table dept2 enable constraint dept2_id_pk cascade;

alter table dept2 enable constraint dept2_id_pk cascade

*

ERROR at line 1:

ORA-00933: SQL command not properly ended

SQL> alter table dept2 enable constraint dept2_id_pk;

Table altered.

SQL> select constraint_name,table_name,status from user_constraints where constraInt_name like '%DEPT%' and (table_name='EMP2' or table_name='DEPT2');

CONSTRAINT_NAME TABLE_NAME STATUS

------------------------------ ------------------------------ --------

DEPT2_ID_PK DEPT2 ENABLED

EMP2_DEPT2_FK EMP2 DISABLED

SQL> select index_name,uniqueness from user_indexes where table_name='DEPT2';

INDEX_NAME UNIQUENES

------------------------------ ---------

DEPT2_ID_PK UNIQUE

SQL> alter table emp2 enable constraint emp2_dept2_fk;

Table altered.

SQL> select constraint_name,table_name,status from user_constraints where constraInt_name like '%DEPT%' and (table_name='EMP2' or table_name='DEPT2');

CONSTRAINT_NAME TABLE_NAME STATUS

------------------------------ ------------------------------ --------

DEPT2_ID_PK DEPT2 ENABLED

EMP2_DEPT2_FK EMP2 ENABLED

（4）延迟约束

Deferrable表示该约束是可延迟验证的。它有两个选项：

initially immediate（默认）：立即验证，执行完一个sql后就进行验证；
initially deferred：延迟验证，当事务提交时或调用set constraint [all | ]immediate语句时才验证。这两个区别是：initially deferred，事务提交时验证不通过，则立即回滚事务；set constraint immediate时只验证，不回滚事务。

登录/注册后可看大图

其中：

initially immediate为默认值
如果没有指定deferrable，则语句执行时会立即检查约束
如果创建的主键或者唯一键是延迟约束，那么自动生成的索引是非唯一索引

例子：创建延迟约束并验证

SQL> create table emp_new_sal(

2 salary number constraint sal_ck check(salary>1000) deferrable initially immediate,

3 bonus number constraint bonus_ck check(bonus>0) deferrable initially deferred);

Table created.

SQL> insert into emp_new_sal values(90,5);

insert into emp_new_sal values(90,5)

*

ERROR at line 1:

ORA-02290: check constraint (HR.SAL_CK) violated

SQL> insert into emp_new_sal values(1100,-1);

1 row created.

SQL> commit;

commit

*

ERROR at line 1:

ORA-02091: transaction rolled back

ORA-02290: check constraint (HR.BONUS_CK) violated

SQL> set constraint sal_ck deferred;

Constraint set.

SQL> insert into emp_new_sal values(90,5);

1 row created.

SQL> commit;

commit

*

ERROR at line 1:

ORA-02091: transaction rolled back

ORA-02290: check constraint (HR.SAL_CK) violated

SQL> set constraint bonus_ck immediate;

Constraint set.

SQL> insert into emp_new_sal values(1100,-1);

insert into emp_new_sal values(1100,-1)

*

ERROR at line 1:

ORA-02290: check constraint (HR.BONUS_CK) violated

也可以使用下面的语句进行设置

SQL> alter session set constraints=immediate;

Session altered.

没有使用deferrable设置的约束不能重新设置为deferred

SQL> set constraint dept2_id_pk deferred;

set constraint dept2_id_pk deferred

*

ERROR at line 1:

ORA-02447: cannot defer a constraint that is not deferrable

如果创建的主键或者唯一键是延迟约束，那么自动生成的索引是非唯一索引

SQL> alter table dept3 add constraint dept3_id_pk primary key(department_id) deferrable initially deferred;

Table altered.

SQL> select index_name,uniqueness from user_indexes where table_name='DEPT3';

INDEX_NAME UNIQUENES

------------------------------ ---------

DEPT3_ID_PK NONUNIQUE

而且禁用约束后，不会去删除这个索引

SQL> alter table dept3 disable constraint dept3_id_pk;

Table altered.

SQL> select index_name,uniqueness from user_indexes where table_name='DEPT3';

INDEX_NAME UNIQUENES

------------------------------ ---------

DEPT3_ID_PK NONUNIQUE

（5）重命名约束

例子：重命名表dept3的约束dept3_id_pk为pk_dept3_id

SQL> alter table dept3 rename constraint dept3_id_pk to pk_dept3_id;

Table altered.

SQL> select constraint_name,table_name from user_constraints where table_name='DEPT3';

CONSTRAINT_NAME TABLE_NAME

------------------------------ ------------------------------

PK_DEPT3_ID DEPT3

3、索引

（1）创建索引

创建索引的2种方式：

自动创建索引（unique index）：创建primary key和unique约束时，索引的名字就是约束的名字
手动创建索引（nonunique index）：使用create index或create table
可以使用CREATE UNIQUE INDEX语句手动创建unique index

Indexes can be unique or non-unique. Unique indexes guarantee that no two rows of a table have duplicate values in the key column (or columns). Non-unique indexes do not impose this restriction on the column values. Use the CREATE UNIQUE INDEX statement to create a unique index. This statement creates a unique index. Alternatively, you can define UNIQUE integrity constraints on the desired columns. The database enforces UNIQUE integrity constraints by automatically defining a unique index on the unique key. However, it is advisable that any index that exists for query performance, including unique indexes, be created explicitly. Unique and Nonunique Indexes

Indexes can be unique or nonunique. Unique indexes guarantee that no two rows of a table have duplicate values in the key column or column. For example, no two employees can have the same employee ID. Thus, in a unique index, one rowid exists for each data value. The data in the leaf blocks is sorted only by key.

Nonunique indexes permit duplicates values in the indexed column or columns. For example, the first_name column of the employees table may contain multiple Mike values. For a nonunique index, the rowid is included in the key in sorted order, so nonunique indexes are sorted by the index key and rowid (ascending).

例子：在创建表的时候创建索引

SQL> create table new_emp(

2 employee_id number(6) primary key using index(create index emp_id_idx on new_emp(employee_id)),

3 first_name varchar2(20),

4 last_name varchar2(25));

Table created.

SQL> select index_name,table_name,uniqueness from user_indexes where table_name='NEW_EMP';

INDEX_NAME TABLE_NAME UNIQUENES

------------------------------ ------------------------------ ---------

EMP_ID_IDX NEW_EMP NONUNIQUE

这种方式创建的索引是一个非唯一索引，而且还可以通过这种方式改变默认设置，将索引和表放在不同的表空间。

目前索引和表都位于users表空间

SQL> select index_name,tablespace_name,uniqueness from user_indexes where table_name='NEW_EMP';

INDEX_NAME TABLESPACE_NAME UNIQUENES

------------------------------ ------------------------------ ---------

EMP_ID_IDX USERS NONUNIQUE

SQL> select table_name,tablespace_name from user_tables where table_name='NEW_EMP';

TABLE_NAME TABLESPACE_NAME

------------------------------ ------------------------------

NEW_EMP USERS

删除表，重新创建表并指定索引所在表空间

SQL> drop table new_emp;

Table dropped.

SQL> create table new_emp(

2 employee_id number(6) primary key using index(create index emp_id_idx on new_emp(employee_id) tablespace example),

3 first_name varchar2(20),

4 last_name varchar2(25));

Table created.

SQL> select index_name,tablespace_name from user_indexes where table_name='NEW_EMP';

INDEX_NAME TABLESPACE_NAME

------------------------------ ------------------------------

EMP_ID_IDX EXAMPLE

SQL> select table_name,tablespace_name from user_tables where table_name='NEW_EMP';

TABLE_NAME TABLESPACE_NAME

------------------------------ ------------------------------

NEW_EMP USERS

可以通过重建的方式改变索引的表空间

SQL> alter index emp_id_idx rebuild tablespace users;

Index altered.

SQL> select index_name,tablespace_name from user_indexes where table_name='NEW_EMP';

INDEX_NAME TABLESPACE_NAME

------------------------------ ------------------------------

EMP_ID_IDX USERS

（2）基于函数的索引

如果在有索引的字段上面使用了函数，那么在实际的查询过程中，将不会使用索引，这时就需要对整个函数创建索引
由query_rewrite_enabled参数控制是否使用基于函数的索引

SQL> conn / as sysdba

Connected.

SQL> show parameter query_rewrite_enable;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

query_rewrite_enabled string TRUE

SQL> conn hr/hr

Connected.

例子：查看索引字段使用函数后的执行计划以及对整个函数创建索引后的执行计划

SQL> create index dept2_name_idx on dept2(department_name);

Index created.

SQL> select index_name,table_name from user_indexes where table_name='DEPT2';

INDEX_NAME TABLE_NAME

------------------------------ ------------------------------

DEPT2_ID_PK DEPT2

DEPT2_NAME_IDX DEPT2

查看没有使用函数前的执行计划，使用了索引

SQL> explain plan for select * from dept2 where department_name='SALES';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

Plan hash value: 1462790849

----------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 21 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| DEPT2 | 1 | 21 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | DEPT2_NAME_IDX | 1 | | 1 (0)| 00:00:01 |

----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

---------------------------------------------------

2 - access("DEPARTMENT_NAME"='SALES')

14 rows selected.

查看使用函数后的执行计划，没有使用索引

SQL> explain plan for select * from dept2 where upper(department_name)='SALES';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

Plan hash value: 3960531969

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 21 | 3 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| DEPT2 | 1 | 21 | 3 (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

1 - filter(UPPER("DEPARTMENT_NAME")='SALES')

13 rows selected.

创建基于函数的索引

SQL> create index dept2_name_fidx on dept2(UPPER("DEPARTMENT_NAME"));

Index created.

再次查看执行计划，使用了基于函数的索引

SQL> explain plan for select * from dept2 where upper(department_name)='SALES';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

Plan hash value: 1567914267

-----------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 21 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| DEPT2 | 1 | 21 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | DEPT2_NAME_FIDX | 1 | | 1 (0)| 00:00:01 |

-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

---------------------------------------------------

2 - access(UPPER("DEPARTMENT_NAME")='SALES')

14 rows selected.

（3）删除索引

语法：

登录/注册后可看大图

例子：删除刚刚创建的基于函数的索引

SQL> drop index dept2_name_fidx;

Index dropped.

4、删除表

直接使用drop删除表，只是将表放入了回收站，硬盘空间没有回收，在回收站进行了删除后，才回收硬盘空间
使用purge选项删除表，表示彻底删除，同时回收硬盘空间

例子：删除表emp2，进入回收站

SQL> drop table emp2;

Table dropped.

SQL> show recyclebin;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

---------------- ------------------------------ ------------ -------------------

EMP2 BIN$I7afhEQkJmvgU4rmqMCi5Q==$0 TABLE 2015-11-04:19:52:16

例子：彻底删除表dept80

SQL> show recyclebin;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

---------------- ------------------------------ ------------ -------------------

EMP2 BIN$I7afhEQkJmvgU4rmqMCi5Q==$0 TABLE 2015-11-04:19:52:16

SQL> drop table dept80 purge;

Table dropped.

SQL> show recyclebin;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

---------------- ------------------------------ ------------ -------------------

EMP2 BIN$I7afhEQkJmvgU4rmqMCi5Q==$0 TABLE 2015-11-04:19:52:16

例子：清空回收站

SQL> purge recyclebin;

Recyclebin purged.

5、闪回表

删除后进入回收站的表可以通过闪回恢复
闪回恢复后，依赖表的对象，比如约束、索引等，名字依然使用回收站里面的名字，而不是原来的名字
外键的约束不随表恢复

语法：

登录/注册后可看大图

例子：删除表后再恢复

SQL> drop table dept2;

Table dropped.

SQL> select original_name,operation,droptime from recyclebin;

ORIGINAL_NAME OPERATION DROPTIME

-------------------------------- --------- -------------------

DEPT2_NAME_IDX DROP 2015-11-04:20:03:02

DEPT2_ID_PK DROP 2015-11-04:20:03:02

DEPT2 DROP 2015-11-04:20:03:02

SQL> flashback table dept2 to before drop;

Flashback complete.

SQL> select original_name,operation,droptime from recyclebin;

no rows selected

SQL> select index_name,table_name from user_indexes where table_name='DEPT2';

INDEX_NAME TABLE_NAME

------------------------------ ------------------------------

BIN$I7bK2SrbJu3gU4rmqMBihg==$0 DEPT2

BIN$I7bK2SraJu3gU4rmqMBihg==$0 DEPT2

6、外部表

主要用于外部文件的导入
通过目录对象指定外部表的路径，故需要先创建目录对象并赋予权限
外部表不占用数据库的空间
外部表不能创建索引，不支持DML操作

语法：

登录/注册后可看大图

例子：创建目录对象及外部表

先创建目录对象

SQL> conn / as sysdba

Connected.

SQL> !pwd

/home/oracle

SQL> !ls

database sql.txt

SQL> !mkdir emp_dir

SQL> !ls

database emp_dir sql.txt

SQL> create or replace directory emp_dir as '/home/oracle/emp_dir';

Directory created.

SQL> grant read,write on directory emp_dir to hr;

Grant succeeded.

SQL> select * from all_directories where directory_name like '%EMP%';

OWNER DIRECTORY_NAME DIRECTORY_PATH

------------------------------ ------------------------------ ------------------------------

SYS EMP_DIR /home/oracle/emp_dir

SQL> !vi emp_dir/emp.dat

Ellen Abel

Sundar Ande

Mozhe Atkinson

David Austin

Hermann Baer

Shelli Baida

Amit Banda

Elizabeth Bates

Sarah Bell

David Bernstein

最后创建外部表

SQL> conn hr/hr

Connected.

SQL> create table old_emp(

2 fname char(25),

3 lname char(25))

4 organization external(

5 type oracle_loader

6 default directory emp_dir

7 access parameters(

8 records delimited by newline

9 nobadfile

10 nologfile

11 fields terminated by ' '

12 (fname position(1:20) char,lname position(22:41) char))

13 location('emp.dat'))

14 parallel 5

15 reject limit 200;

Table created.

查询外部表

SQL> select * from old_emp;

FNAME LNAME

------------------------- -------------------------

Ellen Abel

Sundar Ande

Mozhe Atkinson

David Austin

Hermann Baer

Shelli Baida

Amit Banda

Elizabeth Bates

Sarah Bell

David Bernstein

10 rows selected.

在外部表上面创建索引报错

SQL> create index old_emp_idx on old_emp(lname);

create index old_emp_idx on old_emp(lname)

*

ERROR at line 1:

ORA-30657: operation not supported on external organized table

在外部表上面执行DML操作报错

SQL> delete from old_emp;

delete from old_emp

*

ERROR at line 1:

ORA-30657: operation not supported on external organized table

通过外部表创建表

SQL> create table new_emp1 as select * from old_emp;

Table created.

通过外部表创建视图

SQL> create table old_empvu as select * from old_emp;

Table created.

6、总结

Add constraints
Create indexes
Create a primary key constraint using an index
Create indexes using the CREATE TABLE statement
Create function-based indexes
Drop columns and set column UNUSED
Perform FLASHBACK operations
Create and use external tables

7、相关习题

（1）View the Exhibit and examine the structure of the EMPtable which is not partitioned and not an index-organized table. Evaluate the following SQL statement: ALTER TABLE emp DROP COLUMN first_name? Which two statements are true regarding the above command? (Choose two.)

A.The FIRST_NAME column would be dropped provided it does not contain any data.

B.The FIRST_NAME column would be dropped provided at least one or more columns remain in the table.

C.The FIRST_NAME column can be rolled back provided the SET UNUSED option is added to the above SQL statement.

D.The FIRST_NAME column can be dropped even if it is part of a composite PRIMARY KEY provided the CASCADE option is used.

答案：BD

（2）EMPDET is an external table containing the columns EMPNO and ENAME.Which command would work in relation to the EMPDET table ?

A.UPDATE empdet SET ename = 'Amit' WHERE empno = 1234

B.DELETE FROM empdet WHERE ename LIKE 'J%'

C.CREATE VIEW empvu AS SELECT * FROM empdept

D.CREATE INDEX empdet_idx ON empdet(empno)

答案：C

（3）View the Exhibit and examine the structure of the CUST table. Evaluate the following SQL statements executed in the given order: ALTER TABLE cust ADD CONSTRAINT cust_id_pk PRIMARY KEY(cust_id) DEFERRABLE INITIALLY DEFERRED? INSERT INTO cust VALUES (1,'RAJ')? row 1 INSERT INTO cust VALUES (1,'SAM')? row 2 COMMIT? SET CONSTRAINT cust_id_pk IMMEDIATE? INSERT INTO cust VALUES (1,'LATA')? row 3 INSERT INTO cust VALUES (2,'KING')? row 4 COMMIT? Which rows would be made permanent in the CUST table?

登录/注册后可看大图

A.row 4 only

B.rows 2 and 4

C.rows 3 and 4

D.rows 1 and 4

答案：C

（4）Which statement is true regarding external tables?

A.The default REJECT LIMIT for external tables is UNLIMITED.

B.The data and metadata for an external table are stored outside the database.

C.ORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when used with an external table.

D.The CREATE TABLE AS SELECT statement can be used to unload data into regular table in the database from an external table.

答案：D

（5）View the Exhibit and examine the structure of the EMP table. You executed the following
command to add a primary key to the EMP table:

ALTER TABLE emp ADD CONSTRAINT emp_id_pk PRIMARY KEY (emp_id) USING INDEX emp_id_idx?

Which statement is true regarding the effect of the command?

登录/注册后可看大图

A.The PRIMARY KEY is created along with a new index.
B.The PRIMARY KEY is created and it would use an existing unique index.
C.The PRIMARY KEY would be created in a disabled state because it is using an existing index.
D.The statement produces an error because the USING clause is permitted only in the CREATE TABLE
command.

答案：B

（6）Evaluate the following CREATE TABLE command: CREATE TABLE order_item (order_id NUMBER(3), item_id NUMBER(2), qty NUMBER(4), CONSTRAINT ord_itm_id_pk PRIMARY KEY (order_id,item_id) USING INDEX (CREATE INDEX ord_itm_idx ON order_item(order_id,item_id)))；Which statement is true regarding the above SQL statement?

A.It would execute successfully and only ORD_ITM_IDX index would be created.

B.It would give an error because the USING INDEX clause cannot be used on a composite primary key.

C.It would execute successfully and two indexes ORD_ITM_IDX and ORD_ITM_ID_PK would be created.

D.It would give an error because the USING INDEX clause is not permitted in the CREATE TABLE command.

答案：A

（7）Which mandatory clause has to be added to the following statement to successfully create an external table called EMPDET;CREATE TABLE empdet (empno CHAR(2), ename CHAR(5), deptno NUMBER(4)) ORGANIZATION EXTERNAL (LOCATION ('emp.dat'));

A.TYPE

B.REJECTLIMIT

C.DEFAULT DIRECTORY

D.ACCESS PARAMETERS

答案：C

（8）The first DROP operation is performed on PRODUCTS table using the following command: DROP TABLE products PURGE；Then you performed the FLASHBACK operation by using the following command: FLASHBACK TABLE products TO BEFORE DROP; Which statement describes the outcome of the FLASHBACK command ?

A.It recovers only the table structure.
B.It recovers the table structure, data, and the indexes.
C.It recovers the table structure and data but not the related indexes.
D.It is not possible to recover the table structure, data, or the related indexes.

答案：D

（9）Evaluate the following SQL statements that are issued in the given order: CREATE TABLE emp (emp_no NUMBER(2) CONSTRAINT emp_emp_no_pk PRIMARY KEY,ename VARCHAR2(15), salary NUMBER(8,2), mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp);ALTER TABLE emp DISABLE CONSTRAINT emp_emp_no_pk CASCADE? ALTER TABLE emp ENABLE CONSTRAINT emp_emp_no_pk? What would be the status of the foreign key EMP_MGR_FK?

A.It would be automatically enabled and deferred.

B.It would be automatically enabled and immediate.

C.It would remain disabled and has to be enabled manually using the ALTER TABLE command.

D.It would remain disabled and can be enabled only by dropping the foreign key constraint and re?creating it.

答案：C

（10）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 synonym existing on the EMP table would have to be re-created.

B.Any constraints defined on the MGR_ID column would be removed by the above command.

C.Any views created on the EMP table that include the MGR_ID column would have to be dropped and re-created.

D.Any index created on the MGR_ID column would continue to exist until the DROP UNUSED COLUMNS command is executed.

答案：B

（11）Evaluate the following ALTER TABLE statement: ALTER TABLE orders SET UNUSED order_date;Which statement is true?

A.The DESCRIBE command would still display the ORDER_DATE column.
B.ROLLBACK can be used to get back the ORDER_DATE column in the ORDERS table.
C.The ORDER_DATE column should be empty for the ALTER TABLE command to execute successfully.
D.After executing the ALTER TABLE command, you can add a new column called ORDER_DATE to the ORDERS table.

答案：D

（12）View the Exhibit and examine the data in the PRODUCTS table. Which statement would add a column called PRICE, which cannot contain NULL?

A. ALTER TABLE products ADD price NUMBER(8,2) NOT NULL;

B. ALTER TABLE products ADD price NUMBER(8,2) DEFAULT NOT NULL;

C. ALTER TABLE products ADD price NUMBER(8,2) DEFAULT 0 NOT NULL;

D. ALTER TABLE products ADD price NUMBER(8,2) DEFAULT CONSTRAINT p_nn NOT NULL;

答案：C

（13）View the Exhibit and examine the data in EMP and DEPT tables. In the DEPT table, DEPTNO is the PRIMARY KEY. In the EMP table, EMPNO is the PRIMARY KEY and DEPTNO is the FOREIGN KEY referencing the DEPTNO column in the DEPT table. What would be the outcome of the following statements executed in the given sequence? DROP TABLE emp；FLASHBACK TABLE emp TO BEFORE DROP；INSERT INTO emp VALUES (2,COTT 10)；INSERT INTO emp VALUES (3,ING 55)；

登录/注册后可看大图

A.Both the INSERT statements would fail because all constraints are automatically retrieved when the table is flashed back.
B.Both the INSERT statements would succeed because none of the constraints on the table are automatically retrieved when the table is flashed back.
C.Only the first INSERT statement would succeed because all the constraints except the primary key constraint are automatically retrieved after a table is flashed back.
D.Only the second INSERT statement would succeed because all the constraints except referential integrity constraints that reference other tables are retrieved automatically after the table is flashed back.

答案：D

帐号		自动登录	找回密码
密码			注册

[认证考试] OCP课程15：SQL之管理模式对象