2 links :
JSONB PostgreSQL: How To Store & Index JSON Data (scalegrid.io)
RDBMS Expertise - Page 6
-
About JSON in RDBMS
-
Désactiver le mode visual sur VI
J'ai remarqué un truc bien chiant sur les nouvelles VM linux : le clic droit de la souris ne peut plus coller...!
pour y remédier :
echo "set compatible" > ~/.vimrc
echo "set mouse-=a" >> ~/.vimrc -
PG_BADGER
pgbadger -j 4 --outfile $HOME/rapport_complet.html -f stderr -p '%t:%r:%u@%d:[%p]:' /pglog/postgresql-2021-09-10_000000.log
-
Configuration application cloud postgres
USERNAME :: postgres://<instance>/<role>/user
PASSWORD ::postgres://<instance>/<role>/password
URL :: jdbc:postgresql://<instance>.<domain>:<port>/<dbname>
-
[ora2pg script] Data and metadata export (multiple schemas)
#!/bin/sh
#-------------------------------------------------------------------------------
#
# Created by joguess the Oracle database admin, version 2022
#
#-------------------------------------------------------------------------------
#data export
EXPORT_TYPE="COPY"
SCHEMA="TITI TOTO TATA"
namespace="."
for etype in $(echo $EXPORT_TYPE | tr " " "n")
do
for eschema in $(echo $SCHEMA | tr " " "n")
do
ltype=`echo $etype | tr '[:upper:]' '[:lower:]'`
ltype=`echo $ltype | sed 's/y$/ie/'`
echo "Running: ora2pg -t $etype -o $ltype_$eschema.sql -n $eschema -b $namespace/data -c $namespace/config/ora2pg.conf"
time ora2pg -t $etype -o $ltype_$eschema.sql -n $eschema -b $namespace/data -c $namespace/config/ora2pg.conf -j 8 -J 3 -L 60000
ret=`grep "Nothing found" $namespace/data/$ltype.sql 2> /dev/null`
if [ ! -z "$ret" ]; then
rm $namespace/data/$ltype.sql
fi
done
done
echo
echo
echo "Extraction effectuee sous :"
echo
echo " $namespace/data"
echo
exit 0
____________________________________
#!/bin/sh
#-------------------------------------------------------------------------------
#
# Created by joguess the Oracle database admin, version 2022
#
#-------------------------------------------------------------------------------
#metadata export
EXPORT_TYPE="SEQUENCE TABLE PACKAGE VIEW GRANT TRIGGER FUNCTION PROCEDURE PARTITION TYPE MVIEW SYNONYM"
SCHEMA="TITI TOTO TATA"
namespace="."
for etype in $(echo $EXPORT_TYPE | tr " " "n")
do
for eschema in $(echo $SCHEMA | tr " " "n")
do
ltype=`echo $etype | tr '[:upper:]' '[:lower:]'`
ltype=`echo $ltype | sed 's/y$/ie/'`
echo "Running: ora2pg -t SHOW_TABLE -n $eschema -c $namespace/config/ora2pg.conf > $namespace/reports/tables_$eschema.txt"
time ora2pg -t SHOW_TABLE -n $eschema -c $namespace/config/ora2pg.conf > $namespace/reports/tables_$eschema.txt
echo "Running: ora2pg -t SHOW_TABLE -n $eschema -c $namespace/config/ora2pg.conf > $namespace/reports/tables_$eschema.txt"
time ora2pg -t SHOW_TABLE -n $eschema -c $namespace/config/ora2pg.conf > $namespace/reports/tables_$eschema.txt
echo "Running ora2pg -t SHOW_COLUMN -n $eschema -c $namespace/config/ora2pg.conf > $namespace/reports/columns_$eschema.txt"
time ora2pg -t SHOW_COLUMN -n $eschema -c $namespace/config/ora2pg.conf > $namespace/reports/columns_$eschema.txt
echo "Running: ora2pg -t SHOW_REPORT -n $eschema -c $namespace/config/ora2pg.conf --estimate_cost --cost_unit_value 10 --dump_as_html > $namespace/reports/assessment/assessment_$eschema.html"
time ora2pg -t SHOW_REPORT -n $eschema -c $namespace/config/ora2pg.conf --estimate_cost --cost_unit_value 10 --dump_as_html > $namespace/reports/assessment/assessment_$eschema.html
echo "Running: ora2pg -p -t $etype -o $ltype_$eschema.sql -n $eschema -b $namespace/schema/${ltype}s -c $namespace/config/ora2pg.conf"
time ora2pg -p -t $etype -o $ltype_$eschema.sql -n $eschema -b $namespace/schema/${ltype}s -c $namespace/config/ora2pg.conf
ret=`grep "Nothing found" $namespace/schema/$ltype.sql 2> /dev/null`
if [ ! -z "$ret" ]; then
rm $namespace/schema/$ltype.sql
fi
done
done
echo
echo
echo "Extraction effectuee sous :"
echo
echo " $namespace/schema"
echo
exit 0 -
Service HAS
Faire repartir le service HAS.
Nettoyage : /grid/oracle/product/19.0/bin/crsctl stop has -f
Faire repartir : /grid/oracle/product/19.0/bin/crsctl start has
-
Ora2PG now supports oracle_fdw to increase the data migration speed
-
[PostgreSQL] Voir les tables partitionnées
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; -
[MSSQL] Disable/Enable FK
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;
-
[ORACLE]Evaluer une base de données
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;