ORACLE应用开发

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(