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 ???

Les Virtual Private Database (VPD) sur PGSQL

Les Virtual Private Database (VPD) sur PGSQL sont disponbiles depuis la version 9.1.

 

source : https://www.enterprisedb.com/blog/virtual-private-database-vpd-ppas-91

Hidden Virtual Column

https://blogs.oracle.com/sql/ora-54033-and-the-hidden-virtual-column-mystery

select column_name, data_default, hidden_column 
from   user_tab_cols
where  table_name = 'TAB';

COLUMN_NAME                    DATA_DEFAULT                   HID
-----------	               ------------                   ---
SYS_STUYPW88OE302TFVBNC6$MMQXE SYS_OP_COMBINED_HASH("X","Y"   YES
Z                                                             NO
Y                                                             NO
X                                                             NO

column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL]

https://oracle-base.com/articles/11g/virtual-columns-11gr1

Guide survie statpack

https://www.akadia.com/services/ora_statspack_survival_guide.html

PRocédure pour envoi de mail après insertion dans une table oracle

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:7267435205059

Ajouter une image dans une base

procédure stockée pour ajouter une image de cul  dans la table GONZO de ma base de données PORNO :

 

create or replace procedure ajoute_image_X
(
    nom_fichier in VARCHAR2,
    p_id_image in VARCHAR2,
    p_nom_actrice in VARCHAR2,
    p_no_film in VARCHAR2,
    p_mensuration in INTEGER
)
IS
    dest_secure     BLOB;
    file_dest         BFILE;
    blob_size         INTEGER;
    extension         VARCHAR2(3);
BEGIN
    BEGIN
        file_loc := BFILENAME ('BFILE_DATA', nom_fichier);
        DBMS_LOB.FILEOPEN (file_dest);
        blob_size := DBMS_LOB.GETLENGTH (file_dest);
        extension := substr(nom_fichier, -3, 3);

        INSERT INTO GONZO (
            ID_IMAGE,
            NOM_FICHIER,
            NOM_ACTRICE,
            NO_FILM,
            MENSURATION,
            FSSIZE,
            FSTYPE,
            CONTENU)
        VALUES (
            p_id_image,
            nom_fichier,
            p_nom_actrice,
            p_no_film,
            p_mensuration,
            blob_size,
            extension,
            EMPTY_BLOB())
            RETURNING CONTENU INTO dest_secure;

        DBMS_LOB.LOADFROMFILE (dest_secure, file_dest, blob_size);
        DBMS_LOB.FILECLOSE (file_dest);

        commit;
    END;
END ajoute_image_X;

Insérer plusieurs données bidon

SQL> set timing on

SQL> set autotrace traceonly

SQL> insert into rd

  2  select

  3       (dbms_random.value(1,1.99999999999999999999999999999999999999)) r_id,

  4       dbms_random.string('U',20) r_string from dual

  5  connect by level <=100000

  6  /

source : https://community.oracle.com/thread/3696816

Library Cache Miss

Explaination of

• Misses in library cache during parse
  • (Misses while looking for plan in library cache–Hard parse)
• Misses in library cache during execute
  • (Misses while about to execute the plan and found it missing/invalid in library cache)

source : https://jonathanlewis.wordpress.com/2007/07/19/library-cache-miss/

Mise en trace oracle , Tuning avec TKPROF

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 :
 
/export/BD/ORACLE/oratrc/QUALIF/bdump/diag/rdbms/qualif/QUALIF/trace/
 
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.

 

source : http://www.dbrev.com/dbBlog/2014/12/31/how-to-enable-various-level-of-trace-in-oracle-11g-database/

 

tuning avec tkprof : https://oracle.developpez.com/guide/tuning/tkprof/

oradebug

http://what-when-how.com/Tutorial/topic-16mglm7e/Secrets-of-the-Oracle-Database-520.html

Show replication status in PostgreSQL

https://www.niwi.nz/2013/02/16/replication-status-in-postgresql/

 

select client_addr, state, sent_location, write_location,
        flush_location, replay_location from pg_stat_replication;

This query can output can be like this:

postgres=# select client_addr, state, sent_location,write_location, flush_location, replay_location, sync_priority from pg_stat_replication;

Disable Streaming Replication without restarting either master or slave

I understand the way things work currently, but would it not be more
convenient to let the replication be stopped and started from SQL commands
like:

On Master:

select pg_pause_streaming_replication(slave_fqdn);
select pg_resume_streaming_replication(slave_fqdn);

On Slave:

select pg_pause_streaming_replication();
select pg_pause_streaming_replication();

If Cascaded Streaming Replication is confugured, then

On Slave:

select pg_start_streaming_replication(cascaded_slave_fqdn);
select pg_stop_streaming_replication(cascaded_slave_fqdn);

Or an OS executable [ either in the core or a contrib module] like

pg_replication -p primary_fqdn -s slave_fqdn -a pause
pg_replication -p primary_fqdn -s slave_fqdn -a resume
pg_replication -p primary_fqdn -s slave_fqdn -a setup

[ the last one would awesome; and i do not think that it is impossible, and
would be loved by one and all ]

[UNIX] convertir un paquage debian en rpm et vice versa

https://blog.packagecloud.io/eng/2015/10/26/use-alien-to-convert-deb-to-rpm-and-rpm-to-deb/

 

https://help.ubuntu.com/community/RPM/AlienHowto