ORACLE應(yīng)用開發(fā)

1、ORACLE應(yīng)用開發(fā)過程中常見問題1、數(shù)據(jù)庫日常運維監(jiān)控1.1 檢查表空間文件使用率一、執(zhí)行如下SQL語句進(jìn)行查詢：select b.file_name, b.tablespace_name, b.bytes / 1024 / 1024 總空間大小, (b.bytes - sum(nvl(a.bytes, 0) / 1024 / 1024 已使用空間, substr(b.bytes - sum(nvl(a.bytes, 0) / (b.bytes) * 100, 1, 5) 利用率 from dba_free_space a, dba_data_files b where a.file_id

2、= b.file_idgroup by b.tablespace_name, b.file_name, b.bytes order by b.tablespace_name;二、分析與注意事項：1)數(shù)據(jù)庫用戶建議同時創(chuàng)建多個表空間，數(shù)據(jù)表空間和索引表空間盡量單獨建立，增加IO的并行響應(yīng)效率；2)用戶的表空間應(yīng)該由多個表空間文件組成，建議每個表空間文件大小為10G；3)用戶的表空間利用率超出80%以上，為避免表空間不足引起異常，應(yīng)該提前申請增加表空間文件；三、表空間不足解決方法:1）修改表空間數(shù)據(jù)文件大?。篈LTER DATABASE DATAFILE '/opt/oracle/orad

3、ata/TEST/test.dbf' RESIZE 20000M;2）為表空間增加數(shù)據(jù)文件：alter tablespace sales add datafile '/home/app/oracle/oradata/oracle8i/sales02.dbf' size 20000M autoextend on next 1000M maxsize 30000M;3)清理表空間垃圾:purge recyclebin;4）磁盤碎片壓縮釋放表空間： 對表進(jìn)行磁盤壓縮，當(dāng)對表內(nèi)部數(shù)據(jù)進(jìn)行了大量的UPDATE、DELETE操作后，一定時間需要進(jìn)行磁盤壓縮，否則在查詢時，若通過FU

4、LL SCAN掃描數(shù)據(jù)，將會把空塊也會掃描到，對表進(jìn)行磁盤壓縮需要進(jìn)行行遷移操作，所以首先需要操作： ALTER TABLE A ENABLE ROW MOVEMENT; 對表的壓縮語法為： ALTER TABLE A SHRINK SPACE;對于索引也需要進(jìn)行壓縮，索引也是表：ALTER INDEX <index_name> shrink space;1.2 檢查臨時表空間在oracle數(shù)據(jù)庫中，臨時表空間主要用于用戶在使用order by 、group by語句進(jìn)行排序和匯總時所需的臨時工作空間。要查詢數(shù)據(jù)庫中臨時表空間的名稱，大小及數(shù)據(jù)文件，命令如下：select tabl

5、espace_name,current_users,total_extents,used_extents,free_extents from v$sort_segment;分析：臨時表空間經(jīng)常處于飽和狀態(tài)，除了需要適當(dāng)調(diào)整臨時表空間的初始化大小，另一個工作就是查找最消耗臨時表空間的SQL語句進(jìn)行分析優(yōu)化，這個才是治標(biāo)治本的關(guān)鍵。1.3 檢查單張表的使用情況一、查詢所有用戶表使用大小的前三十名select * from (select segment_name, bytes / 1024 / 1024 | 'M' DATASPACE from (select segment_na

6、me, SUM(bytes) bytes from dba_segments where owner = USER GROUP BY segment_name) order by bytes desc) where rownum <= 30; 二、分析：ORACLE官方建議單表數(shù)據(jù)超過2G的數(shù)據(jù)表，應(yīng)該進(jìn)行分區(qū)，分區(qū)的目的是使得每個分區(qū)的數(shù)據(jù)量保持一定大小，分區(qū)之間數(shù)據(jù)量分布比較均勻，必須結(jié)合業(yè)務(wù)應(yīng)用特征，將每次用戶使用的數(shù)據(jù)限制在同一個分區(qū)內(nèi)，盡量避免跨分區(qū)查詢。1.4 oracle鎖表問題一、檢查鎖表對象，可直接執(zhí)行： Select a.inst_id, c.sid, c.seria

7、l#, , b.object_name, c.username, a.object_id, gram, c.status, c.osuser, c.terminal from gv$Locked_object a, All_objects b, gv$session c, audit_actions d where a.object_id = b.object_id and a.inst_id = c.inst_id(+) and a.session_id = c.sid(+) and mand = d.action; 二、對于長期鎖住不釋放的表進(jìn)行手動解鎖：1）以系統(tǒng)管

