Appendix

Appendix A: Data type

The following query will help you to get an overview of the data types used in (specified) non-system schemas in Oracle and their corresponding Exasol data types. With this query you can easily find out which data types are not supported. 

select     o.*, count(*) as data_type_count 
from (
select     t.owner, data_type as oracle_data_type,
case     when data_type in ('CHAR', 'NCHAR') then 'CHAR(' || char_length || ')'
when data_type in ('VARCHAR','VARCHAR2', 'NVARCHAR2') then 'VARCHAR(' || char_length || ')'
when data_type in ('CLOB', 'NCLOB') then 'VARCHAR(2000000)'
when data_type = 'XMLTYPE' then 'VARCHAR(2000000)'
when data_type = 'RAW'  then 
case     when data_length <= 1024 then 'HASHTYPE(' || data_length || ' BYTE)'
else 'VARCHAR(' || (data_length * 2) || ') ASCII'
end
when data_type in ('DECIMAL') and (data_precision is not null and data_scale is not null) then
                        case    when data_scale > 36 then 'DECIMAL(' || 36 || ',' || 36 || ')' 
                                when data_precision > 36 and data_scale <= 36 then 'DECIMAL(' || 36 || ',' || data_scale || ')' 
                                when data_precision <= 36 and data_scale > data_precision then 'DECIMAL(' || data_scale || ',' || data_scale || ')' 
                                else 'DECIMAL(' || data_precision || ',' || data_scale || ')' 
                        end
                when data_type = 'NUMBER' and (data_precision is not null and data_scale is not null) then
                case    when data_scale > 36 then 'DECIMAL(' || 36 || ',' || 36 || ')' 
                        when data_precision > 36 and data_scale <= 36 then 'DECIMAL(' || 36 || ',' || data_scale || ')' 
                                when data_precision <= 36 and data_scale > data_precision then 'DECIMAL(' || data_scale || ',' || data_scale || ')' 
                                else 'DECIMAL(' || data_precision || ',' || data_scale || ')' 
                        end
                when data_type = 'NUMBER' and (    data_length is not null and data_precision is null and data_scale is not null) then 'INTEGER' 
                when data_type = 'NUMBER' and (data_precision is null and data_scale is null) then 'DOUBLE PRECISION'
                when data_type in ('DOUBLE PRECISION', 'FLOAT', 'BINARY_FLOAT', 'BINARY_DOUBLE') then 'DOUBLE PRECISION'
                when data_type = 'DATE' then 'TIMESTAMP'
                when data_type like 'TIMESTAMP(%)%' or data_type like 'TIMESTAMP%' then    'TIMESTAMP'
                when data_type like 'TIMESTAMP%WITH%TIME%ZONE%' then 'TIMESTAMP' 
                when data_type like 'INTERVAL YEAR%TO MONTH%' then 'INTERVAL YEAR(' || case when data_precision = 0 then 1 else data_precision end || ') TO MONTH'
                when data_type like 'INTERVAL DAY%TO SECOND%' then 'INTERVAL DAY(' || case when data_precision = 0 then 1 else data_precision end || ') TO SECOND(' || data_scale || ')'
                else 'UNSUPPORTED DATA TYPE: ' || data_type
            end exasol_data_type
from         dba_tab_columns t
left join dba_recyclebin r on t.table_name = r.object_name and t.owner = r.owner
where         
--owner in ('C##DB_MIG') and
t.owner not in (
'SYS', 'AUDSYS', 'SYSTEM', 'SYSBACKUP', 'SYSDG', 'SYSKM', 
'SYSRAC', 'OUTLN', 'XS$NULL', 'GSMADMIN_INTERNAL', 'GSMUSER', 
'GSMROOTUSER', 'DIP', 'REMOTE_SCHEDULER_AGENT', 'DBSFWUSER', 
'ORACLE_OCM', 'SYS$UMF', 'LBACSYS', 'DBSNMP', 'APPQOSSYS', 
'GSMCATUSER', 'GGSYS', 'XDB', 'ANONYMOUS', 'WMSYS'
)
and r.object_name is null
) o
group by owner, oracle_data_type, exasol_data_type
order by owner, oracle_data_type, exasol_data_type
;

