一、表空間管理
CREATE [BIGFILE|SMALLFILE] [TEMPORARY] TABLESPACE tablespace name -- 表空間名字
DATAFILE datafile spec | TEMPORARYFILE tempfile spec -- 數(shù)據(jù)文件/臨時(shí)文件說明
[MINIMUM EXTENT minimum extent size] -- 最小擴(kuò)展空間
[[BLOCKSIZE blocksize] DEFAULT STORAGE (defalut storage clause)] -- (鎖空間大?。?默認(rèn)存儲(chǔ)子句
[LOGGING | NOLOGGING] -- 有無日志
[FORCE LOGGING] -- 強(qiáng)制日志
[ONLINE | OFFLINE] -- 在線/不在線
[EXTENT MANAGEMENT DICTIONARY | LOCAL [AUTOALLOCATE | UNIFORM SIZE size]] -- 擴(kuò)展管理字典/本地(自動(dòng)定位/統(tǒng)一大?。?br> [SEGMENT SPACE MANAGEMENT MANUAL | AUTO] -- 管理extent中的block(設(shè)置為自動(dòng)最佳)
[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)建臨時(shí)表空間
例:
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)臨時(shí)表空間
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)無任何對(duì)象時(shí)才能刪除
drop tablespace mytemp
1.4 為表空間增加文件
alter tablespace users add datafile 'd:\oracle\oradata\....\users02.dbf' size 10m;
1.5 默認(rèn)臨時(shí)表空間
在創(chuàng)建用戶時(shí),如果沒有指定臨時(shí)表空間,則會(huì)使用系統(tǒng)表空間作為臨時(shí)表空間,如果指定了默認(rèn)臨時(shí)表空間,則在未指定臨時(shí)表空間的情況下使用默認(rèn)的臨時(shí)表空間。
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)先將對(duì)應(yīng)表空間脫機(jī)
2)將文件重命名或則移動(dòng)到新的位置
3)alter tablespace user01 rename datafile 'd:\oracle\oradata\fox\user01.dbf' to 'd:\oracle\oradata\fox\user02.dbf'
1.9 查看臨時(shí)表空間的文件
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 --其他對(duì)象通用
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 截?cái)啾?br> truncate table employees --清除所有的數(shù)據(jù),保留表結(jié)構(gòu),該操作不記錄日志,請(qǐng)慎用
2.1.8 刪除表
DROP TABLE table_name [CASCADE CONSTRAINTS] [PURGE] -- CASCADE CONSTRAINTS 選項(xiàng)用于刪除其他表的外鍵約束,[PURGE]選項(xiàng)用于徹底刪除表
2.1.9 恢復(fù)被刪除的表
Flashback table employees to before drop
三、約束
3.1 創(chuàng)建約束
3.1.1 NOT NULL約束
注:NOT NULL約束只能定義為列級(jí)約束,而不能為表級(jí)約束
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 禁用/激活約束
禁用/激活約束會(huì)引起刪除和重建索引的操作
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'
四、索引
索引和對(duì)應(yīng)的表應(yīng)該位于不同的表空間中,oracle能夠并行讀取位于不同硬盤上的數(shù)據(jù),可以避免產(chǎn)生I/O沖突
B樹索引:在B樹的葉節(jié)點(diǎn)中存儲(chǔ)索引字段的值與ROWID.
唯一索引和不唯一索引都只是針對(duì)B樹索引而言.
Oracle最多允許包含32個(gè)字段的復(fù)合索引
索引創(chuàng)建策略:
1.導(dǎo)入數(shù)據(jù)后再創(chuàng)建索引
2.不需要為很小的表創(chuàng)建索引
3.對(duì)于取值范圍很小的字段(比如性別字段)應(yīng)當(dāng)建立位圖索引
4.限制表中的索引的數(shù)目
5.為索引設(shè)置合適的PCTFREE值
6.存儲(chǔ)索引的表空間最好單獨(dú)設(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 修改索引
由于定義約束時(shí)由oracle自動(dòng)建立的索引通常是不知道名稱的,對(duì)這類索引的修改經(jīng)常是利用alter table ..using index語句進(jìn)行的,而不是alter index語句。
利用下面的語句將employees表中primary key約束對(duì)應(yīng)的索引的PCTFREE參數(shù)修改為5:
alter table employees enable primary key using index pctfree 5;
4.2.2 合并索引
只是簡單的將B樹葉結(jié)點(diǎn)中的存儲(chǔ)碎片合并在一起,并不會(huì)改變索引的物理組織結(jié)構(gòu)
alter index emp_pk coalesce;
4.2.3 重建索引
不僅能夠消除存儲(chǔ)碎片,還可以改變索引的全部存儲(chǔ)參數(shù)設(shè)置,并且可以將索引移動(dòng)到其它的表空間中,重建索引
實(shí)際上就是再指定的表空間中重新建立一個(gè)新的索引,然后刪除原來的索引。
alter index emp_pk rebuild;
4.3 刪除索引
在刪除一個(gè)表時(shí),oracle會(huì)自動(dòng)刪除所有與該表相關(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)閉時(shí)可能會(huì)發(fā)生跳號(hào)現(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 為序列的下一個(gè)編號(hào),CURRVAL 為當(dāng)前編號(hào)
or
UPDATE Orders_tab SET Orderno = Order_seq.NEXTVAL WHERE Orderno = 10112;
6.4 刪除序列
DROP SEQUENCE order_seq;
聯(lián)系客服