Category Archives: Oracle

Oracle, Oracle 10g

Oracle Temporary Tablespace Movement

Leave a comment

Temporary tablespace’s in the Oracle environment are used for sorting and other non-permanent data operations. Although the space in a temporary tablespace is not permanent, a database of any size cannot properly function without the use of very large amounts of temporary tablespace. This procedure covers the movement of a temporary tablespace after space on the original storage device becomes insufficient. We will cover the movement of the TEMPORARY tablespace from the mount point data1 to mount point data3.

1. Find all tablespaces defined as TEMPORARY

SQL> select tablespace_name from dba_tablespaces where contents=’TEMPORARY’;

TABLESPACE_NAME
——————————
TEMP
TEMPORARY

SQL>

2. Locate the temp_files in the temporary tablespace.

SQL> select file_name from dba_temp_files where tablespace_name = ‘TEMPORARY’;

FILE_NAME
/data1/temporary01.dbf
/data1/temporary02.dbf
/data1/temporary03.dbf

SQL>

3. Find size of current files

SQL> select file_name, bytes/1024/1024 from dba_temp_files where tablespace_name = ‘TEMPORARY’;
FILE_NAME BYTES/1024/1024
—————— —————
/data1/temporary01.dbf 4096
/data1/temporary02.dbf 2048
/data1/temporary03.dbf 2048

SQL>

4. Add new temp files on your new mount point to the tablespace temporary with the command ‘alter tablespace’.

SQL> alter tablespace temporary add tempfile ‘/data3/temporary01.dbf’
size 4096m;
Tablespace altered.

SQL> alter tablespace temp add tempfile ‘/data3/temprary02.dbf’
size 2048m;
Tablespace altered.

SQL> alter tablespace temp add tempfile ‘/data3/temprary03.dbf’
size 2048m;
Tablespace altered.

5. Shutdown database to free all in-use segments of the temporary tablespace TEMPORARY with the command ‘shutdown immediate’.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

6. Startup the database in restricted mode using the command ‘startup restict’ so no user activity can pickup segments from the tablespace TEMPORARY.

SQL> startup restrict
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 159386500 bytes
Database Buffers 444596224 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL>

7. Drop the old datafiles using the command ‘alter database’

SQL> alter database tempfile ‘/data1/temprary01.dbf’
drop including datafiles;
Database altered.

SQL> alter database tempfile ‘/data1/temprary02.dbf’
drop including datafiles;
Database altered.

SQL> alter database tempfile ‘/data1/temprary03.dbf’
drop including datafiles;
Database altered.

8. View the new temp files with the following command.

SQL> select file_name from dba_temp_files where tablespace_name=’TEMPORARY’;
FILE_NAME
———————–
/data3/temprary01.dbf
/data3/temprary02.dbf
/data3/temprary03.dbf

SQL>

9. Open the database for normal use with ‘alter system’ command.

SQL> alter system disable restricted session;

System altered.

SQL>

This completes the movement of temporary tablespaces tempfiles.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g

Movement of Oracle datafiles

Leave a comment

Oracle allows for the movement of datafiles from one storage device to another when you exhaust space on the original device without having to recreate your database. In this procedure we will move the datafiles located on LUN data4 to a new LUN on data1.

1. Find all datafiles located on mount point /data4 and view size in MB.

SQL> select tablespace_name, bytes/1024/1024, file_name from dba_data_files where
file_name like ‘%data3%’;

SYSTEM /data4/oradata/system01.dbf
DRSYS /data4/oradata/drsys01.dbf
TOOLS /data4/oradata/tools01.dbf
OEM /data4/oradata/oem01.dbf
USERDATA /data4/oradata/userdata_01.dbf
USERDATA /data4/oradata/userdata_02.dbf
USERDATA /data4/oradata/userdata_03.dbf
USERDATA /data4/oradata/userdata_04.dbf

SQL> select SUM(bytes)/1024/1024 from dba_data_files
13:15:46 2 where file_name like ‘%data4%’;

SUM(BYTES)/1024/1024
——————–
32546

2. The data files in tablespace USERDATA amount to 32GB of space,
we will move these data files to the new mount point /data1.

