Orakel regulärer Betrieb von Tablespace und Datenmigration

Überprüfen Sie die Beziehung zwischen Tablespace und Benutzername.

Benutzernamen auswählen,default_tablespace von dba_users;

SQL> Benutzernamen auswählen,default_tablespace von dba_users;

BENUTZERNAME DEFAULT_TABLESPACE

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

NAME_A TB_A

NAME_B TB_B

NAME_C TB_C

…………….

 

show logical dictionary

SQL>wählen * aus dba_directories;

create a new logical dictionary

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

chown -R Orakel: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’ Größe 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>Gewähren Sie Connect,Ressource,dba to USER_B;

set logical dictionary permisson for user.

SQL>Grant lesen,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