Réduire la taille du tablespace SYSTEM [Résolu]

[annemar]

Une base de données 9i (9.2.0.7) de 15go avec un tbs SYSTEM de 7Go !! Puis-je réduire la taille de ce tbs et comment ?
Merci pour toutes réponses ...
annemar

[scheu]

7 Go ? Es-tu sûr que tu n'as pas d'objets appartenant à des schémas applicatifs ?
Que donne la requête suivante :

Code :

1
2
SELECT owner,sum(bytes) FROM dba_segments 
WHERE tablespace_name = 'SYSTEM' GROUP BY owner;

[7gyY9w1ZY6ySRgPeaefZ]

ouais, il y a surement des choses dans system qui ne sont pas au bon endroit !

[13thFloor]

Envoyé par Jerome_Mtl

ouais, il y a surement des choses dans system qui ne sont pas au bon endroit !

Pour sur, il y a du move et du rebuild dans l'air.
A moins qu'un tablespace temporaire défini en tant que system ait écrit dedans.

La réduction d'un datafile se fait avec la commande alter database datafile '/..../fichier.xxx' resize nnnM;
Si toutefois le high water mark du datafile le permet.

[annemar]

Bon, il n'y a pas que du SYSTEM ou SYS, mais ce ne sont pas des schémas de nos applicatifs. De plus, la taille cumulée de ces octets ne semble pas faire les 7go de la tbs.
SQL> SELECT owner,sum(bytes) FROM dba_segments
WHERE tablespace_name = 'SYSTEM' GROUP BY owner; 2

OWNER SUM(BYTES)
------------------------------ ----------
DBSNMP 720896
ORDSYS 458752
OUTLN 393216
SYS 640942080
SYSTEM 21757952
WMSYS 3866624

6 rows selected.

Que puis-je faire ?

[scheu]

La somme des tailles des objets fait beaucoup moins que 7 Go ...
Comme dit 13thFloor, le tablespace SYSTEM ne serait-il pas utilisé à tort comme tablespace temporaire ? As-tu bien créé un autre tablespace temporaire (souvent il s'appelle TEMP) utilisé par défaut ?

[annemar]

Correction de ce que je viens de répondre :
SQL> SELECT owner,sum(bytes) FROM dba_segments
2 WHERE tablespace_name = 'SYSTEM' GROUP BY owner;

OWNER SUM(BYTES)
------------------------------ ----------
DBSNMP 720896
ORDSYS 458752
OUTLN 393216
SYS 6959857664
SYSTEM 21626880
WMSYS 3866624

6 rows selected.

On a bien les 6 schémas mais la taille n'est pas la même ; ici, on retrouve bien les 7 go !! (je m'étais trompée de db, bien sûr !!)

[annemar]

Complément d'info
Via OEM Grid, je trouve :
table SYS.SOURCE$ --> 4 711 424 KB
index SYS.I_SOURCE1 --> 1 246 208 KB

Comment les "réduire" ?

[orafrance]

c'est probablement le code stockée en base, tu as beaucoup de packages ou vues ?

[annemar]

Je ne pense pas qu'il y ait bp de packages, vues etc ... En fait, cette base de données est la copie de la base de production qui a un tablespace SYSTEM de 600Mo (ce qui est très bien chez nous)
La spécificité de cette base qui pose problème est que chaque nuit, un batch tourne pour la réalimenter :
1) drop dans le schéma de l'application de tout (table, procédure, type, view, package, sequence, function), soit 1300 objets environ
2) import du schéma "fraichement" exporté de la base de prod.
Cette base copie est alimentée ainsi depuis mars 2006. Peut-être est-ce cela qui a fait grossir la table SOURCE$.
Que faire de cette table SOURCE$ et de cet index I_SOURCE1 ?
Merci de vos réponses rapides. Ca fait avancer la réflexion !

[orafrance]

A mon avis tu as un paramétrage différent des tablespaces sur les 2 bases. Vérifie au moins que les clauses storages ne sont pas extravagantes notamment le PCTINCREASE et NEXT_EXTENT.

[laurentschneider]

Envoyé par annemar

SYS 6959857664

quels sont ces gros objets dans sys?

Code :

1
2
3
4
5
6
 
SELECT * FROM (
  SELECT segment_type, segment_name, bytes 
  FROM dba_segments WHERE owner='SYS' 
  ORDER BY bytes DESC) 
WHERE rownum<10;

peut-être qu'il y a en effet une quantité effroyable de packages...

Code :

1
2
3
4
 
SELECT owner, sum(length(text)) 
FROM dba_source 
GROUP BY rollup(owner) ORDER BY 2;

[13thFloor]

Envoyé par annemar

