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