Export/Import

See Datapump, How to export/import statistics, File Compression

NLS environment
select 'export NLS_LANG="' ||
(select value from NLS_DATABASE_PARAMETERS where parameter = 'NLS_LANGUAGE') || '_' ||
(select value from NLS_DATABASE_PARAMETERS where parameter = 'NLS_TERRITORY')|| '.' || 
(select value from NLS_DATABASE_PARAMETERS where parameter = 'NLS_CHARACTERSET') || '"' env 
from dual;

 

Compressed export (unix)
set environment
[exp.par example]
consistent=y
feedback=50000
full=y
    
mknod expdat.dmp p
or
mkfifo -m777 expdat.dmp

nohup compress < expdat.dmp > expdat.dmp.Z &
nohup exp parfile=exp.par userid="system/pwd@al11" &
tail -100f nohup.out

 

Determine export size (Compressed or not)
set environment
mknod expdat.dmp p
--COMPRESS
mknod expdat.dmp p
nohup compress < expdat.dmp > expdat.dmp.Z &
nohup exp parfile=exp.par &
--COMPRESS
dd if=expdat.dmp.Z of=/dev/null bs=1024 &
--NO COMPRESS
dd if=expdat.dmp of=/dev/null bs=1024 &
tail -100f nohup.out
To obtain size in MB divide by 1024

 

Piped export -> import (*full*)
set environment
mkfifo -m777 xpipe

From user to user
nohup
imp userid=rmansasint/rmansasint file=xpipe fromuser=rmansas 
touser=rmansasint log=import.log &
[1] 19215
nohup
exp userid=rmansas/rmansas file=xpipe log=export.log &

Full
I always manually created the tablespaces on dest db.
See Script generation for DDL operations
Make temp tablespace autoextensible to allow indexes creation
Be sure temporary tablespace have same name otherwise you may get:
IMP-00003: ORACLE error 1435 encountered

[exp.txt] (source db)
direct=y
feedback=50000
file=xpipe
full=y
log=export.log
buffer=10000000
userid=system/manager

[imp.txt] (dest db)
commit=y
feedback=50000
file=xpipe
full=y
log=import.log
ignore=y
buffer=10000000
userid=system/manager@destdb

nohup imp parfile=imp.txt &
[1] 19215
nohup exp parfile=exp.txt &

 

Compressed import (Solaris)
set environment
mknod expdat.dmp p
nohup uncompress < expdat.dmp.Z > expdat.dmp &
nohup imp parfile=imp.par userid="system/pwd@al11" &
tail -100f nohup.out

 

Export multi users

[exp.par]
buffer=10000000
feedback=50000
log=exp.log
statistics=none
owner=RLOMBARDIA_R2_OWN, RLOMBARDIA_R2_APP, JMS_OWN, JMS_APP
userid=system/manager
file=expdat.dmp

On dest db:
  Create users, tablespaces, grant on tablespaces
  
Example:
  CREATE USER "JMS_APP"  PROFILE "DEFAULT" IDENTIFIED BY "JMS_APP" DEFAULT TABLESPACE "USERS" 
  TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;
  GRANT "CONNECT", unlimited tablespace TO "JMS_APP";
  ALTER USER "JMS_APP" QUOTA UNLIMITED ON "USERS";

 

Verify workspaces

Get space occupied by source tables in source database

select sum(bytes)/1024/1024 MB from (
	select a.*
	, (select sum(bytes) from dba_segments where owner = a.owner and segment_name=a.name) bytes
	from 
	(
		select owner, table_name name, owner table_owner, table_name, 'TABLE' type from dba_tables
		union all
		select owner, index_name name, table_owner, table_name, 'INDEX' type from dba_indexes
	) a
) b
where table_owner = 'VAN'
and table_name in (
'TELEFONO_S', 
'INDIRIZZO_POSTALE_S', 
'INDIRIZZO_TELEMATICO_S', 
'DOCUMENTO_PERSONALE_S',
'COORDINATA_FINANZIARIA_S',
'IDENTIFICATIVO_ESTERNO_S',
'SINISTRO_S',
'NOTA_SOGGETTO_S',
'PERSONA_FISICA_S',
'PERSONA_GIURIDICA_S',
'LEGAME_SOGGETTI_S',
'SOGGETTO_S');

 

Autoextend on/off on destination datafiles before import
select 
'ALTER DATABASE DATAFILE '''||file_name ||''' AUTOEXTEND ON NEXT  10M;' AUTOEXTEND_ON
, 'ALTER DATABASE DATAFILE '''||file_name ||''' AUTOEXTEND OFF;' AUTOEXTEND_OFF 
from dba_data_files
where tablespace_name in 
(
 select distinct tablespace_name from dba_segments where owner in (upper('&&owner1'))
)

 

Disable / Enable constraints
select disable_script, enable_script from (
	select enable_script, disable_script, own_tab from (
		--Constraints where table references -> ----------------------------------
		SELECT 
		 B1.owner referencer_owner
		, B1.table_name referencer_table_name
		, upper(C1.COLUMN_NAME) referencer_column
		, a1.owner referenced_owner 
		, A1.TABLE_NAME referenced_table_name
		, C1.POSITION, 
		b1.CONSTRAINT_NAME
		, a1.delete_rule
		, 'alter table '|| B1.owner||'.'|| B1.table_name ||' disable constraint ' || b1.CONSTRAINT_NAME || ';'as disable_script
		, 'alter table '|| B1.owner||'.'|| B1.table_name ||' enable constraint ' || b1.CONSTRAINT_NAME || ';'as enable_script
		, B1.owner||'.'||B1.table_name own_tab
		FROM
		DBA_CONSTRAINTS A1, DBA_CONSTRAINTS B1, dba_CONS_COLUMNS C1 
		 where A1.Constraint_Name = B1.R_Constraint_name 
		 And A1.Owner=B1.R_Owner 
		 And a1.CONSTRAINT_TYPE IN ('P', 'U') 
		 And C1.Table_name=B1.table_name 
		 And C1.Constraint_Name = B1.Constraint_name 
		 And C1.owner = B1.owner
		--------------------------------------------------------------------------
	) 
	union all
	select enable_script, disable_script, own_tab from (
		   --Constraints where table is referencesd <- -------------------------------------------
			SELECT DBA_CONS_COLUMNS.owner referenced_owner
			, DBA_CONS_COLUMNS.Table_name referenced_table_name
			, upper(DBA_CONS_COLUMNS.COLUMN_NAME) referenced_column
			, DBA_CONSTRAINTS.owner referencer_owner
			, DBA_CONSTRAINTS.TABLE_NAME referencer_table
			, DBA_CONS_COLUMNS.POSITION, 
			 DBA_CONSTRAINTS.CONSTRAINT_NAME,  DBA_CONSTRAINTS.delete_rule
			, 'alter table ' || DBA_CONSTRAINTS.owner ||'.'||DBA_CONSTRAINTS.Table_name|| ' disable constraint ' || DBA_CONSTRAINTS.CONSTRAINT_NAME || ';'as disable_script 
			, 'alter table ' || DBA_CONSTRAINTS.owner ||'.'||DBA_CONSTRAINTS.Table_name|| ' enable constraint ' || DBA_CONSTRAINTS.CONSTRAINT_NAME || ';'as enable_script
			, DBA_CONS_COLUMNS.owner||'.'||DBA_CONS_COLUMNS.Table_name own_tab
			FROM DBA_CONSTRAINTS, sys.DBA_CONS_COLUMNS
			where 
			    DBA_CONSTRAINTS.R_Constraint_Name = DBA_CONS_COLUMNS.Constraint_name
			 And DBA_CONSTRAINTS.R_Owner=DBA_CONS_COLUMNS.Owner
			 And DBA_CONSTRAINTS.CONSTRAINT_TYPE = 'R' 
			 order by DBA_CONSTRAINTS.owner, DBA_CONSTRAINTS.Table_name, DBA_CONS_COLUMNS.Position
		   ----------------------------------------------------------------------------------------
	) 
	union all
	select enable_script, disable_script, own_tab from (
		--Table triggers -------------------------------------------------------------
		select 'ALTER TRIGGER '|| owner||'.'||trigger_name ||' ENABLE;' enable_script
		, 'ALTER TRIGGER '|| owner||'.'||trigger_name ||' DISABLE;' disable_script
		, table_owner||'.'||table_name own_tab 
		from dba_triggers
		------------------------------------------------------------------------------
	)
)
where own_tab in('VPO.MOV_CONTABILE')

 

Validate export only

imp userid=system/pwd file=expdat.dmp show=y full=y