Data Pump Exit Codes

oracle@Linux01:[/u01/oracle/DPUMP] $ exp atoorpu file=abcd.dmp logfile=test.log table=sys.aud$
About to export specified tables via Conventional Path ...
. . exporting table                           AUD$     494321 rows exported
Export terminated successfully without warnings.

oracle@qpdbuat211:[/d01/oracle/DPUMP] $ echo $?
0


oracle@Linux01:[/u01/oracle/DPUMP] $ imp atoorpu file=abcd.dmp logifle=test.log
LRM-00101: unknown parameter name 'logifle'

IMP-00022: failed to process parameters, type 'IMP HELP=Y' for help
IMP-00000: Import terminated unsuccessfully

oracle@Linux01:[/u01/oracle/DPUMP] $ echo $?
1

Can be used in export shell scripts for status verification:

if test $status -eq 0
then
 echo "export was successfull."
else
 echo "export was not successfull."
fi

Also check below page fore reference :

https://docs.oracle.com/database/121/SUTIL/GUID-34D0DEE7-3530-42DC-BE01-C2588CC73CE5.htm#SUTIL3834

Automate recyclebin purge in oracle

Setup this simple scheduler job as sysdba to purge the objects in the recycbin.
This is one of the most space cosuming location that often dba's forget to cleanup and the
objects get piled up occupying lot of space. Based on how long you want to save these dropped object setup a job under scheduler to run below plsql block either daily, weekly or monthly. 

 I suggest to run weekly.


--For user_recyclebin purge--
-- plsql --

declare
VSQL varchar2(500);
VSQL1 varchar2(500);
Vcnt number(5);
begin

select count(*) into Vcnt from user_recyclebin;

/***  Optional if you would like to keep record count of objects purged
-- Uncomment if you would like to keep this
insert into SYS.PURGE_STATS (obj_count) values (Vcnt);
commit;
**/

if Vcnt>0 then
VSQL1:='purge user_recyclebin';
execute immediate VSQL1;
dbms_output.put_line('DBA RECYCLEBIN has been purged.');
end if;
end;
/



--For dba_recyclebin purge--
-- plsql --


declare
VSQL varchar2(500);
VSQL1 varchar2(500);
Vcnt number(5);
begin

select count(*) into Vcnt from dba_recyclebin;

/***  Optional if you would like to keep record count of objects purged
-- Uncomment if you would like to keep this
insert into SYS.PURGE_STATS (obj_count) values (Vcnt);
commit;
**/

if Vcnt>0 then
VSQL1:='purge dba_recyclebin';
execute immediate VSQL1;
dbms_output.put_line('DBA RECYCLEBIN has been purged.');
end if;
end;
/

Prerequisites

The database object must reside in your own schema or you must have the DROP ANY ... system privilege for the type of object to be purged, or you must have the SYSDBA system privilege. To perform the PURGE DBA_RECYCLEBIN operation, you must have the SYSDBA or PURGE DBA_RECYCLEBINsystem privilege.

Pluggable database save state -12C

SQL> ALTER PLUGGABLE DATABASE ALL OPEN;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       READ WRITE
PDBORCL2       READ WRITE
PDBORCL1       READ WRITE

SQL> SELECT con_name, instance_name, state FROM dba_pdb_saved_states;

no rows selected

SQL> SELECT name, open_mode FROM v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       READ WRITE
PDBORCL2       READ WRITE
PDBORCL1       READ WRITE

SQL> ALTER PLUGGABLE DATABASE pdborcl1 SAVE STATE;

Pluggable database altered.


SQL> COLUMN con_name FORMAT A20
COLUMN instance_name FORMAT A20

SELECT con_name, instance_name, state FROM dba_pdb_saved_states;

CON_NAME     INSTANCE_NAME  STATE
-------------------- -------------------- --------------
PDBORCL1     orcl  OPEN

SQL> SHUTDOWN IMMEDIATE;
STARTUP;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> ORACLE instance started.

Total System Global Area  788529152 bytes
Fixed Size    2929352 bytes
Variable Size  541068600 bytes
Database Buffers  239075328 bytes
Redo Buffers    5455872 bytes
Database mounted.
Database opened.
SQL> SELECT name, open_mode FROM v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       MOUNTED
PDBORCL2       MOUNTED
PDBORCL1       READ WRITE

SQL> COLUMN con_name FORMAT A20
COLUMN instance_name FORMAT A20

SELECT con_name, instance_name, state FROM dba_pdb_saved_states;SQL> SQL> SQL>

CON_NAME     INSTANCE_NAME  STATE
-------------------- -------------------- --------------
PDBORCL1     orcl  OPEN

***********************************************
DISCARD PDB SAVED STATE
***********************************************

SQL> COLUMN con_name FORMAT A20
COLUMN instance_name FORMAT A20

SELECT con_name, instance_name, state FROM dba_pdb_saved_states;SQL> SQL> SQL>

CON_NAME     INSTANCE_NAME  STATE
-------------------- -------------------- --------------
PDBORCL1     orcl  OPEN


SQL> ALTER PLUGGABLE DATABASE pdborcl1 discard state;

Pluggable database altered.

SQL> SELECT con_name, instance_name, state FROM dba_pdb_saved_states;

no rows selected

how to create nested cursor in plsql

select * from empinfo; 

"ID"    "FNAME"    "LNAME"    "DEPTNO"
1    "arv"    "red"    100
2    "sam"    "kum"    200
3    "tom"    "com"    100
4    "Hef"    "Kom"    200
5    "fam"    "ily"    300



select * from deptinfo;

