Berikut ini scipt dari Rampant untuk Re-Create table partition. Dimana ada sedikit modifikasi yang saya lakukan di script ini .
/* Copyright © 2004, 2008 by Rampant TechPress Inc.*/
--- Modify By Ida Bagus Enderajana DTP
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
( Source : Internet )
No comments:
Post a Comment