Tri Oracle Sql

Bonjour,

J'aimerais arriver à ce résultat là :

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

UTILISATEUR TYPE D'OBJET TOTAL ----------- --------------- ------ SYS LOB 28 TYPE 482 VIEW 2033 INDEX 308 QUEUE 4 TABLE 327 CLUSTER 10 CONTEXT 1 LIBRARY 62 PACKAGE 314 SYNONYM 6 TRIGGER 4 FUNCTION 37 OPERATOR 1 SEQUENCE 40 PROCEDURE 24 TYPE BODY 23 PACKAGE BODY 306 RESOURCE PLAN 3 CONSUMER GROUP 4 EVALUATION CONT 1 EXT   *********** ------ sum 4018   OUTLN INDEX 3 TABLE 3 PROCEDURE 1 *********** ------ sum 7   ...

---

Le problème se trouve à la ligne "sum", je n'arrive pas à l'intégrer. J'ai déjà ça :

Code:

---

1 2 3 4

SELECT owner AS "UTILISATEUR", object_type AS "TYPE D'OBJET", COUNT(*) AS "TOTAL" FROM sys.dba_objects GROUP BY owner, object_type ORDER BY owner, object_type;

---

Code:

---

1 2 3 4

SELECT owner AS "UTILISATEUR", COUNT(*) AS "TOTAL" FROM sys.dba_objects GROUP BY owner ORDER BY owner;

---

Une idée ?!

Sous SQL*Plus :

Code:

---

1 2	BREAK ON utilisateur SKIP 1 COMPUTE SUM OF total ON utilisateur

---

http://68.142.116.70/docs/cd/B19306_...9.htm#SQPUG030
http://68.142.116.70/docs/cd/B19306_...4.htm#SQPUG035

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 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82

SQL> SELECT owner AS "UTILISATEUR", object_type AS "TYPE D'OBJET", COUNT(*) AS "TOTAL" 2 FROM sys.dba_objects 3 WHERE owner LIKE 'SYS%' 4 GROUP BY owner, object_type 5 ORDER BY owner, object_type;   UTILISATEUR TYPE D'OBJET TOTAL ------------------------------ ------------------- ---------- SYS CLUSTER 10 CONSUMER GROUP 5 CONTEXT 4 DIRECTORY 1 EVALUATION CONTEXT 10 FUNCTION 57 INDEX 698 INDEX PARTITION 181 JOB 4 JOB CLASS 2 LIBRARY 112 LOB 93 LOB PARTITION 1 OPERATOR 15 PACKAGE 475 PACKAGE BODY 460 PROCEDURE 38 PROGRAM 3 QUEUE 15 RESOURCE PLAN 3 RULE 4 RULE SET 11 SCHEDULE 1 SEQUENCE 71 SYNONYM 6 TABLE 687 TABLE PARTITION 171 TRIGGER 5 TYPE 933 TYPE BODY 81 UNDEFINED 6 VIEW 2950 WINDOW 2 WINDOW GROUP 1 ****************************** ---------- sum 7116   SYSMAN EVALUATION CONTEXT 1 FUNCTION 8 INDEX 396 LOB 28 PACKAGE 72 PACKAGE BODY 71 PROCEDURE 2 QUEUE 2 RULE SET 2 SEQUENCE 5 TABLE 341 TRIGGER 48 TYPE 217 TYPE BODY 7 VIEW 136 ****************************** ---------- sum 1336   SYSTEM INDEX 179 INDEX PARTITION 32 LOB 23 PACKAGE 1 PACKAGE BODY 1 PROCEDURE 1 QUEUE 4 SEQUENCE 20 SYNONYM 8 TABLE 138 TABLE PARTITION 27 TRIGGER 2 TYPE 1 VIEW 12 ****************************** ---------- sum 449     63 rows selected.

---

Si votre version d'Oracle le permet :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

SELECT case when grouping(object_type) = 1 then 'sum' when owner = lag(owner, 1, 0) over (order by owner, object_type) then null else owner end AS "UTILISATEUR", object_type AS "TYPE D'OBJET", COUNT(*) AS "TOTAL" FROM sys.dba_objects WHERE owner LIKE 'SYS%' GROUP BY owner, rollup(object_type) ORDER BY owner asc, object_type asc;

---