Detailed steps for importing and exporting data from Oracle Data Pump (expdp)

Source database operation (data backup)

Custom variables

1. Query the custom variables of the current database (including the export data file path variable configuration, i.e. DUMP_DIR)

select * from dba_directories;

2. If there is no configuration, create a dump_dir (the variable name can be customized, and you can use a custom variable later)

create directory dump_dir as 'F:/dbback'

3. Export the data of the specified user, specify the exported user, export file name, export file path definition, and log file information during export

expdp PMS_ZS/000000@pms schemas=PMS_ZS dumpfile= directory=DUMP_DIR logfile=

After the above steps, the data from the source database will be backed up.

Target database operation (data recovery)

Create a user (skip if the user exists)

1. Query the tablespace and temporary tablespace of the current user

select default_tablespace,temporary_tablespace from dba_users where username = USER;

2. Create the corresponding temporary table space based on the table space queryed above (the temporary table space specifies the maximum capacity)

create temporary tablespace PMS_DATA_TMP_ZS
tempfile 'D:\Oracle\oradata\PMS\PMS_DATA_TMP_ZS.DBF'
size 32m
autoextend on
next 32m
maxsize 4096m
extent management local;

Modify the temporary table space to unlimited size (32G upper limit):

alter database tempfile 'D:\Oracle\oradata\PMS\PMS_DATA_TMP_ZS.DBF' autoextend on next 32m maxsize unlimited;

Add a new temporary tablespace file (fixed size must be set at this time)

alter tablespace temp add tempfile 'D:\Oracle\oradata\PMS\PMS_DATA_TMP_ZS_01.DBF' size 4096m;

Modify the second temporary tablespace file to unlimited size

alter database tempfile 'D:\Oracle\oradata\PMS\PMS_DATA_TMP_ZS_01.DBF' autoextend on next 32m maxsize unlimited;

3. Create the corresponding table space based on the table space queryed above

CREATE TABLESPACE PMS_DATA_ZS
DATAFILE 'D:\Oracle\oradata\PMS\PMS_DATA_ZS.DBF'
SIZE 32M
AUTOEXTEND ON NEXT 100M
MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;

-- Add data file 1 to the table space (one file supports up to 32G data, and multiple files can break through the maximum capacity of the table space 32G limit)

ALTER TABLESPACE PMS_DATA_ZS ADD DATAFILE 'D:\Oracle\oradata\PMS\PMS_DATA_ZS_01.DBF'
SIZE 32M
AUTOEXTEND ON NEXT 100M
MAXSIZE UNLIMITED;

-- Add data file 2 to the table space (one file supports up to 32G data)

ALTER TABLESPACE PMS_DATA_ZS ADD DATAFILE 'D:\Oracle\oradata\PMS\PMS_DATA_ZS_02.DBF'
SIZE 32M
AUTOEXTEND ON NEXT 100M
MAXSIZE UNLIMITED;

-- Add data file 3 to the table space (one file supports up to 32G data)

ALTER TABLESPACE PMS_DATA_ZS ADD DATAFILE 'D:\Oracle\oradata\PMS\PMS_DATA_ZS_03.DBF'
SIZE 32M
AUTOEXTEND ON NEXT 100M
MAXSIZE UNLIMITED;

-- Add data file 4 to the table space (one file supports up to 32G data)

ALTER TABLESPACE PMS_DATA_ZS ADD DATAFILE 'D:\Oracle\oradata\PMS\PMS_DATA_ZS_04.DBF'
SIZE 32M
AUTOEXTEND ON NEXT 100M
MAXSIZE UNLIMITED;

If the tablespace is created incorrectly, the excess tablespace data file can be deleted.

ALTER DATABASE DROP DATAFILE 'D:\Oracle\oradata\PMS\PMS_DATA_ZS01.DBF';

4. Create a user and specify a tablespace

create user PMS_ZS identified by PMSTEST2023
default tablespace PMS_DATA_ZS
temporary tablespace PMS_DATA_TMP_ZS;

5. Authorize the user

grant create session to PMS_ZS;
grant create table to PMS_ZS;
grant unlimited tablespace to PMS_ZS;
grant create any table to PMS_ZS;
grant select on dual to PMS_ZS；
grant insert on dual to PMS_ZS；
grant all on dual to PMS_ZS；
grant dba,connect,resource to PMS_ZS;

Import data

6. Import data

Before importing data, please check whether the path variable of the imported file has been defined.

The inspection method refers to the custom variable at the top of this article. If there is no definition, it needs to be created. Save the backup data file in the path corresponding to the variable.

impdp pms_zs/000000@pms dumpfile= DIRECTORY=dump_dir logfile= table_exists_action=replace

Summarize

This is the article about importing and exporting data from Oracle Data Pump (expdp). For more information about importing and exporting data from Oracle Data Pump (expdp) please search for my previous articles or continue browsing the related articles below. I hope you will support me in the future!