歡迎光臨
每天分享高質量文章

Oracle資料庫常用十一大操作指令

活動預告:

 

ACOUG 成都 2019 於4月27日在成都舉辦,歡迎參會,馬上報名:2019 ACOUG China Tour 成都站

 

DBA們每天會用到大大小小很多指令,本文對這些指令做了歸納總結,希望可以幫助大家查閱翻看,喜歡記得轉發分享+“在看”哦。

 

一、啟動和關閉Oracle資料庫


 

1. 資料庫啟動

以SYSDBA身份登錄

資料庫啟動命令:STARTUP 【啟動選項】

 

資料庫啟動三個階段:

  • 啟動Oracle實體(非安裝階段)
  • 由實體安裝資料庫(安裝階段)
  • 打開資料庫(打開階段)

 

 

2. 資料庫的關閉

  以SYSDBA身份登錄

 資料庫關閉命令:SHUTDOWN 【啟動選項】

 

資料庫關閉三個階段:

  •  關閉資料庫
  • 卸載資料庫
  • 關閉Oravle實體

 

 

 

二、啟動、關閉Oracle監聽行程


 

監聽器lsnrctl:提供資料庫訪問,預設端口1521

為了使客戶端用戶能連接到Oracle實體,要啟動監聽

 

1.啟動監聽

啟動(START)監聽是Oracle用戶在操作系統下執行的命令,可以直接在LSNRCTL後加引數,也可以在該命令提示符後在進行操作。

 

 

2.關閉監聽

 

註意:先啟動監聽,後啟動資料庫。

 

三、表空間


 

1. 創建表空間

 

引數解釋:

tablespacename:表空間名稱

DATAFILE:指定組成表空間的一個或多個資料檔案,當有多個資料檔案時使用逗號分隔

filename:表空間中資料檔案的路徑和名稱

SIZE:指定檔案的大小,用K指定千位元組大小,用M指定兆位元組大小

AUTOEXTEND:用來啟用或禁用資料檔案的自動擴展

 

舉例:

 

2. 表空間的管理

(1)調整表空間的大小。當表空間已滿的情況下,可以通過ALTER陳述句來調整表空間的大小。

 方法一:更改資料檔案的大小,並指明資料檔案的存放路徑,通過使用RESIZE關鍵字,用於指定調整後的表空間的大小

 

  

 

方法二:向表空間內添加資料檔案。為表空間添加一個新的資料檔案

  

 

(2)改變表空間的讀寫狀態。

 

 ALTER  TABLESPACE  表空間名  READ   WHITE ;      --使表空間可讀寫 ALTER  TABLESPACE  表空間名  READ   ONLY ;       --使表空間只讀

 

(3)刪除表空間,可以通過DROP陳述句來刪除表空間,再加上表空間的名字即可。

 DROP  TABLESPACE  表空間名 [INCLUDING  CONTENTS] ;

其中INCLUDING  CONTENTS是可選項。如果刪除仍包含資料的表空間,需要加上該選項。

 

四、用戶管理


 

1. 創建用戶

 

舉例:

 

2. 修改用戶的密碼

ALTER  USER  用戶名  IDENTIFIED  BY  密碼 ;

 

3. 刪除用戶。使用DROP USER命令可以刪除用戶,當用戶擁有樣式物件時則無法刪除用戶,而必須使用CASCADE選項以刪除用戶及用戶樣式物件。

 

DROP  USER  用戶名  CASCADE;

 

創建用戶前的準備工作:

1)      選擇用戶名和密碼

2)      識別用戶需用於儲存物件的表空間

3)      決定每個表空間的限額

4)      分配預設表空間和臨時表空間

5)      創建用戶

6)      向用戶授予權限和角色

 

五、資料庫權限管理


 

1. 系統權限

 

系統權限是指在資料庫中執行某種系統級別的操作,或者針對某一類物件執行某種操作的權利

 

常用的系統權限如下:

CREATE SESSION:連接到資料庫

CREATE TABLE:創建表

CREATE VIEW:創建視圖

CREATE SEQUENCE:創建序列

 

2. 物件權限

物件權限是指標對某個特定樣式物件執行操作的權利,只能針對樣式物件來設置管理物件權限,包括:表、視圖、序列、儲存過程等。

 

 

Oracle資料用戶有兩種途徑獲得權限:

(1)直接向用戶授予權限

