oracle10g教程從入門到精通

1、問題：能否按照中國(guó)人習(xí)慣的方式年月日添加日期。 系統(tǒng)函數(shù) sys_context 1）terminal：當(dāng)前會(huì)話客戶所對(duì)應(yīng)的終端的標(biāo)示符 2）lanuage: 語言 3）db_name： 當(dāng)前數(shù)據(jù)庫(kù)名稱 4）nls_date_format： 當(dāng)前會(huì)話客戶所對(duì)應(yīng)的日期格式 5）session_user： 當(dāng)前會(huì)話客戶所對(duì)應(yīng)的數(shù)據(jù)庫(kù)用戶名 6）current_schema： 當(dāng)前會(huì)話客戶所對(duì)應(yīng)的默認(rèn)方案名 7）host： 返回?cái)?shù)據(jù)庫(kù)所在主機(jī)的名稱 通過該函數(shù)，可以查詢一些重要信息，比如你正在使用哪個(gè)數(shù)據(jù)庫(kù)？ select sys_context(USERENV,db_name) from dual

2、; 注意：USERENV是固定的，不能改的，db_name可以換成其它,比如select sys_context(USERENV,lanuage) from dual;又比如select sys_context(USERENV,current_schema) from dual; 十：數(shù)據(jù)庫(kù)管理，表的邏輯備份與恢復(fù)數(shù)據(jù)庫(kù)管理員 介紹 每個(gè)oracle數(shù)據(jù)庫(kù)應(yīng)該至少有一個(gè)數(shù)據(jù)庫(kù)管理員（dba），對(duì)于一個(gè)小的數(shù)據(jù)庫(kù)，一個(gè)dba就夠了，但是對(duì)于一個(gè)大的數(shù)據(jù)庫(kù)可能需要多個(gè)dba分擔(dān)不同的管理職責(zé)。那么一個(gè)數(shù)據(jù)庫(kù)管理員的主要工作是什么呢： 職責(zé) 1.安裝和升級(jí)oracle數(shù)據(jù)庫(kù) 2.建庫(kù)，表空間，表，視

3、圖，索引 3.制定并實(shí)施備份和恢復(fù)計(jì)劃 4.數(shù)據(jù)庫(kù)權(quán)限管理，調(diào)優(yōu)，故障排除 5.對(duì)于高級(jí)dba，要求能參與項(xiàng)目開發(fā)，會(huì)編寫sql語句、存儲(chǔ)過程、觸發(fā)器、規(guī)則、約束、包 管理數(shù)據(jù)庫(kù)的用戶主要是sys和system （sys好像是董事長(zhǎng)，system好像是總經(jīng)理，董事長(zhǎng)比總經(jīng)理大，但是通常是總經(jīng)理干事） 在前面我們已經(jīng)提到這兩個(gè)用戶，區(qū)別主要是： 1.最重要的區(qū)別，存儲(chǔ)的數(shù)據(jù)的重要性不同 sys：所有oracle的數(shù)據(jù)字典的基表和視圖都存放在sys用戶中，這些基表和視圖對(duì)于oracle的運(yùn)行是至關(guān)重要的，由數(shù)據(jù)庫(kù)自己維護(hù)，任何用戶都不能手動(dòng)更改。sys用戶擁有dba，sysdba，sysoper角

4、色或權(quán)限，是oracle權(quán)限最高的用戶。 system：用于存放次一級(jí)的內(nèi)部數(shù)據(jù)，如oracle的一些特性或工具的管理信息。system用戶擁有dba，sysdba角色或系統(tǒng)權(quán)限。 看圖： sysdba可以建數(shù)據(jù)庫(kù)，sysoper不能建數(shù)據(jù)庫(kù) 2. 其次的區(qū)別，權(quán)限的不同。 sys用戶必須以as sysdba或as sysoper形式登錄。不能以normal方式登錄數(shù)據(jù)庫(kù) system如果正常登錄，它其實(shí)就是一個(gè)普通的dba用戶，但是如果以as sysdba登錄，其結(jié)果實(shí)際上它是作為sys用戶登錄的，從登錄信息里面我們可以看出來。 sysdba和sysoper權(quán)限區(qū)別圖，看圖： sysdbas

5、ysoperdba 可以看到：只要是sysoper擁有的權(quán)限，sysdba都有；藍(lán)色是它們區(qū)別的地方。（它們的最大區(qū)別是：sysdba可以創(chuàng)建數(shù)據(jù)庫(kù)，sysoper不可以創(chuàng)建數(shù)據(jù)庫(kù)） dba權(quán)限的用戶 dba用戶是指具有dba角色的數(shù)據(jù)庫(kù)用戶。特權(quán)用戶可以執(zhí)行啟動(dòng)實(shí)例，關(guān)閉實(shí)例等特殊操作，而dba用戶只有在啟動(dòng)數(shù)據(jù)庫(kù)后才能執(zhí)行各種管理工作。 （相當(dāng)于說dba連startup和shutdown這兩個(gè)權(quán)限都沒有） 兩個(gè)主要的用戶，三個(gè)重要權(quán)限，他們的區(qū)別和聯(lián)系，大家要弄清楚 管理初始化參數(shù) 管理初始化參數(shù)（調(diào)優(yōu)的一個(gè)重要知識(shí)點(diǎn)，憑什么可以對(duì)數(shù)據(jù)庫(kù)進(jìn)行調(diào)優(yōu)呢？是因?yàn)樗梢詫?duì)數(shù)據(jù)庫(kù)的一些參數(shù)進(jìn)行修改修

