EXPDP-- Export Dump

Hi,

Lets see some basic steps to do Export data from one  schema another schema.

To do export data ,we have Oracle Utility i.e. EXPDP and same way to do import in another schema ,we can use IMPDP .

Dump file which  is created by EXPDP can be imported by using only IMPDP utility.


Basic Requirements:
1) Need to have directory ==> When we do EXPDP then need to specify directory name.In this directory only DUMP file will be stored and can be used further to do import.


Check  here to create directory.


2) Privilege ==>  Need to get Privilege from DBA to do Export/Import in any schema.

Those are..

SQL> grant EXP_FULL_DATABASE to CNYCBAIND;

Grant succeeded


SQL> grant IMP_FULL_DATABASE to CNYCBAIND;

Grant succeeded.


You can query below table to check whether your schema got privilege or not.

select * from dba_role_privs where grantee='SCOTT';

here 'SCOTT' is schema name,replace this with your schema name.

Now  you are ready to do export or import.

1) How to do Full export.

By default ,oracle Expdp will do full database export.

username ==> need to specify your schema credentials
Directory==> Directory name which you created in 1st step
Dumpfile==> Specify name whichever you want.Dumpfile will be  created with same name which you mentioned here.
Logfile ==> Specify name whichever you want.Log file is to check your Export or import activity status and errors etc.
Parallel ==> How many process to run parallel for your export or import activity.

Please note, mention all options names in  CAPS only.

expdp USERNAME=Username/Password DIRECTORY=CBADUMP
DUMPFILE=expdp_full.dmp
LOGFILE=Export_Full.LOG
PARALLEL=8



2) How to do particular table export.

expdp USERNAME=Username/Password DIRECTORY=CBADUMP
DUMPFILE=expdp_table.dmp
LOGFILE=Export_table.LOG
PARALLEL=8
TABLES=ICTB_ENTRIES_HISTORY

TABLES-->  Specify which table you want to export,suppose if you want to export multiple tables then use ','(comma) between tablenames.

ORA-24005: Inappropriate utilities used to perform DDL on AQ table

We cant directly drop queue tables using drop command .

Follow below steps to drop Queue table:

Syntax:


BEGIN
DBMS_AQADM.DROP_QUEUE_TABLE('name of queue table',TRUE);
END;
/

ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

You may be facing error "ORA-30036" while doing  import since it will through if your tablespace doesn't have sufficient space  in it.

let's check this error and resolve it.

From above error,its telling that in "UNDOTBS1" tablespace is full and doesn't have space to store any extra data.

You can see in below screenshot that its shows empty space.

Use below command to add data file(Space to tablespace).

ALTER tablespace  UNDOTBS1 add datafile '/mnt/devops_0/dba/oradata/APAC11R2/undotbs05.dbf'  size 10000M

--here "/mnt/devops_0/dba/oradata/APAC11R2/undotbs05.dbf'" is your data file ,which you are adding to tablespace.

Now run below  query  to see list of datafiles in your tablespace.

SQL> select df.file_name,
  2            df.tablespace_name,
  3            ROUND(sum(bytes) / 1024 / 1024 / 1024, 2) Size_In_GB
  4       from dba_data_files df
  5       join dba_TABLESPACES dt on dt.tablespace_name = df.tablespace_name
  6      Where dt.tablespace_name = 'UNDOTBS1'
  7      group by df.file_name, df.tablespace_name
  8  ;

FILE_NAME                                                   TABLESPACE_NAME                SIZE_IN_GB
-------------------------------------------------------------------------------- ------------------------------ ----------
/mnt/devops_0/dba/oradata/APAC11R2/undotbs03.dbf            UNDOTBS1                             9.77
/mnt/devops_0/dba/oradata/APAC11R2/undotbs02.dbf            UNDOTBS1                             4.88
/mnt/devops_0/dba/oradata/APAC11R2/undotbs05.dbf            UNDOTBS1                             9.77
/mnt/devops_0/dba/oradata/APAC11R2/undotbs01.dbf            UNDOTBS1                               32
/mnt/devops_0/dba/oradata/APAC11R2/undotbs04.dbf            UNDOTBS1                             4.88

Query:   select b.tablespace_name, tbs_size Size_Gb, a.free_space Free_gb
                                from (select tablespace_name,
                                   round(sum(bytes) / 1024 / 1024 / 1024, 2) as free_space
                        from dba_free_space    group by tablespace_name) a, (select tablespace_name,                                       sum(bytes) / 1024 / 1024 / 1024 as tbs_size  from dba_data_files
group by tablespace_name) b
 where a.tablespace_name(+) = b.tablespace_name
   and b.tablespace_name = 'UNDOTBS1';

Query to check locked objects

Below query will provide list of objects are under particular Schema.

SELECT a.sid,
       a.serial#,
       a.username,
       c.os_user_name,
       a.program,
       a.logon_time,
       a.machine,
       a.terminal,
       b.object_id,
       substr(b.object_name, 1, 40) object_name,
       DECODE(c.locked_mode,
              1,
              'No Lock',
              2,
              'Row Share',
              3,
              'Row Exclusive',
              4,
              'Shared Table',
              5,
              'Shared Row Exclusive',
              6,
              'Exclusive') locked_mode
  from v$session a, dba_objects b, v$locked_object c
 where a.sid = c.session_id
   and b.object_id = c.object_id
--   and a.USERNAME = 'CNYCBAIND'.

Now use Below command to kill session.

Syntax: ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;

SQL> ALTER SYSTEM KILL SESSION '483,6849' immediate;

session will be killed.

How to create Directory

let's check out create directory syntax .


SQL> CREATE OR REPLACE DIRECTORY TEST_DIR AS '/scratch/Dump_CBA';

Directory created

SQL> grant read,write,execute on directory TEST_DIR to SCOTT;

Grant succeeded

SQL> select * from all_directories where directory_name='TEST_DIR';

OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------ -------------------------------------------------------------
SYS                            TEST_DIR                                     /scratch/Dump_CBA

ORA-14452

We cant drop temporary table directly. If you try  to drop you would get below error.

So truncate this table first and then try to drop it.you will be succeed.