ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout

ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout

KUP-11024: This external table can only be accessed from within a Data Pump job.

Alert Log details: 

Trace file details:

Cause:

The primary cause of this issue is that an external table existed at some point in time but does not now. However, the database still believes the table exists since the dictionary information about the object has not been modified to reflect the change. When DBMS_STATS is run against the table in question, it makes a call out to the external table which fails because the object is not there.

There are many reasons that an external table may not exist including:

• - Temporary Datapump external tables have not been cleaned up properly. The dictionary information should have been dropped when the DataPump jobs completed.
• - An OS file for an External table has been removed without clearing up the corresponding data dictionary information. 
• Solution:
• Essentially the solution to this issue is to clean up the orphaned dictionary entries.
• 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;
• or 
• select OWNER,OBJECT_NAME,OBJECT_TYPE, status,
  to_char(CREATED,'dd-mon-yyyy hh24:mi:ss') created
  ,to_char(LAST_DDL_TIME , 'dd-mon-yyyy hh24:mi:ss') last_ddl_time
  from dba_objects
  where object_name like 'ET$%'
  /
  
  select owner, TABLE_NAME, DEFAULT_DIRECTORY_NAME, ACCESS_TYPE
  from dba_external_tables
  order by 1,2
  /
• 
  Drop external temporary table
• 
  Ref. Doc: ID 1274653.1

How to get Scheduled jobs information in Oracle?

SQL> select TABLE_NAME from dict where table_name like 'DBA_SCHEDULER_JOB%';

DBA_SCHEDULER_JOBS
DBA_SCHEDULER_JOB_ARGS
DBA_SCHEDULER_JOB_CLASSES
DBA_SCHEDULER_JOB_DESTS
DBA_SCHEDULER_JOB_LOG
DBA_SCHEDULER_JOB_ROLES
DBA_SCHEDULER_JOB_RUN_DETAILS

SQL> select TABLE_NAME from dict where table_name like 'ALL_SCHEDULER_JOB%';

ALL_SCHEDULER_JOBS
ALL_SCHEDULER_JOB_ARGS
ALL_SCHEDULER_JOB_CLASSES
ALL_SCHEDULER_JOB_DESTS
ALL_SCHEDULER_JOB_LOG
ALL_SCHEDULER_JOB_RUN_DETAILS

To See All Scheduled Jobs

 select owner, job_name, state from all_scheduler_jobs;

To Check all scheduled jobs for a particular user

select owner, job_name, state from all_scheduler_jobs where owner ='XXX';

To See Job Run Details

 select LOG_ID, LOG_DATE, JOB_NAME, STATUS from ALL_SCHEDULER_JOB_RUN_DETAILS where job_name ='JOB_AUTOCLOSE_QUEUE';

Enable / Disable a job

BEGIN
  DBMS_SCHEDULER.ENABLE('myjob');
END;
BEGIN
  DBMS_SCHEDULER.DISABLE('myjob');
END;


The DBA views are restricted. So you won't be able to query them unless you're connected as a DBA or similarly privileged user.
The ALL views show you the information you're allowed to see. Normally that would be jobs you've submitted, unless you have additional privileges.

To extract DDL of Scheduled Job

select dbms_metadata.get_ddl('PROCOBJ', 'JOB_AUTOCLOSE_QUEUE') from dual;

Oracle User Privileges: How to See All Privileges for a User

Querying DBA/USER Privilege Views

A database administrator (DBA) for Oracle can simply execute a query to view the rows in DBA_SYS_PRIVS, DBA_TAB_PRIVS, and DBA_ROLE_PRIVS to retrieve information about user privileges related to the system, tables, and roles, respectively.

To view all system privileges granted to all users would issue the following query:

SELECT  * FROM  DBA_SYS_PRIVS;

The DBA_SYS_PRIVS view contains three columns of data:

GRANTEE is the name, role, or user that was assigned the privilege.
PRIVILEGE is the privilege that is assigned.
ADMIN_OPTION indicates if the granted privilege also includes the ADMIN option.

To determine which users have direct grant access to a table we’ll use the DBA_TAB_PRIVS view:

SELECT  * FROM  DBA_TAB_PRIVS;

Important columns are:

GRANTEE is the name of the user with granted access.
TABLE_NAME is the name of the object.
PRIVILEGE is the privilege assigned to the GRANTEE for the associated object.
 
Finally, querying the DBA_ROLE_PRIVS view has much of the same information but applicable to roles instead, where the GRANTED_ROLE column specifies the role in question:

SELECT  * FROM  DBA_ROLE_PRIVS;
 
TO KNOW CURRENT USER’S PRIVILEGES

To view the privileges solely for the current user.

This is done by alternatively querying USER_ versions of the above DBA_ views. Thus, instead of looking at DBA_SYS_PRIVS we’d query USER_SYS_PRIVS, like so:

SELECT  * FROM  USER_SYS_PRIVS;

Since the USER_ privilege views are effectively the same as their DBA_ counterparts, but specific to the current user only, the type of returned data and column names are all identical to those when querying DBA_ views intead.

Which session is generating the redo ?

select username, sid, value, sql_id, prev_sql_id
from ( select username,s.sid, value, sql_id, prev_sql_id 
from v$session u, v$sesstat s, v$statname n
where n.name = 'redo size'
and s.statistic# = n.statistic#
and u.sid = s.sid order by value desc )
where rownum <= 10
order by value desc
/

User privilege for kill session

1) please create in sys ( login as sysdba)
-------------------------------------------------
create or replace procedure sys.kill_session
(
p_sid NUMBER
,p_serial# NUMBER
) AS
m_sql VARCHAR2(1000);
m_loginusername VARCHAR2(30);
m_killusername VARCHAR2(30);
m_schemaname varchar2(30);
BEGIN
SELECT a.USERNAME,
a.SCHEMANAME
INTO m_killusername,
m_schemaname
FROM v$session a
WHERE a.sid = p_sid
AND a.serial# = p_serial#;
select user
into m_loginusername
from dual;
IF m_loginusername = m_killusername AND
m_schemaname NOT IN ('SYS', 'SYSTEM')
THEN
m_sql := 'ALTER SYSTEM KILL SESSION ' || '''' || p_sid || ',' ||
p_serial# || '''' || ' IMMEDIATE';
EXECUTE IMMEDIATE m_sql;
dbms_output.put_line('Session Killed ');
ELSE
dbms_output.put_line(' Login User Name ' || m_loginusername ||
' User Session to Kill ' ||m_killusername);
dbms_output.put_line('Cannot kill session of other users or Admin users');
raise_application_error(-20101,'Cannot kill session of other users or Admin users or self');
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error in Kill Session.' ||SQLERRM(SQLCODE));
END kill_session;
------------------------------------------------------
2) grant execute on kill_seesion to suh;
3) grant select on v_$session to suh;
4) login as suh/suh
5) select a.sid, a.serial#
from v$session a
where username = 'SUH'
/
6) exec kill_session ( sid,serial#)
7) exec kill_session(18,3320);