(2)將權限授予給角色,再將角色授予給一個或多個用戶。使用角色能夠更加方便和高效地對權限進行管理,所以資料庫管理員通常使用角色向用戶授予權限,而不是直接向用戶授予權限。

 

Oracle中常用系統預定義角色如下:

(1)CONNECT:擁有連接資料庫的權限

(2)RESOURCE:擁有創建表、觸發器、過程等權限

(3)DBA:資料庫管理員角色,擁有管理資料庫的最高權限

 

3. 新建的用戶必須授予一定的權限才能進行相關資料庫操作。

 

授權通過GRANT陳述句,取消授權則通過REVOKE陳述句。

 

授予權限語法格式如下:

 

GRANT   權限 | 角色   TO    用戶名;

 

撤銷權限語法格式如下:

REVOKE     權限  |  角色    FROM    用戶名;

 

六、事務控制


 

 

COMMIT:提交事務,即把事務中對資料庫的修改進行永久儲存。

ROLLBACK:回滾事務,即取消對資料庫所做的任何修改

自動提交:set  autocommit on

關閉自動提交:set autocommit off

 

七、索引


 

索引是oracle的一個物件,是與表關聯的可選結構,提供了一種快速訪問資料的途徑,提高了資料庫檢索性能。索引使資料庫程式無需對整個表進行掃描,就可以在其中找到所需要的資料。就像書的目錄,可以通過目錄快速查找所需信息,無需閱讀整本書。

 

1、索引的特點

適當地使用索引可以提高查詢速度

可以對錶的一列或多列建立索引

建立索引的數量沒有限制

索引需要磁盤儲存,可以指定表空間,由oracle自動維護

索引對用戶透明,檢索時是否使用索引由oracle自身決定

 

Oracle資料庫管理系統在訪問資料時使用以下三種方式:

  • 全表掃描
  • 通過ROWID(行地址,快速訪問表的一行)
  • 使用索引

 

當沒有索引或者不選擇使用索引時就用全表掃描的方式

 

2.索引的分類

 

(1)創建普通索引(B樹索引)

 

引數解釋:

index_name:創建索引的名稱

tablename:為之創建索引的表名

columnname:在其上創建索引的列名串列,可以基於多列創建索引,列之間用逗號分隔

tablespace:為索引指定表空間

 

(2)創建唯一索引

保證定義索引的列中沒有任何兩行有重覆值。唯一索引中的索引關鍵字只能指向表中的一行。

 

(3)創建反向鍵索引

與常規B樹索引相反,反向鍵索引在保持列順序的同時反轉索引列的位元組。反向鍵索引通過反轉索引鍵的資料值,使得索引的修改平均分佈到整個索引樹上。主要應用於所多個實體同時訪問一個資料庫的場景中。

 

(4)創建位圖索引

 

優點:相對於B樹索引而言,基於位圖索引列的查詢可以減少響應時間。

 

相比其他索引技術,位圖索引占用空間明顯減少。

 

 

(5)其他索引

 

組合索引:在表內多列上創建。索引中的列不必與表中的列順序一致,也不必相互鄰接。

基於函式的索引:需要創建的索引需要使用表中一列或多列的函式或運算式,也可以將基於函式的索引創建為B樹索引或位圖索引

 

 

3. 創建索引的原則

 

頻繁搜索的列可以作為索引列

經常排序,分組的列可以作為索引

經常用作連接的列(主鍵/外鍵)可以作為索引

將索引放在一個單獨的表空間中,不要放在有回退段、臨時段和表的表空間中

對於大型索引而言,考慮使用NOLOGIN子句創建大型索引。

      根據業務資料發生頻率,定期重新生成或重新組織索引,進行碎片整理

 

4. 查看索引

 

和索引有關的資料字典:

USER_INDEXS:用戶創建的索引信息

USER_IND_COLUMNS:與索引相關的表列信息

 

5. 維護索引

 

(1)重建索引

索引需要維護,如果建立了索引的表中有大量的刪除和插入操作,會使得索引很大,因為刪除操作後,刪除值的索引空間不能被自動重新使用,對於大表和DML操作很頻繁的表,索引的維護是很重要的。Oracle提供了rebuild指令來重建索引。使索引空間可以重用刪除值所占用的空間,使索引更加緊湊。

 

(2)合併索引碎片

合併索引碎片可以釋放部分磁盤空間,是索引維護的一種重要方式,也是維護磁盤空間的方式,類似於磁盤碎片整理,把不用的空間釋放出來再利用。

 

