Oracle資料庫常用操作命令

一、啟動和關閉Oracle資料庫

資料庫啟動

以SYSDBA身份登錄

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

資料庫啟動三個階段：

啟動Oracle實例（非安裝階段）

由實例安裝資料庫（安裝階段）

打開資料庫（打開階段）

資料庫的關閉

以SYSDBA身份登錄

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

資料庫關閉三個階段：

關閉資料庫

卸載資料庫

關閉Oravle實例

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

監聽器lsnrctl：提供資料庫訪問，默認埠1521

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

1.啟動監聽

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

2.關閉監聽

注意：先啟動監聽，後啟動資料庫。

三、表空間

創建表空間

參數解釋：

tablespacename：表空間名稱

DATAFILE：指定組成表空間的一個或多個數據文件，當有多個數據文件時使用逗號分隔

filename：表空間中數據文件的路徑和名稱

SIZE：指定文件的大小，用K指定千位元組大小，用M指定兆位元組大小

AUTOEXTEND：用來啟用或禁用數據文件的自動擴展

舉例：

表空間的管理

（1）調整表空間的大小。當表空間已滿的情況下，可以通過ALTER語句來調整表空間的大小。

方法一：更改數據文件的大小，並指明數據文件的存放路徑，通過使用RESIZE關鍵字，用於指定調整後的表空間的大小

方法二：向表空間內添加數據文件。為表空間添加一個新的數據文件

（2）改變表空間的讀寫狀態。

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

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

（3）刪除表空間，可以通過DROP語句來刪除表空間，再加上表空間的名字即可。

DROP TABLESPACE 表空間名 [INCLUDING CONTENTS] ;

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

四、用戶管理

創建用戶

舉例：

修改用戶的密碼

ALTER USER 用戶名 IDENTIFIED BY 密碼；

刪除用戶。使用DROP USER命令可以刪除用戶，當用戶擁有模式對象時則無法刪除用戶，而必須使用CASCADE選項以刪除用戶及用戶模式對象。

DROP USER 用戶名 CASCADE；

創建用戶前的準備工作：

1)選擇用戶名和密碼

2)識別用戶需用於存儲對象的表空間

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

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

5)創建用戶

6)向用戶授予許可權和角色

五、資料庫許可權管理

系統許可權

系統許可權是指在資料庫中執行某種系統級別的操作，或者針對某一類對象執行某種操作的權利

常用的系統許可權如下：

CREATE SESSION：連接到資料庫

CREATE TABLE：創建表

CREATE VIEW：創建視圖

CREATE SEQUENCE：創建序列

對象許可權

對象許可權是指針對某個特定模式對象執行操作的權利，只能針對模式對象來設置管理對象許可權，包括：表、視圖、序列、存儲過程等。

Oracle數據用戶有兩種途徑獲得許可權：

（1）直接向用戶授予許可權

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

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

（1）CONNECT：擁有連接資料庫的許可權

（2）RESOURCE：擁有創建表、觸發器、過程等許可權

（3）DBA：資料庫管理員角色，擁有管理資料庫的最高許可權

新建的用戶必須授予一定的許可權才能進行相關資料庫操作。授權通過GRANT語句，取消授權則通過REVOKE語句。

授予許可權語法格式如下：

GRANT 許可權 | 角色 TO 用戶名；

撤銷許可權語法格式如下：

REVOKE 許可權 | 角色 FROM 用戶名；

六、事務控制

COMMIT：提交事務，即把事務中對資料庫的修改進行永久保存。

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

自動提交： set autocommit on

關閉自動提交： set autocommit off

七、索引

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

1、索引的特點

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

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

建立索引的數量沒有限制

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

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

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

n全表掃描

n通過ROWID（行地址，快速訪問表的一行）

n使用索引

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

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，將其他數據落入此分區，一旦需要時可以利用拆分分區的技術將需要的數據從最後一個分區分離出入，單獨形成一個分區，如果沒有創建最大的分區，插入的數據查出範圍就會報錯。如果插入的數據是分區鍵上的值，則該數據落入下一個分區。

鏈接：http://blog.51cto.com/13555753/2103591

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！