1、ORACLE應用開發過程中常見問題1、數據庫日常運維監控1.1 檢查表空間文件使用率一、執行如下SQL語句進行查詢：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)數據庫用戶建議同時創建多個表空間，數據表空間和索引表空間盡量單獨建立，增加IO的并行響應效率；2)用戶的表空間應該由多個表空間文件組成，建議每個表空間文件大小為10G；3)用戶的表空間利用率超出80%以上，為避免表空間不足引起異常，應該提前申請增加表空間文件；三、表空間不足解決方法:1）修改表空間數據文件大小：ALTER DATABASE DATAFILE '/opt/oracle/orad

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

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

5、espace_name,current_users,total_extents,used_extents,free_extents from v$sort_segment;分析：臨時表空間經常處于飽和狀態，除了需要適當調整臨時表空間的初始化大小，另一個工作就是查找最消耗臨時表空間的SQL語句進行分析優化，這個才是治標治本的關鍵。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官方建議單表數據超過2G的數據表，應該進行分區，分區的目的是使得每個分區的數據量保持一定大小，分區之間數據量分布比較均勻，必須結合業務應用特征，將每次用戶使用的數據限制在同一個分區內，盡量避免跨分區查詢。1.4 oracle鎖表問題一、檢查鎖表對象，可直接執行： 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; 二、對于長期鎖住不釋放的表進行手動解鎖：1）以系統管

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中的進程被殺掉后，狀態被置為"killed"，但是鎖定的資源很長時間不釋放，現在提供一種方法解決這

9、種問題，那就是在ORACLE中殺不掉的，在OS一級再殺。 1.5 檢查消耗數據庫性能的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;二、執行次數最多的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 檢查數據庫連接數1)當前的數據庫連接數 select count(*) from v$process 2)數據庫允許的最大連接數 select value from v$parameter where name ='processes'3)修改最大連接數: alter system set processes = 300 scope = spfile;4)修改完需重啟數據庫生效 shutdown imm

11、ediate; startup;5)查看當前有哪些用戶正在執行哪些語句 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)當前的session連接數 select count(*) from v$session;-集群環境，查看每個單點服務器分別占用了多少進程select INST_I

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

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

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

15、段（SEGMENT），可以存放到不同的表空間中。查詢時可以通過查詢表來訪問各個分區中的數據，也可以通過在查詢時直接指定分區的方法來進行查詢。When to Partition a Table什么時候需要分區表，官網的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.分區提供以下優點：（1）由于將數據分散到各個分區中，減少了數據損壞的可能性；（2）可以對單獨的分區進行備份和恢復；（3）可以將分區映射到不同的物理磁盤上，來分散IO；（4）提高可管理性、可用性和性能。Oracle 10g提供了以下幾種分區類型：（1）范圍分區（range）；（2）哈希分區（hash）；（3）列表分區（list）；（4）范圍哈希復合分區（range-hash）；（

17、5）范圍列表復合分區（range-list）。2.3 Range分區：Range分區是應用范圍比較廣的表分區方式，它是以列的值的范圍來做為分區的劃分條件，將記錄存放到列值所在的range分區中。如按照時間劃分，2010年1月的數據放到a分區，2月的數據放到b分區，在創建的時候，需要指定基于的列，以及分區的范圍值。在按時間分區時，如果某些記錄暫無法預測范圍，可以創建maxvalue分區，所有不在指定范圍內的記錄都會被存儲到maxvalue所在分區中。如：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分區：對于那些無法有效

19、劃分范圍的表，可以使用hash分區，這樣對于提高性能還是會有一定的幫助。hash分區會將表中的數據平均分配到你指定的幾個分區中，列所在分區是依據分區列的hash值自動分配，因此你并不能控制也不知道哪條記錄會被放到哪個分區中，hash分區也可以支持多個依賴列。如：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);在這里，我們指定了每個分區的表空間。2.5 List分區：List分區也需要指定列的值，其分區值必須明確指定，該分區列只能有一個，不能像range或者hash分區那樣同時指定多個列做為分區依賴列，但它的單個分區對應值可以是多個。在分區時必須確定分區列可能存在的值，一旦插入的列值不在分區范圍內，則插入/更新就會失敗，因此通常建議使用list分區時，要創建一個default分區存儲那些不在指定范圍內的記錄，類似range分區中的maxvalue分區。在根據某

21、字段，如城市代碼分區時，可以指定default，把非分區規則的數據，全部放到這個default分區。如：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 組合分區：如果某表按照某列分區之后，仍然較大，或者是一些其它的需求，還可以通過分區內再建子分區的方式將分區再分區，即組合分區的方式。組合分區呢在10g中有兩種：range-hash，range-list。注意順序，根分區只能是range分區，子分區可以是hash分區或list分區。如：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中，組合分區功能這塊有所增強，又增加

33、了range-range,list-range,list-list,list-hash，并且 11g里面還支持Interval分區和虛擬列分區。2.7 創建global全局索引（主要針對range分區）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 創建本地分區索引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 對于分區表的分區索引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索引較為方便，而且維護代價較低，并且LOCAL索引是在分區的基礎上去創建索引，類似于在一個子表內部去創建索引，這樣開銷主要是區 分分區上，很規范的管理起來，在OLAP系統中應用很廣泛；而相對的GLOBAL索引是全局類型的索引，根據實際情況可以調整分區的類別，而并非按照分區

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

38、dex_name> REBUILD PARTITION <partition_name>  對于全局索引，根據全局索引鎖定義的分區名稱修改即可，若沒有分區，和普通單表索引重新編譯方式相同： ALTER INDEX <index_name> REBUILD;3、數據開發常見語句-查找重復數據 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;-刪除重復數據 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); -快照,創建新表速度N快語句： insert /*+ append */ into tf_acc_detail_hjf select * from tf_acc_detail； -遍歷樹型結構語句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 -（加上關鍵字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 閃回技術恢復DROP表誤刪除操作執行如下SQL語句，將被誤刪的表S_UNIT恢復并修改名稱為S_UNIT_BAK：FLASHB

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