Managing Schema Objects管理模式对象
you should be able to: 1、Manage constraints 2、Create and use temporary tables 3、Create and use external tables
Use the ALTER TABLE statement to: 1、Add or drop a constraint, but not to modify its structure 2、Enable or disable constraints 3、Add a NOT NULL constraint by using the MODIFY clause
ALTER TABLE <table_name> ADD [CONSTRAINT <constraint_name>] type (<column_name>);
ALTER TABLE emp2 MODIFY employee_id PRIMARY KEY;
ALTER TABLE emp2 ADD CONSTRAINT emp_mgr_fk FOREIGN KEY(manager_id) REFERENCES emp2(employee_id);
ALTER TABLE emp2 DROP CONSTRAINT emp_mgr_fk;
ALTER TABLE emp2 DROP PRIMARY KEY CASCADE; 级联删除
ALTER TABLE myemp2 DROP CONSTRAINT emp_name_pk ONLINE;
ALTER TABLE dept2 ADD CONSTRAINT dept_lc_fk FOREIGN KEY (location_id) REFERENCES locations(location_id) ON DELETE CASCADE; 允许级联删除数据
ALTER TABLE emp2 ADD CONSTRAINT emp_dt_fk FOREIGN KEY (Department_id) REFERENCES departments(department_id) ON DELETE SET NULL; 允许级联设置空值
ALTER TABLE emp2 DROP COLUMN employee_id CASCADE CONSTRAINTS;
ALTER TABLE test1 DROP (col1_pk, col2_fk, col1);
ALTER TABLE marketing RENAME to new_marketing; 改表名
ALTER TABLE new_marketing RENAME COLUMN team_id TO id; 改列名
ALTER TABLE new_marketing RENAME CONSTRAINT mktg_pk TO new_mktg_pk; 改约束名
ALTER TABLE emp2 DISABLE CONSTRAINTS emp_dt_pk;
ALTER TABLE dept2 DISABLE primary key CASCADE;
ALTER TABLE emp2 ENABLE CONSTRAINT emp_dt_fk;
A UNIQUE index is automatically created if you enable a UNIQUE key or a PRIMARY KEY constraint. 禁用主键或唯一约束时（自动创建的唯一索引） ，其索引也会自动删除。
An integrity constraint defined on a table can be in one of the following states: 1、ENABLE VALIDATE 2、ENABLE NOVALIDATE 3、DISABLE VALIDATE 4、DISABLE NOVALIDATE
ALTER TABLE dept2 ENABLE NOVALIDATE PRIMARY KEY;
Constraints can have the following attributes: DEFERRABLE 延时 or NOT DEFERRABLE 不延时 INITIALLY DEFERRED 初始化延时 or INITIALLY IMMEDIATE 初始化立即生效
ALTER TABLE dept2 ADD CONSTRAINT dept2_id_pk PRIMARY KEY (department_id) DEFERRABLE INITIALLY DEFERRED;
SET CONSTRAINTS dept2_id_pk IMMEDIATE;
ALTER SESSION SET CONSTRAINTS= IMMEDIATE;
CREATE TABLE emp_new_sal (salary NUMBER CONSTRAINT sal_ck CHECK (salary > 100) DEFERRABLE INITIALLY IMMEDIATE, bonus NUMBER CONSTRAINT bonus_ck CHECK (bonus > 0 ) DEFERRABLE INITIALLY DEFERRED );
DROP TABLE emp_new_sal PURGE; 直接删除
** Creating and using temporary tables 创建临时表**
When session/transaction completes
基于事务级别： 此表commit提交后，会自动删除临时表所有数据行，效率高
基于会话级别： 此表commit提交后，数据还在， 重新会话后数据不在
SQL> / 重复上一次操作
CREATE GLOBAL TEMPORARY TABLE cart(n NUMBER,d DATE) ON COMMIT DELETE ROWS;
CREATE GLOBAL TEMPORARY TABLE today_sales ON COMMIT PRESERVE ROWS AS SELECT * FROM orders WHERE order_date = SYSDATE;
Creating a Directory for the External Table 创建外部表 从外部目录加入数据库
CREATE OR REPLACE DIRECTORY emp_dir AS '/…/emp_dir';
GRANT READ ON DIRECTORY emp_dir TO ora_21;
CREATE TABLE <table_name> ( <col_name> <datatype>, … ) ORGANIZATION EXTERNAL (TYPE <access_driver_type> DEFAULT DIRECTORY <directory_name> ACCESS PARAMETERS (… ) ) LOCATION ('<location_specifier>') REJECT LIMIT [0 | <number> | UNLIMITED];
CREATE TABLE oldemp (fname char(25), lname CHAR(25)) ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY emp_dir ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE FIELDS(fname POSITION ( 1:20) CHAR, lname POSITION (22:41) CHAR)) LOCATION ('emp.dat'));
SELECT * FROM oldemp;
CREATE TABLE emp_ext (employee_id, first_name, last_name) ORGANIZATION EXTERNAL ( TYPE ORACLE_DATAPUMP #数据泵 DEFAULT DIRECTORY emp_dir LOCATION ('emp1.exp','emp2.exp') ) PARALLEL AS SELECT employee_id, first_name, last_name FROM employees;
外部表可以实现，通过数据库对象直接访问目录文件里的格式数据，加载方式分为两种oracle_loader和oracle_datapump,oracle_loader方式通过sqlldr引擎方式加载，访问flat格式文件；oracle_datapump通过datapump接口来加载，访问通过oracle_datapump方式卸载的dmp文件；
------oracle_loader ----自定义两个格式文件内容如下： ====a.dat==== 360,Jane,Janus,ST_CLERK,121,17-MAY-2001,3000,0,50,jjanus 361,Mark,Jasper,SA_REP,145,17-MAY-2001,8000,.1,80,mjasper 362,Brenda,Starr,AD_ASST,200,17-MAY-2001,5500,0,10,bstarr 363,Alex,Alda,AC_MGR,145,17-MAY-2001,9000,.15,80,aalda
====b.dat==== 401,Jesse,Cromwell,HR_REP,203,17-MAY-2001,7000,0,40,jcromwel 402,Abby,Applegate,IT_PROG,103,17-MAY-2001,9000,.2,60,aapplega 403,Carol,Cousins,AD_VP,100,17-MAY-2001,27000,.3,90,ccousins 404,John,Richardson,AC_ACCOUNT,205,17-MAY-2001,5000,0,110,jrichard
----创建需要的目录 CONNECT / AS SYSDBA; -- Set up directories and grant access to hr CREATE OR REPLACE DIRECTORY admin_dat_dir AS '/u01/backup'; CREATE OR REPLACE DIRECTORY admin_log_dir AS '/u01/backup'; CREATE OR REPLACE DIRECTORY admin_bad_dir AS '/u01/backup'; GRANT READ ON DIRECTORY admin_dat_dir TO sun; GRANT WRITE ON DIRECTORY admin_log_dir TO sun; GRANT WRITE ON DIRECTORY admin_bad_dir TO sun;
----通过loader方式创建外部表，直接通过表来查询操作 conn sun/sun -- create the external table CREATE TABLE admin_ext_employees (employee_id NUMBER(4), first_name VARCHAR2(20), last_name VARCHAR2(25), job_id VARCHAR2(10), manager_id NUMBER(4), hire_date DATE, salary NUMBER(8,2), commission_pct NUMBER(2,2), department_id NUMBER(4), email VARCHAR2(25) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY admin_dat_dir ACCESS PARAMETERS ( records delimited by newline badfile admin_bad_dir:'empxt%a_%p.bad' logfile admin_log_dir:'empxt%a_%p.log' fields terminated by ',' missing field values are null ( employee_id, first_name, last_name, job_id, manager_id, hire_date char date_format date mask "dd-mon-yyyy", salary, commission_pct, department_id, email ) ) LOCATION ('a.dat', 'b.dat') ) PARALLEL REJECT LIMIT UNLIMITED;
create table emp as select * from admin_ext_employees;
SQL> SQL> SELECT * FROM emp;
EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_ID M
401 Jesse Cromwell HR_REP
402 Abby Applegate IT_PROG
403 Carol Cousins AD_VP
404 John Richardson AC_ACCOUNT
360 Jane Janus ST_CLERK
361 Mark Jasper SA_REP
362 Brenda Starr AD_ASST
363 Alex Alda AC_MGR
SQL> SELECT * FROM user_external_tables ;
no rows selected
[ora11@prim backup]$ ls -l total 20 -rw-r--r-- 1 ora11 oinstall 228 Sep 6 21:17 a.dat -rw-r--r-- 1 ora11 oinstall 252 Sep 6 21:17 b.dat -rw-r--r-- 1 ora11 oinstall 2826 Sep 6 21:19 empxt000_2756.log -rw-r--r-- 1 ora11 oinstall 3110 Sep 6 21:19 empxt000_2763.log -rw-r--r-- 1 ora11 oinstall 3110 Sep 6 21:19 empxt001_2765.log
------oracle_datapump
----更多是实现数据迁移的功能，通过datapump方式卸载并迁移到其他系统；
----创建datapump方式的外部表，并将数据卸载到目录文件中；
SQL> CREATE TABLE admin_ext_employees
2 (employee_id ,
3 first_name ,
4 last_name ,
5 job_id ,
6 manager_id ,
7 hire_date ,
8 salary ,
9 commission_pct ,
10 department_id ,
11 email
12 )
13 ORGANIZATION EXTERNAL
14 (
15 TYPE ORACLE_DATAPUMP
16 DEFAULT DIRECTORY admin_dat_dir
17 LOCATION ('emp2.dmp'))
18 AS SELECT * FROM emp;
Table created.
----查询文件内容，大体是产生XML格式的文件 -rw-r----- 1 ora11 oinstall 12288 Sep 6 21:33 emp2.dmp [ora11@prim backup]$ strings emp2.dmp "SUN"."U" x86_64/Linux 2.4.xx
----通过loader方式创建外部表，直接通过表来查询操作 SQL> CREATE TABLE admin_ext_employees2 2 (employee_id NUMBER(4), 3 first_name VARCHAR2(20), 4 last_name VARCHAR2(25), 5 job_id VARCHAR2(10), 6 manager_id NUMBER(4), 7 hire_date DATE, 8 salary NUMBER(8,2), 9 commission_pct NUMBER(2,2), 10 department_id NUMBER(4), 11 email VARCHAR2(25) 12 ) 13 ORGANIZATION EXTERNAL 14 ( 15 TYPE ORACLE_DATAPUMP 16 DEFAULT DIRECTORY admin_dat_dir 17 LOCATION ('emp2.dmp')) ;
Table created.
SQL> SQL> SELECT * FROM admin_ext_employees2 ;
EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_ID
401 Jesse Cromwell HR_REP
402 Abby Applegate IT_PROG
403 Carol Cousins AD_VP
404 John Richardson AC_ACCOUNT
360 Jane Janus ST_CLERK
361 Mark Jasper SA_REP
362 Brenda Starr AD_ASST
363 Alex Alda AC_MGR
