Friday, October 12, 2012

Cleanup Orphaned DATAPUMP jobs

Some times you cannot stop / kill a datapump job using the attach command , possibly with an error , and its status can be undefined or not running either on EM or in dba_datapump_jobs. If you are sure that this job must be cleared then perform the following as sys:



connect sys as sysdba on SQLPLUS

-- locate Data Pump jobs:

SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs
WHERE job_name NOT LIKE 'BIN$%'
ORDER BY 1,2;



e.g

OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED
---------- ------------------- --------- --------- ----------- --------
SCOTT SYS_EXPORT_TABLE_01 EXPORT TABLE NOT RUNNING 0 


-- locate Data Pump master tables:

SELECT o.status, o.object_id, o.object_type,
o.owner||'.'||object_name "OWNER.OBJECT"
FROM dba_objects o, dba_datapump_jobs j
WHERE o.owner=j.owner_name AND o.object_name=j.job_name
AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;


STATUS OBJECT_ID OBJECT_TYPE OWNER.OBJECT
------- ---------- ------------ -------------------------
VALID 85215 TABLE SCOTT.SYS_EXPORT_TABLE_01


-- drop the master table

DROP TABLE scott.sys_export_table_01 purge;


Wednesday, October 3, 2012

Truncate table makes previously UNUSABLE indexes USABLE


Check the example below :

alter index my_idx unusable; --> This make the index unusable
truncate table my_table;
The index my_idx becomes usable !!!


References :
http://www.oramoss.com/blog/2006/07/14/truncate-command-marks-previously-unusable-indexes-as-usable/