oracle数据泵

/*
1)逻辑备份恢复：数据泵
2)物理备份恢复：Rman

数据泵技术（源端-》目标端）
源端 启动Data Pump Job(expdp)
目标端 启动Data Pump Job(impadp)
1)数据泵核心部分程序包：DBMS_DATAPUMP
2)提供元数据的程序包:DBMS_MATADATA
3)命令行客户机（实用程序）：EXPDP,IMPDP

数据泵文件
1)转储文件：文件包含对象数据
2)日志文件：记录操作信息和结果
3)SQL文件：将导入作业中的DDL语句写入SQLFILE指定的参数文件中

数据泵目录（逻辑目录）和文件位置
使用sys和system用户完成数据导入导出，可以使用缺省目录 DATA_PUMP_DIR
*/
-- expdp
-- 创建备份文件的目录
select * from dba_directories;
create or replace directory DATA_DIR as '/data/cdbl_data';
-- 用户赋予在指定目录的操作权限
grant read,write on directory DATA_DIR to VPDBA; --这里的VPDBA 属于用户

-- 查看授权结果
select * from DBA_TAB_PRIVS where GRANTEE ='FINANCE';

================================================================================以自己的虚拟机作为例子 把scott用户下的users表备份出来
================================================================================================================================================================
select * from dba_data_files where TABLESPACE_NAME='TBS_ZDXF_DATA2';==查看表空间

-- 创建表空间
CREATE TABLESPACE "TBS_ZDXF_DATA2"
DATAFILE 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\TBS_ZDXF_DATA02.dbf' size 1024m
autoextend on
next 1024m;

-- 创建临时表空间
create temporary tablespace TBS_ZDXF_DATA2_TEMP
tempfile 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\TBS_ZDXF_DATA_TEMP03.dbf'
size 1024m
autoextend on
next 1024m;

/opt/oracle/admin/orcl/dpdump/ 这个是directory的目录

grant read,write on directory DATA_PUMP_DIR to scott;

====这个不是在sqlplus里面执行
expdp scott/[email protected]/orcl tables=users dumpfile=scott_user.dmp directory=DATA_PUMP_DIR logfile=scott_user.log

expdp scott/tiger tables=users dumpfile=scott_user.dmp directory=DATA_PUMP_DIR logfile=scott_user.log
select * from dba_directories;

CREATE USER zdxf2 IDENTIFIED BY zdxf2 DEFAULT TABLESPACE TBS_ZDXF_DATA2 TEMPORARY TABLESPACE TBS_ZDXF_DATA2_TEMP;
GRANT DBA TO zdxf2;
ALTER USER zdxf2 DEFAULT ROLE DBA;

impdp zdxf2/zdxf2 remap_schema=scott:zdxf2 directory=DATA_PUMP_DIR dumpfile=scott_user.dmp remap_tablespace=USERS:TBS_ZDXF_DATA2
================================================================================================================================================================
================================================================================================================================================================

-- impdp
-- 创建表空间 finance
CREATE TABLESPACE "TBS_RISK_MARKET_DATA"
DATAFILE '/data/oradata/dw/TBS_RISK_MARKET_DATA01.dbf' size 10g
autoextend on
next 1024m;

--MARKETS用户
CREATE TABLESPACE "MARKETS_TABLESPACE"
DATAFILE '/data/oradata/dw/MARKETS_TABLESPACE01.dbf' SIZE 10g
AUTOEXTEND ON NEXT 1024m;
CREATE USER "MARKETS" IDENTIFIED BY "MARKETS" DEFAULT TABLESPACE "MARKETS_TABLESPACE" TEMPORARY TABLESPACE "TEMP";
GRANT "DBA" TO "MARKETS";
ALTER USER "MARKETS" DEFAULT ROLE "DBA";

-- 创建临时表空间
create temporary tablespace TBS_RISK_MARKET_TEMP
tempfile '/data/oradata/dw/TBS_RISK_MARKET_TEMP01.dbf'
size 2048m
autoextend on
next 1024m;

CREATE USER "RISK_MARKET" IDENTIFIED BY "rm123" DEFAULT TABLESPACE "TBS_RISK_MARKET_DATA" TEMPORARY TABLESPACE "TBS_RISK_MARKET_TEMP";

GRANT "DBA" TO "RISK_MARKET";
ALTER USER "RISK_MARKET" DEFAULT ROLE "DBA";

-- 查询表空间使用情况
SELECT
UPPER( F.TABLESPACE_NAME ) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR( ROUND( ( D.TOT_GROOTTE_MB - F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2 ), '990.99' ) "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM
(
SELECT
TABLESPACE_NAME,
ROUND( SUM( BYTES ) / ( 1024 * 1024 ), 2 ) TOTAL_BYTES,
ROUND( MAX( BYTES ) / ( 1024 * 1024 ), 2 ) MAX_BYTES
FROM
SYS.DBA_FREE_SPACE
GROUP BY
TABLESPACE_NAME
) F,
(
SELECT
DD.TABLESPACE_NAME,
ROUND( SUM( DD.BYTES ) / ( 1024 * 1024 ), 2 ) TOT_GROOTTE_MB
FROM
SYS.DBA_DATA_FILES DD
GROUP BY
DD.TABLESPACE_NAME
) D
WHERE
D.TABLESPACE_NAME = F.TABLESPACE_NAME
and F.TABLESPACE_NAME='TEST_ODS_TABLESPACE';

-- 增加表空间
ALTER tablespace TEST_ODS_TABLESPACE ADD datafile '/data/oradata/dw/test_ods05.dbf' SIZE 30g;

select * from dba_data_files where TABLESPACE_NAME='TEST_ODS_TABLESPACE';
-- 查看管理员目录
select * from dba_directories;
-- 用户赋予在指定目录的操作权限
grant read,write on directory DATA_DIR to TEST_ODS;

select * from V$VERSION;