Using msdb.dbo.sp_update_job one can disable or enable jobs, the job_id can be retrieved from msdb.dbo.sysjobs
To enable
EXEC msdb.dbo.sp_update_job @job_id, @enabled = 1
To disable
EXEC msdb.dbo.sp_update_job @job_id, @enabled = 0
In 10g using dbms_flashback one can flashback using timestamp or SCN. If a user accidentally deletes rows or updates rows one can use this to review the data without need to have flashback turned on in the database. Note: SYS can’t use dbms_flashback to flashback, Oracle will return “ORA-08185: Flashback not supported for user SYS” when SYS will try to enable flashback.
Example:
– shows flashback is off
SQL> select flashback_on from V$database;
FLASHBACK_ON
——————
NO
– declare a variable to store current SCN
SQL> variable current_scn number
– Get the current SCN and initialize current_scn
SQL> execute :current_scn := sys.dbms_flashback.get_system_change_number();
PL/SQL procedure successfully completed.
– print the current scn
SQL> print current_scn;
CURRENT_SCN
———–
389907
– query the table, shows no rows
SQL> select * FROM test;
no rows selected
– insert the row and commit
SQL> insert into test values ( 1 );
1 row created.
SQL> commit;
Commit complete.
– flashback to before insert was executed, so select should return no rows
SQL> execute dbms_flashback.enable_at_system_change_number(:current_scn);
PL/SQL procedure successfully completed.
– as expected select returned no rows
SQL> select * FROM test;
no rows selected
– disable flashback
SQL> execute dbms_flashback.disable;
PL/SQL procedure successfully completed.
Note: If there is a truncate executed on the table then data can’t be read from the table after enabling flashback Oracle will return “ORA-01466: unable to read data – table definition has changed” on truncated table.
Using the package DBMS_SCHEDULER one can enable/disable jobs.
To disable job: This disables the job from running
SQL> exec dbms_scheduler.disable(‘GATHER_STATS_JOB’);
PL/SQL procedure successfully completed.
– check job status
SQL> select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = ‘GATHER_STATS_JOB’;
JOB_NAME ENABL
————————- —–
GATHER_STATS_JOB FALSE
To enable job:
SQL> exec dbms_scheduler.enable(‘GATHER_STATS_JOB’);
PL/SQL procedure successfully completed.
– check job status
SQL> select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = ‘GATHER_STATS_JOB’;
JOB_NAME ENABL
————————- —–
GATHER_STATS_JOB TRUE
If you have ‘&’ as part of your data that is part of the string and needs to inserted/updated/deleted and you are using SQL*Plus, you would need to do the following as ‘&’ has a special meaning in SQL*Plus as by default SQL*Plus considers it as a variable.
E.g: SQL> select ‘xxxx &abc’ from dual;
In the example SQL*Plus will prompt to enter a value for variable abc
So to ensure ‘&’ is taken in it’s literal value and it doesn’t prompt, one can do one of the following if you need to run SQL scripts from SQL*Plus
1) SET DEFINE OFF — Turns off substitution of variables
2) SET DEFINE # — Change the substitution character to # or to a character that is not part of the script
3) Change the above sample SQL to the following
SQL> select ‘xxxx &’ || ‘abc’ from dual;