Overview

This document provides an example explaining how to selectively export only data that meets specific conditions using the query option in Tibero's tbexport utility.

Method

1. tbexport Query Option

Specify filter conditions for the data to be exported.

If the part to be written as a condition is a string
ex) "where c1='test'"
If the condition is not a string
ex) "where c2=1"
If an error occurs in bash
ex) "\"where c2=1\""

• Works regardless of mode, but be cautious as it may apply to unwanted tables.
• The WHERE condition must be enclosed with \". However, if the content of the condition clause needs to be treated as a string, it must be enclosed with \". (Be sure to check the string in the condition clause before execution)
• If an error occurs after using it in bash, enclose it once more with "".
• If a syntax error occurs in the SQL statement due to the specified condition, the condition will not be applied and the attempt will be retried.

(If a syntax error occurs, the export may proceed without applying the condition, so verify the data count before and after export)

2. Scenario

Using tbexport/tbimport to migrate monthly data of large log-type general tables to new partitioned tables

2-1. Create Log-type General Table

################################
General Table KKB.DUMP_TEST(Source)
################################

CREATE TABLE "KKB"."DUMP_TEST" (
"ID1" NUMBER, 
"ID2" NUMBER, 
"NAME" VARCHAR2(10), 
"DATE1" VARCHAR2(8), 
"DATE2" VARCHAR2(8), 
"DATE3" DATE, 
"PHONE" VARCHAR2(13), 
"PRICE" NUMBER, 
"QTY" NUMBER, 
"TEST1" NUMBER, 
"TEST2" VARCHAR2(5), 
"TEST3" VARCHAR2(4)) 
 PCTFREE 10 INITRANS 2 NOCOMPRESS NOLOGGING
 STORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 4294967295 BUFFER_POOL DEFAULT)
 TABLESPACE "USR";

2-2. Create Partitioned Table (RANGE)

#######################################
Partitioned Table KKB.DUMP_TEST_PART(Target)
#######################################

CREATE TABLE "KKB"."DUMP_TEST_PART" (
"ID1" NUMBER, 
"ID2" NUMBER, 
"NAME" VARCHAR2(10), 
"DATE1" VARCHAR2(8), 
"DATE2" VARCHAR2(8), 
"DATE3" DATE, 
"PHONE" VARCHAR2(13), 
"PRICE" NUMBER, 
"QTY" NUMBER, 
"TEST1" NUMBER, 
"TEST2" VARCHAR2(5), 
"TEST3" VARCHAR2(4)) 
 PCTFREE 10 INITRANS 2 NOCOMPRESS LOGGING
  TABLESPACE "USR"
PARTITION BY RANGE("DATE3")
(
PARTITION "PART_202501" VALUES LESS THAN (TO_DATE('2025-02-01', 'YYYY-MM-DD'))  PCTFREE 10 INITRANS 2 NOCOMPRESS LOGGING
 STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 4294967295 BUFFER_POOL DEFAULT)
 TABLESPACE "USR"
, PARTITION "PART_202502" VALUES LESS THAN (TO_DATE('2025-03-01', 'YYYY-MM-DD'))  PCTFREE 10 INITRANS 2 NOCOMPRESS LOGGING
 STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 4294967295 BUFFER_POOL DEFAULT)
 TABLESPACE "USR"
, PARTITION "PART_202503" VALUES LESS THAN (TO_DATE('2025-04-01', 'YYYY-MM-DD'))  PCTFREE 10 INITRANS 2 NOCOMPRESS LOGGING
 STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 4294967295 BUFFER_POOL DEFAULT)
 TABLESPACE "USR"
, PARTITION "PART_202504" VALUES LESS THAN (TO_DATE('2025-05-01', 'YYYY-MM-DD'))  PCTFREE 10 INITRANS 2 NOCOMPRESS LOGGING
 STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 4294967295 BUFFER_POOL DEFAULT)
 TABLESPACE "USR"
, PARTITION "PART_202505" VALUES LESS THAN (TO_DATE('2025-06-01', 'YYYY-MM-DD'))  PCTFREE 10 INITRANS 2 NOCOMPRESS LOGGING
 STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 4294967295 BUFFER_POOL DEFAULT)
 TABLESPACE "USR"
