Purge AUD$ table using DBMS_AUDIT_MGMT

            Oracle 11gR2 introduced DBMS_AUDIT_MGMT for managing audit trails.  The growth of AUD$ can impact the performance of the database. So purging it regularly is the best practice followed by DBA’s and DBMS_AUDIT_MGMT makes it easier. Follow below steps for puring aud$ table. 1. Make sure AUD$ table is not […]

Interval Partitioning in oracle

        Interval Partitioning has been introduced in oracle 11g. With this method, we can automate the creation of range partition .While creating the partitioned table, we just need to define one partition. New partitions will be created automatically based on interval criteria when the data is inserted to the table. We don’t […]

How to move lob segment to another tablespace

Follow below steps from moving lob segment from one tablespace to another. SQL> ;   1*  select table_name,COLUMN_NAME,SEGMENT_NAME,TABLESPACE_NAME from dba_lobs where OWNER=’DBACLASS’ SQL> / TABLE_NAME     COLUMN_NAME  SEGMENT_NAME                       TABLESPACE_NAME ————– ———— ———————————- —————————— FD_BREL_AUDIT PAYLOAD      SYS_LOB0000100201C00011$$          WEBMDATA   SQL> alter table DBACLASS.FD_BREL_AUDIT move lob (PAYLOAD) store as SYS_LOB0000100201C00011$$ ( tablespace USERS); Table altered.   SQL>  select […]

ORA-01940: cannot drop a user that is currently connected

 While dropping a user in oracle database , you may face below error. ORA-01940 Problem: SQL> drop user SCOTT cascade 2 / drop user SCOTT cascade * ERROR at line 1: ORA-01940: cannot drop a user that is currently connected Solution: 1. Find the sessions running from this userid:

2. Kill the sessions:


How to change the oracle database name using nid utility

  Follow below steps for changing the oracle database name using nid utility. We will change the database name from B2CRMD2 to S1D2ST. 1. Mount the database

2. Run the NID utility SYNTAX – nid sys/password@CURRENT_DBNAME DBNAME=NEW_DBNAME  

3. change the db_name parameter in the parameter file.

4. Rename the spfile to […]

How to find when a table was last modified in oracle

    If you want to find, when a table was last modified like insert,update ,delete, then use the dictionary table dba_tab_modifications. SCENARIO: 1. Insert into test data:

2. Check dba_tab_modification:

  As you can see, the dba_tab_modification is not showing any rows. Now you need to flush the info , to update […]

Enable archive log mode in Oracle RAC

            Follow below steps for enabling archive log mode in oracle RAC. 1. stop the database service.

2. start the database in mount state.

3. enable archive log mode.

4. Restart the database service (using srvctl)

5. set the archive destination to a ASM DISK


How to apply JVM patch in oracle 12c database

                  Below are the steps for applying JVM patch in oracle 12c database.   Steps 1 : Download the respective patch from oracle support  and unzip it in the server. unzip cd 21555660 Steps 2 : check the conflict against ORACLE_HOME  

  Step 3: […]

Create Incident package using ADRCI utility

     ADRCI is an command line oracle utility for managing diagnostic data. We can create incident packages and provide it to oracle support team for analysis. Follow below steps to create incident package. View the incidents:

Create incident package:  

How to Enable Trace for a listener

If you want to enable trace for a listener, Then follow below steps. Set current listener:  

Check status

Enable Trace:

Check the status again and the trace file location:


Page 4 of 6« First...23456
Skip to toolbar