How to solve ORA-55622 during DROP USER ... CASCADE;

During a DROP USER ... CASCADE operation, you may encounter the following error:

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-55622: DML, ALTER and CREATE UNIQUE INDEX operations are not allowed on table "SCOTT"."SYS_FBA_TCRV_83287"

Solution: disable flashback archive:

Check the currently enabled flashback archive tables:

set lines 155
col TABLE_NAME for a30
col OWNER_NAME for a30
col FLASHBACK_ARCHIVE_NAME for a30
col ARCHIVE_TABLE_NAME for a30
col STATUS for a30
SELECT * 
FROM DBA_FLASHBACK_ARCHIVE_TABLES 
WHERE OWNER_NAME=upper('SCOTT');

Put the following in a script called gen_disable_fda.sql:

set trimspool on
set heading off
set feedback off
set echo off
set verify off
spool disable_flb_archive.sql
SELECT 'spool disable_flb_archive.log' FROM dual;
SELECT 'alter table ' || owner_name || '.' || table_name || ' no flashback archive;' 
FROM DBA_FLASHBACK_ARCHIVE_TABLES 
WHERE OWNER_NAME=upper('SCOTT');
SELECT 'exit' FROM dual;
exit

The script above will generate alter table statements for all SCOTT-owned tables with flashback archive enabled:

sqlplus / as sysdba @gen_disable_fda.sql

Finally, run the script disable_flb_archive.sql, which now contain statements as shown below:

alter table SCOTT.EMP no flashback archive;

After this has been done, the schema may be dropped.

Display the current locks in a tree-like fashion

Oracle provides some built-in scripts and views that can be used to monitor locks in the database


http://docs.oracle.com/database/121/ADMIN/monitoring.htm#ADMIN11255

To illustrate this, I will open a session that updates a table, then create another session that updates the same table:

Session# 1:

select SYS_CONTEXT('userenv','con_name') "container name",
       SYS_CONTEXT('userenv','con_id') "container id",
       SYS_CONTEXT('userenv','CURRENT_SCHEMA') "Current schema",
       SYS_CONTEXT('userenv','SID') "SID"
FROM DUAL

container name       container id         Current schema                 SID
-------------------- -------------------- ------------------------------ -----
VEGDB01              3                    SCOTT                          362

--Update a the table rows:
 update emp set sal=1000;

14 rows updated.

I will keep the session alive, without doing commit/rollback.

From session# 2:

select SYS_CONTEXT('userenv','con_name') "container name",
        SYS_CONTEXT('userenv','con_id') "container id",
        SYS_CONTEXT('userenv','CURRENT_SCHEMA') "Current schema",
        SYS_CONTEXT('userenv','SID') "SID"
FROM DUAL;

container name       container id         Current schema                 SID
-------------------- -------------------- ------------------------------ -----
VEGDB01              3                    SCOTT                          130

--Update the same rows as in session# 1:
update emp set sal=2000;

This session will not give you the prompt back, as it is trying to aquire a lock on the table rows.

I then ran the utllockt.sql script:

sqlplus / as sysdba @$ORACLE_HOME/rdbms/admin/utllockt.sql

Output (abbreviated):

WAITING_SESSION   LOCK_TYPE         MODE_REQUESTED  MODE_HELD     LOCK_ID1          LOCK_ID2
----------------- ----------------- --------------- ------------ ----------------- -----------------
362               None                
   130            Transaction       Exclusive       Exclusive     524308             17836

The leftmost session (362) is blocking the sessions listed underneath it.

Let's check what the session with SID = 130 is doing:

SELECT sess.sid,sess.serial#,sess.sql_id,s.plan_hash_value, s.child_number,sess.client_info,substr(sql_text,1,30) "sql text",sess.program,sess.pq_status,
        decode(sess.blocking_session_status,'VALID','blocked', 'NO HOLDER','Not blocked') "blocked?",
        sess.blocking_session "blocking session SID",sess.event
FROM V$SESSION sess inner join v$sql s 
on (sess.sql_id = s.sql_id)
WHERE sess.SID IN (130)
and sess.sql_child_number = s.child_number;

This query confirms the output from the Oracle supplied utllockt.sql, and reveals what the blocking session is trying to do, in this case, to execute DML on table rows as that are already being locked for update:

