Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

  1. #1
    Membre du Club
    Inscrit en
    mai 2006
    Messages
    104
    Détails du profil
    Informations forums :
    Inscription : mai 2006
    Messages : 104
    Points : 57
    Points
    57
    Par défaut Récupérer le texte de création d'un index contenant une fonction
    Bonjour,

    J'aimerais savoir s'il est possible de récupérer dans Oracle (11gR2) le texte de création d'un index contenant une fonction.

    Depuis SQL Developer, je peux récupérer ce texte dans l'onglet "Index" de ma table :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
     
    INDEX_OWNER  INDEX_NAME  UNIQUENESS STATUS INDEX_TYPE             TEMPORARY  PARTITIONED  FUNCIDX_STATUS  JOIN_INDEX  COLUMNS                     COLUMN_EXPRESSION
    NOM_PROP      IDX_MONIDX  NONUNIQUE  VALID  FUNCTION-BASED NORMAL  N         NO             ENABLED        NO          SYS_NC00057$,SYS_NC00058$   TO_CHAR("DATE_SORTIE",'yyyy')
    NOM_PROP      IDX_MONIDX  NONUNIQUE  VALID  FUNCTION-BASED NORMAL  N         NO             ENABLED        NO          SYS_NC00057$,SYS_NC00058$   TO_CHAR("DATE_SORTIE",'mm')

    Depuis SQL Developer, voici le descriptif de mon index (onglet "Colonnes" lorsque je choisis l'index)
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
     
    INDEX_OWNER INDEX_NAME  TABLE_OWNER  TABLE_NAME  COLUMN_NAME  COLUMN_POSITION DESCEND
    NOM_PROP     NOM_INDEX  NOM_PROP     NOM_TABLE   SYS_NC00057$  1               ASC
    NOM_PROP     NOM_INDEX  NOM_PROP     NOM_TABLE   SYS_NC00058$  2               ASC
    Depuis SQL Developer, voici le texte que je récupère depuis l'onglet "SQL"
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
     
    CREATE INDEX NOM_PROP.NOM_INDEX ON NOM_PROP.NOM_TABLE (TO_CHAR("DATE_SORTIE",'yyyy'), TO_CHAR("DATE_SORTIE",'mm'));
    Ma question est : comment récupérer dans les métadonnées Oracle le texte complet de création de l'index (avec la "traduction" entre les colonnes "SYS_NC00057$" et TO_CHAR("DATE_SORTIE",'yyyy') et "SYS_NC00058$" et TO_CHAR("DATE_SORTIE",'mm').

    Merci beaucoup pour les solutions que vous pourriez me proposer.

    MarieO

  2. #2
    Expert éminent
    Avatar de pachot
    Homme Profil pro
    DBA au CERN (Genève), Oracle ACE Director, OCM 12c, Oak Table
    Inscrit en
    novembre 2007
    Messages
    1 744
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 48
    Localisation : Suisse

    Informations professionnelles :
    Activité : DBA au CERN (Genève), Oracle ACE Director, OCM 12c, Oak Table
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : novembre 2007
    Messages : 1 744
    Points : 6 152
    Points
    6 152
    Par défaut
    Dans quel but? SYS_NC00057$ est un nom généré. On ne doit pas le mettre dans le CREATE. Imaginez que ce no existe déjà par exemple.
    Franck Pachot - DBA au CERN - Oracle ACE Director - OCM 12c - Oak Table member - twitter: @FranckPachot - blog: blog.pachot.net

  3. #3
    Membre régulier
    Homme Profil pro
    Développeur Oracle
    Inscrit en
    décembre 2019
    Messages
    40
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Nord (Nord Pas de Calais)

    Informations professionnelles :
    Activité : Développeur Oracle

    Informations forums :
    Inscription : décembre 2019
    Messages : 40
    Points : 99
    Points
    99
    Par défaut
    Bonjour,

    Tu peux retrouver le texte des FBI (Function-Based-Indexes) dans la vue ALL_IND_EXPRESSIONS:

    https://docs.oracle.com/cd/E18283_01...views_1101.htm

    Sinon pour reconstruire l'instruction DDL d'un index tu peux utiliser la fonction DBMS_METADATA.GET_DDL:

    https://docs.oracle.com/cd/E11882_01...htm#ARPLS66885

  4. #4
    Membre du Club
    Inscrit en
    mai 2006
    Messages
    104
    Détails du profil
    Informations forums :
    Inscription : mai 2006
    Messages : 104
    Points : 57
    Points
    57
    Par défaut
    Bonjour,

    Je viens de tester la requête suivante sur ma base de données:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
     
    SELECT table_owner, index_owner, table_name, index_name, 
         listagg(column_expression,',') within group 
    	   (order by table_owner, table_name, index_name, column_position) as list_columns
       FROM all_ind_expressions where index_owner='NOM_PROP';
    et j'ai le message d'erreur ci-dessous :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
     
    ORA-00932: types de données incohérents ; attendu : NUMBER ; obtenu : LONG
    00932. 00000 -  "inconsistent datatypes: expected %s got %s"
    *Cause:    
    *Action:
    Erreur à la ligne 2, colonne 14

    A quoi cela est-il dû et comment faire pour corriger cette anomalie ?

    Merci beaucoup.

  5. #5
    Membre régulier
    Homme Profil pro
    Développeur Oracle
    Inscrit en
    décembre 2019
    Messages
    40
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Nord (Nord Pas de Calais)

    Informations professionnelles :
    Activité : Développeur Oracle

    Informations forums :
    Inscription : décembre 2019
    Messages : 40
    Points : 99
    Points
    99
    Par défaut
    Bonjour,

    Tu obtiens l'erreur car tu appliques une fonction sur une colonne de type LONG. Ce type de données n'est pas du tout pratique à manipuler, et pourtant Oracle continue de l'utiliser dans son dictionnaire de données!
    Il faut que tu passes par du pl/sql. Par exemple:
    https://blog.mclaughlinsoftware.com/...t-long-to-clob

    De plus, ta requête ne fonctionnera pas car il manquera un GROUP BY.

  6. #6
    Membre du Club
    Inscrit en
    mai 2006
    Messages
    104
    Détails du profil
    Informations forums :
    Inscription : mai 2006
    Messages : 104
    Points : 57
    Points
    57
    Par défaut
    Effectivement, il manque la ligne GROUP BY à ma requête SQL

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
     
    SELECT table_owner, index_owner, table_name, index_name, 
         listagg(column_expression,',') within group 
    	   (order by table_owner, table_name, index_name, column_position) as list_columns
       FROM all_ind_expressions where index_owner='NOM_PROP'
    GROUP BY table_owner, index_owner, table_name, index_name
    ORDER BY table_name, index_name;

  7. #7
    Membre du Club
    Inscrit en
    mai 2006
    Messages
    104
    Détails du profil
    Informations forums :
    Inscription : mai 2006
    Messages : 104
    Points : 57
    Points
    57
    Par défaut
    J'ai fini par trouver comment faire, en suivant les idées de vanagreg

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
     
    SELECT owner, table_name, index_name, index_type, table_owner, table_type,
         trim(replace(replace(replace(
    	        substr(DBMS_METADATA.GET_DDL('INDEX',index_name,'NOM_PROP'),1,
                       instr(DBMS_METADATA.GET_DDL('INDEX',index_name,'NOM_PROP'),'PCTFREE')-1),
              chr(10)),chr(13)),'"'))) as index_text
       FROM all_indexes 
       WHERE owner='NOM_PROP'
        AND Not Exists(SELECT 1 FROM all_constraints WHERE all_indexes.owner=all_constraints.owner 
    	                  AND all_indexes.table_name=all_constraints.table_name
                          AND all_indexes.index_name=all_constraints.index_name)
       ORDER BY table_owner, table_name, index_name;
    Un grand merci à tous pour vos idées et votre aide...

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Ecriture jointure réflexive compatible avec la création d'un index sur une vue
    Par Christophe Charron dans le forum Développement
    Réponses: 8
    Dernier message: 28/11/2018, 10h09
  2. Erreur lors de la création d'un index sur une vue
    Par Christophe Charron dans le forum Développement
    Réponses: 2
    Dernier message: 02/09/2018, 12h30
  3. Réponses: 7
    Dernier message: 02/12/2011, 11h49
  4. Création des bons index pour une requête
    Par genova dans le forum Requêtes
    Réponses: 2
    Dernier message: 21/12/2008, 15h47
  5. Création d'un index sur une grosse table
    Par Jester dans le forum SQL Procédural
    Réponses: 5
    Dernier message: 02/04/2008, 13h44

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo