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 — table locks disabled for T -- shows one can’t truncate … Continue reading Example shows how “DISABLE TABLE LOCK” works
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 default value is ENABLED The values can set at session … Continue reading How to find and enable/disable parallel DML, DDL, Query?
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 … Continue reading How to use DBMS_FLASHBACK?
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 … Continue reading How to enable/disable a scheduled job?
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 … Continue reading How to run SQL statements in SQL*Plus if you have ‘&’ as part of your data?
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, the flash recovery area must be stored in a clustered … Continue reading Enabling Flashback Database
