Script to check the Dead locks and Blocking sessions and table locks

Blokked sessions
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SELECT (
SELECT username
FROM gv$session
WHERE sid=a.sid) blocker,
a.sid, ' is blocking ', (
SELECT username
FROM gv$session
WHERE sid=b.sid) blockee,
b.sid
FROM gv$lock a, gv$lock b
WHERE a.block = 1
AND b.request > 0
AND a.id1 = b.id1
AND a.id2 = b.id2;






SELECT (SELECT username
          FROM gv$session
         WHERE SID = a.SID AND inst_id = a.inst_id) blocker, a.SID,
       ' is blocking ', (SELECT username
                           FROM gv$session
                          WHERE SID = b.SID AND inst_id = b.inst_id) blockee,
       b.SID,b.type,b.lmode,b.request
  FROM gv$lock a, gv$lock b
 WHERE a.BLOCK > 0
   AND b.request > 0
   AND a.id1 = b.id1
   AND a.id2 = b.id2
   AND a.TYPE = b.TYPE
   AND a.inst_id = b.inst_id;












SELECT B.USERNAME,b.sid, B.MACHINE, B.TERMINAL, B.STATUS, A.SQL_FULLTEXT, A.SQL_TEXT FROM V$SQLAREA A, V$SESSION B WHERE A.HASH_VALUE=B.PREV_HASH_VALUE AND b.username='STDUIAPR29A' and sid=1531;

Script to check High water mark

SET LINESIZE 300
SET SERVEROUTPUT ON
SET VERIFY OFF

DECLARE
  CURSOR cu_tables IS
    SELECT a.owner,
           a.table_name
    FROM   all_tables a
    WHERE  a.table_name = Decode(Upper('&&Table_Name'),'ALL',a.table_name,Upper('&&Table_Name'))
    AND    a.owner      = Upper('&&Table_Owner')
    AND    a.partitioned='NO'
    AND    a.logging='YES'
order by table_name;

  op1  NUMBER;
  op2  NUMBER;
  op3  NUMBER;
  op4  NUMBER;
  op5  NUMBER;
  op6  NUMBER;
  op7  NUMBER;
BEGIN

  Dbms_Output.Disable;
  Dbms_Output.Enable(1000000);
  Dbms_Output.Put_Line('TABLE                             UNUSED BLOCKS     TOTAL BLOCKS  HIGH WATER MARK');
  Dbms_Output.Put_Line('------------------------------  ---------------  ---------------  ---------------');
  FOR cur_rec IN cu_tables LOOP
    Dbms_Space.Unused_Space(cur_rec.owner,cur_rec.table_name,'TABLE',op1,op2,op3,op4,op5,op6,op7);
    Dbms_Output.Put_Line(RPad(cur_rec.table_name,30,' ') ||
                         LPad(op3,15,' ')                ||
                         LPad(op1,15,' ')                ||
                         LPad(Trunc(op1-op3-1),15,' '));
  END LOOP;

END;
/

solution for ORA-19809: limit exceeded for recovery files

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 08/27/2013 15:58:
05
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 52428800 bytes disk space from 4327473152 limit
When I went through some forums, I found a lot of solutions, but when I checked with their situations, it doesn't match.

One solution was to increase the db_recovery space. So I checked the space and got this result.
db_recovery size

 SQL> show parameter db_recovery;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_
                                                 area
db_recovery_file_dest_size           big integer 4127M

Space Usage

SQL> select SPACE_USED/1024/1024/1024,SPACE_LIMIT/1024/1024/1024 from  v$recovery_file_dest;

SPACE_USED SPACE_LIMIT
---------- -----------
 800155136  4327473152
Available Space

 SQL>SELECT
