/*
 * Title  : tts_gen_scr.sql
 * Author : EXADIME LLC
 * Created: 29-Jul-2017
 * Updated: 29-Jul-2017
 * Release: 1.0.0
 * Purpose: Generates TTS migration scripts.
 * History: N/A
 */

------------------------------------------------------
--Scripts Executable
------------------------------------------------------
--1.1  tts_vc.sql      S  Violations Check
--1.2  tts_att.sql     S  Anonymous Tablespaces Tables
--1.3  tts_ro.sql      S  Read Only Tablespaces
--1.4  tts_rw.sql      S  Read Write Tablespaces
--2.1  tts_rol.sql     T  Roles
--2.2  tts_pbs.sql     T  Public Synonyms
--2.3  tts_usr.sql     T  Users
--2.4  tts_srp.sql     T  System Roles & Privileges
--2.5  tts_or.sql      T  Object Roles (Grants)
--2.6  tts_dts.sql     T  Default Tablespaces
--2.7  tts_tsq.sql     T  Tablespace Quotas
--3.1  tts_exp.sh      S  TTS Export Dump
--3.2  tts_imp.sh      T  TTS Import Dump
--3.3  tts_ddl_exp.sh  S  DDL Export Dump
--3.4  tts_ddl_imp.sh  T  DDL Import Dump
--4.1  tts_pug.sql     B  Purge Tablespaces
--4.2  tts_cpl.sql     B  Compile Objects
--4.3  tts_vfy.sql     B  Verify Migration
--5.1  tts_rb.sql      T* Rollback Migration
------------------------------------------------------

------------------------------------------------------
--Scripts Support
------------------------------------------------------
--3.1  tts_exp.par     N  TTS Export Parameters
--3.2  tts_imp.par     N  TTS Import Parameters
--3.3  tts_ddl_exp.par N  DDL Export Parameters
--3.4  tts_ddl_imp.par N  DDL Import Parameters
------------------------------------------------------

------------------------------------------------------
--Notations
------------------------------------------------------
--S	Source
--T	Target
--B	Both
--N	None
--*	Optional
------------------------------------------------------

start oracle.ini

set echo off
set feedback off
set serverout on

set linesize 1000
set pagesize 0

set heading off
set headsep on
set underline on
set colsep " "

set verify off
set escape off
set embedded on

define tts_dir=&&tmp_dir

declare
	--1.1.Violations Check
	cursor csr_vc is
	select
		name
	from
		tts_tbs;

	--1.2.Anonymous Tablespace Tables
	cursor csr_att is
	select
		owner,
		table_name
	from
		dba_tables
	where
		owner in (
			select
				name
			from
				tts_usr
		)
	and
		tablespace_name is null;

	--1.3.Read Only Tablespaces
	cursor csr_ro is
	select
		name
	from
		tts_tbs;

	--1.4.Read Write Tablespaces
	cursor csr_rw is
	select
		name
	from
		tts_tbs;

	--2.1.Create object roles.
	cursor csr_rol is
	select
		name
	from
		tts_rol;

	--2.2.Create public synonyms.
	cursor csr_pbs is
	select
		synonym_name,
		table_owner,
		table_name
	from
		dba_synonyms
	where
		owner='PUBLIC'
	and
		table_owner in (
			select
				name
			from
				tts_usr
		);

	--2.3.Create business users.
	cursor csr_usr is
	select
		username,
		password
	from
		dba_users
	where
		username in (
			select
				name
			from
				tts_usr
		);

	--2.4.Grant sys roles and privs to business users.
	cursor csr_srp is
	select
		grantee,
		privilege
	from
		dba_sys_privs
	where
		grantee in (
			select
				name
			from
				tts_usr
		);

	--2.5.Roles granted to users and roles.
	cursor csr_or is
	select
		grantee,       
		granted_role
	from
		dba_role_privs
	where
		grantee in (
			select
				name
			from
				tts_usr
			union
			select
				name
			from
				tts_rol
		)
	and	granted_role in (
			select
				name
			from
				tts_rol
		);
		
	--2.6.Reset default tablespaces.
	cursor csr_dts is
	select
		username,
		default_tablespace
	from
		dba_users
	where
		username in (
			select
				name
			from
				tts_usr
		);
	
	--2.7.Reset tablespace quotas.
	cursor csr_tsq is
	select 
		q.username,
		q.tablespace_name,
		decode(q.max_bytes,-1,'unlimited',max_bytes) quota
	from
		dba_ts_quotas q,
		dba_tablespaces t,
		dba_users u
	where
		q.tablespace_name=t.tablespace_name
	and	q.username=u.username	
	and	q.username in (
				select
					name
				from
					tts_usr
		);

	--3.1.Create tts export dump par file.
	cursor csr_exp is
	select
		name
	from
		tts_tbs;

	--3.2.Create tts import dump par file.
	cursor csr_imp is
	select
		file_name
	from
		dba_data_files
	where 
		tablespace_name in (
			select
				name
			from
				tts_tbs
		);

	--3.3.Create ddl export dump par file.
	cursor csr_ddl_exp is
	select
		name
	from
		tts_usr;

	--3.4.Create ddl import dump par file.
	cursor csr_ddl_imp is
	select
		'fromuser=' || name || ' touser=' || name as name
	from
		tts_usr;

	--4.1.Purge Tablespaces.
	cursor csr_pug is
	select
		'purge tablespace ' || name || ';' as name
	from
		tts_tbs;

	--4.2.Compile Objects.
	cursor csr_cpl is
	select
		'@?/rdbms/admin/utlrp.sql' as name
	from
		dual;

	--4.3.Verify migration.
	cursor csr_vfy is
	select
		name
	from
		tts_usr;

	--5.1.Rollback Migration
	cursor csr_rb is
	select
		'drop role ' || name || ';' as name
	from
		tts_rol
	union
	select
		'drop user "' || name || '" cascade;' as name
	from
		tts_usr
	union
	select
		'drop tablespace ' || name || ' including contents;' as name
	from
		tts_tbs
	union
	select
		'drop public synonym ' || synonym_name || ';' as name
	from
		dba_synonyms
	where
		owner='PIBLIC';

	v_query		varchar2(4000);
	i		number:=0;
	v_names		varchar2(4000);
	n_first		number:=0;
