第 4 頁

共 41 頁

南華大學計算機科學與技術學院畢業設計（論文）

2.2.1 備份恢復的分類

按照備份恢復的方式，可以分為邏輯備份、冷備份（脫機備份）、熱備份，其中，熱備份和冷備份合稱為物理備份。按照備份的工具，可以分為EXP/IMP備份、操作系統備份RMAN、第三方工具備份，如VERITAS等。

2.2.2 備份方案的介紹

邏輯備份是指只備份數據庫中的數據但不記錄數據物理位置的一種備份。導出為數據庫作一個二進制的備份，并且這個備份只能由其姐妹程序imp來讀取

操作系統備份包括冷備份和熱備份。操作系統備份和上面的邏輯備份有本質的區別，它將拷貝整個的數據文件。冷備份在文件級備份開始前數據庫必須徹底關閉。熱備份是當數據庫打開時的操作系統備份。

Recovery Manager（RMAN）是一個使DBA能很方便地對數據庫執行備份和恢復任務的oracle應用工具，能夠提供DBA對企業數據庫備份與恢復操作的集中控制。

2.3 數據庫的備份策略

2.3.1 概述

爭取的備份策略不僅能保證數據庫服務器的7*24小時的高性能的運行，還能保證備份與恢復的快速性與可靠性。采用多級備份就是為了減少每天備份所需要的時間，而保證系統有良好的恢復性。恢復時間與備份時間要有一個權衡。比如只要進行一個數據庫的全備份，然后就只備份歸檔也可以保證能把數據庫恢復到最新的狀態，但是這樣的恢復時間將是不可容忍的。

第 5 頁

共 41 頁

南華大學計算機科學與技術學院畢業設計（論文）

第三章 備份與恢復功能設計與實現

3.1 冷備份（脫機備份）

3.1.1 定義

冷備份（脫機備份）是當數據庫的一切可以被讀取和寫入的數據庫物理文件（參數文件、控制文件，數據文件等）具有一樣的系統改變號（SCN）時才能進行的數據庫的一致性備份，數據庫保持在同一狀態的唯一方法是將數據庫正常關閉，因此只在數據庫正常關閉情況下的備份才算是一致性備份。

冷備份既適用于archivelog模式，也適用于noarchivelog模式。

3.1.2 優點與缺點

優點：

快速并且相對簡單的備份方法（因為僅僅需要拷貝文件）。容易恢復至某個時間點上（僅需將文件拷貝回去）。低度維護，高度安全。缺點：

單獨使用時，只能提供到“某一時間點上”的恢復。在冷備份過程中，數據庫必須是關閉狀態。

3.1.3 步驟

（1）、查看數據庫文件物理位置。

第 6 頁

共 41 頁

南華大學計算機科學與技術學院畢業設計（論文）

圖 2.1 數據文件

圖2.2 控制文件

第 7 頁

共 41 頁

南華大學計算機科學與技術學院畢業設計（論文）

圖2.3 日志文件

圖2.4 參數文件

（2）關閉運行的數據庫。

（3）備份參數文件，控制文件，數據文件，日志文件到指定目錄下。

3.1.4 備份恢復功能設計與實現

（1）建立測試表test，并且插入數據‘1’，恢復后，數據應該還是存在的

圖2.5 創建測試表

圖2.6（2）關閉數據庫，執行備份

圖2.7 執行冷備份

（3）插入另外一條數據‘2’，恢復時應該丟失的數據

第 8 頁

共 41 頁

南華大學計算機科學與技術學院畢業設計（論文）

圖2.8 插入驗證信息

（4）關閉數據庫，刪除所有的文件：數據文件，控制文件，參數文件，日志文件

圖2.9 刪除文件

（5）啟動數據庫

圖2.10 啟動數據庫

（6）恢復冷備份的文件到各自的文件夾

圖2.11 冷備份的恢復

（7）驗證冷備份的恢復，數據‘2’丟失，只有數據‘1’，備份后插入的數

第 9 頁

共 41 頁

南華大學計算機科學與技術學院畢業設計（論文）

據不存在。

圖2.12 驗證恢復

3.2 熱備份恢復（聯機備份恢復）

3.2.1定義

數據庫熱備份是指數據庫在啟動運行的狀態下，對數據庫的控制文件、參數文件、數據文件等進行備份操作。熱備份是用戶管理備份恢復的一種方式，也是除了RMAN備份之外比較經常使用的一種備份方式。

熱備份僅僅適用于archivelog狀態。

3.2.2 優點與缺點

優點：

可以在表空間或數據文件級備份，備份的時間短。備份時數據庫仍可以使用。

可達到秒級恢復（恢復到某一時間點上）缺點：

不能出錯，否則備份數據不可用。備份方法相對復雜，難于維護。

3.2.3 備份設計與實現

凍結塊頭-->控制SCN在備份時不發生變化 進行物理拷貝

第 10 頁

共 41 頁

南華大學計算機科學與技術學院畢業設計（論文）

解凍塊頭-->讓SCN可以變化(當對SCN解凍后，系統會自動更新SCN至最新的狀態)查看是否在自動歸檔模式：

圖2.13 非歸檔模式

如果不是則啟動自動歸檔模式：

圖2.14 啟動歸檔模式

第 11 頁

共 41 頁

南華大學計算機科學與技術學院畢業設計（論文）

圖2.15 在線備份數據文件

圖2.16 在線備份控制文件

圖2.17 備份參數文件和重做日志歸檔文件

完全恢復：

（1）用戶表空間數據文件受損

A、查看數據庫是否運行在歸檔模式，確保能把數據完全恢復，如果不是，則設置為自動歸檔模式

圖2.18 歸檔模式

第 12 頁

共 41 頁

南華大學計算機科學與技術學院畢業設計（論文）

B、在用戶表空間創建測試表，并且插入測試數據

圖2.19 插入驗證數據

C、對users表空間數據文件進行熱備份

圖2.20 備份用戶空間

D、插入熱備份后的另外一條數據，驗證熱備份完全恢復

圖2.21 插入測試數據

第 13 頁

共 41 頁

南華大學計算機科學與技術學院畢業設計（論文）

E、關閉數據庫，刪除用戶表空間文件，模擬出錯

圖2.22 模擬故障

查表v$recover_file，用戶數據文件需要恢復

圖2.23 查看需要回復的數據文件

如果實際中，數據庫需要對外服務，但是用戶數據文件尚未修復，可以選擇先把數據文件離線再進行修復。

圖2.24 離線數據文件

第 14 頁

共 41 頁

南華大學計算機科學與技術學院畢業設計（論文）

圖2.25 恢復離線的數據文件

F、查看驗證熱備份的數據，確認是否完全恢復，重做數據文件到當前的時間點，確保備份后的數據還在，前提是要處于歸檔模式，切歸檔重做文件都沒被刪除

圖2.26 查看熱備份的效果

(2)用戶表空間數據文件受損且無備份的恢復 第一種情況，先刪除數據文件，在觸發檢查點

第 15 頁

共 41 頁

南華大學計算機科學與技術學院畢業設計（論文）

A、創建用戶表空間做測試

圖2.27 創建測試數據

B、刪除用戶表空間，模擬故障，表格還在的原因是因為數據已經緩存到內存了，查找內容實際是在內存找

圖2.28 模擬故障

C、當觸發檢查點，內存數據會寫盤，然而查找數據的話，沒改變數據內容，寫盤的時候不需要修改內容，故數據文件test.dbf不存在也不會報錯，但是再查找表的內容的時候，要從磁盤讀，因此發現了數據文件不存在

圖2.29 數據文件故障

第 16 頁

共 41 頁

南華大學計算機科學與技術學院畢業設計（論文）

D、關閉系統，然后啟動后發現，數據文件處于recover狀態

圖2.30 recover狀態的數據文件

E、創建丟失的數據文件，恢復文件的內容

圖2.31 創建需要的文件

圖2.32 查看數據文件狀態

第 17 頁

共 41 頁

南華大學計算機科學與技術學院畢業設計（論文）

F、驗證數據是否還是存在，數據還在的條件是，歸檔日志文件存在的時間要比表空間創建的時間要久遠，才有重做日志文件支持完全恢復。

圖2.33 驗證恢復

第二種情況：先觸發檢查點，在刪除數據文件

圖2.34 情況二

第 18 頁

共 41 頁

南華大學計算機科學與技術學院畢業設計（論文）

圖2.34 數據文件需要恢復

圖2.35 恢復后的數據

（3）System表空間數據文件或者大量數據文件受損 A、對system表空間進行熱備份

