SelectEtoile : Base de connaissance SGBD

Posts Tagged 'oracle'

Présentation du site

 
Ce site a été développé afin de fournir des explications, documentations et astuces en tout genre concernant les bases de données.Ils proviennent de retour d'expériences de différents DBA.
Il est à noter que ces articles ne sont que des notes et en aucune façon des documentations officielles.
Ce site peut contenir des commandes non supportées par les différents éditeurs qui sont à utiliser avec précaution.
Bien sur, si vous constatez des erreurs ou avez des remarques sur un ou plusieurs articles, n'hésitez pas à nous en faire part, soit via l'onglet Contacts, soit via les commentaires à votre disposition à la fin de chaque article.
 
Pour ceux qui seraient intéressés pour écrire ou proposer des articles, n'hésitez pas à me contacter via 'Contacts' dans le menu du haut.
flux_rssInscrivez vous au fil d'actualité selectetoile.net flux_rss

TORA

TORA

 

Description

L'outil a été dévolppé pour ORACLE mais supporte MySQL, PostGreSQL aussi

Voici les différentes fonctionnalités de celui-ci :

Contient les différents outils pour monitorer une requête (explain plan, statistics)

Extraction des objets et même des schémas

Arborescence de tous les objets

Recherche avancée

Éditeur et débugger PL/SQL / Remontée des erreurs de structure

Tuning de Serveur (Caractéristiques du serveur, I/O par tablespace, Indicateurs de performances, Statistiques...)

Outil de comparaison des schémas et des objets

Gestion de la sécurité, du stockage et des sessions..

...


 

Installation

Pour télécharger, http://sourceforge.net/projects/tora/files/

Le site officiel :  http://torasql.com/

Pour l'installation, il suffit de décompresser le fichier dans un répertoire

 

Lancement

Lancer tora.exe

 

 

 

 

Articles tagged

Utilisation de COPY FROM

Tache à réaliser

Copier une table d’une base vers une autre.

 

Le usage de la commande

usage: COPY FROM <db> TO <db> <opt> <table> { (<cols>) } USING <sel>

  <db>   : database string, e.g., hr/your_password@d:chicago-mktg

  <opt>  : ONE of the keywords: APPEND, CREATE, INSERT or REPLACE

  <table>: name of the destination table

  <cols> : a comma-separated list of destination column aliases

  <sel>  : any valid SQL SELECT statement

A missing FROM or TO clause uses the current SQL*Plus connection.

 

 

Le plus produit

Pas besoin d’avoir de droit DBA

Pas de déclaration dans le tnsnames.ora

Pas de création de db link

Pour import/export, il faut de l’espace disque et ce sont 2 étapes

Cette méthode peut être utile à un client et éviter l’intervention du DBA (heu … réfléchir 10s secondes avant quand même)

On peut sélectionner les colonnes, limiter le result set avec une clause where …

 

 

Modus operandi

La commande COPY ne fait comme son nom l’indique que la copie des données, pas la création de la table.

 

Etape 1 : création de la table cible

 

Sur la source, on lance utilise un package standard Oracle pour générer le DDL :

 

SQL> set long 10000

SQL> select dbms_metadata.get_ddl('TABLE','TABLE_NAME','SCHEMA') from dual;

 

DBMS_METADATA.GET_DDL('TABLE','TABLE_NAME','SCHEMA')

--------------------------------------------------------------------------------

 

  CREATE TABLE "SCHEMA"."TABLE_NAME"

   (    "COL1" DATE NOT NULL ENABLE

   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

  STORAGE(INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

  TABLESPACE "SCHEMA_DAT_64K"

 

Nota bene : le SET LONG 10000 n’est pas que décoratif ; sans cela l’ordre SQL est tronqué

 

Sur la target, copy / paste de l’ordre

 

SQL>   CREATE TABLE "SCHEMA"."TABLE_NAME"

  2     (    "COL1" DATE NOT NULL ENABLE

  3    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

  4    STORAGE(INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645

  5    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

  6    TABLESPACE "SCHEMA_DAT_64K"

  7  /

 

Table created.

 

Etape 2 : la copie proprement dite

 

SQL> copy from SCHEMA/schema_pwd@'//host_source:1526/ORACLE_SID_SOURCE' to SCHEMA/SCHEMA_PWD@ORACLE_SID_CIBLE INSERT TABLE_NAME USING SELECT * from TABLE_NAME;

 

Array fetch/bind size is 15. (arraysize is 15)

Will commit when done. (copycommit is 0)

Maximum long size is 80. (long is 80)

   30 rows selected from SCHEMA@//host_source:1526/ORACLE_SID_SOURCE.

   30 rows inserted into TABLE_NAME.

   30 rows committed into TABLE_NAME at SCHEMA@ORACLE_SID_CIBLE.

 

Nota bene : on a copier la table mais pas ses index, ni ses contraintes, ni ses triggers …

 

Explications

En 9i on peut se connecter à une base en se passant de la résolution de nom, ie. du tnsnames.ora en indiquant explicitement toutes les information :

 

$ sqlplus SCHEMA/schema_pwd@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host_source)(PORT=1526)))(CONNECT_DATA=(SID =ORACLE_SID_SOURCE)))'

 

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Jun 18 18:23:44 2009

 

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

 

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

 

SQL> select instance_name from v$instance;

 

INSTANCE_NAME

----------------

ORACLE_SID_SOURCE

 

