Expdp Impdp Tutorial

1. Import using NETWORK_LINK 2. QUERY CLAUSE in EXPDP 3. SQLFILE parameter in impdp 4. TABLE_EXISTS_ACTION option in IMPDP 5. EXCLUDE/INCLUDE option 6. DATA_OPTION=SKIP_CONSTRAINT_ERRORS 7.LOGTIME=ALL Parameter – Oracle 12c new feature 8. Views_as_tables Parameter In Datapump Of Oracle 12c 9. TRANSFORM=DISABLE_ARCHIVE_LOGGING – Oracle 12c new feature 10. ENCRYPTION in export Import 11. CONTENT parameter in […]

Drop and recreate undo tablespace

Sometimes we may require to drop the existing tablespace undo and create a fresh one if the size of undo has increased a lot and we are unable to reclaim it. Below are the steps: 1. Check the existing UNDO details:

2. Create a new undo tablespace:

3. Update undo_tablespace parameter

4. Check […]

How to recover a dropped user using flashback oracle

                Using flashback method, we can restore an accidentally dropped users in oracle. Basically, we will flashback the database to past, when the user was available, Then take an export dump of the schema, and restore the database to same current state. Once db is up, we can import […]

How to Reload expdp and impdp utility in oracle

   In some cases the datapump utility gets corrupted .  To overcome it , we need to reload the expdp and impdp datapump utility in oracle.   Applicable from 11g onwards.  STEPS: 1.  start the database in upgrade mode:

2. Run catproc.sql and ultrp.sql script.

Tracing sessions in oracle

           There are multiple methods for enabling tracing for sessions in oracle. 1. Enabling tracing for all session of a user.              For this we need to create a trigger.

2. Enabling trace for a single session(using dbms_system)

3.  Enabling trace using oradebug.


Disabling auto tasks from dba_autotask_client

                       Follow below steps for disabling auto tasks in oracle database.

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:


Page 3 of 512345
Skip to toolbar