圖2.35 熱備份

第 19 頁

共 41 頁

南華大學計算機科學與技術學院畢業設計（論文）

B、刪除system數據文件，模擬數據文件損壞

圖2.36 刪除數據文件

C、把熱備份的數據文件重新拷回去，恢復系統數據文件

圖2.37 恢復數據文件

（4）回滾文件損壞的恢復

A、查看回滾表空間的相關信息（回滾文件可以在線刪除）

圖2.38 回滾表空間信息

B、刪除undo數據文件，啟動數據庫

圖2.39 模擬故障

第 20 頁

共 41 頁

南華大學計算機科學與技術學院畢業設計（論文）

圖2.40 故障

C、把回滾數據文件離線，啟動數據庫

圖2.41 離線數據文件

D、創建一個新的回滾表空間，并修改參數undo_tablespace，使undotbs1能成功刪除

圖2.42 創建回滾表空間

圖2.43 創建修改回滾表空間

第 21 頁

共 41 頁

南華大學計算機科學與技術學院畢業設計（論文）

E、創建測試表test

圖2.44 創建測試數據

F、關閉數據庫，發現數據庫一直關閉不了，掛起了，查看警告文件

圖2.45 關機掛起

第 22 頁

共 41 頁

南華大學計算機科學與技術學院畢業設計（論文）

圖2.46 查看跟蹤文件

G、啟動數據庫

圖2.47 故障

H、修改參數文件pfile，增加一行

圖2.48 修改SPFILE

圖2.48 啟動數據庫

（5）臨時文件損壞的修復 A、當控制文件不需要重建的時候

第 23 頁

共 41 頁

南華大學計算機科學與技術學院畢業設計（論文）

圖2.49 模擬故障

B、控制文件重建的時候，因為控制文件的創建不包含temp的路徑，若創建時指定會報錯，故在啟動數據庫后要手工綁定。

圖2.50 模擬故障2

圖2.51 查看臨時表空間

圖2.52 大查詢

圖2.53 手工創建臨時表空間

不完全恢復

（1）基于time時間的恢復

基于時間的恢復能將數據庫通過不完全恢復，到過去的某一時間點，經常被用在誤操作導致的刪除數據，在備機上將數據庫恢復至被刪除時間點之前的狀態，然后把該表導出到正式環境，以挽回丟失數據。

第 24 頁

共 41 頁

南華大學計算機科學與技術學院畢業設計（論文）

A、建立測試表test，插入測試數據

圖2.54 創建測試數據表

B、熱備份除temp表空間以外所有的數據文件，雖然恢復的數據在users表空間，但是不完全恢復基于時間的恢復，所有的表空間數據文件都應該恢復到同一個時間點上，因此，需要備份所有數據文件。

圖2.55 熱備份所有文件

第 25 頁

共 41 頁

南華大學計算機科學與技術學院畢業設計（論文）

C、繼續插入測試數據，熱備份后的數據，如果僅僅恢復備份，數據應該不存在，查看數據丟失之前的時間點，用于不完全恢復的時間參考。

圖2.54 繼續創建測試數據

D、刪除測試數據表，關閉數據庫，拷回熱備份，啟動到mount狀態，根據時間點恢復。

圖2.55

E、重新以resetlogs方式打開數據庫，查看丟失數據是否存在。

圖2.56 驗證

（2）基于改變scn的恢復

第 26 頁

共 41 頁

南華大學計算機科學與技術學院畢業設計（論文）

基于改變scn號（system change number）的恢復可以將數據庫不完全恢復到過去的某一個scm改變點，也常用于在誤操作刪除數據后，在備用機上將數據庫恢復到刪除scn之前，然后把表導出到正式的環境，挽回丟失的數據。

圖2.57

圖2.58

第 27 頁

共 41 頁

南華大學計算機科學與技術學院畢業設計（論文）

圖2.59（3）控制文件損壞的恢復 *單個控制文件被破壞

由于數據庫的三個控制文件都是相互備份的，因此，當損壞一個數據文件的時候，可以從另外的兩個數據文件拷貝創建新的數據文件，名字相同即可。或者把pfile中控制文件的數量改變為兩個，重新以pfile啟動數據庫

圖2.60

第 28 頁

共 41 頁

南華大學計算機科學與技術學院畢業設計（論文）

圖2.61 *多個控制文件被破壞

在很多個控制文件都被破壞的情況下，如果沒有對控制文件做備份，那么只能通過重建控制文件生成。在創建控制文件的時候，應該很清楚的了解到每一個數據文件，日志文件的具體位置，確保在重建數據文件之后，數據庫的正確性，并且，在重建控制文件的時，是不包含temp01.dbf數據文件的，只能在打開數據庫后，進行temp表空間的重新建立，以達到temp表空間的使用。

圖2.62

第 29 頁

共 41 頁

南華大學計算機科學與技術學院畢業設計（論文）

圖2.63（4）日志文件損壞的恢復

*正常關機情況下非當前聯機日志文件被破壞

第 30 頁

共 41 頁

南華大學計算機科學與技術學院畢業設計（論文）

正常關閉DB時緩存均已寫盤，且logfile已歸檔，故僅需clear logfile 重新生成丟失的非當前日志文件即可，無數據丟失；若日志未歸檔，必須加unarchived參數，才會允許重新生成日志文件，并標志所有備份失效，應該重做全備份。

圖2.64

圖2.65 *正常關機情況下當前聯機（或所有）日志文件被破壞

第 31 頁

共 41 頁

南華大學計算機科學與技術學院畢業設計（論文）

正常關閉DB時緩存均已寫盤，且logfile已歸檔，因此僅需until cancel 取消系統查找當前聯機日志，并且resetlogs重新生成對視的當前聯機日志文件即可，無數據丟失。

圖2.66

圖2.67

第 32 頁

共 41 頁

南華大學計算機科學與技術學院畢業設計（論文）

*DB運行時或非正常關機的情況下已歸檔active日志文件被破壞

圖2.68 系統suggestion提示文件正確即可回車確認使用該文件進行重做，提示錯誤則可手工輸入filename使用指定文件進行重做，這兩種重做都是單步進行的，重做完本歸檔或者日志文件后，又會繼續提示指定用來重做的下一個歸檔或日志文件，而auto則自動尋找所有歸檔或日志文件進行重做，非單步進行，后續歸檔或日志文件的重做無提示等待確認

圖2.69

第 33 頁

共 41 頁

南華大學計算機科學與技術學院畢業設計（論文）

前一個歸檔或日志文件已經完成恢復重做，不再需要，且再次提示的用于后續恢復的歸檔文件是錯誤的而應該使用當前日志文件進行恢復，因此手工輸入當前日志文件名進行恢復。

因active日志已經被歸檔，因此oracle數據庫可以到歸檔文件或者current日志文件中找到需要的數據，以進行實例恢復，次過程沒有數據丟失，最后resetlogs用來重新生成已經丟失的歸檔active的日志文件。

*DB運行時或非正常關機情況下未歸檔active或current日志文件被破壞

圖2.70

圖2.71

第 34 頁

共 41 頁

南華大學計算機科學與技術學院畢業設計（論文）

圖2.72 因active/current日志并沒有被歸檔，所以數據庫沒辦法到歸檔日志中找到需要數據進行數據庫的實例恢復，數據文件在沒有歸檔active或current日志文件上不一致，并且沒辦法使用已經丟失的沒有歸檔文檔active或當前日志文件把數據文件重做到一致的時間點上，只可以通過全備份進行不完全恢復，將所有數據文件時間點重做到最后一個可用日志文件中的最后一條重做記錄處，其后丟失的未歸檔active或當前日志文件通過resetlogs重新生成而未進行重做，故有數據丟失，但數據仍一致的，所有數據文件均處于最后一個日志文件中最后一條重做記錄所對應的時間點處。

第 35 頁

共 41 頁

南華大學計算機科學與技術學院畢業設計（論文）

*DB運行時或非正常關機情況下未歸檔active或current日志文件被破壞（無備份）

圖2.73

圖2.74

第 36 頁

共 41 頁

南華大學計算機科學與技術學院畢業設計（論文）

圖2.75

圖2.76 因current/active日志并沒有歸檔，因此數據庫沒辦法到歸檔日子文件中找到需要的數據進行實例恢復，數據文件在沒有歸檔active或current當前日志文件上是不一致的，并且沒有全備份重做恢復到相同的時間點上面，只能通過設置_allow_resetlogs_corruption=true隱含參數，并resetlogs重新生成丟失的未歸檔active或current日志文件，使數據庫在不一致情況下啟動以便備出數據，這種操作會有數據丟失。

