SQL> select * from v$TEMPSEG_USAGE;
USERNAME USER SESSION_ADDR SESSION_NUM SQLADDR SQLHASH SQL_ID TABLESPACE CONTENTS SEGTYPE SEGFILE# SEGBLK# EXTENTS BLOCKS SEGRFNO#
------------------------------ ------------------------------ ---------------- ----------- ---------------- ---------- ------------- ------------------------------- --------- --------- ---------- ---------- ---------- ---------- ----------
DEV_USER DEV_USER 000000047FA65178 9265 000000046A8007C8 3403569819 3nz260v5dwqnv NEW_TEMP TEMPORARY DATA 263 474761 1 128 3
DEV_USER DEV_USER 000000047FA65178 9265 000000046A8007C8 3403569819 3nz260v5dwqnv NEW_TEMP TEMPORARY LOB_DATA 263 483849 1 128 3
DEV_USER DEV_USER 000000047FA65178 9265 000000046A8007C8 3403569819 3nz260v5dwqnv NEW_TEMP TEMPORARY INDEX 263 110345 1 128 3
INTEGRATION_TOOL INTEGRATION_TOOL 000000047FA915F0 37125 0000000461DBD8D8 2330358966 5ckar9q5fcx5q NEW_TEMP TEMPORARY SORT 263 493065 57201 7321728 3
INTEGRATION_TOOL INTEGRATION_TOOL 000000047FA915F0 37125 0000000461DBD8D8 2330358966 5ckar9q5fcx5q NEW_TEMP TEMPORARY SORT 263 256649 8693 1112704 3
INTEGRATION_TOOL user had used so many temp tablespace. I check the session’s status is ‘db file sequential read’ , seems normal.
Please keep monitor it , thanks.
*******************************************************************
We got error in database alert log.
Errors in file /opt/oracle/admin/wminvp1/bdump/wminvp11_j000_29617.trc:
ORA-12012: error on auto execute of job 55200
ORA-06550: line 22, column 6:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 21, column 12:
PL/SQL:
SQL Statement ignored
ORA-06550: line 28, column 51:
PLS-00364: loop index variable 'REC' use is invalid
ORA-06550: line 28, column 3:
PL/SQL: Statement ignored
SQL> select log_user,schema_user from dba_jobs where job=55200;
LOG_USER SCHEMA_USER
------------------------------ ------------------------------
BATCH_USER BATCH_USER
SQL> select log_user,schema_user from dba_jobs where job=55200;
LOG_USER SCHEMA_USER
------------------------------ ------------------------------
BATCH_USER BATCH_USER
SQL> select what from dba_jobs where job=55200;
WHAT
--------------------------------------------------------------------------------
win_cust_repl_tgt_pkg.pull_replication(1,'catalog');
insert into win_crp_multirow_req_log(message_type,message_text,created_dtm) val
ues('I','Starting delete TNT RNG',sysdate);
delete /* parallel(r3 5) */ from win_carrier_days_transit_rng r3;
insert into win_crp_multirow_req_log(message_type,message_text,created_dtm) val
ues('I','Finished delete TNT RNG',sysdate);
insert into win_crp_multirow_req_log(message_type,message_text,created_dtm) val
ues('I','Starting insert TNT RNG',sysdate);
insert /*+ append parallel(r2 5)*/ into win_carrier_days_transit_rng r2
select /*+ parallel(r 5) */ * from
wca_carrier_days_transit_rng@catalogr;
insert into win_crp_multirow_req_log(message_type,message_text,created_dtm) val
ues('I','Finished insert TNT RNG',sysdate);
commit;
delete from win_crp_process_req_status s
where
s.req_id in
(select r.req_id
from
job_control@catalogj,wca_crp_process_req@catalog r
where r.created_dtm between j.start_dtm-1/12 and nvl(j.end_dtm,sysdate) +
1/12
and r.remote_database_name='INVENTORY'
) ;
commit;
for rec in (
SELECT distinct di.item_id
FROM win_item_inventory ii,
wca_item_distributor@catalogdi
WHERE ii.item_id = di.item_id
AND ii.distributor_id = di.distributor_id
AND nvl(ii.is_active,'N') != nvl(di.is_active,'N') )
loop
win_item_availability_pkg.chg_itm_av_on_is_actv(rec.item_id);
end loop;
The job is belong to batch_user
