oracle分区表在线重定义

?
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
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
「使用在线重定义的一些限制条件」：
必须有足够的表空间来容纳表的两倍数据量。
主键列不能被修改。
表必须有主键。
必须在同一个用户下进行在线重定义。
SYS和SYSTEM用户下的表无法进行在线重定义。
在线重定义无法采用nologging。
如果中间表有新增列，则不能有NOT NULL约束
 
模拟oracle分区表在线重定义
 
sqlplus par/par
SQL> create table par1 (id number(8) primary key,name varchar2(10) not null,par_date date) tablespace par;
 
Table created.
 
SQL> comment on table par1 is 'fenqubiao';
 
Comment created.
 
SQL>
SQL> comment on column par1.name is 'mingzi';
 
Comment created.
 
SQL> comment on column par1.par_date is 'fenquriqi';
 
Comment created.
 
SQL> create index index_name on par1(name) tablespace par;
 
Index created.
 
sqlplus par/par
SQL> begin
  2  for i in 1..30
  3  loop
  4  insert into par1 values (i,'yang'||i,add_months(to_date('2021-1-1', 'yyyy-mm-dd'), i));
  5  end loop;
  6  commit;
  7  end;
  8  /
 
PL/SQL procedure successfully completed.
 
 
select cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name = 'PAR1';
 
sqlplus / as sysdba
exec dbms_redefinition.can_redef_table('PAR', 'PAR1');
 
 
sqlplus par/par
create table par2
(
  id       NUMBER(8),
  name     VARCHAR2(20),
  par_date DATE
)
partition BY RANGE(par_date)(
partition par_P202101 values less than (TO_DATE(' 2021-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition par_P202102 values less than (TO_DATE(' 2021-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition par_P202103 values less than (TO_DATE(' 2021-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition par_P202104 values less than (TO_DATE(' 2021-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition par_P202105 values less than (TO_DATE(' 2021-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition par_P202106 values less than (TO_DATE(' 2021-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition par_P202107 values less than (TO_DATE(' 2021-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition par_P202108 values less than (TO_DATE(' 2021-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition par_P202109 values less than (TO_DATE(' 2021-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition par_P202110 values less than (TO_DATE(' 2021-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition par_P202111 values less than (TO_DATE(' 2021-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition par_P202112 values less than (TO_DATE(' 2022-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition par_P202201 values less than (TO_DATE(' 2022-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition par_P202202 values less than (TO_DATE(' 2022-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition par_P202203 values less than (TO_DATE(' 2022-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition par_P202204 values less than (TO_DATE(' 2022-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition par_P202205 values less than (TO_DATE(' 2022-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition par_P202206 values less than (TO_DATE(' 2022-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition par_P202207 values less than (TO_DATE(' 2022-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition par_P202208 values less than (TO_DATE(' 2022-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition par_P202209 values less than (TO_DATE(' 2022-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition par_P202210 values less than (TO_DATE(' 2022-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition par_P202211 values less than (TO_DATE(' 2022-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition par_P202212 values less than (TO_DATE(' 2023-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition par_P202301 values less than (TO_DATE(' 2023-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition par_P202302 values less than (TO_DATE(' 2023-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition par_P202303 values less than (TO_DATE(' 2023-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition par_P202304 values less than (TO_DATE(' 2023-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition par_P202305 values less than (TO_DATE(' 2023-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition par_P202306 values less than (TO_DATE(' 2023-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition par_P202307 values less than (TO_DATE(' 2023-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition par_MAX values less than (maxvalue) tablespace par)
             enable row movement
             tablespace par;
              
sqlplus / as sysdba         
select row_movement from dba_tables where table_name='PAR1' and owner='PAR';
select row_movement from dba_tables where table_name='PAR2' and owner='PAR';
 
sqlplus / as sysdba
exec dbms_stats.gather_table_stats(ownname => 'PAR',tabname => 'PAR1',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE,degree => '8') ;
exec dbms_stats.gather_table_stats(ownname => 'PAR',tabname => 'PAR2',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE,degree => '8') ;
 
sqlplus / as sysdba
EXEC DBMS_REDEFINITION.START_REDEF_TABLE('PAR','PAR1','PAR2');
 
sqlplus par/par
create index index_name_par on par2(name) tablespace par local parallel 8;
 
sqlplus / as sysdba
BEGIN
dbms_redefinition.sync_interim_table(
uname => 'PAR',
orig_table => 'PAR1',
int_table => 'PAR2');
END;
/
 
sqlplus / as sysdba
BEGIN
dbms_redefinition.finish_redef_table(
uname => 'PAR',
orig_table => 'PAR1',
int_table => 'PAR2');
END;
/
 
 
sqlplus par/par
SQL> select table_name,partitioned from user_tables where table_name in ('PAR1','PAR2');
 
TABLE_NAME                     PAR
------------------------------ ---
PAR1                           YES
PAR2                           NO
 
SQL> select INDEX_NAME,TABLE_NAME from dba_indexes where TABLE_NAME in ('PAR1','PAR2');
 
INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
INDEX_NAME_PAR                 PAR1
INDEX_NAME                     PAR2
SYS_C0011135                   PAR2
 
SQL> alter index index_name rename to index_name_bak;
 
Index altered.
 
SQL> alter index index_name_par rename to index_name;
 
Index altered.
 
SQL> select INDEX_NAME,TABLE_NAME from dba_indexes where TABLE_NAME in ('PAR1','PAR2');
 
INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
INDEX_NAME                     PAR1
INDEX_NAME_BAK                 PAR2
SYS_C0011135                   PAR2

　　

posted @   orcl  阅读(120)  评论(0)  编辑  收藏  举报
相关博文：
· oracle迁移（rman）
· oracle迁移（duplicate）
· Oracle在线重定义分区表
· oracle 在线重定义表
· oracle 数据库基本操作
阅读排行：
· 阿里最新开源QwQ-32B，效果媲美deepseek-r1满血版，部署成本又又又降低了！
· 开源Multi-agent AI智能体框架aevatar.ai，欢迎大家贡献代码
· Manus重磅发布：全球首款通用AI代理技术深度解析与实战指南
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· AI技术革命，工作效率10个最佳AI工具
历史上的今天：
2020-08-05 oracle 11.2.0.4下载
点击右上角即可分享
微信分享提示
AI IDE Trae