Oracle常用DDL語句

胡余 2010-10-26

展開全文

一、表空間管理
CREATE [BIGFILE|SMALLFILE] [TEMPORARY] TABLESPACE tablespace name -- 表空間名字
DATAFILE datafile spec | TEMPORARYFILE tempfile spec        -- 數(shù)據(jù)文件/臨時文件說明
[MINIMUM EXTENT minimum extent size]        -- 最小擴(kuò)展空間
[[BLOCKSIZE blocksize] DEFAULT STORAGE (defalut storage clause)]        -- （鎖空間大?。?默認(rèn)存儲子句
[LOGGING | NOLOGGING]        -- 有無日志
[FORCE LOGGING]        -- 強(qiáng)制日志
[ONLINE | OFFLINE]        -- 在線/不在線
[EXTENT MANAGEMENT DICTIONARY | LOCAL [AUTOALLOCATE | UNIFORM SIZE size]] -- 擴(kuò)展管理字典/本地（自動定位/統(tǒng)一大?。?br> [SEGMENT SPACE MANAGEMENT MANUAL | AUTO] -- 管理extent中的block（設(shè)置為自動最佳）
[FLASHBACK ON|OFF]         -- 刪除文件是否可找回
1、1、創(chuàng)建永久表空間
例：
CREATE TABLESPACE MYTABLESPACE LOGGING
DATAFILE 'D:\oracle\product\10.1.0\oradata\MYDB\MYTABLESPACE.DBF' SIZE 10M   REUSE
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
1、2、創(chuàng)建臨時表空間
例：
CREATE TEMPORARY TABLESPACE MYTEMP LOGGING
TEMPFILE 'D:\oracle\product\10.1.0\oradata\MYDB\MYTEMP.DBF' SIZE 10M REUSE
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
更改默認(rèn)臨時表空間
alter database default temporary tablespace TEMP2;
1、3 刪除表空間
1）強(qiáng)行刪除表空間，包括數(shù)據(jù)文件
drop tablespace mytemp including contents and datafiles cascade constraints
2）表空間內(nèi)無任何對象時才能刪除
drop tablespace mytemp
1.4 為表空間增加文件
alter tablespace users add datafile 'd:\oracle\oradata\....\users02.dbf' size 10m;
1.5 默認(rèn)臨時表空間
在創(chuàng)建用戶時，如果沒有指定臨時表空間，則會使用系統(tǒng)表空間作為臨時表空間，如果指定了默認(rèn)臨時表空間，則在未指定臨時表空間的情況下使用默認(rèn)的臨時表空間。
alter database default temporary tablespace temp01
1.6 將表空間脫機(jī)
alter tablespace users offline;
1.7 更改表空間的大小
alter tablespace datafile 'd:\oracle\....\xxx.dbf' resize 100m;
1.8 更改表空間文件的名稱或路徑
1)先將對應(yīng)表空間脫機(jī)
2)將文件重命名或則移動到新的位置
3)alter tablespace user01 rename datafile 'd:\oracle\oradata\fox\user01.dbf' to 'd:\oracle\oradata\fox\user02.dbf'
1.9 查看臨時表空間的文件
select * from dba_temp_files;
or select * from v$tempfile;

二、表管理
2.1 建表
CREATE TABLE emp
(
empno number(6) constraint emp_empno_pk primary key using index (create index emp_empno_pk on emp(empno) tablespace idx_ts), --不使用默認(rèn)Primary 的索引
firstname varchar(20),
lastname varchar(25) constraint emp_last_name_nn not null,
hire_date date default sysdate,
salary number(8,2) constraint salary_ck check(salary>0),
managerid number(6),
deptno number(4) constraint emp_deptno_fk references deptment(deptno), --定義外鍵約束
email varchar(25),
constraint emp_email_uq unique(email) --創(chuàng)建唯一約束
)
tablespace mytablespace --制定表空間
    2.2 修改表
2.1.1 增加字段
     alter table emp add(birthday date default sysdate,ename varchar(20) constriant ename_ck check(ename<>'Admin'))
