--查看分区记录
select * from dba_tab_partitions where table_name='TB_LOAN_ACCOUNT_CON';
--查看分区表下有多少个分区
select table_name,count(*) from dba_tab_partitions group by table_name order by 2 desc;
--我的业务创建分区表 -- 在线重定义
--查看该表是否可以进行在线重定义
execute dbms_redefinition.can_redef_table('SCMS_CON','TB_LOAN_ACCOUNT_CON');
--grant all on SYS.DBMS_REDEFINITION to SCMS_CON;
--grant create any table,alter any table,drop any table,lock any table,select any table to scms_con;
-- Create table
create table TB_LOAN_ACCOUNT_CON_BAK
(
uuid VARCHAR2(32) not null,
party_id VARCHAR2(32),
contract_no VARCHAR2(30),
paper_contract_no VARCHAR2(150),
contract_amt NUMBER(26,8),
currency_cd VARCHAR2(6),
banlence_amt NUMBER(20,2),
con_used_amt NUMBER(20,2),
product_type VARCHAR2(30),
mk_product_type VARCHAR2(30),
cycle_flag VARCHAR2(1),
guaranty_type VARCHAR2(20),
main_guaranty_type VARCHAR2(6),
start_date CHAR(8),
end_date CHAR(8),
sign_term VARCHAR2(10),
term_unit VARCHAR2(3),
sign_date CHAR(8),
sign_address VARCHAR2(200),
sign_way VARCHAR2(3),
print_count VARCHAR2(3),
loan_con_status VARCHAR2(6),
user_num VARCHAR2(20) not null,
org_num VARCHAR2(10),
create_time VARCHAR2(50),
update_time VARCHAR2(50),
del_flag VARCHAR2(1),
trunc_no NUMBER,
data_chan VARCHAR2(3),
bank_code VARCHAR2(16),
conid VARCHAR2(32),
acc_id VARCHAR2(32),
move_flag VARCHAR2(1),
recerve_user VARCHAR2(10),
recerve_org VARCHAR2(10),
fina_support_type VARCHAR2(2),
cus_no VARCHAR2(50),
cus_name VARCHAR2(200),
nft_no VARCHAR2(32),
cont_level_type CHAR(2)
) partition by hash(bank_code) partitions 6 ;
-- Create/Recreate indexes
create index IDX_N_ACCOUNT_CON_CONID on TB_LOAN_ACCOUNT_CON_BAK (CONID);
create index IDX_N_ACCOUNT_CON_PARTY_ID on TB_LOAN_ACCOUNT_CON_BAK (PARTY_ID);
create index IDX_N_ACCOUNT_CON_USER_ORG on TB_LOAN_ACCOUNT_CON_BAK (ORG_NUM, USER_NUM);
create index IND_TB_LOAN_ACCOUNT_CON_ACC_ID on TB_LOAN_ACCOUNT_CON_BAK (ACC_ID);
-- Create/Recreate primary, unique and foreign key constraints
alter table TB_LOAN_ACCOUNT_CON add constraint TB_LOAN_ACCOUNT_CON_BAK primary key (UUID);
--在见表后紧接着应该是建立需要的索引? 暂不确定 建立完成之后也可进行建立索引的操作吧?!
--执行表的在线重定义
execute dbms_redefinition.start_redef_table('SCMS_CON','TB_LOAN_ACCOUNT_CON','TB_LOAN_ACCOUNT_CON_BAK');
--执行中间表和数据源表的数据同步TB_LOAN_ACCOUNT_CON_BAK
execute dbms_redefinition.sync_interim_table('SCMS_CON','TB_LOAN_ACCOUNT_CON','TB_LOAN_ACCOUNT_CON_BAK');
--结束
execute dbms_redefinition.finish_redef_table('SCMS_CON','TB_LOAN_ACCOUNT_CON','TB_LOAN_ACCOUNT_CON_BAK');
记一次Oracle的分区表建立
最新推荐文章于 2024-07-25 07:07:02 发布