FAQ OracleConsultez toutes les FAQ

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
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).

1
2
3
4
5
6
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

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

Analyze Table MaTable;

1
2
3
4
5
6
7
8
9
10
11
12
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;

1
2
3
4
5
6
7
8
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;

1
2
3
4
5
6
7
8
9
10
11
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