2.1.2 刪除字段
alter table emp drop column birthday
2.1.3 修改字段
alter table emp modify ename varchar(30) default 'unknow'
2.1.4 修改列名
alter table rename column ename to en_name
2.1.5 修改表名
rename emp to employees --其他對象通用
2.1.6 為(表)列添加描述
-- 為表添加描述
comment on table employees is '存放雇員的信息'
comment on column employees.en_name is '存放雇員的英文名'
-- 查詢字段的描述
select owner,table_name,column_name,comments from dba_col_comments where table_name='EMPLOYEES' --好象所有的值都大寫
-- 查詢表的描述
SELECT TABLE_NAME,COMMENTS FROM ALL_TAB_COMMENTS WHERE TABLE_NAME='EMPLOYEES'
2.1.7 截斷表
truncate table employees   --清除所有的數(shù)據(jù)，保留表結(jié)構(gòu)，該操作不記錄日志，請慎用
2.1.8 刪除表
DROP TABLE table_name [CASCADE CONSTRAINTS] [PURGE] -- CASCADE CONSTRAINTS 選項用于刪除其他表的外鍵約束，[PURGE]選項用于徹底刪除表
2.1.9 恢復(fù)被刪除的表
Flashback table employees to before drop

三、約束
3.1 創(chuàng)建約束
3.1.1   NOT NULL約束
注:NOT NULL約束只能定義為列級約束，而不能為表級約束
eg: CREATE TABLE performance
   (
   student_no int not null,
   student_name varchar(10) constraint student_name_nn NOT NULL,
   performance number(6,2)
   )
-- 修改列為not null約束
alter table performance modify performance not null
3.1.2 UNIQUE約束
eg: CREATE TABLE EMP
(
   e_no int,
   name varchar(8),
   salary number(6,2),
   constraint name_unq UNIQUE(name)
   )
-- 增加UNIQUE約束
constraint emp_name_uk unique (e_no,name)
3.1.3 PRIMARY KEY約束
eg: create table emp
(
    emp_no int primary key,
    emp_name varchar(8),
    address varchar(20)
   )
or
   create table emp
   (
    emp_no int,
    emp_name varchar(8),
    address varchar(20),
    constraint emp_no_pk primary key(emp_no)
   )
-- 增加索引
alter table emp add constraint emp_pk primary key (emp_no,emp_name)
3.1.3 foreign key 約束
eg: create table salary
      (
       emp_no int,
       salary number(6,2) constraint emp_no_fk references emp(emp_no)
      )
   or
      create table salary
      (
       emp_no int,
       salary number(6,2),
       constraint emp_no_fk foreign key (emp_no) references emp(emp_no)
      )
-- 添加外鍵約束
alter table emp add constraint emp_no_fk foreign key (emp_no) references emp(emp_no) on delete cascade
-- 添加外鍵約束(雙主鍵)
alter table emp add constraint emp_no_fk foreign key (emp_no,emp_name) references emp(emp_no,emp_name) on delete cascade
3.1.4 CKECK 約束
     create table salary
     (
       emp_no int,
       salary number(6,2) constraint salary_min check(salary>0)
      )
   or
     create table salary
     (
       emp_no int,
       salary number(6,2),
       constraint salary_min check(salary>0)
     )
-- 添加CKECK約束
alter table salary add constraint salary_min_chk check(salary>0)
3.2 修改約束
3.2.1 禁用/激活約束
禁用/激活約束會引起刪除和重建索引的操作
alter table employees disable/enable unique email
alter table employees disable/enable constraint emp_ename_pk
alter tabel employees modify constraint emp_pk disable/enable
alter tabel employees modify constraint emp_ename_phone_uk disable/enable
3.2.2 刪除約束
alter table salary drop constraint salary_min_chk
3.2.3 查詢約束信息
-- 約束及表信息
select owner,constraint_name,table_name,search_condition from dba_constraints where owner='WENZI'
-- 約束及字段信息
select owner,constraint_name,table_name,column_name,position from dba_cons_columns where owner='WENZI'

