大数据-玩转数据-Oracle系统知识小结
一、說明
Oracle數據庫是甲骨文的核心產品，Oracle 的操作都遵循sql標準，所以各個版本在基本操作方面都變化不大。Oracle劃分在大型關系數據庫范疇，比較Mysql或Nosql數據庫來說，還是缺少些擴容的靈活性，但傳統金融、通信、電力、航空、保險、大型企業用得比較多。
二、背景知識、數據建模和Linux
1.關系數據庫
關系型數據庫，是指采用了關系模型來組織數據的數據庫，其以行和列的形式存儲數據，以便于用戶理解，關系型數據庫這一系列的行和列被稱為表，一組表組成了數據庫。用戶通過查詢來檢索數據庫中的數據，而查詢是一個用于限定數據庫中某些區域的執行代碼。關系模型可以簡單理解為二維表格模型，而一個關系型數據庫就是由二維表及其之間的關系組成的一個數據組織。主流的關系數據庫有oracle、db2、sqlserver、sybase、mysql等。
關系模型滿足的確定約束條件稱為范式，關系型數據庫的三范式：
根據滿足約束條件的級別不同，范式由低到高分為：1NF（第一范式）、2NF（第二范式）、3NF（第三范式）、BNF（BC范式）、4NF（第四范式），不同級別的范式性質不同。
第一范式（1NF）
1NF是最低的規范化要求。如果關系R中所有屬性的值域都是簡單域，其元素（即屬性）不可再分，是屬性項而不是屬性組，那么關系模型R是滿足第一范式的。通俗點也就是說一張關系表中，所有的字段的值都必須是不可再拆分的。例如：表1所示結構就不滿足1NF的定義。
第二范式（2NF）
如果一個關系 R 屬于 1NF，且所有的非主屬性都完全依賴于主屬性，則稱之為第二范式（也就是除了主鍵以外的其他字段，都必須完全依賴于主鍵）。例如表3所示結構就不滿足第二范式。
第三范式（3NF）
如果一個關系 R 屬于 2NF，且每個非主屬性不傳遞依賴于主屬性，這種關系是 3NF。為了理解這種關系，將以表6中的字段作為講解。
2.數據庫設計
關系數據建模設計工具常用有Erwin，PowerDesigner.完成模型設計后將其轉化為各種類型的對象和關系。
個人總結了數據庫設計對象命名規則：
數據庫對象命名規則
2.1 約定
a.數據庫對象如表、字段、索引、序列、存儲過程等的命名約定；
b.命名使用富有意義的英文詞匯，中間以下劃線分割；
c.避免使用Oracle的保留字如LEVEL、關鍵字如TYPE（見Oracle保留字和關鍵字）；
d.各表之間相關列名盡量同名；命名只能使用英文字母，數字和下劃線；
e.索引和數據使用單獨表空間存放
2.2 表名
報表: RPT_TABLEANME
臨時表:TMP_TABLEANME（一次性使用的臨時表用完及時清理；非程序使用臨時表，表名須包含使用人標識，以便清理；如WANGXF/ZHOUSY/CAOY/…）
系統類型:SYS_TABLEANME
子系統表采用:xxx_TABLEANME（xxx指子系統名稱）
共用類的表:PUB_TABLENAME
各業務類型:T_TABLENAME（TABLENAME指業務類型）；
程序及系統表名須完善表注釋；
例如：
2.3 存儲過程
存儲過程采用PROC_TABLENAME
存儲過程代碼書寫全部采用范例PROC_DEMO
2.4 索引
索引采用IDX_TABLENAME_SEQ (SEQ指序號)，并指定對應的表空間
例如：
2.5 主外鍵
主鍵采用PK_TABLENAME
外鍵采用FK_TABLENAME
例如：
2.6 函數
1、函數采用F_XXXX (XXXX 業務含義)
例如：
CREATE OR REPLACE FUNCTION F_GET_AGE–該函數獲取年齡
2.7 表空間命名
表空間命名:TBS_SCHEMANAME_DATA TBS_SCHEMANAME_IDX
數據文件: tbs_schemaname_dataxx.dbf
tbs_schemaname_idxxx.dbf其中xx代表序號
2.8 字段命名
1、業務含義:對應的英文縮寫，切記使用關鍵字，盡量使用varchar2代替char類型，時間類型不要用字符串，使用NUMBER數據類型時給定長度
2、系統及程序使用表須完善字段注釋
Oracle關鍵字:
ACCESS DECIMAL INITIAL ON START ADD NOT INSERT ONLINE SUCCESSFUL ALL DEFAULT INTEGER OPTION SYNONYM ALTER DELETE INTERSECT OR SYSDATE AND DESC INTO ORDER TABLE ANY DISTINCT IS PCTFREE THEN AS DROP LEVEL PRIOR TO ASC ELSE LIKE PRIVILEGES TRIGGER AUDIT EXCLUSIVE LOCK PUBLIC UID BETWEEN EXISTS LONG RAW UNION BY FILE MAXEXTENTS RENAME UNIQUE FROM FLOAT MINUS RESOURCE UPDATE CHAR FOR MLSLABEL REVOKE USER CHECK SHARE MODE ROW VALIDATE CLUSTER GRANT MODIFY ROWID VALUES COLUMN GROUP NOAUDIT ROWNUM VARCHAR COMMENT HAVING NOCOMPRESS ROWS VARCHAR2 COMPRESS IDENTIFIED NOWAIT SELECT VIEW CONNECT IMMEDIATE NULL SESSION WHENEVER CREATE IN NUMBER SET WHERE CURRENT INCREMENT OF SIZE WITH DATE INDEX OFFLINE SMALLINT CHAR VARHCAR VARCHAR2 NUMBER DATE LONG CLOB BLOB BFILE INTEGER DECIMAL SUM COUNT GROUPING AVERAGE TYPE3.Linux
見Linux專題文章。
三、體系結構、模式和事務管理
1. 幾個重要的Oracle術語
要學習Oracle的體系結構，先要搞明白幾個重要的術語：Oracle服務器、Oracle實例、Oracle數據庫。
Oracle服務器：即Oracle server，由Oracle實例和Oracle數據庫組成。
Oracle實例：即Oracle instance，是在Oracle啟動的第一個階段根據參數文件，生成的一系列的后臺進程和一塊共享內存SGA共同組成。
Oracle數據庫：即Oracle database，是由Oracle所有的物理文件所組成。其中最關鍵的有：控制文件、數據文件、redo log文件等。
Oracle實例與Oracle數據庫進行交互，Oracle實例來對數據庫進行各種操作，從而對外提供數據庫的存儲和檢索服務。
2. Oracle總體結構
Oracle server由Oracle instance和Oracle database組成。而Oracle instance又由后臺進程和共享內存組成，所以Oracle的結構又包含了內存結構和進程結構；而Oracle database有物理文件組成，所以Oracle結構也包含了存儲結構。
下面分別對Oracle內存結構、Oracle進程結構、Oracle存儲結構進行概述，讓我們對Oracle有一個對初步的概念。
3. Oracle內存結構
總體而言Oracle的內存由兩大部分組成：PGA和SGA，其結構如下圖所示：
4. Oracle進程結構
Oracle的進程主要有后臺進程和Server process(其實按照Linux的嚴格意義來說，Server process也是屬于后臺進程)。后臺進程主要對Oracle數據庫進程各種維護和操作，而Server process主要來處理用戶的請求：
用戶進程通過監聽器來訪問Oracle instacne，那么就會觸發生成一個Server process進程，來對該用戶進程的請求進程處理。后臺進程一般有：LGWR, DBWn, ARCn, CKPT, SMON, PMON等等。
1)DBWn(database writer數據庫寫)：
主要作用是將被修改過的buffer cache按照一定的條件寫入物理磁盤。
2)LGWR(log writer，日志寫)：
主要作用是將log buffer中的redo log記錄按照一定的條件寫入聯機的redo log文件。
3)CKPT(checkpoint,檢查點進程)：
主要作用是將檢查點位置(checkpoint position)寫入控制文件和數據文件的頭部。
4)SMON(system monitor,系統監控進程)：
主要作用是在數據庫啟動時，判斷實例上次是否正常關閉，如果是非正常關閉，則進程實例恢復。另外，還會合并相連的可以空間。
5)PMON(process monitor,進程監控)：
監控Server process, 如果Server process非正常關閉，則PMON負責清理它占用的各種資源。
5. Oracle存儲結構
存儲結構即物理文件的組成結構，Oracle涉及的物理文件如下所示：
其中的控制文件、數據文件、重做日志文件是不可或缺的關鍵文件：
1)control file(控制文件)：
包含了數據庫物理結構的信息，比如各種文件的存放位置，當前數據庫的運行狀態等。十分重要，丟失則數據庫實例不能啟動。
2)datafile(數據文件)：
存放數據的文件。
3)online redo log file(聯機重做日志文件)：
存放redo log的文件。維護數據庫的一致性，用于數據庫恢復。
6. Oracle邏輯結構與物理結構關系
四、數據庫對象管理
1. Oracle的啟動與關閉
啟動Oracle
1.#su - oracle 切換到 oracle 用戶且切換到它的環境 2.$lsnrctl status 查看監聽及數據庫狀態 3.$lsnrctl start 啟動監聽 4.$sqlplus / as sysdba 以 DBA 身份進入 sqlplus 5.SQL>startup 啟動 db停止Oracle
1.#su - oracle 切換到 oracle 用戶且切換到它的環境 2.$lsnrctl stop 停止監聽 3.$sqlplus / as sysdba 以 DBA 身份進入 sqlplus 4.SQL>SHUTDOWN IMMEDIATE 關閉 db2. 手工創建數據庫及用戶
參考：
創建數據庫文件
MyDataBase:數據庫名稱
D:\Oracle\database\MyDataBase.dbf：數據庫文件目錄
創建數據庫臨時文件
MyDataBase_temp：數據庫臨時文件名稱
D:\Oracle\database\MyDataBase_temp.dbf：數據庫臨時文件目錄
創建用戶與上述兩個文件形成映射關系
username：用戶名
password：密碼
MyDataBase：映射的數據庫名稱
MyDataBase_temp：映射的數據庫臨時文件名稱
添加用戶權限
刪除數據庫
conn sys/dwh as sysdba;drop tablespace MyDataBase including contents and datafiles;drop tablespace MyDataBase_temp including contents and datafiles;刪除用戶
drop user username cascade;改變用戶表空間
alter user 用戶 quota unlimited on 表空間A; alter user 用戶 quota unlimited on 表空間B;或者放開所有表空間
grant unlimited tablespace to 用戶;或者索性給所有權限
grant resource,connect,dba to 用戶;查詢用戶
select * from all_users;授權
grant connect to XH_Bai_History_WS;– --是授予最終用戶的典型權利，最基本的權力，能夠連接到ORACLE數據庫中，并在對其他用戶的表有訪問權限時，做SELECT、UPDATE、INSERTT等操作
grant resource to XH_Bai_History_WS;–是授予開發人員的，能在自己的方案中創建表、序列、視圖等。
grant dba to XH_Bai_History_WS;–是授予系統管理員的，擁有該角色的用戶就能成為系統管理員了，它擁有所有的系統權限
revoke connect,resource from java_user;–取消權限revoke
grant create view to java_user–創建視圖權限，一般網上找都是說的這句，但是光有這句還是無法創建
grant create view to XH_Bai_History_WS;–授予查詢權限
grant select any table to B;–授予權限
grant select any dictionary to B;–以上3項地后就能正常創建視圖了。
3. 表空間管理
select distinct tablespace_name from dba_free_space; SELECT A.TABLESPACE_NAME,FILENUM,TOTAL "TOTAL (MB)",F.FREE "FREE (MB)",TO_CHAR(ROUND(FREE * 100 / TOTAL, 2), '990.00') "FREE%",TO_CHAR(ROUND((TOTAL - FREE) * 100 / TOTAL, 2), '990.00') "USED%",ROUND(MAXSIZES, 2) "MAX (MB)"FROM (SELECT TABLESPACE_NAME,COUNT(FILE_ID) FILENUM,SUM(BYTES / (1024 * 1024)) TOTAL,SUM(MAXBYTES) / 1024 / 1024 MAXSIZESFROM DBA_DATA_FILESGROUP BY TABLESPACE_NAME) A,(SELECT TABLESPACE_NAME, ROUND(SUM(BYTES / (1024 * 1024))) FREEFROM DBA_FREE_SPACEGROUP BY TABLESPACE_NAME) F WHERE A.TABLESPACE_NAME = F.TABLESPACE_NAME--查詢表所涉及對象select * from dba_source t where upper(t.TEXT) LIKE '%TMP_T_FACEBANK_LOAN_ITEM%'--清理無用的表DROP TABLE TMP_T_FACEBANK_LOAN_ITEM PURGE--處理 10G: --回收HWM alter table &table shrink space--重組該表中現有的行并回收HWM alter table &table shrink space cascade4. Oracle原用戶
Sys:超級管理員（校長）
System:管理員 修改密碼 解鎖 授權（老師）（orcl）
system@orcl as sysdba
Alter user scott identified by 新密碼
–the accout is locked
解鎖：Alter user scott account unlock
Scott：普通用戶 （學生）
5. 數據語言PL/SQL
1.數據定義語言（DDL）:create drop alter
創建，修改，刪除數據庫對象（表），操作的是表的結構，不是表的數據
2.數據操作語言（DML）:insert delete update ,操作是表中數據
注意：oracle事務需要手動提交
添加數據
Insert into 表名（列名，列名,…）values（值，值，…）
刪除數據
Delete from 表名 【where】
更新數據
Update 表名 set 列名=值，列名=值，… 【where】
下面就數據的DML,DDL舉例
1.創建表
Create table 表名( 列名 數據類型 【約束】, 列名 數據類型 【約束】, .... 列名 數據類型 【約束】 ) create table stu_b(stuId int primary key,stuName varchar2(50) not null,age int check(age>18) ) insert into stu_b(stuId,stuname,age) values(1,'小明',19); insert into stu_b values(2,'張三',20); select * from stu_b; create table text_b(txtId int primary key,txtName varchar2(20) not null,txtAge int check(txtAge>18) ) insert into text_b values(1,'歷史',20); select * from text_b;數據類型：
字符型：char varchar varchar2
Char:固定長度 char(10):abc 占10
Varchar2:可變（在存空字符時varchar存儲的是空字符，varchar2存的是null）
數字型：number(m,n):數字的精度 n:小數點后幾位 int float
時間：date
約束：非空（not null）檢查（check）默認（default）唯一（unique）
主鍵（primary key）外鍵（foreign key）
注意：oracle沒有主鍵自增，需要借助序列（sequence）例：6序列講解
2.刪除表
Drop table 表名 [purge]
Purge:表就會被徹底刪除
3.找回刪除的表
Flashback table 表名 to before drop;
4.重命名
Rename 原來表名 to 新表名
5.增加一列（修改表的結構）
Alter table 表名
Add 列名 數據類型 [約束]
6.刪除一列
Alter table 表名
drop column 列名
7.修改已有列的數據類型
Alter table 表名 modify 列名 修改類型
8.列重命名
Alter table 表名
Rename column 原來列名 to 新名字
9.查詢select
Emp:雇員表 dept：部門表
基本查詢
Select * from 表名
說明：*代表所有的列，直接寫列名
模糊查詢：like
查詢條件不精確，通過關鍵字進行查詢
%：0或n個字符
_：1個字符
一般聚合函數（sum,avg,max,min,count），統計
注意：1.聚合函數 2.分組列名
Order by ：排序（升序，降序 desc）
注意：避免笛卡爾積，加上主外鍵約束關系
(1). Where
(2). 內連接 inner join
select empno,ename,sal,dname,loc from emp inner join dept on emp.deptno=dept.deptno(3). 外鏈接 ：left join right join full join
主表，附表：主表數據全部顯示。，附表匹配主表進行顯示
主表有的列附表沒有，附表以空格的形式填充
主表沒有附表有的列，附表的列就不在顯示
5. 子查詢
查詢語句嵌套了查詢語句
注意:子查詢必須加上（）
相關子查詢：子查詢不可以脫離父查詢而單獨執行
先執行父查詢，子查詢利用父查詢的列執行查詢，父查詢在利用子查詢返回的結果作為查詢條件
非相關子查詢（獨立子查詢）：先執行子查詢（內查詢），將子查詢的結果父查詢（外查詢）的條件,子查詢都可以脫離父查詢而單獨執行
(1). 單行子查詢:子查詢返回的結果一個
運算符：>,<,>=,<=,=,<>
(2). 多行子查詢:子查詢返回的結果又多個值
運算符：in, all, any，not in
Rownum從1開始執行
6.序列（掌握）
在oracle中sequence就是序號，每次取的時候它會自動增加。sequence與表沒有關系
Create sequence 名稱(seq_表名)
注意：需要先執行一次nextval,才能執行currval
操作數據：
delete from stu where stuId=2; update stu set stuName='麗水'，phones='13478564' where stuId=3;序列字段說明
create sequence SEQ_LOG_ID
minvalue 1 --增長最小值
maxvalue 9999999999 --增長最大值,也可以設置NOMAXvalue – 不設置最大值
start with 101 --從101開始計數
increment by 1 --自增步長為1
cache 50 --設置緩存cache個序列，如果系統down掉了或者其它情況將會導致序列不連續，也可以設置為—NOCACHE防止跳號
cycle; --循環,當達到最大值時,不是從start with設置的值開始循環。而是從1開始循環
使用
insert into 表名(id,name)values(seqtest.Nextval,‘sequence 插入測試’);CurrVal：返回 sequence的當前值 NextVal：增加sequence的值，然后返回 增加后sequence值
注意：第一次NEXTVAL返回的是初始值；隨后的NEXTVAL會自動增加你定義的INCREMENT BY值，然后返回增加后的值。
CURRVAL 總是返回當前SEQUENCE的值，但是在第一次NEXTVAL初始化之后才能使用CURRVAL，否則會出錯。
一次NEXTVAL會增加一次 SEQUENCE的值，所以如果你在同一個語句里面使用多個NEXTVAL，其值就是不一樣的。- 如果指定CACHE值，ORACLE就可以預先在內存里面放置一些sequence，這樣存取的快些。cache里面的取完后，oracle自動再取一組 到cache。 使用cache或許會跳號， 比如數據庫突然不正常down掉（shutdown abort),cache中的sequence就會丟失. 所以可以在create sequence的時候用nocache防止這種情況。
7.創建視圖
8.函數/過程/觸發器
--字符碼—返回字符對應十進制 select ASCII('我愛你') from dual; select chr(52946) from dual;--參數為整數表示unicode碼，返回對應的字符--鏈接concat— --concat鏈接兩個字符串 select concat('0371-','4265324532') from dual; --||連接符 select '0371-'||'3644532' from dual; select concat('0319-','45836')||'轉2465239' 電話碼 from dual; --首字母大寫 --initcap返回字符串將其第一個字母大寫，其余變小寫 select initcap('hello') from dual; --全大寫 --upper返回字符串，并將其所有大寫 select upper('hello') from dual; --全小寫 --lower返回字符串，并將其所有小寫 select lower('HELLO') from dual; --查找出現位置 --instr(備查字符串，要查找字符串，查找起始位置，第幾次出現) select instr('411421199603026845','1',1,3) from dual; --補充 --lpad(原字符串，補充到達個數，補充的字符串) select lpad('gao',10,'*#') from dual; --lpad在列的左邊粘貼字符 select rpad('gao',7,'#@') from dual; --rpad在列的右邊粘貼字符 select ltrim(' ltrim') from dual; --刪除左邊出現的字符 如空格 select rtrim('ltrimr','rm') from dual; --刪除右邊的字符串 --trim(type ‘字符1’ from ‘字符2’)從字符2兩邊開始刪除字符1，如果前兩個參數和from省略從字符2刪除兩邊，type:刪除方式（leading:從左邊開始刪除；trailing:從右邊開始刪除；both:默認兩邊刪除） select trim(leading '=' from '=trim=') from dual; --刪除字符串leading左邊的字符串默認兩邊 select trim(trailing '=' from '=trim=') from dual;--- trailing右邊刪除 --截取substr --substr(字符串，起始位置，截取個數) select substr('123843564',3,5) from dual; --替換 select replace('全能就是好','全能','切糕') from dual; --日期函數 --sysdate系統當前日期 Select sysdate,to_char(sysdate,’dd-mm-yyyy day’) from dual; select sysdate+14 from dual; --add_months增加或減去月份 select add_months(sysdate,+1) from dual; -- month_between(date2,date1)給出date2和date1相差月份 select month_between(sysdate,hiredate),ename from emp; -- select last_day(sysdate) from dual; --下一個 --next_day(date,’day’)當前data下個星期的日期 select next_day(sysdate,'星期三') from dual; --舍取小數 select abs(-10) from dual; --取正 select ceil(3.006) from dual; --ceil向上取值 select floor(3.9999) from dual; --floor向下取值 select round(2.65) from dual; --四舍五入--trunc 截斷 select trunc(3.34523,2) from dual; --截斷結果3.34 select trunc(sysdate,'dd') from dual;--截斷到dd天 --mod取余 select mod(9,4) from dual; --求次方power select power(2,2) from dual; --開平方 sqrt select sqrt(4) from dual; --to_char類型轉換 select to_char(sysdate,'yyyy/mm/dd') from dual; --將數字轉換字符串--點可以用D代替 select to_char(123.4,'999.9')+100 from dual; --轉換成指定類型999.9 select to_char(1273494,'999,999,999')from dual; ---將字符串轉換時間 select to_date('2019-03-20','yyyy-MM-dd') from dual; --to_number(string,numeric) --查詢獎金不為空的員工數量 count不統計空值 select count(comm) from emp; -- nvl,nvl2代替空值 --nvl(expr1,expr2)如果expr1為null返回expr2,不為null返回expr1, 注意expr1,expr2兩者類型要一致 --nvl2(expr1,expr2,expr3) 如果expr1不為null返回expr2,為null返回expr3,如果expr2和expr3類型不同，expr3會轉換為expr2類型。 ----員工工資（基本+獎金(有獎金的返回獎金，沒有0)） select sal+nvl(comm,0) from emp; select sal+nvl2(comm,comm,0) from emp; --nullif 比較 expr1和 expr2 的值，若二者相等，則返回 NULL,否則返回 expr1的值其中 expr1不能為 NULL select nullif(3000,2000) from dual; --plsql代碼塊 --定義變量v_ename varchar2(20); --定義常量v_pi constant number(6,2):=3.14declare--定義部分 v_ename varchar2(20); begin—執行--通過編號得到用戶名select enam into v_enam from emp where empno=&eno;-- &eno輸入符號加載體 exception –異常處理部分when no_data_fount then dbms_output.put_line(‘用戶名不存在！’); end;--結束declare v_ip constant number(6,2):=3.14;v_r number(1):=2;v_area number(6,2);--1.數據類型 2.精度 beginv_area:=v_pi*v_r*v_r;dbms_output.put_line(‘面積為:’||v_area); end;--自動匹配變量類型 --into字句:在plsql塊中查詢出的數據必須借助變量輸出 into 給v_emp變量 declare v_emp emp.ename%type; beginselect ename into v_emp from emp where empno=&eno;dbms_output.put_line(v_emp); end; --rowtype匹配一行 declare--定義部分--rowtype匹配一行v_emp emp%rowtype; beginselect * into v_emp from emp where empno=&eon;dbms_output.put_line(v_emp.ename||v_emp.sal); end; ----table匹配整表 declare--定義table類型type v_emp_type is table of emp%rowtypeindex by binary_integer;--通過索引一行行存儲--定義變量v_emp v_emp_type;beginselect * into v_emp(0) from emp where empno=7369;select * into v_emp(1) from emp where empno=7499;dbms_output.put_line(v_emp(0).ename||v_emp(1).ename); end; ----record:自定義 declaretype v_emp_recordtype is record(--定義想要的數據ename emp.ename%type,total_sal number(6) --不在表里類型也可以自定義);v_emp v_emp_recordtype; beginselect ename,sal+nvl(comm,0) into v_emp from emp where empno=&eno;dbms_output.put_line('實發工資:'||v_emp.total_sal); end; ---流程控制語句 （分支)(循環） --分支if-eals(區間) switch-case --oracle --if then end if; case when then else end case; --if then end if; --plsql塊 declare ---輸入v_s number; beginv_s:=&s;if v_s<60 thendbms_output.put_line('不及格');elsedbms_output.put_line('及格');end if; end; ---if then elseif else end if declarev_s number; beginv_s:=&s;if v_s<60 thendbms_output.put_line('不及格');elsif v_s>=60 and v_s<70 thendbms_output.put_line('及格');elsif v_s>=70 and v_s<90 thendbms_output.put_line('良好');elsif v_s>=90 and v_s<=100 thendbms_output.put_line('優秀');elsedbms_output.put_line('輸入有誤！'); end if; end; -------------------------------- declarev_empno emp.empno%type;v_sal emp.sal%type;v_comm emp.comm%type;beginv_empno:=&eno;select sal,comm into v_sal,v_comm from emp where empno=v_empno;if v_comm is null thenupdate emp set comm=v_sal*0.1 where empno=v_empno;elsif v_comm<1000 thenupdate emp set comm=1000 where empno=v_empno;elseupdate emp set comm=v_comm+v_comm*0.1 where empno=v_empno;end if; end;select * from dept; --case when then else end case declarev_deptno dept.deptno%type; beginv_deptno:=&deo;case v_deptnowhen 10 then dbms_output.put_line('紐約');when 20 then dbms_output.put_line('達拉斯');when 30 then dbms_output.put_line('芝加哥');when 40 then dbms_output.put_line('波士頓');else dbms_output.put_line('....');end case; end; -------------------case2 declarev_sal emp.sal%type; beginselect sal into v_sal from emp where empno=&emo;casewhen v_sal<2000 then dbms_output.put_line('A級別工資');when v_sal>=2000 and v_sal<3000 then dbms_output.put_line('B級別工資');else dbms_output.put_line('C級別工資');end case; end; select * from dept; ----------------- /***循環1.loop 2.while 3.for */ --loop exit when end loop --loop循環體exit 【when】;--退出出口end loop; declare--定義table類型type v_dept_table is table of dept%rowtypeindex by binary_integer;---定義變量v_dept v_dept_table;---定義循環變量i number:=0; beginloopselect * into v_dept(i) from dept where deptno=(i+1)*10;dbms_output.put_line('編號:'||v_dept(i).deptno||'名稱'||v_dept(i).dname||'地址'||v_dept(i).loc);i:=i+1;exit when i=4;end loop; end; -----while I loop end loop --while 表達式 loop 循環體 end loop; declare type v_dept_table is table of dept%rowtypeindex by binary_integer;v_dept v_dept_table;i number:=0; beginwhile i<4 loopselect * into v_dept(i) from dept where deptno=(i+1)*10;dbms_output.put_line('編號:'||v_dept(i).deptno||'名稱'||v_dept(i).dname||'地址'||v_dept(i).loc);i:=i+1;end loop; end; -------for I in 0..number loop end loop --for 循環變量 i in 初始表達式..終止表達式 loop 循環體 end loop; declaretype v_dept_table is table of dept%rowtypeindex by binary_integer;v_dept v_dept_table; beginfor i in 0..3 loopselect * into v_dept(i) from dept where deptno=(i+1)*10;dbms_output.put_line('編號:'||v_dept(i).deptno||'名稱'||v_dept(i).dname||'地址'||v_dept(i).loc);end loop; end; ---異常處理--- begininsert into dept values(10,'aaa','bbb');exceptionwhen dup_val_on_index thendbms_output.put_line('aaaaaa');dbms_output.put_line('其他'); end; ---自定義異常 declaremy_exception exception; begindelete from emp where empno=&eno;if sql%notfound thenraise my_exception;--raise引發end if;exceptionwhen my_exception thendbms_output.put_line('編號不存在！'); end; ---事務--- create table bankjs(jsId int primary key,jsMonny varchar2(20) not null); create table bankny(nyId int primary key,nyMonny varchar2(20) not null); insert into bankjs values(1,'100'); insert into bankny values(1,'100'); select * from bankny; select * from bankjs; declarei number:=1; beginupdate bankjs set jsMonny=jsMonny+100 where jsId=1;i:=i/0;update bankny set nyMonny=nyMonny-100 where nyId=1;commit;--提交exceptionwhen zero_divide thenrollback;--回滾end; ---存儲過程----- --1,保存數據庫中，針對相同的操作，下次再次使用不用重新編譯 --2，預編譯：sql--先編譯—在執行 /*變量聲明塊：緊跟著的as (is )關鍵字，可以理解為pl/sql的declare關鍵字，用于聲明變量。 */ 在存儲過程（PROCEDURE）和函數(FUNCTION)中沒有區別，在視圖（VIEW）中只能用 ，在游標(CURSOR)中只能用IS不能用AS。 --不帶參 --編譯的過程：并沒有執行修改的操作，只是對要執行的操作進行一個語法解析等操作 create or replace procedure ifrst_procedure is---聲明 begin--要執行的參數update emp set comm=nvl(comm,0)+300; end; select * from emp; --調用執行過程：這個時候才真正的執行 beginifrst_procedure; end; --帶輸入參數 --默認不寫是in 入參 create or replace procedure text_in(v_empno in number) is v_sal emp.sal%type; beginselect sal into v_sal from emp where empno=v_empno;dbms_output.put_line(v_sal); end;begintext_in(7369); end; --帶輸入參數輸出參數 create or replace procedure text_inout(v_empno in number,v_sal out number) is beginselect sal into v_sal from emp where empno=v_empno; end;declare v_salout emp.sal%type; begintext_inout(7369,v_salout);dbms_output.put_line(v_salout); end; --入參數輸出參數 in out----- --in out ：在執行時先作為輸入參數使用，在作為輸出參數 create or replace procedure tet_inout(v_inout in out number) is beginselect sal into v_inout from emp where empno=v_inout; end; -----create or replace procedure test_inout(in_out in out number)isbeginselect sal into in_out from emp where empno=in_out;end;----執行declarea number;begina:=&a;--test_inout(a);tet_inout(a);dbms_output.put_line(a);end;---存儲過程添加--- create or replace procedure insert_data(v_dempno number,v_dname varchar2,v_loc varchar2) is begininsert into dept values(v_dempno,v_dname,v_loc); end;--執行 begininsert_data(50,'部門','地址'); end; select * from dept; ----傳參關聯付=> --傳參方式：位置，名稱，組合。 --形參和實參關聯。傳遞參數可以不按順序-名稱傳遞 begininsert_data(v_dname=>'部門1',v_loc=>'地址1',v_dempno => 60); end; --------函數--------- --函數-先編譯：返回特定數據，函數肯定會給我們一個數據 --不帶參function return create or replace function my_func return number –返回隨機數 is v_num number; --接受產生的隨機數并返回 beginv_num:=floor(dbms_random.value(1,10));--產生隨機數return v_num; --注意:至少有一條return語句 end;--執行 declare a number; begina:=my_func;dbms_output.put_line(a); end; --輸入輸出function in out return var create or replace function my_funout(v_empno in number,v_dname out varchar2) return varchar2 is v_loc dept.loc%type; beginselect dname,loc into v_loc,v_dname from dept,emp where dept.deptno=emp.deptno and emp.empno=v_empno;return v_loc; end; declarev_loc dept.loc%type;v_dname dept.dname%type; beginv_loc:=my_funout(7369,v_dname);dbms_output.put_line(v_loc);dbms_output.put_line(v_dname); end; --包規范-- package create or replace package my_package ispi constant number(10,7):=3.1415926;--定義常量 關鍵詞constant :=賦值符function getarea(ridus number) return number;--定義函數procedure print_area;--定義過程 end my_package; ---包體-- package body create or replace package body my_package is v_area number; --實現函數 function getarea(ridus number)return numberisbeginv_area:=pi*ridus*ridus;return v_area;end; --實現過程 procedure print_areaisbegindbms_output.put_line(v_area);end; end my_package; ------調用包.---- declare area number; beginarea:=my_package.getarea(4);dbms_output.put_line(area);my_package.print_area; end; --序列-- create sequence cc start with 5 increment by 2; select cc.currval from dual; --------過程--------- create or replace procedure v_dept_output(v_empno in out number) is beginselect sal into v_empno from emp where empno=v_empno; end; declareempno_sal number; beginempno_sal:=&編號;v_dept_output(empno_sal);dbms_output.put_line(empno_sal); end; ----函數 return out— /**過程和函數相同點：1，先編譯，再執行2，編譯直接保存在數據庫中3，帶參數，參數類型相同不同點：1,語法，函數:function 過程:procedure 3. 函數有返回值，return什么時候使用：多個值或者不返回值用過程procedure特定值使用函數function **/ ---通過函數輸出兩個數據：return 1個 利用out參數 --通過多表聯合通過編號 部門 地址 create or replace function my_fun1(v_empno in number,v_loc out varchar2) return varchar2 is v_dname dept.dname%type; beginselect dname,loc into v_dname,v_loc from emp,dept where dept.deptno=emp.deptno and empno=v_empno;return v_dname; end; --- declare v_dname dept.dname%type; v_loc dept.loc%type; beginv_dname:=my_fun1(7369,v_loc);dbms_output.put_line(v_loc);dbms_output.put_line(v_dname); end; -----包規范---- ----定義一些共有的組件，沒有實現體 create or replace package my_packages1 isip constant number:=3.1415926;function getarea(ridus in number) return number;--函數只用定義部分沒有實現體procedure print_area; --過程只有定義部分 end my_packages1; ----包體----- --包體具體執行部分，是實現包規范 create or replace package body my_packages1 is --is里定義全局變量 v_area number;---實現包規范--實現函數function getarea(ridus in number)return numberis beginv_area:=ip*ridus*ridus;return v_area;end;---實現過程procedure print_areaisbegindbms_output.put_line(v_area);end; end my_packages1; -----調用執行：包名.---- declarevar_arae number; beginvar_arae:=my_packages1.getarea(5);dbms_output.put_line(var_arae);my_packages1.print_area; end; select * from emp; --通過查詢字典USER_SOURCE,可顯示當前子程序及源碼 Select text from user_source where name=’pack_util’; --刪除子程序 Drop procedure proc_name; --創建索引—create index table on column create index emp_index on emp(deptno);select * from emp where emp.deptno='20' ----------------創建包規范—游標處理結果集相當java類接口 create or replace package testProduct istype cursorType is ref cursor;--定義一個游標變量oracle分配內存處理結果集 end testProduct; --過程 create or replace procedure testProcedure(userId in number,userList out testProduct.cursorType) is beginif userId=null or userId='' thenopen userList for select * from userinfo;else open userList for select * from userinfo u where u.userid=userId;end if; end;--申明包結構 create or replace package atii.mypackage as type mycursor is ref cursor; procedure queryCount(startDate in date,endDate in date,countList out mycursor); end mypackage;--創建包體 create or replace package body atii.mypackage as procedure queryCount(startDate in date,endDate in date,countList out mycursor) as begin open countList for select t.createDate,count(t.createDate) from (select case when createtime>=(trunc(createtime)+18/24) then trunc(createtime)+1 else trunc(createtime) end createDate from t_count ) t where t.createDate>=to_date('2017-08-19','yyyy-MM-dd') and t.createDate<=to_date('2017-08-24','yyyy-MM-dd') group by t.createDate order by t.createDate; end queryCount; end mypackage;----create or replace procedure PRC_STAT_LOGIN ISv_username users.username%TYPE;v_password USERS.PASSWORD%TYPE;v_userlevel USERS.USER_LEVEL%TYPE;begin --聲明游標 查詢出每個每個用戶的信息declare cursor cursor_login is select * from users ;--cursor cursor_name is select username from users; beginif not cursor_login%isopen thenopen cursor_login;end if;loopFETCH cursor_login INTO v_username,v_password,v_userlevel; EXIT WHEN cursor_login%NOTFOUND; --正常登錄，返回”成功登陸”DBMS_OUTPUT.PUT_LINE('Login successfully!');IF v_username is NULL THEN--如用戶名不存在，返回”用戶名不存在”DBMS_OUTPUT.put_line('The user is not existed!');ELSEIF v_userlevel = 'U' THEN--如用戶名、密碼都正確，但是級別不夠，管理員是A，一般用戶是U，那么返回”級別不夠”DBMS_OUTPUT.put_line('Low level!') ; END IF;end if;end loop;exceptionwhen NO_DATA_FOUND THENDBMS_OUTPUT.put_line('No data found!');when LOGIN_DENIED THEN --如用戶名存在，密碼錯誤，返回”密碼錯誤”DBMS_OUTPUT.PUT_LINE('PASSWORD ERROR');close cursor_login; end; end PRC_STAT_LOGIN;-------游標使用--------- 游標分為兩種：1.隱含游標用于處理select into 和DML語句2.顯示游標用于select語句返回多行數據BeginDelete from emp where empno=7369;--DMLIF sql%notfound then –被隱含創建了dbms_output.put_line(‘sql起作用了’);end if;end;使用顯示游標語法： 1， 定義游標CURSOR cursor_name IS select_statement; 2， 打開游標OPEN cursor_name; 3， 提取數據 FETCH cursor_name INTO variable1,variable2; 4， 關閉游標 CLOSE cursor_name; --游標：oracle分配的一塊內存，通過指針針對每一行單獨處理 --游標一行一行提取數據 declaretype v_cursor is ref cursor;--定義游標test_cursor v_cursor;test_table test_user_info%rowtype; begin--打開游標 --執行查詢，將結果集放在內存中，等待提取，每次提取一行。open test_cursor for select * from test_user_info;loop –循環提取fetch test_cursor into test_table;exit when test_cursor%notfound;dbms_output.put_line(test_table.user_id||'-'||test_table.user_name||'-'||test_table.sex); end loop;close test_cursor;--關閉游標，釋放內存 end; --------------table-------------一次性提取 declarecursor emp_cursor is select * from emp;type test_emp_table is table of emp%rowtypeindex by binary_integer;v_emp test_emp_table; beginopen emp_cursor;fetch emp_cursor bulk collect into v_emp; ----一次性全部提取到表結構中close emp_cursor; --關閉游標 for i in v_emp.first..v_emp.last loop dbms_output.put_line(v_table(i).user_id||'-'||v_table(i).user_name||'-'||v_table(i).sex); end loop; end; ------------------定義參數游標--------------------- declare –部門20員工cursor emp_cursor(var_deptno number) isselect * from emp where deptno=v_deptno; v_emp emp%rowtype; begin open emp_cursor(20);loopfetch emp_cursor into v_emp; exit when emp_cursor%notfound;dbms_output.put_line(‘編號’||v_emp.empno||’名稱’v_emp.ename); end loop; close emp_cursor; end; -------------部門信息--------------- --部門編號對應員工信息 declare --部門游標 cursor dept_cursor is Select * from dept; v_dept dept%rowtype; --emp游標cursor emp_cursor(v_deptno number) isselect * from emp where deptno=v_deptno; begin open dept_cursor;loop fetch dept_cursor into v_dept; exit when dept_cursor%notfound; dbms_output.put_line(‘部門編號’||v_dept.deptno); open emp_cursor(v_dept.deptno)loopfetch emp_cursor into v_emp; exit when emp_cursor%notfound;dbms_output.put_line(‘ 員工編號’||v_emp.empno||’名稱’v_emp.ename); end loop; close emp_cursor; end loop; close dept_cursor; end; ----簡化操作----- --游標for循環簡化游標處理，當使用for循環時，oracle會隱含的打開游標，提取數據，關閉游標 declarecursor emp_cursor isselect * from emp; beginfor emp_row in emp_cursor loopdbms_output.put_line('編號'||emp_row.empno||'名字'||emp_row.ename);end loop; end; ---------當時使用游標for循環，可以直接使用子查詢----- beginfor emp_row in (select * from emp) loop –使用子查詢當做內容dbms_output.put_line('編號'||emp_row.empno||'名字'||emp_row.ename);end loop; end;----游標變量----- Declare--游標類型type cursor_type is ref cursor;--定義變量cursor_emp cursor_type;v_emp emp%rowtypw; begin--打開游標Open cursor_emp for select * from emp;loopfetch cursor_emp into v_emp; exit when emp_cursor%notfound;dbms_output.put_line(‘ 員工編號’||v_emp.empno||’名稱’v_emp.ename); end loop; end;--系統動態游標SYS_REFCURSOR的使用--- -- 創建表 create table test_user_info(user_id integer primary key,--primary keyuser_name varchar2(20),sex varchar2(2) );-- 插入測試數據 insert into test_user_info(user_id,user_name,sex) values(1,'小明','M'); insert into test_user_info(user_id,user_name,sex) values(2,'小美','F'); insert into test_user_info(user_id,user_name,sex) values(3,'小美','w'); insert into test_user_info(user_id,user_name,sex) values(4,'小美','q'); commit; -- 查數 select * from test_user_info; ---- 創建過程取數 create or replace procedure test_SysCursor(p_cursor out sys_refcursor) is beginopen p_cursor for select * from test_user_info; end; ---sys_refcursor入參返結果集 create or replace procedure inout_SysCursor(tname Nvarchar2,out_resultSet out sys_refcursor) is beginopen out_resultSet for select * from test_user_info where user_name=tname; end; --測試 declarev_cursor sys_refcursor;type type_table is table of test_user_info%rowtypeindex by binary_integer;v_table type_table;--su varchar2(20); begininout_SysCursor('小美',v_cursor);fetch v_cursor bulk collect into v_table;for i in v_table.first..v_table.last loopdbms_output.put_line(v_table(i).user_id||'-'||v_table(i).user_name||'-'||v_table(i).sex); end loop; end;----測試1 declarev_cursor sys_refcursor;u test_user_info%rowtype; begintest_SysCursor(v_cursor);--loop fetch v_cursor into u.user_id, u.user_name,u.sex; loop fetch v_cursor into u; exit when v_cursor%notfound; dbms_output.put_line(u.user_id||'-'||u.user_name||'-'||u.sex); end loop; end; ---測試2 declarev_cursor sys_refcursor;type test_table is table of test_user_info%rowtypeindex by binary_integer;v_table test_table; begintest_SysCursor(v_cursor);fetch v_cursor bulk collect into v_table;--一次性全部提取for i in v_table.first..v_table.last loopdbms_output.put_line(v_table(i).user_id||'-'||v_table(i).user_name||'-'||v_table(i).sex); end loop; end;---普通動態游標的創建declaretype rc is ref cursor; -- 定義類型cursor c isselect * from dual; -- 普通靜態游標r_cursor rc; -- 普通動態游標sr_cursor sys_refcursor; -- 系統動態游標 beginif (to_char(sysdate, 'mi') >= 40) then-- ref cursor with dynamic sql open r_cursor for 'select * from dim_employee';open sr_cursor for 'select * from dim_org_dept';elsif (to_char(sysdate, 'mi') <= 20) then-- ref cursor with static sql open r_cursor for select * from dim_org_dept;open sr_cursor for select * from dim_employee;else-- ref cursor with static sql open r_cursor for select * from dual;open sr_cursor for select * from dual;end if;-- the "normal" static cursor open c;end;----觸發器--- 1.DML觸發器—在對數據庫DML操作觸發，并且可以對每一行或者語句操作上進行觸發 2.替代觸發器—專門為試圖操作的一種觸發器 3,系統觸發器—對數據庫系統事件進行觸發，如啟動關閉 --觸發器組成1.觸發事件—DML或者DDL語句2.觸發時間，是在觸發之前(before)還是之后（aftre）3.觸發操作—使用PL/sql4.觸發對象—表，視圖，模式，數據庫5.觸發頻率，定義執行次數 --觸發器調用過程，只能包含DMLDML觸發器----事件觸發器 -----星期天時不能對emp進行修改操作（insert,delete,update） Create or replace trigger tri_no_sun Before insert or update or delete --在修改，添加，刪除之前觸發 On emp –在哪個表觸發 Begin –被觸發執行的操作If to_char(sysdate,’day’) in (‘星期日’) then--raise_application_error(-20000,‘今天不能修改emp表’); case when inserting then –條件謂詞使用raise_application_error(-20000,‘今天不能添加emp表’);when updateing thenraise_application_error(-20001,‘今天不能修改emp表’); when deleteing thenraise_application_error(-20002,‘今天不能刪除emp表’); end caes;End if; End; 測試 Delete from emp where empno=7369; -----失效觸發器 Alter trigger tri_on_sun disable; --啟動 Aletr tigger tri_on_sun enable; --刪除 Drop trigger tri_on_sun; -----行級觸發器-fo reach row----- --當降低部門30工資觸發 Create trigger no_sql Before update of sal,comm or delete On emp for each row –行級觸發器when(old.deptno=30) begincaes when updateing(‘sal’) then if :new.sal<:old.sal thenraise_application_error(-20002,‘這個部門工資不能降低’); end if;when updateing(‘comm) then if :new.comm<:old.comm thenraise_application_error(-20000,‘這個部門獎金不能降低’); end if;when deleteing thenraise_application_error(-20001,‘這個部門不能刪除’); end; -----after觸發器執行DML之后觸發---- ---級聯更新after觸發--- Create trigger case_update After update of deptno –修改emp表之后觸發 On dept For each row BeginUpdate emp set deptno=:new.deptno where deptno=:lod.deptno; End;Update dept set deptno=50 where deptno=20;------數據的備份----- --刪除一個表時，將被刪除的數據通過過程添加到一個回收表，回收表在刪除時被觸發調用 --1.創建一個回收表，和被刪除的表一樣 Create table deldept(Deptno number(7),Dname varchar2(30),Loc varchar2(40) ); --2,創建過程 Create or replace procedore add_deldept(v_deptno number,v_dname varchar2,v_loc varchar2) Is BeginInsert into deldept values(v_deptno,v_dname,v_loc); End; --觸發器調用過程 create or replace trigger beifen_dept after delete –在刪除dept行之后觸發 on dept for each row –行級觸發 beginadd_deldept(:old,deptno,:old,bname,:old.loc);--存儲舊參數 endl delete from dept where deptno=40;替代觸發器 --不能在復雜視圖上執行DML操作，必須基于視圖創建instead of 觸發器注意：只能適用于視圖，不能指定before和after,必須指定for each row,只能視圖上創建 Create or replace view emp_view As Selet deptno,count(*) total_employeer,sun(sql) total_sal from emp group by deptnol--刪除不成功 Delete from emp_view where deptno=20;Create or replace trigger view_tri Instead of delete On emp_view For each row Begindbms_output.put_line(‘替代觸發器創建成功’); End;五、數據裝載、備份與恢復
1. Oracle的備份與恢復三種標準的模式
大致分為兩 大類，備份恢復(物理上的)以及導入導出(邏輯上的)，而備份恢復又可以根據數據庫的工作模式分為非歸檔模式(Nonarchivelog-style) 和歸檔模式(Archivelog-style),通常，我們把非歸檔模式稱為冷備份，而相應的把歸檔模式稱為熱備份，他們的關系如下所示
三種方式各有優點，我們做個比較(這個是用Fireworks畫的，有點糙)：
熱備份和冷備份優缺點
熱備份的優點是：
1．可在表空間或數據文件級備份，備份時間短。
2．備份時數據庫仍可使用。
3．可達到秒級恢復（恢復到某一時間點上）。
4．可對幾乎所有數據庫實體作恢復。
5．恢復是快速的，在大多數情況下在數據庫仍工作時恢復。
熱備份的不足是：
1．不能出錯，否則后果嚴重。
2．若熱備份不成功，所得結果不可用于時間點的恢復。
3．因難維護，所以要特別仔細小心，不允許“以失敗而告終”。
冷備份的優點是：
1．是非常快速的備份方法（只需拷貝文件）
2．容易歸檔（簡單拷貝即可）
3．容易恢復到某個時間點上（只需將文件再拷貝回去）
4．能與歸檔方法相結合，作數據庫“最新狀態”的恢復。
5．低度維護，高度安全。
冷備份不足是：
1．單獨使用時，只能提供到“某一時間點上”的恢復。
2．在實施備份的全過程中，數據庫必須要作備份而不能作其它工作。也就是說，數據庫必須是關閉狀態。
3．若磁盤空間有限，只能拷貝到磁帶等其它外部存儲設備上，速度會很慢。
4．不能按表或按用戶恢復。
2. 邏輯備份方式的方法
利用Export可將數據從數據庫中提取出來，利用Import則可將提取出來的數據送回到Oracle數據庫中 去。理論基礎：Oracle提供的Export和Import具有三種不同的操作方式(就是備份的數據輸出(入)類型)：
- 1，表方式(T) 可以將指定的表導出備份；
- 2，全庫方式(Full) 將數據庫中的所有對象導出；
- 3，用戶方式(U) 可以將指定的用戶相應的所有數據對象導出；
在導入導出備份方式中，提供了很強大的一種方法，就是增量導出/導入，但是它必須作為System來完成增量的導入導出，而且只能是對整個數據庫進行實施。增量導出又可以分為三種類別：
- 1，完全增量導出(Complete Export) 這種方式將把整個數據庫文件導出備份；
（為了方便檢索和事后的查詢，通常我們將備份文件以日期或者其他有明確含義的字符命名）
- 2，增量型增量導出(Incremental Export) 這種方式將只會備份上一次備份后改變的結果；
- 3，累積型增量導出(Cumulate Export) 這種方式的話，是導出自上次完全增量導出后數據庫變化的信息。
通常情況下，DBA們所要做的，就是按照企業指定或者是自己習慣的標準(如果是自己指定的標準，建議寫好計劃說明)，一般，我們采用普遍認可的下面的方式進行每天的增量備份：
- Mon： 完全備份（A）
- Tue： 增量導出（B）
- Wed：增量導出（C）
- Thu： 增量導出（D）
- Fri： 累計導出（E）
- Sat： 增量導出（F）
- Sun： 增量導出（G）
這樣，我們可以保證每周數據的完整性，以及恢復時的快捷和最大限度的數據損失。恢復的時候，假設事故發生在周末，DBA可按這樣的步驟來恢復數據庫：
- 第一步：用命令CREATE DATABASE重新生成數據庫結構；
- 第二步：創建一個足夠大的附加回滾。
- 第三步：完全增量導入A：
- 第四步：累計增量導入E：
- 第五步：最近增量導入F：
通常情況下，DBA所要做的導入導出備份就算完成，只要科學的按照規律作出備份，就可以將數據的損失降低到最小，提供更可靠的服務。另外，DBA最好對每次的備份做一個比較詳細的說明文檔，使得數據庫的恢復更加可靠。
3. 物理備份之冷備份(條件－NonArchiveLog)：
當數據庫可以暫時處于關閉狀態時，我們需要將它在這一穩定時刻的數據相關文件轉移到安全的區域，當數據庫遭到破壞，再從安全區域將備份的數據庫相關文件拷 貝回原來的位置，這樣，就完成了一次快捷安全等數據轉移。由于是在數據庫不提供服務的關閉狀態，所以稱為冷備份。冷備份具有很多優良特性，比如上面圖中我 們提到的，快速，方便，以及高效。一次完整的冷備份步驟應該是：
-
1，首先關閉數據庫（shutdown normal）
-
2，拷貝相關文件到安全區域（利用操作系統命令拷貝數據庫的所有的數據文件、日志文件、控制文件、參數文件、口令文件等（包括路徑））
-
3，重新啟動數據庫（startup）
以上的步驟我們可以用一個腳本來完成操作：
這樣，我們就完成了一次冷備份，請確定你對這些相應的目錄（包括寫入的目標文件夾）有相應的權限。
恢復的時候，相對比較簡單了，我們停掉數據庫，將文件拷貝回相應位置，重啟數據庫就可以了，當然也可以用腳本來完成。
4. 物理備份之熱備份：(條件－ArchiveLog)
當我們需要做一個精度比較高的備份，而且我們的數據庫不可能停掉（少許訪問量）時，這個情況下，我們就需要歸檔方式下的備份，就是下面討論的熱備份。熱備 份可以非常精確的備份表空間級和用戶級的數據，由于它是根據歸檔日志的時間軸來備份恢復的，理論上可以恢復到前一個操作，甚至就是前一秒的操作。具體步驟 如下：
1，通過視圖v$database,查看數據庫是否在Archive模式下:
SQL> select log_mode from v$database;如果不是Archive模式
則設定數據庫運行于歸檔模式下：
SQL>shutdown immediate SQL>startup mount SQL> alter database archivelog; SQL> alter database open;如果Automaticarchival顯示為“Enabled”，則數據庫歸檔方式為自動歸檔。否則需要手工歸檔，或者將歸檔方式修改為自動歸檔，如：
正常shutdown數據庫，在參數文件中init.ora中加入如下參數
修改init.ora：
LOG_ARCHIVE_START=TRUE LOG_ARCHIVE_DEST1=ORACLE_HOME/admin/o816/arch(歸檔日值存放位置可以自己定義) SQL>startup然后，重新啟動數據庫，此時Oracle數據庫將以自動歸檔的方式工作在Archive模式下。其中參數LOG_ARCHIVE_DEST1是指定的歸 檔日志文件的路徑，建議與Oracle數據庫文件存在不同的硬盤，一方面減少磁盤I/O競爭，另外一方面也可以避免數據庫文件所在硬盤毀壞之后的文件丟 失。歸檔路徑也可以直接指定為磁帶等其它物理存儲設備，但可能要考慮讀寫速度、可寫條件和性能等因素。
注意： 當數據庫處在ARCHIVE模式下時，一定要保證指定的歸檔路徑可寫，否則數據庫就會掛起，直到能夠歸檔所有歸檔信息后才可以使用。另外，為創建一個有效 的備份,當數據庫在創建時,必須履行一個全數據庫的冷備份,就是說數據庫需要運行在歸檔方式,然后正常關閉數據庫,備份所有的數據庫組成文件。這一備份是 整個備份的基礎,因為該備份提供了一個所有數據庫文件的拷貝。（體現了冷備份與熱備份的合作關系，以及強大的能力）
2. 備份表空間文件：
- a,首先，修改表空間文件為備份模式
- b,然后，拷貝表空間文件到安全區域
- c,最后，將表空間的備份模式關閉
3，對歸檔日志文件的備份：
停止歸檔進程－－>備份歸檔日志文件－－>啟動歸檔進程
如果日志文檔比較多，我們將它們寫入一個文件成為一個恢復的參考：
$ files `ls <歸檔文件路徑>/arch*.dbf`;export files4，備份控制文件:
SQL> alter database backup controlfile to 'controlfile_back_name（一般用2004-11-20的方式）' reuse;當然，我們也可以將上面的東東寫為一個腳本，在需要的時候執行就可以了:
腳本范例：
熱備份的恢復，對于歸檔方式數據庫的恢復要求不但有有效的日志備份還要求有一個在歸檔方式下作的有效的全庫備份。歸檔備份在理論上可以無數據丟失，但 是對于硬件以及操作人員的要求都比較高。在我們使用歸檔方式備份的時候，全庫物理備份也是非常重要的。歸檔方式下數據庫的恢復要求從全備份到失敗點所有的 日志都要完好無缺。
恢復步驟：LOG_ARCHIVE_DEST_1
- shutdown數據庫。
- 將全備份的數據文件放到原來系統的目錄中。
- 將全備份到失敗點的所有歸檔日志放到參數LOG_ARCHIVE_DEST_1所指定的位置。
- 利用sqlplus登陸到空實例。（connect / as sysdba） 然后
這樣，我們的熱恢復就算完工了。
六、管理運行中的數據庫
linux系統的系統日志一般位于/var/log目錄下。linux的系統日志由一個叫syslog的進程管理的，如下日志都是由syslog服務驅動的。
- /var/log/ messages:記錄linux系統常見的系統和服務錯誤。
- /var/log/lastlog：記錄最后一次用戶登錄的時間，登錄IP等信息。
- /var/log/secure：linux系統安全日志，記錄用戶和工作組變化情況，用戶登錄認證信息。
- /var/log/btmp：記錄linux登錄失敗的用戶，時間以及遠程ip地址。
- /var/log/cron：記錄crond計劃任務服務的執行情況。
上面的日志都可以用linux系統的vi和cat命令查看。
$vi 日志名、 $cat 日志名
- oracle數據庫中最重要的日志是警告日志，警告日志alert_sid.log一般位于$ORACLE_HOME/admin/ORACLE_SID/bdump目錄下面。如果不知道oracle11g數據庫警告日志的具體位置，可以通過以下代碼查找：
打開alert_sid.log日志，主要產看ora-或者是errers關鍵詞的，這一般是數據庫發生錯誤的信息。
- 檢查linux磁盤空間情況：$df -lh
- 檢查數據庫表空間使用率：
oracle的dba權限登錄pl/sql development，在sql窗口輸入如下內容：
SQL> select a.tablespace_name,round((a.maxbytes / 1024 / 1024), 2) "sum MB",round((a.bytes / 1024 / 1024), 2) "datafile MB",round(((a.bytes - b.bytes) / 1024 / 1024), 2) "used MB",round(((a.maxbytes - a.bytes + b.bytes) / 1024 / 1024), 2) "free MB",round(((a.bytes - b.bytes) / a.maxbytes) * 100, 2) "percent_used"from (select tablespace_name, sum(bytes) bytes, sum(maxbytes) maxbytesfrom dba_data_fileswhere maxbytes != 0group by tablespace_name) a,(select tablespace_name, sum(bytes) bytes, max(bytes) largestfrom dba_free_spacegroup by tablespace_name) bwhere a.tablespace_name = b.tablespace_nameorder by ((a.bytes - b.bytes) / a.maxbytes) desc執行即可產看結果.
- 檢查數據庫文件是否為自動擴展：
在pl/sql developer的sql窗口輸入下面語句：
SQL>select file_id,file_name,tablespace_name,autoextensible from dba_data_files;- 檢查RMAN備份是否出錯：
回車可以看到rman備份情況及對應的日志，可以打開日志進行查看。如果日志中包含RMAN-的則表示可能不成功。
- 實時監控linux操作系統：
回車即可監控linux系統的cpu，內存，交換空間，IO的使用情況。
如果想停止實時監控，可以同時按ctrl+C組合鍵來終止。
- 查看oracle數據庫進程：
回車即可查看oracle11g所有進程情況。
- 查看oracle數據庫的監聽程序：
- 查看等待事件
- 在線回滾段名字描述
- 監控表空間的 I/O 比例
- 數據庫使用的數據文件信息顯示的信息是來自控制文件的
- 監控 SGA 的命中率
- 監控 SGA 中字典緩沖區的命中率
- 監控 SGA 中共享緩存區的命中率，應該小于1%
- 監控 SGA 中重做日志緩存區的命中率，應該小于1%
- 數據庫運行了一段時間后，由于不斷的在表空間上創建和刪除對象，會在表空間上產生大量的碎片，DBA應該及時了解表空間的碎片和可用空間情況，以決定是否要對碎片進行整理或為表空間增加數據文件。以下為引用的內容：
上面的SQL列出了數據庫中每個表空間的空閑塊情況,如下所示:
以下為引用的內容：
TABLESPACE_NAME CHUNKS MAX_CHUNK
INDX 1 57.9921875
RBS 3 490.992188
RMAN_TS 1 16.515625
SYSTEM 1 207.296875
TEMP 20 70.8046875
TOOLS 1 11.8359375
USERS 67 71.3671875
其中，CHUNKS列表示表空間中有多少可用的空閑塊(每個空閑塊是由一些連續的Oracle數據塊組成)，如果這樣的空閑塊過多，比如平均到每個數據文件上超過了100個，那么該表空間的碎片狀況就比較嚴重了，可以嘗試用以下的SQL命令進行表空間相鄰碎片的接合:
alter tablespace 表空間名coalesce;
然后再執行查看表空間碎片的SQL語句，看表空間的碎片有沒有減少。如果沒有效果，并且表空間的碎片已經嚴重影響到了數據庫的運行，則考慮對該表空間進行重建。
MAX_CHUNK列的結果是表空間上最大的可用塊大小，如果該表空間上的對象所需分配的空間(NEXT值)大于可用塊的大小的話，就會提示ORA-1652、ORA-1653、ORA-1654的錯誤信息，DBA應該及時對表空間的空間進行擴充，以避免這些錯誤發生。
DBA要定時對數據庫的連接情況進行檢查，看與數據庫建立的會話數目是不是正常，如果建立了過多的連接，會消耗數據庫的資源。同時，對一些“掛死”的連接，可能會需要DBA手工進行清理。
- 以下的SQL語句列出當前數據庫建立的會話情況:以下為引用的內容：
輸出結果為:以下為引用的內容：
SID SERIAL# USERNAME PROGRAM MACHINE STATUS
1 1 ORACLE.EXE WORK3 ACTIVE
2 1 ORACLE.EXE WORK3 ACTIVE
3 1 ORACLE.EXE WORK3 ACTIVE
4 1 ORACLE.EXE WORK3 ACTIVE
5 3 ORACLE.EXE WORK3 ACTIVE
6 1 ORACLE.EXE WORK3 ACTIVE
7 1 ORACLE.EXE WORK3 ACTIVE
8 27 SYS SQLPLUS.EXE WORKGROUP\WORK3 ACTIVE
11 5 DBSNMP dbsnmp.exe WORKGROUP\WORK3 INACTIVE
注：
SID會話(session)的ID號；
SERIAL#會話的序列號，和SID一起用來唯一標識一個會話；
USERNAME建立該會話的用戶名；
PROGRAM這個會話是用什么工具連接到數據庫的；
STATUS當前這個會話的狀態，ACTIVE表示會話正在執行某些任務，INACTIVE表示當前會話沒有執行任何操作。
如果DBA要手工斷開某個會話，則執行:
注意，上例中SID為1到7(USERNAME列為空)的會話，是Oracle的后臺進程，不要對這些會話進行任何操作。
- 查看undo回滾率
七、性能調整
Oralce 性能調優（基礎）可以從以下幾個方面入手
1. 外部的性能問題
我們應該記住 Oracle 并不是單獨運行的。因此我們將查看一下通過調整 Oracle 服務器以得到高的性能
首先從調整 Oracle 外部的環境開始。如果內存和 CPU 的資源不足的話，任何的 Oracle 調整都是沒有幫助的。Oracle 并不是單獨運行的。 Oracle 數據庫的性能和外部的環境有很大的關系。
這些外部的條件包括有：
-
CPU ：CPU 資源的不足令查詢變慢。當查詢超過了 Oracle 服務器的 CPU 性能時，你的數據庫性能就受到 CPU 的限制。
-
內存 ：可用于 Oralce 的內存數量也會影響 SQL 的性能，特別是在數據緩沖和內存排序方面。
-
網絡 ：大量的 Net 通信令 SQL 的性能變慢。 許多新手都錯誤的認為應該首先調整 Oracle
數據庫，而不是先確認外部資源是否足夠。實際上，如果外部環境出現瓶頸，再多的 Oracle 調整都是沒有幫助的。
在檢查 Oracle 的外部環境時，有兩個方面是需要注意的：
-
1 、當運行隊列的數目超過服務器的 CPU 數量時，服務器的性能就會受到 CPU 的限制。補救的方法是為服務器增加額外的 CPU
或者關閉需要很多處理資源的組件，例如 Oracle Parallel Query 。 -
2 、內存分頁。當內存分頁時，內存容量已經不足，而內存頁是與磁盤上的交換區進行交互的。補救的方法是增加更多的內存，減少 Oracle
SGA 的大小，或者關閉 Oracle 的多線程服務器。
可以使用各種標準的服務器工具來得到服務器的統計數據，例如 vmstat,glance,top 和 sar 。 DBA 的目標是確保數據庫服務器擁有足夠的 CPU 和內存資源來處理 Oracle 的請求。
2. row-resequencing （行的重新排序）
當 Oracle 由磁盤上的一個數據文件得到一個數據塊時，讀的進程就必須等待物理 I/O 操作完成。磁盤操作要比數據緩沖慢 10,000 倍。因此，如果可以令 I/O 最小化，或者減少由于磁盤上的文件競爭而帶來的瓶頸，就可以大大地改善 Oracle 數據庫的性能。
如果系統響應很慢，通過減少磁盤 I/O 就可以有一個很快的改善。如果在一個事務中通過按一定的范圍搜索 primary-key 索引來訪問表，那么重新以 CTAS 的方法組織表將是你減少 I/O 的首要策略。通過在物理上將行排序為和 primary-key 索引一樣的順序，就可以加快獲得數據的速度。
就象磁盤的負載平衡一樣，行的重新排序也是很簡單的，而且也很快。通過與其它的 DBA 管理技巧一起使用，就可以在高 I/O 的系統中大大地減少響應的時間。
在高容量的在線事務處理環境中（ online transaction processing ， OLTP ），數據是由一個 primary 索引得到的，重新排序表格的行就可以令連續塊的順序和它們的 primary 索引一樣，這樣就可以在索引驅動的表格查詢中，減少物理 I/O 并且改善響應時間。這個技巧僅在應用選擇多行的時候有用，或者在使用索引范圍搜索和應用發出多個查詢來得到連續的 key 時有效。對于隨機的唯一 primary-key （主鍵）的訪問將不會由行重新排序中得到好處。
考慮以下的一個 SQL 的查詢，它使用一個索引來得到 100 行：
select salary from employee where last_name like 'B%';這個查詢將會使用 last_name_index ，搜索其中的每一行來得到目標行。這個查詢將會至少使用 100 次物理磁盤的讀取，因為 employee 的行存放在不同的數據塊中。不過，如果表中的行已經重新排序為和 last_name_index 的一樣，同樣的查詢又會怎樣處理呢？我們可以看到這個查詢只需要三次的磁盤 I/O 就讀完全部 100 個員工的資料（一次用作索引的讀取，兩次用作數據塊的讀取），減少了 97 次的塊讀取。
重新排序帶來的性能改善的程度在于在你開始的時候行的亂序性如何，以及你需要由序列中訪問多少行。至于一個表中的行與索引的排序鍵的匹配程度，可以查看數據字典中的 dba_indexes 和 dba_tables 視圖得到。
在 dba_indexes 的視圖中，查看 clustering_factor 列。如果 clustering_factor 的值和表中的塊數目大致一樣，那么你的表和索引的順序是一樣的。不過，如果 clustering_factor 的值接近表中的行數目，那就表明表格中的行和索引的順序是不一樣的。
行重新排序的作用是不可以小看的。在需要進行大范圍的索引搜索的大表中，行重新排序可以令查詢的性能提高三倍。一旦你已經決定重新排序表中的行，你可以使用以下的工具之一來重新組織表格。
使用 Oracle 的 Create Table As Select (CTAS) 語法來拷貝表格
Oracle9i 自帶的表格重新組織工具
3. SQL 調優
- 3.1 消除不必要的大表全表搜索：
不必要的全表搜索導致大量不必要的 I/O ，從而拖慢整個數據庫的性能。調優專家首先會根據查詢返回的行數目來評價 SQL 。在一個有序的表中，如果查詢返回少于 40% 的行，或者在一個無序的表中，返回少于 7% 的行，那么這個查詢都可以調整為使用一個索引來代替全表搜索。對于不必要的全表搜索來說，最常見的調優方法是增加索引。可以在表中加入標準的 B 樹索引，也可以加入 bitmap 和基于函數的索引。要決定是否消除一個全表搜索，你可以仔細檢查索引搜索的 I/O 開銷和全表搜索的開銷，它們的開銷和數據塊的讀取和可能的并行執行有關，并將兩者作對比。在一些情況下，一些不必要的全表搜索的消除可以通過強制使用一個 index 來達到，只需要在 SQL 語句中加入一個索引的提示就可以了。
在全表搜索是一個最快的訪問方法時，將小表的全表搜索放到緩存中，調優專家應該確保有一個專門的數據緩沖用作行緩沖。在 Oracle7 中，你可以使用 alter table xxx cache 語句，在 Oracle8 或以上，小表可以被強制為放到 KEEP 池中緩沖。
- 3.2 確保最優的索引使用 ：
對于改善查詢的速度，這是特別重要的。有時 Oracle 可以選擇多個索引來進行查詢，調優專家必須檢查每個索引并且確保 Oracle 使用正確的索引。它還包括 bitmap 和基于函數的索引的使用。
-
3.3 確保最優的 JOIN 操作
有些查詢使用 NESTED LOOP join 快一些，有些則是 HASH join 快一些，另外一些則是 sort-merge join 更快。 -
3.4 基礎SQL優化寫法
1、查詢時候不要寫SELECT * 需要什么字段就放什么字段，select * 非常耗費性能
2、盡量少用distinct ，建議用group by
3、in 或 not in 推薦用 exists /not exist 代替
有時候如果in 中出現的為連續數值 可以用BETWEEN AND 代替
4、IS NULL 或IS NOT NULL操作，建議用替代方法
判斷字段是否為空一般是不會應用索引的，因為索引是不索引空值的。
推薦方法：用其它相同功能的操作運算代替，如：a is not null 改為 a>0 或a>’’等。不允許字段為空，而用一個缺省值代替空值，如字段不允許為空。
5、> 及 < 操作符（大于或小于操作符）
大于或小于操作符一般情況下是不用調整的，因為它有索引就會采用索引查找，但有的情況下可以對它進行優化，如一個表有100萬記錄，一個數值型字段A，30萬記錄的A=0，30萬記錄的A=1，39萬記錄的A=2，1萬記錄的A=3。那么執行A>2與A>=3的效果就有很大的區別了，因為A>2時ORACLE會先找出為2的記錄索引再進行比較，而A>=3時ORACLE則直接找到=3的記錄索引。
6、LIKE操作符
LIKE操作符可以應用通配符查詢，里面的通配符組合可能達到幾乎是任意的查詢，但是如果用得不好則會產生性能上的問題，如LIKE ‘%5400%’ 這種查詢不會引用索引，而LIKE ‘X5400%’則會引用范圍索引。
7、OR 操作符
建議使用union all 連接兩段查詢結果
8、where 字段
比如 substr(a,1,4)=‘abcd’ 可改為 a like ‘abcd%’ 使其走范圍索引
a+2>3 可改為 a>1
9、寫法問題
同一sql 建議寫法一致，比如 select * from table;
不同人可能喜歡全部大寫 SELECT * FROM TABLE
加個空格 SELECT * FROM TABLE;
大小寫混合 select * from TABLE
以上為整理的一些常見的
4. 調整 Oracle 排序
排序是 SQL 語法中一個小的方面，但很重要，在 Oracle 的調整中，它常常被忽略。當用 create index 、 ORDER BY或者 GROUP BY 的語句時， Oracle 數據庫將會自動執行排序的操作。通常，在以下的情況下 Oracle 會進行排序的操作：
使用 Order by 的 SQL 語句
使用 Group by 的 SQL 語句
在創建索引的時候
進行 table join 時，由于現有索引的不足而導致 SQL 優化器調用 MERGE SORT
當與 Oracle 建立起一個 session 時，在內存中就會為該 session 分配一個私有的排序區域。如果該連接是一個專用的連接 (dedicated connection) ，那么就會根據 init.ora 中 sort_area_size 參數的大小在內存中分配一個 Program Global Area (PGA) 。如果連接是通過多線程服務器建立的，那么排序的空間就在 large_pool 中分配。不幸的是，對于所有的 session ，用做排序的內存量都必須是一樣的，我們不能為需要更大排序的操作分配額外的排序區域。因此，設計者必須作出一個平衡，在分配足夠的排序區域以避免發生大的排序任務時出現磁盤排序（ disk sorts ）的同時，對于那些并不需要進行很大排序的任務，就會出現一些浪費。當然，當排序的空間需求超出了 sort_area_size 的大小時，這時將會在 TEMP 表空間中分頁進行磁盤排序。磁盤排序要比內存排序大概慢 14,000 倍。
上面我們已經提到，私有排序區域的大小是有 init.ora 中的 sort_area_size 參數決定的。每個排序所占用的大小由 init.ora 中的 sort_area_retained_size 參數決定。當排序不能在分配的空間中完成時，就會使用磁盤排序的方式，即在 Oracle 實例中的臨時表空間中進行。
磁盤排序的開銷是很大的，有幾個方面的原因。首先，和內存排序相比較，它們特別慢；而且磁盤排序會消耗臨時表空間中的資源。 Oracle 還必須分配緩沖池塊來保持臨時表空間中的塊。無論什么時候，內存排序都比磁盤排序好，磁盤排序將會令任務變慢，并且會影響 Oracle 實例的當前任務的執行。還有，過多的磁盤排序將會令 free buffer waits 的值變高，從而令其它任務的數據塊由緩沖中移走。
5. 調整 Oracle 的競爭
Oracle 的其中一個優點時它可以管理每個表空間中的自由空間。 Oracle 負責處理表和索引的空間管理，這樣就可以讓我們無需懂得 Oracle 的表和索引的內部運作。不過，對于有經驗的 Oracle 調優專家來說，他需要懂得 Oracle 是如何管理表的 extent 和空閑的數據塊。對于調整擁有高的 insert 或者 update 的系統來說，這是非常重要的。
要精通對象的調整，你需要懂得 freelists 和 freelist 組的行為，它們和 pctfree 及 pctused 參數的值有關。這些知識對于企業資源計劃（ ERP ）的應用是特別重要的，因為在這些應用中，不正確的表設置通常是 DML 語句執行慢的原因。
對于初學者來說，最常見的錯誤是認為默認的 Oracle 參數對于所有的對象都是最佳的。除非磁盤的消耗不是一個問題，否則在設置表的 pctfree 和 pctused 參數時，就必須考慮平均的行長和數據庫的塊大小，這樣空的塊才會被有效地放到 freelists 中。當這些設置不正確時，那些得到的 freelists 也是 “dead” 塊，因為它們沒有足夠的空間來存儲一行，這樣將會導致明顯的處理延遲。
Freelists 對于有效地重新使用 Oracle 表空間中的空間是很重要的，它和 pctfree 及 pctused 這兩個存儲參數的設置直接相關。通過將 pctused 設置為一個高的值，這時數據庫就會盡快地重新使用塊。不過，高性能和有效地重新使用表的塊是對立的。在調整 Oracle 的表格和索引時，需要認真考慮究竟需要高性能還是有效的空間重用，并且據此來設置表的參數。以下我們來看一下這些 freelists 是如何影響 Oracle 的性能的。
當有一個請求需要插入一行到表格中時， Oracle 就會到 freelist 中尋找一個有足夠的空間來容納一行的塊。你也許知道， freelist 串是放在表格或者索引的第一個塊中，這個塊也被稱為段頭（ segment header ）。 pctfree 和 pctused 參數的唯一目的就是為了控制塊如何在 freelists 中進出。雖然 freelist link 和 unlink 是簡單的 Oracle 功能，不過設置 freelist link (pctused) 和 unlink (pctfree) 對 Oracle 的性能確實有影響。
由 DBA 的基本知識知道， pctfree 參數是控制 freelist un-links 的（即將塊由 freelists 中移除）。設置 pctfree=10 意味著每個塊都保留 10% 的空間用作行擴展。 pctused 參數是控制 freelist re-links 的。設置 pctused=40 意味著只有在塊的使用低于 40% 時才會回到表格的 freelists 中。
許多新手對于一個塊重新回到 freelists 后的處理都有些誤解。其實，一旦由于一個刪除的操作而令塊被重新加入到 freelist 中，它將會一直保留在 freelist 中即使空間的使用超過了 60% ，只有在到達 pctfree 時才會將數據塊由 freelist 中移走。
表格和索引存儲參數設置的要求總結
以下的一些規則是用來設置 freelists, freelist groups, pctfree 和 pctused 存儲參數的。你也知道， pctused 和 pctfree 的值是可以很容易地通過 alter table 命令修改的，一個好的 DBA 應該知道如何設置這些參數的最佳值。
有效地使用空間和高性能之間是有矛盾的，而表格的存儲參數就是控制這個方面的矛盾：
對于需要有效地重新使用空間，可以設置一個高的 pctused 值，不過副作用是需要額外的 I/O 。一個高的 pctused 值意味著相對滿的塊都會放到 freelist 中。因此，這些塊在再次滿之前只可以接受幾行記錄，從而導致更多的 I/O 。
追求高性能的話，可以將 pctused 設置為一個低的值，這意味著 Oracle 不會將數據塊放到 freelists 中直到它幾乎是空的。那么塊將可以在滿之前接收更多的行，因此可以減少插入操作的 I/O 。要記住 Oracle 擴展新塊的性能要比重新使用現有的塊高。對于 Oracle 來說，擴展一個表比管理 freelists 消耗更少的資源。
設置對象存儲參數的一些常見規則：
經常將 pctused 設置為可以接收一條新行。對于不能接受一行的 free blocks 對于我們來說是沒有用的。如果這樣做，將會令 Oracle 的性能變慢，因為 Oracle 將在擴展表來得到一個空的塊之前，企圖讀取 5 個 “dead” 的 free block 。
表格中 chained rows 的出現意味著 pctfree 太低或者是 db_block_size 太少。在很多情況下， RAW 和 LONG RAW 列都很巨大，以至超過了 Oracle 的最大塊的大小，這時 chained rows 是不可以避免的。
如果一個表有同時插入的 SQL 語句，那么它需要有同時刪除的語句。運行單一個一個清除的工作將會把全部的空閑塊放到一個 freelist 中，而沒有其它包含有任何空閑塊的 freelists 出現。
freelist 參數應該設置為表格同時更新的最大值。例如，如果在任何時候，某個表最多有 20 個用戶執行插入的操作，那么該表的參數應該設置為 freelists=20
八、數據字典、動態視圖
dictionary and dynamic view
Oracle 字典和動態視圖
oracle服務顯示全部的系統統計信息在v$sysstat 視圖中，也用很多視圖展現oracle性能和問題快照信息。
你能夠通過查詢這些視圖發現被啟動的實例總體信息。
oracle服務在DBA_xxx 視圖中顯示數據存儲統計信息，可以用來查找存儲故障(表，簇，索引)
utlbstat and utlestat 工具
你可能需要收集在制定業務時間內的性能特征， 可以用utlbstata.sql 和utlbstat.sql腳本 ，有經驗的人通常用這兩個工具去獲取數據來調優項目
Oracle 等待事件
如果你正排出數據庫系統的故障，那么需要知道有那些等待的進程
oracle 提供了一個等待事件列表，一些字典視圖也提供了哪些session 在等待
oracle 診斷和調優包
在調優時可以使用oracle提供的圖形界面工具來診斷和調優。例如圖形監控、分析、自動調優。
字典和專用視圖
在運行分析命令后，字典和專用視圖回提供有用的統計信息。例如：
dba_tables dba_tab_columns dba_clusters dba_indexs ,dba_index_stats, index_histogram, dba_histogams.
下面是動態快照和性能試圖
vviewvview vviewvfixed_table
x$tables
下面分類列出了可用到的一些統計視圖
數據庫實例信息
v$database
v$instance
v$option
v$parameter
v$backup
v$process
v$waritstat
v$system_event
磁盤信息
v$datafile
v$filestat
v$log
v$log_history
v$dbfile
V$tempfile
v$tempstat
用戶和會話信息
v$lock
v$oper_cursor
v$process
v$sort_usage
v$session
v$sessstat
v$transaction
v$session_event
v$session_wait
v$px_sesstat
v$px_session
v$session_object_cache
內存信息
v$buffer_pool_statistics
v$db_object_cache
v$librarycache
v$rowcache
v$systat
v$sgastat
競爭信息
v$lock
v$rollname
v$rollstat
v$waitstat
v$latch
總結
以上是生活随笔為你收集整理的大数据-玩转数据-Oracle系统知识小结的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 图像的离散余弦变换
- 下一篇： c语言字符串逆置,字符串逆置