ORA-23421: job number xxx is not a job in the job queue

So, This morning I tried to clean up the jobs running in my database.  We have a huge support database where there are around 90 schemas installed. Each schema has its own set of jobs running.

Obviously, this was consuming lot of resources, and we decided to remove the jobs that are not necessary.

I logged in as SYS to scan dba_jobs, and I found there are 500 jobs scheduled

select job, log_user, priv_user,schema_user,what, interval, next_date, broken
from dba_jobs ;

When I tried to remove a job, with dbms_job.job =42, using

dbms_job.remove (42), I got the error below



So, there are 2 options

Option 1
1) Find the Owner of the job from dbms_job table, using schema_user column
2) Log in as schema user and then remove the job. Obviosly, this is time consuming to remove 500 jobs

Option 2
1) Oracle provides another package called dbms_ijob. Both Spec an Body are wrapped, so you cannot find the procedures, but it works pretty similar to dbms_job



Remember to COMMIT, in the end. Otherwise, it will still show up in dba_jobs  in somebody else's session.

Rollback a commited transaction

Using Oracle's FLASHBACK command, it is possible to take any table to its previous state (even after commiting some delete statements)

1) You need to enable row movement on the table
2) You can take the table back to its previous state as of any time period


1) alter table table_name enable row movement;

FLASHBACK TABLE table_name TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' minute);
or
FLASHBACK TABLE table_name TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '30' second);

ORA-00904: "OBJECT_VALUE": invalid identifier

OBJECT_VALUE is the pseudocolumn for xmltype. Its also called as sys_nc_rowinfo$.

It only works if you create a table of xmltype



Note that, when you use select * from, the colum name is called SYS_NC_ROWINFO$



Alternativley, you can get the column using object_value pseudo column.


If you create the same table in a different way, OBJECT_value will not work

How to erase company logo

How many times you wanted to debrand your phone/monitor/cups etc

Here is an easy way, no more scratches and dents

1. Take your sunblock/sunscreen lotion, and apply it over the logo
2. Wait for a minute
3. Use a soft cloth and gently rub on the logo area
4. your logo will start vanishing !!!

Worked great on my computer monitor, bank sponsored water bottle and cell phones

Convert schema to Read Only

There are times where you want to restrict a schema to Read Only mode. Say you want to freeze your production system, and you don't want anybody to modify any data.

The quickest way to do is to make the tablespaces read only

In this example below, I have RMANNI schema and my database is ORCLDB11G

Login as SYS or SYSTEM

1) Find all the tablespaces used by the RMANNI schema

select distinct tablespace_name from dba_tables where owner ='RMANNI';
select distinct tablespace_name from dba_indexes where owner ='RMANNI';

2) Change tablespace to Read Only ( Make sure that you have no transactions pending a commit or rollback)

 alter tablespace rmanni_data read only;
 alter tablespace rmanni_indx read only;



3) Now when you query dba_tablespaces and check the status column, they will show as READ ONLY instead of ONLINE
 

4) If you try make any modification to the schema, you will get a error message now



5) To make the tablespaces back to read write,






you can see the status changed from READ ONLY to ONLINE

***Only issue/bug I noticed is, even if the tablespace is read only, Oralce allows me to create empty tables.