四、索引
索引和對應(yīng)的表應(yīng)該位于不同的表空間中,oracle能夠并行讀取位于不同硬盤上的數(shù)據(jù)，可以避免產(chǎn)生I/O沖突
B樹索引：在B樹的葉節(jié)點中存儲索引字段的值與ROWID.
唯一索引和不唯一索引都只是針對B樹索引而言.
Oracle最多允許包含32個字段的復(fù)合索引
索引創(chuàng)建策略：
1.導(dǎo)入數(shù)據(jù)后再創(chuàng)建索引
2.不需要為很小的表創(chuàng)建索引
3.對于取值范圍很小的字段（比如性別字段）應(yīng)當(dāng)建立位圖索引
4.限制表中的索引的數(shù)目
5.為索引設(shè)置合適的PCTFREE值
6.存儲索引的表空間最好單獨設(shè)定
4.1 創(chuàng)建索引
4.1.1 創(chuàng)建不唯一索引
create index emp_ename on employees(ename) tablespace users
4.1.2 創(chuàng)建唯一索引
create unique index emp_email on employees(email) tablespace users;
4.1.3 創(chuàng)建位圖索引
create bitmap index emp_sex on employees(sex) tablespace users;
4.1.4 創(chuàng)建反序索引
create unique index order_reinx on orders(order_num,order_date) tablespace users reverse
4.1.5 創(chuàng)建函數(shù)索引
create index emp_substr_empno on employees(substr(empno,1,2)) tablespace users;
4.2 維護(hù)索引
4.2.1 修改索引
由于定義約束時由oracle自動建立的索引通常是不知道名稱的，對這類索引的修改經(jīng)常是利用alter table ..using index語句進(jìn)行的,而不是alter index語句。
利用下面的語句將employees表中primary key約束對應(yīng)的索引的PCTFREE參數(shù)修改為5：
alter table employees enable primary key using index pctfree 5;
4.2.2 合并索引
只是簡單的將B樹葉結(jié)點中的存儲碎片合并在一起，并不會改變索引的物理組織結(jié)構(gòu)
alter index emp_pk coalesce;
4.2.3 重建索引
不僅能夠消除存儲碎片,還可以改變索引的全部存儲參數(shù)設(shè)置，并且可以將索引移動到其它的表空間中,重建索引
實際上就是再指定的表空間中重新建立一個新的索引,然后刪除原來的索引。
alter index emp_pk rebuild;
4.3 刪除索引
在刪除一個表時,oracle會自動刪除所有與該表相關(guān)的索引.
drop index emp_ename
4.4 查詢索引的相關(guān)信息
-- 索引名稱及表：
select owner,index_name,index_type,table_owner,table_type,tablespace_name,status from dba_indexes where owner='WENZI'
-- 索引及字段
select INDEX_OWNER,INDEX_NAME,TABLE_NAME,COLUMN_NAME,COLUMN_POSITION,DESCEND FROM dba_ind_columns where index_owner='WENZI'

五、視圖
5.1 創(chuàng)建視圖
CREATE VIEW division1_staff AS
SELECT ename, empno, job, dname FROM emp, dept
WHERE emp.deptno IN (10, 30) AND emp.deptno = dept.deptno;
5.2 修改視圖
CREATE OR REPLACE VIEW sales_staff AS
SELECT empno, ename, deptno FROM emp
WHERE deptno = 30 WITH CHECK OPTION CONSTRAINT sales_staff_cnst;
5.3 刪除視圖
DROP VIEW emp_dept;

六、序列
6.1 創(chuàng)建序列
CREATE SEQUENCE emp_sequence
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
CACHE 10; --指定CACHE可以提高效率，但是當(dāng)系統(tǒng)意外關(guān)閉時可能會發(fā)生跳號現(xiàn)象.
6.2 修改序列
ALTER SEQUENCE emp_sequence
INCREMENT BY 10
MAXVALUE 10000
CYCLE
CACHE 20;
6.3 使用序列
INSERT INTO Orders_tab (Orderno, Custno) VALUES (Order_seq.NEXTVAL, 1032); --NEXTVAL 為序列的下一個編號，CURRVAL 為當(dāng)前編號
or
UPDATE Orders_tab SET Orderno = Order_seq.NEXTVAL WHERE Orderno = 10112;
6.4 刪除序列
DROP SEQUENCE order_seq;