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=
exp halim/halim file=c:\emp\uw_test.dmp
--------------------------------
Log File Name
exp userid=
exp halim/halim log=c:\emp.log
------------------------------------------------
Buffer size -- O/S dependent and can usually be ignored
exp userid=
-- 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=
exp halim/halim compress=N
--------------------------------------------
Consistent (default is N) -- Implements SET TRANSACTION READ ONLY
exp userid=
exp halim/halim file=c:\emp\cnsstnt.dmp consistent=Y
--------------------------------------------------------
Constraints (default is Y)
exp userid=
exp halim/halim file=c:\emp\cnstrnt.dmp constraints=N
--------------------------------------------------------------
Direct Path (default is N)
exp userid=
exp halim/halim file=c:\emp\dirpath.dmp direct=Y
-----------------------------------------------
Feedback (default is 0)
exp userid=
exp halim/halim file=c:\emp\back.dmp feedback=100
---------------------------------------------------
File Size (default unlimited)
exp userid=
exp halim/halim file=c:\emp\size.dmp filesize 100MB
---------------------------------------------------------------
Flashback By SCN
exp userid=
exp halim/halim file=c:\emp\bscn.dmp flashback_scn=4567892
------------------------------------------------------------
Flashback By Timestamp
exp userid=
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=
exp halim/halim full=Y
----------------------------------------------------------------
Grants
exp userid=
exp halim/halim grants=N
--------------------------------------------
Indexes
exp userid=
exp halim/halim indexes=N
----------------------------------------------
Object Consistent
exp userid=
exp halim/halim object_consistent=Y
---------------------------------------------
Owner
exp userid=
exp halim/halim owner=(halim, ids, webapps)
-----------------------------------------
Parameter File
exp userid=
exp halim/halim parfile=c: emp\uwparfile.ctl
--------------------------------------------------
Query
exp userid=
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=
exp halim/halim recordlength=32000
---------------------------------------------------------
Resumable
exp userid=
exp halim/halim resumable=Y
------------------------------------------------------------
Resumable Name
exp userid=
exp halim/halim resumable_name 'halim'
-----------------------------------------------------------------
Resumable Time Out
exp userid=
exp halim/halim resumable_timeout=18000
-------------------------------------------------------------
Rows
exp userid=
exp halim/halim rows=N
-------------------------------------------------------------
Statistics
exp userid=
STATISTICS=
exp halim/halim statistics=COMPUTE
-------------------------------------------------------------
Tables
exp userid=
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=
exp halim/halim transport_tablespace=Y
-------------------------------------------------------------
Triggers
exp userid=
exp halim/halim triggers=N
-------------------------------------------------------------
TTS Full Check
exp userid=
exp halim/halim tts_full_check=Y
-------------------------------------------------------------
Volume Size
exp userid=
exp halim/halim volsize=10GB
No comments:
Post a Comment