Complément d'info
Via OEM Grid, je trouve :
table SYS.SOURCE$ --> 4 711 424 KB
index SYS.I_SOURCE1 --> 1 246 208 KB

Comment les "réduire" ?

Si j'ai bonne mémoire, source$ stocke tout le code PL/SQL des packages, fonctions, procédures et triggers.
C'est un des objets du dictionnaire qui parfois est le plus gros, notamment quand un base comporte de très nombreux codes source (cas des ERP ou des spécifiques ou tout le code applicatif est stocké en base).

Un alter table source$ move; devrait compacter cette table.
Ne pas oublier un alter index I_SOURCE1 rebuild;
A faire pendant aucune activité (mode restricted conseillé).

C'est néanmoins curieux qu'un clone avec 600 Mo pour le tablespace system passe à plus d'1 Go : de nouveaux schémas ont dû être créés.
A vérifier : le nombre d'objets par schémas.

Code :

1
2
3
4
5
SELECT owner,object_type,count(*)
  FROM dba_objects
 WHERE owner NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP','WMSYS','SYSMAN','TSMSYS')
 GROUP BY owner,object_type
 ORDER BY owner,object_type;

J'ai laissé volontairement PERFSTAT, sait-on jamais, tu as peut être tous les snapshots depuis mars 2006 => sppurge à lancer, mais visiblement non vu les résultats des requêtes précédentes.

[laurentschneider]

Envoyé par 13thFloor

Un alter table source$ move; devrait compacter cette table.
Ne pas oublier un alter index I_SOURCE1 rebuild;
A faire pendant aucune activité (mode restricted conseillé).

attention, cette opération n'est pas innocente et pourrait corrompre ta base...

pour mémoire : http://download.oracle.com/docs/cd/B....htm#sthref854
Caution:
Altering or manipulating the data in data dictionary tables can permanently and
detrimentally affect the operation of a database.

[13thFloor]

Envoyé par laurentschneider

attention, cette opération n'est pas innocente et pourrait corrompre ta base...
...
Altering or manipulating the data in data dictionary tables can permanently and
detrimentally affect the operation of a database.

Ce n'est pas courant je te l'accorde mais en déplaçant la table, on ne touche pas aux datas, seulement à la structure. Je viens de faire un test rapide (mais sur une 10gr2) : no souci mais pas en phase avec son environnement donc sans valeur.
De toute façon, avant une telle manipulation, il vaut mieux faire un backup.
A part recréer sa base, je ne vois pas comment abaisser drastiquement la table SOURCE$.

[laurentschneider]

recréer la base me parait une bonne solution. CREATE DATABASE, EXP, IMP.

Si tu fais un "ALTER" sur une table système, tu risques gros. Même si tu penses que ça marche, peut-être qu'une perversité du système provoquera une corruption dans le dictionnaire, et cette corruption, tu la remarqueras peut-être dans plusieurs années

Lorsque tu as une corruption dans le dictionnaire, c'est très difficile d'en connaitre la cause, c'est pourquoi je recommende la plus grande prudence !

[orafrance]

surtout que tu risques d'invalider des vues ou packages du dictionnaire ce qui n'est pas très joyeux

[philcero]

Tout à fait d'accord avec vous, on ne touche pas aux objets du tablespace SYSTEM impunément.

Si la base n'est pas H24, un bon petit EXPORT/NOUVELLE BASE/IMPORT reste la meilleure chose à faire. Par mesure de sécurité, si il y a de la place, rien n'empêche de faire cela dans une deuxième base qui peut cohabiter avec la première...

Mais avant tout il faudrait être sûr de la volumétrie des sources. Peux-tu poster le résultat de la requête suivante s.v.p :

Code :

1
2
3
4
SELECT owner, type, ceil(sum(length(text))/1024) Ko
FROM dba_source
GROUP BY owner, type
ORDER BY owner, type;

[annemar]

Mes réponses dans l'ordre :

pour orafrance -------> pas de pct_increase et next_extent sur cette tbs SYSTEM (ni sur la tbs SYSTEM de la base de prod). Est-ce parce que la tbs est locally managed, créée a priori sans spécifier ces paramètres ?

pour laurent schneider ------->
les objets de SYS les plus gros sont comme je le disais plus haut SOURCE$ et index:
SEGMENT_TYPE SEGMENT_NAME BYTES
------------------ ------------- ----------
TABLE SOURCE$ 4824498176
INDEX I_SOURCE1 1276116992
TABLE IDL_UB1$ 430964736
TABLE IDL_UB2$ 200278016
TYPE2 UNDO _SYSSMU6$ 159506432
TYPE2 UNDO _SYSSMU1$ 109174784
TYPE2 UNDO _SYSSMU9$ 109174784
TYPE2 UNDO _SYSSMU2$ 109174784
TYPE2 UNDO _SYSSMU5$ 109174784

