Ora2PG now supports oracle_fdw to increase the data migration speed
En poursuivant votre navigation sur ce site, vous acceptez l'utilisation de cookies. Ces derniers assurent le bon fonctionnement de nos services. En savoir plus.
WITH RECURSIVE partition_info(relid, relname, relsize, relispartition, relkind)
AS (SELECT oid AS relid, relname, pg_relation_size(oid) AS relsize, relispartition, relkind
FROM pg_catalog.pg_class
WHERE relkind = 'p' AND relname = 'mof_ple_part' -- Pour une table donnee.
UNION ALL
SELECT c.oid AS relid, c.relname AS relname, pg_relation_size(c.oid) AS relsize,
c.relispartition AS relispartition, c.relkind AS relkind
FROM partition_info AS p, pg_catalog.pg_inherits AS i, pg_catalog.pg_class AS c
WHERE p.relid = i.inhparent AND c.oid = i.inhrelid AND c.relispartition)
SELECT * FROM partition_info;
select relnamespace::regnamespace::text schema_name, oid::regclass::text table_name from pg_class
where relkind = 'p' and oid in (select distinct inhparent from pg_inherits)
order by schema_name, table_name;
disable FK
DECLARE @sql NVARCHAR(MAX) = N'';
;WITH x AS
(
SELECT DISTINCT obj =
QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.'
+ QUOTENAME(OBJECT_NAME(parent_object_id))
FROM sys.foreign_keys
)
SELECT @sql += N'ALTER TABLE ' + obj + ' NOCHECK CONSTRAINT ALL;
' FROM x;
EXEC sp_executesql @sql;
enable FK
DECLARE @sql NVARCHAR(MAX) = N'';
;WITH x AS
(
SELECT DISTINCT obj =
QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.'
+ QUOTENAME(OBJECT_NAME(parent_object_id))
FROM sys.foreign_keys
)
SELECT @sql += N'ALTER TABLE ' + obj + ' WITH CHECK CHECK CONSTRAINT ALL;
' FROM x;
EXEC sp_executesql @sql;
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;
show default_tablespace ;
alter system set default_tablespace='tbs_msg_data' ;
select pg_reload_conf();
show default_tablespace
show temp_tablespaces ;
alter system set temp_tablespaces='temp' ;
select pg_reload_conf();
show temp_tablespaces
SELECT *, pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
AND datname = '<dbname>';
# kill sessions
# trouver la sessions
select * from pg_stat_activity;
select pg_terminate_backend(pid)
from pg_stat_activity
where pid = 'PID';
Roles creation for dMZR postgreSQL- version batch
script pour la création d'une nouvelle base dans le cloud IBM avec les rôles dans Hashicorp Vault.
Roles : own , app, devpg
Se connecter sur le serveur cible.
pg_dumpall --roles-only -h remotehost -U remoteuser | psql -h localhost -U localuser
pg_dump -C -h remotehost -U remoteuser dbname | psql -h localhost -U localuser