第 37 頁

共 41 頁

南華大學計算機科學與技術學院畢業設計（論文）

第四章 總結

保證oracle數據庫的安全是系統安全的重要組成部分，必須要設計完善的數據庫備份和恢復方案。Oracle提供的各種工具結合起來使用能夠使數據庫的備份和恢復變得簡單。在實際的oracle數據庫的備份和恢復中，會有許多不同的和復雜的情況出現，針對不同的情況，要本著使數據具有最大的可恢復性和恢復時間最短的原則去進行數據庫的恢復，這需要大量的實驗和經驗積累。

第 38 頁

共 41 頁

南華大學計算機科學與技術學院畢業設計（論文）

參考文獻

[1] Thomas Kyte.Expert One-on-One[M].Apress，2003.8 [2] Benjamin Rosenzweig，Elena Silvestrova Rakhimov.Oracle PLSQL實例精解第4版[M].Prentice Hall PTR，2009.6 [3] Thomas Kyte.Expert Oracle Database Architecture 2nd Edition[M].Apress，2010.7 [4] Administrator's Guide.oracle公司

[5] Backup and Recovery Advanced User's Guide.oracle公司 [6] Backup and Recovery Basics.oracle公司

[7] 李丙洋.涂抹oracle--三思筆記之一步一步學oracle[M]中國水利水電出版社,2010.1 [8] Karen Morton.Oracle SQL高級編程[M].人民郵電出版社，2011.11 [9] 梁敬彬，梁敬弘.收獲不止oracle[M].電子工業出版社,2013.5 [10] 蓋國強.循序漸進oracle數據庫管理、優化與備份恢復[M].人民郵電出版社，2011.8 [11] Lan Abramson,Micheael Abbey,Michael J.Corey,竇朝輝.oracle database 11g:初學者指南[M].清華大學出版社，2010-01 [12] 蓋國強.oracle dba手記3：數據庫性能優化與內部原理解析[M].電子工業出版社，2011.9 [13] K Gopalakrishnan，賈洪峰，梁濤，郭紹明.oracle database 11g Rac手冊[M].清華大學出版社，2012.6 [14] John Beresniewicz,Adrian Billington,Martin Buchi.oracle PL/SQL實戰[M].人民郵電出版社，2012.11 [15] 蒙邵良，oracle dba高效入職指南[M].清華大學出版社，2012.1 [16] 郭鄭州，陳軍紅.oracle 完全學習手冊[M].清華大學出版社，2011.1 [17] 包光磊.臨危不懼：oracle 11g 數據庫恢復技術[M].電子工業出版社，2012.7 [18] Robert G.Freeman,Matthew Hart,王念濱，陳子陽.oracle database 11g Rman備份與恢復[M].清華大學出版社,2011.4

第 39 頁

共 41 頁

南華大學計算機科學與技術學院畢業設計（論文）

[19] Edward Whalen，陳曙暉.基于Linux平臺的oracle database 10g管理[M].清華大學出版社，2007.1

[20] 林樹澤，歷鐵帥，何會軍.oracle 數據庫管理之道[M].清華大學出版社,2012.7

第 40 頁

共 41 頁

南華大學計算機科學與技術學院畢業設計（論文）

謝 辭

論文工作接近尾聲，本科學習生涯即將結束。我要感謝大學期間老師、同學們對我的幫助和指導，特別是在論文寫作期間，譚敏生院長對我的論文指導跟審查，使我的論文得以順利完成。

最后我想談談這篇論文和系統存在的不足。這篇論文的寫作以及修改過程，也是我認識到自己知識與經驗缺乏的過程。雖然我盡可能地收集資料，竭盡所能運用自己所學的知識進行論文寫作，但是我的論文還存在許多不足之處，有待改進。由于本人水平有限，對某些概念和方法的理解還不是很深刻，以致文章的闡述缺乏足夠的說服力，請各位評委老師批評指正。通過這次論文的寫作，我個人在信息檢索、閱讀寫作、基礎知識、溝通能力等方面都得到了一定程度的提高。這是我很欣慰的地方。但是，這篇論文的寫作以及分析的過程，也是我越來越認識到自己知識與分析能力薄弱的過程。雖然，我盡可能地收集材料，竭盡所能運用自己所學的知識進行的論文寫作和分析，但論文還是存在許多不足之處分析不透徹或者建議不具體，在以后的研究中還需要努力。所以請各位老師多批評指正，讓我在今后的學習與工作中做到更好。

第 41 頁

共 41 頁

第二篇：oracle語法

第一篇 基本操作

--解鎖用戶 alter user 用戶 account unlock;--鎖定用戶 alter user 用戶 account lock;alter user scott account unlock;

--創建一個用戶yc 密碼為a create user 用戶名 identified by 密碼； create user yc identified by a;

--登錄不成功，會缺少create session 權限，賦予權限的語法 grant 權限名 to 用戶； grant create session to yc;

--修改密碼 alter user 用戶名 identified by 新密碼； alter user yc identified by b;

--刪除用戶 drop user yc;

--查詢表空間

select *from dba_tablespaces;--查詢用戶信息

select *from dba_users;--創建表空間

create tablespace ycspace datafile 'E:oracleappproduct11.2.0dbhome_1oradataycspace.dbf' size 2m autoextend on next 2m maxsize 5m offline;

--創建臨時表空間

create temporary yctempspace tempfile 'E:oracleappproduct11.2.0dbhome_1oradataycspace.dbf' size 2m autoextend on next 2m maxsize 5m offline;

--查詢數據文件

select *from dba_data_files;

--修改表空間

--

1、修改表空間的狀態

--默認情況下是online,只有在非離線情況下才可以進行修改

alter tablespace ycspace offline;--離線狀態，不允許任何對象對該表空間的使用，使用情況：應用需要更新或維護的時候；數據庫備份的時候 alter tablespace ycspace read write;--讀寫狀態 alter tablespace ycspace online;alter tablespace ycspace read only;--只讀，可以查詢信息，可以刪除表空間的對象，但是不能創建對象和修改對象。使用情況：數據存檔的時候

--

2、修改表空間的大小--增加文件的大小

alter database datafile 'E:oracleappproduct11.2.0dbhome_1oradataycspace.dbf' resize 10m;--增加數據文件

alter tablespace ycspace add datafile 'E:oracleappproduct11.2.0dbhome_1oradataadd.dbf' size 2m;

--刪除表空間的數據文件

alter tablespace 表空間的名字 drop datafile 數據文件名；

--刪除表空間

drop tablespace ycspace;

--刪除表空間且表空間中的內容和數據文件

drop tablespace ycspace including contents and datafiles;

--指定表空間 的 創建用戶的語法

create user yc1 identified by a default tablespace ycspace temporary tablespace temp;

--刪除用戶 drop user yc1;

--權限

--賦予創建會話的權限 grant create session to yc1;

--創建一個表

create table studentInfo(sid int, sname varchar2(10));

--賦予yc1用戶創建表的權限 grant create table to yc1;--賦予yc1使用表空間的權限 grant unlimited tablespace to yc1;

--系統權限

--對象權限

--插入

insert into studentInfo values(2,'abcd');--查詢

select *from studentInfo;--修改

update studentInfo set sid=1;--刪除

delete studentInfo;drop table studentInfo;--系統權限刪除表

--賦權的語法

--系統權限

grant 權限名（系統權限或對象權限,角色，all)to 用戶(角色，public)with admin option;

--對象權限

grant 權限名（系統權限或對象權限,角色，all)on 用戶(角色，public)with grant option;

--收權語法--系統權限

revoke 權限名（系統權限或對象權限,角色，all)from 用戶(角色，public)with admin option;--對象權限

revoke 權限名（系統權限或對象權限,角色，all)from 用戶(角色，public)with grant option;

--賦予創建用戶的權限并且把這個權限傳遞下去，即yc1可以給別人賦權 grant create user to yc1 with admin option;

--收回權限,只能收回scottd ,不能收回由scott賦權的yc1的權限 revoke create user from scott;

--查看用戶所具有的權限 select *from user_sys_privs;

--對象權限詳解 select * from emp;--使用yc1來查詢scott里面的emp表 select * from scott.emp;

--賦予yc1查詢emp表和插入的權限 grant select on emp to yc1;grant insert on emp to yc1;grant update(empno,ename)on emp to yc1;

grant delete on emp to yc1;

--對scott的emp表添加數據

