100分的对现有数据库扩展的问提 由于业务量的增加,公司已有的数据库中的消费明细表,将被改为分区表,但是表已建完就不能改成分区表了,为了不影响到现有的运营,请各位数据库高手提供一个可行的解决方案。100分奉送! 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 建立分区表A,insert into A select from old_table就可以了.记得好象exchange partion with 也可以,具体语法记不清了. 建立分区表A,insert /*+append*/ into A select from old_table 然后 drop table old_table;然后 alter table a rename to old_table;最后重新编译存储过程、触发器、视图,重建索引 把表内数据导成insert语句脚本,drop原表,建分区表,执行脚本导入数据 exchange partition提供了快速转换普通成分区表的方法,它通过更新数据字典来实现分区与普通表的置换,所以速度相当快.create table t1 as select sysdate dt, all_objects.* from all_objects;create table t3 as select add_months(sysdate,-24) dt, all_objects.* from all_objects;create table t( dt, OWNER, OBJECT_NAME, SUBOBJECT_NAME,OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED,LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY,GENERATED, SECONDARY )partition by range(dt) (partition part2003 values less than ( to_date( '01-jan-2004','dd-mon-yyyy') ),partition part2004 values less than ( to_date( '01-jan-2005','dd-mon-yyyy') ),partition part2005 values less than ( to_date( '01-jan-2006','dd-mon-yyyy') ))asselect sysdate dt, all_objects.* from all_objects where 1=0;/ SQL 10G>set timing onSQL 10G>alter table t 2 exchange partition part2003 3 with table t3 4 /Table altered.Elapsed: 00:00:00.07 SQL 10G>alter table t 2 exchange partition part2005 3 with table t1 4 /Table altered.Elapsed: 00:00:00.07 它是更新数据字典而不是重新copy data block.这也是我知道的最快捷的办法了. 请教个SQL语句 请问在Oracle For Windows中,怎么知道本地Oracle服务器上有哪些数据库? 请教Oracle数据库分离的问题 如何对结果集进行统计 求助一较复杂SQL语句 update语句更新问题 805中创建临时表问题,急,在线 触发器和事务处理的问题,高手请进!!!! 关于oralce listener随系统启动的问题 转行后是否考虑待遇 急!不知道为什么oracle10g中的OracleDBConsoleorcl这个服务启不来,请高手指点,在线等待 在pl/sql中为什么不支持truncate?
记得好象exchange partion with 也可以,具体语法记不清了.
create table t1 as select sysdate dt, all_objects.* from all_objects;
create table t3 as select add_months(sysdate,-24) dt, all_objects.* from all_objects;create table t( dt, OWNER, OBJECT_NAME, SUBOBJECT_NAME,
OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED,
LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY,
GENERATED, SECONDARY )
partition by range(dt) (
partition part2003 values less than ( to_date( '01-jan-2004',
'dd-mon-yyyy') ),
partition part2004 values less than ( to_date( '01-jan-2005',
'dd-mon-yyyy') ),
partition part2005 values less than ( to_date( '01-jan-2006',
'dd-mon-yyyy') )
)
as
select sysdate dt, all_objects.* from all_objects where 1=0;
/
SQL 10G>set timing on
SQL 10G>alter table t
2 exchange partition part2003
3 with table t3
4 /
Table altered.
Elapsed: 00:00:00.07
SQL 10G>alter table t
2 exchange partition part2005
3 with table t1
4 /
Table altered.
Elapsed: 00:00:00.07