6、正） 初始化參數(shù)用于設(shè)置實(shí)例或是數(shù)據(jù)庫(kù)的特征。oracle9i提供了200多個(gè)初始化參數(shù)，并且每個(gè)初始化參數(shù)都有默認(rèn)值。 顯示初始化參數(shù)l (1) show parameter命令 如何修改參數(shù)l 需要說明的如果你希望修改這些初始化的參數(shù)，可以到文件D:oracleadminmyoralpfileinit.ora文件中去修改比如要修改實(shí)例的名字 數(shù)據(jù)庫(kù)（表）的邏輯備份與恢復(fù) 介紹 介紹 邏輯備份是指使用工具export將數(shù)據(jù)對(duì)象的結(jié)構(gòu)和數(shù)據(jù)導(dǎo)出到文件的過程，邏輯恢復(fù)是指當(dāng)數(shù)據(jù)庫(kù)對(duì)象被誤操作而損壞后使用工具import利用備份的文件把數(shù)據(jù)對(duì)象導(dǎo)入到數(shù)據(jù)庫(kù)的過程。 物理備份即可在數(shù)據(jù)庫(kù)open的狀

7、態(tài)下進(jìn)行也可在關(guān)閉數(shù)據(jù)庫(kù)后進(jìn)行，但是邏輯備份和恢復(fù)只能在open的狀態(tài)下進(jìn)行。 看圖： 導(dǎo)出 導(dǎo)出具體的分為：導(dǎo)出表，導(dǎo)出方案，導(dǎo)出數(shù)據(jù)庫(kù)三種方式。 導(dǎo)出使用exp命令來完成的，該命令常用的選項(xiàng)有： userid： 用于指定執(zhí)行導(dǎo)出操作的用戶名，口令，連接字符串 tables： 用于指定執(zhí)行導(dǎo)出操作的表 owner： 用于指定執(zhí)行導(dǎo)出操作的方案 full=y： 用于指定執(zhí)行導(dǎo)出操作的數(shù)據(jù)庫(kù) inctype： 用于指定執(zhí)行導(dǎo)出操作的增量類型 rows： 用于指定執(zhí)行導(dǎo)出操作是否要導(dǎo)出表中的數(shù)據(jù) file： 用于指定導(dǎo)出文件名 導(dǎo)出表 1.導(dǎo)出自己的表 exp userid=scott/tiger

8、myoral tables=(emp,dept) file=d:e1.dmp 2.導(dǎo)出其它方案的表 如果用戶要導(dǎo)出其它方案的表，則需要dba的權(quán)限或是exp_full_database的權(quán)限，比如system就可以導(dǎo)出scott的表 E:oracleora92binexp userid=system/managermyoral tables=(scott.emp) file=d:e2.emp 特別說明：在導(dǎo)入和導(dǎo)出的時(shí)候，要到oracle目錄的bin目錄下。 3. 導(dǎo)出表的結(jié)構(gòu) exp userid=scott/tigeraccp tables=(emp) file=d:e3.dmp rows

9、=n 4. 使用直接導(dǎo)出方式 exp userid=scott/tigeraccp tables=(emp) file=d:e4.dmp direct=y 這種方式比默認(rèn)的常規(guī)方式速度要快，當(dāng)數(shù)據(jù)量大時(shí)，可以考慮使用這樣的方法。 這時(shí)需要數(shù)據(jù)庫(kù)的字符集要與客戶端字符集完全一致，否則會(huì)報(bào)錯(cuò). 導(dǎo)出方案 導(dǎo)出方案是指使用export工具導(dǎo)出一個(gè)方案或是多個(gè)方案中的所有對(duì)象（表，索引，約束.）和數(shù)據(jù)。并存放到文件中。 1. 導(dǎo)出自己的方案 exp userid=scott/tigermyorcl owner=scott file=d:scott.dmp 2. 導(dǎo)出其它方案 如果用戶要導(dǎo)出其它方案，則

10、需要dba的權(quán)限或是exp_full_database的權(quán)限，比如system用戶就可以導(dǎo)出任何方案 exp userid=system/managermyorcl owner=(system,scott) file=d:system.dmp 導(dǎo)出數(shù)據(jù)庫(kù) 導(dǎo)出數(shù)據(jù)庫(kù)是指利用export導(dǎo)出所有數(shù)據(jù)庫(kù)中的對(duì)象及數(shù)據(jù)，要求該用戶具有dba的權(quán)限或者是exp_full_database權(quán)限 增量備份（好處是第一次備份后，第二次備份就快很多了） exp userid=system/managermyorcl full=y inctype=complete file=d:all.dmp 導(dǎo)入 介紹 導(dǎo)入

11、就是使用工具import將文件中的對(duì)象和數(shù)據(jù)導(dǎo)入到數(shù)據(jù)庫(kù)中，但是導(dǎo)入要使用的文件必須是export所導(dǎo)出的文件。與導(dǎo)出相似，導(dǎo)入也分為導(dǎo)入表，導(dǎo)入方案，導(dǎo)入數(shù)據(jù)庫(kù)三種方式。 imp常用的選項(xiàng)有 userid： 用于指定執(zhí)行導(dǎo)入操作的用戶名，口令，連接字符串 tables： 用于指定執(zhí)行導(dǎo)入操作的表 formuser： 用于指定源用戶 touser： 用于指定目標(biāo)用戶 file： 用于指定導(dǎo)入文件名 full=y： 用于指定執(zhí)行導(dǎo)入整個(gè)文件 inctype： 用于指定執(zhí)行導(dǎo)入操作的增量類型 rows： 指定是否要導(dǎo)入表行（數(shù)據(jù)） ignore： 如果表存在，則只導(dǎo)入數(shù)據(jù) 導(dǎo)入表 1. 導(dǎo)入自己的

