Oracle

WHILE RUNNING DATAPATCH VERBOSE WE ARE GETTING FOLLOWING ERROR ORA-20002, ORA-04088, ORA-00604, ORA-20002

by

ISSUE: – WHILE RUNNING DATAPATCH VERBOSE WE ARE GETTING FOLLOWING ERROR -> Error at line 255138: script md/admin/sdordfm.plb- ORA-20002: ORA-06502: PL/SQL: numeric or value error: character string buffer-> Error at line 255182: script md/admin/sdordfb.plb- ORA-04088: error during execution of trigger ‘SYS.SERVER_ERROR_TO_ALERT’-> Error at line 255183: script md/admin/sdordfb.plb- ORA-00604: error occurred at recursive SQL level 1-> … Read more

PDB went in restricted mode in Oracle 19c

by

Issue: PDB went into restricted mode Investigation su – oracle export ORACLE_HOME=/u01/oracle/product/19/db export ORACLE_SID= cp9494 $ORACLE_HOME/OPatch/datapatch -verbose If you get INVALID object error, run the utlrp as below cd $ORACLE_HOME/rdbms/admin$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -c P327 -b utlrp -d ”’.”’ utlrp.sql There is a chance that the PDB will still be in restricted mode even … Read more

Export from 11g and import to 19c

by

To perform the export/import first we need to create a backup directory at the OS level as well as Database level using the following steps. Create a backup directory Take Full Export from 11g Database Check tablespaces Before importing the above data in Oracle 19c you must check the existing tablespaces detail in the Oracle … Read more

Recovery using datafile copy – SWITCH DATAFILE TO COPY

by

By using Optimized Incremental Backup to disk backup strategy, an up to date copy of the database is always available on disk. In the event of a failure, the SWITCH command will point the controlfile to the backup of the datafiles that are present on disk. In this way, we can significantly reduce the downtime … Read more

Trigger – Oracle Database Objects

by

Overview of Triggers A database trigger is a compiled stored program unit, written in either PL/SQL or Java, that Oracle Database invokes (“fires”) automatically in certain situations. A trigger fires whenever one of the following operations occurs: 1. DML statements on a particular table or view, issued by any user DML statements modify data in … Read more

ORA-01000: Maximum Open Cursors Exceeded

by

The ORA-01000 error indicates that the maximum number of open cursors allowed in the database session has been exceeded. This is controlled by the OPEN_CURSORS initialization parameter, which sets the maximum number of cursors (pointers to SQL statements) a session can have open at once. Identify Open Cursors: You can use the following query to … Read more

Oracle Database Locking Mechanism – 3

by

Oracle Database automatically locks a resource on behalf of a transaction to prevent other transactions from doing something that requires exclusive access to the same resource. The database automatically acquires different types of locks at different levels of Restrictiveness depending on the resource and the operation being performed. Lock Description DML Locks Protect data. For … Read more

How to check Total, Active and Inactive sessions in Oracle database ?

by

To check the total, active, and inactive sessions in an Oracle database, you can query the V$SESSION view. Checking Total Sessions To get the total number of sessions: Checking Active Sessions Active sessions are those that are currently executing SQL commands. To find the number of active sessions: Checking Inactive Sessions Inactive sessions are those … Read more