Création de requêtes SQL

Version imprimable

Bonjour a tous,

J'ai un souci pour cree une requete SQL (je suis admin SAP, et franchement je suis pas un pro du SQL).

Je suis sur ORACLE 9.2.0.6 Enterprise Edition.

Ce que j'ai reussi a faire:
Je veux connaitre le nombre de table qui ont un champs LONG ou LONG RAW,le nombre de lignes dans ces tables, tous ca groupe par tablespace.
Code:

1 2 select TABLESPACE_NAME,count(table_name),SUM(NUM_ROWS) from DBA_TABLES where table_name IN (select TABLE_NAME from all_tab_columns where DATA_TYPE like 'LONG%') group by TABLESPACE_NAME;
Voici ce que j'obtiens:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 TABLESPACE_NAME NB TABLE NB RECORD --------------- ---------- ---------- PSAPBTABD 206 3230712 PSAPCLU2D 32 PSAPCUSTD 5 PSAPDDICD 9 3619 PSAPDOCU2D 1 0 PSAPEL46C2D 7 240326 PSAPES46CD 16 1203020 PSAPLOAD2D 1 0 PSAPPOOLD 156 122770 PSAPPROTD 9 4 PSAPSOURCE2D 12 2699 PSAPSTABD 59 2335 PSAPUSER2D 1 1 SYSTEM 46
Maintenant, ce que je desirerais obtenir:
Deja, le resultats de ma requete ne me donne pas toujours le nombre de ligne...alors que les tables sont pleines...deja si on peut regler ca

Ensuite, j'amerais avoir les infos suivantes dans ma requete:
Total de la taille en Mo des tables avec un champ LONG ou LONG RAW.
Nb de tables qui ont 0 lignes (ou qui sont NULL)
Nb de tables qui ont + de 0 lignes

A mon niveau , obtenir toute ces infos dans une requetes, c'est mission impossible :D

Merci beaucoup de votre aide

13/06/2008, 15h24
mnitu

Citation:

Envoyé par vorta251

...
Deja, le resultats de ma requete ne me donne pas toujours le nombre de ligne...alors que les tables sont pleines...deja si on peut regler ca
...

Num_Rows est null si les statistiques ne sont pas calculé.
17/06/2008, 10h22
vorta251
C'est exact.

J'ai creuse un peu le sujet, au depart j'etais partis pour utiliser une commande de mise a jour des stats :
Code:

1 2 exec DBMS_STATS.GATHER_SCHEMA_STATS( OWNNAME => 'SAPR3', BLOCK_SAMPLE => FALSE, DEGREE => 2, GRANULARITY => 'ALL' ,CASCADE => TRUE );
Mais comme ma base heberge SAP, j'ai lu que ce n'etait pas conseiller d'utiliser autre chose que les outils SAP.

Resultat des courses les stats sont incompletes de facon delibere...

Je m'oriente donc vers une autre solution.
Dans ma requetes je vais faire un count sur les tables qui ont un champ LONG/LRAW.

Je vois pas trop comment articuler ca dans ma requete, donc si quelqu'un a une idee je suis preneurs!!

Merci de votre aide
17/06/2008, 10h28
mnitu

Il te faut employer du SQL dynamique. Je pense que tu devrait créer un script SQL ou une procédure pipelined, qui calcule via plusieurs requête SQL ce que tu a besoin.

Merci de tes indications mnitu!

Bon, la je suis en galere :-D

J'ai cree une vue d'apres dba_segments pour voir chaque table avec son tablespace et sa taille en Mo.
Code:

1 2 3 4 5 create view tab_long (TABLESPACE_NAME,TABLE_NAME,TAILLE_MO) as select TABLESPACE_NAME,SEGMENT_NAME,lpad(substr(round(bytes/ (1024*1024),1),1,10),10) as Mo from dba_segments where SEGMENT_TYPE = 'TABLE';
Mon idée c'etait de faire une boucle dans ma vue (comme tu me l'as suggere).

ca ferait qqch comme ca (la on est en mode syntax unix/sql/n'importe quoi):

for TABLE in (select TABLE_NAME from tab_long)
do
if [ select TABLE_NAME from all_tab_columns where TABLE_NAME = $TABLE and DATA_TYPE like 'LONG%' ]
then
echo $TABLE
select count(*) from $TABLE;
fi
done

Est ce que tu peux m'orienter (j'ai aucune connaissance PL/SQL...)?

Merci!

Je tiens le bon bout!!

J'ai pillé sans vergogne un script trouvé ici : http://www.developpez.net/forums/sho...d.php?t=562178

J'ai bricolé pour inclure mes requetes SQL

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
DECLARE
        v_enreg_1 VARCHAR2(50);
        v_enreg_2 VARCHAR2(50);
        v_rqy VARCHAR2(250);
        v_nb_ligne Number;
