知識星球活動預告：
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，將其他資料落入此分割槽，一旦需要時可以利用拆分分割槽的技術將需要的資料從最後一個分割槽分離出入，單獨形成一個分割槽，如果沒有建立最大的分割槽，插入的資料查出範圍就會報錯。如果插入的資料是分割槽鍵上的值，則該資料落入下一個分割槽。
朋友會在“發現-看一看”看到你“在看”的內容