Pour le nombre de packages et autres, j'ai les mêmes tailles ou presque entre les 2 bd. Dans la base de données qui pose problème :

OWNER SUM(LENGTH(TEXT))
------------------------------ -----------------
OUTLN 582
WEBUTIL 5983
WMSYS 6371
SYSTEM 61391
PERFSTAT 100105
DBSNMP 101107
ORDPLUGINS 235618
PLTOOLKIT 298880
HST65 785114
ORDSYS 929413
XDB 1061610
WKSYS 2233285
CTXSYS 3519014
SYS 35017045
GENOBJ 45080673
89436191

pour "13th floor" et laurent schneider ----->
Je vais effectivement attendre avant de compacter SOURCE$ (et index). Si je n'ai vraiment pas d'autre solution, je ferai cela pour voir, avant de reconstruire entièrement la base copie (ça c'était mon idée première, mais si une solution moins bourrin existe ...).

J'ai à peu de chose près le même nombre de schémas et autres entre la base de prod (SYSTEM : 600Mo) et la base copie (SYSTEM : 7Go). Ce qui peut faire la "différence" c'est peut-être la suppression de chaque objet du schéma GENOBJ et le réimport, de façon quotidienne.

D'autres idées pour les 2 questions qui finalement se posent :
comment réduire SOURCE$ et index ?
pourquoi SOURCE$ et index sont-ils devenus aussi gros ?

Merci d'avance

[annemar]

Mes réponses suite

Pour laurent schneider, orafrance
c'est une base copie, je peux la recréer "à la demande" par une copie de la base de production et create des control files. Donc si je fais la manip d'alter sur la table SOURCE$, ce sera pour un essai, à un moment ou j'aurai l'opportunité de recréer la base copie, si nécessaire. En attendant, elle fonctionne très bien avec son SYSTEM à 7 Go

pour philcero :
Ce n'est pas une grosse base. Voici le résultat de ta 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
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
OWNER                          TYPE                 KO
------------------------------ ------------ ----------
CTXSYS                         FUNCTION              5
CTXSYS                         PACKAGE             269
CTXSYS                         PACKAGE BODY       2957
CTXSYS                         PROCEDURE             1
CTXSYS                         TYPE                 29
CTXSYS                         TYPE BODY           178
DBSNMP                         PACKAGE               5
DBSNMP                         PACKAGE BODY         95
GENOBJ                         JAVA SOURCE           3
GENOBJ                         PACKAGE            2368
GENOBJ                         PACKAGE BODY      32566
GENOBJ                         PROCEDURE           267
GENOBJ                         TRIGGER            8781
GENOBJ                         TYPE                 18
GENOBJ                         TYPE BODY            25
HST65                          PACKAGE              94
HST65                          PACKAGE BODY        496
HST65                          PROCEDURE             2
HST65                          TRIGGER             177
ORDPLUGINS                     PACKAGE              34
ORDPLUGINS                     PACKAGE BODY        197
ORDSYS                         FUNCTION              6
ORDSYS                         JAVA SOURCE          33
ORDSYS                         PACKAGE              28
ORDSYS                         PACKAGE BODY        263
ORDSYS                         TYPE                 66
ORDSYS                         TYPE BODY           514
OUTLN                          PROCEDURE             1
PERFSTAT                       PACKAGE               8
PERFSTAT                       PACKAGE BODY         91
PLTOOLKIT                      PACKAGE              66
PLTOOLKIT                      PACKAGE BODY        227
SYS                            FUNCTION             13
SYS                            JAVA SOURCE          33
SYS                            PACKAGE            4420
SYS                            PACKAGE BODY      28785
SYS                            PROCEDURE           239
SYS                            TRIGGER               3
SYS                            TYPE                258
SYS                            TYPE BODY           450
SYSTEM                         PACKAGE              13
SYSTEM                         PACKAGE BODY         46
SYSTEM                         PROCEDURE             2
SYSTEM                         TRIGGER               1
SYSTEM                         TYPE                  1
WEBUTIL                        PACKAGE               2
WEBUTIL                        PACKAGE BODY          5
WKSYS                          FUNCTION              2
WKSYS                          PACKAGE             154
WKSYS                          PACKAGE BODY       2020
WKSYS                          PROCEDURE             4
WKSYS                          TRIGGER               3
WKSYS                          TYPE                  1
WMSYS                          PROCEDURE             3
WMSYS                          TYPE                  4
XDB                            FUNCTION              7
XDB                            PACKAGE             116
XDB                            PACKAGE BODY        842
XDB                            PROCEDURE            29
XDB                            TRIGGER               4
XDB                            TYPE                 38
XDB                            TYPE BODY             4