12、表 imp userid=scott/tigermyorcl tables=(emp) file=d:xx.dmp 2. 導(dǎo)入表到其它用戶 要求該用戶具有dba的權(quán)限，或是imp_full_database imp userid=system/tigermyorcl tables=(emp) file=d:xx.dmp touser=scott 3. 導(dǎo)入表的結(jié)構(gòu) 只導(dǎo)入表的結(jié)構(gòu)而不導(dǎo)入數(shù)據(jù) imp userid=scott/tigermyorcl tables=(emp) file=d:xx.dmp rows=n 4. 導(dǎo)入數(shù)據(jù) 如果對(duì)象（如比表）已經(jīng)存在可以只導(dǎo)入表的數(shù)據(jù) imp user

13、id=scott/tigermyorcl tables=(emp) file=d:xx.dmp ignore=y 導(dǎo)入方案 導(dǎo)入方案是指使用import工具將文件中的對(duì)象和數(shù)據(jù)導(dǎo)入到一個(gè)或是多個(gè)方案中。如果要導(dǎo)入其它方案，要求該用戶具有dba的權(quán)限，或者imp_full_database 1 導(dǎo)入自身的方案 imp userid=scott/tiger file=d:xxx.dmp 2 導(dǎo)入其它方案 要求該用戶具有dba的權(quán)限 imp userid=system/manager file=d:xxx.dmp fromuser=system touser=scott 導(dǎo)入數(shù)據(jù)庫(kù) 在默認(rèn)情況下，當(dāng)

14、導(dǎo)入數(shù)據(jù)庫(kù)時(shí)，會(huì)導(dǎo)入所有對(duì)象結(jié)構(gòu)和數(shù)據(jù)，案例如下： imp userid=system/manager full=y file=d:xxx.dmp十一：數(shù)據(jù)字典和動(dòng)態(tài)性能視圖介紹 是什么 數(shù)據(jù)字典是oracle數(shù)據(jù)庫(kù)中最重要的組成部分，它提供了數(shù)據(jù)庫(kù)的一些系統(tǒng)信息。 動(dòng)態(tài)性能視圖記載了例程啟動(dòng)后的相關(guān)信息。 數(shù)據(jù)字典 數(shù)據(jù)字典記錄了數(shù)據(jù)庫(kù)的系統(tǒng)信息，它是只讀表和視圖的集合，數(shù)據(jù)字典的所有者為sys用戶。 用戶只能在數(shù)據(jù)字典上執(zhí)行查詢操作（select語句），而其維護(hù)和修改是由系統(tǒng)自動(dòng)完成的。 這里我們談?wù)剶?shù)據(jù)字典的組成：數(shù)據(jù)字典包括數(shù)據(jù)字典基表和數(shù)據(jù)字典視圖，其中基表存儲(chǔ)數(shù)據(jù)庫(kù)的基本信息，普通

15、用戶不能直接訪問數(shù)據(jù)字典的基表。數(shù)據(jù)字典視圖是基于數(shù)據(jù)字典基表所建立的視圖，普通用戶可以通過查詢數(shù)據(jù)字典視圖取得系統(tǒng)信息。數(shù)據(jù)字典視圖主要包括user_xxx，all_xxx，dba_xxx三種類型。 user_tables； 用于顯示當(dāng)前用戶所擁有的所有表，它只返回用戶所對(duì)應(yīng)方案的所有表 比如：select table_name from user_tables; all_tables； 用于顯示當(dāng)前用戶可以訪問的所有表，它不僅會(huì)返回當(dāng)前用戶方案的所有表，還會(huì)返回當(dāng)前用戶可以訪問的其它方案的表： 比如：select table_name from all_tables; dba_tables

16、； 它會(huì)顯示所有方案擁有的數(shù)據(jù)庫(kù)表。但是查詢這種數(shù)據(jù)庫(kù)字典視圖，要求用戶必須是dba角色或是有select any table系統(tǒng)權(quán)限。 例如：當(dāng)用system用戶查詢數(shù)據(jù)字典視圖dba_tables時(shí)，會(huì)返回system，sys，scott.方案所對(duì)應(yīng)的數(shù)據(jù)庫(kù)表。 用戶名，權(quán)限，角色 在建立用戶時(shí)，oracle會(huì)把用戶的信息存放到數(shù)據(jù)字典中，當(dāng)給用戶授予權(quán)限或是角色時(shí)，oracle會(huì)將權(quán)限和角色的信息存放到數(shù)據(jù)字典。 通過查詢dba_users可以顯示所有數(shù)據(jù)庫(kù)用戶的詳細(xì)信息； 通過查詢數(shù)據(jù)字典視圖dba_sys_privs，可以顯示用戶所具有的系統(tǒng)權(quán)限； 通過查詢數(shù)據(jù)字典視圖dba_tab

17、_privs，可以顯示用戶具有的對(duì)象權(quán)限； 通過查詢數(shù)據(jù)字典dba_col_privs可以顯示用戶具有的列權(quán)限； 通過查詢數(shù)據(jù)庫(kù)字典視圖dba_role_privs可以顯示用戶所具有的角色。 這里給大家再講講角色和權(quán)限的關(guān)系。 例如：要查看scott具有的角色，可查詢dba_role_privs； SQL select * from dba_role_privs where grantee=SCOTT; /查詢orale中所有的系統(tǒng)權(quán)限，一般是dba select * from system_privilege_map order by name; /查詢oracle中所有對(duì)象權(quán)限，一般是db

