詳解Oracle存儲結構 掌握基本操作管理

2018.10.14那天我寫了Oracle12C 的安裝并初步了解了一下Oracle體系結構中數據庫和實例。從中我們知道：

數據庫是磁盤上數據的集合，位于收集和維護相關信息的數據庫服務器上的一個或多個文件中。數據庫由各種物理和邏輯結構組成，而表則是數據庫中最重要的邏輯結構。表由包含數據的相關行和列組成。

• 組成數據庫的文件主要分為兩類：數據庫文件和非數據庫文件。兩者之間的區別在于存儲何種數據。數據庫文件包含數據和元數據，非數據庫文件則包含初始參數和日志記錄信息等。數據庫文件對于每時每刻正在進行的數據庫操作來說至關重要。
• 典型的企業服務器的主要組成部分是一個或多個CPU（有多個核）、磁盤空間和內存。Oracle數據庫存儲在服務器的磁盤上，而Oracle實例則存在于服務器的內存中。Oracle實例由一個大型內存塊和大量后臺進程組成；該內存塊分配在系統全局區域中，后臺進程在SGA和磁盤上的數據庫文件之間交互。
• 在OracleRAC中，多個實例將使用同一個數據庫。雖然共享數據庫的實例可能在同一服務器上，但最可能的是這些實例位于不同服務器上，這些服務器通過高速互連進行連接，并且訪問駐留在專門的，支持RAID磁盤子系統上的數據庫。Oracle Exadata數據庫一體機是一個將數據庫服務器、I/O服務器和磁盤存儲組合到一個或多個機柜，并針對RAC環境優化的示例（包括以每接口40Gbps的速度連接所有這些設備的雙InfiniBand接口）。

本篇開頭我將Oracle體系結構中的存儲結構先進行一個歸納：

• Oracle的存儲結構從兩方面看：
  1.Oracle邏輯存儲結構（表空間、塊、盤區、段）；
  2.Oracle物理存儲結構（數據文件、重做日志文件、控制文件、歸檔的日志文件、初始化參數文件、警報和跟蹤日志文件、備份文件、Oracle管理文件、密碼文件）；
• 在邏輯存儲結構下Oracle的邏輯數據庫結構：表、約束、索引、視圖、用戶和模式、配置文件、序列、同義詞、PL/SQL、外部文件訪問、數據庫鏈接和遠程數據庫；
• 另外Oracle 12C與之前版本最大的亮點是；支持數據庫熱插拔，可以和其他類型的數據庫對接

通過結構圖就能一目了然：

• Oracle邏輯存儲結構圖：
  

• Oracle邏輯數據庫結構圖：
  
• Oracle物理存儲結構圖：
  
  詳解Oracle邏輯存儲結構：

  Oracle數據庫中的數據文件被分組到一個或多個表空間中。在每個表空間中，邏輯數據庫結構（如表和索引）都是片段，被進一步細分為“盤區”（extent）和“塊”（block）。這種存儲的邏輯細分允許Oracle更有效的控制磁盤空間的利用率。

  1. 表空間：
     Oracle表空間由一個或多個數據文件組成，一個數據文件是且只能是一個表空間的一部分。對于Oracle12c的安裝，最少會創建兩個表空間：SYSTEM表空間和SYSAUX表空間。Oracle12c的默認安裝創建6個表空間；
  2. 段：
     數據庫中的下一個邏輯分組級別是段。段是一組盤區，這組盤區組成了被Oracle視為一個單位的數據庫對象，如表或索引。因此，段一般是數據庫終端用戶要處理的最小存儲單位。Oracle數據庫中可看到4種類型的段：數據段（非分區表和分區表的每個分區）、索引段、臨時段和回滾段。
  3. 盤區：
     它由一個或多個數據庫塊組成。當擴大數據庫對象時，為該對象添加的空間將分配為一個盤區。
  4. 塊：
     數據庫塊是Oracle數據庫最小的存儲單位。塊的大小是數據庫內給定表空間中特定數量的存儲字節。默認塊大小是8KB；

Oracle邏輯數據庫結構組成：