insert into scott.emp(empno,ename)value(111,'acv');update scott.emp set ename='yc'where empno=111;

--賦予查詢、賦予刪除、添加、修改 grant select on 表名 to 用戶

--grant select,delete,update,insert on 表名 to 用戶 grant select,delete,update,insert on emp to yc1;grant all on dept to yc1;--all代表所有的對象權限

select *from scott.emp;

select *from scott.dept;insert into scott.dept values(50,'企事業文化部','bumen');

--查看角色

--dba:數據庫管理員，系統最高權限，可以創建數據結構（表空間等）--resource:可以創建實體（表、視圖），不可以創建數據庫的結構

--connect:連接的權限，可以登錄數據庫，但是不可以創建實體和不可以創建數據庫結構

select *from role_sys_privs;

grant connect to yc1;

--將可以連接的角色賦予給yc1，則yc1就是應該可以連接數據庫的人，類似于 create session。create table StuInfos(sid int);

select *from StuInfos;

create table stuInfo(sid int primary key ,--主鍵 primary key 非空且唯一（主鍵約束）sname varchar2(10)not null,--姓名不能為空,（非空約束）sex char(2)check(sex in('男','女')),--（檢查約束），check, age number(3,1)constraint ck_stuInfo_age check(age>10 and age<100),--也可以用varchar ；age between 10 and 100 ,在10和100之間，是一個閉區間 tel number(15)unique not null,--唯一約束，address varchar2(200)default '什么鬼')

insert into stuInfo values（3,'大大','男',18，4321543,default);insert into stuInfo values(1,'張三','男',10);select *from stuInfo;

drop table stuInfo;

create table classInfo(cid int primary key,--班級id cname varchar2(20)not null unique--班級名)create table stuInfo(sid int primary key, sname varchar2(20), cid int constraint fofk_stuInfo_cid references classInfo(cid)on delete cascade)insert into classInfo values(1,'1班');insert into classInfo values(2,'2班');insert into classInfo values(3,'3班');insert into classInfo values(4,'4班');

select *from classInfo;select *from stuInfo;

insert into stuInfo values(1001,'張三',2);insert into stuInfo values(1002,'張四',4);

update classInfo set cid=1 where cid=8;

drop table stuInfo;--要先刪除這個 drop table classInfo;--再刪除這個

delete classInfo where cid=4;--同時刪除這兩個表中的4

--刪除用戶的時候

drop user yc1 [cascade]--刪除用戶的同時把它創建的對象都一起刪除

--修改表

--

1、添加表中字段

--alter table 表名 add 字段名 類型

alter table classInfo add status varchar2(10)default '未畢業'

--

2、修改已有字段的數據類型

--alter table 表名 modify 字段名 類型 alter table classInfo modify status number(1)

--

3、修改字段名

--alter table 表名 rename column 舊字段名 to 新的字段名 alter table classInfo rename column cname to 班級名;

--

4、刪除字段--alter table 表名 drop column 字段名 alter table classInfo drop column status;

--

5、修改表名

--rename 舊表名 to 新表名 rename classInfo to 班級信息；

--刪除表

--

1、截斷表效率高，每刪除一次會產生一次日志

2、截斷會釋放空間，而delete不會釋放空間

--刪除表結構和數據 drop table 表名；--刪除表中所有數據 truncate table classInfo;delete classInfo;

create table classInfo(cid int primary key,--班級id cname varchar2(20)not null unique ,--班級名 stasuts varchar2(100));select *from classInfo;

--數據的操作

--增加數據語法

