FAQ OracleConsultez toutes les FAQ

Trois vues permettent d'obtenir des informations sur les tables de votre base :

• DBA_TABLES
• ALL_TABLES
• USER_TABLES

DBA_TABLES liste toutes les tables de tous les schémas
ALL_TABLES liste toutes les tables que l'utilisateur peut voir (les tables de son schéma plus celles des autres schémas dans lesquels il possède des droits)
USER_TABLES liste les tables du schéma de l'utilisateur connecté

Les colonnes de la vue DBA_TABLES sont les suivantes :

OWNER Schéma propriétaire de la table
TABLE_NAME Nom de la table
TABLESPACE_NAME Nom du tablespace de stockage de la table
CLUSTER_NAME Nom du cluster qui contient la table
IOT_NAME Nom de l'IOT à laquele se réfère la zone de débordement ou la table de correspondances
PCT_FREE Pourcentage minimum d'espace libre pour un bloc
PCT_USED Pourcentage minimum d'espace utilisé pour un bloc
INI_TRANS Nombre initial de transactions
MAX_TRANS Nombre maximum de transactions autorisées
INITIAL_EXTENT Taille du segment initial en octets
NEXT_EXTENT Taille du deuxième extent en octets
MIN_EXTENTS Nombre minimum d'extents alloués dans le segment
MAX_EXTENTS Nombre maximum d'extents alloués dans le segment
PCT_INCREASE Pourcentage d'augmentation des nouveaux extents créés
FREELISTS Nombre de freelists allouées pour le segment
FREELIST_GROUPS Nombre de groupes de freelists allouées pour le segment
LOGGING Attribut de logging
BACKED_UP Indique si la table a été sauvegardée depuis la dernière modification
NUM_ROWS Nombre de lignes de la table (*)
BLOCKS Nombre de blocs utilisés pour la table
EMPTY_BLOCKS Nombre de blocs vide (jamais utilisés) pour la table
AVG_SPACE Valeur moyenne de l'espace libre pour la table (en nombre de lignes) (*)
CHAIN_CNT Nombre de lignes chaînées pour la table (*)
AVG_ROW_LEN Longueur moyenne d'une ligne (incluant l'entête) (*)
AVG_SPACE_FREELIST_BLOCKS Valeur moyenne de l'espace libre de tous les blocs dans une freelist (*)
NUM_FREELIST_BLOCKS Nombre de blocs dans la freelist
DEGREE Nombre de processus par instance
INSTANCES Nombre d'instances à travers lesquelles la table est scannée
CACHE Indique si la table est chargée en cache
TABLE_LOCK Indique si le verouillage de table est activé ou désactivé
SAMPLE_SIZE Valeur de l'échantillon utilisé pour analyser la table
LAST_ANALYZED Date de la dernière analyse de la table
PARTITIONED Indique si la table est partitionnée
IOT_TYPE Indique le type d'objet d'une table organisée en index (Préfixe, zone de débordement ou table de correspondance (MAPPING TABLE)
TEMPORARY Indique si la session courante peut voir les données insérées
SECONDARY Indique si la table fait partie de la création d'un index de domaine
NESTED Indique s'il s'agit d'une table imbriquée
BUFFER_POOL Buffer pool par défaut de chargement des blocs
ROW_MOVEMENT Indique si le déplacement des lignes partitionnées est activé ou désactivé
GLOBAL_STATS Indique si les statistiques ont été calculées sans fusion des partitions sous-jacentes
USER_STATS Indique si les statistiques ont été saisies directement par l'utilisateur
DURATION Persistance des données d'une table temporaire (sys$session oo sys$transaction)
SKIP_CORRUPT Indique si le contournement des blocs endommagés est activé ou désactivé
MONITORING Indique si le traçage est activé
CLUSTER_OWNER Propriétaire du cluster
DEPENDENCIES Indique si le traçage des dépendances de niveau ligne est activé
COMPRESSION Indique si la compression de données est activée ou désactivée

(*) ne prenez par comme argent comptant la valeur de ces colonnes, particulièrement NUM_ROWS.
En effet ces colonnes ne sont (re)valorisées qu'après analyse de la table.
Utilisez donc ces valeurs à bon escient.
Si votre système analyse vos tables chaque nuit, par exemple, ces valeurs peuvent sans crainte vous donner une bonne indication de la volumétrie de vos tables, sans exécuter la gourmande instruction : SELECT COUNT(*).

SQL> SELECT 
  2     OWNER, 
  3     TABLE_NAME, 
  4     TABLESPACE_NAME, 
  5     NUM_ROWS, 
  6     BLOCKS, 
  7     EMPTY_BLOCKS, 
  8     LAST_ANALYZED 
  9  FROM  DBA_TABLES 
 10  WHERE OWNER = 'FD' ; 

OWNER      TABLE_NAME           TABLESPACE_NAME   NUM_ROWS     BLOCKS EMPTY_BLOCKS LAST_ANA 
---------- -------------------- --------------- ---------- ---------- ------------ -------- 
FD         CONTACT              USERS                    2          5            0 03/12/02 
FD         DEPT                 USERS 
FD         EMP                  USERS 
FD         EMPLOYE              USERS 
FD         FACTURE              USERS 
FD         LOV_BLOCK            USERS 
FD         LOV_COLONNE          USERS 
FD         LOV_ELEMENT_COLONNE  USERS 
FD         LOV_ITEM             USERS 
FD         LOV_LOV              USERS 
FD         LOV_MODULE           USERS 
FD         NOMBRES              USERS 
FD         TEST                 USERS                    0          1            0 03/12/02 
FD         TESTX                USERS 
FD         TEST_LOV             USERS                52000        432           80 08/07/04 
FD         TEST_TYPES           USERS 
FD         UTIL_PREFS           USERS 
FD         UTIL_PREFS_ORDER     USERS 
FD         UTIL_PREFS_RECORD_OR USERS 
           DER 

19 ligne(s) sélectionnée(s).

Après analyse de la table DEPT

SQL> / 

OWNER      TABLE_NAME           TABLESPACE_NAME   NUM_ROWS     BLOCKS EMPTY_BLOCKS LAST_ANA 
---------- -------------------- --------------- ---------- ---------- ------------ -------- 
FD         CONTACT              USERS                    2          5            3 14/10/04 
FD         DEPT                 USERS                    4          4            4 14/10/04 

Certaines tables doivent absolument être mises à l'abri des modifications, par exemple une table de paramétrage ou un historique en ligne. Même si le seul privilège donné sur ces tables est le SELECT, il y a toujours un risque que le propriétaire de la table, ou un utilisateur disposant d'un privilège global de type ANY TABLE, modifie par erreur ces tables.
Comment se prémunir contre de telles erreurs ?

Il suffit de mettre à profit un mode quelque peu méconnu des contraintes : DISABLE VALIDATE.
En effet, ce mode interdit sur la table concernée l'exécution de toute instruction INSERT, UPDATE, DELETE ou même TRUNCATE, quels que soient les privilèges dont on dispose.
La nature et la pertinence de la contrainte importent peu, seuls comptent les mots magiques DISABLE VALIDATE.
Cependant, une contrainte neutre est préférable, pour le cas où elle serait activée, c'est pourquoi je propose un CHECK(1=1).

ALTER TABLE emp ADD CONSTRAINT lect_seule CHECK (1=1) DISABLE VALIDATE;
UPDATE emp SET ename=ename;

--> ORA-25128: Aucune insertion/mise à jour/suppression possible sur une table avec contrainte (SCOTT.LECT_SEULE) désactivée et validée

Notre table est donc protégée contre toute modification accidentelle de son contenu.

Tout d'abord, il faut au préalable :

• exécuter le script $ORACLE_HOME/rdbms/admin/utlchain,
• analyser la table en question avec la commande suivante :

Analyze Table MaTable;

La requête suivante vous permet maintenant de lister les lignes chaînées :

SELECT table_name, 
       owner, 
       num_rows,
       chain_cnt, 
      (chain_cnt * 100) / num_rows AS ratio, 
       pct_used, 
       pct_free 
  FROM Dba_Tables 
 WHERE Owner NOT IN ('SYS','SYSTEM')
   AND table_name = 'MaTable'
   AND Chain_Cnt !=0 
 ORDER BY 5 desc;

Attention, cela ne fonctionne pas avec le package DBMS_STATS.

Une table est un ensemble "physique" de données qui occupe un espace disque.
Une vue est une interrogation logique (SELECT) basée sur des tables ou d'autres vues qui n'occupent pas de place sur le disque. Une vue n'est en fait qu'un "SELECT" mémorisé en base (l'instruction est mémorisée, pas les résultats).

C'est la raison qui justifie l'impossibilité de créer un index, de n'importe quel type, sur ce type de segment. Afin d'optimiser le temps de réponse d'une requête se basant sur un select, l'index est donc à créer sur la table sur laquelle porte la requête.

Concernant l'écriture des requêtes, les vues et les tables s'utilisent de la même manière

Les vues sont toutes stockées dans le tablespace SYSTEM, alors que les tables sont stockés dans les tablespaces dédiés.
Les tables sont visibles via les tables systèmes : DBA_TABLES, ALL_TABLES et USER_TABLES alors que les vues sont visibles via les DBA_VIEWS, ALL_VIEWS et USER_VIEWS.

Grâce à la requête suivante :

SELECT utc.table_name, 
       count (distinct utc.column_name) nb_col, 
       count (distinct index_name) nb_index,
       count (distinct uic.column_name) / count (distinct utc.column_name) prct_index
  FROM user_tab_columns utc, user_ind_columns uic
 WHERE utc.table_name = uic.table_name
   AND utc.table_name= 'PS_JOB'
 GROUP BY utc.table_name;

il suffit de regarder la table USER_CONSTRAINTS, en particulier la colonne CONSTRAINT_TYPE de cette table, qui est codée de la manière suivante :

• C : contrainte de type CHECK (genre champ NOT NULL ou avec une condition de vérification genre BETWEEN)
• P : contrainte de clé primaire (PK)
• U : contrainte d'unicité (Unique)
• R : contrainte référentielle (FK)

SQL> SELECT constraint_name,
  1         decode(constraint_type, 'C', 'check', 'P', 'clé primaire ', 'U', 'Contrainte d''unicité', 'R', 'Contrainte Référentielle ') constraint_name 
  2         table_name, search_condition, status
  3    FROM USER_CONSTRAINTS;

CONSTRAINT_NAME                CONSTRAINT_NAME           TABLE_NAME                     SEARCH_CONDITION                        
------------------------------ ------------------------- ------------------------------ ------------
PK_LIST                        clé primaire              USERS_ERP                                                              
PK_SEQUENCE                    clé primaire              DD_JOURNAL                                                             
PK_PS_PROJECT_LIST             clé primaire              PS_PROJECT_LIST                                                        
SYS_C00774                     check                     PSACCESSPRFL                   "ACCESSID" IS NOT NULL