18、a select distinct privilege from dba_tab_privs; /查詢oracle中所有的角色，一般是dba select * from dba_roles; /查詢數(shù)據(jù)庫(kù)的表空間 select tablespace_name from dba_tablespaces; 問題1：如何查詢一個(gè)角色包括的權(quán)限？ a.一個(gè)角色包含的系統(tǒng)權(quán)限 select * from dba_sys_privs where grantee=角色名 另外也可以這樣查看： select * from role_sys_privs where role=角色名 b.一個(gè)角色包含的對(duì)象權(quán)限

19、select * from dba_tab_privs where grantee=角色名 問題2：oracle究竟有多少種角色？ SQL select * from dba_roles; 問題3：如何查看某個(gè)用戶，具有什么樣的角色？ select * from dba_role_privs where grantee=用戶名 顯示當(dāng)前用戶可以訪問的所有數(shù)據(jù)字典視圖。 select * from dict where comments like %grant%; 顯示當(dāng)前數(shù)據(jù)庫(kù)的全稱 select * from global_name; 其它說明 數(shù)據(jù)字典記錄有oracle數(shù)據(jù)庫(kù)的所有系統(tǒng)信息

20、。通過查詢數(shù)據(jù)字典可以取得以下系統(tǒng)信息：比如 1.對(duì)象定義情況 2.對(duì)象占用空間大小 3.列信息 4.約束信息 . 但是因?yàn)檫@些個(gè)信息，可以通過pl/sql developer工具查詢得到，所以這里我就飄過。 動(dòng)態(tài)性能視圖 動(dòng)態(tài)性能視圖用于記錄當(dāng)前例程的活動(dòng)信息，當(dāng)啟動(dòng)oracle server時(shí)，系統(tǒng)會(huì)建立動(dòng)態(tài)性能視圖；當(dāng)停止oracle server時(shí)，系統(tǒng)會(huì)刪除動(dòng)態(tài)性能視圖。oracle的所有動(dòng)態(tài)性能視圖都是以v_$開始的，并且oracle為每個(gè)動(dòng)態(tài)性能視圖都提供了相應(yīng)的同義詞，并且其同義詞是以V$開始的，例如v_$datafile的同義詞為v$datafile;動(dòng)態(tài)性能視圖的所有者為s

21、ys，一般情況下，由dba或是特權(quán)用戶來查詢動(dòng)態(tài)性能視圖。 因?yàn)檫@個(gè)在實(shí)際中用的較少，所以飛過。 十二：數(shù)據(jù)庫(kù)管理 - 管理表空間和數(shù)據(jù)文件介紹 表空間是數(shù)據(jù)庫(kù)的邏輯組成部分。從物理上講，數(shù)據(jù)庫(kù)數(shù)據(jù)存放在數(shù)據(jù)文件中；從邏輯上講，數(shù)據(jù)庫(kù)則是存放在表空間中，表空間由一個(gè)或多個(gè)數(shù)據(jù)文件組成。 數(shù)據(jù)庫(kù)的邏輯結(jié)構(gòu) 介紹 oracle中邏輯結(jié)構(gòu)包括表空間、段、區(qū)和塊。 說明一下數(shù)據(jù)庫(kù)由表空間構(gòu)成，而表空間又是由段構(gòu)成，而段又是由區(qū)構(gòu)成，而區(qū)又是由oracle塊構(gòu)成的這樣的一種結(jié)構(gòu)，可以提高數(shù)據(jù)庫(kù)的效率。 為了讓大家明白，我們畫圖說明邏輯關(guān)系：看圖： 表空間 介紹 表空間用于從邏輯上組織數(shù)據(jù)庫(kù)的數(shù)據(jù)。數(shù)據(jù)庫(kù)

22、邏輯上是由一個(gè)或是多個(gè)表空間組成的。通過表空間可以達(dá)到以下作用： 1. 控制數(shù)據(jù)庫(kù)占用的磁盤空間 2. dba可以將不同數(shù)據(jù)類型部署到不同的位置，這樣有利于提高i/o性能，同時(shí)利于備份和恢復(fù)等管理操作。 建立表空間 建立表空間是使用crate tablespace命令完成的，需要注意的是，一般情況下，建立表空間是特權(quán)用戶或是dba來執(zhí)行的，如果用其它用戶來創(chuàng)建表空間，則用戶必須要具有create tablespace的系統(tǒng)權(quán)限。 建立數(shù)據(jù)表空間 在建立數(shù)據(jù)庫(kù)后，為便于管理表，最好建立自己的表空間 create tablespace data01 datafile d:testdada01.db

23、f size 20m uniform size 128k; 說明：執(zhí)行完上述命令后，會(huì)建立名稱為data01的表空間，并為該表空間建立名稱為data01.dbf的數(shù)據(jù)文件，區(qū)的大小為128k 使用數(shù)據(jù)表空間 create table mypart(deptno number(4), dname varchar2(14), loc varchar2(13) tablespace data01; 改變表空間的狀態(tài) 當(dāng)建立表空間時(shí)，表空間處于聯(lián)機(jī)的（online）狀態(tài)，此時(shí)該表空間是可以訪問的，并且該表空間是可以讀寫的，即可以查詢?cè)摫砜臻g的數(shù)據(jù)，而且還可以在表空間執(zhí)行各種語句。但是在進(jìn)行系統(tǒng)維護(hù)或是

24、數(shù)據(jù)維護(hù)時(shí)，可能需要改變表空間的狀態(tài)。一般情況下，由特權(quán)用戶或是dba來操作。 1. 使表空間脫機(jī) alter tablespace 表空間名 offline; 2. 使表空間聯(lián)機(jī) alter tablespace 表空間名 online; 3. 只讀表空間 當(dāng)建立表空間時(shí)，表空間可以讀寫，如果不希望在該表空間上執(zhí)行update，delete，insert操作，那么可以將表空間修改為只讀 alter tablespace 表空間名 read only; (修改為可寫是 alter tablespace 表空間名 read write;) 改變表空間的狀態(tài) 我們給大家舉一個(gè)實(shí)例，說明只讀特性：

