EXPDP AND IMPDP COMMANDS

----------------IMPDP FULL SCHEMA------------------------------
impdp system/<syspassword>  transform=segment_attributes:n DUMPFILE=<dumpfile>.dmp logfile=<logfilename>.log remap_schema=<expdp schema>:<impdp schema> PARALLEL=5

--------------IMPDP for table--------------------------------
impdp system/<syspassword> tables=<schema name>.<tablename> DUMPFILE=<dumpfile>.dmp logfile=<dumpfile>.log transform=segment_attributes:n 

--------------expdp for full database with parallel--------------
expdp system/<syspassword> FULL=y DUMPFILE=<filename>.dmp PARALLEL=5 LOGFILE=<logfilename>.log JOB_NAME=<jobname>

----------------expdp for a table----------------------------------
expdp system/<syspassword> tables=<schemaname>.<tablename> DUMPFILE=<filename>.dmp PARALLEL=5 LOGFILE=<logfilename>.log JOB_NAME=<jobname>

--------------how to stop and start job-----------------------------
IMPORT>STOP_JOB=IMMEDIATE;
IMPORT>START_JOB;
IMPORT>STATUS;
IMPORT>HELP;

---------------EXPDP for a schema--------------------
expdp system/<systempasword> SCHEMAS=<schemaname> DUMPFILE=<dumpfilename>.dmp LOGFILE=<logfilename>.log PARALLEL=5

--------------IMPDP for a schema---------------------------------
impdp system/<systempasword> SCHEMAS=<schemadname> DUMPFILE=<dumpfilename>.dmp LOGFILE=<logfilename>.log PARALLEL=5

------------IMPDP with exclude option----------------------------
impdp system/<password> SCHEMAS=<schemaname> REMAP_SCHEMA=<expdp schema>:<impdpschema>
DUMPFILE=<dumpfilename>.dmp 
EXCLUDE=constraint, ref_constraint, index,materialized_view  
logfile=<logfilename>.log 

-----------IMPDP with SQLFILE option------------------------
impdp <schemaname>/<password> DUMPFILE=<dumpfile>.dmp SQLFILE=<filename>.sql

----------How to use Exdp and Impdp over Network Link : Oracle DB--------------------

#Login in sqlplus 
sqlplus / as sysdba

#Create a connection link for the database which you want to export 
create database link remotelink connect to targetdbuser identified by targetdbpassword using 'hostname:port/sid'
#check connection 
select * from dual@remotelink

# create a local directory where the dump file will be stored and map it to your database dir by creating 
create directory dumpdir as '<path>';
# give permission to local user by whom we will be running expdp
GRANT read, write ON DIRECTORY dumpdir TO localdb;
# check if the dir is created 
 select directory_name, directory_path from dba_directories ;

# You might get following error , if you do not grant read write access of directory to the user .
#ORA-39002: invalid operation
#ORA-39070: Unable to open the log file.
#ORA-39087: directory name dumpdir is invalid

GRANT EXP_FULL_DATABASE to targetdbuser;

expdp userid=localdb/localdb@//localhost:1521/ORCL dumpfile=testdump.dmp logfile=testdump.log SCHEMAS=myschema directory=dumpdir 

grant imp_full_database to myschema ;

impdp myschema/mytest@//localhost:1521/orcl schemas=myschema directory=dumpdir dumpfile=testdump.dmp logfile=impdpnewtest1.log 


------------------------------Schema level with content dataonly and table exist action truncate--------------
impdp system/<password> schemas=<schemaname> content=data_only directory=<directoryname> dumpfile=<dumpfilename>.dmp logfile='<logfilename>.log' TABLE_EXISTS_ACTION=TRUNCATE

-----------------------------Schema level with content dataonly and table exist action replace---------------------
impdp system/<password> schemas=<schema> directory=<dictionary> dumpfile=<dumpfilename>.dmp logfile='<logfilename>.log' TABLE_EXISTS_ACTION=REPLACE

Note: TABLE_EXISTS_ACTION={SKIP | APPEND | TRUNCATE | REPLACE} 

SP2-0308: cannot close spool file

SP2-0308: cannot close spool file

This Error popup due to disk partition is going to full so to remove this error delete something from your hard disk partition..

Database properties views

Below are some query that will help you to check current database properties:

SQL>SELECT * FROM DATABASE_PROPERTIES;

