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?
The following shows how exceptions can be captured. — Create exceptions table SQL> @?/rdbms/admin/utlexcpt.sql Table created. SQL> desc exceptions; Name Null? Type —————————————– ——– ———————— ROW_ID ROWID OWNER VARCHAR2(30) TABLE_NAME VARCHAR2(30) CONSTRAINT VARCHAR2(30) SQL> select * from xx; X ———- 11 11 — add constraint but disable it SQL> alter table xx add constraint xx_pk primary key (x) disable; Table altered. — enable constraint and … Continue reading How to generate a list of exceptions generated due to constraint violations?
SQL> SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON NAME TYPE VALUE ———————————— ———– —————————— sec_case_sensitive_logon boolean TRUE SQL> SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE; System altered. Continue reading How to turn off case sensitive in 11g
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?
Using Oracle SID and SERIAL# can turn trace on/off a Oracle session that has already started. The values of SID and serial# can be obtained from GV$SESSION. This will create trace file in directory set by the parameter user_dump_dest. To find the SID and SERIAL# you want to debug you can query GV$session to query by program, username, machine, terminal. To start trace: SQL> exec … Continue reading Enable trace in a running session from PL/SQL or SQL*Plus
