一次导数据流程

1. 整理客户提供的列表，创建external table

列表整理如下

CICOSADMIN.BN_PARTICULAR
CICOSADMIN.DL_AGENT
CICOSADMIN.EP_BILL
CICOSADMIN.EP_NOM_PTY
CICOSADMIN.IP_BILL
CICOSADMIN.BN_APPLN
CICOSADMIN.BN_ALLOCATE
CICOSADMIN.CARGO_EVENT
CICOSADMIN.EP_SHPMT
CICOSADMIN.EP_SPLIT_SHPMT
CICOSADMIN.EP_CLOSE_SHPMT
CICOSADMIN.EP_TRANS_DTL
CICOSADMIN.IP_BL_ITEM
CICOSADMIN.IP_CGO_NOM
CICOSADMIN.IP_NOM_SEC_CARR
CICOSADMIN.IP_CLOSE_BJ
CICOSADMIN.IP_DOCU_FEE
CICOSADMIN.IP_PROCESSING
CICOSADMIN.IP_SPLIT_BL
CICOSADMIN.IP_TRANS_DTL
CICOSADMIN.IP_TRANS_DELV
CICOSADMIN.VO_CLOSE_VSL
CICOSADMIN.VSL_CONV
CICOSADMIN.MPT_ADMIN_FEE_EVENT
CICOSADMIN.MPT_CARGO_EVENT
CICOSADMIN.MPT_CLOSE_BJ_EVENT
CICOSADMIN.MPT_CLOSE_SHPMT_EVENT
CICOSADMIN.MPT_CLOSE_VSL_EVENT
CICOSADMIN.MPT_DELIVERY_EVENT
CICOSADMIN.CSBS_CAR_FACTOR
CICOSADMIN.CSBS_CAR_FACTOR_DTL
CICOSADMIN.CSBS_CAR_OVERFLOW
CICOSADMIN.CSBS_CAR_OVERFLOW_DTL
CICOSADMIN.CSBS_CFS_CNTR_TRANSFER
CICOSADMIN.CSBS_CFS_DELIVERY
CICOSADMIN.CSBS_CFS_MISC_ITEM
CICOSADMIN.CSBS_CFS_PACK
CICOSADMIN.CSBS_CFS_RECEIVE
CICOSADMIN.CSBS_CFS_STORE_ORDER
CICOSADMIN.CSBS_CFS_UNPACKING
CICOSADMIN.CSBS_DELIVERY
CICOSADMIN.CSBS_FLEXIBLE_PRICE
CICOSADMIN.CSBS_LEASE_AGRMT
CICOSADMIN.CSBS_MISC_ITEM
CICOSADMIN.CSBS_RECEIVE
CICOSADMIN.CSBS_STORE_ORDER

整理好的列表

将列表放在 DB的 /tmp/mydir目录下面，创建外部表。外部表如下：

CREATE DIRECTORY mydir AS '/tmp/mydir';

GRANT READ,WRITE ON DIRECTORY mydir TO CICOSADMIN;

CREATE TABLE raw_list(owner VARCHAR2(28),table_name VARCHAR2(28)) 
ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY mydir ACCESS PARAMETERS(
    RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY '.'
    MISSING FIELD VALUES ARE NULL(OWNER,TABLE_NAME) )LOCATION ('list'));

创建外部表语句

2. truncate 表

检测外键

SELECT 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' DISABLE CONSTRAINT '||CONSTRAINT_NAME||';'
FROM DBA_CONSTRAINTS 
WHERE     CONSTRAINT_TYPE='F' 
    AND R_OWNER='CICOSADMIN'
    AND R_CONSTRAINT_NAME IN 
        (SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE CONSTRAINT_TYPE='P' AND OWNER='CICOSADMIN' AND TABLE_NAME IN  (SELECT DISTINCT TABLE_NAME FROM RAW_LIST));

检测外键

注意这里的 owner cicosadmin需要根据实际情况填写。(这里的外键类型是R不是F)

生成truncate 脚本

SELECT 'TRUNCATE TABLE '||OWNER||'.'||TABLE_NAME FROM RAW_LIST;

truncate脚本

3. check trigger

SELECT 'ALTER TRIGGER '||OWNER||'.'||NAME||' DISABLE ;'
FROM DBA_DEPENDENCIES
WHERE TYPE='TRIGGER' AND REFERENCED_OWNER='CICOSADMIN' AND REFERENCED_NAME IN (SELECT TABLE_NAME FROM RAW_LIST );

check trigger

4. 导入数据

for tab in `cat list`
do
  echo "truncate table CICOSADMIN.$tab;" | sqlplus / as sysdba
  imp \'/ as sysdba\' fromuser=CICOSADMIN touser=CICOSADMIN statistics=none ignore=y log=./log/$tab.log file=../dump/$tab.exp tables=$tab
done

import