Appendix B: View texts

This query provides information about the views in (specified) non-system tables. The view text length can be used as a simple indicator of complexity, while the status field might help you to filter out invalid views to create a better estimate of the effort required for the migration.

select     v.owner, v.view_name, v.text_length, o.created, v.text, o.status
from     dba_views v join dba_objects o on v.owner = o.owner and v.view_name = o.object_name
where     
--v.owner in ('C##DB_MIG') and
o.object_type = 'VIEW' and
v.owner not in (
'SYS', 'AUDSYS', 'SYSTEM', 'SYSBACKUP', 'SYSDG', 'SYSKM', 
'SYSRAC', 'OUTLN', 'XS$NULL', 'GSMADMIN_INTERNAL', 'GSMUSER', 
'GSMROOTUSER', 'DIP', 'REMOTE_SCHEDULER_AGENT', 'DBSFWUSER', 
'ORACLE_OCM', 'SYS$UMF', 'LBACSYS', 'DBSNMP', 'APPQOSSYS', 
'GSMCATUSER', 'GGSYS', 'XDB', 'ANONYMOUS', 'WMSYS'
)
order by v.owner, v.view_name
;

Appendix C: Materialized view texts

The query below provides information about materialized views used in (specified) non-system schemas. For example, the text length is a simple indicator for complexity of the underlying query and the refresh mode / method give you better understanding of the materialized view configuration. The query should help you to define a migration path for materialized views and estimate the migration effort for related to that. 

select     mv.owner, mv.mview_name, mv.query_len, mv.query, mv.rewrite_enabled, mv.rewrite_capability, mv.refresh_mode, mv.refresh_method, mv.build_mode, mv.fast_refreshable, mv.last_refresh_type, mv.last_refresh_date, mv.last_refresh_end_time, mv.staleness, mv.stale_since
from dba_mviews mv 
where 
--mv.owner in ('C##DB_MIG') and
mv.owner not in (
'SYS', 'AUDSYS', 'SYSTEM', 'SYSBACKUP', 'SYSDG', 'SYSKM', 
'SYSRAC', 'OUTLN', 'XS$NULL', 'GSMADMIN_INTERNAL', 'GSMUSER', 
'GSMROOTUSER', 'DIP', 'REMOTE_SCHEDULER_AGENT', 'DBSFWUSER', 
'ORACLE_OCM', 'SYS$UMF', 'LBACSYS', 'DBSNMP', 'APPQOSSYS', 
'GSMCATUSER', 'GGSYS', 'XDB', 'ANONYMOUS', 'WMSYS'
)
order by mv.owner, mv.mview_name
;

Appendix D: External tables and directories

The following query gives you an overview of external tables in the database. The query contains information about the locations and access drivers, so that you can plan the migration path for external tables (view with subimport for on the-fly loading, or explicit scheduled import into table).

select t.owner, t.table_name, t.type_owner, t.type_name, l.location, l.directory_owner, l.directory_name
from dba_external_tables t 
join dba_external_locations l on t.owner = l.owner and t.table_name = l.table_name
where 
--t.owner in ('C##DB_MIG') and
t.owner not in (
'SYS', 'AUDSYS', 'SYSTEM', 'SYSBACKUP', 'SYSDG', 'SYSKM', 
'SYSRAC', 'OUTLN', 'XS$NULL', 'GSMADMIN_INTERNAL', 'GSMUSER', 
'GSMROOTUSER', 'DIP', 'REMOTE_SCHEDULER_AGENT', 'DBSFWUSER', 
'ORACLE_OCM', 'SYS$UMF', 'LBACSYS', 'DBSNMP', 'APPQOSSYS', 
'GSMCATUSER', 'GGSYS', 'XDB', 'ANONYMOUS', 'WMSYS'
)
;

