Oracle_071_lesson_p15
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
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_idTO> ALTER TABLE new_marketing RENAME CONSTRAINT mktg_pkTO new_mktg_pk;改约束名
ALTER TABLE emp2 DISABLE CONSTRAINTS emp_dt_pk;
ALTER TABLE dept2 DISABLE primary key CASCADE;
ALTER TABLE emp2ENABLE 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 SESSIONSETCONSTRAINTS= 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创建临时表
Whensession/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 ;
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
$ ls -l
total 20
-rw-r--r-- 1 ora11 oinstall228 Sep6 21:17 a.dat
-rw-r--r-- 1 ora11 oinstall252 Sep6 21:17 b.dat
-rw-r--r-- 1 ora11 oinstall 2826 Sep6 21:19 empxt000_2756.log
-rw-r--r-- 1 ora11 oinstall 3110 Sep6 21:19 empxt000_2763.log
-rw-r--r-- 1 ora11 oinstall 3110 Sep6 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 Sep6 21:33 emp2.dmp
$ 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
页:
[1]