Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle partition table data migration, process management automation     - Use the TC flow control test under Linux (Linux)

- 11G ASM disk group does not automatically handle MOUNT (Database)

- Using Vagrant to build multi-platform environment (Server)

- How to install Nginx on FreeBSD 10.2 as an Apache reverse proxy (Server)

- Use Swift remove the spaces in the string (Programming)

- Install minimize RHEL / CentOS 7 things to do (Linux)

- Share and show your code on GitHub (Linux)

- PCM audio under Linux (Linux)

- Oracle Database Performance Optimization of memory disk (Database)

- Linux basic articles of the boot process (Linux)

- How to install CentOS CentOS web page (Server)

- Setting Lns firewall against ARP attacks (Linux)

- NFS-based services and service utilization Corosync DRBD high availability cluster configuration, respectively (Server)

- Java Adapter Mode (Programming)

- New experience Budgie (Budgerigar) desktop environment (Linux)

- Spark On YARN cluster installation deployment (Server)

- Slice MyCAT common fragmentation rules of enumeration (Database)

- CentOS 6.6 permanent method to modify the DNS address (Linux)

- Android developers learning Adapter (data adapter) (Programming)

- The Concept and Semantics of Java Memory Model (Programming)

 
         
  Oracle partition table data migration, process management automation
     
  Add Date : 2017-07-22      
         
         
         
  The following procedure around the company running the Oracle database environment, write to the main purpose of automation.
 Process is complete tasks
1. Before partition automatically add one day to the time to do partition
2. T_partition_1 automatically deleted partition table six days before the partition, T_partition_1 current table
3. T_partition_2 automatically deleted partition table partition 1 year ago, T_partition_2 history table and store historical data
4. The data is exchanged only for a day five days ago, the exchange of data to T_PARTITION_SWAP T_partition_1 table inside, in exchange T_PARTITION_SWAP to T_partition_2 history table
5. There will be inserted into an abnormal error log table for easy viewing

 Process name: manage_partition

 create or replace procedure manage_partition is

 partition_name_add_1 varchar2 (20);
 partition_name_reduce_5 varchar2 (20);
 current_time varchar2 (20);
 v_Sql varchar2 (1000);
 partiton_name varchar2 (50);
 partition_values ​​varchar2 (20);
 swap_count number (38);
 pro_name varchar2 (20);
 err_info varchar2 (20);
 sj varchar2 (20);

 cursor all_data is select table_name, max (partition_name) as partition_name, tablespace_name from user_tab_partitions where table_name in ( 'T_partition_1', 'T_partition_2') group by
 table_name, tablespace_name;

 type mt_his is record (table_name varchar2 (20), partiton_name varchar2 (20), tablespace_name varchar2 (50));

 all_table mt_his;

 begin

  select to_char (sysdate + 1, 'yyyy-mm-dd hh24: mi: ss') into partition_values ​​from dual;

 select to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss') into current_time from dual;

 select 'P_' || substr (to_char (sysdate + 1, 'yyyymmdd'), 1,8) || '_23' into partition_name_add_1 from dual;
 select 'P_' || substr (to_char (sysdate - interval '5' day, 'yyyymmdd'), 1,8) || '_23' into partition_name_reduce_5 from dual;


 for all_table in all_data loop


  if partition_name_add_1 < > all_table.partition_name then

  v_Sql: = 'alter table' || all_table.table_name || 'add partition' || partition_name_add_1 || 'values ​​less than (TO_DATE (' || '' '' || partition_values ​​|| '' '' || ', '||' '' YYYY-MM-DD HH24: MI: SS '' '||')) tablespace '|| all_table.tablespace_name ||' ';
  execute immediate v_Sql;

  end if;

 end loop;


    declare
    cursor old_partition_1 is select partition_name, table_name from user_tab_partitions where table_name = 'T_partition_1' and substr (partition_name, 3,10) < to_char (sysdate - interval '6' day, 'yyyymmdd');
    --old_p_1 user_tab_partitions.partition_name% type;
    begin
      for old_p_1 in old_partition_1 loop
    v_Sql: = 'alter table' || old_p_1.table_name || 'drop partition' || old_p_1.partition_name || '';
      execute immediate v_Sql;

      end loop;
    end;

    declare


      cursor old_partition_2 is select partition_name, table_name from user_tab_partitions where table_name = 'T_partition_2' and substr (partition_name, 3,10) < to_char (sysdate - interval '1' year, 'yyyymmdd');
    --old_p_1 user_tab_partitions.partition_name% type;
    begin
      for old_p_2 in old_partition_2 loop
    v_Sql: = 'alter table' || old_p_2.table_name || 'drop partition' || old_p_2.partition_name || '';
    dbms_output.put_line (old_p_2.table_name);
      execute immediate v_Sql;

        end loop;
    end;
    select count (1) into swap_count from T_PARTITION_SWAP;
    if swap_count = 0 then

        v_Sql: = 'alter table T_partition_1 exchange partition' || partition_name_reduce_5 || 'with table T_PARTITION_SWAP UPDATE INDEXES';
      execute immediate v_Sql;
        v_Sql: = 'alter table T_partition_2 exchange partition' || partition_name_reduce_5 || 'with table T_PARTITION_SWAP UPDATE INDEXES';
        execute immediate v_Sql;
      else
        v_Sql: = 'truncate table T_SMSGATEWAY_MT_SWAP';
        execute immediate v_Sql;
          v_Sql: = 'alter table T_SMSGATEWAY_MT exchange partition' || partition_name_reduce_5 || 'with table T_SMSGATEWAY_MT_SWAP UPDATE INDEXES';
      execute immediate v_Sql;
        v_Sql: = 'alter table T_SMSGATEWAY_MT_HIS exchange partition' || partition_name_reduce_5 || 'with table T_SMSGATEWAY_MT_SWAP UPDATE INDEXES';
        execute immediate v_Sql;
      end if;
 exception
  when others then
    --sg_log_err ( 'manage_partition', sqlerrm);
    pro_name: = 'manage_partition';
    err_info: = sqlerrm;
    select sysdate into sj from dual;
    v_Sql: = 'insert into err_log values ​​(' || '' 'pro_name' '' || ',' '' || err_info || '' ',' '' || sj || '' ')';
    execute immediate v_Sql;
    commit;
    
    dbms_output.put_line (sqlcode || sqlerrm);
 end manage_partition;