8、理員登陸 sqlplus name/passwddbname as sysdba 2）查看鎖的SID和SERIAL#，把鎖給KILL掉 -alter system kill session 'sid,serial#' alter system kill session '146,21177' 3）如果是賬號被鎖，需要給賬戶解鎖 alter user ermsdata account unlock; 4）處理Oracle中殺不掉的鎖一些ORACLE中的進(jìn)程被殺掉后，狀態(tài)被置為"killed"，但是鎖定的資源很長時間不釋放，現(xiàn)在提供一種方法解決這

9、種問題，那就是在ORACLE中殺不掉的，在OS一級再殺。 1.5 檢查消耗數(shù)據(jù)庫性能的SQL語句一、檢查cpu_time占用top 10的sqlselect cpu_time, sql_text from (select sql_text, cpu_time, rank() over(order by cpu_time desc) exec_rank from v$sql m) where exec_rank <= 10;二、執(zhí)行次數(shù)最多的top 10的SQL語句select sql_text, executions from (select sql_text, executions,

10、rank() over(order by executions desc) exec_rank from v$sql) where exec_rank <= 10;1.6 檢查數(shù)據(jù)庫連接數(shù)1)當(dāng)前的數(shù)據(jù)庫連接數(shù) select count(*) from v$process 2)數(shù)據(jù)庫允許的最大連接數(shù) select value from v$parameter where name ='processes'3)修改最大連接數(shù): alter system set processes = 300 scope = spfile;4)修改完需重啟數(shù)據(jù)庫生效 shutdown imm

11、ediate; startup;5)查看當(dāng)前有哪些用戶正在執(zhí)行哪些語句 SELECT osuser, a.username,cpu_time/executions/1000000|'s',b.sql_text,machine from v$session a, v$sqlarea b where a.sql_address =b.address order by cpu_time/executions desc;6)當(dāng)前的session連接數(shù) select count(*) from v$session;-集群環(huán)境，查看每個單點服務(wù)器分別占用了多少進(jìn)程select INST_I

12、D, count(1) from gv$session t group by t.INST_ID;7)當(dāng)前的并發(fā)連接數(shù) select count(*) from v$session where status='ACTIVE'8)最大連接 show parameter processes 9)修改最大連接數(shù)alter system set processes = value scope =spfile; 2、大數(shù)據(jù)表分區(qū)索引創(chuàng)建122.1 查找有建分區(qū)的表select * from user_part_tables;1）ORACLE建議，單表數(shù)據(jù)大小超過2G或者數(shù)據(jù)量按時間持續(xù)增

13、長的歷史表，應(yīng)該考慮進(jìn)行分區(qū)操作；2）分區(qū)字段不能用來創(chuàng)建索引；3）創(chuàng)建分區(qū)索引，對于只會在同一個分區(qū)內(nèi)使用的字段，盡量使用LOCAL本地索引；4）通過主鍵進(jìn)行查詢時，無需加分區(qū)字段進(jìn)行條件限制，主鍵本身已經(jīng)是最快；5）分區(qū)盡量有相對獨立的表空間，提升IO響應(yīng)并發(fā)度；7）分區(qū)的字段一定要是經(jīng)常用以提取數(shù)據(jù)的字段，否則會在提取過程中導(dǎo)致遍歷多個分區(qū)，這樣比沒有分區(qū)還要慢。8）分區(qū)字段要選擇合適，數(shù)據(jù)較為均勻分布到各個分區(qū)，不要太多也不要太少，而且根據(jù)分區(qū)字段可以很快定位到分區(qū)范圍。9）一般情況下，盡量然業(yè)務(wù)操作在同一個分區(qū)內(nèi)部完成。10）分區(qū)表、索引、分區(qū)索引，要利用其性能優(yōu)勢，最基本就是要提取

14、數(shù)據(jù)時，要通過它首先將數(shù)據(jù)的范圍縮小到一個即使做全盤掃描也不會太慢的情況。11）所以SQL一定要有分區(qū)上的這個字段的一個WHERE條件，將數(shù)據(jù)迅速定位到分區(qū)內(nèi)部，而且盡量定位到一個分區(qū)里面（這個和創(chuàng)建分區(qū)的規(guī)則有關(guān)系）。12）建立分區(qū)本身不提要性能，要用好才可提高性能，在必要的RAC集群中，若存在多分區(qū)提取數(shù)據(jù)，適當(dāng)采用并行提取可以提高提取的速度。2.2 分區(qū)表理論知識Oracle提供了分區(qū)技術(shù)以支持VLDB(Very Large DataBase)。分區(qū)表通過對分區(qū)列的判斷，把分區(qū)列不同的記錄，放到不同的分區(qū)中。分區(qū)完全對應(yīng)用透明。Oracle的分區(qū)表可以包括多個分區(qū)，每個分區(qū)都是一個獨立的

