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 5

  • Dataguard - qqs commandes bien utiles

    Source : https://valehagayev.wordpress.com/2016/07/09/dataguard-commands-and-sql-scripts/

    start redo apply in foreground

    stop redo apply process on the Standby database (to stop MRP)

    start real-time redo apply

    start redo apply in background

    check redo apply  and Media recovery service status

    gather Data Guard configuration information(standby)

    calculate the Redo bytes per second

    check status of Data Guard synchronization(standby)

    verify there is no log file gap between the primary and the standby database

    verify that the primary database can be switched to the standby role

    convert the primary database into a physical standby

    verify Managed Recovery is running on the standby

    show information about the protection mode, the protection level, the role of the database, and switchover status

    On the standby database, query the V$ARCHIVED_LOG view to identify existing files in the archived redo log

    On the standby database, query the V$ARCHIVED_LOG view to verify the archived redo log files were applied.

    To determine which log files were not received by the standby site.

    http://www.aodba.com/steps-stop-start-oracle-standby-database/

    https://support.oracle.com/knowledge/Oracle%20Database%20Products/1221163_1.html

    MRP : Managed Recovery Process

     

    et bien plus encore sur le DG Broker .

  • DBA ORACLE

    It's now easier for people to find your Page in search. People can also visit your Page at fb.me/oracledbajoly and send messages to your Page at m.me/oracledbajoly.

  • SQL ANSI - TEST

    https://lamp-dev.com/elance-ansi-sql-code-test-with-answers/109

    1. Consider a table named “salary” having the following columns:

    “id” (type: INT)
    “salary” (type: INT)
    “incentive” (type: INT)
    “tax” (type: INT)

    Write a standard SQL query which will update the tax column with the sum of 10% of salary and 2% of incentive, for those salaries which are more than 15000.

    UPDATE salary SET tax = 0.1*salary+0.02*incentive WHERE salary > 15000


    2. Consider a table named “employee” having the following columns:

    “empid” (type: INT)
    “empname” (type: TEXT)
    “salary” (type: INT)

    Write a standard SQL query which retrieves the empnames whose values start with the string ‘john’ followed by any characters.
        
    SELECT empname FROM employee WHERE empname LIKE 'john%'

    3. Consider a table named “employee” having the following columns:

    “empid” (type: INT)
    “empname” (type: TEXT)
    “salary” (type: INT)

    Write a standard SQL query which retrieves the number of rows where the salary is not null. The returned value should be represented using the column name “validsalarycount”.

    SELECT count(salary) AS validsalarycount FROM employee WHERE salary IS NOT NULL


    4. Consider a table named “store” having the following columns:

    “storename” (type: TEXT)
    “sales” (type: INT)
    “Date” (type: DATE)

    Write a standard SQL query which retrieves the storenames, whose sales lie between 100 and 2000 (not inclusive). The storenames should not be repeated.

    SELECT DISTINCT storename FROM  store WHERE sales > 100 AND sales < 2000


    5. Consider a table named “staff” having the following column structure:

    “empid” (type: INT)
    “empname” (type: TEXT)
    “salary” (type: INT)

    Write a standard SQL query which retrieves the sum of 75 percent of the salaries from the staff table (only salaries above 5000 are to be considered). The returned value should be represented using the column name ‘total’.

    SELECT sum(0.75*salary) AS total FROM staff WHERE salary > 5000


    6. Consider the following tables:

    department
    ———-
    deptid (type: INT)
    deptname (type: TEXT)
    hours (type: INT)
    active (type: BIT)

    employee
    ——–
    empid (type: INT)
    empname (type: TEXT)
    deptid (type: INT)
    designation (type: TEXT)
    salary (type: INT)

    Write a query to return the columns empname and deptname of the employees belonging to those departments that have a head count of 4 or more. The records should be returned in alphabetical order of empname.

    SELECT e.empname, d.deptname FROM department d INNER JOIN employee e WHERE e.deptid=d.deptid
    WHERE e.deptid IN ( SELECT e.deptid FROM employee d GROUP BY e.deptid HAVING COUNT(e.deptid) >=4 ) ORDER BY e.empname ASC;

    7. Consider a table called carrecords with the following structure:

    name (type: TEXT)
    price (type: INT)
    color (type: TEXT)
    vehicletype (type: TEXT) eg. SEDAN/SUV

    A customer wants to see the details (name, price, color, vehicletype) of the vehicles that suit his preferences. This is what he says:

    Write a query to return the columns empname and deptname of the employees belonging to those departments that have a head count of 4 or more. The records should be returned in alphabetical order of empname.
    “If its a black sedan, I’m ready to pay 10,000, but if its red or white, then no more than 8,000. For any other color I won’t go above 7,000, except if its an SUV, in which case my budget is upto 15,000 for a black one or upto 14,000 for any other color.”
        

    SELECT name, price, color, vehicletype FROM carrecords WHERE
    (vehicletype = 'SEDAN' AND color = 'black' AND price <= 10000)
    OR (vehicletype = 'SEDAN' AND color IN('red','white') AND price <= 8000 )
    OR (vehicletype = 'SUV' AND color = 'black' AND price <= 15000)
    OR (vehicletype = 'SUV' AND color != 'black' AND price <= 14000)
    OR (price <= 7000)
    ORDER BY price ASC
        
    SELECT name, price, color, vehicletype FROM carrecords WHERE vehicletype = 'SEDAN' AND color  = 'BLACK' AND price <= 10000 UNION
    SELECT name, price, color, vehicletype FROM carrecords WHERE vehicletype = 'SEDAN' AND color IN ('red','white') AND price <= 8000 UNION
    SELECT name, price, color, vehicletype FROM carrecords WHERE vehicletype  = 'SEDAN' AND color NOT IN('RED','WHITE','BLACK') AND price <= 7000 UNION
    SELECT name, price, color, vehicletype FROM carrecords WHERE vehicletype = 'SUV' AND color  = 'BLACK' AND price <= 15000 UNION
    SELECT name, price, color, vehicletype FROM carrecords WHERE vehicletype = 'SUV' AND color  != 'BLACK' AND price <= 14000)
    ORDER BY price ASC;

    8. Consider a database with a table called “accounts”, having two fields:

    “entrydate” (type: DATE)
    “accountno” (type: INT)

    Write a SQL query which returns the accountno of the most recent entrydate. The returned value should be represented using the column name, “accountno”.
        
    SELECT accountno FROM accounts ORDER BY entrydate DESC LIMIT 0,1;

    9. Consider a table called “students”, having the following column fields:

    “id” (type: INT)
    “name” (type: TEXT)
    “marks” (type: INT)

    Write a SQL query which will calculate the average of the marks of the students passing. The passing criteria is that the marks should be at least 40. The average marks are to be returned using the column name ‘marksaverage’.
        
    SELECT avg(marks) AS marksaverage FROM students WHERE id IN (SELECT id FROM students WHERE marks >= 40)

    10. Consider a table called “department”, having the following columns:

    “id” (type: INT)
    “deptname” (type: TEXT)
    “rank” (type: INT)

    Write a SQL query which will return the deptnames of the departments whose rank lies between 2 and 5 (inclusive). The results should be returned in increasing order of rank (rank 3 being higher than rank 6).
        
    SELECT deptname FROM department WHERE rank >= 2 AND rank <= 5 ORDER BY rank ASC

  • Note de frais en CDI

    Se faire rembourser ses notes de frais dans le cadre d'un CDI, c'est possible, en voici la preuve ici.

     

    https://www.juritravail.com/chiffres-et-indices/frais-professionnels-allocations-forfaitaires-limites-exoneration.html

  • Méthode de prospection pour devenir freelance

    _s'inscrire sur les sites généralistes https://www.freelance-info.fr/ https://www.freelance.com/,
    _candidater aux missions en cours en evnoyant son cv en postulant comme freelance,
    _s'inscrire sur les sites des ESN comme HAYS, AKKA, CELAD…Ou encore, les sites des cabinets spécialisés dans le recrutement de freelance (Mushroom, Urban Linker, Agence-e),
    _avoir un profil linkedin à jour, un profil malt à jour.

  • Tracer sql query dans base oracle

    •    Pour tracer les requêtes SQL dans une base Oracle il faut passer la commande :
     
    execute dbms_monitor.database_trace_enable(waits=>TRUE,binds=>TRUE,instance_name=>'QUALIF');
     
    •    Pour stopper la trace :
    execute dbms_monitor.database_trace_disable(instance_name=>'QUALIF');
     
    •    La trace sera stockée sous :
     
    select
       value
    from
       v$diag_info
    where
       name ='Default Trace File';
     
    Exemple :
    /export/BD/ORACLE/oratrc/QUALIF/bdump/diag/rdbms/qualif/QUALIF/trace/QUALIF_ora_58689.trc
     
    Elle pourra être lisible avec tkprof  :
     
    Syntaxe : tkprof <nom_de_la_trace> <nom_du_fichier_texte_lisible> sys=no  
     
    Exemple:             
    $tkprof QUALIF_ora_7767.trc QUALIF_ora_7767.txt sys=no  
     
    L’option sys = no ne garde pas les requêtes lancées par l’utilisateur sys.

  • Règle Boyce Codd et dénormalisation

    Règles de Boyce

    Une dénormalisation intelligente est basée sur cinq principes de base :

    • rajouter des colonnes calculées persistantes, voire des index calculés;
    • mettre en place des index couvrants et/ou filtrés;
    • utiliser la compression des données;
    • utiliser des vues matérialisées (Oracle ou PostgreSQL) ou indexées (SQL Server);
    • partitionner les très grandes tables.
  • Trouver les clés étrangères qui font référence à un champ dans une table x

    --donne la liste des clés étrangères
    select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name
      from all_constraints
     where constraint_type='R' and R_OWNER='nom_du_schéma'
       and r_constraint_name in (select constraint_name
                                   from all_constraints
                                  where constraint_type in ('P','U')
                                    and table_name='nom_de_la_table');

  • Problème d'accès ssh après maintenance de serveur

    https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/6/html/deployment_guide/installing_sssd-starting_and_stopping_sssd

     

    Starting and Stopping SSSD - Red Hat Customer Portal

  • Passer du SQL oracle au SQL ansi

    https://stackoverflow.com/questions/18891148/oracle-joins-comparison-between-conventional-syntax-vs-ansi-syntax#

     

     

    ┌───────────────────────────────────┬─────────────────────────────────────────────┐
     INNER JOIN - CONVENTIONAL          INNER JOIN - ANSI SYNTAX                    
    ├───────────────────────────────────┼─────────────────────────────────────────────┤
     SELECT                             SELECT                                      
          emp.deptno                          ename,                                
     FROM                                     dname,                                
          emp,                                emp.deptno,                           
          dept                                dept.deptno                           
     WHERE                              FROM                                        
          emp.deptno = dept.deptno;           scott.emp INNER JOIN scott.dept       
                                              ON emp.deptno = dept.deptno;          
    ├───────────────────────────────────┼─────────────────────────────────────────────┤
     LEFT OUTER JOIN - CONVENTIONAL     LEFT OUTER JOIN - ANSI SYNTAX               
    ├───────────────────────────────────┼─────────────────────────────────────────────┤
     SELECT                             SELECT                                      
          emp.deptno                         ename,                                 
     FROM                                    dname,                                 
          emp,                               emp.deptno,                            
          dept                               dept.deptno                            
     WHERE                              FROM                                        
          emp.deptno = dept.deptno(+);       scott.emp LEFT OUTER JOIN scott.dept   
                                             ON emp.deptno = dept.deptno;           
    ├───────────────────────────────────┼─────────────────────────────────────────────┤
     RIGHT OUTER JOIN - CONVENTIONAL    RIGHT OUTER JOIN - ANSI SYNTAX              
    ├───────────────────────────────────┼─────────────────────────────────────────────┤
     SELECT                             SELECT                                      
          emp.deptno                         ename,                                 
     FROM                                    dname,                                 
          emp,                               emp.deptno,                            
          dept                               dept.deptno                            
     WHERE                              FROM                                        
          emp.deptno(+) = dept.deptno;       scott.emp RIGHT OUTER JOIN scott.dept  
                                             ON emp.deptno = dept.deptno;           
    ├───────────────────────────────────┼─────────────────────────────────────────────┤
     FULL OUTER JOIN - CONVENTIONAL     FULL OUTER JOIN - ANSI SYNTAX               
    ├───────────────────────────────────┼─────────────────────────────────────────────┤
     SELECT                             SELECT                                      
          *                                  *                                      
     FROM                               FROM                                        
          emp,                               scott.emp FULL OUTER JOIN scott.dept   
          dept                               ON emp.deptno = dept.deptno;           
     WHERE                                                                          
          emp.deptno = dept.deptno(+)                                               
     UNION ALL                                                                      
     SELECT                                                                         
          *                                                                         
     FROM                                                                           
          emp,                                                                      
          dept                                                                      
     WHERE                                                                          
          emp.deptno(+) = dept.deptno                                               
          AND emp.deptno IS NULL;                                                   
    └───────────────────────────────────┴─────────────────────────────────────────────┘
  • Comparer deux schémas Oracle

    compare.sh

     

    #!/bin/sh
    # Exemple : sh compare.sh 'SCHEMA1' 'SCHEMA2' 'DEV'
    # Modifier la valeur de ORACLE_SID si besoin
    export ORACLE_SID=DEV
    SCHEMA1=$1
    SCHEMA2=$2
    DBLINK=$3
    echo "Schema1="$SCHEMA1" ; Schema2="$SCHEMA2"@"$DBLINK
    sqlplus / as sysdba <<-_EOF
    @COMP1.sql $SCHEMA1 $SCHEMA2 $DBLINK;
    exit;
    _EOF

     

    COMP1.sql

    -- Au prealable on a cree un repertoire pour les scripts generes :
    -- CREATE DIRECTORY SCRIPTS_GENERES as '/export/home/oracle/SCRIPTS_GENERES/'
    SET SERVEROUTPUT ON
    SET VERIFY OFF
    DECLARE
        CURSOR c1 IS
            select table1, table2 from
                (select table_name table1 from dba_tables where owner='&1') t1
                full outer join
                (select table_name table2 from dba_tables@&3 where owner='&2') t2
                on t1.table1=t2.table2
            where (table1 is null and table2 not like 'BIN$%') or (table2 is null and table1 not like 'BIN$%')
            order by table1, table2;
            
        CURSOR c2 IS
            select table1, column1, table2, column2 from
                (select table_name table1, column_name column1
                from dba_tab_columns
                where owner='&1'
                and table_name not in (select view_name from dba_views where owner='&1')) t1
                full outer join
                (select table_name table2, column_name column2 from dba_tab_columns@&3
                where owner='&2'
                and table_name not in (select view_name from dba_views@&3 where owner='&2')) t2
                on t1.table1=t2.table2 and t1.column1=t2.column2
            where (column1 is null and table2 not like 'BIN$%') or (column2 is null and table1 not like 'BIN$%')
            order by table1, table2, column1, column2;
            
        CURSOR c3 is
            select table1, column1, type1, length1, precision1, scale1, table2, column2, type2, length2, precision2, scale2
            from
                (select table_name table1, column_name column1, data_type type1, data_length length1, data_precision precision1, data_scale scale1
                from dba_tab_columns
                where owner='&1'
                and table_name not in (select view_name from dba_views where owner='&1')) t1
                full outer join
                (select table_name table2, column_name column2, data_type type2, data_length length2, data_precision precision2, data_scale scale2
                from dba_tab_columns@&3
                where owner='&2'
                and table_name not in (select view_name from dba_views@&3 where owner='&2')) t2
                on t1.table1=t2.table2 and t1.column1=t2.column2
            where type1<>type2
            or length1<>length2
            or precision1<>precision2
            or scale1<>scale2
            or (type1 is not null and precision1 is null and precision2 is not null)
            or (type2 is not null and precision2 is null and precision1 is not null)
            order by table1, table2, column1, column2;
            
        CURSOR c4 IS
            select table1, table2 from
                (select view_name table1 from dba_views where owner='&1') t1
                full outer join
                (select view_name table2 from dba_views@&3 where owner='&2') t2
                on t1.table1=t2.table2
            where (table1 is null and table2 not like 'BIN$%') or (table2 is null and table1 not like 'BIN$%')
            order by table1, table2;
            
        CURSOR c5 IS
            select v1.view_name table1, v1.text_length length1, v2.view_name table2, v2.text_length length2
            from dba_views v1
            full outer join dba_views@&3 v2
            on v1.owner=v2.owner and v1.view_name=v2.view_name
            where v1.text_length<>v2.text_length
            and v1.owner='&1'
            and v2.owner='&2';
            
        filesortie UTL_FILE.FILE_TYPE;
        table1 varchar2(50);
        table2 varchar2(50);
        column1 varchar2(50);
        column2 varchar2(50);
        type1 varchar2(50);
        type2 varchar2(50);
        length1 varchar2(10);
        length2 varchar2(10);
        precision1 varchar2(10);
        precision2 varchar2(10);
        scale1 varchar2(10);
        scale2 varchar2(10);
    BEGIN
        filesortie:= UTL_FILE.FOPEN('SCRIPTS_GENERES', 'resultat_comparaison.csv', 'W', 10000);
            
        UTL_FILE.PUT_LINE(filesortie,'Schema 1;Schema 2');
        UTL_FILE.NEW_LINE(filesortie);
        
        UTL_FILE.PUT_LINE(filesortie,'Tables schema 1;Tables schema 2');
        OPEN c1;
        LOOP
            FETCH c1 INTO table1, table2;
            EXIT WHEN c1%NOTFOUND;
            IF table1 is not null THEN
                UTL_FILE.PUT_LINE(filesortie,table1||';_');
            ELSIF table2 is not null THEN
                UTL_FILE.PUT_LINE(filesortie,'_;'||table2);
            END IF;
        END LOOP;
        CLOSE c1;
        UTL_FILE.NEW_LINE(filesortie);
        
        UTL_FILE.PUT_LINE(filesortie,'Colonnes schema 1;Colonnes schema 2');
        OPEN c2;
        LOOP
            FETCH c2 INTO table1, column1, table2, column2;
            EXIT WHEN c2%NOTFOUND;
            IF column1 is not null THEN
                UTL_FILE.PUT_LINE(filesortie,table1||'('||column1||');_');
            ELSIF column2 is not null THEN
                UTL_FILE.PUT_LINE(filesortie,'_;'||table2||'('||column2||')');
            END IF;
        END LOOP;
        CLOSE c2;
        UTL_FILE.NEW_LINE(filesortie);
        
        UTL_FILE.PUT_LINE(filesortie,'Types colonnes schema 1;Types colonnes schema 2');
        OPEN c3;
        LOOP
            FETCH c3 INTO table1, column1, type1, length1, precision1, scale1, table2, column2, type2, length2, precision2, scale2;
            EXIT WHEN c3%NOTFOUND;
            UTL_FILE.PUT(filesortie,table1||'('||column1||') : '||type1);
            UTL_FILE.PUT(filesortie,'('||length1||'/'||precision1||'/'||scale1||')');
            UTL_FILE.PUT(filesortie,';');
            UTL_FILE.PUT(filesortie,table2||'('||column2||') : '||type2);
            UTL_FILE.PUT(filesortie,'('||length2||'/'||precision2||'/'||scale2||')');
            UTL_FILE.NEW_LINE(filesortie);
        END LOOP;
        CLOSE c3;
        UTL_FILE.NEW_LINE(filesortie);
        
        UTL_FILE.PUT_LINE(filesortie,'Vues schema 1;Vues schema 2');
        OPEN c4;
        LOOP
            FETCH c4 INTO table1, table2;
            EXIT WHEN c4%NOTFOUND;
            IF table1 is not null THEN
                UTL_FILE.PUT_LINE(filesortie,table1||';_');
            ELSIF table2 is not null THEN
                UTL_FILE.PUT_LINE(filesortie,'_;'||table2);
            END IF;
        END LOOP;
        CLOSE c4;
        UTL_FILE.NEW_LINE(filesortie);
        
        UTL_FILE.PUT_LINE(filesortie,'Longueur texte vues schema 1;Longueur texte vues schema 2');
        OPEN c5;
        LOOP
            FETCH c5 INTO table1, length1, table2, length2;
            EXIT WHEN c5%NOTFOUND;
            UTL_FILE.PUT_LINE(filesortie,table1||' : '||length1||';'||table2||' : '||length2);
        END LOOP;
        CLOSE c5;

        UTL_FILE.FCLOSE(filesortie);

        DBMS_OUTPUT.PUT_LINE('Fin de l''execution du script');    
        DBMS_OUTPUT.PUT_LINE('Le resultat de la comparaison a ete genere dans le dossier /export/home/oracle/SCRIPTS_GENERES/');
    END;
    /

  • Table precision

    Bonjour mon client a une table PRECISION qui contient une colonne TYPE qui stocke :

     

     <

    =

     

    J'offre une prime à celui ou celle qui m'explique l'intérêt de faire cela ???