Error log table used to record the exception log
 Create Statements
create table err_log (pro_name varchar2 (20), err_log varchar2 (200), error_time date);
     
         
         
         
  More:      
 
- Oracle first Automated Installation Packages (Database)
- To configure and use multiple Tomcat under Linux environment (Server)
- Shell scripts quickly deploy Tomcat project (Server)
- Oracle 11g principle study Dataguard (Database)
- Sublime Text Add instructions to insert the current time zone (Linux)
- Linux kernel panic (because glibc result) Repair (Linux)
- CentOS yum configuration under local sources (Linux)
- SQL Server 2012 failover looksalive check and is alive check (Database)
- Oracle Execute to Parse perform analytical Ratio Analysis (Database)
- MySQL 5.7.10 source code for the latest version of the installation process in detail (Database)
- Installation and configuration of Hadoop under Linux (Server)
- Use PXE with kickstart to install Linux automatically (Linux)
- CentOS / Linux restart active NIC connection paths / network configure error Error Resolution (Linux)
- Linux log management make the system more secure (Linux)
- Modifying the system registry protection server security (Linux)
- Use Elasticsearch + Logstash + Kibana set up centralized log Practice Analysis Platform (Server)
- Binary tree to the next node (Programming)
- MySQL my.cnf sql_mode schema modifications (Database)
- To install Git on Ubuntu systems (Server)
- Installing PHP Memcache extension under Linux (Server)
     
           
     
  CopyRight 2002-2020 newfreesoft.com, All Rights Reserved.