Friday, 22 March 2013

Export: Oracle all commands

Export: Oracle all commands

 How does one use the import/export utilities?
exp scott/tiger file=emp.dmp log=emp.log tables=emp rows=yes indexes=no
exp scott/tiger file=emp.dmp tables=(emp,dept)

Can one export a subset of a table?
exp scott/tiger tables=emp query="where deptno=10"
exp scott/tiger file=abc.dmp tables=abc query=\"where sex=\'f\'\" rows=yes

Can one monitor how fast a table is imported?
select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
         round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
         trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min
  from   sys.v_$sqlarea
  where  sql_text like 'INSERT %INTO "%'
    and  command_type = 2
    and  open_versions > 0;

Can one export to multiple files?
exp SCOTT/TIGER FILE=D:F1.dmp,E:F2.dmp FILESIZE=10m LOG=scott.log

In case of low-performance system, it is better to add RECORDLENGTH parameter with tiny value to ensure that gzip has enough time to extract data before imp reads it:
imp system/pwd@sid RECORDLENGTH=4096 file=imp_pipe log=imp_pipe.log

exp file=    log=   full=  y direct=y

./exp manjit/manjit file=/var/backup/backup.dmp compress=y log=/var/backup/backup.log statistics=none full=y direct=y  ----full backup}

exp sys/sys file=full.dmp log=full.log statistics=none full=y

exp file=CP_owner1.dmp log=dec16.log full=y direct=y

imp sys/sys file=full.dmp log=full.log fromuser=schema1 touser=schema2 ignore=y


exp SCOTT/TIGER FILE=D:F1.dmp,E:F2.dmp FILESIZE=10m LOG=scott.log


No comments:

Post a Comment