Depuis la 10g on peut se connecter à une base en indiquant une descritpion syntétique « //host:port/SID »

 

$ sqlplus SCHEMA/schema_pwd@'//host_source:1526/ORACLE_SID_SOURCE'

                                                                          

Pour plus d’info : http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/apb.htm

 

 

Apparement dans la doc, on peut même créer la table mais je ne vois pas comment lui spécifier un tablespace (la table ira dans le tablespace par default L) :

 

SQL> copy from SCHEMA/schema_pwdt@'//host_source:1526/ORACLE_SID_SOURCE' -

> create SCHEMA.TABLE_NAME -

> tablespace SCHEMA_DAT_64K -

> USING SELECT * from TABLE_NAME;

SP2-0498: missing parenthetical column list or USING keyword

 

SQL> ! oerr SP2 0498

00498,0, "missing parenthetical column list or USING keyword\n"

// *Cause:  A parenthetical list was missing in the column list or the

//          USING keyword is missing in the COPY command.

// *Action: Check the syntax of the COPY command for the correct

//          options.

 

SQL> copy from SCHEMA/schema_pwd@'//host_source:1526/ORACLE_SID_SOURCE' -

> create SCHEMA.TABLE_NAME -

> USING SELECT * from TABLE_NAME -

> tablespace SCHEMA_DAT_64K;

 

Array fetch/bind size is 15. (arraysize is 15)

Will commit when done. (copycommit is 0)

Maximum long size is 80. (long is 80)

 

SP2-0502: SCHEMA_DAT_64K

SP2-0503: *

SP2-0501: Error in SELECT statement: ORA-00933: SQL command not properly ended

 

 

 

Merci à Nicolas pour cet article

 

Articles tagged

Trace Serveur Session ORACLE

  • Différents paramètres :

TIMED_STATISTICS : mise en place de la collecte des informations sur le temps d’attente d’un événement.

STATISTICS_LEVEL : Niveau de collecte des statistics (Basic, Typical ou ALL)

DB_CACHE_ADVICE : off, ready ou on

TIMED_OS_STATISTICS

SQL_TRACE

LOG_CHECKPOINT_TO_ALERT

  • Serveur :
    • V$systat : activité compteur de l’instance (voir aussi v$statname)

    • V$system_event : compteur d’attente (associé à v$event_name(name))


  • Session :

    • V$session v$sesstat v$statname

    • V$session_wait (temps reel) v$event_name

    • V$session_event (cumul)

    • V$mystat : stat de ma session

    • Exemples de requête :

select vsn.name,vst.value from v$sesstat vst,v$statname vsn where vst.sid=7 and vst.STATISTIC#=vsn.STATISTIC# order by 2

 

select * from v$session_wait where sid=7


SQL> select s.sid,s.username, n.name, t.value

from v$statname n, v$session s, v$sesstat t

where s.sid=t.sid

and n.statistic#=t.statistic#

and s.type='USER'

and n.name='session pga memory'

order by 4


USERNAME NAME VALUE

------------------------------ ---------------------------------------------------------------- ----------

SYS session pga memory 357848

 

  • Exemple de trace d'une requete

 

Alter system set sql_trace : Setting sql_trace=true is a prerequisite when using tk prof.

Alter system set timed_statistics : Setting timed_statistics=true might be usefule when using tk prof

Mise en place de la trace sur une session :

alter system set sql_trace=true;

alter session set sql_trace=true;

sys.dbms_system.set_sql_trace_in_session(session's id,serial number, true)

alter system set timed_statistics=true;

alter session set timed_statistics=true;

 

 

Les fichiers de trace sont dans user_dump_dest de v$parameter

Une fois les traces mises, nous lancons la requete problematique, puis nous mettons à false les traces ou on se deconnecte.

Pour analyser les traces, il suffit d’aller dans le repertoire de log et de lancer « tkprof ‘nom_fic_trc’ ‘fic_out’ »

 

 

 

  • Divers

    • Connaître le plan d’une requete : ‘explain plan for’

    • Connaître activité user : trace tkprof

    • Mise en place des traces : ‘alter session set SQL_TRACE true ;’

    • Voir plan d’un requete dans library cache : v$sql_plan

 

  • Analyser les trace oracle
Dans le pfile:
    sql_trace=true
    user_dump_dest=$USER_DUMP_DEST

ou dans une session:

exec sys.dbms_system.set_sql_trace_in_session(SID,SERIAL#,true);

Analyse des fichier traces:

for i in  $(ls *.trc)
do
 tkprof $i $i.out explain=system/cdcmgr sys=no sort=prscnt,prscpu,prsela,execpu,execnt,exerow,exemis,fchcpu,fchela,fchdsk,fchrow,userid waits=yes
done

 

 

  • Session consommatrice en espace TEMP :

 

SELECT   se.osuser, se.username, se.sid,su.extents, su.blocks * to_number(rtrim(p.value)) AS Space,tablespace
FROM v$sort_usage su, v$parameter p, v$session se
WHERE    p.name = 'db_block_size'
AND      su.session_addr = se.saddr

 

 

 

  • Session consommatrice en PGA :

 

select s.sid,s.username, n.name, t.value

from v$statname n, v$session s, v$sesstat t

where s.sid=t.sid

and n.statistic#=t.statistic#

and s.type='USER'

and n.name='session pga memory'

order by 4

 

 

  •  Trace d'une requête consommatrice (ex : vue v_today_diffusions)  :

SET LONG 2000

set linesize 2000

set pagesize 2000

set time on

set timing on

set autotrace traceonly explain

select * from v_today_diffusions;

-- explain plan for select * from v_today_diffusions;

-- select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));

