TIP for Oracle - Create/Remove jobs for other users

I'm sure every DBA is able to delete or send a task to other users if the administrator knows the password for another user, life will be easy and it's as simple as connecting with the user and send a job dbms_job.submit or remove it with dbms_job.remove.

What if DBA has not the password, If DBA tries to remove other user jobs, the following error will be shown :

ORA-23421: job number nnnn is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 536
ORA-06512: at "SYS.DBMS_JOB", line 261
ORA-06512: at line 1

Also DBA can not submit job with dbms_job for other users with dbms_job if DBA does not know the password.

Fortunately, Oracle has undocumented dbms_ijob package which let you as DBA to submit jobs as other users and also remove jobs of other users.

Here is a sample on how to submit a job for user TEST.


declare
job_num number;
nlsvar varchar2(4000);
envvar raw(32);
begin
select nls_env,misc_env into nlsvar,envvar from dba_jobs where rownum<2 and nls_env is not null and misc_env is not null ;
select max(job)+1 into job_num from dba_jobs;
sys.dbms_ijob.submit(job=>job_num,luser=>'TEST',puser=>'TEST',cuser=>'TEST',what=>'insert into tst values(1);' ,next_date=>sysdate+1/(24*60), interval=>'trunc(sysdate,''MI'')+1/48', broken=>FALSE,nlsenv=>nlsvar,env=>envvar);
dbms_output.put_line(job_num);
end;
/
commit;
nlsenv and env should not be passed NULL,In above sample to make it simple, I copied it from the existing jobs. puser (privileged user), luser (login user), cuser(schema user) are the same as what we have in dba_jobs. Job number needs to be passed to dbms_ijob, to make sure its uniqueness, a number higher than max job number is passed.

In order to remove any job , the following simple code can be run.


dbms_ijob.remove(job_number??);

After choosing Oracle SQL Developer as a tool for Oracle database preferred, had it installed and learn a new technology to support their MS SQL databases? Not at all! It is easy to connect SQL Developer for MS SQL databases, and I'll show you how.

Background
For years I worked in technical support for software vendors, and I do not know what the client tool should be available when the customer receives the system. In fact, in many cases, just give me a command line sqlplus access. So, I never chose my favorite Oracle client, and became particularly skilled with something different from sqlplus command line.

Finally, I found myself working directly with a customer, and finally determine which tool I liked best, and like many of you, I chose Raptor - now known as Oracle SQL Developer. Explain my choice goes beyond this article, but suffice it to say that it is a powerful time saver, at best, and a big step on the command line sqlplus at worst.

Connecting to MS SQL
 Not all database applications that build on the foundation of Oracle, and the most popular variant is curious nephew Sybase MS-SQL. Fortunately, there is no need for another application to the case of MS-SQL, you can use SQL Developer, and here's how.


Step 1. Get the JDBC driver for MS SQL

If you don't already have one, download a JDBC driver for MS-SQL. I use the jTDS driver, which is open-source Java. It doesn't matter where you place it, but remember wher

Step 2. Load the JDBC driver
As explained in the tutorial from Oracle, under Settings, scroll to the third database and JDBC drivers. When you load jtds-1.2 jar file, you should see both SQL Server and Sybase aging uncle added to your list of options when you create a new database connection (You get Oracle and Access as value default).

 
Step 3. Set up your new database connection
Now you can create a new connection to MS SQL database that you were an Oracle connection. The only difference is that you must select the tab instead of SQLServer Oracle. If you do not see this tab, then something went wrong - either you got the wrong jar file or not load properly.


Troubleshooting
Needless to say that technologies as complex as sometimes occur with unexpected difficulties. For example, you may find that you must copy the file in the SQL Developer SSO ntlmauth.dll jdk / jre / bin. Or, you may need to check your Configuration Manager database SQL Server to make sure you have TCP / IP is enabled and port 1433 by default. If you use the controller, recommended that I have a FAQ that can help identify certain types of errors.


If support for multiple databases, you may not need to install a set of tools. Oracle SQL Developer can be combined with different flavors of databases like MS SQL. Just download and load the JDBC driver, and you should be able to create a new database connection as easily as you can with Oracle.



Tags:oracle ms sql,developer oracle ms sql,oracle tutorialoracle dba training,oracle dba jobs,oracle database,oracle software,oracle jobs,oracle dba jobs in canada,oracle dba jobs in usa,oracle database management,oracle consulting,oracle database tools,oracle 11g,oracle dba jobs,