[Oracle9i] Modification taille d'une colonne

Version imprimable

Bonjour
Je souhaite modifier la taille de certaines colonnes d'une table et plus précisemment les réduire. Ce n'est pas possible de les réduire même lorsqu'il y a des données dedans ? Je souhaite pourtant fortement les garder ...
Pourrais-je éviter de faire qq chose du genre :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 ALTER TABLE nom_table ADD (nom_colonne_temp VARCHAR2(9)) UPDATE ENTREPRISE SET nom_colonne_temp=nom_colonne ALTER TABLE nom_table DROP COLUMN nom_colonne CREATE TABLE temporary (nom_colonne, col1, col2 ,...) AS SELECT nom_colonne_temp, col1, col2 ,... FROM nom_table; DROP TABLE nom_table; RENAME temporary TO nom_table; ALTER TABLE nom_table ADD CONSTRAINT nom_contrainte PRIMARY KEY (nom_colonne)
Merci !

En 9i

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
SQL> CREATE TABLE TEST(ID VARCHAR2(50));
 
Table crÚÚe.
 
SQL> INSERT INTO TEST VALUES('0123456789');
 
1 ligne crÚÚe.
 
SQL> INSERT INTO TEST VALUES('0123456789');
 
1 ligne crÚÚe.
 
SQL> ALTER TABLE TEST MODIFY ID VARCHAR2(10);
 
Table modifiÚe.
 
SQL> describe TEST
 Nom                                       NULL ?   Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 VARCHAR2(10)
 
SQL> select * from TEST
  2  ;
 
ID
----------
0123456789
0123456789

Mais je pensais pas que ça marcherai ...

05/08/2005, 09h40
Jibees
Eh ben mince alors effectivement cela fonctionne ici. Pourtant j'ai bien l'erreur suivante :

Code:

ORA-01440: column to be modified must be empty to decrease precision or scale

Mais cette erreur ne se produit que pour la colonne suivante :
Code:

1 2 ALTER TABLE SCOTT.TABLE MODIFY (COL NUMBER(37) )
Pour les autres des char tout s'est bien passé ...
05/08/2005, 09h43
nuke_y

Et si tu fais exactement ce que j'ai fait ça donne quoi ? Il y a ptet une histoire de commits, de clés étrangères ou ce genre de choses.

Déjà es-tu sur que les données que tu as dans ta colonne sont inférieure à la taille que tu essayes de lui fixer ?

Quelle version de base ?
05/08/2005, 09h45
Jibees

Citation:

Envoyé par nuke_y

Déjà es-tu sur que les données que tu as dans ta colonne sont inférieure à la taille que tu essayes de lui fixer ?

Je suis relativement sur puisque

Code:

number(38)

est immense.
Précision : la colonne que je veux passer en Number est un float.
05/08/2005, 09h52
nuke_y

Houla attends c'est pas la même chose que ce que j'ai fait là. Pas du tout du tout.
Le VARCHAR2 je pense que ça lui pose pas de problème car il gère le fait que la taille soit dynamique. Donc si tu n'as que des données qui ont 9 caractères dans une colonne qui peut en accepter 50, il ne considère que les 9 caractères et donc on peut réduire la colonne à 9.

maintenant pour les numbers... arf !! Je regarde
05/08/2005, 10h02
Jibees

A priori mon cas rentre bien dans l'ensemble des cas possibles :

Citation:

Envoyé par www.developez.com

Le type de la colonne peut être modifié si toutes les lignes ont une valeur NULL, où si le nouveau type défini ne remet pas en cause les données existantes dans la colonne.
Vous pouvez modifier une colonne de type DATE en TIMESTAMP ou TIMESTAMP WITH LOCAL TIME ZONE.
Vous pouvez modifier une colonne de type TIMESTAMP WITH LOCAL TIME ZONE en DATE (mais les fractions de seconde et le décalage horaire sont perdus).
Vous pouvez modifier une colonne de type LONG en CLOB ou NCLOB et une colonne de type LONG RAW en BLOB.
Le seul type de contrainte applicable avec la clause MODIFY est NOT NULL, si aucune ligne de la table ne contient de NULL.
Pour définir de nouvelle contraintes (UNIQUE, PRIMARY KEY, REFERENCES, CHECK)sur une colonne existante, utilisez la clause add_column_clause.
Pour modifier une contrainte existante sur la colonne, utilisez la clause constraint_clauses.

Restrictions liées à la modification de colonnes

* Vous ne pouvez pas modifier une colonne si un index de domaine y est défini. Vous devez préalablement supprimer cet index
* Vous ne pouvez pas définir une colonne de type ROWID sur une IOT (par contre vous pouvez la définir de type UROWID)
* Vous ne pouvez pas modifier le type d'une colonne REF

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
SQL> CREATE TABLE TEST(ID float);
 
