SCRIPT FOR CREATING PARTITIONED TABLES

Di bawah ini script buatan Rampant TechPress Inc yang sangat berguna untuk mengcreate ulang table partition, untuk script dibawah ini ada beberapa bagian yang saya modifikasi sesuai dengan keperluan, berikut ini script lengkapnya


/* Copyright © 2004, 2008 by Rampant TechPress Inc.*/


REM



REM part_tab_rct.sql
REM



REM FUNCTION: SCRIPT FOR CREATING PARTITIONED TABLES
REM



REM           This script can be run by any user .
REM



REM           This script is intended to run with Oracle8i.
REM



REM           Running this script will in turn create a script to
REM           build all the partitioned tables owned by the user in the database.
REM           This created script, create_part_table.sql, can be run by any user
REM           with the 'CREATE TABLE' system privilege.
REM



REM NOTE:     The script will NOT include constraints on tables.  This
REM           script will also NOT capture tables created by user 'SYS'.
REM



REM Only preliminary testing of this script was performed.  Be sure to test
REM it completely before relying on it.
REM




set verify off
set feedback off
set echo off;
set pagesize 0 lines 132 embedded on

set termout on
select 'Creating table build script...' from dual;


--- drop table t_temp;

create table t_temp
     (lineno NUMBER, tb_owner VARCHAR2(30), tb_name VARCHAR2(30),
      text VARCHAR2(2000))
/
drop sequence lineno_seq;
create sequence lineno_seq start with 1 increment by 1 nocache nocycle;

DECLARE
   CURSOR tab_cursor(tab_name VARCHAR2) IS select   table_name,
                                 PARTITIONING_TYPE,            
                                 SUBPARTITIONING_TYPE,          
                                 PARTITION_COUNT,              
                                 DEF_SUBPARTITION_COUNT,        
                                 PARTITIONING_KEY_COUNT,        
                                 SUBPARTITIONING_KEY_COUNT,    
                                 DEF_TABLESPACE_NAME
                                 from     dba_part_tables
            where  table_name=upper(tab_name);
--
   CURSOR col_cursor (c_tab VARCHAR2) IS select  
                          column_name,
                          data_type,
                          data_length,
                          data_precision,
                          data_scale,
                          nullable
                     from     dba_tab_columns
                     where    table_name = c_tab
                    order by column_id;
--
   CURSOR  part_key (c_tab VARCHAR2) is select     column_name,
                            column_position
                      from dba_part_key_columns
                                          where trim(name)=c_tab
                                          and trim(object_type)='TABLE'
                                          order by column_position;
--
   CURSOR  part_cursor (c_tab VARCHAR2) IS select     PARTITION_NAME,        
                            SUBPARTITION_COUNT,
                            HIGH_VALUE,            
                            HIGH_VALUE_LENGTH,    
                            PARTITION_POSITION,
                            TABLESPACE_NAME                                
                            from dba_tab_partitions
                                        where table_name=c_tab
                                        order by partition_position;
--
   CURSOR subpart_key (c_tab VARCHAR2) IS select     column_name,
                            column_position
                      from dba_subpart_key_columns
                                          where trim(name)=c_tab
                                          and trim(object_type)='TABLE'
                                          order by column_position;
--
   CURSOR subpart_tbsp (c_tab VARCHAR2) IS select unique tablespace_name
                       from dba_tab_subpartitions
                                           where table_name=c_tab;
--
   CURSOR subpart_cursor (c_tab VARCHAR2, tab_part VARCHAR2) IS select
                                SUBPARTITION_NAME,
                                tablespace_name
                     from dba_tab_subpartitions
                                        where table_name=c_tab
                                        and partition_name=tab_part
                                        order by subpartition_position;
--
   CURSOR subpart_stor (c_tab varchar2, tab_part varchar2) IS select
                                initial_extent,
                                next_extent,
                                pct_increase
                     from dba_tab_subpartitions
                                        where table_name=c_tab
                                        and partition_name=tab_part
                                        and subpartition_position=1;
