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

start oracle.ini

set echo off

drop table tts_tab;
drop table tts_tbs;
drop table tts_usr;
drop table tts_rol;

create table tts_tab(
	num_col		number,
	char_col	varchar2(1000),
	date_col	date default sysdate
) nologging;

create table tts_tbs(
	name 		varchar2(30)	not null
);

create table tts_usr(
	name 		varchar2(30)	not null
);

create table tts_rol(
	name 		varchar2(30)	not null
);

insert into tts_tbs
select 
	tablespace_name
from
	dba_tablespaces
where
	tablespace_name not in (
		'SYSTEM',
		'SYSAUX',
		'USERS',
		'INDX',
		'TOOLS'
	)
and
	contents='PERMANENT'
order by
	1;
	
insert into tts_usr
select
	username
from
	dba_users
where
	username not in (
		'SYS',
		'SYSTEM',
		'DBSNMP',
		'OUTLN',
		'MDSYS',
		'ORDSYS',
		'ORDPLUGINS',
		'CTXSYS',
		'DSSYS',
		'WKPROXY',
		'WKSYS',
		'WMSYS',
		'XDB',
		'ANONYMOUS',
		'ODM',
		'ODM_MTR',
		'OLAPSYS',
		'TRACESVR',
		'PERFSTAT',
		'REPADMIN',
		'EXFSYS',
		'DMSYS',
		'SYSMAN',
		'DIP',
		'TSMSYS',
		'DVF',
		'DVSYS',
		'WKUSER',
		'WK_TEST',
		'LBACSYS',
		'SI_INFORMTN_SCHEMA',
		'SCOTT',
		'HR',
		'OE',
		'PM',
		'QS',
		'SH'
	)
order by
	1;	

insert into tts_rol
select
	role
from
	dba_roles
where
	role not in (
		'CONNECT',
		'RESOURCE',
		'DBA',
		'SELECT_CATALOG_ROLE',
		'EXECUTE_CATALOG_ROLE',
		'DELETE_CATALOG_ROLE',
		'EXP_FULL_DATABASE',
		'IMP_FULL_DATABASE',
		'RECOVERY_CATALOG_OWNER',
		'GATHER_SYSTEM_STATISTICS',
		'LOGSTDBY_ADMINISTRATOR',
		'AQ_ADMINISTRATOR_ROLE',
		'AQ_USER_ROLE',
		'GLOBAL_AQ_USER_ROLE',
		'OEM_MONITOR',
		'HS_ADMIN_ROLE',
		'WM_ADMIN_ROLE',
		'JAVAUSERPRIV',
		'JAVAIDPRIV',
		'JAVASYSPRIV',
		'JAVADEBUGPRIV',
		'EJBCLIENT',
		'JAVA_ADMIN',
		'JAVA_DEPLOY',
		'CTXAPP',
		'XDBADMIN',
		'AUTHENTICATEDUSER',
		'OEM_ADVISOR',
		'SCHEDULER_ADMIN'
	)
order by 
	1;

commit;