Oracle邏輯數據庫結構內容非常繁雜，這里先簡單概括一下都有哪些內容組成：
1.表： 是Oracle數據庫中的基本存儲單位，如果沒有表，數據庫對于企業來說就沒有任何價值；
包括： 關系表、臨時表、索引組織表、對象表、外部表、群集表、散列群集、排序的散列群集、分區表、分區索引；
2.約束：Oracle約束是一條或多條規則，它在表的一列或多列上定義，用于幫助實施業務規則；
包括：空值約束、唯一列值、主鍵值、引用完整性值、復合內聯完整性、基于觸發器的完整性；
3.索引：當檢索表少量的行時，使用Oracle索引能更快訪問表中的這些行；
包括：唯一索引、非唯一索引、反向鍵索引、基于函數的索引、位圖索引；
4.視圖：視圖允許用戶查看單獨表或多個連接表中數據的自定義表示。視圖也稱為“存儲查詢”，用戶無法看到視圖底層隱藏的查詢細節；
包括：普通視圖、物化視圖、對象視圖；
5.用戶和模式：有權訪問數據庫的數據庫賬戶稱為“用戶”。用戶可存在與數據庫中，而不擁有任何對象。如果用戶在數據庫中創建并擁有對象，這些對象就是與數據庫用戶同名的模式（schema）的一部分。模式可擁有數據庫中任何類型的對象：表、索引、序列和視圖等。模式擁有者或者DBA可授權其他數據庫用戶訪問這些對象。用戶總是擁有完整的權限，而且可以控制用戶模式中的對象；
6.配置文件：數據庫資源不是無限的，因此DBA必須為所有數據庫用戶管理和分配資源。數據庫資源的一些示例是CPU時間、并發會話、邏輯讀和連接時間。
數據庫配置文件是可以賦給用戶的限定資源的命令集。安裝Oracle后，DEFAULT配置文件已經存在，并且系統將其賦給任何還沒有顯式分配配置文件的用戶。DBA可添加新的配置文件或改變DEFAULT配置文件，從而符合企業的需求。DEFAULT配置文件的初始值允許無限使用所有的數據庫資源。
7.序列： Oracle序列用于分配有序數，并且保證其唯一性。
8.同義詞：Oracle同義詞（synonym）只是數據庫對象的別名，用于簡化對數據庫對象的引用，并且隱藏數據庫對象源的細節。同義詞可以賦給表、視圖、物化視圖、序列、過程、函數和程序包。與視圖類似，除了數據字典中的定義外，同義詞不會再數據中分配任何空間。
同義詞可以是公有或者私有。私有同義詞在用戶模式下定義，并且只有該用戶可用。公有同義詞通常由DBA創建，并且所有的數據庫用戶都可以自動使用。
9.PL/SQL：PL/SQL Developer是一個集成開發環境，專門開發面向Oracle數據庫的應用。PL/SQL也是一種程序語言，叫做過程化SQL語言（Procedural Language/SQL）。PL/SQL是Oracle數據庫對SQL語句的擴展。在普通SQL語句的使用上增加了編程語言的特點，所以PL/SQL把數據操作和查詢語句組織在PL/SQL代碼的過程性單元中，通過邏輯判斷、循環等操作實現復雜的功能或者計算。PL/SQL 只有 Oracle 數據庫有。

Oracle物理存儲結構：

物理存儲構的組成及其功能：
數據文件(.dbf)：用于存儲數據庫中的所有數據，包含系統數據、數據字典數據、臨時數據、索引數據、應用數據等
控制文件(.ctl)：用于記錄和描述數據庫的物理存儲結構信息
重做日志文件(.log)：用于記錄外部程序（用戶）對數據庫的修改操作
初始化參數文件：用于設置數據庫啟動時的參數初始值
跟蹤文件：用戶記錄用戶進程、數據庫后臺進程的運行情況
歸檔文件：用于保存已經寫滿的重做日志文件
口令文件：用于保存具有SYSDBA,SYSOPER權限的用戶名和SYS用戶口令。
數據文件及其管理：
在安裝Oracle數據庫時系統會自動為數據庫創建幾個數據文件，用戶對數據庫的操作本質是對數據文件的操作。一個表空間可以對應多個數據文件，一個數據文件只能從屬于一個表空間。在邏輯上，數據對象都存放在表空間中，實質上是存放在空間對應的數據文件中。

Oracle基本操作管理：

1.數據庫開啟、關閉

* 進入數據庫：
* su - oracle
* sqlplus / as sysdba                    #以最高管理員sysdba登錄
* SQL> help index                       #查看命令列表
* SQL> show user                      #查看當前用戶