exit

 

 

Exemple Import ORACLE

Suppression des connexions sur le schéma à charger

 sqlplus /nolog <<-END_SQL

        connect / as sysdba

        select  'DBADEV' "ADMIN",

                        '$schema' "SCHEMA",

                        substr(s.sid,1,3) "SID",

                        substr(s.serial#,1,6) "SERIAL",

                        substr(p.spid,1,7) "PROCESS"

        from v_\$SESSION s , v_\$PROCESS p

        where s.paddr = p.addr

        and type = 'USER'

        and s.username = UPPER('$schema')

        order by s.SID;

        END_SQL

 

sqlplus /nolog <<-END_SQL

        connect / as sysdba

        alter system disconnect session '$sid,$serial' immediate;

        END_SQL

 

Drop des objets en cascade

 -- Recupere les ordres de drop des tables

sqlplus $schema/$($KSH_ADM/decrypter $ALIAS_INSTANCE $schema) <<-END_SQL

        SPOOL /tmp/tmp1_$$.log;

        SET PAGESIZE 0;

        SELECT 'DROP TABLE ' || ' ' || OBJECT_NAME || ' CASCADE CONSTRAINTS ;'

        FROM USER_OBJECTS

        WHERE OBJECT_TYPE = 'TABLE';

        SPOOL OFF;

        END_SQL

        grep '^DROP TABLE' /tmp/tmp1_$$.log > /tmp/tmp1_$$.cmd

 

        sqlplus $schema/$($KSH_ADM/decrypter $ALIAS_INSTANCE $schema) <<-END_SQL

 

        -- Executer le drop des tables

        @/tmp/tmp1_$$.cmd;

 

        -- Recupere les ordres de drop des autres objets

        SPOOL /tmp/tmp1_$$.log;

        SET PAGESIZE 0;

        SELECT 'DROP ' || OBJECT_TYPE || ' ' || OBJECT_NAME || ' ;'

        FROM USER_OBJECTS

        WHERE OBJECT_TYPE != 'TABLE'

        ORDER BY OBJECT_TYPE;

        SPOOL OFF;

        END_SQL

        grep '^DROP ' /tmp/tmp1_$$.log > /tmp/tmp1_$$.cmd

 

        # Executer le drop des autres objets

        sqlplus $schema/$($KSH_ADM/decrypter $ALIAS_INSTANCE $schema) <<-END_SQL

        @/tmp/tmp1_$$.cmd;

        END_SQL

 

Création d’un pipe nommé

 /etc/mknod /tmp/mypipe$$ p

 

Détar dans le pipe nommé de l’export :

         gzip -d < $ORADUMP/$FICHIER_DUMP > /tmp/mypipe$$ &

 

Mise en place du TBS par défaut pour les données :

 

         sqlplus /nolog << EOF

                connect / as sysdba

                alter user ${schema} default tablespace ${TBS_DATA};

                alter user ${schema} account lock;

                exit

         EOF

 

 

Import des données

         imp \'/ as sysdba\' indexes=n rows=y ignore=y constraints=n fromuser=${SCHEMA_SOURCE} touser=${schema} file=/tmp/mypipe$$ log=$log_data

 

 

Détar dans le pipe nommé de l’export :

         gzip -d < $ORADUMP/$FICHIER_DUMP > /tmp/mypipe$$ &

 

 

Mise en place du TBS par défaut pour les indexes :

         sqlplus /nolog << EOF

                connect / as sysdba

                alter user ${schema} default tablespace ${TBS_IDX};

                alter user ${schema} account lock;

                exit

         EOF

 

Import des indexes

imp \'/ as sysdba\' indexes=y rows=n ignore=y constraints=y grants=n fromuser=${SCHEMA_SOURCE} touser=${schema} file=/tmp/mypipe

$$ log=$log_ind

 

 

Vérification des objets invalides

 

 

 

Articles tagged

Rebuild d'indexes partitionnés parallélisés entre partitions et intra-partitions

Un package standard Oracle permet de paralléliser la reconstruction des partions d’index.

Le parallélisme est entre les partitions et au sein de chaque partition.

 

Exemple sur une table de 38 millions de lignes sur un index unique composite : 4 minutes !

 

SQL> connect / as sysdba

Connected.

SQL> alter system set job_queue_processes=16;

 

System altered.

 

SQL> connect user/pass

Connected.

 

SQL> create UNIQUE index USER.ITEM_COMP_MATCHID_ID on USER.ITEM(MATCHID,ID) LOCAL tablespace USER_ITEM_IDX storage (buffer_pool RECYCLE) unusable;

 

Index created.

 

Elapsed: 00:00:00.12

 

SQL> set serveroutput on size 10000

SQL> EXECUTE dbms_pclxutil.build_part_index(4,4,'ITEM','ITEM_COMP_MATCHID_ID',TRUE);                                                                  

INFO: Job #21 created for partition ITEM_001 with 4 slaves

INFO: Job #22 created for partition ITEM_002 with 4 slaves

INFO: Job #23 created for partition ITEM_003 with 4 slaves

INFO: Job #24 created for partition ITEM_004 with 4 slaves

INFO: Job #25 created for partition ITEM_005 with 4 slaves

INFO: Job #26 created for partition ITEM_006 with 4 slaves

INFO: Job #27 created for partition ITEM_007 with 4 slaves

INFO: Job #28 created for partition ITEM_008 with 4 slaves

INFO: Job #29 created for partition ITEM_009 with 4 slaves

INFO: Job #30 created for partition ITEM_010 with 4 slaves

INFO: Job #31 created for partition ITEM_011 with 4 slaves

INFO: Job #32 created for partition ITEM_012 with 4 slaves

INFO: Job #33 created for partition ITEM_UNMATCHED with 4 slaves

 

PL/SQL procedure successfully completed.

 

Elapsed: 00:04:00.73

Gestion des UNDO Tablespaces

 

Contexte

Les segments d’annulations (undo segments) sont utilisés principalement dans les cas suivants :

            => Annulation d'une transaction (permettant de restaurer la valeur d'origine à partir des undo segments qui contiennent l'image de la valeur d'origine)

            => Récupération de transaction (sur échec et réouverture de base permettant une restauration des transactions non validées contenues dans les redo log)

            => Cohérence en lecture (permettant aux autres utilisateurs de consulter les valeurs contenues dans les undo segments pour les transactions non validées)

            => par les flashback queries (permettant d’avoir une image des données telles quelle étaient dans le temps - en se basant sur une date ou sur un SCN)