begin
	v_query:='declare';
	insert into tts_tab(num_col,char_col) values (1.1,v_query);
	v_query:='tts_names	varchar2(2000);';
	insert into tts_tab(num_col,char_col) values (1.1,v_query);
	v_query:='begin';
	insert into tts_tab(num_col,char_col) values (1.1,v_query);

	for r in csr_vc
	loop
		if n_first = 0 then
			v_names:=r.name;
		else
			v_names:=v_names || ',' || r.name;
		end if;
		
		n_first:=1;
	end loop;

	v_query:='tts_names:='''|| v_names ||'''';	
	v_query:='SYS.DBMS_TTS.TRANSPORT_SET_CHECK(ts_list =>'''|| v_names ||''', incl_constraints => TRUE);';
	insert into tts_tab(num_col,char_col) values (1.1,v_query);
	v_query:='end;';
	insert into tts_tab(num_col,char_col) values (1.1,v_query);
	v_query:='/';
	insert into tts_tab(num_col,char_col) values (1.1,v_query);
	v_query:='select * from transport_set_violations;';
	insert into tts_tab(num_col,char_col) values (1.1,v_query);

	commit;
	
	for r in csr_att
	loop
		v_query:= 'alter table '|| r.owner || '.' || r.table_name || ' move tablespace <tablespace_name>;';
		insert into tts_tab(num_col,char_col) values (1.2,v_query);
	end loop;

	commit;

	for r in csr_ro
	loop
		v_query:='alter tablespace ' || r.name || ' read only;';
		insert into tts_tab(num_col,char_col) values (1.3,v_query);
	end loop;

	commit;
		
	for r in csr_rw
	loop
		v_query:='alter tablespace ' || r.name || ' read write;';
		insert into tts_tab(num_col,char_col) values (1.4,v_query);
	end loop;

	commit;

	for r in csr_rol
	loop
		v_query:='create role ' || r.name ||';';
		insert into tts_tab(num_col,char_col) values (2.1,v_query);
	end loop;

	commit;
	
	for r in csr_pbs
	loop
		v_query:='create public synonym ' || r.synonym_name || ' for ' || r.table_owner || '.' || r.table_name || ';';
		insert into tts_tab(num_col,char_col) values (2.2,v_query);
	end loop;

	commit;

	for r in csr_usr
	loop
		v_query:='create user "' || r.username || '" identified by values ''' || r.password || ''' default tablespace system temporary tablespace temp;';
		insert into tts_tab(num_col,char_col) values (2.3,v_query);
	end loop;
	
	commit;

	for r in csr_srp
	loop
		v_query:='grant ' || r.privilege || ' to '|| r.grantee || ';';
		insert into tts_tab(num_col,char_col) values (2.4,v_query);
	end loop;

	commit;

	for r in csr_or
	loop
		v_query:='grant ' || r.granted_role || ' to ' || r.grantee ||';';
		insert into tts_tab(num_col,char_col) values (2.5,v_query);
	end loop;
	
	commit;

	for r in csr_dts
	loop
		v_query:='alter user "' || r.username || '" default tablespace ' || r.default_tablespace || ';';
		insert into tts_tab(num_col,char_col) values (2.6,v_query);
	end loop;
	
	commit;

	for r in csr_tsq
	loop
		v_query:='alter user "' || r.username || '" quota ' || r.quota || ' on ' || r.tablespace_name || ';';
		insert into tts_tab(num_col,char_col) values (2.7,v_query);
	end loop;
	
	commit;

	v_names:='tablespaces=';

	n_first:=0;
	for r in csr_exp
	loop
		if n_first = 0 then
			v_names:=v_names || r.name;
		else
			v_names:=v_names || ',' || r.name;
		end if;
		
		n_first:=1;
	end loop;

	insert into tts_tab(num_col,char_col) values (3.1,v_names);

	commit;

	n_first:=0;
	v_names:='datafiles=';
	for r in csr_imp
	loop
		if n_first = 0 then
			v_names:=v_names || r.file_name;
		else
			v_names:=v_names || ',' || r.file_name;
		end if;
		
		n_first:=1;
	end loop;

	insert into tts_tab(num_col,char_col) values (3.2,v_names);

	commit;

	v_names:='owner=';
	insert into tts_tab(num_col,char_col) values (3.3,v_names);

	for r in csr_ddl_exp
	loop
		insert into tts_tab(num_col,char_col) values (3.3,r.name);
	end loop;

	commit;

	for r in csr_ddl_imp
	loop
		insert into tts_tab(num_col,char_col) values (3.4,r.name);
	end loop;

	commit;

	for r in csr_pug
	loop
		insert into tts_tab(num_col,char_col) values (4.1,r.name);
	end loop;

	commit;

	for r in csr_cpl
	loop
		insert into tts_tab(num_col,char_col) values (4.2,r.name);
	end loop;

	commit;

	v_query:='	select
			a.owner "Owner",
			(select count(1) from dba_objects x where x.owner=a.owner and x.status=''VALID'') "Valid",
			(select count(1) from dba_objects y where y.owner=a.owner and y.status=''INVALID'') "Invalid",
			count(1) "Total"
		from
			dba_objects a
		where
			a.owner in (';

	n_first:=0;
	for r in csr_vfy
	loop
		if n_first = 0 then
			v_names:='''' || r.name || '''';
		else
			v_names:=v_names || ',' || '''' || r.name || '''';
		end if;
		
		n_first:=1;
	end loop;

	v_query:=v_query || v_names || ')
		group by
			a.owner
		order by
			1;';
	insert into tts_tab(num_col,char_col) values (4.3,v_query);

	commit;

	for r in csr_rb
	loop
		insert into tts_tab(num_col,char_col) values (5.1,r.name);
	end loop;

	commit;
exception
	when others then
		dbms_output.put_line(sqlerrm);
end;
/

--1.1.Voilations Check
spool &&tts_dir/tts_vc.sql

prompt spool &&rpt_dir/tts_vc.rpt

select
	char_col
from
	tts_tab
where
	num_col=1.1;

prompt spool off

spool off

--1.2.Anonymous Tablespace Tables
spool &&tts_dir/tts_att.sql

prompt spool &&rpt_dir/tts_att.rpt

select
	char_col
from
	tts_tab
where
	num_col=1.2;

prompt spool off

spool off

--1.3.Read Only
spool &&tts_dir/tts_ro.sql

prompt spool &&rpt_dir/tts_ro.rpt

select
	char_col
from
	tts_tab
where
	num_col=1.3;

prompt spool off

spool off

--1.4.Read Write
spool &&tts_dir/tts_rw.sql

prompt spool &&rpt_dir/tts_rw.rpt

select
	char_col
from
	tts_tab
where
	num_col=1.4;

prompt spool off

spool off

--2.1.Create object roles.
spool &&tts_dir/tts_rol.sql

prompt spool &&rpt_dir/tts_rol.rpt

select
	char_col
from
	tts_tab
where
	num_col=2.1;

prompt spool off

spool off

--2.2.Create public synonyms.
spool &&tts_dir/tts_pbs.sql

prompt spool &&rpt_dir/tts_pbs.rpt

select
	char_col
from
	tts_tab
where
	num_col=2.2;

prompt spool off

spool off

--2.3.Create business users.
spool &&tts_dir/tts_usr.sql

prompt spool &&rpt_dir/tts_usr.rpt

select
	char_col
from
	tts_tab
where
	num_col=2.3;

prompt spool off

spool off

--2.4.Grant sys roles & privs to business users.
spool &&tts_dir/tts_srp.sql

prompt spool &&rpt_dir/tts_srp.rpt

select
	char_col
from
	tts_tab
where
	num_col=2.4;

prompt spool off

spool off

--2.5.Reset default tablespaces.
spool &&tts_dir/tts_or.sql

prompt spool &&rpt_dir/tts_or.rpt

select
	char_col
from
	tts_tab
where
	num_col=2.5;

prompt spool off

spool off

--2.6.Reset default tablespaces.
spool &&tts_dir/tts_dts.sql

prompt spool &&rpt_dir/tts_dts.rpt

select
	char_col
from
	tts_tab
where
	num_col=2.6;

prompt spool off

spool off

--2.7.Reset tablespace quotas.
spool &&tts_dir/tts_tsq.sql

prompt spool &&rpt_dir/tts_tsq.rpt

select
	char_col
from
	tts_tab
where
	num_col=2.7;

prompt spool off

spool off

--3.1.Export Tablespace Meta Data.
spool &&tts_dir/tts_exp.par

select
	char_col
from
	tts_tab
where
	num_col=3.1;

spool off

spool &&tts_dir/tts_exp.sh

select
	'exp "''/ as sysdba''" TRANSPORT_TABLESPACE=Y PARFILE=tts_exp.par FILE=tts.dmp LOG=tts_exp.log grants=y'
from
	dual;

spool off

--3.2.Import Tablespace Meta Data.
spool &&tts_dir/tts_imp.par

select
	char_col
from
	tts_tab
where
	num_col=3.1
union
select
	char_col
from
	tts_tab
where
	num_col=3.2;

spool off

spool &&tts_dir/tts_imp.sh

select
	'imp "''/ as sysdba''" TRANSPORT_TABLESPACE=Y PARFILE=tts_imp.par FILE=tts.dmp LOG=tts_imp.log ignore=y grants=y'
from
	dual;

spool off

--3.3.Export DDL.
spool &&tts_dir/tts_ddl_exp.par

select
	char_col
from
	tts_tab
where
	num_col=3.3;

spool off

spool &&tts_dir/tts_ddl_exp.sh

select
	'exp "''/ as sysdba''" parfile=tts_ddl_exp.par file=tts_ddl.dmp log=tts_ddl_exp.log triggers=y rows=n constraints=y indexes=y grants=y'
from
	dual;

spool off

--3.4.Import DDL.
spool &&tts_dir/tts_ddl_imp.par

select
	char_col
from
	tts_tab
where
	num_col=3.4;

spool off

spool &&tts_dir/tts_ddl_imp.sh

select
	'imp "''/ as sysdba''" parfile=tts_ddl_imp.par file=tts_ddl.dmp log=tts_ddl_imp.log ignore=y constraints=y indexes=y grants=y'
from
	dual;

spool off

--5.1.Rollback migration.
spool &&tts_dir/tts_rb.sql

select
	char_col
from
	tts_tab
where
	num_col=5.1
order by
	1 desc;

spool off

--4.1.Purge Tablespaces
spool &&tts_dir/tts_pug.sql

prompt spool &&rpt_dir/tts_pug.rpt

select
	char_col
from
	tts_tab
where
	num_col=4.1;

prompt spool off

spool off

--4.2.Compile Objects
spool &&tts_dir/tts_cpl.sql

prompt spool &&rpt_dir/tts_cpl.rpt

select
	char_col
from
	tts_tab
where
	num_col=4.2;

prompt spool off

spool off

--4.3.Verify migration.
spool &&tts_dir/tts_vfy.sql

prompt set pages 100
prompt set head on
prompt
prompt col "Owner"	format a30
prompt col "Valid"	format 9,999,999
prompt col "Invalid"	format 9,999,999
prompt col "Total"	format 9,999,999
prompt
prompt compute sum of valid   on report
prompt compute sum of invalid on report
prompt compute sum of total   on report
prompt
prompt break on report
prompt 
prompt spool &&rpt_dir/tts_vfy.rpt
	
select
	char_col
from
	tts_tab
where
	num_col=4.3;

prompt spool off

spool off