- 刪除現有表格欄位
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;
PURGE TABLESPACE介紹
語法說明
- 可從Recycle Bin(資源回收筒)刪除指定的資料表空間
PURGE TABLESPACE tablespace_name
[USER user_name];
tablespace_name : 資料表空間名稱
user_name : 使用者名稱,指定USER子句可使使用者得以再度使用資料表空間語法示範
說明 : 從Recycle Bin刪除emp資料表空間
purge tablespace emp;
PURGE介紹
語法說明
- 可從Recycle Bin(資源回收筒),刪除特定的表格物件或索引物件,釋放已刪除物件的一切關聯空間
PURGE {TABLE | INDEX} object_name;
TABLE : 表格型態物件
INDEX : 索引型態物件
object_name : 物件名稱語法示範
說明 :從Recycle Bin刪除emp2表格
purge table emp2;
DROP PROFILE介紹
語法說明
- 刪除使用者的控制檔
DROP PROFILE profile_name [CASCADE];
profile_name : 控制檔名稱語法示範
說明 :刪除控制檔hr_user_profile,解除所有關聯至此控制檔的使用者
drop profile hr_user_profile cascade;
ALTER PROFILE介紹
語法說明
- 針對使用者的控制檔作變更
ALTER PROFILE profile_name LIMIT
[FAILED_LOGIN_ATTEMPTS {times | UNLIMITED}]
[PASSWORD_LIFE_TIME {days | UNLIMITED}]
[{PASSWORD_REUSE_TIME | PASSWORD_REUSE_MAX}{days_or_times | UNLIMITED}]
[PASSWORD_LOCK_TIME {days | UNLIMITED}][PASSWORD_GRACE_TIME {days | UNLIMITED}]
[PASSWORD_VERIFY_FUNCTION {function_name | NULL | DEFAULT}][SESSIONS_PER_USER {n | UNLIMITED | DEFAULT}]
[CPU_PER_SESSION {n | UNLIMITED | DEFAULT}]
[CPU_PER_CALL {n | UNLIMITED | DEFAULT}]
[CONNECT_TIME {n | UNLIMITED | DEFAULT}]
[IDLE_TIME {n | UNLIMITED | DEFAULT}]
[LOGICAL_READS_PER_SESSION {n | UNLIMITED | DEFAULT}]
[COMPOSITE_LIMIT {n | UNLIMITED | DEFAULT}]
[PRIVATE_SGA {n | UNLIMITED | DEFAULT}];
profile : 控制檔名稱
times : 次數
days : 天數
days_or_times : 天數或次數
function : 函數名稱
n : 設定數值
FAILED_LOGIN_ATTEMPTS : 容許登入失敗的次數
PASSWORD_LIFE_TIME : 可使用相同密碼認證的天數
PASSWORD_REUSE_TIME : 如 果你指定天數期間內,密碼變更次數達到PASSWORD_REUSE_MAX指定次數前,密碼無法再使用,如果PASSWORD_REUSE_TIME和 PASSWORD_REUSE_MAX任一者指定UNLIMITED參數,密碼無法再使用,兩者指定為UNLIMITED,且無視這些參數
PASSWORD_LOCK_TIME : 連續登入失敗到達指定次數後,帳戶鎖定天數
PASSWORD_GRACE_TIME : 仍允許登入但提出警告的猶豫天數
PASSWORD_VERIFY_FUNCTION : 指定檢查密碼函數,指定NULL則不進行密碼驗證
SESSIONS_PER_USER : 限制使用者可同時執行的作業階段數
CPU_PER_SESSION : 作業階段使用的CPU時間限制(100分之1秒)
CPU_PER_CALL : 單一叫用的統計CPU時間限制(100分之1秒)
CONNECT_TIME : 單一作業階段經過時間統計限制(分)
IDLE_TIME : 作業階段中,連續的非活動時間長度(分)
LOGICAL_READS_PER_SESSION : 單一作業階段中讀入資料區塊限制
COMPOSITE_LIMIT : 單一作業階段的資源與成本,按服務單位指定
PRIVATE_SGA : 單一作業階段中,共享資料區內可分配的私有空間大小,可用位元為KB(K),MB(M),GB(G),TB(T),PB(P),EF(E)等單位指定語法示範
說明 : 變更控制檔hr_user_profile,使用相同密碼認證天數為30天
alter profile hr_user_profile
password_life_time 30;
CREATE PROFILE介紹
語法說明
語法示範
- 建立使用者的控制檔,只要建立使用者都要指定設定檔,ORACLE預設的設定檔為DEFAULT
CREATE PROFILE profile_name LIMIT
[FAILED_LOGIN_ATTEMPTS {times | UNLIMITED}]
[PASSWORD_LIFE_TIME {days | UNLIMITED}]
[{PASSWORD_REUSE_TIME | PASSWORD_REUSE_MAX}{days_or_times | UNLIMITED}]
[PASSWORD_LOCK_TIME {days | UNLIMITED}][PASSWORD_GRACE_TIME {days | UNLIMITED}]
[PASSWORD_VERIFY_FUNCTION {function_name | NULL | DEFAULT}][SESSIONS_PER_USER {n | UNLIMITED | DEFAULT}]
[CPU_PER_SESSION {n | UNLIMITED | DEFAULT}]
[CPU_PER_CALL {n | UNLIMITED | DEFAULT}]
[CONNECT_TIME {n | UNLIMITED | DEFAULT}]
[IDLE_TIME {n | UNLIMITED | DEFAULT}]
[LOGICAL_READS_PER_SESSION {n | UNLIMITED | DEFAULT}]
[COMPOSITE_LIMIT {n | UNLIMITED | DEFAULT}]
[PRIVATE_SGA {n | UNLIMITED | DEFAULT}];
profile : 控制檔名稱
times : 次數
days : 天數
days_or_times : 天數或次數
function : 函數名稱
n : 設定數值
FAILED_LOGIN_ATTEMPTS : 容許登入失敗的次數
PASSWORD_LIFE_TIME : 可使用相同密碼認證的天數
PASSWORD_REUSE_TIME : 如果你指定天數期間內,密碼變更次數達到PASSWORD_REUSE_MAX指定次數前,密碼無法再使用,如果PASSWORD_REUSE_TIME和PASSWORD_REUSE_MAX任一者指定UNLIMITED參數,密碼無法再使用,兩者指定為UNLIMITED,且無視這些參數
PASSWORD_LOCK_TIME : 連續登入失敗到達指定次數後,帳戶鎖定天數
PASSWORD_GRACE_TIME : 仍允許登入但提出警告的猶豫天數
PASSWORD_VERIFY_FUNCTION : 指定檢查密碼函數,指定NULL則不進行密碼驗證
SESSIONS_PER_USER : 限制使用者可同時執行的作業階段數
CPU_PER_SESSION : 作業階段使用的CPU時間限制(100分之1秒)
CPU_PER_CALL : 單一叫用的統計CPU時間限制(100分之1秒)
CONNECT_TIME : 單一作業階段經過時間統計限制(分)
IDLE_TIME : 作業階段中,連續的非活動時間長度(分)
LOGICAL_READS_PER_SESSION : 單一作業階段中讀入資料區塊限制
COMPOSITE_LIMIT : 單一作業階段的資源與成本,按服務單位指定
PRIVATE_SGA : 單一作業階段中,共享資料區內可分配的私有空間大小,可用位元為KB(K),MB(M),GB(G),TB(T),PB(P),EF(E)等單位指定
語法示範
說明 : 建立hr_user_profile控制檔,在單一作業階段,連續的非活動時間達到15分鐘,使用者會自動斷線
create profile hr_user_profile limit
idle_time 15;
DROP PROCEDURE介紹
語法說明
- 刪除預儲函數
DROP PROCEDURE [schema.]procedure_name;
schema : 結構名稱
procedure_name : 預儲程序名稱語法示範
說明 : 刪除預儲程序emp
drop procedure emp;
ALTER PROCEDURE介紹
語法說明
- 可重新編譯預儲程序,將已編譯(已建立)但狀態變成無效的預儲程序可重新編譯,執行時就不用另外暗中編譯,而能提高效能
- 但是程序的參數/聲明/處理紀錄部分有所變更時,必須以CREATE OR REPLACE PROCEDURE句重新建立函數
ALTER PROCEDURE [schema.]procedure_name COMPILE [DEBUG];
schema : 結構名稱
procedure_name : 預儲程序名稱語法示範
說明 : 重新編譯預儲程序emp
alter procedure emp compile;
CREATE PROCEDURE介紹
語法說明
- 建立預儲程式,指定 [OR REPLACE] 可以再次建立已建立的程序,此時不需要再次授與該程序的物件權限,也不需再次授與程序權限給己持有該權限的使用者,另外,預儲程序和使用者定義函數的預儲函數不同,前者不能將值傳回叫用來源
CREATE [OR REPLACE] PROCEDURE [schema.]procedure_name
[(argument [IN | OUT | IN OUT][,argument [IN | OUT | IN OUT]...])]
[AUTHID {CURRENT_USER | DEFINER}]
{IS | AS}
procedure_statement
END [procedure_name];
schema : 結構名稱
procedure_name : 要建立的程序名稱
argument : 引數內容
IN : 在程序啟動時接受值,在程序內無法變更此引數值
OUT : 將程序內代入此引數的值,於程序結束時傳回至叫用來源
IN OUT : IN和OUT組成的模式,程序啟動時交出引數,在程序內也能將值代入此引數,且代入到引數的值,於程序結束值,會傳回至叫用來源
CURRENT_USER : 以目前執行中的使用者權限來執行程序,所建立的程序稱為 [執行者權限程序]
DEFINER : 以程序所在的結構擁有者權限來執行,所建立的程式包裝稱為 [定義者權限程序] ,AUTHID子句的預設值為AUTHID DEFINER語法示範
說明 : 建立預儲程序emp2
create or replace procedure emp2
is
begin
update emp set salary=2000 where emp_id=101;
end;
/
說明 : 執行預儲程序emp2
execute emp2;
說明 : 建立預儲程序emp3
create or replace procedure emp3
(empno in number,result out number)
is
begin
delete from emp where emp.emp_no = empno;
result := 0;
exception
when others then
result := 9;
end;
/
說明 : 執行預儲程序emp3
declare
wk_result number := 0;
begin
emp3(1,wk_result);
DBMS_OUTPUT_PUT_LINE(to_char(wk_result));
CREATE PFILE介紹
語法說明
- 從spfile建立pfile
CREATE PFILE = 'pfile_path' FROM SPFILE = 'spfile_path';
pfile_path : pfile的路徑
spfile_path : spfile的路徑語法示範
說明 : 從spfile建立pfile
create pfile='/oracle/home/initexample.ora' from spfile='/oracle/home/spfileorcl.ora';
CREATE PACKAGE BODY介紹
語法說明
說明 : 建立預儲程式包裝emp_mgmt的程式包裝本體部分
create or replace package body emp_mgmt as
- 執行CREATE PACKAGE BODY句可建立程式包裝的本體部分,指定 [OR REPLACE] 可重建已建立的程式包裝本體部分,此時不必再次授與該程式包裝的物件權限,也不必再次授與物件權限給己獲此權限的使用者,程式包裝本體部分描述程式包裝成員 [預儲程式和預儲數的處理內容]
格式
CREATE [OR REPLACE] PACKAGE BODY [schema.]package_name
{IS | AS}
package_specification
END [package_name];
schema : 結構名稱
package_name : 程式包裝名稱
package_specification : 程式包裝規格,類型定義,指標/變數/常數/例外的聲明,PL/SQL子程式的規格和PL/SQL內部紀錄的(或者是)java常式的聲明(叫用規格)等語法示範
說明 : 建立預儲程式包裝emp_mgmt的程式包裝本體部分
create or replace package body emp_mgmt as
tot_emps NUMBER; tot_depts NUMBER; FUNCTION hire (last_name VARCHAR2, job_id VARCHAR2, manager_id NUMBER, salary NUMBER, commission_pct NUMBER, department_id NUMBER) RETURN NUMBER IS new_empno NUMBER; BEGIN SELECT employees_seq.NEXTVAL INTO new_empno FROM DUAL; INSERT INTO employees VALUES (new_empno, 'First', 'Last','first.last@oracle.com', '(123)123-1234','18-JUN-02','IT_PROG',90000000,00, 100,110); tot_emps := tot_emps + 1; RETURN(new_empno); END; FUNCTION create_dept(department_id NUMBER, location_id NUMBER) RETURN NUMBER IS new_deptno NUMBER; BEGIN SELECT departments_seq.NEXTVAL INTO new_deptno FROM dual; INSERT INTO departments VALUES (new_deptno, 'department name', 100, 1700); tot_depts := tot_depts + 1; RETURN(new_deptno); END; PROCEDURE remove_emp (employee_id NUMBER) IS BEGIN DELETE FROM employees WHERE employees.employee_id = remove_emp.employee_id; tot_emps := tot_emps - 1; END; PROCEDURE remove_dept(department_id NUMBER) IS BEGIN DELETE FROM departments WHERE departments.department_id = remove_dept.department_id; tot_depts := tot_depts - 1; SELECT COUNT(*) INTO tot_emps FROM employees; END; PROCEDURE increase_sal(employee_id NUMBER, salary_incr NUMBER) IS curr_sal NUMBER; BEGIN SELECT salary INTO curr_sal FROM employees WHERE employees.employee_id = increase_sal.employee_id; IF curr_sal IS NULL THEN RAISE no_sal; ELSE UPDATE employees SET salary = salary + salary_incr WHERE employee_id = employee_id; END IF; END; PROCEDURE increase_comm(employee_id NUMBER, comm_incr NUMBER) IS curr_comm NUMBER; BEGIN SELECT commission_pct INTO curr_comm FROM employees WHERE employees.employee_id = increase_comm.employee_id; IF curr_comm IS NULL THEN RAISE no_comm; ELSE UPDATE employees SET commission_pct = commission_pct + comm_incr; END IF; END; END emp_mgmt; /
DROP PACKAGE介紹
語法說明
- 可刪除預儲程式包裝,規格部分和本體部分都會刪除
DROP PACKAGE [schema.]package_name;
schema : 結構名稱
package_name : 程式包裝名稱語法示範
說明 : 刪除預儲程式包裝pack_emp
drop package pack_emp;
ALTER PACKAGE介紹
語法說明
- 可重新編譯預儲程式包裝,將已編譯(已建立)但狀態變成無效的預儲程式包裝重新編譯,執行時就不另外編譯,而能提高效能,在ALTER PACKAGE句裡無法個別編譯程式包裝
ALTER PACKAGE [schema.]package_name COMPILE
[DEBUG]
[SPECIFICATION | BODY | PACKAGE];
schema : 結構名稱
package_name : 程式包裝名稱
DEBUG : 對於PL/SQL的編譯器產生除錯用程式碼
SPECIFICATION : 指編譯程式包裝的規格部分
BODY : 指編譯程式包裝的本體部分
PACKAGE : 編譯程式包裝的規格部分和本體部分,此為預設值語法示範
說明 : 重新編譯預儲程式包裝pack_emp的規格部分和本體部分
alter package pack_emp compile;
CREATE PACKAGE介紹
語法說明
語法示範
- 建立程式包裝的規格部分,指定OR REPLACE可再次建立規格部分,此時不必再次授與該程式包裝的物件權限,也不必再次授與物件權限給己獲此權限的使用者
- 規格部分陳述是可稱為 [程式包裝成員] 的下列物件,這裡聲明的物件也可稱為 [程式包裝物件] 可從程式包裝外部來參照,實際處理內容則紀錄在本體部分
格式
- 預儲程序和預儲函數
- 共享變數和常數
- 例外
CREATE [OR REPLACE] PACKAGE [schema.]package_name
[AUTHID {CURRENT_USER | DEFINER}]
{IS | AS}
package_specification
END [package_name];
schema : 結構名稱
package_name : 程式包裝名稱
package_specification : 程式包裝規格,類型定義,指標/變數/常數/例外的聲明,PL/SQL子程式的規格和PL/SQL內部紀錄的(或者是)java常式的聲明(叫用規格)等
AUTHID : 設定執行程式包裝內之函數和程序的使用者權限,此設定也可適用於程式包裝本體
- AUTHID子句指定值
CURRENT_USER : 以目前執行中的使用者權限來執行程式包裝,建立包裝稱為 [執行者權限程式包裝]DEFINER : 以程式包裝存在的結構使用者權限來執行,建立的程式包裝稱為[定義者 權限程式包裝],沒有指定AUTHID子句時,預設值為AUTHID DEFINER
語法示範
- 說明 : 預儲程式包裝pack_emp的程式包裝規格部分,pack_emp以下列程式包裝構成
- 變數 : last_name
- 函數 : check_emp_sf
- 程序 : update_emp
- 使用者定義例外 : err_empNotFound
create or replace package pack_emp is
--定義變數--
last_name emp.name%type;
--定義函數check_emp_sf--
function check_emp_sf(empno in number) return boolean;
--定義程序update_emp--
procedure update_emp(empno in number,empname in varchar2);
--使用者定義的例外--
err_empNotFound exception;
end pack_emp;
/
DROP MATERIALIZED VIEW LOG介紹
語法說明
語法示範
刪除實體化檢視日誌格式
DROP MATERIALIZED VIEW LOG ON [schema.]table_name;
schema : 結構名稱
table_name : 物件名稱
語法示範
說明 : 刪除實體化檢視日誌
drop materialized view log on hr.emp_log;
ALTER MATERIALIZED VIEW LOG介紹
語法說明
可變更實體化檢視日誌的屬性,變更項目如下
格式
- 記憶空間特性,分割,平行操作,日誌屬性,有無使用CACHE
- 附加在實體化檢視日誌記錄的欄位
- 是否在實體化檢視日誌放入更新前後的資訊
ALTER MATERIALIZED VIEW LOG [FORCE] ON [schema.]table_name
ADD [OBJECT_ID | PRIMARY KEY(column [,column...])
| ROWID | SEQUENCE | (column [,column...])][{INCLUDING | EXCLUDING} NEW VALUES];
schema : 結構名稱
table_name : 物件名稱
column : 欄位名稱
OBJECT ID : 記錄對於更新全列的物件識別符,建立物件表格日誌時可以指定
PRIMARY KEY : 記錄更新全列主鍵
ROWID : 記錄更新全列ROWID
SEQUENCE : 紀錄表示附加序列資訊的序列值,進行高速復新時,必須要在實體化檢視日誌裡記錄此值
NEW VALUES子句可指定更新前後的資訊處理方法
- NEW VALUES子句指定值
語法示範
- INCLUDING : 記錄更新前後資訊,此為預設值
- EXCLUDING : 只記錄更新前的資訊
說明 : 附加在實體化檢視日誌記錄的欄位
alter materialized view log on emp add deptno_id number(10);
說明 : 設定附加記錄欄位並記錄更新前後的值
alter materialized view log on my_emp add(comm) including new values;
訂閱：
文章 (Atom)