25、1. 知道表空間名，顯示該表空間包括的所有表 select * from all_tables where tablespace_name=表空間名; 2. 知道表名，查看該表屬于那個(gè)表空間 select tablespace_name, table_name from user_tables where table_name=emp; 通過2.我們可以知道scott.emp是在system這個(gè)表空間上，現(xiàn)在我們可以將system改為只讀的但是我們不會(huì)成功，因?yàn)閟ystem是系統(tǒng)表空間，如果是普通表空間，那么我們就可以將其設(shè)為只讀的，給大家做一個(gè)演示，可以加強(qiáng)理解。 3. 4. 使表空間可讀寫

26、 alter tablespace 表空間名 read write; 刪除表空間 一般情況下，由特權(quán)用戶或是dba來操作，如果是其它用戶操作，那么要求用戶具有drop tablespace系統(tǒng)權(quán)限。 drop tablespace 表空間 including contents and datafiles; 說明：including contents表示刪除表空間時(shí)，刪除該空間的所有數(shù)據(jù)庫(kù)對(duì)象，而datafiles表示將數(shù)據(jù)庫(kù)文件也刪除。 擴(kuò)展表空間 表空間是由數(shù)據(jù)文件組成的，表空間的大小實(shí)際上就是數(shù)據(jù)文件相加后的大小。那么我們可以想象，假定表employee存放到data01表空間上，初始大小

27、就是2M，當(dāng)數(shù)據(jù)滿2M空間后，如果在向employee表插入數(shù)據(jù)，這樣就會(huì)顯示空間不足的錯(cuò)誤。 案例說明： 1. 建立一個(gè)表空間 sp01 2. 在該表空間上建立一個(gè)普通表 mydment 其結(jié)構(gòu)和dept一樣 3. 向該表中加入數(shù)據(jù) insert into mydment select * from dept; 4. 當(dāng)一定時(shí)候就會(huì)出現(xiàn)無法擴(kuò)展的問題，怎么辦？ 5. 就擴(kuò)展該表空間，為其增加更多的存儲(chǔ)空間。有三種方法： 1. 增加數(shù)據(jù)文件 SQL alter tablespace sp01 add datafile d:testsp01.dbf size 20m; 2. 增加數(shù)據(jù)文件的大小

28、SQL alter tablespace 表空間名 d:testsp01.dbf resize 20m; 這里需要注意的是數(shù)據(jù)文件的大小不要超過500m。 3. 設(shè)置文件的自動(dòng)增長(zhǎng)。 SQL alter tablespace 表空間名 d:testsp01.dbf autoextend on next 10m maxsize 500m; 移動(dòng)數(shù)據(jù)文件 有時(shí)，如果你的數(shù)據(jù)文件所在的磁盤損壞時(shí)，該數(shù)據(jù)文件將不能再使用，為了能夠重新使用，需要將這些文件的副本移動(dòng)到其它的磁盤，然后恢復(fù)。 下面以移動(dòng)數(shù)據(jù)文件sp01.dbf為例來說明： 1. 確定數(shù)據(jù)文件所在的表空間 select tablespace

29、_name from dba_data_files where file_name=d:testsp01.dbf; 2. 使表空間脫機(jī) 確保數(shù)據(jù)文件的一致性，將表空間轉(zhuǎn)變?yōu)閛ffline的狀態(tài)。 alter tablespace sp01(表空間名) offline; 3. 使用命令移動(dòng)數(shù)據(jù)文件到指定的目標(biāo)位置 host move d:testsp01.dbf c:testsp01.dbf 4. 執(zhí)行alter tablespace命令 在物理上移動(dòng)了數(shù)據(jù)后，還必須執(zhí)行alter tablespace命令對(duì)數(shù)據(jù)庫(kù)文件進(jìn)行邏輯修改： alter tablespace sp01 rename da

30、tafile d:testsp01.dbf to c:testsp01.dbf; 5. 使得表空間聯(lián)機(jī) 在移動(dòng)了數(shù)據(jù)文件后，為了使用戶可以訪問該表空間，必須將其轉(zhuǎn)變?yōu)閛nline狀態(tài)。 alter tablespace sp01(表空間名) online; 顯示表空間信息 查詢數(shù)據(jù)字典視圖dba_tablespaces，顯示表空間的信息： select tablespace_name from dba_tablespaces; 顯示表空間所包含的數(shù)據(jù)文件 查詢數(shù)據(jù)字典視圖dba_data_files,可顯示表空間所包含的數(shù)據(jù)文件，如下： select file_name, bytes from

31、 dba_data_files where tablespce_name=表空間; 表空間小結(jié) 1. 了解表空間和數(shù)據(jù)文件的作用 2. 掌握常用表空間，undo表空間和臨時(shí)表空間的建立方法 3. 了解表空間的各個(gè)狀態(tài)(online, offline, read write, read only)的作用，及如何改變表空間的狀態(tài)的方法。 4. 了解移動(dòng)數(shù)據(jù)文件的原因，及使用alter tablespace 和alter datatable命令移動(dòng)數(shù)據(jù)文件的方法。 其它表空間 除了最常用的數(shù)據(jù)表空間外，還有其它類型表空間： 1. 索引表空間 2. undo表空間 3. 臨時(shí)表空間 4. 非標(biāo)準(zhǔn)塊的表

