Shulou(Shulou.com)06/01 Report--

Oracle11g database migration to Oracle12C's PDB (using impdp/expdp)

Alter pluggable database hrdb close immediate

DROP PLUGGABLE DATABASE hrdb INCLUDING DATAFILES

-- data backup

Mkdir / oradata/dumpdir

-- create a directory

Create or replace directory exp_dir as'/ oradata/dumpdir'

-Authorization

Grant read,write on directory exp_dir to hradm

-- query

Select * from dba_directories

Expdp admin/admin file=hr.dmp directory=exp_dir schemas=hradm

-- Delete

Drop directory exp_dir

-second, import data into Oracle12C

-- Log into cpcdb

$sqlplus sys/Oracleedw13@192.168.2.218:1531/hrdb as sysdba

-- create a directory

Create or replace directory exp_dir as'/ oradata/dumpdir'

-Authorization

Grant read,write on directory exp_dir to hradm

-- query

Select * from dba_directories

-- create tbscpc tablespaces

Create tablespace tbshr

Datafile'/ oradata/edw/hrdb/hrdb-001.dbf'

Size 2G

Autoextend on

Next 64m maxsize 20G

Segment space management auto

Extent management local

Alter tablespace tbshr

Add datafile'/ oradata/edw/hrdb/hrdb-002.dbf' size 2G

Autoextend on

Next 50m maxsize 20G

Alter tablespace tbshr

Add datafile'/ oradata/edw/hrdb/hrdb-003.dbf' size 2G

Autoextend on

Next 50m maxsize 20G

-- authorize cpcadm and admin and modify the default tablespace

Grant dba,resource to hradm

Select * from dba_users where username='HRADM'

Alter user hradm default tablespace tbshr

Alter database default tablespace tbshr

Create user admin identified by admin

Grant connect,dba to admin

-- Import hrdb data using impdp

Impdp admin@hrdb directory=exp_dir dumpfile=hr20180401004000.DMP remap_tablespace=tbs_hr:tbshr remap_schema=hr:hradm schemas=hr table_exists_action=replace transform=segment_attributes:n

Remap_tablespace=tbs_hr:tbshr original tablespace: new tablespace

Remap_schema=hr:hradm 's original schema: now schema

Transform=segment_attributes:n removes the tablespace and storage clause, and when this parameter is added, the remap_tablesapce parameter becomes invalid and falls into the user's default tablespace

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.