Il peut être utile d'évaluer une base de données avant de penser à la migrer dans un autre SGBD.
On va alors s'intéresser aux types d'objets qu'elle contient pour valider ou invalider la migration dans tel ou tel SGBDR.
Exemple le plus probant : une migration d'une base oracle dans une base postgreSQL.
La requête suivante générique et passe partout pourra aider à faire l'évaluation.
select (select name from v$database) as database_name, owner,sum(bytes)/1024/1024 as "Number", 'Size in MB' as object_type
from dba_segments
where owner not in ('WMSYS','APPQOSSYS','DBSFWUSER','DBSNMP','AUDSYS','GSMADMIN_INTERNAL','ORACLE_OCM','OUTLN','PUBLIC','REMOTE_SCHEDULER_AGENT','XDB','SYS','SYSTEM')
group by owner
union
select (select name from v$database) as database_name,
col.owner as schema_name, count(distinct col.table_name),
'Lobs' as object_type
from dba_tab_columns col
inner join dba_tables t on col.owner = t.owner and col.table_name = t.table_name
where col.data_type in ('BLOB', 'CLOB', 'NCLOB', 'BFILE')
and col.owner not in ('OAUTHDBSCHEMA','AUDSYS','GSMADMIN_INTERNAL','ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN', 'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST', 'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 'WKSYS', 'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')
group by col.owner
union
SELECT
(select name from v$database) as database_name,
TABLE_OWNER,count(distinct table_name) as partitionned_tables,'Partitions'
FROM dba_tab_partitions
WHERE TABLE_OWNER not in('WMSYS','SYS','SYSTEM','DBSFWUSER','DBSNMP','AUDSYS','GSMADMIN_INTERNAL','XDB','ORACLE_OCM')
group BY TABLE_OWNER
union
select (select name from v$database) as database_name,
owner,
count(distinct object_name),
'Packages' as object_type
from dba_objects where object_type='PACKAGE' and owner not in ('WMSYS','SYS','SYSTEM','DBSFWUSER','DBSNMP','AUDSYS','GSMADMIN_INTERNAL','XDB','ORACLE_OCM')
group by owner
union
select (select name from v$database) as database_name,
owner,
count(distinct object_name),
'Procedures' as object_type
from dba_objects where object_type='PROCEDURE'
and owner not in ('WMSYS','SYS','OUTLN','REMOTE_SCHEDULER_AGENT','SYSTEM','DBSFWUSER','DBSNMP','AUDSYS','GSMADMIN_INTERNAL','XDB','ORACLE_OCM')
group by owner
union
select (select name from v$database) as database_name,
owner,
count(distinct object_name) , 'Functions'
from dba_objects
where object_type='FUNCTION'
and owner not in ('WMSYS','SYS','OUTLN','REMOTE_SCHEDULER_AGENT','SYSTEM','DBSFWUSER','DBSNMP','AUDSYS','GSMADMIN_INTERNAL','XDB','ORACLE_OCM')
group by owner
union
select (select name from v$database) as database_name,
owner ,
count(distinct trigger_name),'Triggers' as object_type
from dba_triggers
where owner not in ('GSMADMIN_INTERNAL','ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS',
'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN',
'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 'WKSYS',
'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')
group by owner
union
select (select name from v$database) as database_name,
owner,count(distinct table_name),'Global Temporary Tables'
from dba_tables
where owner not in ('GSMADMIN_INTERNAL','ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS',
'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN',
'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 'WKSYS',
'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')
and TEMPORARY = 'Y'
group by owner
union
select (select name from v$database) as database_name, OWNER,count(distinct TABLE_NAME) ,'External Tables'
from dba_external_tables
where owner not in ('GSMADMIN_INTERNAL','ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS',
'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN',
'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 'WKSYS',
'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')
group by owner
union
SELECT (select name from v$database) as database_name,schema,count(distinct namespace),'Context' as object_type
FROM dba_context
where schema not in ('GSMADMIN_INTERNAL','ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS',
'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN',
'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 'WKSYS',
'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC','OCOR_OWN')
group by schema;