oracle10g修改字段为clob的异常ora-22858

修改oracle 的表的字段VARCHAR2为clob时，当是10g版本的时候包ora-22858异常；

解决办法如下： 列入修改表PLUGIN_CONF，先将字段修改为long型：

ALTER TABLE TSOCNEW.PLUGIN_CONF

MODIFY(TASKCURSOR LONG); 然后在修改为clob型：

ALTER TABLE TSOCNEW.PLUGIN_CONF

MODIFY(TASKCURSOR CLOB); 原因：Oracle的文档对这个错误的描述是：

ORA-22858: invalid alteration of datatype Cause: An attempt was made to modify the column type to object, REF, nested table, VARRAY or LOB type. Action: Create a new column of the desired type and copy the current column data to the new type using the appropriate type constructor.

显然是CLOB字段的特殊性，限制了直接修改数据类型。

虽然不能直接修改为CLOB，但是如果记录为空，可以直接修改为LONG类型： 不久前的一篇文章介绍过，对于LONG类型，不管有没有数据存在，可以直接修改为CLOB类型：http://yangtingkun.itpub.net/post/468/501094

SQL> INSERT INTO T_VAR 2 VALUES (LPAD('A', 4000, 'A'));

1 row created.

SQL> COMMIT;

Commit complete.

SQL> ALTER TABLE T_VAR MODIFY (C CLOB);

Table altered.

对于LONG类型的转换，Oracle并不是简单的将列的定义换成CLOB，而是生成了一个临时列，将数据保存，然后删除原LONG列。

Oracle可以对LONG类型的转换操作进行封装，不知道为什么没有对VARCHAR2类型转换为CLOB进行封装，使得一个简单的ALTER TABLE命令必须通过多个命令才能完成。

