Probleme SELECT sur vue DBA_TABLESPACES et DBA_SEGMENTS

Salut,

Sous Oracle Applications (base 9.2.0.6), je viens d'effectuer une migration OFA->OATM. Cette migration consiste a normaliser les tablespaces ... bref !

Je voudrai supprimer tout les TBS vide sde ma base. Pour ceci, j'adopte la logique suivante :
Je fais une soustraction entre tout les TBS de ma base et tout les TBS qui contiennent des segments. A la fin j'ai la liste de tout mes TBS vides.

Pour ceci :

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

SQL>   select tablespace_name from dba_tablespaces minus select distinct tablespace_name from dba_segments;   TABLESPACE_NAME ------------------------------ AMWD AMWX ASND ASNX CTXD FPAD FPAX FUND FUNX GCSD GCSX   TABLESPACE_NAME ------------------------------ IAD IAX INTERIM IZUD IZUX LNSD LNSX MSTD MSTX OWAPUB TEMP   TABLESPACE_NAME ------------------------------ TEMPORARY_DATA USER_DATA XLED XLEX ZPBD ZPBX ZXD ZXX   30 ligne(s) sÚlectionnÚe(s).

---

Ensuite j'ai voulu faire un truc du genre :

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

SQL>   select 'DROP TABLESPACE ' || tablespace_name || ' INCLUDING CONTENTS AND DATAFILES' from dba_tablespaces minus select distinct tablespace_name from dba_segments;   'DROPTABLESPACE'||TABLESPACE_NAME||'INCLUDINGCONTENTSANDDATAFILES' ------------------------------------------------------------------------------- DROP TABLESPACE AMWD INCLUDING CONTENTS AND DATAFILES DROP TABLESPACE AMWX INCLUDING CONTENTS AND DATAFILES DROP TABLESPACE APPS_TS_ARCHIVE INCLUDING CONTENTS AND DATAFILES DROP TABLESPACE APPS_TS_INTERFACE INCLUDING CONTENTS AND DATAFILES DROP TABLESPACE APPS_TS_MEDIA INCLUDING CONTENTS AND DATAFILES DROP TABLESPACE APPS_TS_NOLOGGING INCLUDING CONTENTS AND DATAFILES DROP TABLESPACE APPS_TS_QUEUES INCLUDING CONTENTS AND DATAFILES DROP TABLESPACE APPS_TS_SEED INCLUDING CONTENTS AND DATAFILES DROP TABLESPACE APPS_TS_SUMMARY INCLUDING CONTENTS AND DATAFILES DROP TABLESPACE APPS_TS_TX_DATA INCLUDING CONTENTS AND DATAFILES DROP TABLESPACE APPS_TS_TX_IDX INCLUDING CONTENTS AND DATAFILES   'DROPTABLESPACE'||TABLESPACE_NAME||'INCLUDINGCONTENTSANDDATAFILES' ------------------------------------------------------------------------------- DROP TABLESPACE APPS_UNDOTS1 INCLUDING CONTENTS AND DATAFILES DROP TABLESPACE ASND INCLUDING CONTENTS AND DATAFILES DROP TABLESPACE ASNX INCLUDING CONTENTS AND DATAFILES DROP TABLESPACE CTXD INCLUDING CONTENTS AND DATAFILES DROP TABLESPACE FPAD INCLUDING CONTENTS AND DATAFILES DROP TABLESPACE FPAX INCLUDING CONTENTS AND DATAFILES DROP TABLESPACE FUND INCLUDING CONTENTS AND DATAFILES DROP TABLESPACE FUNX INCLUDING CONTENTS AND DATAFILES DROP TABLESPACE GCSD INCLUDING CONTENTS AND DATAFILES DROP TABLESPACE GCSX INCLUDING CONTENTS AND DATAFILES DROP TABLESPACE IAD INCLUDING CONTENTS AND DATAFILES   'DROPTABLESPACE'||TABLESPACE_NAME||'INCLUDINGCONTENTSANDDATAFILES' ------------------------------------------------------------------------------- DROP TABLESPACE IAX INCLUDING CONTENTS AND DATAFILES DROP TABLESPACE INTERIM INCLUDING CONTENTS AND DATAFILES DROP TABLESPACE IZUD INCLUDING CONTENTS AND DATAFILES DROP TABLESPACE IZUX INCLUDING CONTENTS AND DATAFILES DROP TABLESPACE LNSD INCLUDING CONTENTS AND DATAFILES DROP TABLESPACE LNSX INCLUDING CONTENTS AND DATAFILES DROP TABLESPACE MSTD INCLUDING CONTENTS AND DATAFILES DROP TABLESPACE MSTX INCLUDING CONTENTS AND DATAFILES DROP TABLESPACE OLAP INCLUDING CONTENTS AND DATAFILES DROP TABLESPACE OWAPUB INCLUDING CONTENTS AND DATAFILES DROP TABLESPACE PORTAL INCLUDING CONTENTS AND DATAFILES   'DROPTABLESPACE'||TABLESPACE_NAME||'INCLUDINGCONTENTSANDDATAFILES' ------------------------------------------------------------------------------- DROP TABLESPACE SYSTEM INCLUDING CONTENTS AND DATAFILES DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES DROP TABLESPACE TEMPORARY_DATA INCLUDING CONTENTS AND DATAFILES DROP TABLESPACE USER_DATA INCLUDING CONTENTS AND DATAFILES DROP TABLESPACE XLED INCLUDING CONTENTS AND DATAFILES DROP TABLESPACE XLEX INCLUDING CONTENTS AND DATAFILES DROP TABLESPACE ZPBD INCLUDING CONTENTS AND DATAFILES DROP TABLESPACE ZPBX INCLUDING CONTENTS AND DATAFILES DROP TABLESPACE ZXD INCLUDING CONTENTS AND DATAFILES DROP TABLESPACE ZXX INCLUDING CONTENTS AND DATAFILES   43 ligne(s) sÚlectionnÚe(s).

---

Voila, la difference entre les 2 requetes est la concaténation de champs texte. Je ne saisi pas trop pourquoi le resultat est different ...

Une explication ?

Tout simplement parce que le minus compare le nom du tablespace renvoyé dans ton second select, avec le nom du tablespace concaténé dans le premier select. Et forcément, le nom concaténé ne se retrouve pas dans le second select...

Je pense qu'il y a tous les tablespaces dans la seconde requête.

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

SQL> select * from a;   C1 C2 ---------- -------------------- 3501 TOTO 3502 TATA 3503 TITI 3504 TUTU 3505 TETE   SQL> select c2 from a 2 minus select distinct c2 from a;   aucune ligne sÚlectionnÚe   SQL> select c2 || ' TEST' from a 2 minus select distinct c2 from a;   C2||'TEST' ------------------------- TATA TEST TETE TEST TITI TEST TOTO TEST TUTU TEST   SQL> select c2 || ' TEST' from a 2 minus select distinct c2 || ' TEST' from a;   aucune ligne sÚlectionnÚe

---

Essaye de rajouter la même concaténation dans le second select du minus et ca devrait donner le bon résultat.

Oui effectivement c'est OK.

Je ne pensais pas qu'Oracle comparait les concaténations ... seulement les champs !

Bon bref j'aurai pu trouver en cherchant un peu :mrgreen:

Merci