15、段（SEGMENT），可以存放到不同的表空間中。查詢時可以通過查詢表來訪問各個分區(qū)中的數(shù)據(jù)，也可以通過在查詢時直接指定分區(qū)的方法來進(jìn)行查詢。When to Partition a Table什么時候需要分區(qū)表，官網(wǎng)的2個建議如下：（1）Tables greater than 2GB should always be considered for partitioning.（2）Tables containing historical data, in which new data is added into the newest partition. A typical example is

16、a historical table where only the current month's data is updatable and the other 11 months are read only.分區(qū)提供以下優(yōu)點：（1）由于將數(shù)據(jù)分散到各個分區(qū)中，減少了數(shù)據(jù)損壞的可能性；（2）可以對單獨的分區(qū)進(jìn)行備份和恢復(fù)；（3）可以將分區(qū)映射到不同的物理磁盤上，來分散IO；（4）提高可管理性、可用性和性能。Oracle 10g提供了以下幾種分區(qū)類型：（1）范圍分區(qū)（range）；（2）哈希分區(qū)（hash）；（3）列表分區(qū)（list）；（4）范圍哈希復(fù)合分區(qū)（range-hash）；（

17、5）范圍列表復(fù)合分區(qū)（range-list）。2.3 Range分區(qū)：Range分區(qū)是應(yīng)用范圍比較廣的表分區(qū)方式，它是以列的值的范圍來做為分區(qū)的劃分條件，將記錄存放到列值所在的range分區(qū)中。如按照時間劃分，2010年1月的數(shù)據(jù)放到a分區(qū)，2月的數(shù)據(jù)放到b分區(qū)，在創(chuàng)建的時候，需要指定基于的列，以及分區(qū)的范圍值。在按時間分區(qū)時，如果某些記錄暫無法預(yù)測范圍，可以創(chuàng)建maxvalue分區(qū)，所有不在指定范圍內(nèi)的記錄都會被存儲到maxvalue所在分區(qū)中。如：create table pdba (id number, time date) partition by range (time)(parti

18、tion p1 values less than (to_date('2010-10-1', 'yyyy-mm-dd'),partition p2 values less than (to_date('2010-11-1', 'yyyy-mm-dd'),partition p3 values less than (to_date('2010-12-1', 'yyyy-mm-dd'),partition p4 values less than (maxvalue)2.4 Hash分區(qū)：對于那些無法有效

19、劃分范圍的表，可以使用hash分區(qū)，這樣對于提高性能還是會有一定的幫助。hash分區(qū)會將表中的數(shù)據(jù)平均分配到你指定的幾個分區(qū)中，列所在分區(qū)是依據(jù)分區(qū)列的hash值自動分配，因此你并不能控制也不知道哪條記錄會被放到哪個分區(qū)中，hash分區(qū)也可以支持多個依賴列。如：create table test(transaction_id number primary key,item_id number(8) not null)partition by hash(transaction_id)(partition part_01 tablespace tablespace01,partition part

20、_02 tablespace tablespace02,partition part_03 tablespace tablespace03);在這里，我們指定了每個分區(qū)的表空間。2.5 List分區(qū)：List分區(qū)也需要指定列的值，其分區(qū)值必須明確指定，該分區(qū)列只能有一個，不能像range或者h(yuǎn)ash分區(qū)那樣同時指定多個列做為分區(qū)依賴列，但它的單個分區(qū)對應(yīng)值可以是多個。在分區(qū)時必須確定分區(qū)列可能存在的值，一旦插入的列值不在分區(qū)范圍內(nèi)，則插入/更新就會失敗，因此通常建議使用list分區(qū)時，要創(chuàng)建一個default分區(qū)存儲那些不在指定范圍內(nèi)的記錄，類似range分區(qū)中的maxvalue分區(qū)。在根據(jù)某

21、字段，如城市代碼分區(qū)時，可以指定default，把非分區(qū)規(guī)則的數(shù)據(jù)，全部放到這個default分區(qū)。如：create table custaddr(id varchar2(15 byte) not null,areacode varchar2(4 byte)partition by list (areacode)( partition t_list025 values ('025'), partition t_list372 values ('372') , partition t_list510 values ('

22、;510'),partition p_other values (default)2.6 組合分區(qū)：如果某表按照某列分區(qū)之后，仍然較大，或者是一些其它的需求，還可以通過分區(qū)內(nèi)再建子分區(qū)的方式將分區(qū)再分區(qū)，即組合分區(qū)的方式。組合分區(qū)呢在10g中有兩種：range-hash，range-list。注意順序，根分區(qū)只能是range分區(qū)，子分區(qū)可以是hash分區(qū)或list分區(qū)。如：create table test(transaction_id number primary key,transaction_date date)partition by range(transaction_dat

23、e) subpartition by hash(transaction_id)subpartitions 3 store in (tablespace01,tablespace02,tablespace03)(partition part_01 values less than(to_date(2009-01-01,yyyy-mm-dd),partition part_02 values less than(to_date(2010-01-01,yyyy-mm-dd),partition part_03 values less than(maxvalue);create table emp_s

24、ub_template (deptno number, empname varchar(32), grade number)partition by range(deptno) subpartition by hash(empname)subpartition template(subpartition a tablespace ts1,subpartition b tablespace ts2,subpartition c tablespace ts3,subpartition d tablespace ts4)(partition p1 values less than (1000),pa

25、rtition p2 values less than (2000),partition p3 values less than (maxvalue);create table quarterly_regional_sales(deptno number, item_no varchar2(20),txn_date date, txn_amount number, state varchar2(2)tablespace ts4partition by range (txn_date)subpartition by list (state)(partition q1_1999 values le

26、ss than (to_date('1-apr-1999','dd-mon-yyyy')(subpartition q1_1999_northwest values ('or', 'wa'),subpartition q1_1999_southwest values ('az', 'ut', 'nm'),subpartition q1_1999_northeast values ('ny', 'vm', 'nj'),subpartiti

27、on q1_1999_southeast values ('fl', 'ga'),subpartition q1_1999_northcentral values ('sd', 'wi'),subpartition q1_1999_southcentral values ('ok', 'tx'),partition q2_1999 values less than ( to_date('1-jul-1999','dd-mon-yyyy')(subpartition q

28、2_1999_northwest values ('or', 'wa'),subpartition q2_1999_southwest values ('az', 'ut', 'nm'),subpartition q2_1999_northeast values ('ny', 'vm', 'nj'),subpartition q2_1999_southeast values ('fl', 'ga'),subpartition q2_19

29、99_northcentral values ('sd', 'wi'),subpartition q2_1999_southcentral values ('ok', 'tx'),partition q3_1999 values less than (to_date('1-oct-1999','dd-mon-yyyy')(subpartition q3_1999_northwest values ('or', 'wa'),subpartition q3_1999_so

30、uthwest values ('az', 'ut', 'nm'),subpartition q3_1999_northeast values ('ny', 'vm', 'nj'),subpartition q3_1999_southeast values ('fl', 'ga'),subpartition q3_1999_northcentral values ('sd', 'wi'),subpartition q3_1999_sou

31、thcentral values ('ok', 'tx'),partition q4_1999 values less than ( to_date('1-jan-2000','dd-mon-yyyy')(subpartition q4_1999_northwest values ('or', 'wa'),subpartition q4_1999_southwest values ('az', 'ut', 'nm'),subpartition q4_1

32、999_northeast values ('ny', 'vm', 'nj'),subpartition q4_1999_southeast values ('fl', 'ga'),subpartition q4_1999_northcentral values ('sd', 'wi'),subpartition q4_1999_southcentral values ('ok', 'tx');在Oracle 11g中，組合分區(qū)功能這塊有所增強，又增加

33、了range-range,list-range,list-list,list-hash，并且 11g里面還支持Interval分區(qū)和虛擬列分區(qū)。2.7 創(chuàng)建global全局索引（主要針對range分區(qū)）create index idx_parti_range_id on t_partition_range(id) global partition by range(id)( partition i_range_p1 values less than (10) tablespace tbspart01, partition i_range_p2 values less than (40

34、) tablespace tbspart02, partition i_range_pmax values less than (maxvalue) tablespace tbspart03 );2.8 創(chuàng)建本地分區(qū)索引create index IDX_PART_RANGE_ID ON T_PARTITION_RANGE(id) local ( partition i_range_p1 tablespace tbspart01, partition i_range_p2 tablespace tbspart01, partition i_range_p3 tablespace tbspart0

35、2, partition i_range_pmax tablespace tbspart02);2.9 對于分區(qū)表的分區(qū)索引CREATE INDEX INX_TAB_PARTITION_COL1 ON TABLE_PARTITION(COL1)  GLOBAL PARTITION BY RANGE(COL1)(         PARTITION IDX_P1 values less than (1000000),       

36、0; PARTITION IDX_P2 values less than (2000000),         PARTITION IDX_P3 values less than (MAXVALUE)  );對比索引方式一般使用LOCAL索引較為方便，而且維護(hù)代價較低，并且LOCAL索引是在分區(qū)的基礎(chǔ)上去創(chuàng)建索引，類似于在一個子表內(nèi)部去創(chuàng)建索引，這樣開銷主要是區(qū) 分分區(qū)上，很規(guī)范的管理起來，在OLAP系統(tǒng)中應(yīng)用很廣泛；而相對的GLOBAL索引是全局類型的索引，根據(jù)實際情況可以調(diào)整分區(qū)的類別，而并非按照分區(qū)

37、 結(jié)構(gòu)一一定義，相對維護(hù)代價較高一些，在OLTP環(huán)境用得相對較多，這里所謂OLTP和OLAP也是相對的，不是特殊的項目，沒有絕對的劃分概念，在應(yīng)用 過程中依據(jù)實際情況而定，來提高整體的運行性能。2.10 索引分析對表進(jìn)行壓縮后，需要對表和索引進(jìn)行重新分析，對表進(jìn)行重新分析：ANALYZE TABLE <table_name> COMPUTE STATISTICS;索引重新分析對于分區(qū)表并進(jìn)行了索引分區(qū)的情況，需要對每個分區(qū)的索引進(jìn)行重新編譯,這里以LOCAL索引為例子（其每個索引的分區(qū)和表分區(qū)結(jié)構(gòu)相同，默認(rèn)分區(qū)名稱和表分區(qū)名稱相同）： ALTER INDEX <in

38、dex_name> REBUILD PARTITION <partition_name>  對于全局索引，根據(jù)全局索引鎖定義的分區(qū)名稱修改即可，若沒有分區(qū)，和普通單表索引重新編譯方式相同： ALTER INDEX <index_name> REBUILD;3、數(shù)據(jù)開發(fā)常見語句-查找重復(fù)數(shù)據(jù) select a.serv_type_id, city_id, count(1) from td_serv_type a group by a.serv_type_id, city_idhaving count(1) > 1;-刪除重復(fù)數(shù)據(jù) delet

39、e from td_serv_type a where a.city_id = 1004 and a.rowid <> (select max(rowid) from td_serv_type b where b.city_id = 1004 and b.serv_type_id = a.serv_type_id); -快照,創(chuàng)建新表速度N快語句： insert /*+ append */ into tf_acc_detail_hjf select * from tf_acc_detail； -遍歷樹型結(jié)構(gòu)語句SELECT lpad('|- ', (level -

40、1) * 3, '') | a.Unit_Name Unit_Name,A.UNIT_CODE FROM S_UNIT a CONNECT BY PRIOR a.Syscode = a.Psyscode START WITH a.Psyscode IS NULL -（加上關(guān)鍵字prior表示查找所有層）ORDER SIBLINGS BY a.Sno; - (表示在相同層級之間排序)-路徑及遍歷視圖 SELECT substr(SYS_CONNECT_BY_PATH(t.Unit_Name, '/'), 2) AS unitname, LEVEL, syscode

41、 FROM s_unit t START WITH psyscode is null CONNECT BY psyscode = prior syscode; -增加強制索引： select /*+INDEX(F,IDX_CUST_PROD_04)*/count(t.serv_id) cou,t.cust_group from sf_cust_prod t,tf_cust_enrol_info f where t.cust_group=f.cust_group;1.1 閃回技術(shù)恢復(fù)DROP表誤刪除操作執(zhí)行如下SQL語句，將被誤刪的表S_UNIT恢復(fù)并修改名稱為S_UNIT_BAK：FLASHB

42、ACK TABLE S_UNIT TO BEFORE DROP RENAME TO S_UNIT_BAK; 1.2 定時器設(shè)置 begin sys.dbms_job.submit(job => :job, - job編號 what => 'dbms_output.pub_line(''asdf'');', - 執(zhí)行腳本 next_date => to_date('17-01-2011 16:17:31', 'dd-mm-yyyy hh24:mi:ss'), - 下次執(zhí)行時間 interval => 'trunc(