最近操作表的数据量比较大,但是导入的.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文件即可完成在线重定义-分区的过程,执行效果如下图: