Overview

When data migration is required between environments where Tablespace and User are different,
this document summarizes 2 migration methods that can be used in Tibero.

• Method ① Import after modifying DDL

• Method ② Import using the remap_tablespace option

Method

① Import after modifying DDL script

Concept

After extracting the DDL of the existing User,
modify the Tablespace information and reapply it in the new User / new Tablespace environment, then perform the Import.

Procedure Summary

(1) Create Source User (TEST) and set up test data

create tablespace test_ts datafile 'test001.dtf' size 100m autoextend on next 100m maxsize unlimited;
create user test identified by test default tablespace test_ts;
grant connect, resource to test;

Create test data:

CREATE TABLE BULKINS2 (A NUMBER, B NUMBER, C NUMBER);
BEGIN
  FOR i IN 1..100000 LOOP
    INSERT INTO BULKINS2 VALUES (i, 10, 99);
  END LOOP;
  COMMIT;
END;
/

(2) Export Source User

alter system set AUTHENTICATION_TIMEOUT=60;
tbexport username=sys password=tibero sid=tibero port=8629 user=test file=/home/tibero/exp/test_exp.dat log=/home/tibero/exp/test_exp.log

(3) Extract DDL

tbexport username=sys password=tibero sid=tibero port=8629 user=test file=/home/tibero/exp/test_exp_ddl.dat log=/home/tibero/exp/test_exp_ddl.log IGNORE=Y ROWS=N SCRIPT=Y

(4) Modify Tablespace

cp test_exp_ddl.log ddl_change.txt
:%s/USR/TEST_TS2

(5) Create Target User (TEST2)

create tablespace test_ts2 datafile 'test2_001.dtf' size 100m autoextend on next 100m maxsize unlimited;
create user test2 identified by test2 default tablespace test_ts2;
grant connect, resource to test2;

(6) Apply DDL and Import

tbsql test2/test2 @ddl_change.txt
tbimport username=sys password=tibero sid=tibero port=8629 fromuser=test touser=test2 file=/home/tibero/exp/test_exp.dat log=/home/tibero/imp/test_imp.log ignore=y

(7) Verification

• Check that objects are in VALID state

• Compare Table Row Counts

Using the remap_tablespace option

Concept

Use the remap_tablespace option during Export to automatically map Tablespaces without modifying the DDL.

Procedure Summary

(1) Create Target User (TEST3)

create tablespace test_ts3 datafile 'test3_001.dtf' size 100m autoextend on next 100m maxsize unlimited;
create user test3 identified by test3 default tablespace test_ts3;
grant connect, resource to test3;

(2) Export using remap_tablespace option

alter system set AUTHENTICATION_TIMEOUT=60;
tbexport username=sys password=tibero sid=tibero port=8629 user=test file=/home/tibero/exp/test_remap_exp.dat log=/home/tibero/exp/test_remap_exp.log remap_tablespace=USR:TEST_TS3

(3) Perform Import

tbimport username=sys password=tibero sid=tibero port=8629 fromuser=test touser=test3 file=/home/tibero/exp/test_remap_exp.dat log=/home/tibero/imp/test_reamp_imp.log

(4) Verification

• Confirm Table Row Count is the same

• Check that objects are in VALID state

Verification Script (Common)

Check Row Count (using Cursor)

SET SERVEROUTPUT ON
EXEC DBMS_OUTPUT.ENABLE(1000000);

DECLARE
  CURSOR CUR_MYTABLES IS
    SELECT * FROM DBA_TABLES
    WHERE OWNER IN ('TEST','TEST2','TEST3')
    ORDER BY OWNER, TABLE_NAME;
  V_CNT NUMBER;
BEGIN
  FOR I IN CUR_MYTABLES LOOP
    EXECUTE IMMEDIATE
      ('SELECT COUNT(*) FROM ' ||I.OWNER||'.'||I.TABLE_NAME)
      INTO V_CNT;
    DBMS_OUTPUT.PUT_LINE(I.OWNER||'.'||I.TABLE_NAME||' '||V_CNT);
  END LOOP;
END;
/

Recommendations

• When Tablespace change is simple
  → It is recommended to use the remap_tablespace option.

• When fine adjustment of Tablespace/Storage structure is required
  → It is recommended to modify DDL and then import.