"ID"    "DEPTNO"    "DEPTNAME"    "MGR"
1    100    "HR"    "Bill"
2    200    "IT"    "Greg"
3    300    "SALES"    "Phil"


-- sqlcode starts here

declare

CURSOR CUR1 is select * from empinfo ;
REC1 empinfo%rowtype;

CURSOR CUR2(DID number) is select * from deptinfo where deptno=DID;
REC2 deptinfo%rowtype;
begin
open CUR1 ;
loop
FETCH CUR1 into REC1;
EXIT WHEN CUR1%NOTFOUND;

OPEN CUR2(REC1.deptno);
loop
FETCH CUR2 into REC2;
EXIT WHEN CUR2%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('REC1.id:  '||REC1.ID||'  belongs to dept ID : '||REC2.DEPTNO||'  DEPT :'||REC2.DEPTNAME);

END LOOP;
CLOSE CUR2;

end loop;
close CUR1;
end;

Setup Oracle database using Docker container

Step-1:

Install docker container. Based on your windows OS Version. if you are using windows 7 will need docker engine and Kitematic 

if you are on windows 10 or higher use : Docker Community Edition (Download)

Docker Instructions : https://docs.docker.com/install/

Once installed you will see Docker Quickstart Terminal and Kitematic.

open docker machine and then Kitematic.

Search for oracle XE 11g by Seth89. Download and install the below shown container

Once installed You will have oracle database installed and ready to use. you can ignore the below error message  /docker-entrypoint-init.d/cache: no such file or dir.

Another way to confirm successful installation is you will see Unauthorized image in web preview section

Make sure you have bridge network configured. As below

Check if below configured ports are configured.

How to connect to oracle database in the container :

Based on the IP Address and port configured in above image. Use those credentials to connect to DB.

Username : System

Password : oracle

Or to connect via sqlplus use as below:

Optional settings :

To configure or change volumes use. Volumes will help you to copy files over or attach a local working directory to container:

Expdp exclude table/exclude schema

expdp export exclude syntax:

Here is a simple example on how to use exclude in your export cmds.

----------------------------------------------
Exclude Tables
----------------------------------------------

Using parfile:

First create a parfile with details.
$ vi impdp_full.par

--add the details to parfile
directory=DPUMP logfile=SCOTT_EXP.log dumpfile=SCOTT_%U.dmp parallel=4  EXCLUDE=TABLE:"IN ('TABLE1','TABLE2','TABLE3')

Then execute using parfile:
expdp SCOTT/XXXXXX parfile=impdp_full.par

on cmdline:
expdp SCOTT/XXXXX directory=DPUMP dumpfile=SCOTT_%U.dmp logfile=SCOTT.log schemas=SCOTT  parallel=6 EXCLUDE=TABLE:\"IN (\'TABLE1\', \'TABLE2\')\"

 

----------------------------------------------
Exclude Schemas
----------------------------------------------

First create a parfile with details.

$ vi expdp_full.par
--add the details to parfile
directory=DPUMP FULL=Y dumpfile=FULLDB_%U.dmp logfile=FULL.log   parallel=6 EXCLUDE=SCHEMA:"IN ('SCHEMA1','SCHEMA2','SCHEMA3')

Then execute using parfile:
expdp SYSTEM/XXXXXX parfile=expdp_full.par

on cmdline:
expdp SYSTEM/XXXXX directory=DPUMP dumpfile=FULLDB_%U.dmp logfile=FULL.log FULL=Y  parallel=6 EXCLUDE=TABLE:\"IN (\'SCHEMA1\', \'SCHEMA2\')\"

ORA-31633: unable to create master table ".SYS_IMPORT_FULL_05"

 Today I encountered a problem while importing a schema into my local database. I have exported a schema from ORCL (lets say) using expdp command. I tried to import it to another database and I was getting this error.

 ORA-31633: unable to create master table

[oracle@orcl dpump]$ impdp sam/oracle directory=DPUMP dumpfile=abc_2014_11_14.dmp logfile=abc_imp.log schemas=sam1,sam2

Import: Release 11.2.0.4.0 - Production on Fri Nov 14 13:59:56 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31633: unable to create master table "SAM.SYS_IMPORT_FULL_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1038
ORA-01031: insufficient privileges

I tried again and again same error. Then I checked may be it is missing some grants, I havegranted  sysdba privillage to SAM now and tried.

SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 14 14:02:27 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> grant sysdba to SAM;

Lets try again:

[oracle@orcl dpump]$ impdp SAM/SAM  directory=DPUMP dumpfile=abc_2014_11_14.dmp logfile=abc_imp.log schemas=sam1,sam2

Import: Release 11.2.0.4.0 - Production on Fri Nov 14 13:59:56 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31633: unable to create master table "SAM.SYS_IMPORT_FULL_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1038
ORA-01031: insufficient privileges.



Then I realized that it might be missing create table permission and granted that permission to user SAM.

SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 14 14:02:27 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> grant create table to SAM;

Grant succeeded.

Now tried again :

[oracle@orcl dpump]$ impdp SAM/SAM  directory=DPUMP dumpfile=abc_2014_11_14.dmp logfile=abc_imp.log schemas=sam1,sam2


 Import: Release 11.2.0.4.0 - Production on Fri Nov 14 14:02:54 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SAM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SAM"."SYS_IMPORT_SCHEMA_01":  sam/******** dumpfile=abc_2014_11_14.dmp logfile=abc_imp.log schemas=sam1,sam2
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Possible Solutions :

•  It might be a simple missing of create table grant  if you are trying to import it through another user.

• Try to check if there is already a table created (SYS_IMP*) with the name oracle is trying to create table.

• Check if the job name being used in session is already created in database.