insert into thetest values(1,to_date(\u201820151101\u2019, \u2018YYYYMMDD\u2019), \u20181\u2019, \u2018A\u2019);<\/p>\n
insert into thetest values(2,to_date(\u201820151001\u2019, \u2018YYYYMMDD\u2019), \u20181\u2019, \u2018A\u2019);<\/p>\n
insert into thetest values(3,to_date(\u201820151201\u2019, \u2018YYYYMMDD\u2019), \u20181\u2019, \u2018A\u2019);<\/p><\/blockquote>\n
T\u1ea1o m\u1edbi m\u1ed9t b\u1ea3ng s\u1ea1ch s\u1ebd v\u1edbi c\u1ea5u tr\u00fac gi\u1ed1ng h\u1ec7t nh\u01b0ng \u0111\u00e3 khai b\u00e1o partition. \u1ede \u0111\u00e2y t\u00f4i dung composite partition:<\/p>\n
create table thetest2(<\/p>\n
logid number,<\/p>\n
transdate date,<\/p>\n
status varchar2(50),<\/p>\n
pid varchar2(50),<\/p>\n
constraint pk_logid2 PRIMARY KEY(logid))<\/p>\n
partition by range(transdate) subpartition by list(pid)<\/p>\n
subpartition template(<\/p>\n
subpartition pa values(\u2018A\u2019, \u2018a\u2019),<\/p>\n
subpartition pb values(\u2018B\u2019, \u2018b\u2019)<\/p>\n
)<\/p>\n
(<\/p>\n
partition p201509 values less than (to_date(\u201820151001\u2032,\u2019YYYYMMDD\u2019)),<\/p>\n
partition p201510 values less than (to_date(\u201820151101\u2032,\u2019YYYYMMDD\u2019))<\/p>\n
)<\/p><\/blockquote>\n
B\u01b0\u1edbc 3: Ki\u1ec3m tra xem c\u00f3 redefinition \u0111\u01b0\u1ee3c b\u1ea3ng ban \u0111\u1ea7u kh\u00f4ng<\/h3>\n
\u0110\u0103ng nh\u1eadp b\u1eb1ng user SYS<\/strong> ho\u1eb7c m\u1ed9t user n\u00e0o \u0111\u01b0\u1ee3c g\u00e1n quy\u1ec1n EXEC DBMS_REDEFINITION<\/strong><\/p>\nEXEC DBMS_REDEFINITION.can_redef_table<\/em><\/strong>(\u2018SCHEMA\u2019, \u2018THETEST\u2019);<\/p><\/blockquote>\nN\u1ebfu c\u00e2u l\u1ec7nh tr\u00ean kh\u00f4ng g\u00e2y l\u1ed7i t\u1ee9c l\u00e0 c\u00f3 th\u1ec3 redefine l\u1ea1i b\u1ea3ng \u0111\u01b0\u1ee3c v\u1edbi user \u0111\u00e3 khai b\u00e1o<\/p>\n
B\u01b0\u1edbc 4: Ghi nh\u1edb c\u00e2u l\u1ec7nh stop\/abort<\/h3>\n
L\u00e0 m\u1ed9t DBA, b\u1ea1n n\u00ean ghi nh\u1edb c\u00e2u stop tr\u01b0\u1edbc khi h\u1ecdc c\u00e2u start.!<\/p>\n
EXEC DBMS_REDEFINITION.abort_redef_table(\u2018SCHEMA\u2019, \u2018THETEST\u2019, \u2018THETEST2\u2019);<\/p><\/blockquote>\n
N\u1ebfu qu\u00e1 tr\u00ecnh organize table c\u00f3 v\u1ea5n \u0111\u1ec1 ch\u00fang ta s\u1ebd ph\u1ea3i th\u1ef1c hi\u1ec7n vi\u1ec7c restart l\u1ea1i qu\u00e1 tr\u00ecnh n\u00e0y nh\u01b0ng b\u1edfi v\u00ec redefinition s\u1ebd y\u00eau c\u1ea7u t\u1ea1o snapshot n\u00ean ph\u1ea3i ch\u1ea1y c\u00e2u l\u00eanh abort_redef_table()<\/strong> \u1edf tr\u00ean \u0111\u1ec3 release snapshot tr\u01b0\u1edbc khi b\u1eaft \u0111\u1ea7u l\u1ea1i.<\/p>\nC\u00f3 th\u1ec3 th\u1eed b\u1eb1ng c\u00e1ch t\u1ea1o \u00edt partition \u1edf b\u1ea3ng t\u1ea1m h\u01a1n so v\u1edbi c\u00e1c gi\u00e1 tr\u1ecb c\u1ee7a b\u1ea3ng ch\u00ednh. Khi n\u00e0y sau khi b\u1eaft \u0111\u1ea7u ti\u1ebfn tr\u00ecnh th\u00ec Oracle s\u1ebd b\u00e1o l\u1ed7i do m\u1ed9t s\u1ed1 gi\u00e1 tr\u1ecb s\u1ebd kh\u00f4ng bi\u1ebft cho v\u00e0o partition n\u00e0o. L\u00fac n\u00e0y gi\u1ea3 s\u1eed r\u1eb1ng ta \u0111\u00e3 t\u1ea1o th\u00eam partition \u1edf b\u1ea3ng t\u1ea1m cho chu\u1ea9n v\u1edbi d\u1eef li\u1ec7u (c\u00e1ch th\u00eam partition v\u00e0o b\u1ea3ng c\u00f3 s\u1eb5n d\u1eef li\u1ec7u \u0111\u00e3 n\u00eau \u1edf tr\u00ean) ch\u1ea1y l\u1ea1i c\u00e2u l\u1ec7nhstart_redef_table()<\/strong> s\u1ebd b\u1ecb l\u1ed7i:<\/p>\nORA-23539: table \u201cSCHEMA\u201d.\u201dTHETEST\u201d currently being redefined<\/p><\/blockquote>\n
Nh\u01b0 v\u1eady Oracle v\u1eabn ghi nh\u1eadn b\u1ea3ng THETSET \u0111ang \u0111\u01b0\u1ee3c redefined n\u00ean t\u1ea5t nhi\u00ean s\u1ebd ng\u0103n c\u1ea5m kh\u00f4ng cho th\u1ef1c hi\u1ec7n c\u00e2u l\u1ec7nh.<\/p>\n
B\u01b0\u1edbc 5: Th\u1ef1c hi\u1ec7n ti\u1ebfn tr\u00ecnh<\/h3>\nEXEC DBMS_REDEFINITION.start_redef_table<\/em><\/strong>(\u2018SCHEMA\u2019, \u2018THETEST\u2019, \u2018THETEST2\u2019);<\/p>\n\u2014<\/p><\/blockquote>\n
L\u01b0u \u00fd: sau c\u00e2u l\u1ec7nh n\u00e0y, d\u1eef li\u1ec7u \u0111\u00e3 \u0111\u01b0\u1ee3c chuy\u1ec3n sang b\u1ea3ng THETEST2 nh\u01b0ng d\u1eef li\u1ec7u tr\u00ean b\u1ea3ng THETEST v\u1eabn c\u00f2n. Th\u1eddi gian th\u1ef1c hi\u1ec7n ti\u1ebfn tr\u00ecnh n\u00e0y kh\u00f4ng nhanh v\u00e0 ph\u1ee5 thu\u1ed9c v\u00e0o d\u1eef li\u1ec7u trong DB.<\/p>\n
exec DBMS_REDEFINITION.sync_interim_table<\/em><\/strong>(\u2018SCHEMA\u2019, \u2018THETEST\u2019, \u2018THETEST2\u2019);<\/p><\/blockquote>\nCu\u1ed1i c\u00f9ng l\u00e0 k\u1ebft th\u00fac qu\u00e1 tr\u00ecnh redefinition<\/p>\n
exec DBMS_REDEFINITION.finish_redef_table<\/em><\/strong>(\u2018SCHEMA\u2019, \u2018THETEST\u2019, \u2018THETEST2\u2019);<\/p><\/blockquote>\nSau c\u00e2u l\u1ec7nh n\u00e0y, ph\u1ea7n b\u1ea3ng THETEST \u0111\u00e3 c\u00f3 partition \u0111\u1ed3ng th\u1eddi c\u00f3 d\u1eef li\u1ec7u \u0111\u1ea7y \u0111\u1ee7. B\u00e2y gi\u1edd c\u00f3 th\u1ec3 drop b\u1ea3ng t\u1ea1m THETEST2 \u0111i do b\u1ea3ng n\u00e0y v\u1eabn c\u00f2n d\u1eef li\u1ec7u nh\u01b0ng kh\u00f4ng c\u00f2n c\u1ea7n thi\u1ebft n\u1eefa.<\/p>\n
Th\u1eddi gian th\u1ef1c hi\u1ec7n ti\u1ebfn tr\u00ecnh finish n\u00e0y kh\u00e1 d\u00e0i n\u00ean \u0111\u00f2i h\u1ecfi t\u00ednh to\u00e1n h\u1ee3p l\u00fd v\u00e0 c\u00f3 s\u1ef1 ki\u00ean nh\u1eabn. Th\u00eam m\u1ed9t v\u1ea5n \u0111\u1ec1 khi l\u00e0m DBA l\u00e0 t\u00ecnh ki\u00ean tr\u00ec quy\u1ebft kh\u00f4ng b\u1ea5m Cancel. Thay v\u00ec \u0111\u00f3 h\u00e3y gi\u00e0nh th\u1eddi gian chu\u1ea9n b\u1ecb k\u1ef9 c\u00e0ng v\u00e0 h\u1ecdc c\u00e1ch stop ti\u1ebfn tr\u00ecnh.<\/p>\n
B\u01b0\u1edbc 6: D\u1ecdn d\u1eb9p ti\u1ebfn tr\u00ecnh<\/h3>\ndrop table thetest2;<\/p>\n
alter table thetest rename constraint pk_logid2 to pk_logid;<\/p>\n
alter index pk_logid2 rename to pk_logid;<\/p><\/blockquote>\n
\u0110o\u1ea1n tr\u00ean \u0111\u00e3 th\u1ef1c hi\u1ec7n x\u00f3a b\u1ea3ng t\u1ea1m, \u0111\u1ed5i t\u00ean l\u1ea1i c\u00e1c constraint v\u00e0 c\u00e1c index cho \u0111\u00fang v\u1edbi b\u1ea3ng c\u0169. Nghi\u00ean c\u1ee9u v\u00e0 ki\u1ec3m tra ch\u00ednh x\u00e1c b\u1ea3ng c\u0169 l\u00e0 r\u1ea5t quan tr\u1ecdng.<\/p>\n","protected":false},"excerpt":{"rendered":"
H\u01b0\u1edbng d\u1eabn thao t\u00e1c v\u1edbi Partition trong Oracle DB Drop partition Drop m\u1ed9t partition s\u1ebd x\u00f3a to\u00e0n b\u1ed9 d\u1eef li\u1ec7u tr\u00ean partition \u0111\u00f3 \u2013drop a partition alter table thetest drop partition p201510; \u2013>drop a partition…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-401","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/www.asianux.org.vn\/index.php\/wp-json\/wp\/v2\/posts\/401","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.asianux.org.vn\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.asianux.org.vn\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.asianux.org.vn\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.asianux.org.vn\/index.php\/wp-json\/wp\/v2\/comments?post=401"}],"version-history":[{"count":0,"href":"https:\/\/www.asianux.org.vn\/index.php\/wp-json\/wp\/v2\/posts\/401\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.asianux.org.vn\/index.php\/wp-json\/wp\/v2\/media?parent=401"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.asianux.org.vn\/index.php\/wp-json\/wp\/v2\/categories?post=401"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.asianux.org.vn\/index.php\/wp-json\/wp\/v2\/tags?post=401"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}