Oracle_071_lesson_p15

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,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,50,jjanus
361,Mark,Jasper,SA_REP,145,8000,.1,80,mjasper
362,Brenda,Starr,AD_ASST,200,5500,10,bstarr
363,Alex,Alda,AC_MGR,9000,.15,aalda

====b.dat====
401,Jesse,Cromwell,HR_REP,203,7000,40,jcromwel
402,Abby,Applegate,IT_PROG,103,.2,60,aapplega
403,Carol,Cousins,AD_VP,100,27000,.3,90,ccousins
404,John,Richardson,AC_ACCOUNT,205,5000,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,
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,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

[[email?protected] 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
[[email?protected] 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,
9 commission_pct NUMBER(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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!