Table crÚÚe.
 
SQL> INSERT INTO TEST VALUES(123.45678);
 
1 ligne crÚÚe.
 
SQL> INSERT INTO TEST VALUES(0.12345678);
 
1 ligne crÚÚe.
 
SQL> select * from TEST;
 
        ID
----------
 123,45678
 ,12345678
 
SQL> ALTER TABLE TEST MODIFY ID NUMBER(38);
ALTER TABLE TEST MODIFY ID NUMBER(38)
                        *
ERREUR Ó la ligne 1 :
ORA-01440: column to be modified must be empty to decrease precision or scale
 
SQL> UPDATE TEST SET ID = TRUNC(ID);
 
2 ligne(s) mise(s) Ó jour.
 
SQL> ALTER TABLE TEST MODIFY ID NUMBER(38);
ALTER TABLE TEST MODIFY ID NUMBER(38)
                        *
ERREUR Ó la ligne 1 :
ORA-01440: column to be modified must be empty to decrease precision or scale

Donc ça ne marche pas. Solution de contournement sans passer par une table intermédiaire mais une colonne intermédiaire :

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
 
SQL> ALTER TABLE TEST ADD ID_TEMP NUMBER(38);
 
Table modifiÚe.
 
SQL> UPDATE TEST SET ID_TEMP = ID;
 
2 ligne(s) mise(s) Ó jour.
 
SQL> ALTER TABLE TEST DROP COLUMN ID;
 
Table modifiÚe.
 
SQL> ALTER TABLE TEST ADD ID NUMBER(38);
 
Table modifiÚe.
 
SQL> UPDATE TEST SET ID = ID_TEMP;
 
2 ligne(s) mise(s) Ó jour.
 
SQL> ALTER TABLE TEST DROP COLUMN ID_TEMP;
 
Table modifiÚe.
 
SQL> SELECT * FROM TEST;
 
        ID
----------
       123
         0
 
SQL> describe TEST
 Nom                                       NULL ?   Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(38)

05/08/2005, 10h08
nuke_y

Citation:

où si le nouveau type défini ne remet pas en cause les données existantes dans la colonne.

Ben toi ça les remet en cause puisque tu risques de perdre des décimales non ?
05/08/2005, 10h10
Jibees

Ok ben cela fonctionne impécable. C'est étrange que dans ce sens on ne peut pas faire de modifications de type même si l'ensemble des données est compatible.
05/08/2005, 10h12
Jibees

Citation:

Envoyé par nuke_y

Citation:

où si le nouveau type défini ne remet pas en cause les données existantes dans la colonne.

Ben toi ça les remet en cause puisque tu risques de perdre des décimales non ?

Ben je ne perd pas de décimale puisqu'il n'y en a pas ...

Une autre solution :

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
 
SQL> CREATE TABLE TEST(ID float); 
 
Table créée.
 
SQL> INSERT INTO TEST VALUES(123.45678); 
 
1 ligne créée.
 
SQL> INSERT INTO TEST VALUES(0.12345678); 
 
1 ligne créée.
 
SQL> commit ;
 
Validation effectuée.
 
SQL> create table test2 (ID number ) ; 
 
Table créée.
 
SQL> select * from test ;
 
        ID
----------
 123,45678
 ,12345678
 
SQL> insert into test2 select * from test ;
 
2 ligne(s) créée(s).
 
SQL> commit ;
 
Validation effectuée.
 
SQL> select * from test2 ;
 
        ID
----------
 123,45678
 ,12345678
 
SQL> drop table test ;
 
Table supprimée.
 
 
SQL> rename test2 to test ;
 
Table renommée.
 
SQL> select * from test ;
 
        ID
----------
 123,45678
 ,12345678

Cependant cette solution présente quelques inconvénients :

- Oracle transfére automatiquement les indexs , les priviléges lors du renammage , mais auparavant il faut les recréer
- cela invalide les vues , les triggers ...
- cela oblide a re créer les indexs

05/08/2005, 11h16
orafrance

c'est pas plus simple en ajoutant une colonne tout simplement ?
05/08/2005, 11h20
Jaouad

Citation:

Envoyé par orafrance

c'est pas plus simple en ajoutant une colonne tout simplement ?

POurquoi faire ultra simple quand on peut faire trés compliqué :mrgreen:
05/08/2005, 11h25
nuke_y

Excepté qu'il faut peut-être modifier tout un tas de choses, choses pour lesquelles que ce soit un float ou un number est transparent.