oracle 批量增加分区,转为分区表并批量插入数据

本帖最后由 zcs0237 于 2015-12-7 09:18 编辑

一、查看空间是否足够

1.circdata用户默认表空间

SQL> select DEFAULT_TABLESPACE,temporary_tablespace from dba_users where lower(username)='circdata';

--TBS_RESERVE                    TEMP

2.circdata.T_ORIGINAL_DATA数据段所在表空间

SQL> select distinct TABLESPACE_NAME from dba_segments where lower(OWNER)='circdata';

--TBS_RESERVE

3.表空间剩余空间

select 'All_GB='||ceil(BYTES/1024/1024/1024 )   from sm$ts_avail  where tablespace_name='TBS_RESERVE'

union all

select 'used_GB='||trunc(BYTES/1024/1024/1024)  from sm$ts_used  where tablespace_name='TBS_RESERVE'

union all

select 'Free_GB='||trunc(BYTES/1024/1024/1024)  from sm$ts_free  where tablespace_name='TBS_RESERVE';

--All_GB=535  used_GB=398 Free_GB=154

二、查看sql语句是否合法

grep -i -v ^insert /home/oracle/sql/2007-10.sql|grep -i -v ^values

grep -i -v ^insert /home/oracle/sql/2007-11.sql|grep -i -v ^values

grep -i -v ^insert /home/oracle/sql/2007-12.sql|grep -i -v ^values

grep -i -v ^insert /home/oracle/sql/2008-07-12.sql|grep -i -v ^values

grep -i -v ^insert /home/oracle/sql/200801-06.sql|grep -i -v ^values

grep -i -v ^insert /home/oracle/sql/200901-05.sql|grep -i -v ^values

三、在后台执行

nohup sqlplus circ/circ @ /home/oracle/sql/2007-10.sql &

nohup sqlplus circ/circ @ /home/oracle/sql/2007-11.sql &

nohup sqlplus circ/circ @ /home/oracle/sql/2007-12.sql &

nohup sqlplus circ/circ @ /home/oracle/sql/2008-07-12.sql &

nohup sqlplus circ/circ @ /home/oracle/sql/200801-06.sql &

nohup sqlplus circ/circ @ /home/oracle/sql/200901-05.sql &

查看执行情况：

-bash-3.2$ jobs

[1]   Stopped                 nohup sqlplus circ/circ @ /home/oracle/sql/2007-10.sql

[2]   Stopped                 nohup sqlplus circ/circ @ /home/oracle/sql/2007-11.sql

[3]   Stopped                 nohup sqlplus circ/circ @ /home/oracle/sql/2007-12.sql

[4]   Stopped                 nohup sqlplus circ/circ @ /home/oracle/sql/2008-07-12.sql

[5]-  Stopped                 nohup sqlplus circ/circ @ /home/oracle/sql/200801-06.sql

[6]+  Stopped                 nohup sqlplus circ/circ @ /home/oracle/sql/200901-05.sql

四、验证数据行

1、

grep insert /home/oracle/sql/2007-10.sql| wc -l     42

SQL> select count(*) from t_original_bk;            405801

SQL> select count(*) from t_original_data;          405843

2、

SQL> select count(*) from t_original_data;          405919

SQL> select 405919-405843 from dual;                76

grep insert /home/oracle/sql/2007-11.sql| wc -l     76

3、

SQL> select count(*) from t_original_data;          406133

SQL> select 406133-405919 from dual;                214

grep insert /home/oracle/sql/2007-12.sql| wc -l     214

4、

grep insert /home/oracle/sql/2008-07-12.sql| wc -l  446069

SQL> select 406133+446069 from dual;                852202

SQL> select count(*) from t_original_data;          852202

5、

grep insert /home/oracle/sql/200801-06.sql| wc -l   48404

SQL> select 852202+48404 from dual;                 900606

SQL> select count(*) from t_original_data;          900606

6、

grep insert /home/oracle/sql/200901-05.sql| wc -l   578296

SQL> select 900606+578296 from dual;                1478902

SQL> select count(*) from t_original_data;          1478902