(3)重命名索引

 

(4)刪除索引

 

八、視圖


 

視圖是一個虛表,不占用物理空間,因為視圖本身的定義陳述句儲存在資料字典里,視圖中的資料是一個或多個實際表中獲得的。那些用於產生視圖的表叫做該視圖的基表。一個視圖也可以從另一個視圖中產生。

 

1. 視圖的優點:

1)      提供了另外一種級別的表安全性

2)      隱藏的資料的複雜性:一個視圖可能是用多表連接定義的,但用戶不需要知道多表連接的陳述句也可以查詢資料。

3)      簡化的用戶的SQL命令:查詢視圖的時候不需要寫出複雜的查詢陳述句,只需要查詢視圖名稱即可。

4)      隔離基表結構的改變:視圖創建好了之後,如果修改了表的結構,也不會影響視圖的。

5)通過重命名列,從另一個角度提供資料:例如在銷售系統中,每日下班前要對當日資料進行彙總,在銷售人員眼中,該彙總表成為日銷售統計表,在財務人眼中,該銷售表成為銷售日報表

 

2. 創建視圖

引數解釋:

 

OR REPLACE:如果視圖已存在,此選項將重新創建該視圖。

FORCE:如果使用此關鍵字,則無論基表是否存在,都將創建視圖。

NOFORCE:這是預設值,如果使用此關鍵字,則僅當基表存在時才創建視圖。

VIEW_NAME:要創建視圖的名稱

Alias:指定由視圖的查詢所選擇的運算式或列的別名。別名的數目必須與視圖所選擇的運算式的數目相匹配。

Select_statement:select陳述句

WITH CHECK OPTION :此選項指定只能插入或更新視圖可以訪問的行。術語constraint表示為CHECK OPTION約束指定的名稱。

WITH READ ONLY:此選項保證不能在此視圖上執行任何修改操作。

 

3. DML陳述句和複雜視圖

DML陳述句是指用於修改資料的insert、delete和update陳述句。因為視圖是一個虛擬的表,所以這些陳述句也可以與視圖一同使用。一般情況下不通過視圖修改資料,而是直接修改基表,因為這樣條例更清晰。

 

在視圖上使用DML陳述句有如下限制(相對於表)。

1)DML陳述句只能修改視圖中的一個基表。

2)如果過記錄的修改違反了基表的約束條件,則將無法更新視圖。

3)如果創建的視圖包含連接運算子,DISTINCT運算子、集合運算子、聚合函式和groupby子句,則將無法更新視圖。

4)如果創建的視圖包含偽串列達式,則將無法更新視圖。

簡單視圖基於單個基表,不包括函式和分組函式,那麼可以在此視圖中進行insert、update、delete操作,這些操作實際上在基表中插入、更新和刪除行。

 

複雜視圖從多個表提取資料,包括函式分組函式。複雜視圖不一定能進行DML操作。

 

4. 查詢視圖

select    view_name   from    user_views;

 

5. 刪除視圖

drop  view   view_name;

 

6. 物化視圖

含義:就是具有物理儲存的特殊視圖,占據物理空間,就像表一樣

是遠程資料的本地副本,或者用來生成基於資料表求和的彙總表

 

 

物化視圖中兩個重要概念:查詢重寫和物化視圖同步

(1)查詢重寫:對sql陳述句進行重寫,當用戶使用sql陳述句對基表進行查詢時,如果已經建立了基於這些表的物化視圖,oracle將自動計算和使用物化視圖來完成查詢,在某些情況下可以節約查詢時間,減少系統i/o。Oracle將這種查詢優化技術成為查詢重寫。引數QUERY_REWRITE_ENABLED決定是否使用重寫查詢,該引數為布爾型。在創建物化視圖需要用ENABLE_QUERY REWRITE來啟動查詢重寫功能。通過SHOW指令可以查看該引數的值。

 

(2)物化視圖的同步:

物化視圖是基於表創建的,所以當基表變化時,需要同步資料以更新物化視圖中的資料,這樣保持物化視圖中的資料和基表的資料一致性。Oracle提供了兩種物化視圖的掃清方式,決定何時進行掃清,即ON COMMIT方式和ON DEMAND方式。

 

ON COMMIT方式:指物化視圖在對基表的DML操作事務提交的同時進行掃清。

 

ON DEMAND方式:指物化視圖在用戶需要的時候進行更新,可以手工通過DBMS_MVIEW.REFRESH等方式來進行掃清,也可以通過JOB定時進行掃清。

 