* 開啟、關閉數據庫
* SQL>startup                                #開啟：實例--數據庫裝載--數據庫打開
* SQL>shutdown immediate        #關閉：數據庫關閉--卸載數據庫--實例

* 啟用、關閉監聽（作用：客戶端訪問oracle數據庫實例，安裝好數據庫如果重啟主機，則需要開啟監聽服務，客戶端才可以登錄oracle數據庫實例）
* lnsrctl start
* lnsrctl stop

2.創建數據庫：

* dbca   （在桌面界面執行）
#創建數據庫（如果不彈窗口執行: export DISPLAY=:0.0）
* 執行成功會跳出創建數據庫頁面，按以下步驟操作：

3.表空間操作：

創建表空間：

create tablespace school //創建表空間school
2 datafile '/oracle/app/oracle/oradata/school01.dbf' //指定表空間文件存放位置
3 size 10m autoextend on; //指定大小，空間可以擴容

調整表空間大小：

調整大小：
SQL> alter database datafile
2 '/oracle/app/oracle/oradata/school01.dbf'
3 resize 80m;
數據庫已更改。

添加文件：
SQL> alter tablespace school
2 add datafile
3 '/oracle/app/oracle/oradata/school02.dbf'
4 size 20m autoextend on;
表空間已更改。

表空間權限：

SQL> alter tablespace school read only; //修改為只讀權限
表空間已更改。
SQL> alter tablespace school read write; //權限修改為讀寫（默認）
表空間已更改。

刪除表空間：

SQL> drop tablespace school including contents;
表空間已刪除。

4.插拔數據庫（CDB與PDB轉換）：

SQL> show con_name //查看當前所在的容器
SQL> show pdbs; //查詢數據庫的所有容器

將當前數據庫CDB轉換為PDB：

CDB：默認的數據庫
PDB：容器型數據庫
SQL> alter pluggable database orclpdb open; //修改可插拔庫orclpdb打開狀態
插接式數據庫已變更。
SQL> alter session set container=orclpdb; //CDB下切換會話到PDB中
會話已更改。
SQL> shutdown immediate //在PBD中關閉插拔數據庫
插接式數據庫已關閉。
SQL> startup //在PBD中打開插拔數據庫
插接式數據庫已打開。

將PDB切換為CDB：

SQL> alter session set container=cdb$root;
 //切換會話到CDB

5.創建用戶：

* 創建用戶必須指定一個默認的表空間，在12C版本上CDB中創建用戶，用戶名格式為：c##用戶名；PDB中創建用戶，用戶名格式為：用戶名；
* SQL> create user c##jack
2 identified by abc123 //指定密碼
3 default tablespace users //指定默認表空間
4 temporary tablespace temp //指定臨時表空間
5 quota unlimited on users; //不進行限額
更改用戶密碼
SQL> alter user c##jack identified by jack123;
刪除用戶
SQL> drop user c##jack cascade;

6.創建數據表（插入數據，事務）

SQL> create table info
2 (
3 id number(4),
4 name varchar2(10),
5 score number(5,2),
6 riqi date
7 );

7.事務：

在oracle中默認開啟事務不進行提交。因此在進行數據操作時，一定要記得提交，或者設定為自動提交。否則數據不能寫入硬盤中。
SQL> insert into info values (1,'lisi',80,to_date('2018-08-26','yyyy-mm-dd'));
SQL> set autocommit on; //設定自動提交，回滾無效

8.索引：

（SQL>）
B樹索引
create index 索引名稱 on 表名（列名）
唯一索引/非唯一索引
create unique index 索引名稱 on 表名（列名）
反向索引
create index 索引名稱 on 表名（列名）reverse
位圖索引
create bitmap index 索引名稱 on 表名（列名）
其他索引
create index 索引名稱 on 表名（upper（列名）） //大寫函數索引
查看索引
select index_name,index_type,table_name,tablespace_name from user_indexes;
查看索引相關信息
select index_name,table_name,column_name from user_ind_columns where index_name like 'EMP%';
重建索引
alter index 索引名稱 rebuild;
alter index 索引名稱 rebuild tablespace 表空間
合并索引碎片
alter index 索引名稱 coalesce;
刪除索引
drop index 索引名稱

9.視圖：

創建視圖要注意權限問題
SQL> create view vinfo as select from info; //創建視圖
SQL> select from vinfo; //查詢視圖
SQL> drop view vinfo; //刪除視圖

