Oracle 12c expdp&impdp

OS

Windows

Oracle Version

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit
Production

Expdp

1. 在c盘创建dump文件夹，允许写
2. 以sys用户连接oracle，创建逻辑目录，该命令不会在操作系统创建真正的目录，最好以system等管理员创建。
   create directory dpdata1 as 'c:\dump';
3. 查看管理理员目录（同时查看操作系统是否存在，因为Oracle并不关心该目录是否存在，如果不存在，则出错，所以要有第1步）
   select * from dba_directories;
4. 给vkc2用户赋予在指定目录的操作权限，最好以system等管理员赋予。
   grant read,write on directory dpdata1 to vkc2;
5. 执行命令(在cmd命令行里)

按用户导出

expdp user/[email protected]*.***.***.***/xe dumpfile=full_20180
702.dmp  DIRECTORY=dpdata1 schemas=test

导出整个数据库

expdp '/ as sysdba' DIRECTORY=dpdata1 DUMPFILE=full_20180
702.dmp FULL=y PARALLEL=3 LOGFILE=export_20180702.log

Impdp

在sql developer里面，以sys身份连接 创建表空间

create tablespace tabspace
logging datafile 'datafilepath_C:\ORACLEXE\APP\ORACLE\ORADATA\XE\tabspace.DBF' 
size 50m autoextend on next 50m maxsize 10768m extent management local;

创建新的用户

Alter session set “_oracle_script“=true;
DROP USER testuser CASCADE;  
CREATE USER testuser identified by testuser default tablespace tabspace temporary tablespace TEMP;   
GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO testuser;
create directory dpdata1 as 'c:\dump';
grant read,write on directory dpdata1 to testuser;

把dmp文件拷贝到 C:\dump，运行cmd

impdp testuser/testuser DIRECTORY=dpdata1 
DUMPFILE=full_20180702.dmp FULL=y 

Note

1. 官网原文：Data Pump Export
2. 表空间查询(sqlplus / as sysdba)
   表空间使用情况

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
  ORDER BY 4 DESC;

表空间是否具有自动扩展的能力

SELECT T.TABLESPACE_NAME,D.FILE_NAME,D.AUTOEXTENSIBLE,ROUND(D.BYTES / (1024 * 1024), 2) BYTES_MB,ROUND(D.MAXBYTES/ (1024 * 1024), 2) MAXBYTES_MB,D.STATUS  
FROM DBA_TABLESPACES T,DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME
ORDER BY TABLESPACE_NAME,FILE_NAME;

操作表空间文件

   alter tablespace system add datafile 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF' resize 1024M;
   alter database datafile 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF' autoextend on;  
   alter database datafile 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF' resize 1024M;

清理多余表空间

SELECT T.TABLESPACE_NAME,D.FILE_NAME,D.AUTOEXTENSIBLE,ROUND(D.BYTES / (1024 * 1024), 2) BYTES_MB,ROUND(D.MAXBYTES/ (1024 * 1024), 2) MAXBYTES_MB,D.STATUS  
FROM DBA_TABLESPACES T,DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME
ORDER BY TABLESPACE_NAME,FILE_NAME;

DROP TABLESPACE test INCLUDING CONTENTS AND DATAFILES;

create tablespace test
logging 
datafile 'C:\APP\ROOT\ORADATA\XE\test.DBF' 
size 50m 
autoextend on 
next 50m maxsize 10768m 
extent management local;