選擇掃清方式之後,還需要選擇一種掃清型別,掃清型別指定掃清時基表與物化視圖如何實現資料的同步,oracle提供了以下4種掃清型別。

 

COMPLETE:對整個物化視圖進行完全的掃清。

FAST:採用增量掃清,只掃清自上次掃清後進行的修改。

FORCE:oracle在掃清時會去判斷是否可以進行快速掃清,如果可以則採用FAST方式,否則採用COMPLETE方式。

NEVER:物化視圖不進行任何掃清。

預設值是FORCE掃清型別。

 

創建物化視圖

(1)授予權限,具備創建物化視圖的權限、QUERY  REWRITE的權限,以及對創建物化視圖所涉及的表的訪問權限和創建表的權限。

通過SCOTT用戶來演示

 

(2)創建物化視圖日誌

 

(3)創建物化視圖陳述句

其中:

bulid immediate:該引數的含義是立即創建物化視圖,也可以選擇build  deffered,該引數說明在物化視圖定以後不會立即執行,而是延遲執行,在使用該視圖時再創建。

Reffesh fast:掃清資料的型別選擇FAST型別。

ON COMMIT:在基表有更新時提交後立即更新物化視圖。

ENABLE QUERY REWRITE:啟動查詢重寫功能。在創建物化視圖時明確說明啟用查詢重寫功能。

As:定義後面的查詢陳述句。

查詢體:物化視圖的查詢內容,該sql陳述句的查詢結果集輸出到物化視圖中,儲存在由oracle自動創建的表中。

 

(4)刪除物化視圖

drop  materialized  view  view_name;

 

九、序列


 

序列是用來生成唯一、連續的整數資料庫物件。序列通常用來自動生成主鍵或唯一鍵的值。序列可以按升序排列,也可以按降序排列。

 

1. 創建序列

 

引數解釋:

  • START WITH:指定要生成的第一個序列號,對於升序序列,其預設值為序列的最小值,對於降序序列,其預設值為序列的最大值。
  • INCREMENT BY:用於指定序列號之間的間隔,預設值為1,如果n為正值,則生成的序列將按升序排序,如果n為負值,則生成的序列按降序排列。
  • MAXVALUE:指定序列可以生成的最大值
  • NOMAXVALUE:如果指定了NOMAXVALUE,oracle將升序序列的最大值設為1027,將降序序列的最大值設為-1。
  • MINVALUE:指定序列最小值。MINVALUE必須小於或等於START WITH的值,並且必須小於MAXVALUE。
  • NOMINVALUE:如果指定了NOMINVALUE,oracle將升序序列的最小值設為1,或將降序列的值設置為-1026。
  • CYCLE:指定序列在達到最大值或最小值後,將繼續從頭開始生成值。
  • NOCYCLE:指定序列在達到最大值或最小值後,將不在繼續生成值。
  • CHCHE:使用CACHE選項可以預先分配一組序列號,並將其保留在記憶體中,這樣可以更快地訪問序列號,當用完快取中的所有序列號時,oracle將生成另一組數值,並將其保留在快取中。
  • NOCACHE:使用NOCACHE選項,則不會為加快訪問速度而預先分配序列號。如果在創建序列時忽略了CACHE和NOCACHE選項,oracle將預設快取20個序列號。

 

2. 訪問序列

創建了序列之後,可以通過NEXTVAL和CURRVAL偽列來訪問該序列的值。可以從偽列中選擇值。但是不能操縱他們的值。

 

NETXVAL:創建序列後第一次使用NEXTVAL時,將傳回該序列的初始值。以後再取用NETXVAL時,將使用INCREMENT BY子句的值來增加序列值,並傳回這個新值。

CURRVAL:傳回序列的當前值,即最後一次取用NEXTVAL時傳回的值

 

舉例:

創建序列

 

創建表

 

插入資料

 

查看資料

 

查看序列的當前值

 

Currval傳回序列的當前值,即最後一次取用NEXTVAL時傳回的值

 

測試currval

 

重啟實體之後再次寫入資料發現從40開始,因為按創建序列的要求,每次會拿30個序列號放到快取中,實體重啟後,快取中的序列就會消失

 

 

3. 更改序列

Alter sequence命令用於修改序列的定義。如果要進行下列操作,則會修改序列。

設置或刪除MINVALUE或MAXVALUE

