Oracle_071_lesson_p15

jdxyzlh

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>　　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  
　　ADD [CONSTRAINT ]
　　type ();
　　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>　　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     (  , … )
　　ORGANIZATION EXTERNAL
　　(TYPE
　　DEFAULT DIRECTORY
　　ACCESS PARAMETERS
　　(… ) )
　　LOCATION ('')  REJECT LIMIT [0 |  | 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_baddir:'empxt%a%p.bad'
　　logfile admin_logdir:'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