--
   lv_table_name        dba_part_tables.table_name%TYPE;
   lv_partitioning_type        dba_part_tables.partitioning_type%TYPE;
   lv_subpartitioning_type     dba_part_tables.subpartitioning_type%TYPE;
   lv_partition_count        dba_part_tables.partition_count%TYPE;
   lv_subpartition_count     dba_part_tables.def_subpartition_count%TYPE;
   lv_partition_key_count    dba_part_tables.partitioning_key_count%TYPE;
   lv_subpartition_key_count    dba_part_tables.subpartitioning_key_count%TYPE;
   lv_tablespace_name        dba_part_tables.def_tablespace_name%TYPE;
   lv_column_name        dba_tab_columns.column_name%TYPE;
   lv_data_type            dba_tab_columns.data_type%TYPE;
   lv_data_length        dba_tab_columns.data_length%TYPE;
   lv_data_precision        dba_tab_columns.data_precision%TYPE;
   lv_data_scale        dba_tab_columns.data_scale%TYPE;
   lv_nullable            dba_tab_columns.nullable%TYPE;
   lv_pkey_column_name        dba_part_key_columns.column_name%TYPE;
   lv_pkey_column_number    dba_part_key_columns.column_position%TYPE;
   lv_spkey_column_name        dba_subpart_key_columns.column_name%TYPE;
   lv_spkey_column_number    dba_subpart_key_columns.column_position%TYPE;
   lv_part_COMPOSITE        dba_tab_partitions.composite%TYPE;            
   lv_PARTITION_NAME        dba_tab_partitions.partition_name%TYPE;
   lv_part_SUBPARTITION_COUNT    dba_tab_partitions.subpartition_count%TYPE;
   lv_HIGH_VALUE        dba_tab_partitions.high_value%TYPE;
   lv_HIGH_VALUE_LENGTH        dba_tab_partitions.high_value_length%TYPE;
   lv_PARTITION_POSITION    dba_tab_partitions.partition_position%TYPE;
   lv_part_TABLESPACE_NAME         dba_tab_partitions.tablespace_name%TYPE;
   lv_SUBPARTITION_NAME         dba_tab_subpartitions.SUBPARTITION_NAME%TYPE;
   lv_spart_TABLESPACE_NAME    dba_tab_subpartitions.TABLESPACE_NAME%TYPE;
   lv_subpartition_position    dba_tab_subpartitions.subpartition_position%TYPE;
   lv_subp_initial_extent    dba_tab_subpartitions.initial_extent%TYPE;
   lv_subp_next_extent        dba_tab_subpartitions.next_extent%TYPE;
   lv_subp_pct_increase        dba_tab_subpartitions.pct_increase%TYPE;
   lv_first_rec         BOOLEAN;
   lv_string            VARCHAR2(2000);
   tbsp_string        VARCHAR2(2000);
   tmp_string        VARCHAR2(2000);
   nul_cnt           number;
   sp_cnt        number;
   flg            VARCHAR2(32);
   fl            VARCHAR2(32);
   minex        VARCHAR2(32);
   maxex        VARCHAR2(32);
--
   procedure write_out(p_name VARCHAR2,
               p_string VARCHAR2) is
   begin
      insert into t_temp (lineno, tb_name, text)
         values (lineno_seq.nextval,p_name,rtrim(p_string,chr(32)));
   end;