10.物化視圖：

首先要通過管理員帳號為c##jack用戶賦予權限
SQL> grant create materialized view to c##jack; //創建物化視圖權限
SQL> grant query rewrite to c##jack; //查詢，重寫權限
SQL> grant create any table to c##jack; //創建所有表權限
SQL> grant select any table to c##jack; //查詢所有表權限

SQL>alter table info add primary key (id);   #添加主鍵
SQL>create materialized view log on info;  #創建物化視圖之前需要創建物化視圖日志

創建物化視圖：
SQL> create materialized view mtview 
2 build immediate //創建完成立馬生成新數據
3 refresh fast //刷新數據
4 on commit //提交
5 enable query rewrite //開啟查詢重寫功能
6 as
7 select * from info;

SQL> drop materialized view mtview; //刪除物化視圖

11.序列：

oracle中自增列需要單獨進行創建，然后插入數據時進行調用。
創建序列
SQL> create sequence id_seq
2 start with 10 //初始值
3 increment by 1 //增量
4 maxvalue 1000 //最大值
5 nocycle //肺循環
6 cache 50; //緩存

插入數據時調用序列
SQL> insert into info values (id_seq.nextval,'tom',80,to_date('2018-04-10','yyyy-mm-dd'));
查詢序列當前值
SQL> select id_seq.currval from dual;
更改序列
SQL> alter sequence id_seq cache 30;
查看序列信息
SQL> select sequence_name,increment_by,cache_size from user_sequences;
刪除序列
SQL>drop sequence id_seq;

12.同義詞：

俗稱別名，外號，可以提高數據安全可靠性。
私有同義詞：只在當前用戶可以使用
公有同義詞：所有數據庫用戶都可以使用
SQL> create synonym pr_info for info; //創建私有同義詞
SQL> create public synonym pub_info for info; //創建共有同義詞
刪除同義詞
SQL> drop synonym pr_info;
SQL> drop public synonym pub_info;

13.補充：
常用oracle數據庫查詢命令：

（1）Select * from tables（表名）；??//查詢表tables的內容
（2）Truncate table tables（表名）；//清除表內數據；但保留格式
（3）Select? ID（列名）from tables? groupby ID(列名)、ID2（列名）havingcount(*)＞1；?//查詢表中重復的數據
（4）Select * from tables（表名） a where a.id(列名)=’07552223’(重復的數據)；//將在列ID中所有與07552223相同的數據排列出來
（5）delete from tables where rowid in (select min(rowid)fromtables where id=’15252525’) group by username);??//刪除table表中ID列數據為‘15252525’的重復項
（6）delete from table where rowid in(select min(rowid)fromtable group by id..id2);? //刪除表table中所有關于列（ID）的重復信息
（7）select * from tables where regex_like(id，’^[^[:digit:]]+$’);? ??//查詢table在id列中所有是數字的數據
（8）select* from tables where regex_like(id，’^([a-z]|[A-Z]+[0-9]+)$’);? ???????? //查詢table在id列中所有帶有字符和數字的數據
（9）select count（*）fromtables；?//查詢table中所有數據的行數
（10）select table_name from xxcj_tj_zdcj a group by a.table_namecount(*) ＞1；?//查詢數據庫xxcj_tj_zdcj中是否有重復的表
（11）select * from xxcj_tj_zdcj awhere a.table_name in (‘05255’) order by a.table_name for update;? //修改數據庫xxcj_tj_zdcj中的值（即括號內的內容）
（12）select * from xxcj_tj_zdcj awhere a.id=’’ for update;//修改數據庫xxcj_tj_zdcj中id列的內容
（13）truncate table數據庫；?? //數據比對?
（14）select t.* ，t.rowid from 表名 t; //編輯表
（15）alter table 表名 add 列名 字符類型；//新建列
（16）alter table 表名 modify 列名 字符類型；//修改列類型
（17）alter table 表名 drop column 列名；//刪除列（最后一列）
（18）select * from 表名 where asciistr（列名）not like ‘%\%’；//查詢數據表中xx列中的為中文字符的數據
（19）select? substr(f_get_sfzhm18(zjhm) ，7，8)as csrq from 表名where regexp_like(substr(f_get_sfzhm18(zjhm) ，7，1) ，’[^1[2]’);