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 — table locks disabled for T

-- shows one can’t truncate table as table locks are disable
SQL> truncate table t;
truncate table t
*
ERROR at line 1:
ORA-00069: cannot acquire lock — table locks disabled for T

-- DML is okay to run
SQL> delete from t;
1 row deleted.

-- new columns can be added
SQL> alter table t add tt varchar2(1);
Table altered.

-- columns can’t be modified
SQL> alter table t modify tt varchar2(10);
alter table t modify tt varchar2(10)
*
ERROR at line 1:
ORA-00069: cannot acquire lock — table locks disabled for T

-- columns can’t be dropped
SQL> alter table t drop column tt;
alter table t drop column tt
*
ERROR at line 1:
ORA-00069: cannot acquire lock — table locks disabled for T

-- enable locking on the table
SQL> alter table t enable table lock;
Table altered.

-- table now can be dropped
SQL> drop table t;
Table dropped.

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 default value is ENABLED

The values can set at session level.

Example:
SQL> select PDML_ENABLED, PDML_STATUS, PDDL_STATUS, PQ_STATUS FROM V$session where sid = (select sid from v$mystat
where rownum = 1);

PDM PDML_STA PDDL_STA PQ_STATU
— ——– ——– ——–
NO DISABLED ENABLED ENABLED

-- enables parallel DML
SQL> alter session enable parallel dml;

Session altered.

-- Display parallel DML is set to enabled
SQL> select PDML_ENABLED, PDML_STATUS FROM V$session where sid = (select sid from v$mystat where rownum = 1);

PDM PDML_STA
— ——–
YES ENABLED

-- To disable parallel DML
SQL> alter session disable parallel dml;

Session altered.

-- To disable parallel DDL
SQL> alter session disable parallel ddl;

Session altered.

-- To disable parallel query
SQL> alter session disable parallel query;

Session altered.

-- Print the parallel DDL, DML and Query for the current session
SQL> select PDML_ENABLED, PDML_STATUS, PDDL_STATUS, PQ_STATUS FROM V$session where sid = (select sid from v$mystat where rownum = 1);

PDM PDML_STA PDDL_STA PQ_STATU
— ——– ——– ——–
NO DISABLED DISABLED DISABLED

How to generate a list of exceptions generated due to constraint violations?

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 generate any exceptions to exceptions table
SQL> alter table xx enable validate constraint xx_pk exceptions into exceptions;
alter table xx enable validate constraint xx_pk exceptions into exceptions
*
ERROR at line 1:
ORA-02437: cannot validate (SYSTEM.XX_PK) – primary key violated

— List exceptions
SQL> select * from exceptions;
ROW_ID OWNER TABLE_NAME
—————— —————————— ——————————
CONSTRAINT
——————————
AAAErDAABAAAHy5AAB SYSTEM XX
XX_PK
AAAErDAABAAAHy5AAA SYSTEM XX
XX_PK

SQL> select * from xx where rowid IN ( <AAAErDAABAAAHy5AAB>, <AAAErDAABAAAHy5AAA> );

X
———-
11
11

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.

— 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

