- 刪除現有表格欄位
ALTER TABLE [schema.]table_name DROP(column [,column...]);
schema : 結構名稱
table_name : 表格名稱
column : 欄位名稱語法示範
說明 : 刪除emp表格的emp_id欄位
alter table emp drop(emp_id);
ALTER TABLE...DROP介紹
語法說明
ALTER TABLE...ADD介紹
語法說明
- 在表格上附加欄位
ALTER TABLE [schema.]table_name ADD
(column datatype [DEFAULT value] [constraint]
[,column datatype [DEFAULT value] [constraint]...]);
schema :結構名稱
table_name : 表格名稱
column : 欄位名稱
datatype : 資料型別
value : 欄位預設值
constraint : 條件約束語法示範
說明 : 在emp表格加入emp_no欄位
alter table emp add(emp_no number);
CREATE TABLE AS SELECT介紹
語法說明
- 複製資料到所建立的表格上,透過 SELECT 語句完成該操作
CREATE TABLE [schema.]table_name
AS select_statement;
schema : 結構名稱
table_name : 表格名稱
select_statement : 查詢語法,將所查詢的資料複製到所建立的表格語法示範
說明 : 將 employees 表格所有資料複製到 emp 表格
create table emp as select * from employees;
說明 : 將 employees 表格所有欄位複製到 emp 表格
create table emp as select * from employees where 1=2;
CREATE GLOBAL TEMPORARY TABLE介紹
語法說明
- 建立暫存資料表
- 指定 ON COMMIT 子句,設定提交後的資料處理
- 暫存資料表有以下限制
格式
- 無法分割,建立索引,建立叢集
- 無法指定外部條件約束
- 不支援平行DML和平行查詢
- 不支援分散式交易
- 無法包含巢狀的表格欄位
CREATE GLOBAL TEMPORARY TABLE [schema.]table_name
( [column datatype [DEFAULT value] [constraint]
[,column datatype [DEFAULT value] [constraint]...]] )
[ON COMMIT {DELETE | PRESERVE} ROWS]
[TABLESPACE tablespace_name];
schema : 結構名稱
table_name : 表格名稱
column : 欄位名稱
datatype : 資料型別
value : 欄位預設值,可包含字串與SQL函數
constraint : 約束條件
DELETE ROWS : 資料只在commit前有效,commit後資料捨棄
PRESERVE ROWS : 資料只在作業過程中有效,作業階段結束資料捨棄
tablespace_name : 表空間名稱語法示範
說明 : 建立暫存資料表,資料只在作業過程中有效,作業階段結束資料捨棄
create global temporary table hr.emp_tmp
(emp_id number(10),
emp_name varchar2(10))
ON COMMIT PRESERVE ROWS;
CREATE TABLE介紹
語法說明
- 建立表格物件
CREATE TABLE [schema.]table_name
( column datatype [DEFAULT value] [in_constraint_caluse]
[,column datatype [DEFAULT value] [in_constraint_caluse]...]
[,out_constraint_caluse] )
[partition_caluse]
[TABLESPACE tablespace_name];
schema : 結構名稱
table_name : 表格名稱
column : 欄位名稱
datatype : 資料型態
value : 欄位預設值
tablespace_name : 表空間名稱
以下針對 in_constraint_caluse , out_constraint_caluse , partition_caluse 子句做格式介紹in_constraint_caluse格式介紹(在表格內建立條件限制)
[CONSTRAINT constraint_name]
{ NOT NULL |
PRIMARY KEY |
UNIQUE |
REFERENCES [schema.]table_name(column) |
CHECK(condition)}
constraint_name : 條件限制名稱
NOT NULL : 不允許有 null 值
PRIMARY KEY : 只能有唯一值,不允許有 null 值,會自動建立索引
UNIQUE : 只能有唯一值,允許有 null 值,會自動建立索引
REFERENCES : 參考其它表格的欄位值
schema : 其它表格的使用者
table_name : 其它表格
column : 其它表格的欄位
CHECK : 欄位值必須滿足所指定的條件
condition : 指定條件out_constraint_caluse格式介紹(在表格內建立條件限制)
[CONSTRAINT constraint_name]
{ UNIQUE ( column [,column...] ) |
PRIMARY KEY ( column [,column...] ) |
FOREIGN KEY ( column_fk [,column_fk...] )
REFERENCES [schema.]table_name(column_fk2) |
CHECK(condition) }
constraint_name : 條件限制名稱
column : 欄位名稱
PRIMARY KEY : 只能有唯一值,不允許有 null 值,會自動建立索引
UNIQUE : 只能有唯一值,允許有 null 值,會自動建立索引
FOREIGN KEY : 外部條件約束,參考其它表格的欄位值
column_fk : 自表格欄位
schema : 其它表格的使用者
table_name : 其它表格
column_fk2 : 其它表格的欄位
CHECK : 欄位值必須滿足所指定的條件
condition : 指定條件partition_caluse介紹(建立分割資料表)
- 以下介紹常用分割種類
範圍分割 : 以欄位值範圍 , 做分割條件
清單分割 : 指定欄位值 , 做分割條件
- 範圍分割語法格式
PARTITION BY RANGE (column [,column...])
( PARTITION [partition_name_n] VALUES LESS THAN ({ value | MAXVALUE })
[ , PARTITION [partition_name_n] VALUES LESS THAN ({ value | MAXVALUE })...])
column : 分割的欄位,無法指定ROWID , LONG , LOB , TIMESTAMP WITH TIME ZOZE型別欄位
partition_name_n : 分割名稱
value : 分割的上限值
MAXVALUE : 沒有上限值,必須指定給最後的分割
- 清單分割語法格式
PARTITION BY LIST (column)
( PARTITION [partition_name_n] VALUES ({ value | NULL | DEFAULT })
[ , PARTITION [partition_name_n] VALUES ({ value | NULL | DEFAULT })...])
column : 分割的欄位,無法指定 LOB 型別欄位
partition_name_n : 分割名稱
value : 對應分割固定值
NULL : 只能指定給一個分割
DEFAULT : 只能指定給一個分割,且必須指定給最後的分割語法示範
說明 : 建立emp表格到users表空間,在id欄位建立限制條件
create table emp
(id number(10) constraint id_pk primary key)
tablespace users;
說明 : 建立emp2表格,在id欄位建立外部限制條件
create table emp2
( id number(10) primary key )
constraint id_fk foreign key(id) references hr.emp(id);
說明 : 建立emp3表格,在id欄位建立限制條件(欄位值必須大於10)
create table emp3
( id number(10) primary key )
constraint id_ck check(id>10);
說明 : 根據範圍分割,建立emp4分割表格
create table emp4
( id number(10),
name varchar2(10))
partition by range(id)
( partition id_1 values less than(10),
partition id_2 values less than(20),partition id_3 values less than(maxvalue));
說明 : 根據清單分割,建立emp5分割表格
create table emp5
( id number(10),
name varchar2(10))
partition by list(id)
( partition id_1 values (10),
partition id_2 values (20),partition id_3 values (default));
DROP SYNONYM介紹
語法說明
- 若指定 PUBLIC 關鍵字,則刪除公用同義字,若省略不指定,則刪除私有同義字
DROP [PUBLIC] SYNONYM [schema.]synonym_name [FORCE];
schema : 結構名稱
synonym_name : 同義字名稱
FORCE : 可刪除具有依存表格或使用者定義型別之物件型別的同義字語法示範
說明 : 刪除 emp 私有同義字
drop synonym emp;
說明 : 刪除 emp2 公用同義字
drop public synonym emp2;
CREATE SYNONYM介紹
語法說明
- 針對物件建立同義字(別名),同義字可建立以下這些物件
- 表格
- 檢視
- 實體化檢視
- 索引
- 序列
- 同義字
- 預儲程序
- 預儲函數
- 外部程序
- Java 類別關聯
- 指定 PUBLIC 關鍵字可建立公用同義字,可建立任何使用者都能參照公用同義字,若省略不指定,則為私有同義字,表示建立的私有同義字只在建立的使用者結構內有效
CREATE [PUBLIC] SYNONYM [schema.]synonym_name FOR [schema.]object_name;
schema : 結構名稱
synonym_name : 同義字名稱
object_name : 目標物件名稱語法示範
說明 : 在使用者 hr 的 employees 表格的私有同義字為 emp
create synonym emp for hr.employees;
說明 : 在使用者 hr 的 employees2 表格的公用同義字為 emp2
create public synonym emp2 for hr.employees2;
CREATE SPFILE介紹
語法說明
- 從 pfile 建立 spfile
CREATE SPFILE [ = 'spfile_path' ] FROM PFILE [ = 'pfile_path' ];
spfile_path : 建立spfile的路徑
pfile_path : pfile的路徑語法示範
說明 : 從 pfile 建立 spfile
create spfile = '/u01/app/oracle/spfileorcl.ora' from pfile = '/u01/app/oracle/pfileorcl.ora';
說明 : 從 pfile 建立 spfile
create spfile from pfile;
DROP SEQUENCE介紹
語法說明
- 刪除序列物件
DROP SEQUENCE [schema.]sequence_name;
schema : 結構名稱
sequence_name : 序列名稱語法示範
說明 : 刪除 hr_dep_seq 序列物件
drop sequence hr_dep_seq;
ALTER SEQUENCE介紹
語法說明
- 變更序列物件架構,如變更遞增值,最小值,最大值,快取數目等項目
ALTER SEQUENCE [schema.]sequence_name
[ INCREMENT BY i ][ START WITH s ]
[ NOMAXVALUE | MAXVALUE max ]
[ NOMINVALUE | MINVALUE min ]
[ NOCYCLE | CYCLE ]
[ CACHE cache | NOCACHE ]
[ NOORDER | ORDER ];
schema : 結構名稱
sequence_name : 序列名稱
i : 序列的遞增間隔,可指定的數字為28位數內的非0(零)正負整數
s : 序列的開始值,可指定的數字為28位數內的整數,可以為0(零)
max : 序列的最大值
min : 序列的最小值
cache : 快取的序列數
INCREMENT BY : 指定序列遞增間隔,省略不指定預設為1
START WITH : 指定序列起始值,序列為昇冪時,起始值為比最小值還要大的值,降冪時則為比最大值要小的值,若省略而不指定,昇冪時的起始值為最小值,降冪時為最大值
NOMAXVALUE | MAXVALUE : 指定序列的最大值,若省略而不指定,或指定為 NOMAXVALUE 則昇冪時最大值為10的27次方,降冪時為-1
NOMINVALUE | MINVALUE : 指定序列的最小值,若省略而不指定,或指定為 NOMINVALUE 則昇冪時最小值為1,降冪時為10的26次方的負值
NOCYCLE | CYCLE : 當序列達最大值(或是降冪達最小值)時,指定是否循環,若省略而不指定,或指定為 NOCYCLE ,則序列達到最大值或最小值的那一刻起,就不會再產生更大或更小的值,若指定為 CYCLE 當序列達最大值後,會回到最小值 (降冪的情況中則會回到最大值)
CACHE | NOCACHE : 指定快取的序列數,增加快取數量時,雖然能夠快速存取序列,但注意若實體非正常結束,會失去快取中的全部未使用值,指定 NOCACHE 會使快取無效,若省略而不指定,則會快取20個序列
NOORDER | ORDER : 在RAC的環境裡使用序列時,若要按照順序產生序列則需特別指定,不需要按時截傳回時指定 NOORDER ,此為預設值語法示範
說明 : 變更hr_dep_seq序列物件的cycle選項
alter sequence hr_dep_seq cycle;
CREATE SEQUENCE介紹
語法說明
- 建立序列,用序列能自動產生連續數值等,替傳票號碼等唯一號碼進行編號時相當方便實用
CREATE SEQUENCE [schema.]sequence_name
[ INCREMENT BY i ][ START WITH s ]
[ NOMAXVALUE | MAXVALUE max ]
[ NOMINVALUE | MINVALUE min ]
[ NOCYCLE | CYCLE ]
[ CACHE cache | NOCACHE ]
[ NOORDER | ORDER ];
schema : 結構名稱
sequence_name : 序列名稱
i : 序列的遞增間隔,可指定的數字為28位數內的非0(零)正負整數
s : 序列的開始值,可指定的數字為28位數內的整數,可以為0(零)
max : 序列的最大值
min : 序列的最小值
cache : 快取的序列數
INCREMENT BY : 指定序列遞增間隔,省略不指定預設為1
START WITH : 指定序列起始值,序列為昇冪時,起始值為比最小值還要大的值,降冪時則為比最大值要小的值,若省略而不指定,昇冪時的起始值為最小值,降冪時為最大值
NOMAXVALUE | MAXVALUE : 指定序列的最大值,若省略而不指定,或指定為 NOMAXVALUE 則昇冪時最大值為10的27次方,降冪時為-1
NOMINVALUE | MINVALUE : 指定序列的最小值,若省略而不指定,或指定為 NOMINVALUE 則昇冪時最小值為1,降冪時為10的26次方的負值
NOCYCLE | CYCLE : 當序列達最大值(或是降冪達最小值)時,指定是否循環,若省略而不指定,或指定為 NOCYCLE ,則序列達到最大值或最小值的那一刻起,就不會再產生更大或更小的值,若指定為 CYCLE 當序列達最大值後,會回到最小值 (降冪的情況中則會回到最大值)
CACHE | NOCACHE : 指定快取的序列數,增加快取數量時,雖然能夠快速存取序列,但注意若實體非正常結束,會失去快取中的全部未使用值,指定 NOCACHE 會使快取無效,若省略而不指定,則會快取20個序列
NOORDER | ORDER : 在RAC的環境裡使用序列時,若要按照順序產生序列則需特別指定,不需要按時截傳回時指定 NOORDER ,此為預設值序列使用方法
- 序列使用時會運用名為 [CURRVAL] 和 [NEXTVAL]的虛擬欄位,[CURRVAL]顯示序列現在的值,[NEXTVAL]產生下一個序列值
- 在使用NEXTVAL之前,不管參照CURRVAL幾次,序列值永不改變
說明 : 建立hr_seq序列
create sequence hr_seq
start with 0
increment by 1
nocycle;
說明 : 產生序列,並新增到emp表格
insert into emp(id,name) values(hr_seq.nextval ,'John');
說明 : 查詢 hr_seq 序列現在的值
select hr_seq.currval from dual;補充說明
- 序列並不能保證產生連續的數值,例如,在某個時間點的交易所產生的序列,因交易回復,會使得下一個序列不會變成連續的數值,這是因為即使交易回復,之前所產生的序列值也不會跟著回復,因此需要連續號碼時,並不建議使用序列
CREATE SCHEMA介紹
語法說明
- 執行 CREATE SCHEMA 句可指定一個交易,執行多個 DDL ( CREATE TABLE 句, CREATE VIEW 句, GRANT 句)
- 使用 CREATE SCHEMA 句指定的 DDL 用一個交易執行, 所指定的 DDL 句必須正常執行,交易才會提交,只要其中的 DDL 句錯誤,所有交易都會回復
- 不會建立結構,如果要建立結構使用 CREATE USER 句
CREATE SCHEMA AUTHORIZATION schema_name
{ create_table_statement | create_view_statement | grant_statement
[ create_table_statement | create_view_statement | grant_statement... ] };
schema_name : 結構名稱
create_table_statement : 建立表格語法
create_view_statement : 建立檢視語法
grant_statement : 建立授權語法語法示範
說明 : 建立 hr_emp 表格到 hr 使用者
create schema authorization hr
create table hr_emp
( xid number(10) primary key,
name varchar2(10) not null );
DROP ROLE介紹
語法說明
- 刪除角色,包含角色所持有的所有權限
DROP ROLE role_name;
role_name : 角色名稱語法示範
說明 : 刪除 hr_role 角色
drop role hr_role;
ALTER ROLE介紹
語法說明
- 變更角色密碼,變更後要以 SET ROLE 句指定變更後的密碼,讓角色可用
ALTER ROLE role_name
[ IDENTIFIED { BY password | USING [schema.]package | EXTERNALLY| GLOBALLY } | NOT IDENTIFIED ];
role_name : 角色名稱
password : 密碼指定
schema : 結構名稱
package : 程式包裝名稱
EXTERNALLY : 建立外部角色,必須得到外部服務(作業系統和第三方服務等) 的認證
GLOBALLY : 建立全域角色,必須得到企業目錄服務的角色使用者認可語法示範
說明 : 變更 hr_role 角色的密碼
alter role hr_role identified by hr_role_123;
SET ROLE介紹
語法說明
- 在目前作階段中,讓指定的角色可用或禁用,登入資料庫時,所授與的全部權限和預設角色都可用,所以必要時請變更可用角色,獲得指定角色權限的使用者才能夠執行SET ROLE
SET ROLE { ALL | NONE | ALL EXCEPT role_name
| role_name [ IDENTIFIED BY password][,role_name [ IDENTIFIED BY password ]...] };
role_name : 角色名稱
password : 密碼
ALL : 所有角色都可用
NONE : 包含DEFAULT角色在內的全部角色都不可用
ALL EXCEPT : 除了ALL EXCEPT 指定的角色外,使用者所持有的角色都可用語法示範
說明 : 設定 dep_role 角色可用
set role dep_role identified by dep_role123;
CREATE ROLE介紹
語法說明
- 建立角色,使用者角色可以一次操作將分配給使用者的權限整合處理,透過GRANT子句將角色授與該使用者
CREATE ROLE role_name
[ IDENTIFIED {BY password | USING [schema.]package | EXTERNALLY
| GLOBALLY } | NOT IDENTIFIED ];
role_name : 角色名稱
password : 密碼指定
schema : 結構名稱
package : 程式包裝名稱
EXTERNALLY : 建立外部角色,必須得到外部服務(作業系統和第三方服務等) 的認證
GLOBALLY : 建立全域角色,必須得到企業目錄服務的角色使用者認可語法示範
說明 : 建立hr_role角色,並使用外部認證
create role hr_role identified externally;
REVOKE ... ON介紹
語法說明
- 使用者或角色取消特定物件的物件權限,且可多重指定權限,若指定 ALL PRIVILEGE 則全部的物件權限皆會取消
- 取消對象以 ON 子句指定 , 除了物件外,也可指定目錄物件,Java原始程式,資源物件等, FROM 子句指定要取消權限的使用者或角色,若指定 PUBLIC 則只會取消經由PUBLIC角色所授與的權限
REVOKE { object_privilege [,object_privilege] | ALL PRIVILEGE }
ON { [schema.]object_name | DIRECTORY directory_name |
JAVA {SOURCE | RESOURCE} }
FROM { user [,user...] | PUBLIC }
[CASCADE CONSTRAINTS]
[FORCE];
object_privilege : 物件權限
schema : 結構名稱
object_name : 物件名稱
directory_name : 目錄名稱
user : 使用者名稱或角色名稱
CASCADE CONSTRAINTS : 可刪除參考完整性條件限制,在取消REFERENCES權限時指定
FORCE : 取消依賴表格或型別之使用者定義型別物件的EXECUTE物件權限時指定語法示範
說明 : hr 使用者取消 emp 表格的 delete 權限
revoke delete on hr.emp from hr;
說明 : hr 使用者取消 emp 表格的所有權限
revoke all on hr.emp from hr;補充
- 在使用者A授與使用者B權限情境中,一旦自使用者A取消物件權限,使用者B也會失去該物件權限,和系統權限的情境不同,請務必注意
PURGE RECYCLEBIN介紹
語法說明
- 清空資源回收筒
PURGE {RECYCLEBIN | DBA_RECYCLEBIN};
RECYCLEBIN : 清空執行使用者的Recycle Bin , 釋放所有與刪除物件有關的空間
DBA_RECYCLEBIN : 清空全體的Recycle Bin , 等同各自清空每個使用者的Recycle Bin語法示範
說明 : 清空目前使用者的Recycle Bin
purge recyclebin;
說明 : 清空所有使用者的Recycle Bin
purge dba_recyclebin;
REVOKE ... FROM介紹
語法說明
- 使用者或角色取消系統權限或角色,可多重指定權限,若指定 ALL PRIVILEGE 則所有系統權限皆會取消
- FROM 子句可指定要取消系統權限的使用者或角色,若指定 PUBLIC 則只有經由 PUBLIC 角色授與的權限會取消
- 若要取消系統權限或角色,則權限必須是由WITH ADMIN OPTION 所授與的才可以
REVOKE {system_privilege [,system_privilege...] | ALL PRIVILEGE}
FROM {user [,user...] | PUBLIC };
system_privilege : 系統權限
user : 使用者名稱或角色名稱語法示範
說明 : 移除create any table系統權限在hr使用者
revoke create any table from hr;補充說明
- 在使用者A授與使用者B系統權限情境中,即使自使用者A取消該系統權限,使用者B系統權限也不會消失
RENAME介紹
語法說明
- 可變更內部結構的表格,檢視,序列,私有同義字的物件名稱,名稱變更前物件原有的完整性限制條件,索引,權限等皆會自動移至變更後的物件
RENAME old_name TO new_name;
old_name : 目前物件名稱
new_name : 新物件名稱語法示範
說明 : 變更表格名稱
rename emp to emp2;
訂閱：
文章 (Atom)