Custom Search
www.rocket99.com : Technical Guides Sybase Oracle UNIX Javascript


Technical Guides
Sybase
Oracle
UNIX
Javascript




Of Interest

Business Intelligence and Analytics



Oracle Training





Oracle » UNIX » Commands »

Import and Export

     



The import and export utilities are used to move data from one instance to another,
or one schema to another. The utility basically re-reruns all the DDL, in order,
to recreate and fill the tables.



/* Export sample call */

exp username/password feedback=100 file=filename.dat log=export.log

exp help=y

Keyword Description (Default) Keyword Description (Default)
--------------------------------------------------------------------------
USERID username/password FULL export entire file (N)
BUFFER size of data buffer OWNER list of owner usernames
FILE output files (EXPDAT.DMP) TABLES list of table names
COMPRESS import into one extent (Y) RECORDLENGTH length of IO record
GRANTS export grants (Y) INCTYPE incremental export type
INDEXES export indexes (Y) RECORD track incr. export (Y)
ROWS export data rows (Y) PARFILE parameter filename
CONSTRAINTS export constraints (Y) CONSISTENT cross-table consistency
LOG log file of screen output STATISTICS analyze objects (ESTIMATE)
DIRECT direct path (N) TRIGGERS export triggers (Y)
FEEDBACK display progress every x rows (0)
FILESIZE maximum size of each dump file
QUERY select clause used to export a subset of a table
VOLSIZE number of bytes to write to each tape volume

The following keywords only apply to transportable tablespaces
TRANSPORT_TABLESPACE export transportable tablespace metadata (N)
TABLESPACES list of tablespaces to transport



/* Import sample calls */

imp username/password feedback=100 file=filename.dat log=import.log

/* import a single table, table exists already */
imp admin23/portal feedback=100 file=company3.dat log=company.log tables='(company)' ignore=Y

imp help=y

USERID username/password FULL import entire file (N)
BUFFER size of data buffer FROMUSER list of owner usernames
FILE output file (EXPDAT.DMP) TOUSER list of usernames
SHOW just list file contents (N) TABLES list of table names
IGNORE ignore create errors (N) RECORDLENGTH length of IO record
GRANTS import grants (Y) INCTYPE incremental import type
INDEXES import indexes (Y) COMMIT commit array insert (N)
ROWS import data rows (Y) PARFILE parameter filename
LOG log file of screen output

DESTROY overwrite tablespace data file (N)
INDEXFILE write table/index info to specified file
CHARSET character set of export file (NLS_LANG)
POINT_IN_TIME_RECOVER Tablespace Point-in-time Recovery (N)
SKIP_UNUSABLE_INDEXES skip maintenance of unusable indexes (N)
ANALYZE execute ANALYZE statements in dump file (Y)
FEEDBACK display progress every x rows(0)




Bonus Script

#====================================================================
#!/usr/bin/ksh
#
# exp2imp: export Oracle table for import to secondary schema
#
# Parms: user1 table user2
#
#
#====================================================================

dbauser=oradba/dba@prod
tname=$2
user1=$1
user2=$3
tparm2="tables='$tname'"
tparm1="tables='$user1.$tname'"


echo "====================="
echo "Exporting $tparm1 ..."

exp $dbauser feedback=100 file=$tname.dat $tparm1 direct=Y log=/tmp/export.log


echo "====================="
sleep 5
echo "Importing $tparm2 ..."

imp $dbauser fromuser=$user1 touser=$user2 feedback=100 file=$tname.dat log=/tmp/import.log $tparm2 indexes=N ignore=Y


echo "========================================================="
echo `date` Done.
echo "========================================================="
















Oracle : Related Topics

Oracle : UNIX : Environment Settings for Oracle
Oracle : UNIX : SQL*Net
Oracle : UNIX : Using SQL-Plus
Oracle : UNIX : Initialization File
Oracle : UNIX : SQL Loader

Sybase Web Site
Sybase iAnywhere Mobile Web Site
Oracle Enterprise Web Site



Get the latest Rocket99 news and tech tips via






Site Index About this Guide to Sybase, Oracle, and UNIX Contact Us Advertise on this site




Copyright © 2016 Stoltenbar Inc All Rights Reserved.