Discussion :

[Jibees]

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 :

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
 
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 !

[nuke_y]

En 9i

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
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 ...

[Jibees]

Eh ben mince alors effectivement cela fonctionne ici. Pourtant j'ai bien l'erreur suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
ALTER TABLE SCOTT.TABLE
	MODIFY (COL	NUMBER(37) )

Pour les autres des char tout s'est bien passé ...

[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 ?

[Jibees]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

number(38)

est immense.
Précision : la colonne que je veux passer en Number est un float.

[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

[Jibees]

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

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

[nuke_y]

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
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 :

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
 
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)

[nuke_y]

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 ?

[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.

[Jibees]

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 ...

[Jaouad]

Une autre solution :

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
 
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

[orafrance]

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

[Jaouad]

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

POurquoi faire ultra simple quand on peut faire trés compliqué

[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.