何謂CREATE INDEX
- 建立索引在表格欄位上,它可以大幅提升查詢效能,可是對於新增,修改,刪除的操作會降低資料庫的效能,所以建立索引一般是配合查詢操作使用
索引型態介紹
- 複合式索引 : 所建立的索引可參考多個欄位
- 函數索引 : 與函數搭配建立索引
- 一般索引 : 不屬於複合式索引與函數索引
格式
CREATE [UNIQUE | BITMAP] INDEX [schema.]index_name
ON [schema.]table_name(column [,column...]);
UNIQUE : 建立唯一索引,唯一索引所參考到的欄位不能有重複值
BITMAP : 建立位元映射索引,通常使用位元映射索引是針對重複性高的欄位值
schema : 結構名稱
index_name : 索引名稱
table_name : 表格名稱
column : 欄位名稱
使用方式
說明 : 建立唯一索引(又可稱為一般索引)在hr使用者的emp表格的xid欄位上,索引名稱為id
create unique index id on hr.emp(xid);
說明 : 建立位元映射索引(又可稱為複合式索引)在hr使用者的emp表格的xid欄位與name欄位上,索引名稱為x_id
create bitmap index x_id on hr.emp(xid,name);
說明 : 建立索引(又可稱為函數索引)在hr使用者的emp表格的name欄位上,該欄位被索引參考到的值是為大寫,因為使用大寫函數,索引名稱為app
create index app on hr.emp(upper(name));
何謂GRANT-物件權限
格式
GRANT {privilege_name | ALL [PRIVILEGES]}
[(column [,column...])]
ON { [schema.]object_name |
DIRECTORY directory_name |
JAVA {SOURCE | RESOURCE} [schema.]object_name }
TO {grantee | PUBLIC}
[WITH GRANT OPTION];
privilege_name : 物件權限,例如SELECT,INSERT,UPDATE,DELETE
ALL [PRIVILEGES] : 所有物件權限
column : 欄位名稱
schema : 結構名稱
object_name : 物件名稱
directory_name :目錄名稱
grantee : 被授予的使用者或角色
PUBLIC : 所有使用者
WITH GRANT OPTION : 被授予物件權限的使用者可在授予別人,稱為轉授權
使用方式
說明 : 授予aa使用者物件權限,可針對hr使用者emp表格的salary與employee_id欄位進行修改
grant update (salary,employee_id) on hr.emp to aa;
說明 : 授予aa使用者所有物件權限,可針對hr使用者emp表格進行新增,修改,刪除,查詢等,並轉授權
grant all on hr.emp to aa with grant option;
說明 : 授予所有使用者物件權限,可針對hr使用者emp表格進行查詢
grant select on hr.emp to public;
何謂GRANT-系統權限
格式
GRANT {privilege_name [,privilege_name...] | ALL PRIVILEGES}
TO {grantee [,grantee ...] | PUBLIC}
[WITH ADMIN OPTION];
privilege_name : 系統權限或角色
ALL PRIVILEGES : 所有系統權限
grantee : 被授予的使用者或角色
PUBLIC : 所有使用者
WITH ADMIN OPTION : 被授予系統權限或角色的使用者可在授予別人,簡稱轉授權
使用方式
說明 : 授予connect角色與create table系統權限給hr使用者,並轉授權
grant connect,create table to hr with admin option;
說明 : 授予所有系統權限給aa使用者
grant all privileges to aa;
說明 ; 授予create view系統權限給所有使用者
grant create view to public;
何謂DROP DIRECTORY
格式
DROP DIRECTORY directory_name;
directory_name : 目錄名稱
使用方式
說明 : 刪除作業系統上的目錄為awi
drop directory awi;
何謂CREATE DIRECTORY
- 它是一個目錄物件,它會在作業系統建立目錄,並可以提供使用者針對資料庫的資料做匯入,匯出所使用的資料存放路徑,並透過expdp(匯出)與impdp(匯入)來針對資料做匯入或匯出,expdp與impdp是屬於Oracle的應用程式,注意在Oracle 10g前使用exp與imp,在Oracle 10g後則使用expdp與impdp
權限
- 使用者想對資料做匯入,匯出,則必須有READ物件權限與WRITE物件權限,才能使用目錄
格式
CREATE [OR REPLACE] DIRECTORY directory_name
AS 'directory_path';
OR REPLACE : 從覆使用目錄
directory_name : 目錄名稱
directory_path : 資料存放在作業系統入徑
使用方式
說明 : 建立目錄名稱為awi,在作業系統的/home/oracle/Desktop路徑
create directory awi as '/home/oracle/Desktop';
說明 : 授予權限,讓使用者hr可以使用目錄,名稱為awi
grant read,write on directory awi to hr;
何謂DROP DATABASE LINK
- 移除資料庫連結,如果指定PUBLIC可移除公用資料庫連結
格式
DROP [PUBLIC] DATABASE LINK dblink_name;
dblink_name : 資料庫連結名稱
使用方式
說明 : 移除資料庫連結名稱為king
drop database link king;
說明 : 移除公用的資料庫連結名稱為app
drop public database link app;
何謂CREATE DATABASE LINK
- 可建立資料庫連結,透過資料庫連結名稱可以使用遠端資料庫的物件進行操作,所使用的連結符號為@
格式
CREATE [PUBLIC] DATABASE LINK dblink_name
[CONNECT TO user IDENTIFIED BY password]
USING 'connect_string';
PUBLIC : 本端的資料庫使用者,都可以使用資料庫連結名稱
dblink_name : 連結名稱
user : 連入遠端資料庫的使用者名稱
password : 連入遠端資料庫的使用者密碼
connect_string : 遠端資料庫的服務名稱(Local Naming)
使用方式
說明 : 建立遠端資料庫連線到prod,資料庫連結名稱為hr_emp,使用hr_emp可操作遠端資料庫的hr使用者的所有物件
create database link hr_emp connect to hr identified by hr using 'prod';
說明 : 查詢遠端資料庫物件
select count(*) from employees@hr_emp;
說明 : 建立遠端資料庫連線到prod,資料庫連結名稱為king,使用king可操作遠端資料庫的物件,此物件必須存在本端使用者的物件,而如果指定public關鍵字,代表本端使用者都可以使用資料庫連結名稱
create public database link king using 'prod';
補充 : 如果沒有指定public關鍵字,則代表建立資料庫連結名稱的使用者,只能給自己使用,其它使用者則不能存取
何謂COMMENT
- 可以為物件加上註解,可針對表格,檢視,實體化檢視,表格欄位,檢視欄位,實體化檢視欄位,操作符,索引類型
格式
COMMENT ON object_type [schema.]object_name IS 'string';
object_name : 加上註解的物件種類
schema : 結構名稱
object_name : 加上註解的物件名稱
string : 註解內容
使用方式
說明 : 在hr使用者的emp表格加上註解,內容為'員工表格'
comment on table hr.emp is '員工表格';
說明 : 在emp表格的salary欄位加上註解,內容為'薪水'
comment on column emp.salary is '薪水';
何謂NOAUDIT
- 停止監控使用者對資料庫的操作,稱為停止稽核,可停止三種稽核,分別為陳述稽核,權限稽核,物件稽核
格式
NOAUDIT no_audit_option
[WHENEVER SUCCESSFUL | WHENEVER NOT SUCCESSFUL];
no_audit_option : 停止稽核目標
WHENEVER SUCCESSFUL : 停止稽核對於正確執行SQL與操作
WHENEVER NOT SUCCESSFUL : 停止稽核對於錯誤執行SQL與操作
no_audit_option格式
{statement_option [,statement_option...] | ALL}
[BY {proxy | user}]
statement_option : 陳述選項,例如INDEX,INSERT TABLE等SQL語句
ALL : 所有使用者的陳述選項
proxy : 代理伺服器名稱
user : 使用者名稱
{system_privilege [,system_privilege...] | ALL PRIVILEGES}
[BY {proxy | user}]
statement_option : 系統權限,例如CREATE ANY TABLE,CREATE ANY INDEX
ALL PRIVILEGES : 所有使用者的系統權限
proxy : 代理伺服器名稱
user : 使用者名稱
{object_option [,object_option...] | ALL}
{ON [schema.]object_name | ON DIRECTORY directory_name | ON DEFAULT}
object_option : 物件權限,例如SELECT,DELETE,UPDATE,INSERT
ALL : 所有使用者的物件權限
ON : 可以指定目標物件為表格,檢視,序列,預儲程序,預儲函數,預儲程式包裝,實體化檢視,函數庫
DEFAULT : 建立預設的物件選項,指定AUDIT執行後,所建立的物件選項會當成預設的稽核選項應用
schema : 結構名稱
object_name : 物件名稱
directory_name : 目錄名稱
使用方式
說明 : 停止權限稽核,針對hr使用者的alter any table系統權限
noaudit alter any table by hr;
說明 : 停止物件稽核,針對hr使用者emp表格的SELECT物件權限
noaudit select on hr.emp;
說明 : 停止陳述稽核,針對hr使用者執行DDL語句
noaudit index by hr;
何謂AUDIT
- 它被稱為稽核,所謂的稽核是可以監控使用者對資料庫的操作,可分為三種稽核方式,分別為陳述稽核,權限稽核,物件稽核,在使用稽核前,要先設定資料庫的稽核參數
何謂稽核參數
- 稽核參數又稱為AUDIT_TRAIL,它有多種值可以使用,以下是介紹常用的參數值
DB:稽核資料記錄在DBA_AUDIT_TRAIL表格裡
OS:稽核資料記錄在OS裡,所在的路徑為AUDIT_FILE_DEST參數所指定的目錄內
XML:稽核資料記錄在V$XML_AUDIT_TRAIL表格裡,也會存放在OS裡,檔案格式為xml檔,所在的路徑為AUDIT_FILE_DEST參數所指定的目錄內
NONE: 禁止使用資料庫稽核
格式
AUDIT audit_option
[BY {SESSION | ACCESS}]
[WHENEVER SUCCESSFUL | WHENEVER NOT SUCCESSFUL];
audit_option : 稽核目標
BY SESSION : 在同一連線中,多次執行稽核目標操作時,所有的操作輸出稽核資訊,此為預設值
BY ACCESS : 在同一連線中,多次執行稽核目標操作時,只對第一次操作輸出稽核資訊
WHENEVER SUCCESSFUL : 稽核正常執行的SQL與操作
WHENEVER NOT SUCCESSFUL : 稽核失敗或發生錯誤的SQL和操作
audit_option格式
{statement_option [,statement_option...] | ALL}
[BY {proxy | user}]
statement_option : 陳述選項,例如ALTER SYSTEM,ALTER INDEX等SQL語句
proxy : 代理伺服器名稱
user : 使用者名稱
ALL : 所有SQL語句稽核
{system_privilege [,system_privilege...] | ALL PRIVILEGES}
[BY {proxy | user}]
system_privilege : 系統權限,例如CREATE TABLE系統權限或CREATE INDEX系統權限等
proxy : 代理伺服器名稱
user : 使用者名稱
ALL PRIVILEGES : 所有權限稽核
{object_option [,object_option...] | ALL}
{ON [schema.]object_name | ON DIRECTORY directory_name | ON DEFAULT}
object_option : 成為稽核目標操作,例如DELETE,READ,INSERT,UPDATE等操作
ALL : 會稽核所有可在object_option指定操作
ON : 可以指定目標物件為表格,檢視,序列,預儲程序,預儲函數,預儲程式包裝,實體化檢視,函數庫
DEFAULT : 建立預設的物件選項,指定AUDIT執行後,所建立的物件選項會當成預設的稽核選項應用
schema : 結構名稱
object_name : 物件名稱
directory_name : 目錄名稱
使用方式
說明 : 稽核hr使用者擁有表格查詢操作
audit select table by hr;
說明 : 針對使用者hr使用create any table系統權限作稽核
audit create any table by hr;
說明 : 對hr使用者擁有的emp表格的查詢進行稽核
audit select on hr.emp;
何謂ANALYZE
- 它可以分析表格(table),索引(index),叢集(cluster)架構,並收集統計訊息
格式
ANALYZE object_clause validation_clause;
object_clause : 分析目標物件
validation_clause : 分析內容
object_clause格式
{TABLE | INDEX | CLUSTER} [schema.]object_name
schema : 結構名稱
object_name : 物件名稱
validation_clause格式
能對目標表格REF值的妥當性進行檢查,指定SET DANGLING TO NULL的情況中,若指定表格內REF值為無效物件,或者指定不存在物件情況中,REF值設為NULL
格式
VALIDATE REF UPDATE [SET DANGLING TO NULL]
驗證目標物件架構
格式
VALIDATE STRUCTURE [CASCADE]
[INTO [schema.]table_name]
[OFFLINE | ONLINE]
schema : 結構名稱
table_name : 物件名稱
CASCADE : 確認表格各列都擁有索引記錄,確認各項索引指向表格列
INTO : 只有在分割表格有效子句,擁有無法正確對應到分割表格列的情況中,則ROWID會在table_name指定存放的清單表格
OFFLINE | ONLINE : Oracle在DML執行驗證則指定ONLINE,但分析叢集(cluster)與 索引(index)無法指定ONLINE,預設為OFFLINE
可以辨識分析過的表格與叢集的遷徒列與鏈結列,INTO子句指定列出遷徒列與鏈結列清單表格
格式
LIST CHAINED ROWS [INTO [schema.]table_name]
schema:結構名稱
table_name:物件名稱
刪除統計訊息,刪除後Oracle將無法使用統計訊息,如果指定SYSTEM則只會刪除系統統計訊息
格式
DELETE [SYSTEM] STATISTICS
使用方式
說明:分析emp表格有關的索引
analyze table emp validate structure cascade;
說明:刪除emp表格統計資訊
analyze table emp delete statistics;
何謂ALTER SYSTEM SWITCH LOGFILE
- 切換目前online redo log group到下一組online redo log group,這時候redo log buffer會透過LGWR寫入到新的online redo log group,如果在Archive模式下,再切換的同時會產生archive redo log檔,如果在No Archive模式下,再切換的同時不會產生archive redo log檔
格式
ALTER SYSTEM SWITCH LOGFILE;
使用方式
說明:切換online redo log group
alter system switch logfile;
何謂ALTER SYSTEM...SESSION
- 它可以中斷使用者連線,需要使用到V$SESSION表格裡的sid欄位值與serial#欄位值進行中斷
格式
ALTER SYSTEM { KILL SESSION 'n1,n2'} [IMMEDIATE];
n1: V$SESSION表格裡的sid欄位值
n2: V$SESSION表格裡的serial#欄位值
使用方式
說明:查詢HR使用者的sid與serial#,然後中斷HR使用者連線
select sid,serial# from v$session where username='HR';
alter system kill session '140,336' immediate;
補充:如果該使用者的交易還沒結束,你中斷使用者,而使用者的資料會自動rollback;
何謂ALTER SYSTEM FLUSH
- 它可對記憶體(SGA)的資料做刪除,分別針對Database Buffer Cache與Shared Pool做刪除,其它的記憶體區塊無法做刪除
格式
ALTER SYSTEM FLUSH {SHARED_POOL | BUFFER_CACHE}
SHARED_POOL:指定Shared Pool區塊
BUFFER_CACHE:指定Database Buffer Cache區塊
使用方式
說明:刪除Shared Pool區塊資料
alter system flush shared_pool;
何謂ALTER SYSTEM CHECK DATAFILES
- 檢查是否能存取所有線上檔案(datafiles)資料,除了唯讀檔以外
格式
ALTER SYSTEM CHECK DATAFILES;
使用方式
說明:檢查是否能存取所有線上檔案(datafiles)資料,除了唯讀檔以外
alter system check datafiles;
何謂ALTER SYSTEM CHECKPOINT
- 強制性把Database buffer cache變更的資料寫入到硬碟裡的資料檔案裡(datafiles),並寫入檢查點訊息到CONTROLFILES與DATAFILES,執行這段語句是要確認資料庫的一致性
格式
ALTER SYSTEM CHECKPOINT;
使用方式
說明:強制執行檢查點
alter system checkpoint;
補充:Oracle Server會自動執行檢查點,可以不用手動去執行
何謂ALTER SYSTEM ARCHIVE LOG
- 針對online redo log group作切換,切換模式分為ARCHIVELOG模式與NOARCHIVELOG模式,如果是ARCHIVELOG模式,再切換的同時會把目前的online redo log group的資料訊息備份到FRA(flash_recovery_area)變成archive redo log檔,如果是NOARCHIVELOG模式,再切換的同時不會產生archive redo log檔
格式
ALTER SYSTEM ARCHIVE LOG [INSTANCE 'instance_name']
{SEQUENCE n |
CHANGE n |
CURRENT [NOSWITCH] |
GROUP n |
LOGFILE 'file_name' [USING BACKUP CONTROLFILE] |
NEXT |
ALL
} [TO 'location'] ;
instance_name:實體名稱(RAC環境才能指定)
SEQUENCE的n為redo log排序號碼
CHANGE的n為系統變更號碼(SCN)
GROUP的n為群組值
CURRENT:切換下一組online redo log group
file_name:redo log檔名稱
location:放置redo log檔位置
使用方式
說明:切換redo log group
alter system archive log current;
說明: 切換redo log group,手動指定redo log排序號碼,注意要在MOUNT狀態
alter system archive log sequence 8;
說明: 切換redo log group,手動指定群組值,注意要在MOUNT狀態
alter system archive log group 2;
何謂ALTER SYSTEN RESET
- 它可以刪除資料庫的實體層(SPFILE)參數,資料庫需要從新啟動才會生效
格式
ALTER SYSTEM RESET parameter_name;
parameter_name:參數名稱
使用方式
刪除起始參數TIMED_STATISTICS
alter system reset TIMED_STATISTICS;
何謂ALTER SYSTEM SET
- 針對資料庫的系統參數做設定,可分為兩種參數設定模式,第一種可設定在記憶體層,但當資料庫從新啟動,在記憶體層所設定的參數也就失效了,第二種可設定在實體層(SPFILE),它為永久生效,也就是說當資料庫從新起動也不會失效
格式
ALTER SYSTEM SET parameter_name=parameter_value
[SCOPE={MEMORY | SPFILE | BOTH}];
parameter_name:參數名稱
parameter_value:參數值
MEMORY:設定在記憶體層
SPFILE:設定在實體層,從新啟動資料庫才會生效
BOTH:設定在記憶體層與實體層,為馬上生效
使用方式
設定FRA(flash_recovery_area)空間為2G,馬上生效
alter system set db_recovery_file_dest_size=2G scope=both;
設定記憶體管理空間為手動管理,從新啟動資料庫就失效了
alter system set sga_target=0 scope=memory;
何謂SET ROLE
- 使用者連入資料庫的作業階段,它可以設定使用者的角色在作業階段中是使用或是停用
格式
SET ROLE {role IDENTIFIED BY password] | ALL [EXCEPT role] | NONE};
role:角色名稱
password:密碼
使用方式
設定使用者在作業階段中使用abc角色
set role abc;
設定使用者在作業階段中使用所有角色,停用aa角色
set role all except aa;
何謂ALTER SESSION CLOSE DATABASE LINK
格式
ALTER SESSION CLOSE DATABASE LINK dblink;
dblink:遠端連線名稱
使用方式
關閉遠端資料庫連線,名稱為db_1
alter session close database link db_1;
何謂ALTER SESSION SET
- 使用者連入資料庫,使用者可以使用ALTER SESSION SET語句動態設定作業階段參數,而如果使用者離線或斷線這項設定也就失效,除非再次連入資料庫,從新在設定一次才有效
格式
ALTER SESSION SET parameter_name=parameter_valus
parameter_name:更新作業階段參數名稱
parameter_valus:更新作業階段參數的值
使用方式
設定連線階段的時間格式,nls_date_format為資料庫的時間參數
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
補充:資料庫有多種參數,有需要再針對資料庫參數進行設定