--insert into 表名[(列名，....)] values(對應的數據的值）；

insert into classInfo values(1,'一班','未畢業');--需要按照表結構的順序插入 insert into classInfo values(4,'六班','未畢業');insert into classInfo(cname,cid)values('二班',2);--需要按照括號中的順序插入，但是 not null primary key 必須插入的。

insert into classInfo(cname,cid)values('三班',3);

--刪除的語法

--delete 表名 [where 條件] delete classInfo where cid>=2;

--修改記錄的語法

--update 表名 set [字段='值' ] [where 條件] update classInfo set cname='三班';--會修改所有該字段 update classInfo set cname='四班' where cid=1;update classInfo set cname='五班', stasuts ='未畢業' where cid=3;

--alter table classInfo drop constraint SYS_C0011213;

--添加多個時可以使用序列--用序列來做自動增長

create sequence seq_classInfo_cid start with 1001 increment by 1;

insert into classInfo values(seq_classInfo_cid.Nextval,'七班','未畢業');insert into classInfo values(seq_classInfo_cid.Nextval,'八班','未畢業');insert into classInfo values(seq_classInfo_cid.Nextval,'九班','未畢業');insert into classInfo values(seq_classInfo_cid.Nextval,'十班','未畢業');

create table classInfo2(cid int primary key,--班級id cname varchar2(20)not null unique ,--班級名 stasuts varchar2(100));select *from classInfo2;drop table classInfo2;

insert into classInfo2 select *from classInfo;insert into classInfo(cname,cid)select cname,cid from classInfo;alter table classInfo2 drop constraint SYS_C0011213;

select seq_classInfo_cid.nextval from dual;select seq_classInfo_cid.Currval from dual;

--直接創建一個新表，并拿到另一個表其中的數據 create table newTable as select cname,cid from classInfo;create table newTable1 as select *from classInfo;

select *from newTable;select *from newTable1;insert into newTable1 values(1008,'dg','');

直接在使用scott登陸，進行查詢操作

----------------------簡單查詢

select *from emp;

select empno as id,ename as name from emp;

select empno 編號,ename 姓名 from emp;

--去除重復

select job from emp;select distinct job from emp;select job,deptno from emp;select distinct job,deptno from emp;

--字符串的連接

select '員工編號是' ||empno || '姓名是' ||ename ||'工作是'||job from emp;

--乘法

select ename,sal *12 from emp;--加減乘除都類似

---------限定查詢

--獎金大于1500的

select *from emp where sal>1500;--有獎金的

select *from emp where comm is not null;--沒有獎金的

select *from emp where comm is null;--有獎金且大于1500的

select *from emp where sal>1500 and comm is not null;--工資大于1500或者有獎金的

select *from emp where sal>1500 or comm is not null;--工資不大于1500且沒獎金的

select *from emp where sal<=1500 and comm is null;select *from emp where not(sal >1500 or comm is not null);--工資大于1500但是小于3000的

select *from emp where sal>1500 and sal<3000;select *from emp where sal between 1500 and 3000;--between是閉區間，是包含1500和3000的

--時間區間

select *from emp where hiredate between to_date('1981-01-01','yyyy-MM-dd')and to_date('1981-12-31','yyyy-MM-dd');--查詢雇員名字

select *from emp where ename='SMITH';--查詢員工編號

select *from emp where empno=7369 or empno=7499 or empno=7521;select *from emp where empno in(7369,7499,7521);select *from emp where empno not in(7369,7499,7521);--排除這3個，其他的都可以查

--模糊查詢

select *from emp where ename like '_M%';--第2個字母為M的 select *from emp where ename like '%M%';select *from emp where ename like '%%';--全查詢

--不等號的用法

select * from emp where empno!=7369;select *from emp where empno<> 7369;

--對結果集排序--查詢工資從低到高

select *from emp order by sal asc;select *from emp order by sal desc,hiredate desc;--asc 當導游列相同時就按第二個來排序--字符函數

select *from dual;--偽表 select 2*3 from dual;select sysdate from dual;--變成大寫

select upper('smith')from dual;--變成小寫

select lower('SMITH')from dual;--首字母大寫

select initcap('smith')from dual;--連接字符串

select concat('jr','smith')from dual;--只能在oracle中使用 select 'jr' ||'smith' from dual;--推薦使用--截取字符串

select substr('hello',1,3)from dual;--索引從1開始--獲取字符串長度 select length('hello')from dual;--字符串替換

select replace('hello','l','x')from dual;--把l替換為x-------通用函數--數值函數--四舍五入

select round(12.234)from dual;--取整的四舍五入 12 select round(12.657,2)from dual;--保留2位小數 select trunc(12.48)from dual;--取整

select trunc(12.48675，2)from dual;--保留2位小數--取余

select mod(10,3)from dual;--10/3取余 =1

--日期函數

--日期-數字=日期 日期+數字=日期 日期-日期=數字

--查詢員工進入公司的周數

select ename,round((sysdate-hiredate)/7)weeks from emp;--查詢所有員工進入公司的月數

select ename,round(months_between(sysdate,hiredate))months from emp;--求三個月后的日期

select add_months(sysdate,6)from dual;select next_day(sysdate,'星期一')from dual;--下星期 select last_day(sysdate)from dual;--本月最后一天

select last_day(to_date('1997-1-23','yyyy-MM-dd'))from dual;

--轉換函數 select ename , to_char(hiredate,'yyyy')年，to_char(hiredate,'mm')月，to_char(hiredate,'dd')日 from emp;

select to_char(10000000,'$999,999,999')from emp;

select to_number('20')+to_number('80')from dual;--數字相加

--查詢員工年薪

select ename,(sal*12+nvl(comm,0))yearsal from emp;--空和任何數計算都是空

--Decode函數，類似if else if(常用）

select decode(1,1,'one',2,'two','no name')from dual;--查詢所有職位的中文名 select ename, decode(job, 'CLERK', '業務員', 'SALESMAN', '銷售', 'MANAGER', '經理', 'ANALYST', '分析員', 'PRESIDENT', '總裁', '無業')from emp;

select ename, case when job = 'CLERK' then '業務員' when job = 'SALESMAN' then '銷售' when job = 'MANAGER' then '經理' when job = 'ANALYST' then '分析員' when job = 'PRESIDENT' then '總裁' else '無業' end from emp;

-----------------------------

--多表查詢

select *from dept;select *from emp,dept order by emp.deptno;select *from emp e,dept d where e.deptno=d.deptno;select e.*,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno;

--查詢出雇員的編號，姓名，部門編號，和名稱，地址

select e.empno,e.ename,e.deptno,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno;

--查詢出每個員工的上級領導

select e.empno,e.ename,e1.empno,e1.ename from emp e,emp e1 where e.mgr=e1.empno;

select e.empno,e.ename,d.dname from emp e,dept d ,salgrade s, emp e1 where e.deptno=d.deptno and e.sal between s.losal and s.hisal and e.mgr=e1.empno;

select e.empno,e.ename,e1.empno,e1.ename from emp e,emp e1 where e.mgr=e1.empno(+);

--外連接

select *from emp order by deptno;--查詢出每個部門的員工 /* 分析：部門表是全量表，員工表示非全量表，在做連接條件時，全量表在非全量表的哪端，那么連接時全量表的連接條件就在等號哪斷 */--左連接

select * from dept d,emp e where d.deptno=e.deptno(+)order by e.deptno;--右連接

select * from emp e，dept d where e.deptno(+)=d.deptno order by e.deptno;

-----------------------------作業

--查詢與smith相同部門的員工姓名和雇傭日期 select *from emp t where t.deptno=(select e.deptno from emp e where e.ename='SMITH')and t.ename<> 'SMITH';

--查詢工資比公司平均工資高的員工的員工號，姓名和工資 select t.empno,t.ename,t.sal from emp t where t.sal>(select avg(sal)from emp);

--查詢各部門中工資比本部門平均工資高的員工號，姓名和工資 select t.empno,t.ename,t.sal from emp t,(select avg(e.sal)avgsal,e.deptno from emp e group by e.deptno)a where t.sal>a.avgsal and t.deptno=a.deptno;--查詢姓名中包含字母u的員工在相同部門的員工的員工號和姓名 select t.empno,t.ename from emp t where t.deptno in(select e.deptno from emp e where e.ename like '%U%')and t.empno not in(select e.empno from emp e where e.ename like '%U%');

--查詢管理者是king的員工姓名和工資 select t.ename,t.sal from emp t where t.mgr in(select e.empno from emp e where e.ename='KING');

--------------------------sql1999語法

select *from emp join dept using(deptno)where deptno=20;select *from emp natural join dept;select *from emp e join dept d on e.deptno=d.deptno;select *from dept;select *from dept d left join emp e on d.deptno=e.deptno;select *from dept d,emp e where d.deptno=e.deptno(+);

---分組

select count(empno)from emp group by deptno;select deptno,job,count(*)from emp group by deptno,job order by deptno;select *from EMP for UPDATE;

--group by 后面有的字段，select后才可以有，group by后面沒有的字段，select后面絕對不能有

select d.dname, d.loc, count(e.empno)from emp e, dept d where e.deptno = d.deptno group by d.dname, d.loc;

---------子查詢

select *from emp t where t.sal>(select *from emp e where e.empno=7654);

select rownum ,t.* from emp t where rownum <6;

--pagesize 5 select *from(select rownum rw,a.* from(select *from emp)a where rownum <16)b where b.rw>10;select *from(select *from emp)where rownum>0;--索引

create index person_index on person(p_name);

--視圖

create view view2 as select *from emp t where t.deptno=20;select *from view2;

-------------pl/sql--plsql是對sql語言的過程化擴展-----declare begin dbms_output.put_line('hello world');end;-------declare age number(3);marry boolean := true;--boolean不能直接輸出 pname varchar2(10):= 're jeknc';begin age := 20;dbms_output.put_line(age);if marry then dbms_output.put_line('true');else dbms_output.put_line('false');end if;dbms_output.put_line(pname);end;

--常量和變量

--引用變量,引用表中的字段的類型

Myname emp.ename%type;--使用into來賦值

declare pname emp.ename%type;begin select t.ename into pname from emp t where t.empno=7369;dbms_output.put_line(pname);end;

--記錄型變量

Emprec emp%rowtype;--使用into來賦值

declare Emprec emp%rowtype;begin select t.* into Emprec from emp t where t.empno=7369;dbms_output.put_line(Emprec.empno || ' '||Emprec.ename||' '||Emprec.job);end;

--if分支

語法1：

IF 條件 THEN 語句1;語句2;END IF;語法2：

IF 條件 THEN 語句序列1； ELSE 語句序列 2； END IF； 語法3：

IF 條件 THEN 語句;ELSIF 條件 THEN 語句;ELSE 語句;END IF;--1 declare pname number:=# begin if pname = 1 then dbms_output.put_line('我是1');else dbms_output.put_line('我不是1');end if;end;--2 declare pname number := # begin if pname = 1 then dbms_output.put_line('我是1');elsif pname = 2 then dbms_output.put_line('我是2');else dbms_output.put_line('我不是12');end if;end;

--loop循環語句 語法2： Loop EXIT [when 條件];…… End loop

--1 declare pnum number(4):=0;

begin while pnum < 10 loop dbms_output.put_line(pnum);pnum := pnum + 1;end loop;end;

--2(最常用的循環)declare pnum number(4):=0;begin loop exit when pnum=10;pnum:=pnum+1;dbms_output.put_line(pnum);end loop;end;--3 declare pnum number(4);begin for pnum in 1..10 loop dbms_output.put_line(pnum);end loop;end;

-----游標 語法：

CURSOR 游標名 [(參數名 數據類型,參數名 數據類型,...)] IS SELECT 語句;例如：cursor c1 is select ename from emp;

declare cursor c1 is select * from emp;emprec emp%rowtype;begin open c1;loop fetch c1 into emprec;exit when c1%notfound;dbms_output.put_line(emprec.empno || ' ' || emprec.ename);end loop;close c1;--要記得關閉游標 end;

--------例外

--異常，用來增強程序的健壯性和容錯性--no_data_found(沒有找到數據)--too_many_rows(select …into語句匹配多個行)--zero_pide(被零除)--value_error(算術或轉換錯誤)--timeout_on_resource(在等待資源時發生超時)

--寫出被0除的例外程序 declare pnum number(4):= 10;begin pnum := pnum / 0;exception when zero_pide then dbms_output.put_line('被0除了');when value_error then dbms_output.put_line('算術或轉換錯誤');when others then dbms_output.put_line('其他異常');end;

--自定義異常

--No_data exception;--要拋出raise no_data;

declare cursor c1 is select * from emp t where t.deptno = 20;no_data exception;emprec emp%rowtype;begin open c1;loop fetch c1 into emprec;if c1%notfound then raise no_data;else dbms_output.put_line(emprec.empno || ' ' || emprec.ename);end if;end loop;close c1;

exception when no_data then dbms_output.put_line('無員工');when others then dbms_output.put_line('其他異常');end;

--存儲過程 語法：

create [or replace] PROCEDURE 過程名[(參數名 in/out 數據類型)] AS begin PLSQL子程序體； End;

或者

create [or replace] PROCEDURE 過程名[(參數名 in/out 數據類型)] is begin PLSQL子程序體； End 過程名;

-----創建一個存儲過程helloworld create or replace procedure helloworld is begin dbms_output.put_line('hello world');end helloworld;

------創建一個漲工資的

create or replace procedure addsal(eno in emp.empno%type)is emprec emp%rowtype;begin select * into emprec from emp t where t.empno = eno;

update emp t set t.sal = t.sal + 100 where t.empno = eno;dbms_output.put_line('漲工資前是' || emprec.sal || ',漲工資后是' ||(emprec.sal + 100));end addsal;

-----------------java代碼調用存儲過程和函數--存儲過程--create or replace procedure acc_yealsal(eno in emp.empno%type,yearsal out number)is pcomm emp.comm%type;psal emp.sal%type;begin select t.sal,t.comm into psal,pcomm from emp t where t.empno=eno;yearsal :=psal*12 +nvl(pcomm,0);end;----存儲函數

create or replace function 函數名(Name in type, Name in type,...)return 數據類型 is 結果變量 數據類型;begin

return(結果變量);end函數名;--存儲函數計算年薪

create or replace function accf_yearsal(eno in emp.empno%type)return number is Result number;psal emp.sal%type;pcomm emp.comm%type;begin select t.sal, t.comm into psal, pcomm from emp t where t.empno = eno;Result := psal * 12 + nvl(pcomm, 0);return(Result);end accf_yearsal;

-------觸發器

--觸發語句：增刪改： 語法：

CREATE [or REPLACE] TRIGGER 觸發器名 {BEFORE | AFTER} {DELETE | INSERT | UPDATE [OF 列名]} ON 表名

[FOR EACH ROW [WHEN(條件)] ] begin PLSQL 塊 End 觸發器名

---插入一個新員工則觸發

create or replace trigger insert_person after insert on emp begin dbms_output.put_line('插入新員工');end;select *from emp;insert into emp values(1001,'李四','管理',7902,sysdate,100,100,20);

--raise_application_error(-20001, '不能在非法時間插入員工')

--================================ SQL> @ E:powerDesignerA_腳本user.sql--導入腳本文件

select *from H_USER;

insert into h_user valuer(sequserid.nextval,'a','a',sysdate,'北京',1);

--數據庫建模

--一對多：多的一端是2，箭頭指向的是表1，即少的一端

--在實體類中一的一端的實體類有多的一端的實體類的集合屬性

--使用powerDesiger進行數據庫建模，然后將數據導入，導入到plsql中進行使用

--------------------連接遠程數據庫--方法1，修改localhost的地址 ORCL =(DESCRIPTION =(ADDRESS =(PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl.lan)))--方法2--或者直接在登陸界面在database中輸入遠程數據庫的ip地址和端口號進行遠程登陸 1.create user username identified by password;//建用戶名和密碼oracle ,oracle

2.grant connect,resource,dba to username;//授權 grant connect,resource,dba,sysdba to username;

3.connect username/password//進入。

4.select table_name,column_name from user_tab_columns where table_name='TABLE_NAME';//查詢表中的表名，字段名等等。最后的table_name要大寫。

5.如何執行腳本SQL文件? SQL>@PATH/filename.sql;

7.查詢用戶下的所有表 select distinct table_name from user_tab_columns;===僅顯示一列表名。

8.如何搜索出前N條記錄？

select * from tablename where rownum

9.查找用戶下的所有表：select * from tab;--查詢該用戶下的所有表及視圖（顯示表名tname，類型tabname和clusterid）

2、顯示當前連接用戶

SQL> show user –不能用在sql窗口 只能用在command命令窗口。

3、查看系統擁有哪些用戶

SQL> select * from all_users;

4、新建用戶并授權

SQL> create user a identified by a;(默認建在SYSTEM表空間下)

SQL> grant connect,resource to a;

5、連接到新用戶

SQL> conn a/a –或者是connect a/a

6、查詢當前用戶下所有對象

SQL> select * from tab;--table或是view

7、建立第一個表

SQL> create table a(a number);

8、查詢表結構

SQL> desc a

9、插入新記錄

SQL> insert into a values(1);

10、查詢記錄

SQL> select * from a;

11、更改記錄

SQL> update a set a=2;

12、刪除記錄

SQL> delete from a;

13、回滾

SQL> roll;

SQL> rollback;

14、提交

SQL> commit;

select * from

(select t.*,dense_rank()over(order by cardkind)rank from cardkind t)

where rank = 2;

46.如何在字符串里加回車?

select 'Welcome to visit'||chr(10)||'www.CSDN.NET' from dual;--‘||chr(10)||’作為換行符

53.如何使select語句使查詢結果自動生成序號?

select rownum COL from table;--主要就是oracle中引入了rownum

54.如何知道數據褲中某個表所在的tablespace?

select tablespace_name from user_tables where table_name='TEST';--table_name名稱要大寫。

select * from user_tables中有個字段TABLESPACE_NAME，（oracle）;

select * from dba_segments where …;

55.怎么可以快速做一個和原表一樣的備份表?

create table new_table as(select * from old_table);

59.請問如何修改一張表的主鍵?

alter table aaa drop constraint aaa_key;

alter table aaa add constraint aaa_key primary key(a1,b1);

60.改變數據文件的大小?

用 ALTER DATABASE....DATAFILE....;

手工改變數據文件的大小，對于原來的 數據文件有沒有損害。

61.怎樣查看ORACLE中有哪些程序在運行之中？

查看v$session表

62.怎么可以看到數據庫有多少個tablespace?

select * from dba_tablespaces;

72.怎樣查看哪些用戶擁有SYSDBA、SYSOPER權限？

SQL>conn sys/change_on_install –登不上去

SQL>select * from V_$PWFILE_USERS;76.如何顯示當前連接用戶?

SHOW USER

77.如何查看數據文件放置的路徑 ?

col file_name format a50

SQL> select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id;

79.如何改變一個字段初始定義的Check范圍？

SQL> alter table xxx drop constraint constraint_name;

之后再創建新約束:

SQL> alter table xxx add constraint constraint_name check();

83.如何執行腳本SQL文件? SQL>@所在的文件路徑 /filename.sql;例如放在E盤的根目錄下則應該是 @E:a.sql;回車就OK了。

84.如何快速清空一個大表?

SQL>truncate table table_name;

85.如何查有多少個數據庫實例?

SQL>SELECT * FROM V$INSTANCE;

86.如何查詢數據庫有多少表?

SQL>select * from all_tables;

87.如何測試SQL語句執行所用的時間?

SQL>set timing on;

SQL>select * from tablename;

89.字符串的連接

SELECT CONCAT(COL1,COL2)FROM TABLE;

SELECT COL1||COL2 FROM TABLE;

90.怎么把select出來的結果導到一個文本文件中？

SQL>SPOOL C:ABCD.TXT;

SQL>select * from table;

SQL >spool off;

91.怎樣估算SQL執行的I/O數 ?

SQL>SET AUTOTRACE ON;

SQL>SELECT * FROM TABLE;

OR

SQL>SELECT * FROM v$filestat;

可以查看IO數

92.如何在sqlplus下改變字段大小?

alter table table_name modify(field_name varchar2(100));

改大行，改小不行（除非都是空的）

93.如何查詢某天的數據? select * from a where trunc(日期字段)＝to_date('2003-05-02','yyyy-mm-dd');若是date型數據

insert into bsyear values(to_date('20130427','yyyymmdd'));或者是insert into bsyear values('27-4月-2013');

94.sql 語句如何插入全年日期？

create table BSYEAR(d date);insert into BSYEAR select to_date('20030101','yyyymmdd')+rownum-1 from all_objects where rownum <= to_char(to_date('20031231','yyyymmdd'),'ddd');--在表后直接插入365行數據日期。

緊急插入幾條重要的：

如何在Oracle中復制表結構和表數據 1.復制表結構及其數據：

create table table_name_new as select * from table_name_old 2.只復制表結構：

create table table_name_new as select * from table_name_old where 1=2;或者：

create table table_name_new like table_name_old 3.只復制表數據： 如果兩個表結構一樣：

insert into table_name_new select * from table_name_old 如果兩個表結構不一樣：

insert into table_name_new(column1,column2...)select column1,column2...from table_name_old 創建帶主鍵的表：

create table stuInfo(stuID int primary key,stuName varchar2(20),age int);或是不直接增加主鍵

alter table stuInfo add constraint stuInfo _PK primary key(stuID)

95.如果修改表名?

alter table old_table_name rename to new_table_name;

97.如何知道用戶擁有的權限?

SELECT * FROM dba_sys_privs;--一個權限對應一條數據，這樣對于同一個用戶就有多條數據了。

98.從網上下載的ORACLE9I與市場上賣的標準版有什么區別？

從功能上說沒有區別，只不過oracle公司有明文規定；從網站上下載的oracle產品不得用于商業用途，否則侵權。

101.如何搜索出前N條記錄？

SELECT * FROM empLOYEE WHERE ROWNUM < n ORDER BY empno;Select * from a where rownum

104.如何統計兩個表的記錄總數?

select(select count(id)from aa)+(select count(id)from bb)總數 from dual;--總數那是沒有單引號的，雙引號可以。

106.如何在給現有的日期加上2年？（select add_months(sysdate,24)from dual;--2015/4/27 9:28:52

110.tablespace 是否不能大于4G?

沒有限制.111.返回大于等于N的最小整數值?

SELECT CEIL(N)FROM DUAL;

112.返回小于等于N的最小整數值?

SELECT FLOOR(N)FROM DUAL;

113.返回當前月的最后一天?

SELECT LAST_DAY(SYSDATE)FROM DUAL;

;

115.如何找數據庫表的主鍵字段的名稱?

SQL>SELECT * FROM user_constraints WHERE CONSTRAINT_TYPE='P' and table_name='TABLE_NAME';--我沒有查出來。

116.兩個結果集互加的函數?

SQL>SELECT * FROM BSEMPMS_OLD INTERSECT SELECT * FROM BSEMPMS_NEW;

SQL>SELECT * FROM BSEMPMS_OLD UNION SELECT * FROM BSEMPMS_NEW;

SQL>SELECT * FROM BSEMPMS_OLD UNION ALL SELECT * FROM BSEMPMS_NEW;

117.兩個結果集互減的函數?

SQL>SELECT * FROM BSEMPMS_OLD MINUS SELECT * FROM BSEMPMS_NEW;

139.如何查找重復記錄？

SELECT * FROM TABLE_NAME WHERE ROWID!=(SELECT MAX(ROWID)FROM TABLE_NAMe WHERE TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2);

140.如何刪除重復記錄？

DELETE FROM TABLE_NAME WHERE ROWID!=(SELECT MAX(ROWID)FROM TABLE_NAME D WHERE TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2);

162.如何知道表在表空間中的存儲情況?

select segment_name,sum(bytes),count(*)ext_quan from dba_extents where tablespace_name='&tablespace_name' and segment_type='TABLE' group by tablespace_name,segment_name;--把&tablespace_name改成相應的表空間名稱。

第三篇：oracle常用語句

Oracel 中常使用的語法(部分)

Oracel 中常使用的語法

2、顯示當前連接用戶 ：SQL> show user3、查看系統擁有哪些用戶 ：SQL> select * from all_users;

4、新建用戶并授權：SQL> create user a identified by a;（默認建在SYSTEM表空間下）

SQL> grant connect,resource to a;

5、連接到新用戶 :SQL> conn a/a6、查詢當前用戶下所有對象:SQL> select * from tab;

7、建立第一個表 :SQL> create table a(a number);

8、查詢表結構SQL> desc a9、插入新記錄SQL> insert into a values(1);

10、查詢記錄SQL> select * from a;

11、更改記錄SQL> update a set a=2;

12、刪除記錄SQL> delete from a;

13、回滾SQL> roll;或SQL> rollback;

14、提交SQL> commit;

用戶授權:

GRANT ALTER ANY INDEX TO “user_id ”

GRANT “dba ” TO “user_id ”;

ALTER USER “user_id ” DEFAULT ROLE ALL

創建用戶:

CREATE USER “user_id ” PROFILE “DEFAULT ” IDENTIFIED BY “ DEFAULT TABLESPACE

”USERS “ TEMPORARY TABLESPACE ”TEMP “ ACCOUNT UNLOCK;GRANT ”CONNECT “ TO ”user_id “;

用戶密碼設定:

ALTER USER ”CMSDB “ IDENTIFIED BY ”pass_word “

表空間創建:

CREATE TABLESPACE ”table_space “ LOGGING DATAFILE

'C:ORACLEORADATAdbstable_space.ora' SIZE 5M

----------

1、查看當前所有對象:SQL > select * from tab;

2、建一個和a表結構一樣的空表

SQL > create table b as select * from a where 1=2;

SQL > create table b(b1,b2,b3)as select a1,a2,a3 from a where 1=2;

3、察看數據庫的大小，和空間使用情況

SQL > col tablespace format a20

SQL > select b.file_id文件ID,b.tablespace_name表空間,b.file_name物理文件名,b.bytes總字節數,(b.bytes-sum(nvl(a.bytes,0)))已使用,sum(nvl(a.bytes,0))剩余,sum(nvl(a.bytes,0))/(b.bytes)*100 剩余百分比

from dba_free_space a,dba_data_files b

where a.file_id=b.file_id group by b.tablespace_name,b.file_name,b.file_id,b.bytes order by b.tablespace_name /

dba_free_space--表空間剩余空間狀況

dba_data_files--數據文件空間占用情況

4、查看現有回滾段及其狀態 ： SQL > col segment format a30

SQL > SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS FROM DBA_ROLLBACK_SEGS;

5、查看數據文件放置的路徑 ：SQL > col file_name format a50

SQL > select tablespace_name,file_id,bytes/1024/1024,file_name fromdba_data_files order by file_id;

6、顯示當前連接用戶 :SQL > show user7、把SQL*Plus當計算器 :SQL > select 100*20 from dual;

8、連接字符串：SQL > select 列1 | |列2 from 表1;

SQL > select concat(列1,列2)from 表1;

9、查詢當前日期和時間： SQL > select to_char(sysdate,'yyyy-mm-dd,hh24:mi:ss')from dual;

10、用戶間復制數據：SQL > copy from user1 to user2 create table2 using select * from table1;

11、視圖中不能使用order by，但可用group by代替來達到排序目的SQL > create view a as select b1,b2 from b group by b1,b2;

12、通過授權的方式來創建用戶 ：SQL > grant connect,resource to test identified by test;

SQL > conn test/test13、查出當前用戶所有表名： select unique tname from col;

---------

/* 向一個表格添加字段 */alter table alist_table add address varchar2(100);

/* 修改字段 屬性 字段為空 */alter table alist_table modify address varchar2(80);

/* 修改字段名字 */

create table alist_table_copy as select ID,NAME,PHONE,EMAIL, QQ as QQ2,/*qq 改為qq2*/

ADDRESS from alist_table;drop table alist_table;

rename alist_table_copy to alist_table /* 修改表名 */

空值處理，有時要求列值不能為空 ：create table dept(deptno number(2)not null, dname char(14), loc char(13));

在基表中增加一列： alter table dept add(headcnt number(3));

修改已有列屬性 ：alter table dept modify dname char(20);

注：只有當某列所有值都為空時，才能減小其列值寬度。只有當某列所有值都為

空時，才能改變其列值類型。

只有當某列所有值都為不空時，才能定義該列為not null。

例：alter table dept modify(loc char(12));alter table dept modify loc char(12);

alter table dept modify(dname char(13),loc char(12));

查找未斷連接 :

select process,osuser,username,machine,logon_time ,sql_text from v$session a,v$sqltext b where a.sql_address=b.address;

---

1.以USER_開始的數據字典視圖包含當前用戶所擁有的信息, 查詢當前用戶所擁有的表信息:

select * from user_tables;

2.以ALL_開始的數據字典視圖包含ORACLE用戶所擁有的信息, 查詢用戶擁有或有權訪問的所有表信息:

select * from all_tables;

3.以DBA_開始的視圖一般只有ORACLE數據庫管理員可以訪問:select * from dba_tables;

4.查詢ORACLE用戶：

conn sys/change_on_installselect * from dba_users;

conn system/manager;select * from all_users;

5.創建數據庫用戶：CREATE USER user_name IDENTIFIED BY password;GRANT CONNECT TO user_name;GRANT RESOURCE TO user_name;

授權的格式: grant(權限)on tablename to username;

刪除用戶(或表):drop user(table)username(tablename)(cascade);

6.向建好的用戶導入數據表 :

IMP SYSTEM/MANAGER FROMUSER = FUSER_NAME TOUSER = USER_NAME FILE = C:EXPDAT.DMPCOMMIT = Y

7.索引： create index [index_name] on [table_name](”column_name ")

第四篇：oracle學習心得

一、SQL SERVER的理解

SQL SERVER服務器就像一棟大樓，大樓里的機房就像服務器的數據庫，機房里的電腦如同數據庫里的表

1、登錄用戶可以登錄服務器——可以進大樓

2、登錄用戶成為數據庫用戶才能進指定的數據庫——進入大樓的人給了某個機房的鑰匙

才能進入機房

3、登錄用戶有權限使用表——進入機房的人有電腦的密碼才能使用電腦

二、ORACLE的理解

ORACLE服務器（全局數據庫）就像一個商場，商場的每一家公司是表空間，公司的業務是表

1、數據庫由多個表空間組成——商場里有多家公司組成2、表空間由段組成——公司要有自己的經營業務，可以只有一個業務，就是一個表空

間中只有一個段，可以有多個業務，就是一個表空間有多個段

3、段由區組成——單個業務的細分類別。例如有家公司經營三個業務，賣書，賣家電，賣衣服，則每個業務就是一個段。而每個業務又有細分，比如賣書的話要進行分類了。計算機區，人文區，小說區等，每一區都要放上書架存放書籍，則書架就是oracle塊，存放數據的三、數據庫，表空間，用戶（指定默認表空間），表統統由管理員管理

四、在OEM中管理數據庫的步驟

1、創建

1)存儲——表空間——創建表空間(TOMSPACE)（類似于在SQL中創建數據庫，通常可以省略，使用默認表空間為USERS，臨時表空間為TEMP）

2)首先展開安全性——創建新用戶（TOM，指定表空間）（類似于在SQL中指定

數據庫用戶）

3)創建表——指定方案（用戶）和表空間（列名不要帶<>）

