/*
##################################################################
Script to convert a partitioned table into a non-partitioned one.
Written for Oracle 10gR2
##################################################################
(c) 2009 Martin Klier, www.klug-is.de
Licensed under GPL v2
ABSOLUTELY NO WARRANTY
##################################################################
*/

DECLARE
num_errors  PLS_INTEGER;
X          VARCHAR2(200);
TOWNER     VARCHAR2(200);
INTTABLE   varchar2(200);
cur         integer;
curstring   varchar2(500);
rc          integer;
  
BEGIN
  dbms_output.enable (1000000);
  X:='';
  TOWNER:='CUSTOMER';
  INTTABLE:='';
  
for X in (
  select table_name
    from dba_tables 
    where owner=TOWNER
      and partitioned='YES'
  )
  loop
    
    inttable:='INT_'||X.TABLE_NAME;
    dbms_output.put_line(INTTABLE);

    cur := DBMS_SQL.OPEN_CURSOR;
    CURSTRING:='create table '||INTTABLE||' tablespace TABLESPACE as select * from '||X.TABLE_NAME||' where 0=1';
	  DBMS_SQL.PARSE(cur, CURSTRING, DBMS_SQL.NATIVE);
	  rc := DBMS_SQL.EXECUTE(cur);
    DBMS_SQL.CLOSE_CURSOR(cur);
      
    DBMS_REDEFINITION.CAN_REDEF_TABLE(uname=>TOWNER,tname=>X.TABLE_NAME,options_flag=>DBMS_REDEFINITION.CONS_USE_PK);
    DBMS_REDEFINITION.START_REDEF_TABLE(uname=>TOWNER,orig_table=>X.TABLE_NAME,int_table=>INTTABLE);
    DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
          num_errors=>rc, 
          uname=>TOWNER, 
          orig_table=>X.TABLE_NAME, 
          int_table=>INTTABLE,
          copy_indexes=>DBMS_REDEFINITION.CONS_ORIG_PARAMS, 
          copy_triggers=>TRUE, 
          copy_constraints=>TRUE, 
          copy_privileges=>TRUE,
          ignore_errors=>TRUE);
    DBMS_REDEFINITION.SYNC_INTERIM_TABLE(uname=>TOWNER,orig_table=>X.TABLE_NAME,int_table=>INTTABLE);
    DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname=>TOWNER,orig_table=>X.TABLE_NAME,int_table=>INTTABLE);
    
    cur := DBMS_SQL.OPEN_CURSOR;
    curstring:='drop table '||INTTABLE||' cascade constraints';
	  DBMS_SQL.PARSE(cur, CURSTRING, DBMS_SQL.NATIVE);
	  rc := DBMS_SQL.EXECUTE(cur);
    DBMS_SQL.CLOSE_CURSOR(cur);

  end loop;
END;
/