Il y a 3 types de undo segments (cas du mode Automatic Undo Management) :

            - ACTIVE : utilisé par une transaction en cours,

            - UNEXPIRED : plus actif mais qui contient des données potentiellement utilisables par les flash-back queries,

            - EXPIRED : hors de la période de rétention. Oracle les garde quand même, au cas où (réutilisation sur une base LRU)

Quand il y a un besoin d'extent dans le tablespace de UNDO :

            a) Oracle essaie d'abord de s'allouer de l'espace libre

            b) s'il n'y en a plus tape dans EXPIRED

            c) s'il n'y en a plus tape dans UNEXPIRED

            d) s'il n'y en a plus panique.

b) compromet les flash-back queries, mais pas dans la période de rétention.

En revanche c) compromet non seulement les flashback queries dans la période de rétention mais devient également un risque d'ORA-01555 (snapshot too old)

Enfin d) peut être bloquant pour la base.

 

 

Objectif

Contrôle de la répartition des undo segments :

select status, round(sum(bytes)/1024/1024,0) "SIZE (M)"  from dba_undo_extents group by status

Identification et suivi de l’activité UNDO par rapport à une session :

SELECT rn.name "Rollback Segment Name",

                        rs.xacts "Active Transactions" ,

                        t.used_ublk "Nb Used Blocs",

t.start_time "Start time",

                        s.username ||' ('|| s.sid|| ',' || s.serial# ||')' "DB User (SID, SERIAL#)",

                        p.spid "Process ID",

                        s.osuser "OS User", s.machine "Machine", s.program "Program"

            FROM v$rollname rn,v$rollstat rs, v$transaction t, v$session s, v$process p

            WHERE rn.usn = rs.usn and

                        rs.usn = t.xidusn and

                        s.taddr = t.addr and

                        s.paddr = p.addr

            ORDER BY 1

Identification et suivi de l’activité UNDO par rapport à une requête :

SELECT rn.name "Rollback Segment Name",

                        rs.xacts "Active Transactions" ,

                        t.used_ublk "Nb Used Blocs",

                        s.username ||' ('|| s.sid|| ',' || s.serial# ||')' "DB User (SID, SERIAL#)",

                        sq.sql_text "SQL Text", p.spid "Process ID",

                        s.osuser "OS User", s.machine "Machine", s.program "Program"

            FROM v$rollname rn,v$rollstat rs, v$transaction t, v$session s,v$sqlarea sq, v$process p

            WHERE rn.usn = rs.usn and

                        rs.usn = t.xidusn and

                        s.taddr = t.addr and

                        s.sql_address = sq.address(+) and

                        s.sql_hash_value = sq.hash_value(+) and

                        s.paddr = p.addr

            ORDER BY 1

 

 

Bug existant :

A noter un important bug 5387030 - Automatic tuning of undo_retention causes unusual extra space allocation, dont voici un extrait de l’article Métalink  :

When undo tablespace is using NON-AUTOEXTEND datafiles, V$UNDOSTAT.TUNED_UNDORETENTION may be calculated too high preventing undo block from being expired and reused. In extreme cases the undo tablespace could be filled to capacity by these unexpired blocks.

An alert may be posted on DBA_ALERT_HISTORY that advises to increase the space when it is not really necessary if this fix is applied.

If the user sets their own alert thresholds for undo tablespaces the bug may prevent alerts from being produced.

Workaround:

alter system set "_smu_debug_mode" = 33554432;

This causes the v$undostat.tuned_undoretention to be calculated as the maximum of:

- maxquerylen secs + 300

- undo_retention specified in init.ora

Gestion des OUTLINEs sous ORACLE

Rappel

Les outlines sont des directives passées à l’optimizer sous forme de hint afin de fixer – avec plus ou moins de réussite – les plans d’exécution.

 

Objectif

Obtenir des informations sur les outlines sur une base.

 

Problème

La requête SQL sur laquelle a été définie la outline apparait dans les vues comme un champ LONG ce qui ne facilite pas la consultation.

On ne peut notamment pas faire de substr() sur un champ LONG.

 

SQL> select substr(SQL_TEXT,1,100) from DBA_OUTLINES;