SQL> SELECT  * FROM DATABASE_SUMMARY;

If you want the full version information for your DB then:

SQL>SELECT * FROM v$version;

If you want your DB parameters then:

SQL>SELECT * FROM v$parameter;

If you want more information about your DB instance then:

SQL>SELECT * FROM v$database;

SQL>SELECT * FROM v$instance;

If you want the "size" of your database then this will give you a close enough calculation:

SQL>SELECT SUM(bytes / (1024*1024)) "DB Size in MB" FROM dba_data_files;

ORA-02391:exceeded simultaneous SESSIONS_PER_USER limit

ORA-02391 is user session error. It means you need to kill some user session if its not required or you need to increase sessions per use limit. Killing sessions can be very destructive if you kill the wrong session so be careful when killing session. for that first we need to identify that sessions for that query is given below. . SQL>SELECT s.inst_id, s.sid,s.serial#,p.spid,s.username,s.program FROM   gv$session s JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id WHERE  s.type != 'BACKGROUND'; The basic syntax for killing sessions is given below. SQL>ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id' immediate; if you want to increase user session process are given below: 1) SELECT name, value FROM gv$parameter WHERE name = 'resource_limit'; 2) ALTER SYSTEM SET resource_limit=TRUE SCOPE=BOTH; 3) SELECT name, value FROM gv$parameter WHERE name = 'resource_limit'; 4) ALTER PROFILE <profile name>  LIMIT sessions_per_user 80;

Structure of dba_errors

Name                                                        Null?                            Type
 -----------------------------------                               --------                  ----------------------------
 OWNER                                                   NOT NULL               VARCHAR2(30)
 NAME                                                      NOT NULL              VARCHAR2(30)
 TYPE                                                                                          VARCHAR2(12)
 SEQUENCE                                             NOT NULL                NUMBER
 LINE                                                         NOT NULL                NUMBER
 POSITION                                               NOT NULL                NUMBER
 TEXT                                                       NOT NULL               VARCHAR2(4000)
 ATTRIBUTE                                                                              VARCHAR2(9)
 MESSAGE_NUMBER                                                               NUMBER

Structure of dba_tablespace

Name                                                                   Null?                          Type
 -----------------------------------------                             --------                     ----------------------------
 TABLESPACE_NAME                                    NOT NULL              VARCHAR2(30)
 BLOCK_SIZE                                                  NOT NULL               NUMBER
 INITIAL_EXTENT                                                                             NUMBER
 NEXT_EXTENT                                                                                 NUMBER
 MIN_EXTENTS                                               NOT NULL               NUMBER
 MAX_EXTENTS                                                                                NUMBER
 PCT_INCREASE                                                                                 NUMBER
 MIN_EXTLEN                                                                                     NUMBER
 STATUS                                                                                              VARCHAR2(9)
 CONTENTS                                                                                        VARCHAR2(9)
 LOGGING                                                                                           VARCHAR2(9)
 FORCE_LOGGING                                                                             VARCHAR2(3)
 EXTENT_MANAGEMENT                                                                VARCHAR2(10)
 ALLOCATION_TYPE                                                                         VARCHAR2(9)
 PLUGGED_IN                                                                                     VARCHAR2(3)
 SEGMENT_SPACE_MANAGEMENT                                              VARCHAR2(6)
 DEF_TAB_COMPRESSION                                                               VARCHAR2(8)
 RETENTION                                                                                       VARCHAR2(11)
 BIGFILE                                                                                                VARCHAR2(3)

Structure of dba_users

Name                                                          Null?                Type
 -----------------------------------------                           --------           ----------------------------
 USERNAME                                              NOT NULL     VARCHAR2(30)
 USER_ID                                                   NOT NULL       NUMBER
 PASSWORD                                                                        VARCHAR2(30)
 ACCOUNT_STATUS                                NOT NULL       VARCHAR2(32)
 LOCK_DATE                                                                       DATE
 EXPIRY_DATE                                                                    DATE
 DEFAULT_TABLESPACE                        NOT NULL       VARCHAR2(30)
 TEMPORARY_TABLESPACE                 NOT NULL        VARCHAR2(30)
 CREATED                                                  NOT NULL          DATE
 PROFILE                                                   NOT NULL         VARCHAR2(30)
 INITIAL_RSRC_CONSUMER_GROUP                              VARCHAR2(30)
 EXTERNAL_NAME                                                              VARCHAR2(4000)