Skip to content

mass move tables by dbms_redifinition

mass move tables by dbms_redifinition published on Комментариев к записи mass move tables by dbms_redifinition нет

++ redefinition

—ALTER USER «XXXXXX» QUOTA UNLIMITED ON XXXXXX;

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
create table exampl_redif (a clob, i int);
  
delete from exampl_redif;
insert into exampl_redif values('
  exec dbms_redefinition.can_redef_table (''REDIFINISHION_TABLE_OWNER'',''REDIFINISHION_TABLE_NAME'',dbms_redefinition.cons_use_rowid);
  
  BEGIN
   DBMS_REDEFINITION.start_redef_table (''REDIFINISHION_TABLE_OWNER'',
                                        ''REDIFINISHION_TABLE_NAME'',
                                        ''REDIFINISHION_TABLE_NAME_REDEF'',
                                        NULL,
                                        DBMS_REDEFINITION.cons_use_rowid);
END;
/
  
  
  
  
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE(''REDIFINISHION_TABLE_OWNER'',
                                        ''REDIFINISHION_TABLE_NAME'',
                                        ''REDIFINISHION_TABLE_NAME_REDEF'');
END;
/
  
  
DECLARE
   num_errors   PLS_INTEGER;
BEGIN
   DBMS_REDEFINITION.copy_table_dependents (''REDIFINISHION_TABLE_OWNER'',
                                            ''REDIFINISHION_TABLE_NAME'',
                                            ''REDIFINISHION_TABLE_NAME_REDEF'',
                                            1,
                                            TRUE,
                                            TRUE,
                                            TRUE,
                                            TRUE,
                                            num_errors,
                                            TRUE);
END;
/
  
  
begin
for i in (
select ''alter index ''||owner||''."''||index_name||''" rebuild tablespace REDIFINISHION_TABLESPACE_NEW online'' isql from all_indexes where TABLE_NAME=''REDIFINISHION_TABLE_NAME_REDEF'' and tablespace_name=''REDIFINISHION_TABLESPACE_OLD''
)
loop
execute immediate i.isql;
end loop;
end;
/
  
  
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE(''REDIFINISHION_TABLE_OWNER'',
                                        ''REDIFINISHION_TABLE_NAME'',
                                        ''REDIFINISHION_TABLE_NAME_REDEF'');
END;
/
  
begin
for i in (
select ''alter table ''||owner||''.''||table_name||'' drop constraint ''||constraint_name isql from all_constraints where r_constraint_name=(select constraint_name from all_constraints where table_name=''REDIFINISHION_TABLE_NAME_REDEF'' and constraint_type=''P'' and owner=''REDIFINISHION_TABLE_OWNER'') and owner=''REDIFINISHION_TABLE_OWNER'' and status=''DISABLED''
)
loop
execute immediate i.isql;
end loop;
end;
/
  
DROP TABLE REDIFINISHION_TABLE_OWNER.REDIFINISHION_TABLE_NAME_REDEF;
  
  
',1);
  
commit;
  
  
  
  
  
  
  
  
select owner,table_name,
replace(
    replace(
       dbms_metadata.get_ddl('TABLE',table_name,owner),'"'||owner||'"."'||table_name||'"','"'||owner||'"."'||table_name||'_REDEF"'
           ),
'TABLESPACE "USERS"','TABLESPACE "NEW_TABLESPACE"'
       ) || ';' ||
                replace(
                  replace(
                   replace(
                    replace(
                      (select a from  exampl_redif),'REDIFINISHION_TABLE_OWNER',owner
                            ),'REDIFINISHION_TABLE_NAME',table_name
                           ),'REDIFINISHION_TABLESPACE_NEW','PGU'
                          ),'REDIFINISHION_TABLESPACE_OLD','USERS'
                        )                        
from all_tables where TABLESPACE_NAME='USERS' and OWNER='MY_OWNER';

ps: thx to Vladimir Mukin for material

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.

Primary Sidebar