修改增量值

修改快取中的序列號的數目

不能修改序列的START WITH引數

 

4. 刪除序列

 

十、同義詞


 

同義詞是物件的一個別名,不占用任何的實際儲存空間,只在oracle的資料字典中儲存其定義描述,在使用同義詞時,oracle會將其翻譯為對應物件的名稱。

 

1. 用途:

  •    簡化SQL陳述句
  •    隱藏物件的名稱和所有者
  •    為分佈式資料庫的遠程物件提供位置透明性
  •    提供對資料庫物件的公共訪問

 

創建資料庫鏈接的語法:

CREATE DATABASE LINK 資料庫鏈接名 CONNECT TO user名 IDENTIFIED BY 口令 USING ‘Oracle鏈接串’;

 

2. 同義詞的分類

 

(1)私有同義詞

私有同義詞只能被當前樣式的用戶訪問,私有同義詞名稱不可與當前樣式的物件名稱相同。要在自身的樣式創建私有同義詞,用戶必須擁有create synonym系統權限。要在其他用戶樣式創建私有同義詞,用戶必須擁有create any  synonym系統權限。

 

語法:

CREATE [OR REPLACE] SYNONYM  [schema.]synonym_name  FOR  [schema.]object_name ;

 

 

引數解釋:

  • [OR REPLACE]:在同義詞存在的情況下替換該同義詞
  • synonym_name:要創建同義詞的名稱
  • object_name:指定要為之創建同義詞的物件的名稱

 

(2)共有同義詞

公有同義詞被所有的資料庫訪問。公有同義詞可以隱藏基表的身份,並降低sql陳述句的複雜性。要創建公有公有同義詞,用戶必須擁有create public SYNOYM的系統權限。

語法:

CREATE   [OR REPLACE]     PUBLIC    SYNONYM    [schema.]synonym_name    FOR    [schema.]object_name ;

 

 

(3)刪除同義詞

DROP [PUBLIC] SYNONYM  [schema.]synonym_name;

 

 

十一、分割槽表


 

1. 含義:

Oracle允許把一個表重的所有行分成幾個部分,並將它們儲存在不通的表空間,分成的每一部分成為一個分割槽,被分割槽的表成為分割槽表。

 

對於包含大量資料的表來說,分割槽很有用,優點有以下幾點:

1)改善表的查詢性能。在對錶進行分割槽後,用戶執行sql查詢時可以只訪問表中的特定分割槽而非整個表。

2)表更容易管理。因為分割槽表的資料儲存在多個部分中,按分割槽加載和刪除資料比在表中加載和刪除更容易。

3)便於備份和恢復。可以獨立地備份和恢復每個分割槽。

4)提高資料安全性。將不同的分割槽分佈在不同的磁盤,可以減少所有分割槽的資料同時損壞的可能性。

 

符合一下條件的表可以建成分割槽表:

1)資料量大於2GB。

2)已有的資料和新添加的資料有明顯的界限劃分。

表分割槽對用戶是透明的,及應用程式可以不知道表已被分割槽,在更新和查詢分割槽表時當做普通表來操作,但oracle優化程式知道表已被分割槽。

 

2. 分割槽表的分類

Oracle提供的分割槽方法有範圍分割槽、串列分割槽、散列分割槽、複合分割槽、間隔分割槽和虛擬列分割槽等。

 

3. 舉例

 

(1)創建表並且分割槽,以age分割槽

 

(2)向表中插入資料

(3)查詢P100分割槽的資料

 

查詢P200分割槽的資料

 

(4)如果向表中插入以下記錄,會提示插入的分割槽關鍵字未映射到任何分割槽

 

(5)按範圍分割槽是,如果某些記錄暫時無法預測範圍,可以創建maxvalue分割槽,所有不在指定範圍內的記錄都會被儲存到maxvalue所在的分割槽中

 

(6)再次插入以下資料

 

(7)查詢

 

(8)查看所有分割槽的命令

 

一般創建範圍分割槽時都會將最後一個分割槽設置為maxvalue,將其他資料落入此分割槽,一旦需要時可以利用拆分分割槽的技術將需要的資料從最後一個分割槽分離出入,單獨形成一個分割槽,如果沒有創建最大的分割槽,插入的資料查出範圍就會報錯。如果插入的資料是分割槽鍵上的值,則該資料落入下一個分割槽。

    已同步到看一看
    赞(0)

    分享創造快樂