{"id":1160,"date":"2015-06-03T14:18:17","date_gmt":"2015-06-03T06:18:17","guid":{"rendered":"http:\/\/www.821121.com\/?p=1160"},"modified":"2020-12-18T15:07:06","modified_gmt":"2020-12-18T07:07:06","slug":"oracle-11g-%e5%9c%a8%e7%ba%bf%e9%87%8d%e5%ae%9a%e4%b9%89-%e4%b9%8b-%e6%99%ae%e9%80%9a%e8%a1%a8%e8%bd%ac%e5%88%86%e5%8c%ba%e8%a1%a8","status":"publish","type":"post","link":"http:\/\/www.821121.com\/?p=1160","title":{"rendered":"Oracle 11g \u5728\u7ebf\u91cd\u5b9a\u4e49 \u4e4b \u666e\u901a\u8868\u8f6c\u5206\u533a\u8868"},"content":{"rendered":"

\u672c\u6587\u4e3b\u8981\u4ecb\u7ecd\u4e86\u4f7f\u7528\u5728\u7ebf\u91cd\u5b9a\u4e49\u5c06\u666e\u901a\u8868\u8f6c\u4e3a\u5206\u533a\u8868\u7684\u64cd\u4f5c\u6d41\u7a0b\u3002<\/p>\n

\u7528\u6237\u6388\u6743<\/h1>\n
GRANT CREATE SESSION, CREATE ANY TABLE,ALTER ANY TABLE,\nDROP ANY TABLE, LOCK ANY TABLE  ,SELECT ANY TABLE,\nCREATE ANY INDEX,CREATE ANY TRIGGER\nTO WRP_PSS;\nGRANT EXECUTE_CATALOG_ROLE TO USERNAME;<\/code><\/pre>\n
\u786e\u8ba4\u8868\u662f\u5426\u53ef\u4ee5\u505a\u5728\u7ebf\u91cd\u5b9a\u4e49<\/h1>\n
\u6e90\u8868\u6709\u4e3b\u952e\uff1a<\/h2>\n
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('USERNAME', 'TABLENAME');<\/code><\/pre>\n
\u6e90\u8868\u65e0\u4e3b\u952e\uff1a<\/h2>\n
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('USERNAME', 'TABLENAME', dbms_redefinition.cons_use_rowid);<\/code><\/pre>\n
\u6839\u636e\u6e90\u8868DDL\u8bed\u53e5\uff0c\u521b\u5efa\u4e2d\u95f4\u8868<\/h1>\n
create table tablename_TMP\n(\n\n)\npartition by range(TRANSTIME)\n(\n\n)\ntablespace tablespacename\n  pctfree 10\n  initrans 1\n  maxtrans 255\n  storage\n  (\n    initial 64K\n    next 1M\n    minextents 1\n    maxextents unlimited\n  );<\/code><\/pre>\n
\u5f00\u59cb\u5728\u7ebf\u91cd\u5b9a\u4e49<\/h1>\n
\u6e90\u8868\u6709\u4e3b\u952e\uff1a<\/h2>\n
BEGIN\nDBMS_REDEFINITION.START_REDEF_TABLE('USERNAME', 'TABLENAME', 'TABLENAME_TMP');\nEND;\n\/<\/code><\/pre>\n
\u6e90\u8868\u65e0\u4e3b\u952e\uff1a<\/h2>\n
BEGIN\nDBMS_REDEFINITION.START_REDEF_TABLE('USERNAME', 'TABLENAME', 'TABLENAME_TMP', null, dbms_redefinition.cons_use_rowid);\nEND;\n\/<\/code><\/pre>\n
\u628a\u539f\u59cb\u8868\u7684\u89e6\u53d1\u5668\u3001\u6743\u9650\u3001\u7edf\u8ba1\u4fe1\u606f\u3001\u7269\u5316\u89c6\u56fe\u5728\u4e2d\u95f4\u8868\u4e0a\u521b\u5efa\u4e00\u4efd<\/h1>\n
DECLARE\nnum_errors PLS_INTEGER;\nBEGIN\n  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('USERNAME', 'TABLENAME','TABLENAME_TMP',\n   0, TRUE, FALSE, TRUE, FALSE, num_errors, FALSE, TRUE);\nEND;\n\/<\/code><\/pre>\n
\u6839\u636e\u6e90\u8868DDL\u8bed\u53e5\u624b\u5de5\u521b\u5efa\u7d22\u5f15<\/h1>\n
create index IDX_NAME_TMP on tablename_TMP (#\u5b57\u6bb5\u540d)\n  tablespace USERS\n  pctfree 10\n  initrans 2\n  maxtrans 255\n  storage\n  (\n    initial 64K\n    next 1M\n    minextents 1\n    maxextents unlimited\n  );<\/code><\/pre>\n
\u540c\u6b65\u5728\u7ebf\u5dee\u5f02\u6570\u636e<\/h1>\n
EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('USERNAME', 'TABLENAME', 'TABLENAME_TMP');<\/code><\/pre>\n
\u5b8c\u6210\u5728\u7ebf\u91cd\u5b9a\u4e49<\/h1>\n
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('USERNAME', 'TABLENAME', 'TABLENAME_TMP');<\/code><\/pre>\n
\u91cd\u5efa\u7d22\u5f15\uff08\u5168\u5c40-->\u672c\u5730\uff09<\/h1>\n
create index indexname on tablename local;<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
\u672c\u6587\u4e3b\u8981\u4ecb\u7ecd\u4e86\u4f7f\u7528\u5728\u7ebf\u91cd\u5b9a\u4e49\u5c06\u666e\u901a\u8868\u8f6c\u4e3a\u5206\u533a\u8868\u7684\u64cd<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[18],"tags":[],"class_list":["post-1160","post","type-post","status-publish","format-standard","hentry","category-oracle-11g"],"_links":{"self":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/1160","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1160"}],"version-history":[{"count":1,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/1160\/revisions"}],"predecessor-version":[{"id":1161,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/1160\/revisions\/1161"}],"wp:attachment":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1160"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1160"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1160"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}