4)設置約束

5)輸入信息：方案——用戶名——表——右擊——查看 /編輯目錄…

2、修改

1）方案——用戶名——表

2）修改表結構，添加約束

3、刪除： 右擊表——移去

五、注意事項

1、剛創建的用戶不會出現在方案中，只有創建了一個表指定方案和表空間，該用戶的方案名就會出現在方案中，此時就可以方便為該方案創建其他表

1）新創建的用戶能在SQLPLUS中登錄，為什么不能在企業管理器中登錄呢？

解答：需要授予 SELECT ANY DIRECTORY權限才能正常登錄企業管理器，但沒有其他權限

2）可以對創建的用戶在安全性中賦予角色權限，如DBA,則該用戶就是數據庫管理

員

3）SYS用戶主要用來維護系統信息和管理實例，只能以SYSOPER或SYSDBA角

色登錄

4）安裝oracle的用戶自動為ora-dba權限，自動是sys用戶，所以在登錄時不需要

用戶名和密碼，只要選中以sysdba登錄。其他用戶登錄計算機系統，在使用oracle

時就是正常登錄狀態了。

2、向表中添加日期數據：時間格式為DD-MM-YYYY怎么修改呢？如何插入時間數

據

1）先在SQLPLUS中SELECT SYSDATE FROM DUAL;就知道日期的格式