select substr(SQL_TEXT,1,100) from DBA_OUTLINES

              *

ERROR at line 1:

ORA-00932: inconsistent datatypes: expected NUMBER got LONG

 

SQL> select substr(to_char(SQL_TEXT),1,100) from DBA_OUTLINES;

select substr(to_char(SQL_TEXT),1,100) from DBA_OUTLINES

              *

ERROR at line 1:

ORA-00932: inconsistent datatypes: expected CHAR got LONG

 

 

Exemple de listing :

 

Stored Ouline.......: OUTLN_MSG_POSTING_1

Category............: APPLI

Creator / Owner.....: user/ pass

hash_value..........: 2014187508

hash_value2.........: 4144314494

Enabled.............: Yes

Used................: Yes

Creation date.......: 28/10/2009 16:07:25

Hint count..........: 44

Sql Text Length.....: 1210

______________SQL TEXT EXTRACT________________________________

select ID,ACCOUNT_ENTITY_ID,POSTING_TYPE,POSTING_RULE_ID,TRADE_ID,VERSION_ID,GENERATION_DATE-TO_DATE('01/01/1904','DD/MM/YYYY'),POSTING_DATE-TO_DATE('

01/01/1904','DD/MM/YYYY'),ACCOUNT_NUMBER,ACCOUNT_CURRENCY,AMOUNT,CREDIT_DEBIT,THIRD_PARTY_ID,INSTRUMENT_ID,CURRENCY,QUANTITY,SIGN,AUXILIARY1_ACCOUNT,AUXILIARY2_ACCOUNT,AUXILIARY3_ACCOUNT,JOURNAL_ENTRY

,COMMENTS,STATUS,AUXILIARY_DATE-TO_DATE('01/01/1904','DD/MM/YYYY'),ACCOUNT_NAME_ID,AUXILIARY1_ACCOUN

UXILIARY2_ACCOUNT,AUXILIARY3_ACCOUNT,JOURNAL_ENTRY,COMMENTS,STATUS,AUXILIARY_DATE-TO_DATE('01/01/1904','DD/MM/YYYY'),ACCOUNT_NAME_ID,AUXILIARY1_ACCOUNT_ID,AUXILIARY2_ACCOUNT_ID,AUXILIARY3_ACCOUNT_ID,L

INK_ID,RULE_TYPE,POSITION_ID,AUXILIARY_ID,DATENEG-TO_DATE('01/01/1904','DD/MM/YYYY'),HEURENEG from MSG_POSTING where POSTING_DATE-TO_DATE('01-01-1904','DD-MM-YYYY')<=:c1 and (POSTING_TYPE=:c2 or

POSTING_TYPE=:c3 or POSTING_TYPE=:c4 or POSTING_TYPE=:c5

SG_POSTING where POSTING_DATE-TO_DATE('01-01-1904','DD-MM-YYYY')<=:c1 and (POSTING_TYPE=:c2 or POSTING_TYPE=:c3 or POSTING_TYPE=:c4 or POSTING_TYPE=:c5 or POSTING_TYPE=:c6) and (STATUS=:c7 or

STATUS=:c8 or STATUS=:c9) and ACCOUNT_ENTITY_ID=:c10 and ACCOUNT_CURRENCY=:c11 and INSTRUMENT_ID=:c12 and AUXILIARY1_ACCOUNT=:c13 and AUXILIARY2_ACCOUNT=:c14 and AUXILIARY1_ACCOUNT_ID=:c15 and

AUXILIARY2_ACCOUNT_ID=:c16 and (AUXILIARY3_ACCOUNT=:c17 or  AUXILIARY3_ACCOUNT is NULL ) and (AUXILIARY3_ACCOUNT_ID=:c18 or  AUXILIARY3_ACCOUNT_ID is NULL ) order by AUXILIARY_ID, DATENEG, HEURENEG,

POSTING_DATE, TRADE_ID, ID

XILIARY1_ACCOUNT=:c13 and AUXILIARY2_ACCOUNT=:c14 and AUXILIARY1_ACCOUNT_ID=:c15 and AUXILIARY2_ACCOUNT_ID=:c16 and (AUXILIARY3_ACCOUNT=:c17 or  AUXILIARY3_ACCOUNT is NULL ) and

(AUXILIARY3_ACCOUNT_ID=:c18 or  AUXILIARY3_ACCOUNT_ID is NULL ) order by AUXILIARY_ID, DATENEG, HEURENEG, POSTING_DATE, TRADE_ID, ID

______________END OF SQL TEXT EXTRACT_________________________

 

 

 

 

Stored Ouline.......: OUTLN_MSG_POSTING_2

Category............: APPLI

Creator / Owner.....: user / pass

hash_value..........: 3171669409

hash_value2.........: 937014734

Enabled.............: Yes

Used................: Yes

Creation date.......: 28/10/2009 16:07:34

Hint count..........: 44

Sql Text Length.....: 1167

______________SQL TEXT EXTRACT________________________________

select ID,ACCOUNT_ENTITY_ID,POSTING_TYPE,POSTING_RULE_ID,TRADE_ID,VERSION_ID,GENERATION_DATE-TO_DATE('01/01/1904','DD/MM/YYYY'),POSTING_DATE-TO_DATE('

01/01/1904','DD/MM/YYYY'),ACCOUNT_NUMBER,ACCOUNT_CURRENCY,AMOUNT,CREDIT_DEBIT,THIRD_PARTY_ID,INSTRUMENT_ID,CURRENCY,QUANTITY,SIGN,AUXILIARY1_ACCOUNT,AUXILIARY2_ACCOUNT,AUXILIARY3_ACCOUNT,JOURNAL_ENTRY

,COMMENTS,STATUS,AUXILIARY_DATE-TO_DATE('01/01/1904','DD/MM/YYYY'),ACCOUNT_NAME_ID,AUXILIARY1_ACCOUN

UXILIARY2_ACCOUNT,AUXILIARY3_ACCOUNT,JOURNAL_ENTRY,COMMENTS,STATUS,AUXILIARY_DATE-TO_DATE('01/01/1904','DD/MM/YYYY'),ACCOUNT_NAME_ID,AUXILIARY1_ACCOUNT_ID,AUXILIARY2_ACCOUNT_ID,AUXILIARY3_ACCOUNT_ID,L

INK_ID,RULE_TYPE,POSITION_ID,AUXILIARY_ID,DATENEG-TO_DATE('01/01/1904','DD/MM/YYYY'),HEURENEG from MSG_POSTING where POSTING_DATE-TO_DATE('01-01-1904','DD-MM-YYYY')<=:c1 and (POSTING_TYPE=:c2 or

POSTING_TYPE=:c3) and (STATUS=:c4 or STATUS=:c5) and ACC

SG_POSTING where POSTING_DATE-TO_DATE('01-01-1904','DD-MM-YYYY')<=:c1 and (POSTING_TYPE=:c2 or POSTING_TYPE=:c3) and (STATUS=:c4 or STATUS=:c5) and ACCOUNT_ENTITY_ID=:c6 and ACCOUNT_NUMBER=:c7 and

ACCOUNT_NAME_ID=:c8 and ACCOUNT_CURRENCY=:c9 and INSTRUMENT_ID=:c10 and AUXILIARY1_ACCOUNT=:c11 and AUXILIARY2_ACCOUNT=:c12 and AUXILIARY1_ACCOUNT_ID=:c13 and AUXILIARY2_ACCOUNT_ID=:c14 and

(AUXILIARY3_ACCOUNT=:c15 or  AUXILIARY3_ACCOUNT is NULL ) and (AUXILIARY3_ACCOUNT_ID=:c16 or  AUXILIARY3_ACCOUNT_ID is NULL ) order by DATENEG, HEURENEG, POSTING_DATE, TRADE_ID, ID

ILIARY2_ACCOUNT=:c12 and AUXILIARY1_ACCOUNT_ID=:c13 and AUXILIARY2_ACCOUNT_ID=:c14 and (AUXILIARY3_ACCOUNT=:c15 or  AUXILIARY3_ACCOUNT is NULL ) and (AUXILIARY3_ACCOUNT_ID=:c16 or

AUXILIARY3_ACCOUNT_ID is NULL ) order by DATENEG, HEURENEG, POSTING_DATE, TRADE_ID, ID

______________END OF SQL TEXT EXTRACT_________________________

 

Code PL/SQL

set linesize 200

set serveroutput on size 100000

alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';

 

DECLARE

 

    CURSOR c_ol

    IS  SELECT   a.OL_NAME

                ,a.HASH_VALUE

                ,a.HASH_VALUE2

                ,a.CATEGORY

                ,a.SQL_TEXT

                ,a.CREATOR

                ,a.TEXTLEN

                ,a.HINTCOUNT

                ,b.USED

                ,b.ENABLED

                ,b.OWNER

                ,b.TIMESTAMP as TIM

        FROM    OUTLN.OL$ a,

                DBA_OUTLINES b

        WHERE   a.OL_NAME = b.NAME;

 

 

    TMP1 VARCHAR2(30);

 

BEGIN

    FOR v_ol IN c_ol

    LOOP

        DBMS_OUTPUT.PUT_LINE('Stored Ouline.......: ' || v_ol.OL_NAME);

        DBMS_OUTPUT.PUT_LINE('Category............: ' || v_ol.CATEGORY);

        DBMS_OUTPUT.PUT_LINE('Creator / Owner.....: ' || v_ol.CREATOR || ' / ' || v_ol.OWNER );

        DBMS_OUTPUT.PUT_LINE('hash_value..........: ' || v_ol.HASH_VALUE);

        DBMS_OUTPUT.PUT_LINE('hash_value2.........: ' || v_ol.HASH_VALUE2);

        select decode(v_ol.ENABLED,'ENABLED','Yes','DISABLED','No') into TMP1 from dual;

        DBMS_OUTPUT.PUT_LINE('Enabled.............: ' || TMP1);

        select decode(v_ol.USED   ,'USED'   ,'Yes','UNUSED'  ,'No') into TMP1 from dual;

        DBMS_OUTPUT.PUT_LINE('Used................: ' || TMP1);

        DBMS_OUTPUT.PUT_LINE('Creation date.......: ' || v_ol.TIM);

        DBMS_OUTPUT.PUT_LINE('Hint count..........: ' || v_ol.HINTCOUNT);

        DBMS_OUTPUT.PUT_LINE('Sql Text Length.....: ' || v_ol.TEXTLEN);

        DBMS_OUTPUT.PUT_LINE('______________SQL TEXT EXTRACT________________________________');

        DBMS_OUTPUT.PUT_LINE(substr(v_ol.SQL_TEXT,  1, 150));

        DBMS_OUTPUT.PUT_LINE(substr(v_ol.SQL_TEXT,151, 300));

        DBMS_OUTPUT.PUT_LINE(substr(v_ol.SQL_TEXT,301, 451));

        DBMS_OUTPUT.PUT_LINE(substr(v_ol.SQL_TEXT,601, 751));

        DBMS_OUTPUT.PUT_LINE(substr(v_ol.SQL_TEXT,901,1051));

        DBMS_OUTPUT.PUT_LINE('______________END OF SQL TEXT EXTRACT_________________________');

        DBMS_OUTPUT.PUT_LINE(chr(10));

        DBMS_OUTPUT.PUT_LINE(chr(10));

    END LOOP;

END;

/

 

 

Gestion de flashback

Concept

Oracle depuis la 10g propose la fonctionnalité « Flashback » qui marche comme la corbeille mac (ou sa copie windows)

En cas de DROP accidentel, on peut restaurer l’objet simplement.

Au-delà, Oracle permet de restaurer le contenu d’une table qui n’a pas été supprimée avec le contenu qu’elle avait à un moment dans le temps.

 

Afin de bénéficier de cette nouvelle fonctionnalité au niveau de la récupération d’une table après un drop, il faut que le paramètre d’initialisation recyclebin soit à ON (valeur par défaut). Ce paramètre dynamique peut s’activer ou se désactiver au niveau system comme au niveau session.

 

Exemple d’utilisation

purge table t1;                         supprime définitivement la table t1

drop table t1 purge;                    drop la table t1 sans placer le segment dans la poubelle

purge dba_recyclebin;                   purge l’ensemble des objets de la poubelle     

FLASHBACK TABLE t1 TO BEFORE DROP;       restore la table t1

 

 

Question : quelle taille occupent les objets dans la poubelle ?

Réponse :  la vue DBA_RECYCLEBIN contient un champ space qui indique un nombre de block, mais une base peut utiliser plusieurs taille de block.

                  le mieux est encore d’utiliser le champs bytes dans DBA_SEGMENTS, cela permet également de ventiler la consommation par tablespace.

 

TABLESPACE_NAME                | Recycle Segments (MB)

------------------------------ | ---------------------

S_TAB_SCHEMA2                  |                   4.0

S_TAB_SCHEMA3                  |                    .4

SCHEMA_ADMIN                     |                  76.0

SCHEMA_TABLES                    |                  96.3

                               | ---------------------

sum                            |                 176.7

 

Script

set feedback off

set timing off

set colsep ' | '

 

col "Recycle Segments (MB)" for 999G999D9

 

break on report

compute sum of "Recycle Segments (MB)" on report

 

select tablespace_name,sum(bytes)/1024/1024 as "Recycle Segments (MB)"

from    dba_segments seg,

        dba_recyclebin rec

where seg.segment_name = rec.object_name

group by tablespace_name

/

 

Question : quels sont les objets dans la poubelle ?

 

OWNER                | ORIGINAL_NAME                    | OBJECT_NAME                    |    DROPSCN | DROPTIME            | CREATETIME          |          MB

-------------------- | -------------------------------- | ------------------------------ | ---------- | ------------------- | ------------------- | -----------

SCHEMA              | CSCLPO                           | BIN$dlpoiak2AX/gRAAUT0MbuA==$0 |   50837706 | 2009-10-20:10:11:24 | 2009-10-20:10:11:23 |         .06

SCHEMA              | CSCLPO1                          | BIN$dlpoiak1AX/gRAAUT0MbuA==$0 |   50837703 | 2009-10-20:10:11:24 | 2009-10-20:10:11:23 |         .06

SCHEMA              | INSCPT                           | BIN$dlpedhbtVZPgRAAUT0MbuA==$0 |   50793127 | 2009-10-20:10:08:35 | 2009-10-19:17:09:59 |         .06

SCHEMA              | SVCMON                           | BIN$dlqSPHYkaOTgRAAUT0MbuA==$0 |   51094238 | 2009-10-20:10:23:04 | 2009-10-19:17:20:56 |         .06

SCHEMA              | SVCMON_K1                        | BIN$dlqSPHYjaOTgRAAUT0MbuA==$0 |   51094220 | 2009-10-20:10:23:04 | 2009-10-19:17:20:56 |         .06

SCHEMA              | TMAT_MATCHING                    | BIN$eDB93wyXLYLgRAAUT0MbuA==$0 |   62969955 | 2009-11-12:18:01:17 | 2009-11-12:18:01:10 |         .06

SCHEMA              | TMAT_MATCHING                    | BIN$eDB9ciTZJgDgRAAUT0MbuA==$0 |   62969802 | 2009-11-12:18:01:10 | 2009-11-12:17:58:26 |         .06

SCHEMA              | TMAT_MATCHING_DIC                | BIN$eDB93wyYLYLgRAAUT0MbuA==$0 |   62970036 | 2009-11-12:18:01:17 | 2009-11-12:18:01:13 |         .06

SCHEMA              | TMAT_R_MONEP                     | BIN$eDF6wLlpT8rgRAAUT0MbuA==$0 |   62984194 | 2009-11-12:19:12:00 | 2009-11-12:19:06:19 |         .06

SCHEMA              | TMAT_R_MONEP                     | BIN$eDIV5V3+JnbgRAAUT0MbuA==$0 |   62991541 | 2009-11-12:19:55:22 | 2009-11-12:19:45:24 |         .06

SCHEMA              | TMA_DEUREX5                      | BIN$dmE+ZXaoTUTgRAAUT0MbuA==$0 |   51471008 | 2009-10-20:18:20:42 | 2009-10-19:17:21:23 |         .06

SCHEMA              | TMA_DEUREX5                      | BIN$dmFGSkmHVfjgRAAUT0MbuA==$0 |   51473089 | 2009-10-20:18:22:55 | 2009-10-20:18:20:42 |         .06

SCHEMA              | TMA_DEUREX5                      | BIN$dmFdkDSDMUrgRAAUT0MbuA==$0 |   51473496 | 2009-10-20:18:29:25 | 2009-10-20:18:22:55 |         .06

SCHEMA              | TMA_DEUREX5                      | BIN$dmFywU6EaRbgRAAUT0MbuA==$0 |   51474067 | 2009-10-20:18:35:21 | 2009-10-20:18:29:25 |         .06

SCHEMA              | TMA_DEUREX5                      | BIN$dmGC1Ci0GbPgRAAUT0MbuA==$0 |   51474295 | 2009-10-20:18:39:50 | 2009-10-20:18:35:21 |         .06

SCHEMA              | TMA_DEUREX5                      | BIN$dmGJyH1LKtvgRAAUT0MbuA==$0 |   51475019 | 2009-10-20:18:41:47 | 2009-10-20:18:39:50 |         .06

SCHEMA              | TMA_DEUREX5                      | BIN$dmGOymkLMI3gRAAUT0MbuA==$0 |   51475442 | 2009-10-20:18:43:11 | 2009-10-20:18:41:47 |         .06

SCHEMA              | TMA_DEUREX5                      | BIN$dmHx04/dM+fgRAAUT0MbuA==$0 |   51482280 | 2009-10-20:19:10:52 | 2009-10-20:18:43:11 |         .06

SCHEMA              | TMA_DEUREX5                      | BIN$doL1g7X8NrXgRAAUT0MbuA==$0 |   53328990 | 2009-10-22:10:34:08 | 2009-10-20:19:10:53 |         .06

SCHEMA              | TMA_DEUREX5                      | BIN$dpf/ao4zRGLgRAAUT0MbuA==$0 |   53577432 | 2009-10-23:11:40:09 | 2009-10-23:11:37:27 |         .06

SCHEMA              | TMA_DEUREX5                      | BIN$dpf1vy+TK6zgRAAUT0MbuA==$0 |   53576913 | 2009-10-23:11:37:26 | 2009-10-22:10:34:08 |         .06

SCHEMA              | TMA_DEUREX5                      | BIN$dpnL1Iq/C73gRAAUT0MbuA==$0 |   53592888 | 2009-10-23:13:48:53 | 2009-10-23:11:40:09 |         .06

SCHEMA              | TMA_DEUREX5                      | BIN$dpnN5FF1FcPgRAAUT0MbuA==$0 |   53593468 | 2009-10-23:13:49:28 | 2009-10-23:13:48:53 |         .06

SCHEMA              | TMA_DEUREX5                      | BIN$duiBbymOAzXgRAAUT0MbuA==$0 |   54713006 | 2009-10-27:10:43:08 | 2009-10-23:13:49:28 |         .06

SCHEMA              | TMA_DEUREX5                      | BIN$duiOwQ03JrrgRAAUT0MbuA==$0 |   54714666 | 2009-10-27:10:46:51 | 2009-10-27:10:43:08 |         .06

 

Script

col OBJECT_NAME for A30

col "MB" for 999G999D99

select OWNER,ORIGINAL_NAME,OBJECT_NAME,DROPSCN,DROPTIME,CREATETIME,SPACE*8/1024 "MB"

from dba_recyclebin

order by 1,2,3

/

 

 

Question : : quand il existe plusieurs version de la même table (dans l’exemple TMA_DEUREX5) comment restaurer une version en particulier ?

Réponse : il faut utiliser le nom généré par le système

 

SQL> flashback table SCHEMA."BIN$dmFdkDSDMUrgRAAUT0MbuA==$0" to before drop rename to TMA_DEUREX5_restored;

 

Flashback complete.

 

Utilisaton de RMAN sous ORACLE

Commandes utiles de gestion de RMAN sous ORACLE

RMAN permet de sauvegarder / restaurer une instance ORACLE

 

Connexion à RMAN :

se connecter en tant que oracle (ou utilisateur propriétaire de l'instance)

Lancer la commande 'rman'

Puis 'connect target'

ou ' rman target / nocatalog'

 

Vérification de la configuration RMAN :

show all

 

Emplacement et taille des fichiers de sauvegarde, lancer sous sqlplus

  • show parameter db_recovery_file_dest -- donne l'emplacement
  • show parameter db_recovery_file_dest_size -- taile des fichiers d'archive

 

Vérification de conformité entre les fichiers et de l'instance :

list backup; -- liste tous ce qu'il existe dans le catalogue

crosscheck backup;

crosscheck backup of controlfile;

crosscheck backup of database;

crosscheck archivelog all;

report obsolete; -- affiche les fichiers obsolètes par rapport à la rétention définie

list expired backup;

list expired archivelog ALL;

 

Suppression des fichiers backup, archivelog ou report obsolete :

delete expired obsolet;

Répondre  'YES'

delete expired backup;

delete expired archivelog ALL;

 

Modification de configuration :

configure retention policy to recovery window of 7 days;-modification de la retention des fichiers