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 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之管理模式物件

相關文章