, PARTITION "PART_202506" VALUES LESS THAN (TO_DATE('2025-07-01', 'YYYY-MM-DD'))  PCTFREE 10 INITRANS 2 NOCOMPRESS LOGGING
 STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 4294967295 BUFFER_POOL DEFAULT)
 TABLESPACE "USR"
, PARTITION "PART_202507" VALUES LESS THAN (TO_DATE('2025-08-01', 'YYYY-MM-DD'))  PCTFREE 10 INITRANS 2 NOCOMPRESS LOGGING
 STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 4294967295 BUFFER_POOL DEFAULT)
 TABLESPACE "USR"
, PARTITION "PART_202508" VALUES LESS THAN (TO_DATE('2025-09-01', 'YYYY-MM-DD'))  PCTFREE 10 INITRANS 2 NOCOMPRESS LOGGING
 STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 4294967295 BUFFER_POOL DEFAULT)
 TABLESPACE "USR"
, PARTITION "PART_202509" VALUES LESS THAN (TO_DATE('2025-10-01', 'YYYY-MM-DD'))  PCTFREE 10 INITRANS 2 NOCOMPRESS LOGGING
 STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 4294967295 BUFFER_POOL DEFAULT)
 TABLESPACE "USR"
, PARTITION "PART_202510" VALUES LESS THAN (TO_DATE('2025-11-01', 'YYYY-MM-DD'))  PCTFREE 10 INITRANS 2 NOCOMPRESS LOGGING
 STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 4294967295 BUFFER_POOL DEFAULT)
 TABLESPACE "USR"
, PARTITION "PART_202511" VALUES LESS THAN (TO_DATE('2025-12-01', 'YYYY-MM-DD'))  PCTFREE 10 INITRANS 2 NOCOMPRESS LOGGING
 STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 4294967295 BUFFER_POOL DEFAULT)
 TABLESPACE "USR"
, PARTITION "PART_202512" VALUES LESS THAN (TO_DATE('2026-01-01', 'YYYY-MM-DD'))  PCTFREE 10 INITRANS 2 NOCOMPRESS LOGGING
 STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 4294967295 BUFFER_POOL DEFAULT)
 TABLESPACE "USR"
, PARTITION "PART_MAX" VALUES LESS THAN (MAXVALUE)  PCTFREE 10 INITRANS 2 NOCOMPRESS LOGGING
 STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 4294967295 BUFFER_POOL DEFAULT)
 TABLESPACE "USR"
);

2-3. Perform tbexport for Data from One Month Ago

# Backup with Target partition table name using REMAP_TABLE option

$ tbexport username='sys' password='tibero' port='8629' sid='tibero' file='/home/tibero/dump/dump_test1.dmp' log='/home/tibero/dump/dump_test.log' table=KKB.DUMP_TEST constraint=n index=n grant=n query="\"WHERE DATE3 BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'MM'),-1) + (0/24) AND LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE),-1)) + (23/24) + (59/60/24) + (59/60/60/24)\"" REMAP_TABLE=DUMP_TEST:DUMP_TEST_PART

2-4. Perform tbimport on Partitioned Table (Target)

# Use ignore=y option because the table already exists

$ tbimport username='sys' password='tibero' port='8629' sid='tibero' file='/home/tibero/dump/dump_test1.dmp' log='/home/tibero/dump/dump_test_imp.log' table=KKB.DUMP_TEST_PART ignore=y

2-5. Check Previous Month Data Count in Source General Table

SQL> SELECT COUNT(*) FROM DUMP_TEST
WHERE DATE3 BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'MM'),-1) + (0/24) AND LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE),-1)) + (23/24) + (59/60/24) + (59/60/60/24);

  COUNT(*)
----------
       423

1 row selected.

2-6. Check Previous Month Data Count in Target Partitioned Table

# Check data for 2025-11
SQL> select count(*) from dump_test_part partition(part_202510);

  COUNT(*)
----------
         0

1 row selected.

SQL> select count(*) from dump_test_part partition(part_202511);

  COUNT(*)
----------
       423

1 row selected.