3. Shutdown the oracle database with the command ‘shutdown immediate’.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

4. Startup the oracle database in restricted mode, this will prevent non-DBA users from accessing the database while you moving the data files. Use the command ‘startup restrict’.

SQL> startup restrict
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 155192196 bytes
Database Buffers 448790528 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL>

5. Take the tablespace USERDATA offline.

SQL> alter tablespace USERDATA offline;

Tablespace altered.

SQL>

6. View the status of the tablespace USERDATA to ensure that it is in an offline state.

SQL> select tablespace_name, status from dba_tablespaces where tablespace_name =
‘USERDATA’;

TABLESPACE_NAME STATUS
—————————— ———
USERDATA OFFLINE

SQL>

7. Shutdown the database with the command ‘shutdown immediate’.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

8. Move the data files of tablespace USERDATA to their new locations with OS command ‘cp’. One could use the command mv, which is normally faster, however if anything goes wrong you do not have a backup of your datafile.

cp /data4/oradata/userdata_01.dbf /data1/oradata/userdata_01.dbf
cp /data4/oradata/userdata_02.dbf /data1/oradata/userdata_02.dbf
cp /data4/oradata/userdata_03.dbf /data1/oradata/userdata_03.dbf
cp /data4/oradata/userdata_04.dbf /data1/oradata/userdata_04.dbf

9. Open the database in a nomount state with ‘startup nomount’.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 155192196 bytes
Database Buffers 448790528 bytes
Redo Buffers 7135232 bytes
SQL>

10. Change the location of the data files moved in the control file with the command ‘alter database rename datafile’.

SQL> alter database rename datafile ‘/data4/oradata/userdata_01.dbf’ to
‘/data1/oradata/userdata_01.dbf’;
Database alerted.

SQL> alter database rename datafile ‘/data4/oradata/userdata_02.dbf’ to
‘/data1/oradata/userdata_02.dbf’;
Database alerted.

SQL> alter database rename datafile ‘/data4/oradata/userdata_03.dbf’ to
‘/data1/oradata/userdata_03.dbf’;
Database alerted.

SQL> alter database rename datafile ‘/data4/oradata/userdata_04.dbf’ to
‘/data1/oradata/userdata_04.dbf’;
Database alerted.

11. Mount the database with the command ‘alter database mount’.

SQL> alter database mount;

Database altered.

SQL>

12. Open the database with command ‘alter database open’.

SQL> alter database open;

Database altered.

SQL>

13. Bring the tablespaces back online.

SQL> alter tablespace USERDATA online;

Tablespace altered.

SQL>

14. Validate that the tablespace is online with the following SQL.

SQL> select tablespace_name, status from dba_tablespaces where tablespace_name
2 ‘USERDATA’;

TABLESPACE_NAME STATUS
—————————— ———
USERDATA ONLINE

SQL>

Completes movement of Oracle data files.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle

Setup Automatic Oracle STATSPACK jobs

Leave a comment

Oracle provides the package DBMS_JOB(Pre 10g) and DBMS_SCHEDULER(Post 10g) to allow for the automation of pre-scheduled routine tasks in the Oracle database, just as they are performed by CRONTAB on UNIX and the Scheduler on Windows. This article will explain the use of DBMS_JOB and DBMS_SCHEDULER to take automatic snapshots for the STATSPACK utility every two hours. It is important to realize that STATSPACK stores information related to system performance in the database, thus the more often you take snapshots, the great resources you will be consuming.

1. The DBMS_JOB package contains several procedures and functions for creation, modification and deletion of scheduled jobs for Oracle 9i and earlier versions. For Oracle 10g and higher versions use DBMS_SCHEDULER package, which provides for greater flexibility and control over job creation. A complete listing of the procedures and functions can be retrieved in SQL*PLUS with the command desc for both packages.

2. Use the SUBMIT procedure to create a new job to execute. The below statement will execute once every two hours.

For 9i or earlier use dbms_job.submit.

variable jobno number;

exec dbms_job.submit(job=>:jobno, what=>’begin statspack.snap;
end;’,next_date=>sysdate,interval=>’sysdate+1/12′);

