++ redefinition

–ALTER USER “XXXXXX” QUOTA UNLIMITED ON XXXXXX;

 
create table exampl_redif (a clob, i int);
 
delete from exampl_redif;
insert into exampl_redif values('
  exec dbms_redefinition.can_redef_table (''REDIFINISHION_TABLE_OWNER'',''REDIFINISHION_TABLE_NAME'',dbms_redefinition.cons_use_rowid);
 
  BEGIN
   DBMS_REDEFINITION.start_redef_table (''REDIFINISHION_TABLE_OWNER'',
                                        ''REDIFINISHION_TABLE_NAME'',
                                        ''REDIFINISHION_TABLE_NAME_REDEF'',
                                        NULL,
                                        DBMS_REDEFINITION.cons_use_rowid);
END;
/
 
 
 
 
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE(''REDIFINISHION_TABLE_OWNER'',
                                        ''REDIFINISHION_TABLE_NAME'',
                                        ''REDIFINISHION_TABLE_NAME_REDEF'');
END;
/
 
 
DECLARE
   num_errors   PLS_INTEGER;
BEGIN
   DBMS_REDEFINITION.copy_table_dependents (''REDIFINISHION_TABLE_OWNER'',
                                            ''REDIFINISHION_TABLE_NAME'',
                                            ''REDIFINISHION_TABLE_NAME_REDEF'',
                                            1,
                                            TRUE,
                                            TRUE,
                                            TRUE,
                                            TRUE,
                                            num_errors,
                                            TRUE);
END;
/
 
 
begin
for i in (
select ''alter index ''||owner||''."''||index_name||''" rebuild tablespace REDIFINISHION_TABLESPACE_NEW online'' isql from all_indexes where TABLE_NAME=''REDIFINISHION_TABLE_NAME_REDEF'' and tablespace_name=''REDIFINISHION_TABLESPACE_OLD''
)
loop
execute immediate i.isql;
end loop;
end;
/
 
 
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE(''REDIFINISHION_TABLE_OWNER'',
                                        ''REDIFINISHION_TABLE_NAME'',
                                        ''REDIFINISHION_TABLE_NAME_REDEF'');
END;
/
 
begin
for i in (
select ''alter table ''||owner||''.''||table_name||'' drop constraint ''||constraint_name isql from all_constraints where r_constraint_name=(select constraint_name from all_constraints where table_name=''REDIFINISHION_TABLE_NAME_REDEF'' and constraint_type=''P'' and owner=''REDIFINISHION_TABLE_OWNER'') and owner=''REDIFINISHION_TABLE_OWNER'' and status=''DISABLED''
)
loop
execute immediate i.isql;
end loop;
end;
/
 
DROP TABLE REDIFINISHION_TABLE_OWNER.REDIFINISHION_TABLE_NAME_REDEF;
 
 
',1);
 
commit;
 
 
 
 
 
 
 
 
select owner,table_name,
replace(
    replace(
       dbms_metadata.get_ddl('TABLE',table_name,owner),'"'||owner||'"."'||table_name||'"','"'||owner||'"."'||table_name||'_REDEF"'
           ),
'TABLESPACE "USERS"','TABLESPACE "NEW_TABLESPACE"'
       ) || ';' ||
                replace(
                  replace(
                   replace(
                    replace(
                      (select a from  exampl_redif),'REDIFINISHION_TABLE_OWNER',owner
                            ),'REDIFINISHION_TABLE_NAME',table_name
                           ),'REDIFINISHION_TABLESPACE_NEW','PGU'
                          ),'REDIFINISHION_TABLESPACE_OLD','USERS'
                        )                         
from all_tables where TABLESPACE_NAME='USERS' and OWNER='MY_OWNER';

ps: thx to Vladimir Mukin for material