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.

  • Résoudre l'ORA-600 doc id 2523249.1

    Optimizer expression statistics monitoring is introduced in 12.2 database version.

     For specific applications, this might end up in growing repository segments. Monitoring expression statistics is introduced for a new optimizer feature in 12.2 to collect expression usage statistics in SQL queries.

    Monitoring feature is controlled by parameter "_column_tracking_level". High growth have been noticed when monitoring expression statistics is active, enabled by default.

     

     

    Solution:

    Disable future monitoring:

    alter system set "_column_tracking_level"=17 scope=both;

    --wait 10 minutes

     

    To purge data from tables:

    exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();

    truncate table sys.exp_head$ drop storage;

    truncate table sys.exp_obj$ drop storage;

    truncate table sys.exp_stat$ drop storage;

    alter index SYS.I_EXP_HEAD$ rebuild tablespace sysaux online;

    alter index SYS.I_EXP_STAT$ rebuild tablespace sysaux online;

    alter index SYS.I_EXP_OBJ$ rebuild tablespace sysaux online;

     

    To re-enable expression statistics monitoring later :

    alter system set "_column_tracking_level"=21 scope=both;

  • POST MIGRATION ajouter un booleen


    # changer type en booleen
    alter table "<your_table>"
    alter column "<your_column>"
    set data type boolean
    using case
        when "<your_column>" = '1' then true
        when "<your_column>" = '0' then false
        else null
    end;

     

    alter database <dbname> set search_path = "$user", public, <schema> ;

    # post migration pour un schema

    for tablename in $(psql -U postgres -h <hostname> -d <dbname> -t -c "select table_name as _table from information_schema.tables t where t.table_schema='<schema>' order by _table asc"); 
    do
     echo $tablename
     psql -U postgres -h <hostname> -d <dbname> -c "analyze ${tablename};"
     psql -U postgres -h <hostname> -d <dbname> -c "select count(*) ${tablename} from ${tablename};" >> rowcount.log
    done

    # post migration pour plusieurs schemas

    #!/bin/sh
    schema="('sche1','sche2','sche3','...')"

    namespace="."
    dbname="dbname"
    host="hostname"

     for tablename in $(psql -U postgres -h $host -d $dbname -t -c "select table_schema||'.'||table_name as _table from information_schema.tables t where t.table_schema in $schema order by _table asc");
            do
            echo "psql -U postgres -h $host -d $dbname -t -c "select table_schema||'.'||table_name as _table from information_schema.tables t where t.table_schema in $schema order by _table asc""
            echo $tablename
              psql -U postgres -h ${host} -d ${dbname} -c "analyze ${tablename};"
              psql -U postgres -h ${host} -d ${dbname} -c "select count(*) as nr_rows, '${tablename}' as tablename from ${tablename};" >> logs/rowcount.log
     done





    #update sequ

    1-    Récupérer la séquence sur Oracle : 
    SELECT sequence_name, last_number FROM user_sequences; 
    2-    Mettre à jour la séquence sur Postgres : 
    SELECT setval('<nom_sequence',<last_number> , true); 

  • Modify the owner of the tables and the sequences (post migration) 

    Modify the owner of the tables and the sequences (post migration) 




    #change table owner
    for tbl in `psql -h $SERVER$DOMAIN -p $PORT -U <dbauser> -qAt -c "select tablename from pg_tables where schemaname = '<your_schema>';" <database>` ; do  psql -h $SERVER$DOMAIN -p $PORT -U <dbauser> -c "alter table "$tbl" owner to <your_role>" <database>; done

    #change sequence owner
    for tbl in `psql -h $SERVER$DOMAIN -p $PORT -U <dbauser> -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = '<your_schema>';" <database>` ; do  psql -h $SERVER$DOMAIN -p $PORT -U <dbauser> -c "alter sequence "$tbl" owner to <your_role>" <database>; done

  • [SYSTEM]

    This script uses the local tnsnames.ora file to create a connection, and requires the variable ${p} to hold the current SYSTEM password.

    Lire la suite