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_OWNER 
--,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.

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

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

![SNAG_20210217_105837_001.png](/image?hash=3f6e706aa187d2171ceb68ad2eedc22e4ee989d4f9a30463786cc5e698341a9e)

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
```
dbms_job.run(3188); 
-- 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 
SELECT JOB, LOG_USER, SCHEMA_USER FROM DBA_JOBS where JOB = 3188;
```

### 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 `dbms_job.run` 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.