See Datapump, How to export/import statistics, File Compression
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
|
Autoextend on/off on destination datafiles before importselect
'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 constraintsselect 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 |