oracle导入导出

CREATE TABLESPACE META_RESULT_DATA DATAFILE '\data\META_RESULT_DATA.ora' SIZE 5G AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE DAMS_INDEX DATAFILE '/data/DAMS_INDEX.ora' SIZE 1G AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE DAMS_DATA DATAFILE '/data/tablespace1.dbf' SIZE 25G AUTOEXTEND ON NEXT 500M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K;

CREATE TABLESPACE DAMS_DATA DATAFILE '/data/tablespace1.dbf' SIZE 20G AUTOEXTEND ON NEXT 500M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K;

impdp DAMS/DAMS@10.121.182.4:1523/sjzcdb DIRECTORY=DATA_PUMP_DIR DUMPFILE=dams_biz_data_fs_20200402.dmp LOGFILE=dams_biz_data_fs1.log table_exists_action=REPLACE full=y ignore=y remap_schema=dams:dams remap_tablespace=A:B

/data/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xqzt)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

tnsping pdborcl

grant dba to dp identified by dp;

create or replace directory dp_dir as '/home/oracle';

grant read,write on directory dp_dir to dp;

echo $ORACLE_SID

select count(*) from scott.DEPT;

drop user scott cascade;

impdp dp/dp@pdborcl directory=dp_dir dumpfile=scott_pdborcl.dmp logfile=scott_pdborcl_imp.log schemas=scott

impdp DAMS/DAMS@192.168.3.173:1521/ORCLPDB DIRECTORY=dp_dir DUMPFILE=DAMS_20200320.DMP LOGFILE=dams.log full=y ignore=y schemas=DAMS table_exists_action=REPLACE remap_schema=DAMS_DP:dams

 

expdp chf/xff directory=test_dir dumpfile=t1_2.dmp network_link=dblink_test tables=T2_1
impdp chf/xff directory=test_dir network_link=dblink_test REMAP_SCHEMA=chf:xff REMAP_TABLESPACE=odu:users tables=t2_1
conn system/manger@orcl as sysdba
create directory dump_dir as 'd:\test\dump';
select * from dba_directories;
grant read,write on directory dump_dir to scott;
expdp scott/tiger@orcl schemas=scott dumpfile=expdp.dmp directory=dump_dir
expdp scott/tiger@orcl tables=emp,dept dumpfile=expdp.dmp directory=dump_dir
按查询条件导
expdp scott/tiger@orcl directory=dump_dir dumpfile=expdp.dmp tables=emp query='where deptno=20'
按表空间导
expdp system/manager@orcl directory=dump_dir dumpfile=tablespace.dmp tablespaces=temp,example
导整个数据库
expdp system/manager@orcl directory=dump_dir dumpfile=full.dmp full=y
只导出表结构
多加一个参数 : content=metadata_only
指定不到出的表：EXCLUDE=TABLE:"IN('T1','T2','T3')"

$ impdp gomyck/hy123123@orcl directory=dump_dir dumpfile=PMS1.dmp REMAP_SCHEMA=olduser:newuser remap_schema=SCYW:gomyck remap_tablespace=TS_SBTZ:GOMYCK TABLE_EXISTS_ACTION=TRUNCATE
SKIP:不管已经存在的表，直接跳过
APPEND:保持现有数据不变，导入源数据
TRUNCATE:删掉现有数据，导入源数据
REPLACE:删掉现有表，并重建，导入源数据

用多个文件分割一个导出文件
exp system/manager file=(paycheck_1,paycheck_2,paycheck_3,paycheck_4) log=paycheck, filesize=1G tables=hr.paycheck
--“完全”增量导出(complete)，即备份整个数据库
exp system/manager@服务命名 inctype=complete file=990702.dmp

--“增量型”增量导出(incremental)，即备份上一次备份后改变的数据
exp system/manager@服务命名 inctype=incremental file=990702.dmp
--“累计型”增量导出(cumulative)，即备份上一次“完全”导出之后改变的数据
exp system/manager@服务命名 inctype=cumulative file=990702.dmp
导出某个用户所拥有的数据库表：
exp 用户名/密码@服务命名 file=存放位置\存放文件名.dmp log=存放位置\存放文件名.log owner=拥有者用户名

估计导出文件的大小：
SELECT sum(bytes)/1024/1024/1024 "占用空间:单位GB" FROM dba_segments WHERE segment_type = 'TABLE';
--指定用户所属表的总字节数：
SELECT sum(bytes)
FROM dba_segments
WHERE owner = 'ynanPJ'
AND segment_type = 'TABLE';

SELECT sum(bytes)
FROM dba_segments
WHERE owner = 'ynanPJ'
AND segment_type = 'TABLE'
AND segment_name = 'AQUATIC_ANIMAL'

