load data   

infile '/oracle/t_ash_dec_25.txt'   

append into table t_ash_dec_25

fields terminated by ","

trailing nullcols

(DBID, INSTANCE_NUMBER, SAMPLE_ID, SAMPLE_TIME, SESSION_ID, SESSION_SERIAL#, USER_ID, SQL_ID, SQL_CHILD_NUMBER, SQL_PLAN_HASH_VALUE, SERVICE_HASH, SESSION_TYPE, SQL_OPCODE, PLSQL_ENTRY_OBJECT_ID, PLSQL_ENTRY_SUBPROGRAM_ID, PLSQL_OBJECT_ID, PLSQL_SUBPROGRAM_ID, BLOCKING_SESSION, BLOCKING_SESSION_SERIAL#, QC_SESSION_ID, QC_INSTANCE_ID, XID, CURRENT_OBJ#, CURRENT_FILE#, CURRENT_BLOCK#, EVENT_ID, SEQ#, P1, P2, P3, WAIT_TIME, TIME_WAITED, FLAGS, FORCE_MATCHING_SIGNATURE, PROGRAM, MODULE, ACTION, CLIENT_ID, MACHINE, PORT, ECID)

oracle@DBxxx01$ls -lt load*

-rw-r--r--   1 oracle   oinstall    4825 Dec 29 11:28 load_data_ash.log

-rw-r--r--   1 oracle   oinstall     733 Dec 29 11:23 load_data_ash.ctl

oracle@DBxxx01$

oracle@DBxxx01$ls -lt t_ash_dec_25*

-rw-r--r--   1 oracle   oinstall   15196 Dec 29 11:26 t_ash_dec_25.bad

-rw-r--r--   1 oracle   oinstall 85273326 Dec 29 10:07 t_ash_dec_25.txt

oracle@DBxxx01$

create table t_ash_dec_25

(DBID NUMBER, INSTANCE_NUMBER NUMBER, SAMPLE_ID NUMBER , SAMPLE_TIME  timestamp(3), SESSION_ID NUMBER, SESSION_SERIAL# NUMBER, USER_ID NUMBER, SQL_ID varchar2(100), SQL_CHILD_NUMBER NUMBER, 

SQL_PLAN_HASH_VALUE NUMBER, SERVICE_HASH NUMBER, SESSION_TYPE varchar2(100), SQL_OPCODE NUMBER, PLSQL_ENTRY_OBJECT_ID NUMBER, PLSQL_ENTRY_SUBPROGRAM_ID NUMBER, PLSQL_OBJECT_ID NUMBER, PLSQL_SUBPROGRAM_ID NUMBER, BLOCKING_SESSION NUMBER, BLOCKING_SESSION_SERIAL# NUMBER, 

QC_SESSION_ID NUMBER, QC_INSTANCE_ID NUMBER, XID varchar2(100), CURRENT_OBJ# NUMBER, CURRENT_FILE# NUMBER, CURRENT_BLOCK# NUMBER, EVENT_ID NUMBER, SEQ# NUMBER, P1 NUMBER, P2 NUMBER, P3 NUMBER, WAIT_TIME NUMBER, TIME_WAITED NUMBER, FLAGS varchar2(200) , FORCE_MATCHING_SIGNATURE NUMBER, PROGRAM varchar2(200), MODULE varchar2(200), ACTION varchar2(200), CLIENT_ID NUMBER, MACHINE varchar2(200), PORT NUMBER, ECID NUMBER)

-------------------t_ash_dec_25.txt(from newpay2_mmnl_29217-2018-12-25 221928.trc)

2828698043,2,145038176,"12-25-2018 22:19:28.300610000",5097,10,88,"",0,0,3427055676,1,0,0, 0,0, 0,4294967295,0,0,0,,4294967295,0,0,1729366244,25683,2747636884,0,7582522,0,0,0,0,"JDBC Thin Client","JDBC Thin Client","","","PXXXXSDFW01",52896,""

2828698043,2,145038176,"12-25-2018 22:19:28.300610000",5098,4,88,"",0,0,3427055676,1,0,0, 0,0, 0,4294967295,0,0,0,,4294967295,0,0,1729366244,25803,2747636884,0,7582713,0,0,0,0,"JDBC Thin Client","JDBC Thin Client","","","PXXXXSDFW01",52893,""

2828698043,2,145038176,"12-25-2018 22:19:28.300610000",5099,35110,0,"7ng34ruy5awxq",65535,0,3427055676,1,3,4269, 21,0, 0,5439,58471,0,0,,78751,260,374735,916468430,8314,4207688000,1365009656,305,0,0,0,0,"oracle@payttt2 (J000)","DBMS_SCHEDULER","AUTO_SPACE_ADVISOR_JOB","","payttt2",0,""

2828698043,2,145038176,"12-25-2018 22:19:28.300610000",5104,61767,88,"9ynjnmu1c5sba",65535,0,2427994137,1,3,0, 0,0, 0,4294967295,0,0,0,,216738,257,252680,1729366244,9304,2193809770,0,27278,0,0,0,0,"","","","","ggggggx02",56354,""

........

........

-------------------load_data_ash.ctl

load data

infile '/oracle/t_ash_dec_25.txt'

append into table t_ash_dec_25

fields terminated by ','

optionally enclosed by '"'

trailing nullcols

(DBID, INSTANCE_NUMBER, SAMPLE_ID,SAMPLE_TIME "to_timestamp(:SAMPLE_TIME,'''mm-dd-yyyy hh24:mi:ss.ff9''')", SESSION_ID, SESSION_SERIAL#, USER_ID, SQL_ID, SQL_CHILD_NUMBER, SQL_PLAN_HASH_VALUE, SERVICE_HASH, SESSION_TYPE, SQL_OPCODE, PLSQL_ENTRY_OBJECT_ID, PLSQL_ENTRY_SUBPROGRAM_ID, PLSQL_OBJECT_ID, PLSQL_SUBPROGRAM_ID, BLOCKING_SESSION, BLOCKING_SESSION_SERIAL#, QC_SESSION_ID, QC_INSTANCE_ID, XID, CURRENT_OBJ#, CURRENT_FILE#, CURRENT_BLOCK#, EVENT_ID, SEQ#, P1, P2, P3, WAIT_TIME, TIME_WAITED, FLAGS, FORCE_MATCHING_SIGNATURE, PROGRAM, MODULE, ACTION, CLIENT_ID, MACHINE, PORT, ECID)

sqlldr userid='PXXXX/hxxxPXXXX7' control='/oracle/load_data_ash.ctl' log='/oracle/load_data_ash.log' rows=1000 readsize=256000000 bindsize=256000000

sqlldr userid='PXXXX/hxxxPXXXX7' control='/oracle/load_data_ash.ctl' log='/oracle/load_data_ash.log' rows=1000 readsize=2048576000 bindsize=2048576000

---it is ok

sqlldr userid='PXXXX/hxxxPXXXX7' control='/oracle/load_data_ash.ctl' log='/oracle/load_data_ash.log' rows=300000 readsize=456000000 bindsize=456000000

select count(*)  from t_ash_dec_25;