{"id":1706,"date":"2020-01-06T14:35:05","date_gmt":"2020-01-06T06:35:05","guid":{"rendered":"http:\/\/www.llku.com\/?p=1706"},"modified":"2020-01-06T14:57:02","modified_gmt":"2020-01-06T06:57:02","slug":"oracle-%e5%9c%a8%e7%ba%bf%e9%87%8d%e5%ae%9a%e4%b9%89%ef%bc%88%e6%99%ae%e9%80%9a%e8%a1%a8%e5%8f%98%e6%9b%b4%e4%b8%ba%e5%88%86%e5%8c%ba%e8%a1%a8%ef%bc%89","status":"publish","type":"post","link":"https:\/\/www.llku.com\/?p=1706","title":{"rendered":"Oracle \u5728\u7ebf\u91cd\u5b9a\u4e49\uff08\u666e\u901a\u8868\u53d8\u66f4\u4e3a\u5206\u533a\u8868\uff09"},"content":{"rendered":"

–\u80cc\u666f\uff1acux_gl_interfacebak\u6570\u636e\u91cf\u8fc7\u5927\uff08cux_gl_interfacebak\u6709\u4e3b\u952e\uff09\uff0c\u9700\u6309accouting_date\u6309\u5e74\u5206\u533a\uff0c\u4ee5\u4e0b\u547d\u4ee4\u76f4\u63a5\u5728command\u7a97\u53e3\u6267\u884c\u5373\u53ef\u3002
\n–1\u3001\u68c0\u67e5\u9700\u8981\u5728\u7ebf\u51b2\u5b9a\u4e49\u7684\u8868\u662f\u5426
\nbegin
\ndbms_redefinition.can_redef_table(‘apps’,’cux_gl_interfacebak’,dbms_redefinition.cons_use_pk);
\nend;
\n\/<\/p>\n

\u6216\u9a8c\u8bc1\u662f\u5426\u53ef\u4ee5\u901a\u8fc7rowid\u65b9\u5f0f\u5b9a\u4e49<\/p>\n

begin
\n–dbms_redefinition.can_redef_table(‘scott’,’tb_cablecheck_equipment_bak’,2);
\ndbms_redefinition.can_redef_table(‘apps’,’cux_gl_interfacebak’,dbms_redefinition.cons_use_rowid);
\nend;
\n\/<\/p>\n

–2\u3001\u521b\u5efa\u4e2d\u95f4\u8868
\ncreate table CUX_GL_INTERFACEBAK_1
\n(
\nsource_batch_id VARCHAR2(50) not null,
\nsource_line_id NUMBER(10),
\nje_group_id VARCHAR2(50) not null,
\nledger_id VARCHAR2(50) not null,
\naccounting_date DATE not null,
\nprocess_date DATE not null,
\nje_category_name VARCHAR2(25) not null,
\nje_source_name VARCHAR2(25) not null,
\ncurrency_code VARCHAR2(15) not null,
\ncurrency_conversion_date DATE,
\ncurrency_conversion_rate NUMBER(38,2),
\ncurrency_conversion_type VARCHAR2(30),
\nentered_dr NUMBER(38,2),
\nentered_cr NUMBER(38,2),
\naccounted_dr NUMBER(38,2),
\naccounted_cr NUMBER(38,2),
\nactual_flag VARCHAR2(25) not null,
\nimport_flag VARCHAR2(1) not null,
\nimport_date VARCHAR2(25),
\ngl_request_id NUMBER(30),
\nerror_message VARCHAR2(255),
\ndoc_seq_num VARCHAR2(100),
\nsegment1 VARCHAR2(25),
\nsegment2 VARCHAR2(25),
\nsegment3 VARCHAR2(25),
\nsegment4 VARCHAR2(25),
\nsegment5 VARCHAR2(25),
\nsegment6 VARCHAR2(25),
\nsegment7 VARCHAR2(25),
\nsegment8 VARCHAR2(25),
\nsegment9 VARCHAR2(25),
\nsegment10 VARCHAR2(25),
\nsegment11 VARCHAR2(25),
\nsegment12 VARCHAR2(25),
\nsegment13 VARCHAR2(25),
\nline_description VARCHAR2(240),
\nattribute1 VARCHAR2(25),
\nattribute2 VARCHAR2(150),
\nattribute3 VARCHAR2(150),
\nattribute4 VARCHAR2(150),
\nattribute5 VARCHAR2(150),
\nattribute6 VARCHAR2(150),
\nattribute7 VARCHAR2(150),
\nattribute8 VARCHAR2(150),
\nattribute9 VARCHAR2(150),
\nattribute10 VARCHAR2(150),
\nattribute11 VARCHAR2(150),
\nattribute12 VARCHAR2(150),
\nattribute13 VARCHAR2(150),
\nattribute14 VARCHAR2(150),
\nattribute15 VARCHAR2(150),
\nsource_key_id NUMBER(10) not null
\n)<\/p>\n

partition by range(accounting_date)(
\nPARTITION tb_cablecheck_equipment_p1 VALUES LESS THAN (TO_DATE(‘2017-01-01′,’YYYY-MM-DD’)),
\nPARTITION tb_cablecheck_equipment_p2 VALUES LESS THAN(TO_DATE(‘2018-01-01’, ‘YYYY-MM-DD’)),
\nPARTITION tb_cablecheck_equipment_p3 VALUES LESS THAN(TO_DATE(‘2019-01-01’, ‘YYYY-MM-DD’)),
\nPARTITION tb_cablecheck_equipment_p4 VALUES LESS THAN(TO_DATE(‘2020-01-01’, ‘YYYY-MM-DD’)),
\nPARTITION tb_cablecheck_equipment_p5 VALUES LESS THAN(TO_DATE(‘2021-01-01’, ‘YYYY-MM-DD’)),
\nPARTITION tb_cablecheck_equipment_p6 VALUES LESS THAN(MAXVALUE)
\n);<\/p>\n

–3\u3001\u8fdb\u884c\u51b2\u5b9a\u4e49\u547d\u4ee4
\nbegin
\ndbms_redefinition.start_redef_table(‘apps’,’CUX_GL_INTERFACEBAK’,’CUX_GL_INTERFACEBAK_1′,null,2);
\nend;
\n\/<\/p>\n

–4\u3001\u590d\u5236\u4f9d\u8d56\u5bf9\u8c61
\ndeclare
\nnum_errors pls_integer;
\nbegin
\ndbms_redefinition.copy_table_dependents(‘apps’, ‘CUX_GL_INTERFACEBAK’,’CUX_GL_INTERFACEBAK_1′,
\ndbms_redefinition.cons_orig_params, true, true, true, true, num_errors);
\nend;
\n\/<\/p>\n

–5\u3001\u540c\u6b65\u4e2d\u95f4\u8868\uff0c\u4fdd\u8bc1\u6570\u636e\u7684\u4e00\u81f4\u6027
\nbegin
\ndbms_redefinition.sync_interim_table(‘apps’,’CUX_GL_INTERFACEBAK’,’CUX_GL_INTERFACEBAK_1′);
\nend;
\n\/<\/p>\n

–6\u3001\u5b8c\u6210\u91cd\u5b9a\u4e49\u547d\u4ee4
\nbegin
\ndbms_redefinition.finish_redef_table(‘apps’,’CUX_GL_INTERFACEBAK’,’CUX_GL_INTERFACEBAK_1′);
\nend;
\n\/
\n–7\u3001\u9a8c\u8bc1\u51b2\u5b9a\u4e49\u662f\u5426\u6b63\u5e38
\nselect * from CUX_GL_INTERFACEBAK partition(tb_cablecheck_equipment_p4);<\/p>\n

select *
\nfrom cux_gl_interfacebak partition(tb_cablecheck_equipment_p3)
\nwhere 1 = 1
\nand segment3 = ‘6031010101’<\/p>\n

–8\u3001\u5220\u9664\u8868
\ndrop table apps.CUX_GL_INTERFACEBAK_1;<\/p>\n","protected":false},"excerpt":{"rendered":"