Appendix E: Functions, procedures, and packages

This query gives you an overview of the of existing procedures in your database, their types, lines of code, and text length as a simple indicator of complexity to allow for a more accurate estimation of effort for the migration.

select p.owner, p.object_type, p.object_name, p.procedure_name, p.aggregate, max(s.line) AS lines_of_code, sum(LENGTH(s.text)) text_length
from dba_procedures p JOIN dba_source s ON p.owner = s.owner AND p.object_name = s.name AND p.object_type = s.type
where 
--owner in ('C##DB_MIG') and
p.owner not in (
'SYS', 'AUDSYS', 'SYSTEM', 'SYSBACKUP', 'SYSDG', 'SYSKM', 
'SYSRAC', 'OUTLN', 'XS$NULL', 'GSMADMIN_INTERNAL', 'GSMUSER', 
'GSMROOTUSER', 'DIP', 'REMOTE_SCHEDULER_AGENT', 'DBSFWUSER', 
'ORACLE_OCM', 'SYS$UMF', 'LBACSYS', 'DBSNMP', 'APPQOSSYS', 
'GSMCATUSER', 'GGSYS', 'XDB', 'ANONYMOUS', 'WMSYS'
)
GROUP BY p.owner, p.object_type, p.object_name, p.procedure_name, p.aggregate
;

Appendix F: Privileges granted to roles/users

This query provides an overview of the privileges granted to roles/users. For Exasol privileges, see Details on Rights Management.

select case when r.role is null then 'USER' else 'ROLE'end as USER_ROLE, COALESCE(r.ROLE, p.grantee) as GRANTEE, privilege, p.owner, p.table_name, p.grantable, p.hierarchy, p.type
from dba_tab_privs p left join dba_roles r on grantee = role 
left join dba_recyclebin r on p.table_name = r.object_name and p.owner = r.owner
where grantee not in ( --users
'ADM_PARALLEL_EXECUTE_TASK', 'APPLICATION_TRACE_VIEWER', 'APPQOSSYS', 'AQ_ADMINISTRATOR_ROLE', 'AQ_USER_ROLE', 
'AUDIT_ADMIN', 'AUDIT_VIEWER', 'AUDSYS', 'BDSQL_ADMIN', 'BDSQL_USER', 'CAPTURE_ADMIN', 'CDB_DBA', 'DATAPATCH_ROLE', 
'DATAPUMP_IMP_FULL_DATABASE', 'DBA', 'DBFS_ROLE', 'DBMS_MDX_INTERNAL', 'DBSFWUSER', 'DBSNMP', 'DIP', 'EM_EXPRESS_ALL', 
'EM_EXPRESS_BASIC', 'EXECUTE_CATALOG_ROLE', 'EXP_FULL_DATABASE', 'GATHER_SYSTEM_STATISTICS', 'GDS_CATALOG_SELECT', 'GGSYS', 
'GSMADMIN_INTERNAL', 'GSMADMIN_ROLE', 'GSMCATUSER', 'GSMROOTUSER_ROLE', 'GSMUSER_ROLE', 'GSM_POOLADMIN_ROLE', 
'HS_ADMIN_EXECUTE_ROLE', 'HS_ADMIN_SELECT_ROLE', 'IMP_FULL_DATABASE', 'LBACSYS', 'LBAC_DBA', 'LOGSTDBY_ADMINISTRATOR', 
'OEM_MONITOR', 'OPTIMIZER_PROCESSING_RATE', 'ORACLE_OCM', 'OUTLN', 'PDB_DBA', 'PUBLIC', 'RECOVERY_CATALOG_OWNER', 
'RECOVERY_CATALOG_OWNER_VPD', 'RECOVERY_CATALOG_USER', 'REMOTE_SCHEDULER_AGENT', 'SELECT_CATALOG_ROLE', 'SODA_APP', 
'SYS$UMF', 'SYS', 'SYSBACKUP', 'SYSDG', 'SYSKM', 'SYSRAC', 'SYSTEM', 'SYSUMF_ROLE', 'WMSYS', 'WM_ADMIN_ROLE', 'XDB', 
'XDBADMIN', 'XS_CACHE_ADMIN', 'XS_SESSION_ADMIN'
)
and r.object_name is null
order by 1, 2, 3, 4, 5
;

