Oracle Expdp Impdp 数据泵导入导出

布满荆棘的人生 2022-10-02 01:56 5阅读 0赞

[2019独角兽企业重金招聘Python工程师标准>>> ][2019_Python_] ![hot3.png][]

**导出**

sql>conn username/password@orcl as sysdba;
    sql>create directory dmp_dir as 'd:\temp';
    sql>select * from dba_directories;
    
    sql>grant read,write on directory dmp_dir to username;
    
    sql>grant exp_full_database to username;
    sql>grant imp_full_database to username;
    
    sql>alter user username quota unlimited on user_tablespace ; 
    
    c:\expdp username/password@ip/orcl DIRECTORY=dmp_dir SCHEMAS=schema_name(username) dumpfile=XXX.dmp logfile=XXX.log 
    
    -- content=metadata_only : 表结构
    -- include=TABLE,VIEW,Procedure,sequence (表、视图、存储过程、序列)
    c:\expdp username/password@ip/orcl DIRECTORY=dmp_dir SCHEMAS=schema_name(username) dumpfile=XXX.dmp logfile=XXX.log CONTENT=METADATA_ONLY INCLUDE=TABLE,VIEW,Procedure,sequence

新建逻辑目录，Oracle不会自动创建实际的物理目录“D:\\temp”（务必先手动创建此目录然后再执行oracle 导出目录定义），仅仅是进行定义逻辑路径dmp\_dir；

sql>conn username/password@orcl as sysdba;
    sql>create directory dmp_dir as 'd:\temp';
    sql>select * from dba_directories;

oracle 定义路径前必须创建物理地址，不然会出现如下错误。

ORA-39002: invalid operation
        ORA-39070: Unable to open the log file.
        ORA-29283: invalid file operation
        ORA-06512: at "SYS.UTL_FILE", line 536
        ORA-29283: invalid file operation

确认目录存在，当出现如下错误，看看是否有授权

ORA-39002: invalid operation
    ORA-39070: Unable to open the log file.
    ORA-39087: directory name DUMP_DIR is invalid

解决方法：

grant read,write on directory dump_dir to username;

导出时出现如下错误，表示没有给用户授权（grant exp\_full\_database to username;）

ORA-31631: privileges are required
    
        ORA-39161: Full database jobs require privileges

导入时出现如下错误，表示没有给用户授权（grant imp\_full\_database to username;）

ORA-31631: privileges are required
    
        ORA-39122: Unprivileged users may not perform REMAP_SCHEMA remappings.

表空间已满提示错误：

ORA-31626: job does not exist
        ORA-31633: unable to create master table "DRM_PFM.SYS_EXPORT_FULL_06"
        ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
        ORA-06512: at "SYS.KUPV$FT", line 1020
        ORA-01658: unable to create INITIAL extent for segment in tablespace TS_PFM

查看表空间情况：

SELECT a.tablespace_name "表空间名",
           total / 1024 / 1024 "表空间大小单位[M]",
           free / 1024 / 1024 "表空间剩余大小单位[M]",
           (total - free) / 1024 / 1024 "表空间使用大小单位[M]",
           Round((total - free) / total, 4) * 100 "使用率   [%]"
      FROM (SELECT tablespace_name, Sum(bytes) free
              FROM DBA_FREE_SPACE
             GROUP BY tablespace_name) a,
           (SELECT tablespace_name, Sum(bytes) total
              FROM DBA_DATA_FILES
             GROUP BY tablespace_name) b
     WHERE a.tablespace_name = b.tablespace_name;

解决方法：

-- 增加表空间容量
    ALTER DATABASE DATAFILE 'D:\DEVELOP\DATABASE\TABLESPACE\TS_PFM.DBF' RESIZE 200M;
    
    -- 修改表空间自动扩展
    ALTER DATABASE DATAFILE 'D:\DEVELOP\DATABASE\TABLESPACE\TS_PFM.DBF' AUTOEXTEND ON NEXT 100M MAXSIZE 1G;

如果表空间设置了自动扩展，查询到的表空间容量快满了，手动执行表空间容量增加；我的就是设置了表空间自动扩展的，但是expdp 导出的时候还是报错，通过手动增加容量解决问题。

文件已存在提示错误，删除之前因为出现错误创建的文件重新执行即可。

ORA-39001: invalid argument value
        ORA-39000: bad dump file specification
        ORA-31641: unable to create dump file "d:\temp\oracle\drm_pfm0711.dmp"
        ORA-27038: created file already exists
        OSD-04010: <create> option specified, file already exists

根据用户导出数据提示

ORA-31626: job does not exist
    ORA-31633: unable to create master table "DRM_PFM.SYS_EXPORT_SCHEMA_05"
    ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
    ORA-06512: at "SYS.KUPV$FT", line 1020
    ORA-01950: no privileges on tablespace 'TS_PFM'

解决办法

SQL> alter user username quota unlimited on tablespace_name ;

**导入**

角色确实不存在忽略即可，如果还是导入不了看看是否是表空间不足。

IMP-00058: ORACLE error 1653 encountered
    ORA-01653: unable to extend table user.testTable by 8 in tablespace TS_test
    IMP-00028: partial import of previous table rolled back: 10 rows rolled back
    IMP-00017: following statement failed with ORACLE error 1917: 
               "GRANT SELECT ON "testTable" TO "testUser""
    IMP-00003: ORACLE error 1917 encountered
    ORA-01917: user or role 'testUser' does not exist

ORA-39006: internal error
    ORA-39213: Metadata processing is notavailable

SQL> execute  sys.dbms_metadata_util.load_stylesheets;

转载于:https://my.oschina.net/fengzhi714/blog/1844559

[2019_Python_]: https://my.oschina.net/u/2663968/blog/3061697
[hot3.png]: /images/20220113/758e7b53b54641c0bd6cb921a8ef40fd.png