--
BEGIN
   OPEN tab_cursor('&tab_name');
   LOOP
      FETCH tab_cursor INTO     lv_table_name,
                lv_partitioning_type,
                   lv_subpartitioning_type,
                   lv_partition_count,
                   lv_subpartition_count,
                   lv_partition_key_count,
                   lv_subpartition_key_count,
                   lv_tablespace_name;
      EXIT WHEN tab_cursor%NOTFOUND;
    lv_string := 'DROP TABLE '|| lower(lv_table_name)||';';
    write_out(lv_table_name, lv_string);
    lv_first_rec := TRUE;
    lv_string := 'CREATE TABLE '|| lower(lv_table_name)||' (';
    write_out(lv_table_name, lv_string);
      lv_string := null;
      OPEN col_cursor(lv_table_name);
      nul_cnt:=0;
      LOOP
     FETCH col_cursor INTO  lv_column_name,
                lv_data_type,
                lv_data_length,
                lv_data_precision,
                lv_data_scale,
                lv_nullable;
     EXIT WHEN col_cursor%NOTFOUND;
     if (lv_first_rec) then
        lv_first_rec := FALSE;
     else
        lv_string :=  ',';
     end if;
    if ((lv_data_type = 'NUMBER') and (lv_data_precision>0)) then
     lv_string := lv_string || lower(lv_column_name) ||
             ' ' || lv_data_type ||'('||lv_data_precision||','||
            nvl(lv_data_scale,0)||')';
    elsif ((lv_data_type = 'FLOAT') and (lv_data_precision>0)) then
     lv_string := lv_string || lower(lv_column_name) ||
             ' ' || lv_data_type ||'('||lv_data_precision||')';
    else
     lv_string := lv_string || lower(lv_column_name) ||
             ' ' || lv_data_type;
    end if;
     if ((lv_data_type = 'CHAR') or (lv_data_type = 'VARCHAR2')) then
        lv_string := lv_string || '(' || lv_data_length || ')';
     end if;
     if (lv_nullable = 'N') then
        nul_cnt:=nul_cnt+1;
        lv_string := lv_string || ' constraint ck_'||lv_table_name||'_'||nul_cnt||' NOT NULL';
     end if;
      write_out(lv_table_name, lv_string);
      END LOOP;
      CLOSE col_cursor;
      lv_string := ')';
      write_out(lv_table_name, lv_string);
      lv_string := NULL;
      nul_cnt:=0;
--
      OPEN part_key(lv_table_name);
      IF lv_partition_key_count>1 THEN
       LOOP
        FETCH part_key INTO lv_pkey_column_name,lv_pkey_column_number;
        exit when part_key%NOTFOUND;
        if nul_cnt=0 THEN
           tmp_string:=lv_pkey_column_name;
           nul_cnt:=nul_cnt+1;
        else
           tmp_string:=tmp_string||','||lv_pkey_column_name;
           nul_cnt:=nul_cnt+1;
           dbms_output.put_line(tmp_string);
        end if;
       END LOOP;
      end if;
      if lv_partition_key_count=1 THEN
       FETCH part_key INTO lv_pkey_column_name,lv_pkey_column_number;
       tmp_string:=lv_pkey_column_name;
      end if;
      CLOSE part_key;
--
      lv_string:='PARTITION BY RANGE ('||tmp_string||')';
      write_out(lv_table_name, lv_string);
      lv_string := NULL;
      tmp_string:=NULL;
--
      if lv_subpartition_key_count>0 THEN
       OPEN subpart_key(lv_table_name);
       if lv_subpartition_key_count=1 THEN
        FETCH subpart_key INTO lv_spkey_column_name,lv_spkey_column_number;
        tmp_string:=lv_spkey_column_name;
       else
        nul_cnt:=0;
        LOOP
         FETCH subpart_key INTO lv_spkey_column_name,lv_spkey_column_number;
         exit when subpart_key%NOTFOUND;
         if nul_cnt=0 THEN
           tmp_string:=lv_spkey_column_name;
           nul_cnt:=nul_cnt+1;
           dbms_output.put_line(tmp_string);
         else
           tmp_string:=tmp_string||','||lv_spkey_column_name;
           nul_cnt:=nul_cnt+1;
         end if;
        END LOOP;
       CLOSE subpart_key;
       end if;
