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）删除列
语法：
例子：删除表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语句可以：
（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）删除约束
例子：删除约束
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）停用和启用约束
停用约束：
启用约束：
例子：停用约束和启用约束
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表示该约束是可延迟验证的。它有两个选项：
其中：
例子：创建延迟约束并验证
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种方式：
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）基于函数的索引
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
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 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
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 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
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 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、删除表
例子：删除表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、外部表
语法：
例子：创建目录对象及外部表
先创建目录对象
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、总结
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
| 欢迎光临 重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/) | Powered by Discuz! X3.2 |
