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 :
Ensuite j'ai voulu faire un truc du genre :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
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).
Voila, la difference entre les 2 requetes est la concaténation de champs texte. Je ne saisi pas trop pourquoi le resultat est different ...
Code : Sélectionner tout - Visualiser dans une fenêtre à part
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).
Une explication ?
Partager