Oracle在线重定义-分区整理（bat执行）

来源：互联网发布：卖家数据编辑：程序博客网时间：2024/06/05 10:46

最近操作表的数据量比较大，但是导入的.dmp表并没有分区，于是在网上查找了一些oracle在线重定义的资料，由于.dmp比较多，

所以这里用.bat批量执行sql语句，这样我们就可以在分区的过程中做其他的事情，提高效率。步骤如下：

1.首先在桌面创一个文件夹

2.创建两个TXT文档，第一个TXT文档中输入

sqlplus system/cmd@cmd @sql.sql//以system身份登录oracle，并执行sql.sql文件

将文档后缀名改为.bat

3.第二个txt文档中输入

create user BIGCMD identified by BIGCMD default tablespace users temporary tablespace temp quota

unlimited on users;
GRANT CREATE SESSION, CREATE ANY TABLE,ALTER ANY TABLE, DROP ANY TABLE, LOCK ANY TABLE ,SELECT ANY

TABLE, CREATE ANY INDEX,CREATE ANY TRIGGER TO BIGCMD;
GRANT EXECUTE_CATALOG_ROLE TO BIGCMD;
conn BIGCMD/BIGCMD;
create table GPS_140101 as select * from GPSHRB_HIS.GPSHIS_DAY_140101;
alter table GPS_140101 add constraint GPS_140101_pk primary key(GPSDATA_ID);
create index GPS_140101_idx on GPS_140101(GPSTIME);
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('BIGCMD', ' GPS_140101');
create table GPS_140101_tmp partition by range(GPSTIME) (partition p1 values less than (TO_DATE

('2014-01-01 01:00:00','yyyy-mm-dd hh24:mi:ss')),partition p2 values less than (TO_DATE('2014-01-01

02:00:00','yyyy-mm-dd hh24:mi:ss')), partition p3 values less than (TO_DATE('2014-01-01

03:00:00','yyyy-mm-dd hh24:mi:ss')), partition p4 values less than (TO_DATE('2014-01-01

04:00:00','yyyy-mm-dd hh24:mi:ss')), partition p5 values less than (TO_DATE('2014-01-01

05:00:00','yyyy-mm-dd hh24:mi:ss')), partition p6 values less than (TO_DATE('2014-01-01

06:00:00','yyyy-mm-dd hh24:mi:ss')), partition p7 values less than (TO_DATE('2014-01-01

07:00:00','yyyy-mm-dd hh24:mi:ss')), partition p8 values less than (TO_DATE('2014-01-01

08:00:00','yyyy-mm-dd hh24:mi:ss')), partition p9 values less than (TO_DATE('2014-01-01

09:00:00','yyyy-mm-dd hh24:mi:ss')), partition p10 values less than (TO_DATE('2014-01-01

10:00:00','yyyy-mm-dd hh24:mi:ss')), partition p11 values less than (TO_DATE('2014-01-01

11:00:00','yyyy-mm-dd hh24:mi:ss')), partition p12 values less than (TO_DATE('2014-01-01

12:00:00','yyyy-mm-dd hh24:mi:ss')), partition p13 values less than (TO_DATE('2014-01-01

13:00:00','yyyy-mm-dd hh24:mi:ss')), partition p14 values less than (TO_DATE('2014-01-01

14:00:00','yyyy-mm-dd hh24:mi:ss')), partition p15 values less than (TO_DATE('2014-01-01

15:00:00','yyyy-mm-dd hh24:mi:ss')), partition p16 values less than (TO_DATE('2014-01-01

16:00:00','yyyy-mm-dd hh24:mi:ss')), partition p17 values less than (TO_DATE('2014-01-01

17:00:00','yyyy-mm-dd hh24:mi:ss')), partition p18 values less than (TO_DATE('2014-01-01

18:00:00','yyyy-mm-dd hh24:mi:ss')), partition p19 values less than (TO_DATE('2014-01-01

19:00:00','yyyy-mm-dd hh24:mi:ss')), partition p20 values less than (TO_DATE('2014-01-01

20:00:00','yyyy-mm-dd hh24:mi:ss')), partition p21 values less than (TO_DATE('2014-01-01

21:00:00','yyyy-mm-dd hh24:mi:ss')), partition p22 values less than (TO_DATE('2014-01-01

22:00:00','yyyy-mm-dd hh24:mi:ss')), partition p23 values less than (TO_DATE('2014-01-01

23:00:00','yyyy-mm-dd hh24:mi:ss')), PARTITION pmax VALUES LESS THAN (MAXVALUE) ) as select * from

GPS_140101 where 1=2;
BEGIN DBMS_REDEFINITION.START_REDEF_TABLE('BIGCMD', ' GPS_140101',' GPS_140101_TMP');
END;
/
set serveroutput on;
var v_err number;
exec DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('BIGCMD', ' GPS_140101', ' GPS_140101_TMP', NUM_ERRORS

=> :V_ERR);
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('BIGCMD', ' GPS_140101', ' GPS_140101_TMP');
commit;

将文件名改为sql.sql

具体的用户名，表名，分区根据自己的需要而定

之后执行.bat文件即可完成在线重定义-分区的过程，执行效果如下图：

0 0