2）alter session set NLS_DATE_FORMAT='YYYY-MM-DD';修改當前輸入格式

3）oracle中的默認格式是：‘dd-mm-yyyy’ ；修改語句：alter session set

nls_date_format='yyyy-mm-dd';修改后輸入和顯示都為指定格式，缺點是只對當前會話起作用

假如你是要永久修改的話，改注冊表：

regedit->hkey_local_machine->oracle->homeo->

NLS_DATE_FORMAT值YYYY-MM-DD（經驗證不管用）

默認日期：50年之前是當前世紀，50年之后是之前世紀，31-12月-49代表2049年01-1月-50 代表1950年

4）由于在表編輯器中to_date函數的格式是dd-mon-yyyy，輸入時為如下格式13-6

月-1983（代碼編輯時一樣，世紀可以省略，代表當前世紀）

5）to_char通常用于查詢語句； to_date通常用于增刪改語句

6）兩個’代表一個’

六、代碼編輯器

1、SQL PLUS2、SQL PLUS WORKSHEET3、isqlplus

設置列寬：set linesize 50;

七、與SQL2000中的查詢分析器不同，SQL plus worksheet執行所有語句，不單單執行選中語句

八、編輯——清除；可以清除查詢結果

九、查看環境變量：select * from v$nls_parameters;

