oracle oracle-12c add tag
John aka hot2use
We have a rather large Oracle database instance, which has multiple schemas that all have their own **Full Text Search** implemented. The **domain indexes** that are updated on an hourly basis are all located in their individual schema and are set up to use the deprecated `Job` feature of Oracle instead of the newer `Job Scheduler` feature.

Sometimes the Oracle jobs will hang on the executable side which can be observed in Sysinternal's **Process Explorer** as hanging `ctxtx.exe` processes that no longer have any context switching.

In order to fix the issues the processes are killed and the individual **job** (deprecated) will be **Executed** in **Toad for Oracle**.

In order to see which domain indexes still required updating I will run the following query and then the corresponding job in the corresponding schema.

set pages 50
set lines 230
column PND_INDEX_NAME format a20
column PND_INDEX_OWNER format a20
select count(*) as PENDINGDocs, 
--,ccp.PND_INDEX_NAME -- Remove comment for more details
from ctxsys.ctx_pending ccp 
where 1=1 
--AND ccp.PND_INDEX_OWNER = 'SCHEMA_NAME' -- limit results to one office  
group by ccp.PND_INDEX_OWNER
--, ccp.PND_INDEX_NAME -- Remove comment for more details
order by ccp.PND_INDEX_OWNER
--, ccp.PND_INDEX_NAME-- Remove comment for more details

This will return the schemas (or owners) of the Domain Indexes that could be updated.

>     ----------- --------------------
>              79 SCHEMA_NAME          
>     1 row selected.

I can then find the job in the corresponding schema and `right-click | Execute`.


And the Domain Index is updated. Pending documents are indexed and all is well.

# Idea

Instead of switching from **query** to **Schema Browser** and back again, I thought I could possibly execute the job directly from the Query Window in Toad. I log in as `sys as sysdba` in Toad and should then be able to perform a simple:

### Input
-- looks like the job id of the Job I previously ran with 
-- Right-click | Execute (see screen shot)
However, this is returning an error message:

### Output

>     "ORA-23421: job number 3188 is not a job in the job queue" 

# Verify Job Exists

Just to be on the safe side I queried the `dba_jobs` table to verify the jobs exists.

### Input

set pages 50
set lines 230
column log_user format a11
column schema_user format a11 

### Output

>            JOB LOG_USER    SCHEMA_USE
>     ---------- ----------- -----------
>           3188 SCHEMA_NAME SCHEMA_NAME
>     1 row selected.

# Question

How can I run the job as `sys as sysdba` as a statement for the job that has been created in the schema for `SCHEMA_NAME`?

The official documentation for `` isn't providing me with enough information. 

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.