Script generation for DDL operations

Generate unique constraints from current schema

Adding a new column in this example

declare
  s varchar2(1000) := '';
  col varchar2(4000) := '';
BEGIN 
  FOR c1 in (select * from user_constraints p where constraint_type in ('U', 'P') and not table_name in ('SUT')) LOOP
    s := 'alter table ' || c1.table_name || ' add constraint ' || c1.constraint_name ||  ' unique (';
    col := '';
    for c2 in (select * from user_cons_columns where constraint_name = c1.constraint_name order by position) loop
      if(length(col)> 0) then col := col || ', '; end if;
      col := col || c2.column_name;
    end loop;
    col := col || ', GLOBAL_OPERATION_SEQ';
    dbms_output.put_line(s || col || ');');
  END LOOP;
END;

Generate reference constraints from current schema

Adding a new column in this example

declare
  s varchar2(1000) := '';
  col varchar2(4000) := '';
BEGIN 
  FOR c1 in (select p.*, 
             (select table_name from user_constraints where constraint_name = p.r_constraint_name) r_table_name 
             from user_constraints p where constraint_type in ('R')) LOOP
    s := 'alter table ' || c1.table_name || ' add constraint ' || c1.constraint_name ||  ' foreign key (';
    col := '';
    for c2 in (select * from user_cons_columns where constraint_name = c1.constraint_name order by position) loop
      if(length(col)> 0) then col := col || ', '; end if;
      col := col || c2.column_name;
    end loop;
    col := col || ', GLOBAL_OPERATION_SEQ)';
    s := s || col || ' references ' || c1.r_table_name || '(';
    col := '';
    for c2 in (select * from user_cons_columns where constraint_name = c1.r_constraint_name order by position) loop
      if(length(col)> 0) then col := col || ', '; end if;
      col := col || c2.column_name;
    end loop;
    col := col || ', GLOBAL_OPERATION_SEQ)';
    s := s || col;
    dbms_output.put_line(s || ';');
  END LOOP;
END;

Table contains duplicate, delete only one copy

declare
	row_deleted number := 1; 
begin
	while(row_deleted > 0) loop
		delete from prova where id in (
			select id 
			from prova
			having count(*) >= 2
			group by id
		) and rownum = 1;
		row_deleted := sql%rowcount;
	end loop;
end;

Execute statements while heavy work on db

declare
	   resource_busy exception;
	   PRAGMA EXCEPTION_INIT(resource_busy, -54);
	   err boolean;
	   i integer := 0;
begin
	 loop
	 	 i := i +1;
	 	 begin
	 	 	  err := false;
	 	 	  --Place your statement here, don't use ";" as statement termination
			  execute immediate 'drop index vpo.provatr1';
			  -------------------------------------------------------------------
		 exception		
		 	  when resource_busy then
			  begin				 
					err := true;
			  end;
		 	  when others then 
			  begin
			  	   dbms_output.put_line(sqlerrm(sqlcode));
				   err := true;
				   raise;
			  end;   
		 end;
		 exit when (err = false)or(i >= 100);
	 end loop;
	 dbms_output.put_line('Statement executed ' || i || ' times');
	 if err = true then
	 	dbms_output.put_line('Statement has not been executed successfully');
	 else dbms_output.put_line('Statement has been executed successfully');
	 end if;
end;

Template scripts for multiple executions

set serveroutput on;

declare
	   resource_busy exception;
	   PRAGMA EXCEPTION_INIT(resource_busy, -54);
	   err boolean;
	   i integer := 0;
begin
	 loop
	 	 i := i +1;
	 	 err := false;
		 --place you code here--
		 --Example:
		 --begin
		 --  execute immediate 'alter table ALWORKSHOP.NAV_CAT_TAB_UTENTE disable constraint CAT_TABELLE_CATEG_NOTNULL';
		 --  exception when others then 
		 --  begin 
		 --    dbms_output.put_line(sqlerrm(sqlcode)); err := true; 
		 --  end; 
		 --end;

		 -----------------
		 exit when (err = false)or(i >= 10);
	 end loop;
	 dbms_output.put_line('Statement executed ' || i || ' times');
	 if err = true then
	 	dbms_output.put_line('Statement has not been executed successfully');
	 else dbms_output.put_line('Statement has been executed successfully');
	 end if;
end;

One shoot to truncate many tables

Run this and SAVE results, for template script; you may execute it for safety, it just enable already enabled constraints:

select 'begin' ||chr(13)||chr(10)||'execute immediate ''' 
|| 'alter table '||owner||'.'||table_name||' enable constraint '||constraint_name ||''';'
||chr(13)||chr(10)||'exception when others then ' ||chr(13)||chr(10)||
'begin ' ||chr(13)||chr(10)||'dbms_output.put_line(sqlerrm(sqlcode)); err := true; end; end;' statement_to_insert
from dba_constraints
where owner in ('ALWORKSHOP')
and status = 'ENABLED';

Disable constraints

Run this and EXECUTE results, for template script;

select 'begin' ||chr(13)||chr(10)||'execute immediate ''' 
|| 'alter table '||owner||'.'||table_name||' disable constraint '||constraint_name ||''';'
||chr(13)||chr(10)||'exception when others then ' ||chr(13)||chr(10)||
'begin ' ||chr(13)||chr(10)||'dbms_output.put_line(sqlerrm(sqlcode)); err := true; end; end;' statement_to_insert
from dba_constraints
where owner in ('ALWORKSHOP')
and status = 'ENABLED';

Truncate tables

Run this and EXECUTE results, for template script;

select 'begin' ||chr(13)||chr(10)||'execute immediate ''' 
|| 'truncate table '||owner||'.'||table_name||' reuse storage'';'
||chr(13)||chr(10)||'exception when others then ' ||chr(13)||chr(10)||
'begin ' ||chr(13)||chr(10)||'dbms_output.put_line(sqlerrm(sqlcode)); err := true; end; end;' statement_to_insert
from dba_tables
where owner in ('ALWORKSHOP');

Check truncated tables

Run this and EXECUTE to be sure tables contains no data(Please remove the last 'Union all')

select 'select '''||owner||'.'||table_name||''', count(*) from ' ||owner||'.'||table_name||chr(13)||chr(10)||
'union all'||chr(13)||chr(10)
from dba_tables 
where owner in ('ALWORKSHOP');

Run the previously saved script to enable constraints

Drop all tables

Run and execute the script:

select 'drop table '||owner||'.'||table_name||' cascade constraints;'
from dba_tables
where owner in ('ALWORKSHOP');

Create empty tablespaces on onother db

On source db:
SELECT    'CREATE TABLESPACE "'
       || tablespace_name
       || '" logging datafile '''
       || first_datafile
       || tablespace_name
       || '.dbf'''
       || ' size 5M AUTOEXTEND ON NEXT  10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO;'
                                                                       script
  FROM (SELECT dba_tablespaces.tablespace_name,
               (SELECT SUBSTR
                            (file_name,
                             0,
                             INSTR (file_name,
                                    SUBSTR (file_name,
                                            INSTR (file_name, '/', -1),
                                            LENGTH (file_name)
                                           )
                                   )
                            )
                  FROM dba_data_files
                 WHERE tablespace_name = dba_tablespaces.tablespace_name
                   AND ROWNUM = 1) first_datafile
          FROM dba_tablespaces
         WHERE CONTENTS = 'PERMANENT' AND tablespace_name != 'SYSTEM')