Appendix G: Users and their roles

The following query shows the user roles a user has.

select grantee as "USER", listagg(granted_role, ', ' on overflow truncate ' ... ' with count) within group(order by granted_role) roles
from dba_role_privs 
where grantee not in (
            'DATAPUMP_EXP_FULL_DATABASE', 'DATAPUMP_IMP_FULL_DATABASE', 'DBA', 'DBSNMP', 'EM_EXPRESS_ALL', 'EM_EXPRESS_BASIC', 
            'EXECUTE_CATALOG_ROLE', 'EXP_FULL_DATABASE', 'GSMADMIN_INTERNAL', 'GSMADMIN_ROLE', 'GSMCATUSER', 'GSMROOTUSER', 
            'GSMROOTUSER_ROLE', 'GSMUSER', 'GSMUSER_ROLE', 'GSM_POOLADMIN_ROLE', 'HS_ADMIN_ROLE', 'IMP_FULL_DATABASE', 'LBACSYS', 
            'LOGSTDBY_ADMINISTRATOR', 'OEM_MONITOR', 'RECOVERY_CATALOG_OWNER_VPD', 'RESOURCE', 'SELECT_CATALOG_ROLE', 'SYS$UMF', 
            'SYS', 'SYSBACKUP', 'SYSRAC', 'SYSTEM', 'SYSUMF_ROLE', 'WMSYS', 'XDB'
        )
group by grantee
order by 1
;

Appendix H: System privileges

The following query shows the system privileges of users/roles.

select min(case when r.role is null then 'USER' else 'ROLE' end) as type, grantee, listagg(privilege, ', ' on overflow truncate ' ... ' with count) within group(order by privilege)
from dba_sys_privs p left join dba_roles r on grantee = role
where grantee not in (
'ANONYMOUS', 'APPQOSSYS', 'AQ_ADMINISTRATOR_ROLE', 'AUDIT_ADMIN', 'AUDSYS', 'CDB_DBA', 'CONNECT', 'DATAPATCH_ROLE', 
'DATAPUMP_EXP_FULL_DATABASE', 'DATAPUMP_IMP_FULL_DATABASE', 'DBA', 'DBSFWUSER', 'DBSNMP', 'DIP', 'EM_EXPRESS_ALL', 
'EM_EXPRESS_BASIC', 'EXP_FULL_DATABASE', 'GGSYS', 'GSMADMIN_INTERNAL', 'GSMADMIN_ROLE', 'GSMCATUSER', 'GSMUSER_ROLE', 
'IMP_FULL_DATABASE', 'LBACSYS', 'OEM_ADVISOR', 'OEM_MONITOR', 'ORACLE_OCM', 'RECOVERY_CATALOG_OWNER', 
'RECOVERY_CATALOG_OWNER_VPD', 'RESOURCE', 'SCHEDULER_ADMIN', 'SYS$UMF', 'SYS', 'SYSBACKUP', 'SYSDG', 
'SYSKM', 'SYSRAC', 'SYSTEM', 'SYSUMF_ROLE', 'WMSYS', 'XDB', 'XS_CONNECT'
)
group by grantee
;

Appendix I: DB link connections

The following query provides you information about DB Links in the database. Removing the comments will also show you the passwords used for the DB Link. The DB Link information can be used for CONNECTIONS in Exasol.

select l.owner, l.db_link, l.username, l.host, l.created, l.valid
/*
, v.passwordx
*/
from dba_db_links l 
/*
join SYS.KU$_DBLINK_VIEW v on l.owner = v.owner_name and l.db_link = v.name and l.host = v.host and (l.username = v.userid or (l.username is null and v.userid is null))
*/
;