Shulou(Shulou.com)06/01 Report--

Main differences between exp and expdp

EXP and IMP are client-side tools that can be used either on the client side or on the server side.

EXPDP and IMPDP are server-side tools. They can only be used on the ORACLE server, not on the client side.

Common import and export operations:

Exp system/manager buffer= buffer size (1024-4096) file= backup file name (* .dmp) full=y\\ system/manager login and password

Expdp system/oracle directory=hzvidmprd dumpfile=201090314.dmp schemas=MESPRD

Imp MESPRD/MESPRD file=e:/backup/20190313.dmp full=y ignore=y

Impdp hzruser/hzruser2017@mhdb directory=/home dumpfile=20180720.dmp full=y ignore=y

Impdp hzruser/hzruser2017@hzmestesr directory=/home dumpfile=20180720.dmp full=y ignore=y

Imp hzruser/hzruser2017@mhdb DIRECTORY=dpdata1 DUMPFILE=/home/20180720.dmp SCHEMAS=syssde

Impdp system/oracle directory=BACK_DMP dumpfile=201090314.dmp schemas=MESPRD ignore=y

Imp olddms/olddms@hzmestest file=D:\ app\ Administrator\ admin\ HZMESTEST\ dpdump\ GAC_OEM.dmp ignore=y full=y

Create a tablespace

Create tablespace httbs_mesprd datafile'e:\ app\ Administrator\ oradata\ hzvidmprd\ httbs_mesprd_1.DBF'

Size 2G

Autoextend on

Next 100M maxsize 4G

Extent management local

Add data files to the tablespace

Select file_name,tablespace_name from dba_data_files

Alter tablespace httbs_mesprd add datafile'e:\ app\ Administrator\ oradata\ hzvidmprd\ httbs_mesprd_2.DBF'

Size 100m

Autoextend on

Next 100M maxsize 4G

Select file_name,tablespace_name from dba_data_files

Alter tablespace httbs_mesprd add datafile'e:\ app\ Administrator\ oradata\ hzvidmprd\ httbs_mesprd_3.DBF'

Size 100m

Autoextend on

Next 100M

Delete tablespace

Drop tablespace httbs_mesprd including contents and datafiles

Specify tablespace to create user

Create user MESPRD identified by MESPRD

Default tablespace HTTBS_MESPRD

Authorization

Grant connect,resource to MESPRD

View and create a logical directory

Create or replace directory back_dmp as'/ u01swap backup'

Select * from dba_directories

Give the logical directory operation permission to the export user

Grant read,write on directory back_dmp to MESPRD

Export:

Export data by user

Expdp MESPRD/MESPRD@HZVIDMPRD schemas=MESPRD directory=HZMESVIDM dumpfile=20190304.dmp

Export data by tabl

Expdp sys/gacHZdba2018@plusbpdb tables=sys.aud$ dumpfile=sys.aud$.dmp directory=back_dmp

According to the table space guide

Expdp system/oracle directory=hzvidmprd DUMPFILE=mesprd_tablespace.dmp TABLESPACES=temp,example

Guide the entire database

Expdp system/oracle directory=hzvidmprd DUMPFILE=full.dmp FULL=y

Guide by query condition

Expdp system/oracle directory=hzvidmprd dumpfile= 201090314.dmp Tables=DS_QM_DEFECTINSPITEM query='WHERE atr_key=22726'

Truncated history table

Export manually (table)

Exp

/ u01/backup/sysaud.dmp

T

Yes

Yes

Sys.aud$

View the amount of data in the table

Select owner,table_name,SAMPLE_SIZE from dba_tables where owner='SYS' and table_name='AUD$'

View table size

Select SEGMENT_NAME,TABLESPACE_NAME,sum (BYTES/1024/1024) | |'M' from USER_extents where SEGMENT_TYPE='TABLE'

Group by SEGMENT_NAME,TABLESPACE_NAME

Truncation table

TRUNCATE TABLE SYS.AUD$

Import:

Specify the user to import data

Impdp MESPRD/MESPRD DIRECTORY=back_dmp DUMPFILE=20190304.dmp SCHEMAS=MESPRD

(import different user impdp MESPRD/MESPRD DIRECTORY=back_dmp DUMPFILE=20190304.dmp logfile=imp2.log REMAP_SCHEMA=vidm:MESPRD ignore=y)

Change the owner import data of the table

Impdp system/oracle directory=hzvidmprd DUMPFILE=20190304.dmp TABLES=mesprd.DS_QM_DEFECTINSPITEM REMAP_SCHEMA=mesprd:system

Import tablespace

Impdpsystem/oracle directory=hzvidmprd DUMPFILE=mesprd_tablespace.dmp TABLESPACES=example

Import database

Impdb system/oracle directory=hzvidmprd DUMPFILE=full.dmp FULL=y

Additional data

Impdp system/oracle directory=hzvidmprd DUMPFILE=20190304.dmp SCHEMAS=system TABLE_EXISTS_ACTION

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.