How does Oracle modify the data type of a column

Riveore 2021-11-25 18:42:43
oracle modify data type column

Operate on fields Operation method
Update field name alter table TABLE_NAME rename column column_old to column_new;
Add fields alter table TABLE_NAME add COLUMN_NAME varchar(10);
Delete field alter table TABLE_NAME drop column COLUMN_NAME;
Add fields and attach values alter table TABLE_NAME ADD COLUMN_NAME NUMBER(1) DEFAULT 1;
Modify field values update TABLE_NAME set filedname=value where filedname=value;
Modify field data type alter table tablename modify filedname varchar2(20);
Copy code
1 SQL> select * from v$version;

3 BANNER
4 --------------------------------------------------------------------------------
5 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
6 PL/SQL Release 11.2.0.1.0 - Production
7 CORE    11.2.0.1.0      Production
8 TNS for Linux: Version 11.2.0.1.0 - Production
9 NLSRTL Version 11.2.0.1.0 - Production
Copy code
1. When modifying the field data type , If there is data in this column, report ORA-01439: column to be modified must be empty to change datatype, At this point, you need to modify... In another way :


Copy code
 1 SQL> alter table zyt add id_temp varchar2(10);
 2 
 3 Table altered.
 4 
 5 SQL> commit;
 6 
 7 Commit complete.
 8 
 9 SQL> select * from zyt;
10 
11 NAME               ID ID_TEMP
12 ---------- ---------- ----------
13 zyt1                1
14 david               2
15 
16 SQL> alter table zyt rename column id to id_bak;
17 
18 Table altered.
19 
20 SQL> select * from zyt;
21 
22 NAME           ID_BAK ID_TEMP
23 ---------- ---------- ----------
24 zyt1                1
25 david               2
26 
27 SQL> desc zyt;
28  Name                                      Null?    Type
29  ----------------------------------------- -------- ----------------------------
30  NAME                                               VARCHAR2(10)
31  ID_BAK                                    NOT NULL NUMBER(2)
32  ID_TEMP                                            VARCHAR2(10)
33 
34 SQL> update zyt set ID_TEMP = cast(ID_BAK as varchar2(10));
35 
36 2 rows updated.
37 
38 SQL> commit;
39 
40 Commit complete.
41 
42 SQL> select * from zyt;
43 
44 NAME           ID_BAK ID_TEMP
45 ---------- ---------- ----------
46 zyt1                1 1
47 david               2 2
48 
49 SQL>  alter table zyt drop column ID_BAK;
50 
51 Table altered.
52 
53 SQL> commit;
54 
55 Commit complete.
56 
57 SQL> select * from zyt;
58 
59 NAME       ID_TEMP
60 ---------- ----------
61 zyt1       1
62 david      2
63 
64 SQL> desc zyt;
65  Name                                      Null?    Type
66  ----------------------------------------- -------- ----------------------------
67  NAME                                               VARCHAR2(10)
68  ID_TEMP                                            VARCHAR2(10)
Copy code
remarks : This method can meet the needs , Because new fields are added to the end of the table by default , Row migration is possible , Impact on Applications , It also involves complex data , Not the best way


2. Build a middle springboard , Temporary storage of data


Copy code
 1 SQL> desc zyt;
 2  Name                                      Null?    Type
 3  ----------------------------------------- -------- ----------------------------
 4  NAME                                               VARCHAR2(10)
 5  ID                                                 VARCHAR2(10)
 6 
 7 SQL> select * from zyt;
 8 
 9 NAME       ID
