之前做的一个实验，将非分区表转化成分区表，楼主可以参考下。
方案3. 将原表进行重定义，变成分区表。
SQL> exec DBMS_REDEFINITION.can_REDEF_TABLE('TEST','CUSTOMER_CONSOLIDATED2',1);-- 首选验证表是否可以进行REDEFINITION
PL/SQL 过程已成功完成。
已用时间: 00: 00: 00.25
--建立一张表，结构和原表相同，这里因为原表字段过多，不完全展现创建表的过程
CREATE TABLE TEST.t3_p_tmp (
business_day_locale NUMBER(8) not null,
business_day_system NUMBER(8) not null,
business_start_locale VARCHAR2(10) not null,
business_start_system VARCHAR2(10) not null,
operator_id NUMBER not null,
customer_id NUMBER not null,
currency_id NUMBER(3) not null,
cash NUMBER default 0 not null,
non_cash NUMBER default 0 not null,
credit_limit NUMBER default 0 not null,
agency_cash_bal NUMBER default 0 not null,
used_daily_cc_deposit_limit NUMBER default 0 not null,
used_monthly_cc_deposit_limit NUMBER default 0 not null,
first_deposit_date DATE,
first_cc_deposit_date DATE,
last_cc_deposit_date DATE,
today_stakes NUMBER default 0 not null,
today_payout NUMBER default 0 not null,
today_deposit NUMBER default 0 not null,
today_withdrawal NUMBER default 0 not null,
……………………
)
partition by range(business_day_system)(
partition t3_201206 values less than(20120630),
partition t3_201207 values less than(20120731),
partition t3_201208 values less than(20120831),
partition t3_201209 values less than(20120930),
partition t3_201210 values less than(20121031),
partition t3_201211 values less than(20121130),
partition t3_201212 values less than(20121231),
partition t3_201301 values less than(20130131),
partition t3_201302 values less than(20130228)
);-- 创建中间转换需要的临时分区表,表的字段比较多，以 business_day_system 字段来作为时间标示
--0.594s
test/test
begin
dbms_redefinition.start_redef_table('TEST','CUSTOMER_CONSOLIDATED2','T3_P_TMP',
'BUSINESS_DAY_LOCALE BUSINESS_DAY_LOCALE,
BUSINESS_DAY_SYSTEM BUSINESS_DAY_SYSTEM,
BUSINESS_START_LOCALE BUSINESS_START_LOCALE,
BUSINESS_START_SYSTEM BUSINESS_START_SYSTEM,
OPERATOR_ID OPERATOR_ID,
CUSTOMER_ID CUSTOMER_ID,
CURRENCY_ID CURRENCY_ID,
CASH CASH,
NON_CASH NON_CASH,
CREDIT_LIMIT CREDIT_LIMIT,
AGENCY_CASH_BAL AGENCY_CASH_BAL,
USED_DAILY_CC_DEPOSIT_LIMIT USED_DAILY_CC_DEPOSIT_LIMIT,
USED_MONTHLY_CC_DEPOSIT_LIMIT USED_MONTHLY_CC_DEPOSIT_LIMIT,
FIRST_DEPOSIT_DATE FIRST_DEPOSIT_DATE,
FIRST_CC_DEPOSIT_DATE FIRST_CC_DEPOSIT_DATE,
LAST_CC_DEPOSIT_DATE LAST_CC_DEPOSIT_DATE,
TODAY_STAKES TODAY_STAKES,
TODAY_PAYOUT TODAY_PAYOUT,
TODAY_DEPOSIT TODAY_DEPOSIT,
TODAY_WITHDRAWAL TODAY_WITHDRAWAL,
TODAY_TOTAL_BONUS TODAY_TOTAL_BONUS,
ACCUMULATED_STAKES ACCUMULATED_STAKES,
ACCUMULATED_UNSETTLED_STAKES ACCUMULATED_UNSETTLED_STAKES,
ACCUMULATED_PAYOUT ACCUMULATED_PAYOUT,
SUB_AGENT_ID SUB_AGENT_ID,
MAIN_AGENT_ID MAIN_AGENT_ID,
PARTNER_ID PARTNER_ID,
TODAY_MANUAL_ADJ TODAY_MANUAL_ADJ,
ACCUMULATED_STAKES_BASED ACCUMULATED_STAKES_BASED,
ACCUMULATED_UNSETTLED_BASED ACCUMULATED_UNSETTLED_BASED,
ACCUMULATED_PAYOUT_BASED ACCUMULATED_PAYOUT_BASED,
ACCUMULATED_DEPOSIT_BASED ACCUMULATED_DEPOSIT_BASED,
ACCUMULATED_WITHDRAWAL_BASED ACCUMULATED_WITHDRAWAL_BASED,
ACCUMULATED_MANUAL_ADJ_BASED ACCUMULATED_MANUAL_ADJ_BASED,
ACCUMULATED_MANUAL_ADJ ACCUMULATED_MANUAL_ADJ,
ACCUMULATED_DEPOSIT ACCUMULATED_DEPOSIT,
ACCUMULATED_WITHDRAWAL ACCUMULATED_WITHDRAWAL,
FIRST_DEPOSIT_ID FIRST_DEPOSIT_ID,
FIRST_CC_DEPOSIT_ID FIRST_CC_DEPOSIT_ID,
LAST_CC_DEPOSIT_ID LAST_CC_DEPOSIT_ID,
LAST_BET_PLACED_DATE LAST_BET_PLACED_DATE,
TODAY_STAKES_BASED TODAY_STAKES_BASED,
TODAY_PAYOUT_BASED TODAY_PAYOUT_BASED,
TODAY_DEPOSIT_BASED TODAY_DEPOSIT_BASED,
TODAY_WITHDRAWAL_BASED TODAY_WITHDRAWAL_BASED,
TODAY_MANUAL_ADJ_BASED TODAY_MANUAL_ADJ_BASED,
TODAY_SETTLED_STAKES TODAY_SETTLED_STAKES,
TODAY_SETTLED_STAKES_BASED TODAY_SETTLED_STAKES_BASED,
TODAY_STAKES_CASINO TODAY_STAKES_CASINO,
TODAY_STAKES_CASINO_BASED TODAY_STAKES_CASINO_BASED,
TODAY_STAKES_CASINO_BASED2 TODAY_STAKES_CASINO_BASED2,
ACCU_STAKES_CASINO ACCU_STAKES_CASINO,
ACCU_STAKES_CASINO_BASED ACCU_STAKES_CASINO_BASED,
ACCU_STAKES_CASINO_BASED2 ACCU_STAKES_CASINO_BASED2,
TODAY_PAYOUT_CASINO TODAY_PAYOUT_CASINO,
TODAY_PAYOUT_CASINO_BASED TODAY_PAYOUT_CASINO_BASED,
TODAY_PAYOUT_CASINO_BASED2 TODAY_PAYOUT_CASINO_BASED2,
ACCU_PAYOUT_CASINO ACCU_PAYOUT_CASINO,
TODAY_TXFR_IN_CASINO TODAY_TXFR_IN_CASINO,
TODAY_TXFR_IN_CASINO_BASED TODAY_TXFR_IN_CASINO_BASED,
TODAY_TXFR_IN_CASINO_BASED2 TODAY_TXFR_IN_CASINO_BASED2,
ACCU_TXFR_IN_CASINO ACCU_TXFR_IN_CASINO,
ACCU_TXFR_IN_CASINO_BASED ACCU_TXFR_IN_CASINO_BASED,
ACCU_TXFR_IN_CASINO_BASED2 ACCU_TXFR_IN_CASINO_BASED2,
TODAY_TXFR_OUT_CASINO TODAY_TXFR_OUT_CASINO,
TODAY_TXFR_OUT_CASINO_BASED TODAY_TXFR_OUT_CASINO_BASED,
TODAY_TXFR_OUT_CASINO_BASED2 TODAY_TXFR_OUT_CASINO_BASED2,
ACCU_TXFR_OUT_CASINO ACCU_TXFR_OUT_CASINO,
ACCU_TXFR_OUT_CASINO_BASED ACCU_TXFR_OUT_CASINO_BASED,
ACCU_TXFR_OUT_CASINO_BASED2 ACCU_TXFR_OUT_CASINO_BASED2,
TODAY_BAR_ROLLING TODAY_BAR_ROLLING,
TODAY_BAR_ROLLING_BASED TODAY_BAR_ROLLING_BASED,
TODAY_BAR_ROLLING_BASED2 TODAY_BAR_ROLLING_BASED2,
BAR_ROLLING_BAL BAR_ROLLING_BAL,
PLAY_FOR_FUN PLAY_FOR_FUN,
ACCU_PAYOUT_CASINO_BASED ACCU_PAYOUT_CASINO_BASED,
ACCU_PAYOUT_CASINO_BASED2 ACCU_PAYOUT_CASINO_BASED2,
CURRENCY_ID2 CURRENCY_ID2,
TODAY_SETTLED_STAKES_BASED2 TODAY_SETTLED_STAKES_BASED2,
TODAY_MANUAL_ADJ_BASED2 TODAY_MANUAL_ADJ_BASED2,
TODAY_WITHDRAWAL_BASED2 TODAY_WITHDRAWAL_BASED2,
TODAY_DEPOSIT_BASED2 TODAY_DEPOSIT_BASED2,
TODAY_STAKES_BASED2 TODAY_STAKES_BASED2,
TODAY_PAYOUT_BASED2 TODAY_PAYOUT_BASED2,
ACCUMULATED_STAKES_BASED2 ACCUMULATED_STAKES_BASED2,
ACCUMULATED_UNSETTLED_BASED2 ACCUMULATED_UNSETTLED_BASED2,
ACCUMULATED_PAYOUT_BASED2 ACCUMULATED_PAYOUT_BASED2,
ACCUMULATED_DEPOSIT_BASED2 ACCUMULATED_DEPOSIT_BASED2,
ACCUMULATED_WITHDRAWAL_BASED2 ACCUMULATED_WITHDRAWAL_BASED2,
ACCUMULATED_MANUAL_ADJ_BASED2 ACCUMULATED_MANUAL_ADJ_BASED2,
TODAY_AGENCY_TXFR_IN TODAY_AGENCY_TXFR_IN,
TODAY_AGENCY_TXFR_IN_BASED TODAY_AGENCY_TXFR_IN_BASED,
TODAY_AGENCY_TXFR_IN_BASED2 TODAY_AGENCY_TXFR_IN_BASED2,
ACCUMULATED_AGENCY_TXFR_IN ACCUMULATED_AGENCY_TXFR_IN,
ACCU_AGENCY_TXFR_IN_BASED ACCU_AGENCY_TXFR_IN_BASED,
ACCU_AGENCY_TXFR_IN_BASED2 ACCU_AGENCY_TXFR_IN_BASED2,
TODAY_BAR_REDEEMED TODAY_BAR_REDEEMED,
TODAY_BAR_REDEEMED_BASED TODAY_BAR_REDEEMED_BASED,
ACCU_BAR_REDEEMED ACCU_BAR_REDEEMED,
ACCU_BAR_REDEEMED_BASED ACCU_BAR_REDEEMED_BASED,
ACCU_BAR_REDEEMED_BASED2 ACCU_BAR_REDEEMED_BASED2,
TODAY_BAR_REDEEMED_BASED2 TODAY_BAR_REDEEMED_BASED2,
ACCU_BAR_ROLLING_BAL ACCU_BAR_ROLLING_BAL,
ACCU_BAR_ROLLING_BAL_BASED ACCU_BAR_ROLLING_BAL_BASED,
ACCU_BAR_ROLLING_BAL_BASED2 ACCU_BAR_ROLLING_BAL_BASED2,
TODAY_STAKES_LF TODAY_STAKES_LF,
TODAY_STAKES_LF_BASED TODAY_STAKES_LF_BASED,
TODAY_STAKES_LF_BASED2 TODAY_STAKES_LF_BASED2,
ACCU_STAKES_LF ACCU_STAKES_LF,
ACCU_STAKES_LF_BASED ACCU_STAKES_LF_BASED,
ACCU_STAKES_LF_BASED2 ACCU_STAKES_LF_BASED2,
TODAY_PAYOUT_LF TODAY_PAYOUT_LF,
TODAY_PAYOUT_LF_BASED TODAY_PAYOUT_LF_BASED,
TODAY_PAYOUT_LF_BASED2 TODAY_PAYOUT_LF_BASED2,
ACCU_PAYOUT_LF ACCU_PAYOUT_LF,
ACCU_PAYOUT_LF_BASED ACCU_PAYOUT_LF_BASED,
ACCU_PAYOUT_LF_BASED2 ACCU_PAYOUT_LF_BASED2,
TODAY_TXFR_IN_LF TODAY_TXFR_IN_LF,
TODAY_TXFR_IN_LF_BASED TODAY_TXFR_IN_LF_BASED,
TODAY_TXFR_IN_LF_BASED2 TODAY_TXFR_IN_LF_BASED2,
ACCU_TXFR_IN_LF ACCU_TXFR_IN_LF,
ACCU_TXFR_IN_LF_BASED ACCU_TXFR_IN_LF_BASED,
ACCU_TXFR_IN_LF_BASED2 ACCU_TXFR_IN_LF_BASED2,
TODAY_TXFR_OUT_LF TODAY_TXFR_OUT_LF,
TODAY_TXFR_OUT_LF_BASED TODAY_TXFR_OUT_LF_BASED,
TODAY_TXFR_OUT_LF_BASED2 TODAY_TXFR_OUT_LF_BASED2,
ACCU_TXFR_OUT_LF ACCU_TXFR_OUT_LF,
ACCU_TXFR_OUT_LF_BASED ACCU_TXFR_OUT_LF_BASED,
ACCU_TXFR_OUT_LF_BASED2 ACCU_TXFR_OUT_LF_BASED2,
TODAY_BAR_ROLL_LF TODAY_BAR_ROLL_LF,
TODAY_BAR_ROLL_LF_BASED TODAY_BAR_ROLL_LF_BASED,
TODAY_BAR_ROLL_LF_BASED2 TODAY_BAR_ROLL_LF_BASED2,
ACCU_BAR_ROLL_BAL_LF ACCU_BAR_ROLL_BAL_LF,
ACCU_BAR_ROLL_BAL_LF_BASED ACCU_BAR_ROLL_BAL_LF_BASED,
ACCU_BAR_ROLL_BAL_LF_BASED2 ACCU_BAR_ROLL_BAL_LF_BASED2,
REBATE_AMT REBATE_AMT,
REBATE_AMT_BASED REBATE_AMT_BASED,
REBATE_AMT_BASED2 REBATE_AMT_BASED2,
TDY_REBATE_AMT TDY_REBATE_AMT,
TDY_REBATE_AMT_BASED TDY_REBATE_AMT_BASED,
TDY_REBATE_AMT_BASED2 TDY_REBATE_AMT_BASED2,
REBATE_AMT_LF REBATE_AMT_LF,
REBATE_AMT_LF_BASED REBATE_AMT_LF_BASED,
REBATE_AMT_LF_BASED2 REBATE_AMT_LF_BASED2,
TDY_REBATE_LF_AMT TDY_REBATE_LF_AMT,
TDY_REBATE_LF_AMT_BASED TDY_REBATE_LF_AMT_BASED,
TDY_REBATE_LF_AMT_BASED2 TDY_REBATE_LF_AMT_BASED2,
REBATE_AMT_CAS REBATE_AMT_CAS,
REBATE_AMT_CAS_BASED REBATE_AMT_CAS_BASED,
REBATE_AMT_CAS_BASED2 REBATE_AMT_CAS_BASED2,
TDY_REBATE_CAS_AMT TDY_REBATE_CAS_AMT,
TDY_REBATE_CAS_AMT_BASED TDY_REBATE_CAS_AMT_BASED,
TDY_REBATE_CAS_AMT_BASED2 TDY_REBATE_CAS_AMT_BASED2,
ALLOW_CL_NEGATIVE_FLAG ALLOW_CL_NEGATIVE_FLAG,
DAY_TXFR_USED_LF_LIMIT DAY_TXFR_USED_LF_LIMIT,
DAY_TXFR_USED_CAS_LIMIT DAY_TXFR_USED_CAS_LIMIT,
DAY_TXFR_USED_LF_LIMIT_BASED DAY_TXFR_USED_LF_LIMIT_BASED,
DAY_TXFR_USED_LF_LIMIT_BASED2 DAY_TXFR_USED_LF_LIMIT_BASED2,
DAY_TXFR_USED_CAS_LIMIT_BASED DAY_TXFR_USED_CAS_LIMIT_BASED,
DAY_TXFR_USED_CAS_LIMIT_BASED2 DAY_TXFR_USED_CAS_LIMIT_BASED2,
SET_PERIOD_STAKES SET_PERIOD_STAKES,
SET_PERIOD_STAKES_CAS SET_PERIOD_STAKES_CAS,
SET_PERIOD_STAKES_LF SET_PERIOD_STAKES_LF,
SET_PERIOD_WIN SET_PERIOD_WIN,
SET_PERIOD_WIN_CAS SET_PERIOD_WIN_CAS,
SET_PERIOD_WIN_LF SET_PERIOD_WIN_LF,
SET_PERIOD_REBATE SET_PERIOD_REBATE,
SET_PERIOD_REBATE_CAS SET_PERIOD_REBATE_CAS,
SET_PERIOD_REBATE_LF SET_PERIOD_REBATE_LF,
SET_PERIOD_CL_TXFR SET_PERIOD_CL_TXFR,
SET_PERIOD_TXFR_IN_CAS SET_PERIOD_TXFR_IN_CAS,
SET_PERIOD_TXFR_OUT_CAS SET_PERIOD_TXFR_OUT_CAS,
SET_PERIOD_TXFR_IN_LF SET_PERIOD_TXFR_IN_LF,
SET_PERIOD_TXFR_OUT_LF SET_PERIOD_TXFR_OUT_LF,
CREDIT_RESET_FLAG CREDIT_RESET_FLAG,
CUSTOMER_TYPE CUSTOMER_TYPE,
CREDIT_LIMIT_ASSIGNED CREDIT_LIMIT_ASSIGNED,
CREDIT_LIMIT_REMAINING CREDIT_LIMIT_REMAINING,
CREDIT_LIMIT_USED CREDIT_LIMIT_USED,
TELEBET_PIN TELEBET_PIN,
REMARKS REMARKS,
LOSS_REBATE_COMM LOSS_REBATE_COMM,
LOSS_REBATE_COMM_EFF LOSS_REBATE_COMM_EFF,
PARENT_TYPE PARENT_TYPE,
AGENT_POSITION AGENT_POSITION,
EXCHANGE_RATE EXCHANGE_RATE,
EXCHANGE_RATE2 EXCHANGE_RATE2,
TRANSACTION_DATE TRANSACTION_DATE,
TOTAL_BONUS TOTAL_BONUS,
TODAY_ADJ TODAY_ADJ,
TODAY_ADJ_BASED TODAY_ADJ_BASED,
TODAY_ADJ_BASED2 TODAY_ADJ_BASED2,
CURRENCY_ID_P CURRENCY_ID_P,
CURRENCY_ID_MA CURRENCY_ID_MA,
CURRENCY_ID_SA CURRENCY_ID_SA,
EXCHANGE_RATE_P EXCHANGE_RATE_P,
EXCHANGE_RATE_MA EXCHANGE_RATE_MA,
EXCHANGE_RATE_SA EXCHANGE_RATE_SA,
POSITION_AH_OU_SUB_AGENT POSITION_AH_OU_SUB_AGENT,
POSITION_AH_OU_MAIN_AGENT POSITION_AH_OU_MAIN_AGENT,
POSITION_AH_OU_PARTNER POSITION_AH_OU_PARTNER,
POSITION_AH_OU_OPERATOR POSITION_AH_OU_OPERATOR,
POSITION_PROD_SUB_AGENT POSITION_PROD_SUB_AGENT,
POSITION_PROD_MAIN_AGENT POSITION_PROD_MAIN_AGENT,
POSITION_PROD_PARTNER POSITION_PROD_PARTNER,
POSITION_PROD_OPERATOR POSITION_PROD_OPERATOR,
POSITION_RNG_SUB_AGENT POSITION_RNG_SUB_AGENT,
POSITION_RNG_MAIN_AGENT POSITION_RNG_MAIN_AGENT,
POSITION_RNG_PARTNER POSITION_RNG_PARTNER,
POSITION_RNG_OPERATOR POSITION_RNG_OPERATOR,
POSITION_LD_SUB_AGENT POSITION_LD_SUB_AGENT,
POSITION_LD_MAIN_AGENT POSITION_LD_MAIN_AGENT,
POSITION_LD_PARTNER POSITION_LD_PARTNER,
POSITION_LD_OPERATOR POSITION_LD_OPERATOR,
COMM_AH_OU_SUB_AGENT COMM_AH_OU_SUB_AGENT,
COMM_AH_OU_MAIN_AGENT COMM_AH_OU_MAIN_AGENT,
COMM_AH_OU_PARTNER COMM_AH_OU_PARTNER,
COMM_PROD_SUB_AGENT COMM_PROD_SUB_AGENT,
COMM_PROD_MAIN_AGENT COMM_PROD_MAIN_AGENT,
COMM_PROD_PARTNER COMM_PROD_PARTNER,
COMM_RNG_SUB_AGENT COMM_RNG_SUB_AGENT,
COMM_RNG_MAIN_AGENT COMM_RNG_MAIN_AGENT,
COMM_RNG_PARTNER COMM_RNG_PARTNER,
COMM_LD_SUB_AGENT COMM_LD_SUB_AGENT,
COMM_LD_MAIN_AGENT COMM_LD_MAIN_AGENT,
COMM_LD_PARTNER COMM_LD_PARTNER,
COMM_AH_OU_CUSTOMER COMM_AH_OU_CUSTOMER,
COMM_PROD_CUSTOMER COMM_PROD_CUSTOMER,
COMM_RNG_CUSTOMER COMM_RNG_CUSTOMER,
COMM_LD_CUSTOMER COMM_LD_CUSTOMER,
TDY_STAKES_AGENT TDY_STAKES_AGENT,
TDY_STAKES_AGENT_BASED TDY_STAKES_AGENT_BASED,
TDY_STAKES_AGENT_CAS TDY_STAKES_AGENT_CAS,
TDY_STAKES_AGENT_CAS_BASED TDY_STAKES_AGENT_CAS_BASED,
TDY_STAKES_AGENT_LD TDY_STAKES_AGENT_LD,
TDY_STAKES_AGENT_LD_BASED TDY_STAKES_AGENT_LD_BASED,
TDY_WIN_AGENT TDY_WIN_AGENT,
TDY_WIN_AGENT_BASED TDY_WIN_AGENT_BASED,
TDY_WIN_AGENT_CAS TDY_WIN_AGENT_CAS,
TDY_WIN_AGENT_CAS_BASED TDY_WIN_AGENT_CAS_BASED,
TDY_WIN_AGENT_LD TDY_WIN_AGENT_LD,
TDY_WIN_AGENT_LD_BASED TDY_WIN_AGENT_LD_BASED,
TDY_COMM_AGENT TDY_COMM_AGENT,
TDY_COMM_AGENT_BASED TDY_COMM_AGENT_BASED,
TDY_COMM_AGENT_CAS TDY_COMM_AGENT_CAS,
TDY_COMM_AGENT_CAS_BASED TDY_COMM_AGENT_CAS_BASED,
TDY_COMM_AGENT_LD TDY_COMM_AGENT_LD,
TDY_COMM_AGENT_LD_BASED TDY_COMM_AGENT_LD_BASED,
ACU_STAKES_AGENT ACU_STAKES_AGENT,
ACU_STAKES_AGENT_BASED ACU_STAKES_AGENT_BASED,
ACU_STAKES_AGENT_CAS ACU_STAKES_AGENT_CAS,
ACU_STAKES_AGENT_CAS_BASED ACU_STAKES_AGENT_CAS_BASED,
ACU_STAKES_AGENT_LD ACU_STAKES_AGENT_LD,
ACU_STAKES_AGENT_LD_BASED ACU_STAKES_AGENT_LD_BASED,
ACU_WIN_AGENT ACU_WIN_AGENT,
ACU_WIN_AGENT_BASED ACU_WIN_AGENT_BASED,
ACU_WIN_AGENT_CAS ACU_WIN_AGENT_CAS,
ACU_WIN_AGENT_CAS_BASED ACU_WIN_AGENT_CAS_BASED,
ACU_WIN_AGENT_LD ACU_WIN_AGENT_LD,
ACU_WIN_AGENT_LD_BASED ACU_WIN_AGENT_LD_BASED,
ACU_COMM_AGENT ACU_COMM_AGENT,
ACU_COMM_AGENT_BASED ACU_COMM_AGENT_BASED,
ACU_COMM_AGENT_CAS ACU_COMM_AGENT_CAS,
ACU_COMM_AGENT_CAS_BASED ACU_COMM_AGENT_CAS_BASED,
ACU_COMM_AGENT_LD ACU_COMM_AGENT_LD,
ACU_COMM_AGENT_LD_BASED ACU_COMM_AGENT_LD_BASED,
TDY_STAKES_MAGENT TDY_STAKES_MAGENT,
TDY_STAKES_MAGENT_BASED TDY_STAKES_MAGENT_BASED,
TDY_STAKES_MAGENT_CAS TDY_STAKES_MAGENT_CAS,
TDY_STAKES_MAGENT_CAS_BASED TDY_STAKES_MAGENT_CAS_BASED,
TDY_STAKES_MAGENT_LD TDY_STAKES_MAGENT_LD,
TDY_STAKES_MAGENT_LD_BASED TDY_STAKES_MAGENT_LD_BASED,
TDY_WIN_MAGENT TDY_WIN_MAGENT,
TDY_WIN_MAGENT_BASED TDY_WIN_MAGENT_BASED,
TDY_WIN_MAGENT_CAS TDY_WIN_MAGENT_CAS,
TDY_WIN_MAGENT_CAS_BASED TDY_WIN_MAGENT_CAS_BASED,
TDY_WIN_MAGENT_LD TDY_WIN_MAGENT_LD,
TDY_WIN_MAGENT_LD_BASED TDY_WIN_MAGENT_LD_BASED,
TDY_COMM_MAGENT TDY_COMM_MAGENT,
TDY_COMM_MAGENT_BASED TDY_COMM_MAGENT_BASED,
TDY_COMM_MAGENT_CAS TDY_COMM_MAGENT_CAS,
TDY_COMM_MAGENT_CAS_BASED TDY_COMM_MAGENT_CAS_BASED,
TDY_COMM_MAGENT_LD TDY_COMM_MAGENT_LD,
TDY_COMM_MAGENT_LD_BASED TDY_COMM_MAGENT_LD_BASED,
ACU_STAKES_MAGENT ACU_STAKES_MAGENT,
ACU_STAKES_MAGENT_BASED ACU_STAKES_MAGENT_BASED,
ACU_STAKES_MAGENT_CAS ACU_STAKES_MAGENT_CAS,
ACU_STAKES_MAGENT_CAS_BASED ACU_STAKES_MAGENT_CAS_BASED,
ACU_STAKES_MAGENT_LD ACU_STAKES_MAGENT_LD,
ACU_STAKES_MAGENT_LD_BASED ACU_STAKES_MAGENT_LD_BASED,
ACU_WIN_MAGENT ACU_WIN_MAGENT,
ACU_WIN_MAGENT_BASED ACU_WIN_MAGENT_BASED,
ACU_WIN_MAGENT_CAS ACU_WIN_MAGENT_CAS,
ACU_WIN_MAGENT_CAS_BASED ACU_WIN_MAGENT_CAS_BASED,
ACU_WIN_MAGENT_LD ACU_WIN_MAGENT_LD,
ACU_WIN_MAGENT_LD_BASED ACU_WIN_MAGENT_LD_BASED,
ACU_COMM_MAGENT ACU_COMM_MAGENT,
ACU_COMM_MAGENT_BASED ACU_COMM_MAGENT_BASED,
ACU_COMM_MAGENT_CAS ACU_COMM_MAGENT_CAS,
ACU_COMM_MAGENT_CAS_BASED ACU_COMM_MAGENT_CAS_BASED,
ACU_COMM_MAGENT_LD ACU_COMM_MAGENT_LD,
ACU_COMM_MAGENT_LD_BASED ACU_COMM_MAGENT_LD_BASED,
TDY_STAKES_PARTNER TDY_STAKES_PARTNER,
TDY_STAKES_PARTNER_BASED TDY_STAKES_PARTNER_BASED,
TDY_STAKES_PARTNER_CAS TDY_STAKES_PARTNER_CAS,
TDY_STAKES_PARTNER_CAS_BASED TDY_STAKES_PARTNER_CAS_BASED,
TDY_STAKES_PARTNER_LD TDY_STAKES_PARTNER_LD,
TDY_STAKES_PARTNER_LD_BASED TDY_STAKES_PARTNER_LD_BASED,
TDY_WIN_PARTNER TDY_WIN_PARTNER,
TDY_WIN_PARTNER_BASED TDY_WIN_PARTNER_BASED,
TDY_WIN_PARTNER_CAS TDY_WIN_PARTNER_CAS,
TDY_WIN_PARTNER_CAS_BASED TDY_WIN_PARTNER_CAS_BASED,
TDY_WIN_PARTNER_LD TDY_WIN_PARTNER_LD,
TDY_WIN_PARTNER_LD_BASED TDY_WIN_PARTNER_LD_BASED,
TDY_COMM_PARTNER TDY_COMM_PARTNER,
TDY_COMM_PARTNER_BASED TDY_COMM_PARTNER_BASED,
TDY_COMM_PARTNER_CAS TDY_COMM_PARTNER_CAS,
TDY_COMM_PARTNER_CAS_BASED TDY_COMM_PARTNER_CAS_BASED,
TDY_COMM_PARTNER_LD TDY_COMM_PARTNER_LD,
TDY_COMM_PARTNER_LD_BASED TDY_COMM_PARTNER_LD_BASED,
ACU_STAKES_PARTNER ACU_STAKES_PARTNER,
ACU_STAKES_PARTNER_BASED ACU_STAKES_PARTNER_BASED,
ACU_STAKES_PARTNER_CAS ACU_STAKES_PARTNER_CAS,
ACU_STAKES_PARTNER_CAS_BASED ACU_STAKES_PARTNER_CAS_BASED,
ACU_STAKES_PARTNER_LD ACU_STAKES_PARTNER_LD,
ACU_STAKES_PARTNER_LD_BASED ACU_STAKES_PARTNER_LD_BASED,
ACU_WIN_PARTNER ACU_WIN_PARTNER,
ACU_WIN_PARTNER_BASED ACU_WIN_PARTNER_BASED,
ACU_WIN_PARTNER_CAS ACU_WIN_PARTNER_CAS,
ACU_WIN_PARTNER_CAS_BASED ACU_WIN_PARTNER_CAS_BASED,
ACU_WIN_PARTNER_LD ACU_WIN_PARTNER_LD,
ACU_WIN_PARTNER_LD_BASED ACU_WIN_PARTNER_LD_BASED,
ACU_COMM_PARTNER ACU_COMM_PARTNER,
ACU_COMM_PARTNER_BASED ACU_COMM_PARTNER_BASED,
ACU_COMM_PARTNER_CAS ACU_COMM_PARTNER_CAS,
ACU_COMM_PARTNER_CAS_BASED ACU_COMM_PARTNER_CAS_BASED,
ACU_COMM_PARTNER_LD ACU_COMM_PARTNER_LD,
ACU_COMM_PARTNER_LD_BASED ACU_COMM_PARTNER_LD_BASED,
LOGIN_ID LOGIN_ID,
TDY_STAKES_OPR TDY_STAKES_OPR,
TDY_STAKES_OPR_BASED TDY_STAKES_OPR_BASED,
TDY_STAKES_OPR_CAS TDY_STAKES_OPR_CAS,
TDY_STAKES_OPR_CAS_BASED TDY_STAKES_OPR_CAS_BASED,
TDY_STAKES_OPR_LD TDY_STAKES_OPR_LD,
TDY_STAKES_OPR_LD_BASED TDY_STAKES_OPR_LD_BASED,
TDY_WIN_OPR TDY_WIN_OPR,
TDY_WIN_OPR_BASED TDY_WIN_OPR_BASED,
TDY_WIN_OPR_CAS TDY_WIN_OPR_CAS,
TDY_WIN_OPR_CAS_BASED TDY_WIN_OPR_CAS_BASED,
TDY_WIN_OPR_LD TDY_WIN_OPR_LD,
TDY_WIN_OPR_LD_BASED TDY_WIN_OPR_LD_BASED,
TDY_COMM_OPR TDY_COMM_OPR,
TDY_COMM_OPR_BASED TDY_COMM_OPR_BASED,
TDY_COMM_OPR_CAS TDY_COMM_OPR_CAS,
TDY_COMM_OPR_CAS_BASED TDY_COMM_OPR_CAS_BASED,
TDY_COMM_OPR_LD TDY_COMM_OPR_LD,
TDY_COMM_OPR_LD_BASED TDY_COMM_OPR_LD_BASED,
ACU_STAKES_OPR ACU_STAKES_OPR,
ACU_STAKES_OPR_BASED ACU_STAKES_OPR_BASED,
ACU_STAKES_OPR_CAS ACU_STAKES_OPR_CAS,
ACU_STAKES_OPR_CAS_BASED ACU_STAKES_OPR_CAS_BASED,
ACU_STAKES_OPR_LD ACU_STAKES_OPR_LD,
ACU_STAKES_OPR_LD_BASED ACU_STAKES_OPR_LD_BASED,
ACU_WIN_OPR ACU_WIN_OPR,
ACU_WIN_OPR_BASED ACU_WIN_OPR_BASED,
ACU_WIN_OPR_CAS ACU_WIN_OPR_CAS,
ACU_WIN_OPR_CAS_BASED ACU_WIN_OPR_CAS_BASED,
ACU_WIN_OPR_LD ACU_WIN_OPR_LD,
ACU_WIN_OPR_LD_BASED ACU_WIN_OPR_LD_BASED,
ACU_COMM_OPR ACU_COMM_OPR,
ACU_COMM_OPR_BASED ACU_COMM_OPR_BASED,
ACU_COMM_OPR_CAS ACU_COMM_OPR_CAS,
ACU_COMM_OPR_CAS_BASED ACU_COMM_OPR_CAS_BASED,
ACU_COMM_OPR_LD ACU_COMM_OPR_LD,
ACU_COMM_OPR_LD_BASED ACU_COMM_OPR_LD_BASED,
TDY_STAKES_COMM TDY_STAKES_COMM,
TDY_STAKES_COMM_B TDY_STAKES_COMM_B,
ACCU_STAKES_COMM ACCU_STAKES_COMM,
ACCU_STAKES_COMM_B ACCU_STAKES_COMM_B,
TDY_STAKES_COMM_LD TDY_STAKES_COMM_LD,
TDY_STAKES_COMM_LD_B TDY_STAKES_COMM_LD_B,
ACCU_STAKES_COMM_LD ACCU_STAKES_COMM_LD,
ACCU_STAKES_COMM_LD_B ACCU_STAKES_COMM_LD_B,
TDY_STAKES_COMM_CAS TDY_STAKES_COMM_CAS,
TDY_STAKES_COMM_CAS_B TDY_STAKES_COMM_CAS_B,
ACCU_STAKES_COMM_CAS ACCU_STAKES_COMM_CAS,
ACCU_STAKES_COMM_CAS_B ACCU_STAKES_COMM_CAS_B,
TDY_STAKES_COMM_OPR TDY_STAKES_COMM_OPR,
TDY_STAKES_COMM_OPR_B TDY_STAKES_COMM_OPR_B,
TDY_STAKES_COMM_OPR_CAS TDY_STAKES_COMM_OPR_CAS,
TDY_STAKES_COMM_OPR_CAS_B TDY_STAKES_COMM_OPR_CAS_B,
TDY_STAKES_COMM_OPR_LD TDY_STAKES_COMM_OPR_LD,
TDY_STAKES_COMM_OPR_LD_B TDY_STAKES_COMM_OPR_LD_B,
ACU_STAKES_COMM_OPR ACU_STAKES_COMM_OPR,
ACU_STAKES_COMM_OPR_B ACU_STAKES_COMM_OPR_B,
ACU_STAKES_COMM_OPR_CAS ACU_STAKES_COMM_OPR_CAS,
ACU_STAKES_COMM_OPR_CAS_B ACU_STAKES_COMM_OPR_CAS_B,
ACU_STAKES_COMM_OPR_LD ACU_STAKES_COMM_OPR_LD,
ACU_STAKES_COMM_OPR_LD_B ACU_STAKES_COMM_OPR_LD_B,
TDY_STAKES_COMM_PARTNER TDY_STAKES_COMM_PARTNER,
TDY_STAKES_COMM_PARTNER_B TDY_STAKES_COMM_PARTNER_B,
TDY_STAKES_COMM_PARTNER_CAS TDY_STAKES_COMM_PARTNER_CAS,
TDY_STAKES_COMM_PARTNER_CAS_B TDY_STAKES_COMM_PARTNER_CAS_B,
TDY_STAKES_COMM_PARTNER_LD TDY_STAKES_COMM_PARTNER_LD,
TDY_STAKES_COMM_PARTNER_LD_B TDY_STAKES_COMM_PARTNER_LD_B,
ACU_STAKES_COMM_PARTNER ACU_STAKES_COMM_PARTNER,
ACU_STAKES_COMM_PARTNER_B ACU_STAKES_COMM_PARTNER_B,
ACU_STAKES_COMM_PARTNER_CAS ACU_STAKES_COMM_PARTNER_CAS,
ACU_STAKES_COMM_PARTNER_CAS_B ACU_STAKES_COMM_PARTNER_CAS_B,
ACU_STAKES_COMM_PARTNER_LD ACU_STAKES_COMM_PARTNER_LD,
ACU_STAKES_COMM_PARTNER_LD_B ACU_STAKES_COMM_PARTNER_LD_B,
TDY_STAKES_COMM_MAGENT TDY_STAKES_COMM_MAGENT,
TDY_STAKES_COMM_MAGENT_B TDY_STAKES_COMM_MAGENT_B,
TDY_STAKES_COMM_MAGENT_CAS TDY_STAKES_COMM_MAGENT_CAS,
TDY_STAKES_COMM_MAGENT_CAS_B TDY_STAKES_COMM_MAGENT_CAS_B,
TDY_STAKES_COMM_MAGENT_LD TDY_STAKES_COMM_MAGENT_LD,
TDY_STAKES_COMM_MAGENT_LD_B TDY_STAKES_COMM_MAGENT_LD_B,
ACU_STAKES_COMM_MAGENT ACU_STAKES_COMM_MAGENT,
ACU_STAKES_COMM_MAGENT_B ACU_STAKES_COMM_MAGENT_B,
ACU_STAKES_COMM_MAGENT_CAS ACU_STAKES_COMM_MAGENT_CAS,
ACU_STAKES_COMM_MAGENT_CAS_B ACU_STAKES_COMM_MAGENT_CAS_B,
ACU_STAKES_COMM_MAGENT_LD ACU_STAKES_COMM_MAGENT_LD,
ACU_STAKES_COMM_MAGENT_LD_B ACU_STAKES_COMM_MAGENT_LD_B,
TDY_STAKES_COMM_AGENT TDY_STAKES_COMM_AGENT,
TDY_STAKES_COMM_AGENT_B TDY_STAKES_COMM_AGENT_B,
TDY_STAKES_COMM_AGENT_CAS TDY_STAKES_COMM_AGENT_CAS,
TDY_STAKES_COMM_AGENT_CAS_B TDY_STAKES_COMM_AGENT_CAS_B,
TDY_STAKES_COMM_AGENT_LD TDY_STAKES_COMM_AGENT_LD,
TDY_STAKES_COMM_AGENT_LD_B TDY_STAKES_COMM_AGENT_LD_B,
ACU_STAKES_COMM_AGENT ACU_STAKES_COMM_AGENT,
ACU_STAKES_COMM_AGENT_B ACU_STAKES_COMM_AGENT_B,
ACU_STAKES_COMM_AGENT_CAS ACU_STAKES_COMM_AGENT_CAS,
ACU_STAKES_COMM_AGENT_CAS_B ACU_STAKES_COMM_AGENT_CAS_B,
ACU_STAKES_COMM_AGENT_LD ACU_STAKES_COMM_AGENT_LD,
ACU_STAKES_COMM_AGENT_LD_B ACU_STAKES_COMM_AGENT_LD_B,
COMM_LD_ROU_MAIN_AGENT COMM_LD_ROU_MAIN_AGENT,
COMM_LD_SICBO_MAIN_AGENT COMM_LD_SICBO_MAIN_AGENT,
COMM_LD_ROU_PARTNER COMM_LD_ROU_PARTNER,
COMM_LD_SICBO_PARTNER COMM_LD_SICBO_PARTNER,
COMM_LD_ROU_SUB_AGENT COMM_LD_ROU_SUB_AGENT,
COMM_LD_SICBO_SUB_AGENT COMM_LD_SICBO_SUB_AGENT,
COMM_LD_ROU_CUSTOMER COMM_LD_ROU_CUSTOMER,
COMM_LD_SICBO_CUSTOMER COMM_LD_SICBO_CUSTOMER,
POSITION_LD_ROU_SUB_AGENT POSITION_LD_ROU_SUB_AGENT,
POSITION_LD_ROU_MAIN_AGENT POSITION_LD_ROU_MAIN_AGENT,
POSITION_LD_ROU_PARTNER POSITION_LD_ROU_PARTNER,
POSITION_LD_ROU_OPERATOR POSITION_LD_ROU_OPERATOR,
POSITION_LD_SICBO_SUB_AGENT POSITION_LD_SICBO_SUB_AGENT,
POSITION_LD_SICBO_MAIN_AGENT POSITION_LD_SICBO_MAIN_AGENT,
POSITION_LD_SICBO_PARTNER POSITION_LD_SICBO_PARTNER,
POSITION_LD_SICBO_OPERATOR POSITION_LD_SICBO_OPERATOR,
ACCU_TOTAL_GAMES_CAS ACCU_TOTAL_GAMES_CAS,
ACCU_TOTAL_GAMES_LD ACCU_TOTAL_GAMES_LD,
ACCU_DEPOSIT ACCU_DEPOSIT,
ACCU_WITHDRAWAL ACCU_WITHDRAWAL,
PJP_AMT PJP_AMT,
ACU_JACKPOT_CONTRIBUTION ACU_JACKPOT_CONTRIBUTION,
ACU_JACKPOT_WIN ACU_JACKPOT_WIN,
ACU_JACKPOT_AG_DOWNLINE ACU_JACKPOT_AG_DOWNLINE,
ACU_JACKPOT_MA_DOWNLINE ACU_JACKPOT_MA_DOWNLINE,
ACU_JACKPOT_SMA_DOWNLINE ACU_JACKPOT_SMA_DOWNLINE,
ACU_JACKPOT_AG ACU_JACKPOT_AG,
ACU_JACKPOT_MA ACU_JACKPOT_MA,
ACU_JACKPOT_SMA ACU_JACKPOT_SMA',dbms_redefinition.cons_use_pk);
end;
/
PL/SQL 过程已成功完成。-- --开始重定义,在这个过程中，查看dba_segments中，t3_p_tmp的bytes在不断扩大,dba_extents分配的空间也在不断变大，v$locked_object是有数据的，但在这个过程中可以INSERT。该事务结束后，v$locked_object没有记录啦。
在开始重定义的同时，在另外一个SESSION插入一条记录，看看后来能否同步过来，此时并不影响数据的INSERT
已用时间: 00: 11: 54.10
SQL> insert into test.customer_consolidated2 a
2 (a.operator_id,
3 a.customer_id,
4 a.business_day_system,
5 a.business_day_locale,
6 a.business_start_locale,
7 a.business_start_system,
8 a.currency_id)
9 values
10 (1, 1, 20130206, 1, 1,1,1);
已创建 1 行。
已用时间: 00: 00: 00.00
SQL> select count(1) from customer_consolidated2;--插入的记录在原表中了，此时尚未同步到T3_P_TMP中
COUNT(1)
----------
12258338
已用时间: 00: 00: 08.68
SQL> select count(1) from t3_p_tmp;
COUNT(1)
----------
12258337
已用时间: 00: 02: 46.53
SQL> insert into test.customer_consolidated2 a
2 (a.operator_id,
3 a.customer_id,
4 a.business_day_system,
5 a.business_day_locale,
6 a.business_start_locale,
7 a.business_start_system,
8 a.currency_id)
9 values
10 (1, 1, 20130201, 1, 1,1,1);--继续插入数据
已创建 1 行。
已用时间: 00: 00: 00.01
SQL> insert into test.customer_consolidated2 a
2 (a.operator_id,
3 a.customer_id,
4 a.business_day_system,
5 a.business_day_locale,
6 a.business_start_locale,
7 a.business_start_system,
8 a.currency_id)
9 values
10 (1, 1, 20130206, 1, 1,1,1);
已创建 1 行。
已用时间: 00: 00: 00.00
SQL> insert into test.customer_consolidated2 a
2 (a.operator_id,
3 a.customer_id,
4 a.business_day_system,
5 a.business_day_locale,
6 a.business_start_locale,
7 a.business_start_system,
8 a.currency_id)
9 values
10 (1, 1, 20130206, 1, 1,1,1);
已创建 1 行。
已用时间: 00: 00: 00.00
SQL> commit;
SQL> SHOW USER
USER 为 "TEST"
SQL> exec dbms_redefinition.sync_interim_table('TEST','CUSTOMER_CONSOLIDATED2','T3_P_TMP');-- 同步数据，数据同步后CUSTOMER_CONSOLIDATED2和T3_P_TMP数据相同，连着上次INSERT的一条数据和本次的三条数据一起同步过来
PL/SQL 过程已成功完成。
已用时间: 00: 00: 00.81
SQL> select count(1) from customer_consolidated2;
COUNT(1)
----------
12258341
已用时间: 00: 00: 02.89
SQL> select count(1) from t3_p_tmp;
COUNT(1)
----------
12258341
已用时间: 00: 02: 33.79
SQL> exec dbms_redefinition.finish_redef_table('TEST','CUSTOMER_CONSOLIDATED2','T3_P_TMP');-- 结束重定义的过程
PL/SQL 过程已成功完成。
已用时间: 00: 00: 00.71
SQL> col segment_name format a30
SQL> col owner format a10
SQL> select owner,segment_name,partition_name,segment_type from dba_segments where segment_name='CUSTOMER_CONSOLIDATED2' AND OWNER='TEST';
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE
----- ------------------------- ------------------------------ ------------------
TEST CUSTOMER_CONSOLIDATED2 T3_201206 TABLE PARTITION
TEST CUSTOMER_CONSOLIDATED2 T3_201207 TABLE PARTITION
TEST CUSTOMER_CONSOLIDATED2 T3_201208 TABLE PARTITION
TEST CUSTOMER_CONSOLIDATED2 T3_201209 TABLE PARTITION
TEST CUSTOMER_CONSOLIDATED2 T3_201210 TABLE PARTITION
TEST CUSTOMER_CONSOLIDATED2 T3_201211 TABLE PARTITION
TEST CUSTOMER_CONSOLIDATED2 T3_201212 TABLE PARTITION
TEST CUSTOMER_CONSOLIDATED2 T3_201301 TABLE PARTITION
TEST CUSTOMER_CONSOLIDATED2 T3_201302 TABLE PARTITION
已选择9行。
已用时间: 00: 00: 00.01
SQL> select count(1) from test.customer_consolidated2 partition (T3_201302);
COUNT(1)
----------
3
已用时间: 00: 00: 00.00
SQL> select count(1) from test.customer_consolidated2 partition (T3_201301);
COUNT(1)
----------
903603
已用时间: 00: 00: 12.81
SQL> select count(1) from test.customer_consolidated2;
COUNT(1)
----------
12258340
已用时间: 00: 03: 04.40
insert into test.customer_consolidated2 a
(a.operator_id,
a.customer_id,
a.business_day_system,
a.business_day_locale,
a.business_start_locale,
a.business_start_system,
a.currency_id)
values
(1, 1, 20130204, 1, 1,1,1);
insert into test.customer_consolidated2 a
(a.operator_id,
a.customer_id,
a.business_day_system,
a.business_day_locale,
a.business_start_locale,
a.business_start_system,
a.currency_id)
values
(1, 1, 20130209, 1, 1,1,1);
insert into test.customer_consolidated2 a
(a.operator_id,
a.customer_id,
a.business_day_system,
a.business_day_locale,
a.business_start_locale,
a.business_start_system,
a.currency_id)
values
(1, 1, 20130105, 1, 1,1,1);--插入的数据会自动添加到所在的分区
SQL> select count(1) from test.customer_consolidated2 partition (T3_201302);
COUNT(1)
----------
5
已用时间: 00: 00: 00.01
SQL> select count(1) from test.customer_consolidated2 partition (T3_201301);
COUNT(1)
----------
903604
已用时间: 00: 00: 12.64
SQL> select SEGMENT_NAME,partition_name ,bytes/1024/1024/1024 from dba_segments where segment_name='CUSTOMER_CONSOLIDATED2' ;
SEGMENT_NAME PARTITION_NAME BYTES/1024/1024/1024
------------------------------ ------------------------------ --------------------
CUSTOMER_CONSOLIDATED2 T3_201206 .000061035
CUSTOMER_CONSOLIDATED2 T3_201207 2.25
CUSTOMER_CONSOLIDATED2 T3_201208 2.3125
CUSTOMER_CONSOLIDATED2 T3_201209 2.31347656
CUSTOMER_CONSOLIDATED2 T3_201210 2.44042969
CUSTOMER_CONSOLIDATED2 T3_201211 2.3125
CUSTOMER_CONSOLIDATED2 T3_201212 2.46484375
CUSTOMER_CONSOLIDATED2 T3_201301 1.125
CUSTOMER_CONSOLIDATED2 T3_201302 .000061035
已选择9行。
SQL> select count(1) from dba_extents where segment_name='CUSTOMER_CONSOLIDATED2';
COUNT(1)
----------
1529
SQL> select SEGMENT_NAME,SUM(bytes/1024/1024/1024) from dba_segments where segment_name='CUSTOMER_CONSOLIDATED2' GROUP BY SEGMENT_NAME;
SEGMENT_NAME SUM(BYTES/1024/1024/1024)
------------------------------ -------------------------
CUSTOMER_CONSOLIDATED2 15.2188721
SQL> select count(1) from test.CUSTOMER_CONSOLIDATED2;
COUNT(1)
----------
12258343
SQL> alter table CUSTOMER_CONSOLIDATED2 truncate partition t3_201207;--TRUNCATE一个表的分区，对应的SEGMENTS,EXTENTS都变小啦，数据量也不包含所在分区的数据量
表被截断。
已用时间: 00: 00: 00.39
SQL> select SEGMENT_NAME,partition_name ,bytes/1024/1024/1024 from dba_segments where segment_name='CUSTOMER_CONSOLIDATED2' ;
SEGMENT_NAME PARTITION_NAME BYTES/1024/1024/1024
------------------------------ ------------------------------ --------------------
CUSTOMER_CONSOLIDATED2 T3_201206 .000061035
CUSTOMER_CONSOLIDATED2 T3_201207 .000061035
CUSTOMER_CONSOLIDATED2 T3_201208 2.3125
CUSTOMER_CONSOLIDATED2 T3_201209 2.31347656
CUSTOMER_CONSOLIDATED2 T3_201210 2.44042969
CUSTOMER_CONSOLIDATED2 T3_201211 2.3125
CUSTOMER_CONSOLIDATED2 T3_201212 2.46484375
CUSTOMER_CONSOLIDATED2 T3_201301 1.125
CUSTOMER_CONSOLIDATED2 T3_201302 .000061035
已选择9行。
已用时间: 00: 00: 00.04
SQL> select count(1) from dba_extents where segment_name='CUSTOMER_CONSOLIDATED2';
COUNT(1)
----------
1311
已用时间: 00: 00: 00.03
SQL> select SEGMENT_NAME,SUM(bytes/1024/1024/1024) from dba_segments where segment_name='CUSTOMER_CONSOLIDATED2' GROUP BY SEGMENT_NAME;
SEGMENT_NAME SUM(BYTES/1024/1024/1024)
------------------------------ -------------------------
CUSTOMER_CONSOLIDATED2 12.9689331
已用时间: 00: 00: 00.01
SQL> select count(1) from test.CUSTOMER_CONSOLIDATED2;
COUNT(1)
----------
10421784
已用时间: 00: 02: 10.01
SQL> Select table_name,partition_name,high_value,tablespace_name from user_tab_partitions where table_name='CUSTOMER_CONSOLIDATED2' order by partition_positi
TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
-------------------- -------------------- ---------- ---------------
CUSTOMER_CONSOLIDATED2 T3_201206 20120630 USERS
CUSTOMER_CONSOLIDATED2 T3_201207 20120731 USERS
CUSTOMER_CONSOLIDATED2 T3_201208 20120831 USERS
CUSTOMER_CONSOLIDATED2 T3_201209 20120930 USERS
CUSTOMER_CONSOLIDATED2 T3_201210 20121031 USERS
CUSTOMER_CONSOLIDATED2 T3_201211 20121130 USERS
CUSTOMER_CONSOLIDATED2 T3_201212 20121231 USERS
CUSTOMER_CONSOLIDATED2 T3_201301 20130131 USERS
CUSTOMER_CONSOLIDATED2 T3_201302 20130228 USERS
已选择9行。
SQL> alter table customer_consolidated2 add partition T3_201303 values less than(20130331);--尝试添加分区
表已更改。
SQL> Select table_name,partition_name,high_value,tablespace_name from user_tab_partitions where table_name='CUSTOMER_CONSOLIDATED2' order by partition_positi
TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
-------------------- -------------------- ---------- ---------------
CUSTOMER_CONSOLIDATED2 T3_201206 20120630 USERS
CUSTOMER_CONSOLIDATED2 T3_201207 20120731 USERS
CUSTOMER_CONSOLIDATED2 T3_201208 20120831 USERS
CUSTOMER_CONSOLIDATED2 T3_201209 20120930 USERS
CUSTOMER_CONSOLIDATED2 T3_201210 20121031 USERS
CUSTOMER_CONSOLIDATED2 T3_201211 20121130 USERS
CUSTOMER_CONSOLIDATED2 T3_201212 20121231 USERS
CUSTOMER_CONSOLIDATED2 T3_201301 20130131 USERS
CUSTOMER_CONSOLIDATED2 T3_201302 20130228 USERS
CUSTOMER_CONSOLIDATED2 T3_201303 20130331 USERS
已选择10行。
http://www.itpub.net/thread-1759170-1-1.html
181
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