32、空間 這幾種表空間，大家伙可以自己參考書籍研究，這里我就不講。 其它說明 關(guān)于表空間的組成部分 段/區(qū)/塊，我們?cè)诤竺娼o大家講解。十三：約束維護(hù)數(shù)據(jù)的完整性 介紹 介紹 數(shù)據(jù)的完整性用于確保數(shù)據(jù)庫(kù)數(shù)據(jù)遵從一定的商業(yè)和邏輯規(guī)則，在oracle中，數(shù)據(jù)完整性可以使用約束、觸發(fā)器、應(yīng)用程序（過程、函數(shù)）三種方法來實(shí)現(xiàn)，在這三種方法中，因?yàn)榧s束易于維護(hù)，并且具有最好的性能，所以作為維護(hù)數(shù)據(jù)完整性的首選。 約束 約束 約束用于確保數(shù)據(jù)庫(kù)數(shù)據(jù)滿足特定的商業(yè)規(guī)則。在oracle中，約束包括：not null、 unique， primary key， foreign key，和check五種。 使用 not

33、 null（非空） 如果在列上定義了not null，那么當(dāng)插入數(shù)據(jù)時(shí)，必須為列提供數(shù)據(jù)。 unique（唯一） 當(dāng)定義了唯一約束后，該列值是不能重復(fù)的，但是可以為null。 primary key（主鍵） 用于唯一的標(biāo)示表行的數(shù)據(jù)，當(dāng)定義主鍵約束后，該列不但不能重復(fù)而且不能為null。 需要說明的是：一張表最多只能有一個(gè)主鍵，但是可以有多個(gè)unqiue約束。 foreign key（外鍵） 用于定義主表和從表之間的關(guān)系。外鍵約束要定義在從表上，主表則必須具有主鍵約束或是unique約束，當(dāng)定義外鍵約束后，要求外鍵列數(shù)據(jù)必須在主表的主鍵列存在或是為null。 check 用于強(qiáng)制行數(shù)據(jù)必須滿足

34、的條件，假定在sal列上定義了check約束，并要求sal列值在1000-2000之間如果不在1000-2000之間就會(huì)提示出錯(cuò)。 商店售貨系統(tǒng)表設(shè)計(jì)案例 現(xiàn)有一個(gè)商店的數(shù)據(jù)庫(kù)，記錄客戶及其購(gòu)物情況，由下面三個(gè)表組成：商品goods（商品號(hào)goodsId，商品名 goodsName，單價(jià) unitprice，商品類別category，供應(yīng)商provider）； 客戶customer（客戶號(hào)customerId，姓名name，住在address，電郵email，性別sex，身份證cardId）； 購(gòu)買purchase（客戶號(hào)customerId，商品號(hào)goodsId，購(gòu)買數(shù)量nums）； 請(qǐng)用S

35、QL語言完成下列功能： 1. 建表，在定義中要求聲明： (1). 每個(gè)表的主外鍵； (2). 客戶的姓名不能為空值； (3). 單價(jià)必須大于0，購(gòu)買數(shù)量必須在1到30之間； (4). 電郵不能夠重復(fù)； (5). 客戶的性別必須是 男 或者 女，默認(rèn)是男； SQL create table goods(goodsId char(8) primary key, -主鍵 goodsName varchar2(30)， unitprice number(10,2) check(unitprice0), category varchar2(8), provider varchar2(30) ); SQL

36、 create table customer( customerId char(8) primary key, -主鍵 name varchar2(50) not null, -不為空 address varchar2(50), email varchar2(50) unique, sex char(2) default 男 check(sex in (男，女)， - 一個(gè)char能存半個(gè)漢字，兩位char能存一個(gè)漢字 cardId char(18) ); SQL create table purchase( customerId char(8) references customer(cus

37、tomerId), goodsId char(8) references goods(goodsId), nums number(10) check (nums between 1 and 30) ); 表是默認(rèn)建在SYSTEM表空間的 維護(hù) 商店售貨系統(tǒng)表設(shè)計(jì)案例（2） 如果在建表時(shí)忘記建立必要的約束，則可以在建表后使用alter table命令為表增加約束。但是要注意：增加not null約束時(shí)，需要使用modify選項(xiàng)，而增加其它四種約束使用add選項(xiàng)。 1. 增加商品名也不能為空 SQL alter table goods modify goodsName not null; 2. 增

38、加身份證也不能重復(fù) SQL alter table customer add constraint xxxxxx unique(cardId); 3. 增加客戶的住址只能是海淀,朝陽,東城,西城,通州,崇文,昌平； SQL alter table customer add constraint yyyyyy check (address in (海淀,朝陽,東城,西城,通州,崇文,昌平); 刪除約束 當(dāng)不再需要某個(gè)約束時(shí)，可以刪除。 alter table 表名 drop constraint 約束名稱； 特別說明一下： 在刪除主鍵約束的時(shí)候，可能有錯(cuò)誤，比如： alter table 表名

39、drop primary key； 這是因?yàn)槿绻趦蓮埍泶嬖谥鲝年P(guān)系，那么在刪除主表的主鍵約束時(shí)，必須帶上cascade選項(xiàng) 如像： alter table 表名 drop primary key cascade; 顯示約束信息 1.顯示約束信息 通過查詢數(shù)據(jù)字典視圖user_constraints，可以顯示當(dāng)前用戶所有的約束的信息。 select constraint_name, constraint_type, status, validated from user_constraints where table_name = 表名; 2.顯示約束列 通過查詢數(shù)據(jù)字典視圖user_cons

40、_columns，可以顯示約束所對(duì)應(yīng)的表列信息。 select column_name, position from user_cons_columns where constraint_name = 約束名; 3.當(dāng)然也有更容易的方法，直接用pl/sql developer查看即可。簡(jiǎn)單演示一下下. 表級(jí)定義 列級(jí)定義 列級(jí)定義 列級(jí)定義是在定義列的同時(shí)定義約束。 如果在department表定義主鍵約束 create table department4(dept_id number(12) constraint pk_department primary key, name varchar

41、2(12), loc varchar2(12); 表級(jí)定義 表級(jí)定義是指在定義了所有列后，再定義約束。這里需要注意： not null約束只能在列級(jí)上定義。 以在建立employee2表時(shí)定義主鍵約束和外鍵約束為例： create table employee2(emp_id number(4), name varchar2(15), dept_id number(2), constraint pk_employee primary key (emp_id), constraint fk_department foreign key (dept_id) references departmen

42、t4(dept_id); 十四：Oracle索引、權(quán)限管理索引-原理介紹 介紹 索引是用于加速數(shù)據(jù)存取的數(shù)據(jù)對(duì)象。合理的使用索引可以大大降低i/o次數(shù)，從而提高數(shù)據(jù)訪問性能。索引有很多種我們主要介紹常用的幾種： 為什么添加了索引后，會(huì)加快查詢速度呢？ 創(chuàng)建索引 單列索引 單列索引是基于單個(gè)列所建立的索引，比如： create index 索引名 on 表名(列名); 復(fù)合索引 復(fù)合索引是基于兩列或是多列的索引。在同一張表上可以有多個(gè)索引，但是要求列的組合必須不同，比如： create index emp_idx1 on emp (ename, job); create index emp_id

43、x1 on emp (job, ename); 使用原則 使用原則 1. 在大表上建立索引才有意義 2. 在where子句或是連接條件上經(jīng)常引用的列上建立索引 3. 索引的層次不要超過4層 這里能不能給學(xué)生演示這個(gè)效果呢？ 如何構(gòu)建一個(gè)大表呢？ 索引的缺點(diǎn) 索引缺點(diǎn)分析 索引有一些先天不足： 1. 建立索引，系統(tǒng)要占用大約為表1.2倍的硬盤和內(nèi)存空間來保存索引。 2. 更新數(shù)據(jù)的時(shí)候，系統(tǒng)必須要有額外的時(shí)間來同時(shí)對(duì)索引進(jìn)行更新，以維持?jǐn)?shù)據(jù)和索引的一致性。 實(shí)踐表明，不恰當(dāng)?shù)乃饕坏谑聼o補(bǔ)，反而會(huì)降低系統(tǒng)性能。因?yàn)榇罅康乃饕谶M(jìn)行插入、修改和刪除操作時(shí)比沒有索引花費(fèi)更多的系統(tǒng)時(shí)間。 比如在如下