imp system/manager file=bible_db log=dible_db full=y ignore=y

导入一个或一组指定用户所属的全部表、索引和其他对象
imp system/manager file=seapark log=seapark fromuser=seapark
imp system/manager file=seapark log=seapark fromuser=(seapark,amy,amyc,harold)

将一个用户所属的数据导入另一个用户
imp system/manager file=tank log=tank fromuser=seapark touser=seapark_copy
imp system/manager file=tank log=tank fromuser=(seapark,amy) touser=(seapark1, amy1)
imp system/manager file=tank log=tank fromuser=seapark TABLES=(a,b)
从多个文件导入：
imp system/manager file=(paycheck_1,paycheck_2,paycheck_3,paycheck_4) log=paycheck, filesize=1G full=y

增量导入
imp system./manager inctype= RECTORE FULL=Y FILE=A

导出orcldev这个schema的所用对象[schemas or full]：
expdp orcldev/oracle@orcldev directory=backup_path dumpfile=orcldev_schema.dmp logfile=orcldev_schema_2017.log schemas=orcldev

导出orcldev这个用户下的某些表[tables]：
expdp orcldev/oracle directory=dackup_path dumpfile=orcldev_table.dmp logfile=orcldev_table_2017.log tables=(‘TAB_TEST‘,‘TAB_A‘)

只导出orcldev这个用户的元数据[content]：
expdp orcldev/oracle directory=dackup_pathdumpfile=orcldev_meta.dmp logfile=orcldev_meta_2017.log SCHEMAS=orcldev CONTENT=METADATA_ONLY

expdp orcldev/oracle directory=dackup_path dumpfile=orcldev_exclude.dmp logfile=orcldev_exclude.log SCHEMAS=orcldev EXCLUDE=index
expdp orcldev/oracle directory=dackup_path dumpfile=orcldev_exclude.dmp logfile=orcldev_exclude.log SCHEMAS=orcldev EXCLUDE=INDEX:"LIKE ‘TEST%‘" --导出这个orcldev方案，剔除以TEST开头的索引

expdp orcldev/oracle directory=dackup_path dumpfile=orcldev_exclude.dmp logfile=orcldev_exclude.log EXCLUDE=SCHEMA:"=‘SCOTT‘"
expdp orcldev/oracle directory=dackup_path dumpfile=orcldev_exclude.dmp logfile=orcldev_exclude.log EXCLUDE=USER:"=‘SCOTT‘"
--备份整库但剔除SCOTT这个用户的对象。　　
注意：include与exclude不能同时使用。

TABLESPACE导出表空间
eg:expdp orcldev/oracle directory=backup_path dumpfile=2017.dmplogfile =2017.log tablespaces=user,orcldev

expdp orcldev/oracle directory=backup_path dumpfile=2017.dmplogfile =2017.log full=Y VERSION=10.2.0.4

目标端：select userenv(‘language‘) from dual;
设置客户端字符集C:\>SET NLS_LANG= SIMPLIFIED CHINESE_CHINA.ZHS16GBK

创建逻辑目录，并赋予Oracle对其的读写权限
create directory dmp_dir as ‘C:\dump‘
grant read, write on directory dmp_dir to hlsbi;

导出单个用户数据：
expdp test/test@orcl directory=dmp_dir dumpfile=userdum.dmp schemas=hgmmo,hgmqo

导出test用户的emp和dept两个表
expdp system/orcl@orcl directory=dmp_dir dumpfile=tabledum.dmp tables=test.emp query=‘WHERE deptno=20‘

expdporcldev/oracle directory=dackup_path dumpfile=orcldev_parallel_%U.dmplogfile=orcldev_parallel_2013.log parallel=4
"%U"表示自动生成递增的序列号。

全库模式导入
impdp test/test@orcl directory=dmp_dir dumpfile=fulldb.dmp full=y

用户模式导入
impdp test/test@orcl directory=dmp_dir dumpfile=userdum.dmp schemas=hgmmo
impdp test/test@orcl directory=dmp_dir dumpfile=userdum.dmp remap_schema=user1:user2
remap_schema参数相当于imp工具中的fromuser和touser参数，可以实现将一个用户的数据导入到另一个用户中

表空间模式导入
impdp system/orcl@orcl directory=dmp_dir dumpfile=dumptbs.dmp tablespaces=tbs1

追加数据
impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=system TABLE_EXISTS_ACTION=append

.查询oracle版本信息
SQL>show parameter compatible
使用version参数导入导出数据
expdp test/test@orcl directory=dmp_dir dumpfile=userdum.dmp schemas=hgmmo version =11.2.0.0.0
impdp test/test@orcl directory=dmp_dir dumpfile=userdum.dmp schemas=hgmmo version =11.2.0.0.0

