How to stop a running job of DBMS_JOB ?

Stop running job with DBMS_JOB

1. Find the Job You Want to stop.
---------------------------------

select j.sid,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select djr.SID,
dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j;


2. Mark the DBMS_JOB as Broken
------------------------------
Use the following command for the job that you have to deal with.

EXEC DBMS_JOB.BROKEN(job#,TRUE);

All this command does is mark the job so that if we get it to stop, it won't start again. Let's make
one thing perfectly clear, after executing this command the job is still running.

As a side note, if you are trying to shut down a database with jobs that run throughout the day,
they may hinder your attempts to bring down the database cleanly. This is a wonderful command
to make sure no jobs are executing during the shutdown process. Just be aware that you will need
to mark the jobs as unbroken when the database comes back up, more on that later.

3. Kill the Oracle Session
------------------------------

Since the job is still running and it isn't going to end soon, you will need to kill the Oracle
session that is executing the job. Use the following command for to kill the job.

ALTER SYSTEM KILL SESSION 'sid,serial#';

select j.sid,
s.spid,
s.serial#,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select djr.SID,
dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j,
(select p.spid, s.sid, s.serial#
from v$process p, v$session s
where p.addr = s.paddr ) s
where j.sid = s.sid;


4. Kill the O/S Process
--------------------------
More often than not the previous step will still leave the job attached to the database and still running.
When this happens you will need to go out to the operating system level and get rid of the process
that has spawned from the running job. In order to do this you must login to the database box and
issue the following command, depending on the type of operating system you have.

For Windows, at the DOS Prompt: orakill sid spid

For UNIX at the command line> kill '9 spid

The orakill is an Oracle command, while kill is a Unix command.

5. Check if the Job is Still Running (by the above query)
------------------------------------

6. Determine the Current Number of Job Queue Processes
------------------------------------------------------


select name,value from v$parameter where name = 'job_queue_processes';


7. Alter the Job Queue to Zero
------------------------------

ALTER SYSTEM SET job_queue_processes = 0;

This will bring down the entire job queue processes.


8. check that No Processes are Using the Job Queue (by the above query)
-----------------------------------------------------------------------


9. Mark the DBMS_JOB as Not Broken
----------------------------------

You can now reset the broken job to not broken so they can run again. Just issue the command.


SQL> EXEC DBMS_JOB.BROKEN(job#,FALSE):


10. Alter the Job Queue to Original Value
--------------------------------

Set the job queue to its' original value so that the jobs can run again.

ALTER SYSTEM SET job_queue_processes = original_value;

11. check that DBMS_JOB Is Running
---------------------------------

Single Sign On

Single Sign On or Same Sign On?
==================================

Just thinking aloud ... one of the things Oracle has introduced is 'userid consistency' across their applications. This is called 'SSO'.

I thought that SSO stood for 'Single Sign On', and the functionality is 'sign on once and use all applications'.

I am finding it actually means 'Same Sign On' and the functionality is 'log in to each of the the different applications one at time, using the same userid & password'. However, signing out does efficiently log me off all applications.

cannot allocate new log

Thread 1 cannot allocate new log
Checkpoint not complete
===================================

If this problem is shown in alert log then increase the redo log group files.
or
Increase the size of redo log files.

It is batter to increase the redo log files.

==============================================
This occurrs when Oracle attempts to reuse a log file but the checkpoint that would flush
the blocks that may have redo in this log file has not yet completed -- we must wait
until that checkpoint completes before we can reuse that file -- thats when this message
is printed. during this time (when we cannot allocate a new log) processing is suspended
in the database while the checkpoint is made to complete ASAP.

The major way to relieve this is to have sufficient log to carry you through peak times.
that way, we can complete the checkpoint while you are not busy.

also make sure your checkpoints happen as fast as they can (eg: enable ASYNC IO or
configure >1 DBWR if ansyc IO cannot be used, make sure disks are not contending with
other apps and so on)

Another way is to make the log files smaller, hence increasing the frequency with which
we checkpoint (log checkpoint interval and other init.ora parameters achieve the same
effect btw).

Kill session statement generate

Kill session statement generate
======================

select'alter system kill session '''||''||sid||','|| serial#||''''||';' kill_statement
from v$session
where username ='SCOTT' ---give user name
andsid=&p_sid;---give sid or omit it

__________________

Load image from directory to oracle table (blob)

Load image from directory to oracle table
==============================
SQL>
SQL> CONN / AS SYSDBA
Connected.
SQL>
SQL> grant dba to halim2 identified by halim2;
SQL> Conn halim2/halim2
SQL> create directory halim_blob as 'D:\halim_blob_dir';
Directory created.
SQL> CREATE TABLE image_table (
dname VARCHAR2(30), -- directory name
sname VARCHAR2(30), -- subdirectory name
fname VARCHAR2(30), -- file name
iblob BLOB)
/
Table created.

SQL> CREATE OR REPLACE PROCEDURE halim_load_image_file (
dir_name VARCHAR2,
sub_dir_name VARCHAR2,
file_name VARCHAR2
) IS
source_file BFILE;
destenation_file BLOB;
length_file BINARY_INTEGER;
BEGIN
source_file := BFILENAME ('HALIM_BLOB', file_name);

-- insert a NULL record to lock
INSERT INTO image_table
(dname, sname, fname, iblob
)
VALUES (dir_name, sub_dir_name, file_name, EMPTY_BLOB ()
)
RETURNING iblob
INTO destenation_file;

-- lock record
SELECT iblob
INTO destenation_file
FROM image_table
WHERE dname = dir_name AND sname = sub_dir_name AND fname = file_name
FOR UPDATE;

-- open the file
DBMS_LOB.fileopen (source_file, DBMS_LOB.file_readonly);
-- determine length
length_file := DBMS_LOB.getlength (source_file);
-- read the file
DBMS_LOB.loadfromfile (destenation_file, source_file, length_file);

-- update the blob field
UPDATE image_table
SET iblob = destenation_file
WHERE dname = dir_name AND sname = sub_dir_name AND fname = file_name;

-- close file
DBMS_LOB.fileclose (source_file);
END halim_load_image_file;
/
Procedure created.
-----------image load by procedure
SQL> EXEC halim_load_image_file('HALIM_BLOB','Image_test','c .jpg');
PL/SQL procedure successfully completed.
SQL> SELECT *
FROM image_table;
SP2-0678: Column or attribute type can not be displayed by SQL*Plus
SQL>
SQL>
----------image load by sql statement
SQL> DECLARE
image_file BLOB;
BEGIN
SELECT iblob
INTO image_file
FROM image_table;

DBMS_OUTPUT.put_line (DBMS_LOB.getlength (image_file));
END;
/
PL/SQL procedure successfully completed.
SQL> set serveroutput on
SQL>
SQL> ed
Wrote file afiedt.buf
DECLARE
image_file BLOB;
BEGIN
SELECT iblob
INTO image_file
FROM image_table;
DBMS_OUTPUT.put_line (DBMS_LOB.getlength (image_file));
END;
/

PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> exit

What happen when not exception handle ininsert statement ?

What happen when not exception handle ininsert statement ?
================================================== ========
----create a test table
CREATE TABLE test( col number);
---add primary ket to that table
ALTER TABLE test
ADD CONSTRAINT pk_test
PRIMARY KEY(col)
USING INDEX;
---create a procedure to insert record into that table
CREATE OR REPLACE PROCEDURE proce_insert IS
BEGIN
INSERT INTO testVALUES(1);
END proce_insert;
/

----create and Run a anonymous block without exception handling
BEGIN
proce_insert;
proce_insert;
END;
/
--generating error and
-- no records inserted as expected
SELECT * FROM test;

----Create and Run a anonymous block with exception handling
BEGIN
proce_insert;
proce_insert;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
--no error occure and one record inserted
SELECT * FROM test;

commit;

delete from test;

How can I increase redo log file size

How can I increase redo log file size (no archive mode)
=======================================
1.
SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;


GROUP# ARC STATUS
---------- --- ----------------
1 YES INACTIVE
2 YES INACTIVE
3 NO CURRENT

2. Whichever is not current , drop that group and recreate it with appropriate size.
-----------------------------------------------------------------------------------------------

ALTER DATABASE DROP LOGFILE GROUP 1;

3. Manually drop the physical log file.

ALTER DATABASE ADD LOGFILE GROUP 1 ('G:\ORACLE\PRODUCT\10.2.0\ORADATA\BEFTN\REDO02.LOG') SIZE 60M;

ALTER DATABASE DROP LOGFILE GROUP 2;

ALTER DATABASE ADD LOGFILE GROUP 2 ('G:\ORACLE\PRODUCT\10.2.0\ORADATA\BEFTN\REDO03.LOG') SIZE 60M;


4. Then switch the logfile
--------------------------

alter system switch logfile

---alter system checkpoint ----for doing inactive

----ALTER SYSTEM ARCHIVE LOG CURRENT; ---when archive mode ---Wait for the next group to be archived.

5. Then recreate the remaining group.
-----------------------------------

ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE ADD LOGFILE GROUP 3 ('F:\Oracle\Oradata\NEDARR\REDO03A') SIZE 60M;

ORA-07445/ORA-00600:

ORA-07445/ORA-00600:
==========================

yesterday our client cannot connect to system because when they
are trying to connect its become hang.
we also try to connect with that database user from sqlplus
it also hang. but we can connect with sys user.
some alert log error here....


ORA-07445 : exception encountered: core dump [%s] [%s]

ORA-1688: unable to extend table SYS.WRH$_LATCH_CHILDREN partition WRH$_LATCH__2432247821_10332
by 128 in tablespace SYSAUX
Sat Nov 14 22:18:56 2009
MMON Flush encountered SYSAUX out of space error(1688).

Errors in file /d04/admin/stlbas/udump/stlbas_ora_7294.trc:
ORA-00600: internal error code, arguments: [12333], [0], [0], [0], [], [], [], []
Sat Oct 24 16:44:32 2009
Error occured while spawning process P409; error = 3135



ORA-07445 is a critical error generated by an oracle database when it has receives a fatal
signal from the operating system. This error may be raised in either a foreground or
background process. The most common reason for this error is where a pointer in memory
is overwritten in error by a software problem. In this event, when the pointer is used
to lookup the data it is pointing at, this may likely reference an area of memory without
the process address space. Read more about the cause, interpretation and resolution of the
error ORA-07445 error below

ORA-07445 : What can cause this error

Bug in Oracle Code

Change in reference libraries [ If you have recently applied OS patches, it may be wise to
relink the binaries ]
Code changes in application that causes invalid/null value conditions where a valid value is
expected
Software issues like incorrect Libaries due to invalid environment variables primarily related
to PATH and LD_LIBRARY_PATH
ORA-07445 : Where is the diagnostic information
When an ORA-07445 occurs in an oracle database, the error is written to the database alertlog.
Along with the error, a trace file is written to the background or user dump dest directory of oracle. Also a core dump may be created in the core dump destination directory.

ORA 7445 : Anatomy of the Error
There are two typical signals seen, i.e, SIGBUS (signal 10, bus error) and SIGSEGV
(signal 11, segmentation violation).

Examples of the Error can be as follows :

ORA-7445: exception encountered: core dump [some_SQL()+268] [SIGBUS] [Invalid address
alignment] [] [] []

ORA-7445: exception encountered: core dump [10] [2100262800] [261278112] [] [] []

Example 1 shows that the error occurred in some_SQL() and a SIGBUS signal was sent.
Example 2 has very less information indicating a signal 10 was received but the function
is not identified.

Oracle metalink provides detailed information on troubleshooting the errors. The Note 153788.1
- Troubleshoot an ORA-7445 Error



ORA-7445 : Resolution and working with Oracle Support
=======================================================
ORA 7445 error cannot just be eliminated by the oracle database administrator due to the complexity
involved in resolving this error.

To reduce the amount of time taken by the Oracle Support team to reduce this error, dba should
try to reproduce the situation and identify the SQL or Operation that can reproduce this error
Open an ITAR with Oracle using metalink and Create an RDA of the Oracle database and update the
TAR with the alert log, trace files and the core dumps that the error has generated
Review. the following notes provided by oracle to successfully resolving an ORA 7445 error.
Documents and Links mentioned below will require that you have access to Oracle Metalink
Metalink Note 153788.1 : ORA 7445 Search Tool
Metalink Note 164968.1 : What is an ORA 7445 Error



Solution
============

we find a trace file with ORA-7445 and then find a select statmente , for which the
error is generate.

At last we find that those sql is define in a ON LOGON DATABASE trigger of our database. here many update and insert statment is present.
for this reason it generating many redo, and for this reason Database is hang.