Oracle数据导入导出

 

数据导出

exp 
根据用户导出
exp 'wwmh/oracle@orcl2' rows=y file=exp_goldengate.dmp log=exp2.log owner=user

根据用户远程导出
exp sjsyhlj/oracle1@'192.168.3.219:1521/syxk' file=/home/oracle/sjsyhlj_data.dmp owner=sjsyhlj

expdp
select * from dba_directories;
expdp wwmh/oracle@orcl2 directory=DATA_PUMP_DIR dumpfile=wwmh.dmp schemas=wwmh logfile=wwmh.log


过滤掉临时表

  expdp topicis/oracle@orcl2 directory=DATA_PUMP_DIR dumpfile=topicis.dmp schemas=topicis exclude=table:\"like \'BIN$%\'\" logfile=topicis.log

 

 

#排除某一张表 exclude=table:\"in\(\'tablename\'\)\"

#排除某几张表 exclude=table:\"in\(\'tablename01\'\,\'tablename02\'\,\'tablename03\'\)\"

#排除某些字母开头的表 exclude=table:\"like \'t%\'\"

 

数据导入

imp 用户导入

[oracle@topdb ~]$ imp \'sjsyhlj/oracle1@orcl4 \' file=/home/oracle/sjsyhlj_data.dmp log=exp2.log fromuser=sjsyhlj touser=sjsyhlj full=n ignore=y

impdp用户导入

[oracle@topdb ~]$ impdp topicpsp/oracle@orcl4 directory=DATA_PUMP_DIR dumpfile=wwmh.dmp schemas=wwmh logfile=wwmh2.log   

impdp topicpsp/oracle@orcl4 directory=DATA_PUMP_DIR dumpfile=wwmh.dmp schemas=wwmh logfile=wwmh2.log transform=disable:table_name:LOG_INTERFACEAPIERROR_HIS,disable:table_name:LOG_INTERFACE_API_ICPSP,disable:table_name:LOG_MQLISTENERINFO

 

 

 

 

 

 表空间创建

# 查看表空间位置

SELECT file_id, tablespace_name, file_name, bytes/1024/1024 AS size_mb, autoextensible, maxbytes/1024/1024 AS max_size_mb

FROM dba_data_files

ORDER BY tablespace_name, file_id;

 

-- 创建一个数据文件类型表空间

CREATE TABLESPACE wwmh
DATAFILE '/data/oracle/app/oradata/orcl4/wwmh.dbf'
SIZE 100M
AUTOEXTEND ON
NEXT 10M
MAXSIZE UNLIMITED;

 CREATE USER sjsyhlj identified BY oracle1

ALTER USER wwmh DEFAULT TABLESPACE wwmh;
ALTER USER wwmh TEMPORARY TABLESPACE temp; -- 或者你数据库中的临时表空间名称

 

 

expdp topicis/oracle@orcl2 directory=DATA_PUMP_DIR dumpfile=topicis.dmp schemas=topicis exclude=table:\"like \'BIN$%\'\" logfile=topicis.log

CREATE TABLESPACE topicis
DATAFILE '/data/oracle/app/oradata/orcl4/topicis.dbf'
SIZE 100M
AUTOEXTEND ON
NEXT 10M
MAXSIZE UNLIMITED;


CREATE USER topicis identified BY oracle;

ALTER USER topicis DEFAULT TABLESPACE topicis;
ALTER USER topicis TEMPORARY TABLESPACE temp; -- 或者你数据库中的临时表空间名称

GRANT dba TO topicis;


impdp topicpsp/oracle@orcl4 directory=DATA_PUMP_DIR dumpfile=wwmh.dmp schemas=wwmh logfile=wwmh2.log   loglevel=INFO

 

 

查看数据库表占用大小

SELECT 
SEGMENT_NAME TABLENAME
,(BYTES/1024/1024) MB
,RANK() OVER (PARTITION BY NULL ORDER BY BYTES DESC) RANK_ID  //根据表大小进行排序
FROM USER_SEGMENTS
WHERE SEGMENT_TYPE='TABLE'