expdp chf/xff directory=test_dir dumpfile=t1_2.dmp network_link=dblink_test logfile=ynanPJ.log full=y EXCLUDE=TABLE:"IN('T1','T2','T3')"

 

1.首先要在目标库上创建文件夹，创建的名称用于impdp的参数directory,注意指定的文件夹要存在
create or replace directory DMPDIR as 'd:\dmp';
grant read,write on directory DMPDIR to public;

2.在目标库上创建公共的连接源数据库的datalink，用于impdp的network_link参数
create public database link sourcedatabase
connect to XXX IDENTIFIED BY XXXX
using 'XXXXX';

3.命令行执行，把源数据库上的用户e01导到目标库orcl上的e0311
Impdp utest/utest@orcl directory=DMPDIR schemas=e01 network_link=sourcedatabase remap_schema=e01:e0311 job_name=impjob

4.如果把执行窗口关闭，impdp仍然在后台运行，查看jobname
select * from v$datapump_job

用之前执行impdp的用户重新进入,命令行执行
impdp utest/utest attach=上面查到的jobname（如果之前已经指定jobname，就用之前那个jobname）

接下来可以执行help查看相关的命令，可以结束job如stop_job，或者回到执行的窗口如continue_client

expdp yt/ed456 directory=UWP_DIR dumpfile=NEW_INDEX_TOOL_NOW.dmp tables=NEW_INDEX_TOOL_NOW query="'where pub_time>to_date(''2019-12-15 00:00:00'',''yyyy-mm-dd hh24:mi:ss'')'"

expdp scott/oracle directory=xx dumpfile=2emp.dmp tables=emp query='emp:"WHERE deptno=20"'; ---注意后面这几个"'是"+'

expdp yt/ed456 directory=UWP_DIR dumpfile=NEW_INDEX_TOOL_NOW.dmp tables=NEW_INDEX_TOOL_NOW query="'where pub_time>to_date(''2019-12-15 00:00:00'',''yyyy-mm-dd hh24:mi:ss'')'"

C:/Users/Administrator/Desktop/122312342314.sql

expdp system/system_2021@127.0.0.1:1521/orcl directory=EXPDP_DIR dumpfile=EBOS_FILE_ATTACHMENT20211028.dmp logfile=EBOS_FILE_ATTACHMENT20211028.log tables=\(EBOS_FILE_ATTACHMENT\) query="'where Extract(year from CREATE_TIME_)=2021'"

ll | awk -v file_size=0 '{if($5==file_size)print$5 "" $0}'

ll | awk '$5>0'
过滤第一列等于2的行:$ awk '$1==2 {print $1,$3}' log.txt

content:是导出表结构和表数据参数。
compression:是压缩参数。all,data
remap_schema:替换用户参数。
remap_tablespace:替换表空间参数。

expdp scott/oracle directory=xx dumpfile=emp.dmp tables=emp query='emp:"WHERE deptno=20"';
例：
expdp system/system_2021@127.0.0.1:1521/orcl directory=EXPDP_DIR dumpfile=EBOS_FILE_ATTACHMENT20211028.dmp logfile=EBOS_FILE_ATTACHMENT20211028.log tables=EBOS_FILE_ATTACHMENT query='EBOS_FILE_ATTACHMENT:"WHERE id_ in (select ATTACH_UID_ from bgpj_score_attach)"'

impdp system/system_2021@127.0.0.1:1521/orcl dumpfile=EBOS_FILE_ATTACHMENT20211028.dmp logfile=EBOS_FILE_ATTACHMENT20211028.log directory=DATA_PUMP_DIR transform=segment_attributes:n

expdp scott/oracle directory=xx dumpfile=emp.dmp tables=emp,emp2,emp3 query='emp:"WHERE deptno=20"','emp2:"WHERE age=20"','emp3:"WHERE year=2020"'

示例2：要求把user_name='hh'的这条数据导出来如果query里的条件是字符串的话，就要用下面这种方法：

先写出一个参数文件txt.par
dumpfile=1emp.dmp
directory=xx
tables=(emp)
query=(emp:"where username='hh'")
然后执行导出语句：
expdp scott/oracle parfile=txt.par
例：
dumpfile=EBOS_FILE_ATTACHMENT20211028.dmp
directory=EXPDP_DIR
tables=(EBOS_FILE_ATTACHMENT)
query=(EBOS_FILE_ATTACHMENT:"where Extract(year from CREATE_TIME_)=2021")

如果是多张表
tables=(emp1,emp2,emp3)
query=(emp:"where username='hh'",emp2:"where username='hh'",emp3:"where username='hh'")