NAME,
TO_CHAR(SPACE_LIMIT, '999,999,999,999') AS SPACE_LIMIT,
TO_CHAR(SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE,
'999,999,999,999') AS SPACE_AVAILABLE,
ROUND((SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1)
AS PERCENT_FULL
FROM V$RECOVERY_FILE_DEST;

NAME
--------------------------------------------------------------------------------
SPACE_LIMIT      SPACE_AVAILABLE  PERCENT_FULL
---------------- ---------------- ------------
/u01/app/oracle/fast_recovery_area
   4,327,473,152    3,488,460,800         19.4





 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                 99.63                         0             244
FLASHBACKLOG                  0                         0               0

6 rows selected

*****************************************************

change the FRA (DB_RECOVERY_FILE_DEST) size
delete old archive logs from the list

How to lock/unlock statistics on a table?

How to lock/unlock statistics on a table?

In certain cases you may want to lock statistics in a table in certain cases, for example if you want a table not be analyzed by automatic statistics job but analyze it later or in cases where you want prevent from analyzing statistics in cases where data in the table doesn’t change.

The following example shows how to lock table statistics and what happens when one tries to gather statistics on table that has statistics locked.

— create table
SQL> create table test ( x number );

Table created.

— create index
SQL> create index test_idx on test(x);

Index created.

— shows when stats is not locked the value of stattype_locked is NULL
SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'TEST' and owner = 'SCOTT';

STATT
—–
— lock statistics
SQL> exec dbms_stats.lock_table_stats('scott', 'test');

PL/SQL procedure successfully completed.

— shows when stats is locked the value of stattype_locked is ALL
SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'TEST' and owner = 'SCOTT';

STATT
—–
ALL

— try to gather statistics on locked table
SQL> exec dbms_stats.gather_index_stats('scott', 'test_idx');
BEGIN dbms_stats.gather_index_stats('scott', 'test_idx'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at “SYS.DBMS_STATS”, line 10640
ORA-06512: at “SYS.DBMS_STATS”, line 10664
ORA-06512: at line 1

— try to gather statistics on the index using analyze
SQL> analyze index ajaffer.test_idx compute statistics;
analyze index ajaffer.test_idx compute statistics
*
ERROR at line 1:
ORA-38029: object statistics are locked

— unlock statistics
SQL> exec dbms_stats.unlock_table_stats('scott', 'test');

PL/SQL procedure successfully completed.

Oracle 12c:New feature ------- Logging DDL data..

Oracle 12c:New feature ------- Logging DDL data..





Oracle had introduced a cool feature in version 11g, where we were able to log or track DDL statements executed in the database by means of a parameter called ENABLE_DDL_LOGGING without setting up database auditing. Setting ENABLE_DDL_LOGGING to TRUE results in logging DDL statements in to the instance alert log (s). Since the DDL statements are logged into alert log file, it becomes a hard task to scan through the alert log and find the DDL logs.

Oracle has made a significant change in terms of DDL logging with version 12c. In Oracle 12c, the DDL logs are maintained in dedicated DDL log file (s) unlike the instance alert log file which was the case with Oracle 11g. This makes it easier to track DDL statements executed in a database.

In 12c, Oracle maintains the DDL logs in two files (XML and plain text) under the ADR_HOME as listed below.

    XML Version: $ADR_BASE/diag/rdbms/${DBNAME}/${ORACLE_SID}/log/ddl/log.xml
    Text Version: $ADR_BASE/diag/rdbms/${DBNAME}/${ORACLE_SID}/log/ddl_${ORACLE_SID}.log

As per the Oracle documentation, setting ENABLE_DDL_LOGGING to TRUE will log following DDL statements executed in a database.

    ALTER/CREATE/DROP/TRUNCATE CLUSTER
    ALTER/CREATE/DROP FUNCTION
    ALTER/CREATE/DROP INDEX
    ALTER/CREATE/DROP OUTLINE
    ALTER/CREATE/DROP PACKAGE
    ALTER/CREATE/DROP PACKAGE BODY
    ALTER/CREATE/DROP PROCEDURE
    ALTER/CREATE/DROP PROFILE
    ALTER/CREATE/DROP SEQUENCE
    CREATE/DROP SYNONYM
    ALTER/CREATE/DROP/RENAME/TRUNCATE TABLE
    ALTER/CREATE/DROP TRIGGER
    ALTER/CREATE/DROP TYPE
    ALTER/CREATE/DROP TYPE BODY
    DROP USER
    ALTER/CREATE/DROP VIEW

There are some discrepancies while using ENABLE_DDL_LOGGING for tracking DDL statements particularly in the context of multi tenant architecture. In this post, I will go through a quick demonstration to explore how this feature works and what are the discrepancies associated with this feature.

Let’s start with our demonstration. By default, DDL logging is not enabled as we can find by querying the v$parameter view.

   
---//
---// DDL logging is disabled by default //---
---//
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> select name,value,default_value from v$parameter where name='enable_ddl_logging';

NAME                      VALUE      DEFAULT_VALUE
------------------------- ---------- ---------------
enable_ddl_logging        FALSE      FALSE

Let’s enable DDL logging in our database by setting ENABLE_DDL_LOGGING to TRUE as shown below. My demonstration is targeted against a Oracle 12c (12.1.0.2) container database to understand how the feature works in a multi tenant environment.

---//
---// Enable DDL logging //---
---//
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> alter system set enable_ddl_logging=TRUE;

System altered.

---//
---// Validate DDL logging is enabled in the database //---
---//

SQL> select name,value,default_value from v$parameter where name='enable_ddl_logging';

NAME                      VALUE      DEFAULT_VALUE
------------------------- ---------- ---------------
enable_ddl_logging        TRUE       FALSE

At this point, we have enabled DDL logging for our container database. However, there is no log file created yet as we haven’t performed any DDL after enabling the DDL logging. This can be confirmed by looking to the DDL log locations as shown below.

   
##---
##--- DDL log files are not created yet ---##
##---
[oracle@test1 ~]$ cd /app/oracle/diag/rdbms/OCDB1/OCDB1/log/ddl/
[oracle@test1 ddl]$ ls -lrt
total 0

Let’s perform few DDL statements on both container (CDB$ROOT) and pluggable (CPDB1) databases and observe how these statements are logged by Oracle.

---//
---// executing DDL in root(CDB$ROOT) container //---
---//
16:24:02 SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

16:24:16 SQL> create user utest identified by utest;

User created.

16:24:29 SQL> drop user utest cascade;

User dropped.


---//
---// connecting to PDB CPDB1 //---
---//
16:24:36 SQL> conn ktest@CPDB1
Enter password:
Connected.

---//
---// executing DDL in pluggable database CPDB1 //---
---//
16:24:49 SQL> show con_name

CON_NAME
------------------------------
CPDB1

16:25:34 SQL> create table test as select * from all_users;

Table created.

16:25:48 SQL> create table test1 as select * from test;

Table created.

16:26:04 SQL> truncate table test1;

Table truncated.

16:26:13 SQL> drop table test purge;

Table dropped.

We have performed a number of DDL statements in both root (CDB$ROOT) and pluggable (CPDB1) databases. We can now see the existence of respective DDL log files as shown below.

   
##---
##--- DDL logs are created after DDL execution ---##
##---
[oracle@test1 ddl]$ pwd
/app/oracle/diag/rdbms/OCDB1/OCDB1/log/ddl
[oracle@test1 ddl]$ ls -lrt
total 4
-rw-r----- 1 oracle dba 1650 May  8 16:26 log.xml

[oracle@test1 log]$ pwd
/app/oracle/diag/rdbms/OCDB1/OCDB1/log
[oracle@test1 log]$ ls -lrt ddl_${ORACLE_SID}.log
-rw-r----- 1 oracle dba 225 May  8 16:26 ddl_OCDB1.log

Let’s see what is logged in the DDL log files for these DDL statements. First, let’s take a look into the text version of the logs
   
##---
##--- DDL logs from text version of log file ---##
##---
[oracle@test1 log]$ cat ddl_OCDB1.log
diag_adl:drop user utest cascade
Sun May 08 16:25:48 2016
diag_adl:create table test as select * from all_users
diag_adl:create table test1 as select * from test
diag_adl:truncate table test1
diag_adl:drop table test purge

We can see that all the DDL statements are logged (except the “create user” statement as that is not supported) into the log file. However, looks like the log file is just a sequence of DDL statements without any context of those statements. There are some vital information missing from this log file. If you observe it closely, you will find that TIMESTAMP information is missing for most of the statements. I was also expecting the container information to be associated with each of the logged DDL statement. However, that information is not there in the log file. Since, we are dealing with multi tenant database; the container information is very much required to be able to determine in which container a particularly DDL statement was executed. Without the container details, these DDL logging could not server any purpose.

Now let’s take a look into the XML version of the DDL log file to see if we can find any missing information there.

   
##---
##--- DDL logs from XML version of log file ---##
##---
[oracle@test1 ddl]$ cat log.xml
<msg time="2016-05-08T16:24:36.772+05:30" org_id="oracle" comp_id="rdbms" msg_id="opiexe:4409:2946163730" type="UNKNOWN" group="diag_adl" level="16" host_id="test1.oraclebuffer.com" host_addr="192.168.230.15" version="1">
 <txt>drop user utest cascade
 </txt>
</msg>
<msg time="2016-05-08T16:25:48.587+05:30" org_id="oracle" comp_id="rdbms" msg_id="kpdbLogDDL:18891:2946163730" type="UNKNOWN" group="diag_adl" level="16" host_id="test1.oraclebuffer.com" host_addr="192.168.230.15">
 <txt>create table test as select * from all_users
 </txt>
</msg>
<msg time="2016-05-08T16:26:04.508+05:30" org_id="oracle" comp_id="rdbms" msg_id="kpdbLogDDL:18891:2946163730" type="UNKNOWN" group="diag_adl" level="16" host_id="test1.oraclebuffer.com" host_addr="192.168.230.15">
 <txt>create table test1 as select * from test
 </txt>
</msg>
<msg time="2016-05-08T16:26:13.874+05:30" org_id="oracle" comp_id="rdbms" msg_id="kpdbLogDDL:18891:2946163730" type="UNKNOWN" group="diag_adl" level="16" host_id="test1.oraclebuffer.com" host_addr="192.168.230.15">
 <txt>truncate table test1
 </txt>
</msg>
<msg time="2016-05-08T16:26:25.462+05:30" org_id="oracle" comp_id="rdbms" msg_id="kpdbLogDDL:18891:2946163730" type="UNKNOWN" group="diag_adl" level="16" host_id="test1.oraclebuffer.com" host_addr="192.168.230.15">
 <txt>drop table test purge
 </txt>
</msg>




The XML version of the log file looks to be more informative than the text version. We have now additional details available for each of the DDL statements executed like the TIMESTAMP of the DDL along with some session specific details like host_id and host_addr

However, the logs are still not sufficient for a multi tenant container database. The container information is still missing from the logs and we can’t rely on it to track on which container a particular DDL statement was executed
Footnote:

ENABLE_DDL_LOGGING feature can be considered for use in Oracle 11g or Oracle 12c non-CDB database. We must query the XML version of the log file for detailed information related to a DDL statement rather than querying the text version as the text version seems to miss the TIMESTAMP and session specific information for most DDL statements. Moreover, ENABLE_DDL_LOGGING feature seems not to be an ideal option for DDL tracking in a multi tenant container database considering the fact that it doesn’t log the container details of DDL statements and in turn serves no purpose for tracking.