For 10g or higher use dbms_scheduler.create_job (NOTE: start_date is a timestamp not a date value, so use the following query to determine the timestamp format for your database (

Oracle

Taking STATSPACK Snapshots and producing reports

Leave a comment

Oracle’s STATSPACK performance diagnostic tool allows you to
analyze the performance of your Oracle RDBMS. This Analysis can be used to adjust parameters, determine high wait time events, and determine the SQL which causes the highest load on your system.

1. Logon to your Oracle database as sysdba.

mylunix:mydb:/mydb:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Tue Aug 2 07:38:57 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

2. The STATSPACK package is used to take snapshots, by calling the procedure snap. Take your first snapshot at the beginning of your peak usage and at evenly spaced intervals in between. IE: If your peak usage is between 8AM and 4PM, start taking your snapshots at 8AM and every 2 hours ( 10AM, 12PM, 2PM, and 4PM). This will allow you to take reports during the entire span of your peak usage and at intervals in between.

SQL> exec statspack.snap

PL/SQL procedure successfully completed.

SQL>

3. Once you are ready to take a report from you snapshots, you will have to find the snapshot ids which identify a specific snapshot range. This can be done by querying the table PERFSTAT.STATS$SNAPSHOT.

SQL> select snap_id, to_char(snap_time, ‘DD-MON-YY HH:MI:SS’) from
perfstat.stats$snapshot
where snap_time between to_date(’02-AUG-09 07:00:00′,’DD-MON-YY HH24:MI:SS’)
and to_date(’02-AUG-09 17:00:00′,’DD-MON-YY HH24:MI:SS’); 2 3

SNAP_ID TO_CHAR(SNAP_TIME,
———- ——————
1 02-AUG-09 08:22:29
11 02-AUG-09 10:00:07
21 02-AUG-09 12:00:03
31 02-AUG-09 14:00:12
41 02-AUG-09 15:00:48
42 02-AUG-09 14:00:47

6 rows selected.

SQL>

4. Once you have your high and low snap ids, call the Oracle script
$ORACLE_HOME/rdbms/admin/spreport.sql to generate an external trace file of your database performance. This script requires three input variables:
beginning snap_id, ending snap_id, and OS file name.

SQL> @?/rdbms/admin/spreport.sql

Listing all Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level Comment
———— ———— ——— —————– —– ——————–
mydb mydb 1 02 Aug 2009 08:22 5
11 02 Aug 2009 10:00 5
21 02 Aug 2009 12:00 5
31 02 Aug 2009 14:00 5
41 02 Aug 2009 15:00 5
42 02 Aug 2009 16:00 5

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1

Enter value for end_snap: 42

5. Your report will be generated in the current working directory.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 –
64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Dart:mylinux:/mydb>ls -lrt
-rw-r–r– 1 oracle dba 197698 Aug 2 16:38 sp_1_42.lst

This completes execution of STATSPACK SNAPSHOTS and generation of
STATSPACK reports.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle

Setup of Oracle STATSPACK Diagnostics tool

Leave a comment

Oracle STATSPACK is a tool that allows you to analyze the performance of your Oracle RDBMS over a specific period of time. Normally the analysis of how your database is performing should be based over a period of the highest usage, but over a length of time that would avoid short term peaks to skew your results. STATSPACK has been in use since Oracle 8i with continual enhancements with every new version release. In this article, we will show how to install and prepare your database for the use of STATSPACK.

Also see:
Taking STATSPACK snap shots and producing reports.
Setup of STATSPACK scheduled jobs.

1. Logon to Oracle as sys dba

MyLinux:mydb:/mydb:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Mon Aug 3 09:03:30 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

2. The STATSPACK tool is owned by the user PERFSTAT and specific storage should be created to avoid interference with your database. In this step will create specific storage for PERFSTAT user.

SQL> create tablespace perfstat datafile ‘/mydb/ordata/perfstat01.dbf’ size 2096m;

Tablespace created.

SQL> alter tablespace perfstat add datafile ‘/mydb/ordata/perfstat02.dbf’ size 2096m;

Tablespace altered.

3. The user PERFSTAT and all components of the STATSPACK tool are created with the ORACLE provided script SPCREATE.SQL. Execute the script $ORACLE_HOME/rdbms/admin/spcreate.sql to create the perfstat user and data structures.

SQL> @?/rdbms/admin/spcreate.sql

4. The SPCREATE.SQL script accept three parameters before execution: Password; Default Tablespace; and Temp Tablespace. Enter the following when prompted:
PERFSTAT Password
Default Tablespace
Temp Tablespace

5. After execution has occurred, check the following list files to determine that no errors occurred.

spcusr.lis – Creates the perfstat user account.
spctab.lis – Creates all data structure under the perfstat user account.
spcpkg.lis – Creates the perfstat packages.

6. This completes the build of Oracle’s STATSPACK diagnostic tool in your database.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, Oracle, Oracle 10g, oracle 9i, SQL

Oracle auditing of user’s connections

Leave a comment

Oracle provides the ability to audit a great range of activities within the Oracle RDBMS environment. An administrator has the ability to audit as much as the activities of the entire database all the way to any select, insert, or update on a single table. Care should be taken on what you wish to audit, due to the amount of disk space required to store all of this information. In this article, we will discuss the auditing of connects by a single user account.

1. Logon to SQL*PLUS as sysdba

mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Mon Jul 27 19:13:26 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

2. Ensure that audit_trail and audit_sys_operations parameters are set to TRUE.

SQL> show parameter audit

NAME TYPE VALUE
———————————— ———– ——————————
audit_file_dest string /U01/ORACLE/PRODUCT/10.2.0/ADMIN
/ORCL10G/ADUMP
audit_sys_operations boolean FALSE
audit_trail string NONE

SQL> alter system set audit_trail=TRUE scope=spfile;

System altered.

SQL> alter system set audit_sys_operations= TRUE scope=spfile;

System altered.

SQL>

3. Restart the database if you had to edit the init parameters.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 209718148 bytes
Database Buffers 394264576 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL>

4. Ensure that the new values took effect.

SQL> show parameter audit

NAME TYPE VALUE
———————————— ———– ——————————
audit_file_dest string /U01/ORACLE/PRODUCT/10.2.0/ADMIN
/ORCL10G/ADUMP
audit_sys_operations boolean TRUE
audit_trail string TRUE
SQL>

5. Begin auditing of acccount which you suspect is causing an issue.

SQL> audit connect by ljcatt;

Audit succeeded.

SQL>

6. Logon as the user and perform some actions.

SQL> connect ljcatt/ljcatt
Connected.
SQL>

7. Log back in to oracle as sysdba

SQL> connect sys as sysdba
Connected.
SQL>

8. Perform the following SQL to extract the connection information for the USER LJCATT.

SQL> select userhost, terminal, timestamp, action_name from dba_audit_session wh
ere username=’LJCATT’;

USERHOST TERMINAL TIMESTAMP ACTION_NAME
———— ——– ——— ——————
Mylinux Mylinux1 27-JUL-09 LOGON
Mylinux Mylinux1 27-JUL-09 LOGOFF

9. There is a wealth of information that can be obtained by looking at the various dictionary views which cover the Oracle auditing process. You can easily see if someone is accessing your database in an improper way or manipulating data with a direct connection such as SQL*PLUS instead of through an application. This article covered a brief overview of auditing a single user’s connections; however it is easy to see the power that Oracle auditing can provide.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g, oracle 9i, SQL

Recreate Oracle View

Leave a comment

Oracle provides views to allow you to hide the structures of under lying tables, to combine multiple tables into a single logical structure, and to reduce the complexity of SQL being generated against a database. Views allow the developer the flexibility to give users the look and feel of the database they desire, while maintaining a normalized structure under the covers. In this article we will review the SQL to generate a view’s DDL for review or transport.

1. Connect to your Oracle database server and logon to SQL*PLUS.

MyLinux:>sqlplus ‘\ as sysdba’
SQL*Plus: Release 10.2.0.4.0 – Production on Tue Jul 20 23:51:33 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

2. Logon as the user SCOTT and generate the view TEST_VIEW to demonstrate the DDL extraction.

SQL> grant create view to scott;

Grant succeeded.

SQL> connect scott/tiger
Connected.
SQL> create view TEST_VIEW as
2 select emp.ename as employee, dept.dname as department, bonus.sal as salary
from
3 dept, emp, bonus where dept.deptno=emp.deptno and emp.ename=bonus.ename;

View created.

SQL>

3. Execute the following PL/SQL block to generate your DDL.

set serveroutput on
declare

v_ct number;
v_owner varchar2(30):=’SCOTT’;

begin

for v_view in(select view_name, text from dba_views where owner=v_owner)
loop

dbms_output.put_line(‘create view ‘||v_view.view_name||’ as’);
dbms_output.put_line(v_view.text||’;’);

end loop;
end;
/

SQL> connect / as sysdba
Connected.
SQL>
SQL> set serveroutput on
SQL> declare
2
3 v_ct number;
4 v_owner varchar2(30):=’SCOTT’;
5
6 begin
7
8 for v_view in(select view_name, text from dba_views where owner=v_owner)
9 loop
10
11 dbms_output.put_line(‘create view ‘||v_view.view_name||’ as’);
12 dbms_output.put_line(v_view.text||’;’);
13
14 end loop;
15 end;
16 /
create view TEST_VIEW as
select emp.ename as employee, dept.dname as department, bonus.sal as salary from
dept, emp, bonus where dept.deptno=emp.deptno and emp.ename=bonus.ename;

PL/SQL procedure successfully completed.

SQL>
SQL>

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, Oracle, Oracle 10g, oracle 9i, SQL

Oracle auditing insert, update, and delete on a table

Leave a comment

Oracle provides the ability to audit a great range of activities within the Oracle RDBMS environment. Care should be taken on what you wish to audit, due to the amount of disk space required to store all of this information. In this article we will discuss the auditing of insert, update, and deletes by user account access.

1. Logon to SQL*PLUS as sysdba

mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Fri Jul 03 19:18:21 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

2. Ensure that audit_trail is set to DB_EXTENDED and audit_sys_operations parameter is set to TRUE. Note: It is important to realize that setting the AUDIT_TRAIL to DB_EXTENDED will dramatically increase storage, so monitor this closely.

SQL> show parameter audit

NAME TYPE VALUE
———————————— ———– ——————————
audit_file_dest string /U01/ORACLE/PRODUCT/10.2.0/ADMIN
/ORCL10G/ADUMP
audit_sys_operations boolean FALSE
audit_trail string NONE

SQL>

System altered.

SQL> alter system set audit_sys_operations= TRUE scope=spfile;

System altered.

SQL>

3. Restart the database if you had to edit the init parameters.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 209718148 bytes
Database Buffers 394264576 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL>

4. Ensure that the new values took effect.

SQL> show parameter audit

NAME TYPE VALUE
———————————— ———– ——————————
audit_file_dest string /U01/ORACLE/PRODUCT/10.2.0/ADMIN
/ORCL10G/ADUMP
audit_sys_operations boolean TRUE
audit_trail string TRUE
SQL>

5. Execute the audit command below to begin monitoring changes to the table SCOTT.DEPT.

SQL> audit insert, update, delete on scott.dept by session;

Audit succeeded.

SQL>

6. Connect to the database as the user LJCATT and perform a insert, update and delete operation on the table scott.dept.

SQL> connect ljcatt/ljcatt
Connected.
SQL> insert into scott.dept(deptno, dname, loc) values(50,’PARTNERS’,’NEW YORK’)
;

1 row created.

SQL> commit;

Commit complete.

SQL> update scott.dept set loc=’WASHINGTON’ where deptno=50;

1 row updated.

SQL> commit;

Commit complete.

SQL> delete from scott.dept where deptno=50;

1 row deleted.

SQL> commit;

Commit complete.

SQL>

7. Login as sysdba again.

SQL> connect sys as sysdba
SQL>

8. Execute the following SQL to extract the statements executed by LJCATT.
“select timestamp, sql_text from dba_audit_object where username=’LJCATT’;”

SQL> select timestamp, sql_text from dba_audit_object where username=’LJCATT’;

TIMESTAMP SQL_TEXT
——— ——————————————————-
27-AUG-09 update scott.dept set loc=’WASHINGTON’ where deptno=50
27-AUG-09 insert into scott.dept(deptno, dname, loc) values(50,’PARTNERS’,’NEW YORK’)
27-AUG-09 delete from scott.dept where deptno=50

SQL>

9. That completes the use of Oracle Auditing to monitor the changes to the Oracle database by users.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g, oracle 9i, SQL

Disabling and Enabling Oracle Jobs with DBMS_JOB

Leave a comment

Oracle provides the ability to schedule predetermined jobs via the package DBMS_JOB, just as you would in a UNIX environment with CRON or the Windows environment with SCHEDULER. Normally, a production database will have several dozen pre-scheduled jobs executing at various times of the day. There are times when you will need to disable a JOB or all jobs due to various events. This article explains the use of procedure DBMS_JOB.BROKEN which allows you to disable a job and re-enable it once you are finished. This procedure will work in Oracle 8, 8i, 9i, 10g, and 11g.

NOTE: only the user whom own a job can modify with the package DBMS_JOB, if you logon to the database as an administrator and want to disable jobs you must use the package DBMS_IJOB.

1. Logon to SQL*PLUS as sysdba

Mylinux> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.3.0 – Production on Tue May 25 10:19:38 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL>

2. To disable all jobs on the system use the following SQL statement to generate your SQL:
“select ‘exec dbms_ijob.broken(‘||job||’,true);’ from dba_jobs;”

SQL> select ‘exec dbms_ijob.broken(‘||job||’,true);’ from dba_jobs;

‘EXECDBMS_IJOB.BROKEN(‘||JOB||’,TRUE);’
———————————————————————
exec dbms_ijob.broken(1,true);
exec dbms_ijob.broken(2,true);
exec dbms_ijob.broken(4,true);
exec dbms_ijob.broken(5,true);
exec dbms_ijob.broken(41,true);
exec dbms_ijob.broken(42,true);
exec dbms_ijob.broken(370,true);
exec dbms_ijob.broken(371,true);
exec dbms_ijob.broken(1605,true);
exec dbms_ijob.broken(2204,true);
exec dbms_ijob.broken(2224,true);
exec dbms_ijob.broken(2225,true);
exec dbms_ijob.broken(2245,true);

13 rows selected.

SQL>

3. Execute the SQL generated by the step above to disable all jobs.

exec dbms_ijob.broken(2224,true);
exec dbms_ijob.broken(2225,true);
exec dbms_ijob.broken(2245,true);
PL/SQL procedure successfully completed.

SQL>
PL/SQL procedure successfully completed.

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

4. To re-enable all jobs on the system use the following SQL statement to generate your SQL:
“select ‘exec dbms_ijob.broken(‘||job||’,false);’ from dba_jobs;”

SQL> select ‘exec dbms_ijob.broken(‘||job||’,false);’ from dba_jobs;

‘EXECDBMS_IJOB.BROKEN(‘||JOB||’,FALSE);’
———————————————————————-
exec dbms_ijob.broken(1,false);
exec dbms_ijob.broken(2,false);
exec dbms_ijob.broken(4,false);
exec dbms_ijob.broken(5,false);
exec dbms_ijob.broken(41,false);
exec dbms_ijob.broken(42,false);
exec dbms_ijob.broken(370,false);
exec dbms_ijob.broken(371,false);
exec dbms_ijob.broken(1605,false);
exec dbms_ijob.broken(2204,false);
exec dbms_ijob.broken(2224,false);
exec dbms_ijob.broken(2225,false);
exec dbms_ijob.broken(2245,false);

5. Execute the SQL generated by the step above to enable all jobs.

exec dbms_ijob.broken(370,false);
exec dbms_ijob.broken(371,false);
exec dbms_ijob.broken(1605,false);
exec dbms_ijob.broken(2204,false);
SQL>
PL/SQL procedure successfully completed.

SQL>
PL/SQL procedure successfully completed.

SQL>
PL/SQL procedure successfully completed.

6. This completes the disablement and enablement of jobs with DBMS_JOB package.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com