Example shows how “DISABLE TABLE LOCK” works

Following example shows what happens when locks are disabled on the TABLE. -- disable lock on a table SQL> alter table t disable table lock; Table altered. -- shows one can’t drop table as table locks are disable SQL> drop table t; drop table t * ERROR at line 1: ORA-00069: cannot acquire lock —…

How to find and enable/disable parallel DML, DDL, Query?

In the view v$session there are columns that can tell if parallel DDL, DML, Query is enabled/disabled. PDML_ENABLED and PDML_STATUS – indicates is Parallel DML operation is enabled/disabled, the default is DISABLED. PDDL_STATUS – Indicates if parallel DDL is enabled/disabled, by the default value is ENABLED PQ_STATUS – Indicates if parallel query is enabled/disabled, the…

How to use DBMS_FLASHBACK?

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”…

How to enable/disable a scheduled job?

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. —…

Enabling Flashback Database

The following are the requirements for enabling Flashback Database: – Your database must be running in ARCHIVELOG mode, because archived logs are used in the Flashback Database operation. – You must have a flash recovery area enabled, because flashback logs can only be stored in the flash recovery area. – For Real Application Clusters databases,…