Ok

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.

Professionnel - Page 7

  • [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



  • [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;