44、字段建立索引應(yīng)該是不恰當(dāng)?shù)模?1. 很少或從不引用的字段； 2. 邏輯型的字段，如男或女（是或否）等。 綜上所述，提高查詢效率是以消耗一定的系統(tǒng)資源為代價(jià)的，索引不能盲目的建立，這是考驗(yàn)一個(gè)DBA是否優(yōu)秀的很重要的指標(biāo)。 其它索引 介紹 按照數(shù)據(jù)存儲(chǔ)方式，可以分為B*樹、反向索引、位圖索引； 按照索引列的個(gè)數(shù)分類，可以分為單列索引、復(fù)合索引； 按照索引列值的唯一性，可以分為唯一索引和非唯一索引。 此外還有函數(shù)索引，全局索引，分區(qū)索引. 對(duì)于索引我還要說： 在不同的情況，我們會(huì)在不同的列上建立索引，甚至建立不同種類的索引，請(qǐng)記住，技術(shù)是死的，人是活的。比如： B*樹索引建立在重復(fù)值很少的列上，而

45、位圖索引則建立在重復(fù)值很多、不同值相對(duì)固定的列上。 顯示索引信息 顯示表的所有索引 在同一張表上可以有多個(gè)索引，通過查詢數(shù)據(jù)字典視圖dba_indexs和user_indexs，可以顯示索引信息。其中dba_indexs用于顯示數(shù)據(jù)庫(kù)所有的索引信息，而user_indexs用于顯示當(dāng)前用戶的索引信息： select index_name, index_type from user_indexes where table_name = 表名; 顯示索引列 通過查詢數(shù)據(jù)字典視圖user_ind_columns,可以顯示索引對(duì)應(yīng)的列的信息 select table_name, column_name

46、 from user_ind_columns where index_name = IND_ENAME; 你也可以通過pl/sql developer工具查看索引信息 管理權(quán)限和角色 介紹 介紹 這一部分我們主要看看oracle中如何管理權(quán)限和角色，權(quán)限和角色的區(qū)別在那里。 當(dāng)剛剛建立用戶時(shí)，用戶沒有任何權(quán)限，也不能執(zhí)行任何操作。如果要執(zhí)行某種特定的數(shù)據(jù)庫(kù)操作，則必須為其授予系統(tǒng)的權(quán)限；如果用戶要訪問其它方案的對(duì)象，則必須為其授予對(duì)象的權(quán)限。為了簡(jiǎn)化權(quán)限的管理，可以使用角色。這里我們會(huì)詳細(xì)的介紹。看圖： 權(quán)限 權(quán)限 權(quán)限是指執(zhí)行特定類型sql命令或是訪問其它方案對(duì)象的權(quán)利，包括系統(tǒng)權(quán)限和對(duì)象權(quán)

47、限兩種。 系統(tǒng)權(quán)限 系統(tǒng)權(quán)限介紹 系統(tǒng)權(quán)限是指執(zhí)行特定類型sql命令的權(quán)利。它用于控制用戶可以執(zhí)行的一個(gè)或是一組數(shù)據(jù)庫(kù)操作。比如當(dāng)用戶具有create table權(quán)限時(shí)，可以在其方案中建表，當(dāng)用戶具有create any table權(quán)限時(shí)，可以在任何方案中建表。oracle提供了100多種系統(tǒng)權(quán)限。 常用的有： create session 連接數(shù)據(jù)庫(kù) create table 建表 create view 建視圖 create public synonym 建同義詞 create procedure 建過程、函數(shù)、包 create trigger 建觸發(fā)器 create cluster 建簇