BEGIN
 
  FOR v_enreg_1 IN (SELECT SEGMENT_NAME FROM dba_segments WHERE SEGMENT_TYPE = 'TABLE')
  LOOP
     FOR v_enreg_2 IN (SELECT TABLE_NAME FROM all_tab_columns WHERE table_name = v_enreg_1.segment_name  AND data_type like 'LONG%')
     LOOP
           v_rqy := 'SELECT count(*) FROM '||v_enreg_1.segment_name||' '; 
           execute immediate v_rqy INTO v_nb_ligne;
           dbms_output.put_line(v_rqy);
           IF v_nb_ligne > 0 THEN
                dbms_output.put_line( v_enreg_1.SEGMENT_name || ' --- nombre de ligne  : ' || v_nb_ligne );
           END IF;           
     END LOOP;
  END LOOP;
END;

Bon, maintenant ca fait des erreurs oracle...

Code:

1
2
3
4
5
6
 
DECLARE
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at line 13

hmm, a priori je pense que v_nb_ligne n'est pas bien defini.... mais c'est pas tres clair...

Code:

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
36
37
 
CREATE OR REPLACE Function TabCount(
  p_owner       In Varchar2,
  p_tabname     In Varchar2
) Return Number 
Authid current_user
Is
  l_Cnt   Number;
Begin
  Execute Immediate 'Select count(*) From '||p_owner||'.'||p_tabname Into l_Cnt;
  Return l_Cnt;
End;
/
Select tablespace_name, count(*), Sum(rowCnt), Sum(Bytes), Count(RowCnt)
From (
    Select t.owner, t.table_name, t.tablespace_name, s.bytes,
           case t.rowCnt 
             When 0 Then Null 
             Else t.rowCnt 
           end RowCnt
    From 
        (SELECT owner, table_name, tablespace_name, TabCount(owner, table_name) RowCnt
          FROM DBA_TABLES 
        WHERE (owner, table_name) IN (SELECT owner, TABLE_NAME 
                                         FROM all_tab_columns 
                                        WHERE DATA_TYPE LIKE 'LONG%'
                                      )  
        ) t,
        (SELECT owner, segment_name, Sum(bytes) Bytes
                  FROM dba_segments 
                 Where SEGMENT_TYPE = 'TABLE'
                 Group by owner, segment_name
        ) s                                
    Where t.owner = s.owner
      And t.table_name = s.segment_name)
Group By tablespace_name
/

Merci beaucoup pour le temps que tu passe a m'aider!!!

J'ai donc executer la fonction...et j'ai encore des problemes (decidement, j'ai pas de chance!)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SQL> CREATE OR REPLACE FUNCTION TabCount(
  2    p_owner       IN Varchar2,
  3    p_tabname     IN Varchar2
  4  ) RETURN Number 
  5  Authid current_user
  6  IS
  7    l_Cnt   Number;
  8  Begin
  9    Execute Immediate 'Select count(*) From '||p_owner||'.'||p_tabname INTO l_Cnt;
 10    RETURN l_Cnt;
 11  End;
 12  /

Apres la creation de la fonction j'ai passé cette requete:

Code:

SELECT owner, table_name, tablespace_name, TabCount(owner, table_name) from DBA_TABLES ;

Ca me donne quelque resultats:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
OWNER TABLE_NAME    TABLESPACE_NAME TABCOUNT(OWNER,TABLE_NAME)
----- ------------- --------------- --------------------------
SYS   OBJAUTH$      SYSTEM                                2884
SYS   SYSAUTH$      SYSTEM                                 371
SYS   OBJPRIV$      SYSTEM                                   0
SYS   DEFROLE$      SYSTEM                                   0
SYS   PROFILE$      SYSTEM                                  34
SYS   PROFNAME$     SYSTEM                                   2
SYS   DEPENDENCY$   SYSTEM                               21110
SYS   ACCESS$       SYSTEM                               19260
SYS   AUD$          SYSTEM                                   0
SYS   LINK$         SYSTEM                                   0
SYS   TRUSTED_LIST$ SYSTEM                                   1

Mais ca fait l'erreur suivante:

Code:

1
2
3
4
 
ERROR:
ORA-00911: invalid character
ORA-06512: at "SAPR3.TABCOUNT", line 9

J'arrive pas a pige comment il se retrouve a lister la fonction tabcount...

Il faut trouver un moyen de lui dire de ne pas traiter tabcount lors de son execution...enfin un truc dans le genre :-)

18/06/2008, 13h08
McM

C'est juste que la fonction Tabcount plante à cause d'un nom passé en paramètre qui n'est pas correct.

Essayes de remplacer la ligne 9 par

Code:

Execute Immediate 'Select count(*) From '||p_owner||'."'||p_tabname ||'"' INTO l_Cnt;
18/06/2008, 14h59
vorta251

C'etait ca!

Merci de votre aide , ca va vraiment me simplifier la vie :-)