十、示例用戶

1)scott/tigernormal登錄

2）hr : 需要解鎖和重設密碼（alter user hr account unlock;）

十一、查看和更改oracle http占用端口號，D:ora9ApacheApacheconf下，httpd.conf和httpd.conf.default中將80修改，然后重新啟動HTTP服務 加個字符串

六、工具的使用

1、net manager: 配置服務主機名和監聽主機名

2、net configuration assistant:配置監聽程序，本地服務名配置等

/*****************************************************************************/ oracle小知識點

1、數據操作時，字段區分大小寫

2、Oracle兩種認證模式：（1）操作系統認證（2）文件口令認證。

初始化參數的文件目錄為D:oracle_programdatabase3、sqlplus中使用 help index查看幫助信息目錄，? 命令：查看某個命令的詳細用法

連接數據庫后才能使用

5、當代碼中使用函數時，函數中固定的關鍵字必須寫正確，當放在字符串中的時候，編譯沒有錯誤，運行時才會出現錯誤，通常是SQL遞歸錯誤

第五篇：ORACLE講稿

一.ORACLE管理器和目錄介紹

二、如何配置一個連接和偵聽（連接配置文件、偵聽文件位置）

三、數據庫安裝需注意的參數

字符集 內存 會話數 PFILE，SPFILE 自己在客戶端配置到數據庫服務器的連接

四、PLD工具介紹

1、右鍵菜單

2、Copy to Excel

3、Select for Update

4、執行單條SQL語句

5、查看執行計劃

6、調試存儲過程

五、常用DBA視圖 Dba_object, dba_data_file, dba_tables, v$sqltext, v$session V$lock, dba_tab_columns, dba_indexes

六、物化視圖原理及數據分發常規處理 物化視圖刷新時間 生產機和查詢機

七、作業調度

八、DBLINK

CTAIS體系結構 表（Table）命名規則（1）表名

表名前綴根據表所操作的業務及功能進行分類，使用具體業務名稱的漢語拼音，長度為兩個字符。標題使用規范的漢語拼音縮寫，標題內容以詞組或短語為單位，長度不超過10個字符，詞組與詞組之間使用‘_’相連接。例如，申報征收模塊中小規模納稅人申報增值稅表名為：SB_ZZS_XGMNSR。（2）表名后綴

主表與子表是一對多的關系，子表名是在主表名后加后綴：_ZB 例：DJ_BG_ZB 申請表加后綴：_SQ 例：WS_HDZS_SQ 審批表加后綴：_SP 例：WS_HDZS_SP 擴展表加后綴：_KZ 例：DJ_NSRXX_KZ 附表加后綴：_FB 例：SB_ZZS_2003_FB SB_ZZS_2003_FB1 SB_ZZS_2003_FB2

表（Table）命名規則（3）列名

列名由長度不超過30個英文字符和數字的組合，使用規范的漢語拼音縮寫。命名規則如下：

凡是參照代碼表的列，列名一律加‘_DM’后綴；

例：NSR_SWJG_DM、HY_DM “XX標志”列，當其取值為“真/假”時，數據類型用Char(1)，列名加‘BZ’或‘_BZ’后綴。例：NSR_SWJG_BZ CTAIS表的類型 BB_報表 CX_查詢 DJ_登記 DM_代碼 FP_發票 HD_核定 JC_稽查 KJ_會計 PI_批處理 PZ_票證 QX_權限 RD_認定 SB_申報

WF_維護、工作流 WS_文書 WZ_違章 XT_系統 ZD_字典 ZJ_證件 ZX_執行

ctais部分表

DJ_NSRXX,DJ_NSRXX_KZ,DJ_SZ,DJ_SZ_ZB,DJ_SZ_KZ,DJ_YZCWSBQC RD_NSRZG_LSXX,HD_DSQC_LS WS_DJXX,WS_JMS_SQ,WS_JMS_SP FP_KC,FP_YJ,FP_NSR_JC SB_SBXX,SB_ZZS_2003_YBNSR,SB_ZZS_2003_FB1 SB_ZSXX,SB_JKS,SB_WSZ JC_AJXX,JC_CLJDS,WZ_WFWZXX,WZ_CLCFXX, ZX_ZXXX QX_USER,QX_GNMK,QX_GNMB XT_XTCS,XT_DYCS

如何自己搭建本地的測試庫

1． 安裝ORACLE服務器版，并修改字符集 2． 創建CTAIS實例 3． 創建表空間 4． 創建CTAIS2用戶 5． 執行數據庫安裝腳本 6． 停止觸發器和外鍵

7． 倒入數據（因表已經存在，IMP時需要忽略創建錯誤IGNORE=Y）8． 編譯失效對象