Enable trace in a running session from PL/SQL or SQL*Plus

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 dbms_support.start_trace_in_session(<SID>, <Serial#>);

To stop trace:
SQL> exec dbms_support.stop_trace_in_session(<SID>, <Serial#>);

To start trace with Wait Event data with SQL trace
exec sys.dbms_support.start_trace(<SID>, <Serial#>,waits => TRUE, binds=> TRUE);

If one needs to turn timed statistics on for another session, one can execute it through the package dbms_system.

SQL> exec sys.dbms_system.set_bool_param_in_session(sid => <sid>, serial# => <seral#>, parnam => ‘TIMED_STATISTICS’, bval => true);

To install the DBMS_SUPPORT package if it doesn’t exists in the database run the following script to create/install the package and setup access for other users other than sysdba.
SQL> connect / AS SYSDBA
SQL> @?/rdbms/admin/dbmssupp.sql
SQL> GRANT execute ON dbms_support TO SCOTT_DBA;
SQL> CREATE PUBLIC SYNONYM dbms_support FOR dbms_support;

In 10g there is a new utility DBMS_MONITOR which you can use to achieve the, eg:
exec dbms_monitor.session_trace_enable (session_id=>139, serial_num=>53, waits=>true, binds=>false); For additional information you can the following article by Oracle http://www.oracle.com/technology/oramag/oracle/04-sep/o54talking.html

One can also turn trace on an already running session using oradebug, once session id (SID) has been identified.
SQL> select p.PID, p.SPID, s.SID
from v$process p,v$session s
where s.paddr = p.addr
and s.sid = 62;

PID SPID SID
———- ———— ———-
68 5243378 62

1 row selected.

SQL> connect /as sysdba
— attach to session by OS process id
SQL> oradebug setospid 5243378;
Oracle pid: 68, Unix process pid: 5243378, image: oracle@hostname (TNS V1-V3)
— sets maximum dump file size to be unlimited
SQL> oradebug unlimit
Statement processed.
— turn trace on
SQL> oradebug event 10046 trace name context forever,level 12
Statement processed.
— after gathering information from the same session which is already attached to ospid 5243378 turn off the trace
SQL> oradebug event 10046 trace name context off
Statement processed.

Using ALTER SESSION
SQL> ALTER SESSION SET EVENTS='10046 trace name context forever, level 12';

Session altered.

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, the flash recovery area must be stored in a clustered file system or in ASM.

To enable logging for Flashback Database, set the DB_FLASHBACK_RETENTION_TARGET initialization parameter and issue the ALTER DATABASE FLASHBACK ON statement. Follow the process outlined here:

Start SQL*Plus and ensure that the database is mounted, but not open. For example:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

Optionally, set the DB_FLASHBACK_RETENTION_TARGET to the length of the desired flashback window in minutes:

SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320; # 3 days

By default DB_FLASHBACK_RETENTION_TARGET is set to one day (1440 minutes).

Note: DB_RECOVERY_FILE_DEST_SIZE must be set before DB_RECOVERY_FILE_DEST is set.
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=536870912;

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u01/oradata/flashback_area';

Enable the Flashback Database feature for the whole database:

SQL> ALTER DATABASE FLASHBACK ON;
SQL> ALTER DATABASE OPEN;

SQL> SELECT flashback_on FROM v$database;

FLASHBACK_ON
——————
YES

-- Turn off flashback, this can be executed when the database is in mount/open state
SQL> ALTER DATABASE FLASHBACK OFF;

Database altered.

-- prints current usage and limit set for flashback recovery, amount of space that can be recalimed and number of files
SELECT * FROM V$recovery_file_dest

-- Amount of space used for different types of files in flashback
SELECT * FROM V$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
———— —————— ————————- —————
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 0 0 0
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0

Enabling ARCHIVELOG Mode

Most of the Oracle production database require you to enable ARCHIVELOG mode for your database. When you enable this mode redo logs will be archived instead of overwritten. The archivelogs are stored in a seperate place usually can backed up regularly by your standard filesystem backup system. Archive logs are utilized by RMAN, Data Guard, Flashback and many others.

Enabling archive mode is simple, just connect to your database in mounted but closed mode (startup mount) and alter the database. But if you don’t tune alittle you’ll run into problems down the road, so lets specify some parameters too. Namely, consider LOG_ARCHIVE_DEST.

Lets start by checking the current archive mode.

[oracle@calora01]$sqlplus “/as sysdba”

SQL> SELECT LOG_MODE FROM V$DATABASE;

LOG_MODE
————
NOARCHIVELOG

So we’re in NOARCHIVELOG mode and we need to change.
We can use a database alter statement.

SQL>create spfile from pfile;
SQL>shutdown immediate;
SQL>startup;
SQL>alter system set log_archive_dest_1=’location=/u02/oradata/ATT/arch’ scope=spfile;
SQL>alter system set log_archive_start=TRUE scope=spfile;
SQL>shutdown immediate;
Remember, if you run out of space in your archive log destination the database will shut down!

Now we can startup the database in mount mode and put it in archivelog mode.
[oracle@calora01]$sqlplus “/as sysdba”

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 184549376 bytes
Fixed Size 1300928 bytes
Variable Size 157820480 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
SQL>alter database mount;
Database mounted

SQL> alter database archivelog;
Database altered.

SQL> alter database open;
Database altered.

You can see here that we put the database in ARCHIVELOG mode by using the SQL statement “alter database archivelog”, but Oracle won’t let us do this unless the instance is mounted but not open. To make the change we shutdown the instance, and then startup the instance again but this time with the “mount” option which will mount the instance but not open it. Then we can enable ARCHIVELOG mode and open the database fully with the “alter database open” statement.

There are several system views that can provide us with information reguarding archives, such as:
V$DATABASE
Identifies whether the database is in ARCHIVELOG or NOARCHIVELOG mode and whether MANUAL (archiving mode) has been specified.
V$ARCHIVED_LOG
Displays historical archived log information from the control file. If you use a recovery catalog, the RC_ARCHIVED_LOG view contains similar information.
V$ARCHIVE_DEST
Describes the current instance, all archive destinations, and the current value, mode, and status of these destinations.
V$ARCHIVE_PROCESSES
Displays information about the state of the various archive processes for an instance.
V$BACKUP_REDOLOG
Contains information about any backups of archived logs. If you use a recovery catalog, the RC_BACKUP_REDOLOG contains similar information.
V$LOG
Displays all redo log groups for the database and indicates which need to be archived.
V$LOG_HISTORY
Contains log history information such as which logs have been archived and the SCN range for each archived log.
Using these tables we can verify that we are infact in ARCHIVELOG mode:
SQL> select log_mode from v$database;

LOG_MODE
————
ARCHIVELOG