Oracle imp/exp Import and Export commands

An operational http://www.diybl.com/course/7_databases/oracle/oraclejs/20071211/91845.html that gives users the permission to import data
First, start SQL * puls
Second, log in with system/manager
Third, create user username identified by password (this step can be omitted if you have already created a user)
Fourth, grant create user, drop user, alter user, create any view,
Drop any view, EXP_FULL_DATABASE, IMP_FULL_DATABASE,
DBA, CONNECT, RESOURCE, create session to Username
Fifth, run-cmd-to enter the directory where the dmp file is located,
Imp userid = system/manager full = y file = *. dmp
Or imp userid = system/manager full = y file = filename. dmp

Example:
F: WorkOracle_Databackup> imp userid = test/test full = y file = inner_policy.dmp

Appendix 2:
Oracle cannot directly change the table owner. Export/Import can be used to achieve this purpose.
First create import9.par,
The command is as follows: imp parfile =/filepath/import9.par
The content of import9.par is as follows:
FROMUSER = TGPMS
TOUSER = TGPMS2 (Note: You can change the table owner from FROMUSER to TOUSER. Users of FROMUSER and TOUSER can be different)
ROWS = Y
INDEXES = Y
GRANTS = Y
CONSTRAINTS = Y
Buffer= 409600
File =/backup/ctgpc_20030623.dmp
Log =/backup/import_20030623.log

Adding feedback = 1000 to the import/export command will display an increasing number of "..." to change the blinking cursor.

 

EXP

1 Exp command

Enter the command exp help = y to get the following prompt:
By entering the EXP command and user name/password, you can use the command with the user name/password:

Routine: exp scott/TIGER

Alternatively, you can enter the EXP command with various parameters to control "Export" based on different parameters.

To specify parameters, you can use the Keyword:

Format: exp keyword = value or KEYWORD = (value1, value2,..., valueN)
Routine: exp scott/tiger grants = y tables = (EMP, DEPT, MGR)
Or TABLES = (T1: P1, T1: P2). If T1 is a partitioned table

USERID must be the first parameter in the command line.

Keyword description (default) keyword description (default)
-----------------------------------------------------------------
USERID username/password FULL export the entire file (N)
BUFFER data BUFFER size OWNER User Name List
FILE output FILE (EXPDAT. DMP) TABLES Table Name List
Length of the IO record that COMPRESS imports to a partition (Y) RECORDLENGTH
GRANTS export permission (Y) INCTYPE incremental export type
INDEXES export index (Y) RECORD trace incremental Export (Y)
DIRECT path (N) TRIGGERS export trigger (Y)
The LOG file STATISTICS analysis object (ESTIMATE) output by the LOG Screen)
ROWS export data row (Y) PARFILE parameter file name
CONSISTENT cross tabulation consistency CONSTRAINTS export CONSTRAINTS (Y)

The progress of FEEDBACK is displayed on every x rows (0)
FILESIZE: maximum size of each dump.
FLASHBACK_SCN is used to call back the scn of the session snapshot.
FLASHBACK_TIME is used to obtain the time closest to the SCN of the specified time.
Select clause used by QUERY to export the subset of a table
When the RESUMABLE encounters a space-related error, it suspends (N)
RESUMABLE_NAME is a text string used to identify recoverable statements.
The waiting time of RESUMABLE_TIMEOUT RESUMABLE.
TTS_FULL_CHECK performs full or partial correlation check on TTS.
Table space list of TABLESPACES to Be Exported
TRANSPORT_TABLESPACE export the table space metadata that can be transferred (N)
TEMPLATE: the name of the TEMPLATE to be exported in iAS mode.

2. Imp command

Imp help = y
You can enter the IMP command and your user name/password, followed by the user name/PASSWORD command:

Routine: imp scott/TIGER

Alternatively, you can enter the IMP command and various parameters to control "import" based on different parameters.

To specify parameters, you can use the Keyword:
Format: imp keyword = value or KEYWORD = (value1, value2,..., vlaueN)
Routine: imp scott/tiger ignore = y tables = (EMP, DEPT) FULL = N
Or TABLES = (T1: P1, T1: P2). If T1 is a partitioned table

USERID must be the first parameter in the command line.

Keyword description (default) keyword description (default)
-----------------------------------------------------------------
USERID username/password FULL import the entire file (N)
BUFFER data BUFFER size FROMUSER User Name List
FILE input FILE (EXPDAT. DMP) TOUSER username list
SHOW only lists the file content (N) TABLES Table names
IGNORE ignores creation error (N) recordlength io record length
GRANTS import permission (Y) INCTYPE incremental Import Type
INDEXES import index (Y) COMMIT submit array insert (N)
ROWS import data row (Y) PARFILE parameter file name
Restrictions on importing LOG files output by LOG screens (Y)
DESTROY overwrite the tablespace data file (N)
INDEXFILE writes table/index information to the specified file
SKIP_UNUSABLE_INDEXES skips maintenance of unavailable indexes (N)
The progress of FEEDBACK is displayed on every x rows (0)
TOID_NOVALIDATE skips the verification of the specified type ID
FILESIZE: maximum size of each dump.
STATISTICS always imports pre-calculation STATISTICS
When the RESUMABLE encounters a space-related error, it suspends (N)
RESUMABLE_NAME is a text string used to identify recoverable statements.
The waiting time of RESUMABLE_TIMEOUT RESUMABLE.
COMPILE compilation process, package and function (Y)

The following keywords are only used for table spaces that can be transferred.
TRANSPORT_TABLESPACE import the deletable tablespace metadata (N)
TABLESPACES tablespace to be transmitted to the database
Data files to be transmitted to the database
TTS_OWNERS has users who can transmit table space centralized data.

3.Common EXP options

 

 

1. FULL. This is used to export the entire database. When ROWS = N is used together, the structure of the entire database can be exported.

For example:

 

exp sys file=./db.dmp log=./db.log full=y rows=n compress=y direct=y

 

2. BUFFER and FEEDBACK. When exporting a large amount of data, I will consider setting these two parameters. For example:

Exp newfile = db. dmp log = yw97_2003_3.log feedback = 10000
Buffer = 100000000 tables = WO4, OK _YT

 

3. FILL and LOG. These two parameters respectively specify the DMP name and LOG name of the backup, including the file name and directory.

 

 

Note that EXP can be directly backed up to the tape, that is, FILE =/d

 

 

 

If (exists (select * from dbo. sysobjects where name = 't_ Roles '))

Drop table T_Roles;

Create table T_Roles

(

RoleId int identity not null, -- Role Id

[Name] NVarchar (100) not null unique, -- role Name

Remark NVarchar (200), -- Role remarks

Primary key (RoleId)

);