--
       if lv_subpartition_count>1 then
        OPEN subpart_tbsp(lv_table_name);
        nul_cnt:=0;
        LOOP
         FETCH subpart_tbsp INTO lv_spart_tablespace_name;
         exit when subpart_tbsp%NOTFOUND;
         dbms_output.put_line(lv_spart_tablespace_name);
         if nul_cnt=0 then
          tbsp_string:=lv_spart_tablespace_name;
          nul_cnt:=nul_cnt+1;
         else
          tbsp_string:=tbsp_string||','||lv_spart_tablespace_name;
          nul_cnt:=nul_cnt+1;
         end if;
        END LOOP;
       end if;
       CLOSE subpart_tbsp;
--
       lv_string := 'SUBPARTITION BY HASH ('||tmp_string||')';
       write_out(lv_table_name, lv_string);
       lv_string := 'SUBPARTITIONS '||to_char(lv_subpartition_count);
       write_out(lv_table_name, lv_string);
       lv_string := 'STORE IN ('||tbsp_string||')';
       write_out(lv_table_name, lv_string);
       lv_string := null;
      end if;
--
      OPEN part_cursor(lv_table_name);
      if lv_partition_count=1 THEN
      FETCH part_cursor INTO    lv_PARTITION_NAME,
                lv_part_SUBPARTITION_COUNT,
                lv_HIGH_VALUE,
                lv_HIGH_VALUE_LENGTH,
                lv_PARTITION_POSITION,
                lv_part_TABLESPACE_NAME;
     
      if lv_string is not null then
                  write_out(lv_table_name, lv_string);
      end if;
      ELSE
       nul_cnt:=0;
       LOOP
        FETCH part_cursor INTO  lv_PARTITION_NAME,
                lv_part_SUBPARTITION_COUNT,
                lv_HIGH_VALUE,
                lv_HIGH_VALUE_LENGTH,
                lv_PARTITION_POSITION,
                lv_part_TABLESPACE_NAME;
        exit when part_cursor%NOTFOUND;
        lv_string:='PARTITION '||lv_partition_name||' VALUES LESS THAN ('||lv_high_value||')';
        if nul_cnt=0 then
         lv_string:='('||lv_string;
        else
         lv_string:=','||lv_string;
        end if;
        write_out(lv_table_name, lv_string);
        lv_string:=fl||flg||minex||maxex;
      if lv_string is not null then
                  write_out(lv_table_name, lv_string);
      end if;
       
        nul_cnt:=nul_cnt+1;
--
        if lv_part_subpartition_count>0 THEN
         OPEN subpart_cursor(lv_table_name,lv_partition_name);
         sp_cnt:=0;
         LOOP
          FETCH subpart_cursor INTO        lv_SUBPARTITION_NAME,
                    lv_spart_TABLESPACE_NAME;
          exit when subpart_cursor%NOTFOUND;
          if sp_cnt=0 THEN
           lv_string:='(SUBPARTITION '||lv_subpartition_name||
          ' TABLESPACE '||lv_spart_tablespace_name;
           write_out(lv_table_name, lv_string);
           else
           lv_string:=',SUBPARTITION '||lv_subpartition_name||
          ' TABLESPACE '||lv_spart_tablespace_name;
           write_out(lv_table_name, lv_string);
          end if;
          sp_cnt:=sp_cnt+1;
         END LOOP;
        lv_string:=')';    
        write_out(lv_table_name, lv_string);
        lv_string := null;
        close subpart_cursor;
        end if;
       END LOOP;
       close part_cursor;
      end if;
--
      lv_string := ')'||chr(10)||'/';
      write_out(lv_table_name, lv_string);
      lv_string:='                                                  ';
      write_out(lv_table_name, lv_string);
   END LOOP;
   CLOSE tab_cursor;
END;
/

set heading off trimspool on
spool create_part_table.sql

select   text
from     T_temp
order by  tb_name, lineno;

spool off

drop table t_temp;
set verify on
set feedback on
set termout on
set pagesize 22 lines 80