oracle regular operation of tablespace and data migration

check relationship between tablespace and username.

select username,default_tablespace from dba_users;

SQL> select username,default_tablespace from dba_users;

USERNAME DEFAULT_TABLESPACE

—————————— ——————————

NAME_A TB_A

NAME_B TB_B

NAME_C TB_C

…………….

show logical dictionary

SQL>select * from dba_directories;

create a new logical dictionary

SQL>create directory logical_name as ‘/…../logical_dic_b’;

chown -R oracle:oinstall /…../logical_dic_b

migrate data from server A to server B

In server a

export datafile

expdp USER_A/PASSWD_A@servicename dictionary=logical_dic_A dumpfile=dmp_a_date.dmp

logfile=expdp_a_date.log tablespace=a

In server B

create data tablespace

SQL>create tablespace B datafile ‘/…./oradata/datafile/b.dbf’ size 50m autoextend on;

create temp tablespace

SQL>create temporary tablespace B_TEMP tempfile ‘/…./oradata/datafile/b_temp.dbf’ size 50m

autoextend on;

create an user and set defualt tablespace

SQL>create user USER_B identified by PASSWD_B default tablespace b temporary tablespace b_temp;

set tablespace permission for user

SQL>grant connect,resource,dba to USER_B;

set logical dictionary permisson for user.

SQL>grant read,write on directory data_dump to USER_B;

import datafile

copy dmp_a_date.dmp from logical_dic_A to logical_dic_B

impdp USER_B/PASSWD_B directory=logical_dic_B dumpfile=dmp_a_date.dmp remap_tablespace=a:b

logfile=impdp_a_date.log remap_schema=USER_A:USER_B table_exists_action=replace full=y