SID	SERIAL#	SQL_ID	PLAN_HASH_VALUE	sql text	PROGRAM	PQ_STATUS	blocked?	blocking session SID	EVENT
130	9755	1gpj28ptjj43p	1494045816	update emp set sal=2000	sqlplus@	ENABLED	blocked	362	enq: TX - row lock contention

How to find the number of times a query has been executed

The following query was given to me by an experienced colleague. It shows the number of times different variations of a particular query has been executed. The information is collected from V$SQL which samples the contents in the Library Cache, a part of the Shared Pool.

SELECT SQL_ID, COUNT(DISTINCT SQL_ID) NUM_SQL_IDS, COUNT(CHILD_NUMBER) ANT_CHILD, SUM(EXECUTIONS) 
FROM V$SQL
WHERE SQL_TEXT LIKE ' select count(*) TOTALS from   scott.emp%'
GROUP BY ROLLUP(SQL_ID)
;

Here is parts of the output:

SQL_ID	NUM_SQL_IDS	ANT_CHILD	SUM(EXECUTIONS)
01b71y5wmr2bx	1	1	0
081ha7tszas19	1	3	2
0bfn4yj267xy0	1	2	34
0dk42bqfz6fbr	1	3	6
g1vq03475hqgc	1	3	10
g3hky4g8vu108	1	3	9
--------- Abbreviated ---------
	274	549	1940

From the output we can see that there are a number of variations of the query being executed. The numbers are being rolled up nicely at the end, letting us know that there are 274 unique SQL_IDs, 549 different child cursors with a total of 1940 executions.

This information can certainly be useful during an effort to tune the library cache.

one-liner for debugging script parameter output

To view the output of your script parameters, put this line in the beginning of your script:

printf "Parameters passed are : $*\n"

What are 'TCP Socket (Kgas)' Waits?

Doc ID 416451.1 "What are 'TCP Socket (Kgas)' Waits?" from Oracle support explains:

A session is waiting for an external host to provide requested data over a network socket. The time that this wait event tracks does not indicate a problem, and even a long wait time is not a reason to contact Oracle Support.

and

An application that communicates with a remote host must wait until the data it will read has arrived.
The db session cannot proceed to do anything else until the external host provides the requested data over the network socket.

The proposed "solution":

From the database point of view, these waits can safely be ignored; the wait event does not represent a database issue. It merely reports the total elapsed time for a network connection to be established or for data to arrive from over the network.


Check also Doc ID 558510.1 "WAITEVENT: "TCP Socket (KGAS)" Reference Note"

Shell script snipplet for resetting a password

This little code snipplet to use a Linux/Unix escape character to send a line shift into a string of mulitple commands:

 #!/bin/bash
. /home/oracle/.bash_profile
userid=$1
password=$2
userexist="WHENEVER SQLERROR EXIT;"
echo -e "alter session set container=PDB01;\n$userexist\nalter user $userid identified by $password account unlock;" | sqlplus -L -s / as sysdba

Workaround for ORA-46372: audit file not found

In my environment, I had the following errors popping up in Cloud Control:

ORA-46372: audit file '/u01/oracle/audit/proddb01/ora_audit_10.bin' not found
ORA-17503: ksfdopn:4 Failed to open file
/u01/oracle/audit/proddb01/ora_audit_10.binin
ORA-17500: ODM err:File does not exist

It can be manually reproduced by executing the following query:

select count(1) from unified_audit_trail;

It seems to be related to BUG 26352615 - SPILLOVER AUDIT FILES CANNOT BE FOUND ORA-46372 for platform Linux x86-64. Per May 22ond 2018, it is still in development according to Oracle Support.

In the directory /u01/oracle/audit/proddb01, there is quite correctly no file named ora_audit_10.binin. However, there is one called ora_audit_10.bin

The following workaround worked for me:

Go to the directory /u01/oracle/audit/proddb01
Change the name of the file ora_audit_10.bin to ora_audit_10.binin

Test like this:

select count(1) from unified_audit_trail;

  COUNT(1)
----------
        25

Update 09.03.2020:

Oracle has released a patch for 26352615. It is available for a number of RUs. I just applied the patch for 12c Release 12.2.0.1.200114DBJAN2020RU, and it solved the problem outlined above.