至于VARCHAR2到CLOB的转换就没有必要详细说明了，采用在线重定义就能实现。如果有足够的维护时间，也可以直接添加CLOB列，对新增CLOB列赋值、删除原VARCHAR2类型列，最后对新增CLOB列改名。 来源： [http://www.360doc.com/content/12/0627/10/7662927_220705696.shtml](http://www.360doc.com/content/12/0627/10/7662927_220705696.shtml)

Oracle中JOB的创建后不执行解决方法

leegstar的个人空间

copy Bookmark http://space.itpub.net/26585184

• 博客
• 图片
• 商品
• 下载
• 收藏
• 影音
• 圈子
• 好友
• 论坛
• 留言

空间管理 您的位置: ITPUB个人空间 » leegstar的个人空间 » 日志

Oracle中JOB的创建后不执行解决方法

上一篇 / 下一篇 2013-08-07 15:13:48 / 个人分类：oracle 查看( 174 ) / 评论( 1 ) / 评分( 5 / 0 )

在**[Oracle]()中可以使用JOB来实现一些任务的自动化执行，类似于UNIX操作系统crontab命令的功能。 简单演示一下，供参考。 1.创建表T，包含一个X字段，定义为日期类型，方便后面的定时任务测试。 sec@ora10g> create table t (x date); Table created. 2.创建存储过程p_insert_into_t，每次执行该存储过程都会向T表中插入一条系统当前时间。 sec@ora10g> create or replace procedure p_insert_into_t 2 as 3 begin 4 insert into t 5 values (SYSDATE); 6 end; 7 / Procedure created. 3.OK，准备就绪，我们来创建一个JOB，这个JOB会每分钟运行一次？需要注意一个细节！ sec@ora10g> variable job_number number; sec@ora10g> begin 2 DBMS_JOB.submit (:job_number, 3 'P_INSERT_INTO_T;', 4 SYSDATE, 5 'sysdate+1/(24/*60)'); 6 end; 7 / PL/SQL procedure successfully completed. 4.我们通过USER_JOBS视图查看一下创建的JOB信息。 sec@ora10g> select job, 2 log_user, 3 to_char(last_date,'yyyy-mm-dd hh24:mi:ss') last_date, 4 to_char(next_date,'yyyy-mm-dd hh24:mi:ss') next_date, 5 interval, 6 what 7 from user_jobs 8 / JOB LOG_USER LAST_DATE NEXT_DATE INTERVAL WHAT

---

 27 SEC                          2010-01-29 00:34:20 sysdate+1/(24/*60) P_INSERT_INTO_T;

细节之处在此，此处的LAST_DATE内容是空，表示此JOB没有被执行过，因此这个JOB将永远不会被自动的执行。 这一点可以从T表没有数据来得到验证： sec@ora10g> select / from t; no rows selected 那么，如何才能使它自动执行起来呢？ 很简单，只要我们手动将这个JOB执行一下即可。 5.手工执行JOB一次，使之按照既定的时间间隔执行。 sec@ora10g> execute dbms_job.run(27); PL/SQL procedure successfully completed. 此时T表中将会被插入一条具有当前时间的数据。 sec@ora10g> select / from t;

X

2010-01-29 00:37:42 再次查看JOB的信息 sec@ora10g> select job, 2 log_user, 3 to_char(last_date,'yyyy-mm-dd hh24:mi:ss') last_date, 4 to_char(next_date,'yyyy-mm-dd hh24:mi:ss') next_date, 5 interval, 6 what 7 from user_jobs 8 / JOB LOG_USER LAST_DATE NEXT_DATE INTERVAL WHAT

---

 27 SEC      2010-01-29 00:37:42 2010-01-29 00:38:42 sysdate+1/(24/*60) P_INSERT_INTO_T;

此时LAST_DATE显示了我们执行JOB的时间，同时NEXT_DATE显示了下次JOB将被执行的时间。此后这个JOB将会每隔一分钟被执行一次。 自动执行一段时间后的T表内容如下： sec@ora10g> select /* from t order by x;

X

2010-01-29 00:37:42 2010-01-29 00:38:46 2010-01-29 00:39:46 2010-01-29 00:40:46 2010-01-29 00:41:46 2010-01-29 00:42:46 2010-01-29 00:43:46 2010-01-29 00:44:46 2010-01-29 00:45:46 2010-01-29 00:46:46 2010-01-29 00:47:46 2010-01-29 00:48:46 2010-01-29 00:49:46 2010-01-29 00:50:46 2010-01-29 00:51:46 2010-01-29 00:52:46 16 rows selected. 6.为什么刚刚创建后的JOB不能自动的执行呢？ 这是一个疏忽导致的！ 在创建JOB的时候，需要在结尾处指定“COMMIT;”！表示创建完成之后便执行一次。 删除之前的JOB，重新创建一个带有“COMMIT”语句的新JOB。 sec@ora10g> variable job_number number; sec@ora10g> begin 2 DBMS_JOB.submit (:job_number, 3 'P_INSERT_INTO_T;', 4 SYSDATE, 5 'sysdate+1/(24/*60)'); 6 commit; 7 end; 8 / sec@ora10g> print job_number;

JOB_NUMBER

    29

此次创建的JOB信息如下，可见LAST_DATE在创建完之后便有内容，表示已经被执行了一次。 sec@ora10g> select job, 2 log_user, 3 to_char(last_date,'yyyy-mm-dd hh24:mi:ss') last_date, 4 to_char(next_date,'yyyy-mm-dd hh24:mi:ss') next_date, 5 interval, 6 what 7 from user_jobs 8 / JOB LOG_USER LAST_DATE NEXT_DATE INTERVAL WHAT

---

 29 SEC      2010-01-29 01:02:11 2010-01-29 01:03:11 sysdate+1/(24/*60) P_INSERT_INTO_T;

一分钟过后便可看到T表中已有两条记录。 sec@ora10g> select /* from t;

X

2010-01-29 01:02:11 2010-01-29 01:03:11 7.删除JOB方法 很简单，使用“dbms_job.remove”即可。 sec@ora10g> execute dbms_job.remove(29); PL/SQL procedure successfully completed. 8.最后，谈一下创建JOB时用到的参数。 1）使用desc命令查看DBMS_JOB，可以得到SUBMIT这个存储过程的参数列表。 sec@ora10g> desc DBMS_JOB ... PROCEDURE SUBMIT Argument Name Type In/Out Default?

---

JOB BINARY_INTEGER OUT WHAT VARCHAR2 IN NEXT_DATE DATE IN DEFAULT INTERVAL VARCHAR2 IN DEFAULT NO_PARSE BOOLEAN IN DEFAULT INSTANCE BINARY_INTEGER IN DEFAULT FORCE BOOLEAN IN DEFAULT ... 2）如果希望对这些参数有更好的理解，可以参考Oracle的官方文档描述，细致而周到。 http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_job.htm/#sthref2936 3）重点关注一下官方文档中关于INTERVAL参数的示例 'sysdate + 7'表示一周执行一次； 'next_day(sysdate,''TUESDAY'')' 表示每周二执行一次； 'null'表示只执行一次。 本文中我使用的是'sysdate+1/(24/60)'表示每分钟执行一次。很形象，一天的二十四分之一是一小时，一小时的六十分之一就是一分钟的意思。 9.小结 通过这个文章和大家分享了一点关于JOB的创建方法和使用，希望对大家有帮助。 细节不容错过！ Good luck. **[secooler*]() 10.01.28 -- The End --

http://space.itpub.net/519536/viewspace-626310/

全部脚印 不留脚印 留下脚印:

51317

baojh

淡定的DBA

29112063

hwayw

yhj20041128001

29036651

ITPUB博客管理员

导入论坛 引用链接 收藏 分享给好友 推荐到圈子 管理 举报

TAG: 引用 删除 Guest / 2013-08-08 14:56:40 评 5 分

查看全部评论

-5 -3 -1 - +1 +3 +5

评分：0

我来说两句

显示全部

内容

昵称

验证

提交评论

leegstar

用户菜单

• 给我留言
• 加入好友
• 发短消息
• 我的介绍
• 论坛资料

• 空间管理

  我的栏目

• oracle

• linux
• 虚拟机
• PL/SQL
• mysql
• Hadoop
• svn

标题搜索

日历

« 2013-08-13 日 一 二 三 四 五 六 12345678910111213141516171819202122232425262728293031

我的存档

• 2013年08月 2013年07月
• 2013年06月 2013年05月
• 2013年04月 2013年03月
• 2013年02月 2013年01月
• 2012年12月 2012年11月
• 2012年10月 2012年09月
• 2012年08月 2012年07月
• 2012年06月 2012年05月
• 2012年04月 2012年03月
• 2012年02月 2012年01月
• 2011年12月

• 查看所有存档

  数据统计

• 访问量: 3312

• 日志数: 29
• 建立时间: 2011-12-28
• 更新时间: 2013-08-07

RSS订阅

清空Cookie - 联系我们 - ITPUB个人空间 - 交流论坛 - 空间列表 - 站点存档 - 升级自己的空间

Powered by X-Space 3.0.2 © 2001-2007 Comsenz Inc. 京ICP证:010037号网站统计 Open Toolbar

oracle index的使用我的记事本百度空间 (2)

分享到

• QQ空间
• 新浪微博
• 百度搜藏
• 人人网
• 腾讯微博
• 开心网
• 腾讯朋友
• 百度空间
• 豆瓣网
• 搜狐微博
• 百度新首页
• QQ收藏
• 和讯微博
• 我的淘宝
• 百度贴吧
• 更多...

百度分享

• 广场
• jywv *
• 退出 关注此空间

  我的记事本

2011-07-23 10:13

oracle index的使用

Oracle提供了大量索引选项。知道在给定条件下使用哪个选项对于一个应用程序的性能来说非常重要。一个错误的选择可能会引发死锁，并导致数据库性能急剧下降或进程终止。而如果做出正确的选择，则可以合理使用资源，使那些已经运行了几个小时甚至几天的进程在几分钟得以完成，这样会使您立刻成为一位英雄。这篇文章就将简单的讨论每个索引选项。主要有以下内容：

[1]基本的索引概念

查询DBA_INDEXES视图可得到表中所有索引的列表，注意只能通过USER_INDEXES的方法来检索模式(schema)的索引。访问USER_IND_COLUMNS视图可得到一个给定表中被索引的特定列。

[2]组合索引

当某个索引包含有多个已索引的列时，称这个索引为组合（concatented）索引。在Oracle9i引入跳跃式扫描的索引访问方法之前，查询只能在有限条件下使用该索引。比如：表emp有一个组合索引键，该索引包含了empno、ename和deptno。在Oracle9i之前除非在where之句中对第一列（empno）指定一个值，否则就不能使用这个索引键进行一次范围扫描。

特别注意：在Oracle9i之前，只有在使用到索引的前导索引时才可以使用组合索引！

[3] ORACLE ROWID

通过每个行的ROWID，索引Oracle提供了访问单行数据的能力。ROWID其实就是直接指向单独行的线路图。如果想检查重复值或是其他对ROWID本身的引用，可以在任何表中使用和指定rowid列。

[4]限制索引

限制索引是一些没有经验的开发人员经常犯的错误之一。在SQL中有很多陷阱会使一些索引无法使用。下面讨论一些常见的问题：

4.1使用不等于操作符（<>、!=）

   下面的查询即使在cust_rating列有一个索引，查询语句仍然执行一次全表扫描。

     select cust_Id,cust_name

     from   customers

     where  cust_rating <> 'aa';

    把上面的语句改成如下的查询语句，这样，在采用基于规则的

    优化器而不是基于代价的优化器（更智能）时，将会使用索引。

     select cust_Id,cust_name<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

     from   customers

     where  cust_rating < 'aa' or cust_rating > 'aa';

特别注意：通过把不等于操作符改成OR条件，就可以使用索引，以避免全表扫描。

 4.2使用IS NULL或IS NOT NULL

使用IS NULL或IS NOT NULL同样会限制索引的使用。因为NULL值并没有被定义。在SQL语句中使用NULL会有很多的麻烦。因此建议开发人员在建表时，把需要索引的列设成NOT NULL。如果被索引的列在某些行中存在NULL值，就不会使用这个索引（除非索引是一个位图索引，关于位图索引在稍后在详细讨论）。

4.3使用函数

如果不使用基于函数的索引，那么在SQL语句的WHERE子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。下面的查询不会使用索引（只要它不是基于函数的索引）

      select empno,ename,deptno

      from   emp

      where  trunc(hiredate)='01-MAY-81';

     把上面的语句改成下面的语句，这样就可以通过索引进行查找。

      select empno,ename,deptno

      from   emp

      where  hiredate<(to_date('01-MAY-81')+0.9999);



 4.4比较不匹配的数据类型

    比较不匹配的数据类型也是比较难于发现的性能问题之一。

    注意下面查询的例子，account_number是一个VARCHAR2类型，

    在account_number字段上有索引。下面的语句将执行全表扫描。

     select bank_name,address,city,state,zip

     from   banks

     where  account_number = 990354;

     Oracle可以自动把where子句变成to_number(account_number)=990354，这样就限制了

     索引的使用,改成下面的查询就可以使用索引：

     select bank_name,address,city,state,zip

     from   banks

     where  account_number ='990354';

特别注意：不匹配的数据类型之间比较会让Oracle自动限制索引的使用，

   即便对这个查询执行Explain Plan也不能让您明白为什么做了一次“全表扫描”。

[5]选择性

使用USER_INDEXES视图，该视图中显示了一个distinct_keys列。比较一下唯一键的数量和表中的行数，就可以判断索引的选择性。选择性越高，索引返回的数据就越少。

[6]群集因子(Clustering Factor)

Clustering Factor位于USER_INDEXES视图中。该列反映了数据相对于已索引的列是否显得有序。如果Clustering Factor列的值接近于索引中的树叶块(leaf block)的数目，表中的数据就越有序。如果它的值接近于表中的行数，则表中的数据就不是很有序。

[7]二元高度(Binary height)

索引的二元高度对把ROWID返回给用户进程时所要求的I/O量起到关键作用。在对一个索引进行分析后，可以通过查询DBA_INDEXES的B- level列查看它的二元高度。二元高度主要随着表的大小以及被索引的列中值的范围的狭窄程度而变化。索引上如果有大量被删除的行，它的二元高度也会增加。更新索引列也类似于删除操作，因为它增加了已删除键的数目。重建索引可能会降低二元高度。

[8]快速全局扫描

在Oracle7.3后就可以使用快速全局扫描(Fast Full Scan)这个选项。这个选项允许Oracle执行一个全局索引扫描操作。快速全局扫描读取B-树索引上所有树叶块。初始化文件中的DB_FILE_MULTIBLOCK_READ_COUNT参数可以控制同时被读取的块的数目。

[9]跳跃式扫描

从Oracle9i开始，索引跳跃式扫描特性可以允许优化器使用组合索引，即便索引的前导列没有出现在WHERE子句中。索引跳跃式扫描比全索引扫描要快的多。下面的程序清单显示出性能的差别：

create index skip1 on emp5(job,empno);

index created.



select count(/*)

from emp5

where empno=7900;



Elapsed:00:00:03.13



Execution Plan

0     SELECT STATEMENT Optimizer=CHOOSE(Cost=4 Card=1 Bytes=5)

1  0    SORT(AGGREGATE)

2  1      INDEX(FAST FULL SCAN) OF 'SKIP1'(NON-UNIQUE)



Statistics



6826 consistent gets

6819 physical   reads



select //*+ index(emp5 skip1)/*/ count(/*)

from emp5

where empno=7900;



Elapsed:00:00:00.56



Execution Plan

0     SELECT STATEMENT Optimizer=CHOOSE(Cost=6 Card=1 Bytes=5)

1  0    SORT(AGGREGATE)

2  1      INDEX(SKIP SCAN) OF 'SKIP1'(NON-UNIQUE)



Statistics



21 consistent gets

17 physical   reads

[10]索引的类型

 B-树索引

位图索引

 HASH索引

索引编排表

反转键索引

基于函数的索引

分区索引

本地和全局索引

如果一个表有大量数据被删除 需要rebuild索引，不然索引文件会变大 /#Oralce

分享到：

举报 浏览(153) 评论 转载

您可能也喜欢

评论

同时评论给

同时评论给原文作者

发布

500/0

收起|查看更多

帮助中心 | 空间客服 | 投诉中心 | 空间协议

©2012 Baidu

Oracle 10g SQL 优化再学习 - joe --专注java,开源,架构,项目管理 - BlogJava

joe --专注java,开源,架构,项目管理

STANDING ON THE SHOULDERS OF GIANTS

posts - 339, comments - 292, trackbacks - 0, articles - 1 BlogJava :: 首页 :: 新随笔 :: 联系 :: 聚合 :: 管理 ## Oracle 10g SQL 优化再学习

Posted on 2010-10-10 23:52 @joe 阅读(107) 评论(0) 编辑 收藏 所属分类: java性能 、方法论 、架构

从8i到10g，Oracle不断进化自己的SQL Tuning智能，一些秘籍级的优化口诀已经失效。 但我喜欢失效，不用记口诀，操个Toad for Oracle Xpert ，按照大方向舒舒服服的调优才是爱做的事情。

1.Excution Plan Excution Plan是最基本的调优概念，不管你的调优吹得如何天花乱堕，结果还是要由Excution plan来显示Oracle 最终用什么索引、按什么顺序连接各表，Full Table Scan还是Access by Rowid Index，瓶颈在什么地方。如果没有它的指导，一切调优都是蒙的。

2.Toad for Oracle Xpert 用它来调优在真的好舒服。Quest 吞并了Lecco后，将它整合到了Toad 的SQL Tunning里面：最清晰的执行计划显示，自动生成N条等价SQL、给出优化建议，不同SQL执行计划的对比，还有实际执行的逻辑读、物理读数据等等一目了然。

3.索引 大部分的性能问题其实都是索引应用的问题，Where子句、Order By、Group By 都要用到索引。 一般开发人员认为将索引建全了就可以下班回家了，实则还有颇多的思量和陷阱。

3.1 索引列上不要进行计算 这是最最普遍的失效陷阱，比如where trunc(order_date)=trunc(sysdate), i+2>4。索引失效的原因也简单，索引是针对原值建的二叉树，你将列值/*3/4+2折腾一番后，原来的二叉树当然就用不上了。解决的方法: 1.　换成等价语法，比如trunc(order_date) 换成

where order_date>trunc(sysdate)-1 and order_date<trunc(sysdate)+1　 2. 特别为计算建立函数索引

create index Ｉ_XXXX on shop_order(trunc(order_date)) 3. 将计算从等号左边移到右边 这是针对某些无心之失的纠正，把a/*2>4　改为a>4/2；把TO_CHAR(zip) = '94002' 改为zip = TO_NUMBER('94002');

3.2 CBO与索引选择性 建了索引也不一定会被Oracle用的，就像个挑食的孩子。基于成本的优化器(CBO, Cost-Based Optimizer)，会先看看表的大小，还有索引的重复度，再决定用还是不用。表中有100 条记录而其中有80 个不重复的索引键值. 这个索引的选择性就是80/100 = 0.8，留意Toad里显示索引的Selective和Cardinailty。实在不听话时，就要用hints来调教。 另外，where语句存在多条索引可用时，只会选择其中一条。所以索引也不是越多越好：）

3.3 索引重建 传说中数据更新频繁导致有20%的碎片时，Oracle就会放弃这个索引。宁可信其有之下，应该时常alter index rebuild一下。

3.4 其他要注意的地方 不要使用Not，如goods_no != 2，要改为

where goods_no>2 or goods_no<2 不要使用is null , 如WHERE DEPT_CODE IS NOT NULL 要改为

WHERE DEPT_CODE >=0;3.5 select 的列如果全是索引列时 又如果没有where 条件，或者where条件全部是索引列时，Oracle 将直接从索引里获取数据而不去读真实的数据表，这样子理论上会快很多，比如

select order_no,order_time from shop_order where shop_no=4当order_no,order_time,shop_no 这三列全为索引列时，你将看到一个和平时完全不同的执行计划。

3.6 位图索引 传说中当数据值较少，比如某些表示分类、状态的列，应该建位图索引而不是普通的二叉树索引，否则效率低下。不过看执行计划，这些位图索引鲜有被Oracle临幸的。

4.减少查询往返和查询的表 这也是很简单的大道理，程序与Oracle交互的成本极高，所以一个查询能完成的不要分开两次查，如果一个循环执行１万条查询的，怎么都快不到哪里去了。

4.1 封装PL/SQL存储过程 最高级的做法是把循环的操作封装到PL/SQL写的存储过程里，因为存储过程都在服务端执行，所以没有数据往返的消耗。

4.2 封装PL/SQL内部函数 有机会，将一些查询封装到函数里，而在普通SQL里使用这些函数，同样是很有效的优化。

4.3 Decode/Case 但存储过程也麻烦，所以有case/decode把几条条件基本相同的重复查询合并为一条的用法：

SELECT COUNT(CASE WHEN price < 13 THEN 1 ELSE null END) low, COUNT(CASE WHEN price BETWEEN 13 AND 15 THEN 1 ELSE null END) med, COUNT(CASE WHEN price > 15 THEN 1 ELSE null END) high FROM products;4.4 一种Where/Update语法

SELECT TAB_NAME　FROM TABLES WHERE (TAB_NAME,DB_VER) = （( SELECT TAB_NAME,DB_VER) FROM TAB_COLUMNS WHERE VERSION = 604)

UPDATE EMP SET (EMP_CAT, SAL_RANGE) = (SELECT MAX(CATEGORY)FROM EMP_CATEGORIES) 5.其他优化 5.1RowID和ROWNUM 连Hibernate 新版也支持ROWID了，证明它非常有用。比如号称删除重复数据的最快写法：

DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);6.终极秘技 - Hints 这是Oracle DBA的玩具，也是终极武器，比如Oracle在CBO,RBO中所做的选择总不合自己心水时，可以用它来强力调教一下Oracle，结果经常让人喜出望外。 如果开发人员没那么多时间来专门学习它，可以依靠Toad SQL opmitzer 来自动生成这些提示，然后对比一下各种提示的实际效果。不过随着10g智能的进化，hints的惊喜少了。

1. 找出要优化的Top SQL 磨了这么久的枪，如果找不到敌人是件郁闷的事情。 幸亏10g这方面做得非常好。进入Web管理界面，就能看到当前或者任意一天的SQL列表，按性能排序。 有了它，SQL Trace和TKPROF都可以不用了。

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/calvinxiu/archive/2005/11/15/529756.aspx

新用户注册 刷新评论列表

找优秀程序员，就在博客园 IT新闻： · 婚恋网世纪佳缘乱象重生 成一夜情猎场 · 帝国时代 Online 已发布，可免费下载 · 小米发布会中的亮点与尿点：“狗日的 1999” · 315投诉网疑被关后改名重开张 新网站否认 · 团购网站融资冷却裁员求生 行业洗牌危机并存 博客园 博问 IT新闻 Java程序员招聘 标题 请输入标题 姓名 请输入你的姓名 主页 请输入验证码 验证码 /* 内容(请不要发表任何与政治相关的内容) 请输入评论内容 Remember Me? 登录 [使用Ctrl+Enter键可以直接提交] 推荐职位： · 广州ASP.NET程序员(广州丹霄信息技术) · 上海 .NET软件工程师(上海苏秦网络) · 上海.NET软件开发工程师(东方财富信息) · 北京 SQL数据库开发工程师(圣特尔科技) · 北京高新诚聘 ASP.NET 程序员(盈科融通软件) · 北京 .NET软件工程师(北京科胜永昌) · 北京.NET 研发工程师 (北京捷报数据) · 北京C/#开发工程师 B/S方向(圣特尔科技)

博客园首页随笔： · C/#设计模式——命令模式(Command Pattern) · 老系统维护 · 使用单例模式实现自己的HttpClient工具类 · Spread for Windows Forms高级主题(2)---理解单元格类型 · ERP/MIS开发 菜单设计器(Menu Designer)及其B/S,C/S双重实现（B/S开源） 知识库： · IT项目管理的六种错误思维 · 我的10个开发原则 · 大数据下的数据分析平台架构 · 为什么编程是独一无二的职业 · 分享8年开发经验，浅谈个人发展经历，明确自己发展方向 最简洁阅读版式： Oracle 10g SQL 优化再学习 网站导航:

博客园 IT新闻 知识库 博客生活 IT博客网 C++博客 博问 管理 相关文章:

• 转载一篇文章oracle的一些操作
• 过v$sqlarea,v$sql查询最占用资源的查询
• JVM内存模型以及垃圾回收
• 加速Javascript：DOM操作优化
• Tomcat内存溢出的三种情况及解决办法分析
• Tomcat启动分析server.xml
• Java虚拟机参数 -XX等相关参数应用
• Oracle 10g SQL 优化再学习
• google Analytics 初探
• 使用tomcat的compression来提高网页加载速度 Powered by: BlogJava Copyright © @joe

公告

老婆的淘宝,欢迎选购

留言簿(2)

• 给我留言
• 查看公开留言
• 查看私人留言

随笔分类

• ")all 生活杂谈(14)
• ")android(18)
• ")apache项目(13)
• ")chart(1)
• ")concurrent(1)
• ")database(34)
• ")dwr(3)
• ")flex(1)
• ")hibernate(23)
• ")java (123)
• ")javafx(1)
• ")java安全(6)
• ")java性能(12)
• ")jbpm(1)
• ")jquery(3)
• ")linux(6)
• ")lucene(1)
• ")lucene(1)
• ")others(2)
• ")questions(7)
• ")questions_hander(5)
• ")spring(24)
• ")struts(8)
• swing
• ")UML(2)
• ")web(33)
• ")webservice(5)
• ")xml(2)
• ")敏捷(6)
• ")方法论(14)
• ")架构(10)
• ")网络通讯(5)
• ")项目管理(17)

相册

• 我的相册

搜索

积分与排名

• 积分 - 249820
• 排名 - 69

最新评论

• 1. re: 一篇不错的讲解Java异常的文章(转载）----感觉很不错，读了以后很有启发[未登录]
• 受教了。非常感谢。 不过，后面罗列的那么多异常有点不必要了。
• --jake
• 2. re: strtuts2 异常之Could not create and/or set value back on to object [未登录]
• thank you very much！
• --fighting
• 3. re: Android屏幕元素层次结构[未登录]

• 顶一下

• --黑石
• 4. re: 不同技术团队的配合问题及DevOps(不错的文章，来自infoq)
• 做个记号。
• --何杨
• 5. re: other[未登录]
• 希望学习，谢谢！ trust_myself@126.com
• --asa

阅读排行榜

• 1. 一篇不错的讲解Java异常的文章(转载）----感觉很不错，读了以后很有启发(17440)
• 2. 世界上最健康的作息时间表 大家对比下(16717)
• 3. ibatis学习（三）---ibatis与spring的整合(13592)
• 4. 如何用javascript控制checkbox，并进行批量删除(8527)
• 5. ibatis学习（二）--ibatis使用介绍(6368)

评论排行榜

• 1. other(41)
• 2. 一篇不错的讲解Java异常的文章(转载）----感觉很不错，读了以后很有启发(22)
• 3. ssh中利用pager-taglib和filter进行分页(14)
• 4. 团队内是有必要统一IDE(14)
• 5. 世界上最健康的作息时间表 大家对比下(13)

60天内阅读排行

• 1. 大家都用什么bug管理软件？(2031)
• 2. CountDownLatch 简介和例子(1412)
• 3. 统计指标和术语汇总(1396)
• 4. 不同技术团队的配合问题及DevOps(不错的文章，来自infoq)(1321)
• 5. connection.release_mode，connection.autocommit和transaction.auto_close_session用法(1003)

oracle的表空间，分区表，以及索引的总结开拓者百度空间

分享到

• QQ空间
• 新浪微博
• 百度搜藏
• 人人网
• 腾讯微博
• 开心网
• 腾讯朋友
• 百度空间
• 豆瓣网
• 搜狐微博
• 百度新首页
• QQ收藏
• 和讯微博
• 我的淘宝
• 百度贴吧
• 更多...

百度分享

• 广场
• jywv *
• 退出 关注此空间

  开拓者

开拓者

2008-06-02 22:46

oracle的表空间，分区表，以及索引的总结

表空间： Oracle的UNDOTBS01.DBF文件太大的解决办法 1、.禁止undo tablespace自动增长 alter database datafile 'full_path\undotbs01.dbf' autoextend off; 2.-- 创建一个新的小空间的undo tablespace create undo tablespace undotBS2 datafile 'full_path\UNDOTBS02.DBF' size 100m; -- 设置新的表空间为系统undo_tablespace alter system set undo_tablespace=undotBS2; -- Drop 旧的表空间 drop tablespace undotbs1 including contents; --查看所有表空间的情况 select / from dba_tablespaces --创建表空间 create tablespace HRPM0 datafile '/oradata/misdb/HRPM0.DBF' size 5m autoextend on next 10m maxsize unlimited --删除表空间 DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES; --修改表空间大小 alter database datafile '/path/NADDate05.dbf' resize 100M 分区表： 当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。 Oracle中提供了以下几种表分区： 一、范围分区：这种类型的分区是使用列的一组值，通常将该列成为分区键。 示例1：假设有一个CUSTOMER表，表中有数据200000行，我们将此表通过CUSTOMER_ID进行分区，每个分区存储100000行，我们将每个分区保存到单独的表空间中，这样数据文件就可以跨越多个物理磁盘。下面是创建表和分区的代码，如下： CREATE TABLE CUSTOMER ( CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY, FIRST_NAME VARCHAR2(30) NOT NULL, LAST_NAME VARCHAR2(30) NOT NULL, PHONE VARCHAR2(15) NOT NULL, EMAIL VARCHAR2(80), STATUS CHAR(1) ) PARTITION BY RANGE (CUSTOMER_ID) ( PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE CUS_TS01, PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE CUS_TS02 ) 注意：在创建表进行分区时，表空间必须先存在，而且建议将不同的分区放入不同的表空间中。 示例2：假设有ORDER_ACTIVITIES表，每6个月对订单进行清理，我们可以按月份对表进行分区，分区代码如下： CREATE TABLE ORDER_ACTIVITIES ( ORDER_ID NUMBER(7) NOT NULL, ORDER_DATE DATE, TOTAL_AMOUNT NUMBER, CUSTOTMER_ID NUMBER(7), PAID CHAR(1) ) PARTITION BY RANGE (ORDER_DATE) ( PARTITION ORD_ACT_PART01 VALUES LESS THAN (TO_DATE('01-MAY-2003','DD-MON-YYYY')) TABLESPACE ORD_TS01, PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUN-2003','DD-MON-YYYY')) TABLESPACE ORD_TS02, PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUL-2003','DD-MON-YYYY')) TABLESPACE ORD_TS03 ) 二、列表分区：该分区的特点是某列的值只有几个，基于这样的特点我们可以采用列表分区。 示例1： CREATE TABLE PROBLEM_TICKETS ( PROBLEM_ID NUMBER(7) NOT NULL PRIMARY KEY, DESCRIPTION VARCHAR2(2000), CUSTOMER_ID NUMBER(7) NOT NULL, DATE_ENTERED DATE NOT NULL, STATUS VARCHAR2(20) ) PARTITION BY LIST (STATUS) ( PARTITION PROB_ACTIVE VALUES ('ACTIVE') TABLESPACE PROB_TS01, PARTITION PROB_INACTIVE VALUES ('INACTIVE') TABLESPACE PROB_TS02 ) 三、散列分区：这类分区是在列值上使用散列算法，以确定将行放入哪个分区中。当列的值没有合适的条件时，建议使用散列分区。请看下列示例： 示例1： CREATE TABLE HASH_TABLE ( COL NUMBER(8), INF VARCHAR2(100) ) PARTITION BY HASH (COL) ( PARTITION PART01 TABLESPACE HASH_TS01, PARTITION PART02 TABLESPACE HASH_TS02, PARTITION PART03 TABLESPACE HASH_TS03 ) 四、复合范围列表分区：这种分区是基于范围分区和列表分区，表首先按某列进行范围分区，然后再按某列进行列表分区，分区之中的分区被称为子分区。 示例1： CREATE TABLE SALES ( PRODUCT_ID VARCHAR2(5), SALES_DATE DATE, SALES_COST NUMBER(10), STATUS VARCHAR2(20) ) PARTITION BY RANGE(SALES_DATE) SUBPARTITION BY LIST (STATUS) ( PARTITION P1 VALUES LESS THAN (TO_DATE('2003-01-01','YYYY-MM-DD')) TABLESPACE P1_TS ( SUBPARTITION P1SUB1 VALUES ('ACTIVE') TABLESPACE SUBP1_TS1, SUBPARTITION P1SUB2 VALUES ('INACTIVE') TABLESPACE SUBP1_TS2 ), PARTITION P2 VALUES LESS THAN (TO_DATE('2003-03-01','YYYY-MM-DD')) TABLESPACE P2_TS ( SUBPARTITION P2SUB1 VALUES ('ACTIVE') TABLESPACE SUBP2_TS1, SUBPARTITION P2SUB2 VALUES ('INACTIVE') TABLESPACE SUBP2_TS2 ) ) 示例2：使用TEMPLATE模板 CREATE TABLE SALES ( PRODUCT_ID VARCHAR2(5), SALES_DATE DATE, SALES_COST NUMBER(10), STATUS VARCHAR2(20) ) PARTITION BY RANGE(SALES_DATE) SUBPARTITION BY LIST (STATUS) SUBPARTITION TEMPLATE ( SUBPARTITION SUB1 VALUES ('ACTIVE') TABLESPACE SUBP1_TS1, SUBPARTITION SUB2 VALUES ('INACTIVE') TABLESPACE SUBP2_TS2 ) ( PARTITION P1 VALUES LESS THAN (TO_DATE('2003-01-01','YYYY-MM-DD')) TABLESPACE P1_TS, PARTITION P2 VALUES LESS THAN (TO_DATE('2003-03-01','YYYY-MM-DD')) TABLESPACE P2_TS ) 五、复合范围散列分区：这种分区是基于范围分区和散列分区，表首先按某列进行范围分区，然后再按某列进行散列分区。与上面的定义方式非常的类似，在此不单独举例。 表分区对于用户来说是透明的，我们在插入数据时Oracle会自动判断插入的数据，然后放入相应的表分区中。但有时我们想单独查询某个分区中的数据时，就必须手工指定分区的名称。 示例1：(此示例基于：四、复合范围列表分区的示例一) 向SALES表插入记录，不必指定表分区。 INSERT INTO SALES VALUES('00001','01-1月-02',100,'ACTIVE') / INSERT INTO SALES VALUES('00002','01-1月-01',200,'ACTIVE') / INSERT INTO SALES VALUES('00003','01-2月-03',300,'INACTIVE') / INSERT INTO SALES VALUES('00004','04-2月-03',300,'INACTIVE') / INSERT INTO SALES VALUES('00005','04-2月-02',300,'INACTIVE') / 不指定表分区查看SALES表信息： SELECT / FROM SALES; 结果如下所示： 指定P1表分区查询SALES表信息： SELECT / FROM SALES PARTITION(P1); 结果如下所示： 指定P1SUB1子分区查询SALES表信息: SELECT / FROM SALES SUBPARTITION(P1SUB1); 结果如下所示： 示例2：(此示例基于：四、复合范围列表分区的示例二) 示例2基于TEMPLATE模板的表分区，查询稍稍烦琐一点。 指定P1表分区查询SALES表信息： SELECT / FROM SALES PARTITION(P1); 结果如下所示,和刚才查询一致。 指定SUB1子分区查询SALES表信息: SELECT / FROM SALES SUBPARTITION(SUB1); 出现如下错误信息： 怎么解决以上问题呢？我们通过sys模式查看分区信息的数据字典,如下： 可以看出子分区不叫SUB1，而是P1_SUB1,重新查询信息，如下图所示： 有关表分区的一些维护性操作： 一、添加分区 以下代码给SALES表添加了一个P3分区 ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2003-06-01','YYYY-MM-DD')); 注意：以上添加的分区界限应该高于最后一个分区界限。 以下代码给SALES表的P3分区添加了一个P3SUB1子分区 ALTER TABLE SALES MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES('COMPLETE'); 二、删除分区 以下代码删除了P3表分区： ALTER TABLE SALES DROP PARTITION P3; 在以下代码删除了P4SUB1子分区： ALTER TABLE SALES DROP SUBPARTITION P4SUB1; 注意：如果删除的分区是表中唯一的分区，那么此分区将不能被删除，要想删除此分区，必须删除表。 三、截断分区 截断某个分区是指删除某个分区中的数据，并不会删除分区，也不会删除其它分区中的数据。当表中即使只有一个分区时，也可以截断该分区。通过以下代码截断分区： ALTER TABLE SALES TRUNCATE PARTITION P2; 通过以下代码截断子分区： ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2; 四、合并分区 合并分区是将相邻的分区合并成一个分区，结果分区将采用较高分区的界限，值得注意的是，不能将分区合并到界限较低的分区。以下代码实现了P1 P2分区的合并： ALTER TABLE SALES MERGE PARTITIONS P1,P2 INTO PARTITION P2; 五、拆分分区 拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分。 ALTER TABLE SALES SBLIT PARTITION P2 AT(TO_DATE('2003-02-01','YYYY-MM-DD')) INTO (PARTITION P21,PARTITION P22); 六、接合分区(coalesca) 结合分区是将散列分区中的数据接合到其它分区中，当散列分区中的数据比较大时，可以增加散列分区，然后进行接合，值得注意的是，接合分区只能用于散列分区中。通过以下代码进行接合分区： ALTER TABLE SALES COALESCA PARTITION; 七、重命名表分区 以下代码将P21更改为P2 ALTER TABLE SALES RENAME PARTITION P21 TO P2; 九、跨分区查询 select sum( /) from ( (select count(/) cn from t_table_SS PARTITION (P200709_1) union all select count(/) cn from t_table_SS PARTITION (P200709_2)); 十、查询表上有多少分区 SELECT / FROM useR_TAB_PARTITIONS WHERE TABLE_NAME='tableName' 十一、查询索引信息 select object_name,object_type,tablespace_name,sum(value) from v$segment_statistics where statistic_name IN ('physical reads','physical write','logical reads')and object_type='INDEX' group by object_name,object_type,tablespace_name order by 4 desc --显示数据库所有分区表的信息： select / from DBA_PART_TABLES --显示当前用户可访问的所有分区表信息: select / from ALL_PART_TABLES --显示当前用户所有分区表的信息： select / from USER_PART_TABLES --显示表分区信息 显示数据库所有分区表的详细分区信息： select / from DBA_TAB_PARTITIONS --显示当前用户可访问的所有分区表的详细分区信息： select / from ALL_TAB_PARTITIONS --显示当前用户所有分区表的详细分区信息： select / from USER_TAB_PARTITIONS --显示子分区信息 显示数据库所有组合分区表的子分区信息： select / from DBA_TAB_SUBPARTITIONS --显示当前用户可访问的所有组合分区表的子分区信息： select / from ALL_TAB_SUBPARTITIONS --显示当前用户所有组合分区表的子分区信息： select / from USER_TAB_SUBPARTITIONS --显示分区列 显示数据库所有分区表的分区列信息： select / from DBA_PART_KEY_COLUMNS --显示当前用户可访问的所有分区表的分区列信息： select / from ALL_PART_KEY_COLUMNS --显示当前用户所有分区表的分区列信息： select / from USER_PART_KEY_COLUMNS --显示子分区列 显示数据库所有分区表的子分区列信息： select / from DBA_SUBPART_KEY_COLUMNS --显示当前用户可访问的所有分区表的子分区列信息： select / from ALL_SUBPART_KEY_COLUMNS --显示当前用户所有分区表的子分区列信息： select / from USER_SUBPART_KEY_COLUMNS --怎样查询出oracle数据库中所有的的分区表 select / from user_tables a where a.partitioned='YES' --删除一个表的数据是 truncate table table_name; --删除分区表一个分区的数据是 alter table table_name truncate partition p5; 注：分区根据具体情况选择。 表分区有以下优点： 1、数据查询：数据被存储到多个文件上，减少了I/O负载，查询速度提高。 2、数据修剪：保存历史数据非常的理想。 3、备份：将大表的数据分成多个文件，方便备份和恢复。

4、并行性：可以同时向表中进行DML操作，并行性性能提高。

索引： 1、一般索引： create index index_name on table(col_name); 2、Oracle 分区索引详解 语法：Table Index CREATE [UNIQUE|BITMAP] INDEX [schema.]index_name ON [schema.]table_name [tbl_alias] (col [ASC | DESC]) index_clause index_attribs index_clauses: 分以下两种情况

1. Local Index 就是索引信息的存放位置依赖于父表的Partition信息，换句话说创建这样的索引必须保证父表是Partition 1.1 索引信息存放在父表的分区所在的表空间。但是仅可以创建在父表为HashTable或者composite分区表的。 LOCAL STORE IN (tablespace) 1.2 仅可以创建在父表为HashTable或者composite分区表的。并且指定的分区数目要与父表的分区数目要一致 LOCAL STORE IN (tablespace) (PARTITION [partition [LOGGING|NOLOGGING] [TABLESPACE {tablespace|DEFAULT}] [PCTFREE int] [PCTUSED int] [INITRANS int] [MAXTRANS int] [STORAGE storage_clause] [STORE IN {tablespace_name|DEFAULT] [SUBPARTITION [subpartition [TABLESPACE tablespace]]]]) 1.3 索引信息存放在父表的分区所在的表空间，这种语法最简单，也是最常用的分区索引创建方式。 Local 1.4 并且指定的Partition 数目要与父表的Partition要一致 LOCAL (PARTITION [partition [LOGGING|NOLOGGING] [TABLESPACE {tablespace|DEFAULT}] [PCTFREE int] [PCTUSED int] [INITRANS int] [MAXTRANS int] [STORAGE storage_clause] [STORE IN {tablespace_name|DEFAULT] [SUBPARTITION [subpartition [TABLESPACE tablespace]]]]) Global Index 索引信息的存放位置与父表的Partition信息完全不相干。甚至父表是不是分区表都无所谓的。语法如下： GLOBAL PARTITION BY RANGE (col_list) ( PARTITION partition VALUES LESS THAN (value_list) [LOGGING|NOLOGGING] [TABLESPACE {tablespace|DEFAULT}] [PCTFREE int] [PCTUSED int] [INITRANS int] [MAXTRANS int] [STORAGE storage_clause] ) 但是在这种情况下，如果父表是分区表，要删除父表的一个分区都必须要更新Global Index ,否则索引信息不正确 ALTER TABLE TableName DROP PARTITION PartitionName Update Global Indexes --查询索引 select object_name,object_type,tablespace_name,sum(value) from v$segment_statistics where statistic_name IN ('physical reads','physical write','logical reads')and object_type='INDEX' group by object_name,object_type,tablespace_name order by 4 desc /#Oracle

分享到：

举报 浏览(64) 评论 转载

您可能也喜欢

评论

同时评论给

同时评论给原文作者

发布

500/0

收起|查看更多

帮助中心 | 空间客服 | 投诉中心 | 空间协议

©2012 Baidu