Wednesday, December 29, 2010

Export backup script of oracle database

===================================================
Export backup script of oracle database
===================================================


You can let Export prompt you for parameters by entering the EXP
command followed by your username/password:

Example: EXP SCOTT/TIGER

Or, you can control how Export runs by entering the EXP command followed
by various arguments. To specify parameters, you use keywords:

Format: EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.

Keyword Description (Default)
======= =====================
USERID- username/password

BUFFER- size of data buffer

FILE - output files (EXPDAT.DMP)

COMPRESS- import into one extent (Y)

GRANTS - export grants (Y)

INDEXES - export indexes (Y)

DIRECT - direct path (N)

LOG - log file of screen output

ROWS - export data rows (Y)

CONSISTENT - cross-table consistency(N)

FULL - export entire file (N)

OWNER - list of owner usernames

TABLES - list of table names

RECORDLENGTH - length of IO record

INCTYPE - incremental export type

RECORD- track incr. export (Y)

TRIGGERS- export triggers (Y)

STATISTICS- analyze objects (ESTIMATE)

PARFILE - parameter filename

CONSTRAINTS - export constraints (Y)

OBJECT_CONSISTENT- transaction set to read only during object export (N)

FEEDBACK - display progress every x rows (0)

FILESIZE - maximum size of each dump file

FLASHBACK_SCN - SCN used to set session snapshot back to

FLASHBACK_TIME - time used to get the SCN closest to the specified time

QUERY - select clause used to export a subset of a table

RESUMABLE - suspend when a space related error is encountered(N)

RESUMABLE_NAME - text string used to identify resumable statement

RESUMABLE_TIMEOUT - wait time for RESUMABLE

TTS_FULL_CHECK -perform full or partial dependency check for TTS

TABLESPACES - list of tablespaces to export

TRANSPORT_TABLESPACE- export transportable tablespace metadata (N)

TEMPLATE -template name which invokes iAS mode export




With Example
=================
In CMD command line window.

Export help
exp -help
exp -help
--------------------------------
Export user
exp userid=
exp halim/halim
--------------------------------
Export File Name
exp userid= FILE=
exp halim/halim file=c:\emp\uw_test.dmp
--------------------------------
Log File Name
exp userid= LOG=
exp halim/halim log=c:\emp.log
------------------------------------------------
Buffer size -- O/S dependent and can usually be ignored
exp userid= BUFFER=
-- rows_in_array * maximum_row_size
exp halim/halim buffer=64000
--------------------------------------------
Compress (default is Y) -- The default is Y and it is best to override it.
exp userid= COMPRESS=
exp halim/halim compress=N
--------------------------------------------
Consistent (default is N) -- Implements SET TRANSACTION READ ONLY
exp userid= CONSISTENT=
exp halim/halim file=c:\emp\cnsstnt.dmp consistent=Y
--------------------------------------------------------
Constraints (default is Y)
exp userid= CONSTRAINTS=
exp halim/halim file=c:\emp\cnstrnt.dmp constraints=N
--------------------------------------------------------------
Direct Path (default is N)
exp userid= DIRECT=
exp halim/halim file=c:\emp\dirpath.dmp direct=Y
-----------------------------------------------
Feedback (default is 0)
exp userid= FEEDBACK=
exp halim/halim file=c:\emp\back.dmp feedback=100
---------------------------------------------------
File Size (default unlimited)
exp userid= FILESIZE= [KB|MB]
exp halim/halim file=c:\emp\size.dmp filesize 100MB
---------------------------------------------------------------
Flashback By SCN
exp userid= FLASHBACK_SCN=
exp halim/halim file=c:\emp\bscn.dmp flashback_scn=4567892
------------------------------------------------------------
Flashback By Timestamp
exp userid= FLASHBACK_TIME=
exp halim/halim FLASHBACK_TIME="TIMESTAMP '2002-05-01 11:00:00'"
or
exp halim/halim flashback_time="TO_TIMESTAMP('12-02-2001 14:35:00','DD-MM-YYYY HH24:MI:SS')"
-----------------------------------------------------------
Full -- Yes requires the user to have the EXP_FULL_DATABASE role
exp userid= FULL=
exp halim/halim full=Y
----------------------------------------------------------------
Grants
exp userid= GRANTS=
exp halim/halim grants=N
--------------------------------------------
Indexes
exp userid= INDEXES=
exp halim/halim indexes=N
----------------------------------------------
Object Consistent
exp userid= OBJECT_CONSISTENT=
exp halim/halim object_consistent=Y
---------------------------------------------
Owner
exp userid= OWNER=(o1, o2, ... o#)
exp halim/halim owner=(halim, ids, webapps)
-----------------------------------------
Parameter File
exp userid= PARFILE=
exp halim/halim parfile=c: emp\uwparfile.ctl
--------------------------------------------------
Query
exp userid= QUERY=
exp halim/halim owner=SCOTT tables=emp
query=\"WHERE job=\'MANAGER\' AND sal \>50000\"
exp scott/tiger@orcl file=C:/emp_depno.dmp tables=emp query=\"where deptno=10\"
--------------------------------------------------------
Record Length
exp userid= RECORDLENGTH=
exp halim/halim recordlength=32000
---------------------------------------------------------
Resumable
exp userid= RESUMABLE=
exp halim/halim resumable=Y
------------------------------------------------------------
Resumable Name
exp userid= RESUMABLE_NAME = 'User USERNAME (USERID), Session SESSIONID, Instance INSTANCEID'
exp halim/halim resumable_name 'halim'
-----------------------------------------------------------------
Resumable Time Out
exp userid= RESUMABLE_TIMEOUT= DEFAULT 7200 (2 hours)
exp halim/halim resumable_timeout=18000
-------------------------------------------------------------
Rows
exp userid= ROWS=
exp halim/halim rows=N
-------------------------------------------------------------
Statistics
exp userid=
STATISTICS=
exp halim/halim statistics=COMPUTE
-------------------------------------------------------------
Tables
exp userid= TABLES=(t1, t2, ... t#)
exp halim/halim tables=(emp, dept, bonus)
-------------------------------------------------------------
Tablespaces
exp userid=
TABLESPACES=(tbsp1, tbsp2, ... tbsp#)
exp halim/halim tablespaces=(uwdata, user_data)
-------------------------------------------------------------
Transportable Tablespaces
exp userid= TRANSPORT_TABLESPACE
exp halim/halim transport_tablespace=Y
-------------------------------------------------------------
Triggers
exp userid= TRRIGGERS
exp halim/halim triggers=N
-------------------------------------------------------------
TTS Full Check
exp userid= TTS_FULL_CHECK
exp halim/halim tts_full_check=Y
-------------------------------------------------------------
Volume Size
exp userid= VOLSIZE [KB|MB|GB]
exp halim/halim volsize=10GB

No comments: