此处采用[[docker]]部署的oracle11g数据库 Docker部署Oracle11g并进行持久化

1.新建对应数据库表空间、用户实例


CREATE SMALLFILE TABLESPACE "HOS" DATAFILE'/home/oracle/app/oracle/oradata/helowin/hos.dbf' SIZE 6 G AUTOEXTEND ON NEXT 10 M MAXSIZE UNLIMITED BLOCKSIZE 8192 LOGGING EXTENT MANAGEMENT LOCAL FLASHBACK ON

2.创建用户HOS对应权限


CREATE USER "HOS" IDENTIFIED BY "123456" DEFAULT TABLESPACE "HOS";

GRANT "DBA" TO "HOS" WITH ADMIN OPTION;

ALTER USER "HOS" DEFAULT ROLE "DBA";

ALTER USER "HOS" QUOTA UNLIMITED ON "HOS";

GRANT SYSDBA TO "HOS"

3.新建dmp数据目录

CREATE OR REPLACE DIRECTORY "DATA_DPDUMP_DIR" AS '/home/oracle/app/oracle/admin/helowin/dpdump'

4.将.dmp文件从宿主机拷贝进目录中

docker cp .\hos.dmp oracle11g:/home/oracle/app/oracle/admin/helowin/dpdump

5.数据泵导入表空间

DECLARE h1 NUMBER; BEGIN h1 := DBMS_DATAPUMP.OPEN('IMPORT', 'TABLESPACE', NULL, 'HOS'); DBMS_DATAPUMP.ADD_FILE(h1, 'hos.dmp', 'DATA_DPDUMP_DIR', NULL, DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE); DBMS_DATAPUMP.METADATA_FILTER(h1, 'TABLESPACE_EXPR', 'IN (''HOS'')', NULL); DBMS_DATAPUMP.SET_PARAMETER(h1, 'TABLE_EXISTS_ACTION', 'REPLACE'); DBMS_DATAPUMP.START_JOB(h1, 0, 0, 1, NULL); DBMS_DATAPUMP.DETACH(h1); END;