48、 顯示系統(tǒng)權(quán)限 oracle提供了100多種系統(tǒng)權(quán)限，而且oracle的版本越高，提供的系統(tǒng)權(quán)限就越多，我們可以查詢數(shù)據(jù)字典視圖system_privilege_map，可以顯示所有系統(tǒng)權(quán)限。 select * from system_privilege_map order by name; 授予系統(tǒng)權(quán)限 一般情況，授予系統(tǒng)權(quán)限是由dba完成的，如果用其他用戶來授予系統(tǒng)權(quán)限，則要求該用戶必須具有g(shù)rant any privilege的系統(tǒng)權(quán)限。在授予系統(tǒng)權(quán)限時(shí)，可以帶有with admin option選項(xiàng)，這樣，被授予權(quán)限的用戶或是角色還可以將該系統(tǒng)權(quán)限授予其它的用戶或是角色。為了讓大家快速

49、理解，我們舉例說明： 1.創(chuàng)建兩個(gè)用戶ken，tom。初始階段他們沒有任何權(quán)限，如果登錄就會(huì)給出錯(cuò)誤的信息。 create user ken identfied by ken; 2 給用戶ken授權(quán) 1). grant create session, create table to ken with admin option; 2). grant create view to ken; 3 給用戶tom授權(quán) 我們可以通過ken給tom授權(quán)，因?yàn)閣ith admin option是加上的。當(dāng)然也可以通過dba給tom授權(quán)，我們就用ken給tom授權(quán)： 1. grant create sessio

50、n, create table to tom; 2. grant create view to ken; -ok嗎？不ok 回收系統(tǒng)權(quán)限 一般情況下，回收系統(tǒng)權(quán)限是dba來完成的，如果其它的用戶來回收系統(tǒng)權(quán)限，要求該用戶必須具有相應(yīng)系統(tǒng)權(quán)限及轉(zhuǎn)授系統(tǒng)權(quán)限的選項(xiàng)（with admin option）?；厥障到y(tǒng)權(quán)限使用revoke來完成。 當(dāng)回收了系統(tǒng)權(quán)限后，用戶就不能執(zhí)行相應(yīng)的操作了，但是請(qǐng)注意，系統(tǒng)權(quán)限級(jí)聯(lián)收回的問題?不是級(jí)聯(lián)回收！ system -ken -tom (create session)(create session)( create session) 用system執(zhí)行如下操作：

51、 revoke create session from ken; -請(qǐng)思考tom還能登錄嗎？ 答案：能，可以登錄 對(duì)象權(quán)限 對(duì)象權(quán)限介紹 指訪問其它方案對(duì)象的權(quán)利，用戶可以直接訪問自己方案的對(duì)象，但是如果要訪問別的方案的對(duì)象，則必須具有對(duì)象的權(quán)限。 比如smith用戶要訪問scott.emp表（scott：方案，emp：表） 常用的有： alter 修改 delete 刪除 select 查詢 insert 添加 update 修改 index 索引 references 引用 execute 執(zhí)行 顯示對(duì)象權(quán)限 通過數(shù)據(jù)字段視圖可以顯示用戶或是角色所具有的對(duì)象權(quán)限。視圖為dba_tab_pr

52、ivs SQL conn system/manager; SQL select distinct privilege from dba_tab_privs; SQL select grantor, owner, table_name, privilege from dba_tab_privs where grantee = BLAKE; 1.授予對(duì)象權(quán)限 在oracle9i前，授予對(duì)象權(quán)限是由對(duì)象的所有者來完成的，如果用其它的用戶來操作，則需要用戶具有相應(yīng)的（with grant option）權(quán)限，從oracle9i開始，dba用戶（sys，system）可以將任何對(duì)象上的對(duì)象權(quán)限授予其它用

53、戶。授予對(duì)象權(quán)限是用grant命令來完成的。 對(duì)象權(quán)限可以授予用戶，角色，和public。在授予權(quán)限時(shí)，如果帶有with grant option選項(xiàng)，則可以將該權(quán)限轉(zhuǎn)授給其它用戶。但是要注意with grant option選項(xiàng)不能被授予角色。 1.monkey用戶要操作scott.emp表，則必須授予相應(yīng)的對(duì)象權(quán)限 1). 希望monkey可以查詢scott.emp表的數(shù)據(jù)，怎樣操作？ grant select on emp to monkey; 2). 希望monkey可以修改scott.emp的表數(shù)據(jù)，怎樣操作？ grant update on emp to monkey; 3). 希

54、望monkey可以刪除scott.emp的表數(shù)據(jù)，怎樣操作？ grant delete on emp to monkey; 4). 有沒有更加簡(jiǎn)單的方法，一次把所有權(quán)限賦給monkey？ grant all on emp to monkey; 2.能否對(duì)monkey訪問權(quán)限更加精細(xì)控制。（授予列權(quán)限） 1). 希望monkey只可以修改scott.emp的表的sal字段，怎樣操作？ grant update on emp(sal) to monkey 2).希望monkey只可以查詢scott.emp的表的ename，sal數(shù)據(jù)，怎樣操作？ grant select on emp(ename,sal) to monkey . 3.授予alter權(quán)限 如果black用戶要修改scott.emp表的結(jié)構(gòu)，則必須授予alter對(duì)象權(quán)限 SQL c