10 ---------- ----------
11 zyt1       1
12 david      2
13 
14 SQL> alter table zyt add id_temp VARCHAR2(10)
15 
16 Table altered.
17 
18 SQL> select * from zyt;
19 
20 NAME       ID            ID_TEMP
21 ---------- ---------- ----------
22 zyt1       1
23 david      2
24 
25 SQL> update zyt set ID_TEMP=id,id=null;
26 
27 2 rows updated.
28 
29 SQL> select * from zyt;
30 
31 NAME       ID            ID_TEMP
32 ---------- ---------- ----------
33 zyt1                           1
34 david                          2
35 
36 SQL> alter table zyt modify id number(10);
37 
38 Table altered.
39 
40 SQL> desc zyt;
41  Name                                      Null?    Type
42  ----------------------------------------- -------- ----------------------------
43  NAME                                               VARCHAR2(10)
44  ID                                                 NUMBER(10)
45  ID_TEMP                                            VARCHAR2(10)
46 
47 SQL> update zyt set id=ID_TEMP,ID_TEMP=null;
48 
49 2 rows updated.
50 
51 SQL> select * from zyt;
52 
53 NAME               ID    ID_TEMP
54 ---------- ---------- ----------
55 zyt1                1
56 david               2
57 
58 SQL> alter table zyt drop column ID_TEMP;
59 
60 Table altered.
61 
62 SQL> commit;
63 
64 Commit complete.
65 
66 SQL> select * from zyt;
67 
68 NAME               ID
69 ---------- ----------
70 zyt1                1
71 david               2
72 
73 SQL> desc zyt;
74  Name                                      Null?    Type
75  ----------------------------------------- -------- ----------------------------
76  NAME                                               VARCHAR2(10)
77  ID                                                 NUMBER(10)
Copy code
remarks : The second method , Is to add a column with the same type as the modified column , Then copy the data of the column to be modified to the new column and empty the column to be modified , Then modify the data type , Then copy the data back from the new column , This process involves two data replications , If there is a lot of data , It will be slow and produce a lot of undo and redo; The advantage is that there is no row migration .
版权声明
本文为[Riveore]所创,转载请带上原文链接,感谢
https://javamana.com/2021/11/20211109102332527l.html

  1. GitHub上霸榜久居不下的《Java面试突击宝典》,java图形用户界面设计基础
  2. GitHub上访问下载破百万的神仙文档《Java面试神技》看完我呆了,java面试问项目中遇到的问题
  3. GitHub上标星75k 超牛的《Java面试突击版,java高级工程师技能
  4. Docker development environment Preview
  5. JavaScript高級深入淺出:掌握 this 指向
  6. JavaScript Advanced Insight and outside: Mastering this direction
  7. Vue de l'application pratique de Javascript, drop drag Event
  8. docker 安装部署 Jenkins 2.322
  9. kafka安装
  10. 近九万字图文详解RabbitMQ
  11. Engaged in Java for one and a half years, how to break through yourself
  12. 输出9*9乘法表----java
  13. 判断一个数是不是素数-------java
  14. java项目,记录页面修改值,内部打“官司”用
  15. Docker installation Deployment Jenkins 2.322
  16. Comment porter un pantalon en hiver? Les petits hommes, les jambes épaisses et la largeur de l'entrejambe peuvent être vus. 3 techniques pour éviter la foudre
  17. MySQL下载和安装教程
  18. In depth analysis of rocketmq source code - message storage module
  19. Spring transaction management
  20. mysql恢复ibd数据,为何频频报错?
  21. [skills with annual salary of 60W] after working for 5 years, do you really understand netty and why to use it? (deep dry goods)
  22. Pourquoi MySQL récupère - t - il fréquemment les données ibd?
  23. Tutoriels de téléchargement et d'installation MySQL
  24. In IntelliJ idea, develop a fishing and reading plug-in
  25. Talk about how to integrate SPI with spring
  26. 重学Spring系列之Swagger2.0和Swagger3.0
  27. Hadoop05【命令操作】,Java400道面试题通关宝典助你进大厂
  28. Guava Cache缓存设计原理,java基础入门
  29. Gson:GitHub 标星 18K 的 JSON 解析器,Google 出品的 Java JSON 解析器
  30. gRPC学习之六:gRPC-Gateway集成swagger,java语言程序设计与数据结构进阶版
  31. Projet Java, enregistrer la valeur de modification de la page, utilisé pour les litiges internes
  32. Déterminer si un nombre est un nombre premier - - Java
  33. Sortie 9 * 9 tableau de multiplication - Java
  34. Plusieurs façons de réinitialiser le déplacement du Groupe de consommateurs Kafka
  35. GRPC Learning six: GRPC Gateway Integrated Swagger, Java language programming and data structure Advanced Edition
  36. python数据结构:数组、列表、栈、队列及实现
  37. Gson: json Parser for github Star 18k, Java json Parser from Google
  38. hive学习笔记之二:复杂数据类型,五分钟搞懂MySQL主从复制原理
  39. hive学习笔记之一:基本数据类型,java实现分页技术详解
  40. HDU-3038-How Many Answers Are Wrong【 带权并查集 】题解,java实战项目论坛
  41. HDU 1078 FatMouse and Cheese(记忆化搜索,DP,rocketmq教程教程
  42. Principes de conception du cache Guava, Introduction à la base Java
  43. Un pantalon beige = élégant, blanc + haut de gamme, si beau cet hiver!
  44. Hadoop05 【 commande 】, Java 400 questions d'entrevue pour vous aider à entrer dans l'usine
  45. [Java multithreading] static synchronized method and synchronized (class) code block
  46. [Java multithreading] synchronized statement block
  47. [Java multithreading] synchronized synchronization method
  48. [Java multithreading] thread priority and guard thread
  49. [Java multithreading] stop the thread
  50. Several methods of [Java multithreading] thread
  51. [Java multithreading] Java multithreading skills
  52. Structure des données Python: tableaux, listes, piles, files d'attente et implémentations
  53. Re - Learning Swagger 2.0 and Swagger 3.0 of Spring Series
  54. Error editing HTML5 using webstorm
  55. 【Spring】mybatis-spring
  56. JavaScript post request returned an exception, possibly Cross - Domain request
  57. Springboot + mybatis Plus + springsecurity + JWT implements user stateless request authentication (front and back end separation)
  58. SQL injection and XSS attack of springboot security vulnerability
  59. Docker employee readme: Why did docker lose to kubernetes?
  60. 19 high force linux commands, hurry to use them!