Skip to main content
๐Ÿ““ Tibero Online Manual Click here ๐Ÿ““

Search

Welcome to Tibero GTS!

TTA BMT Function Verification Scenario (SINGLE Configuration) - Table Management

Document Type | Technical Information

Category | Administration

Applicable Product Versions | Tibero5, Tibero6, Tibero7

Document Number | TADTI219

 

Overview

This document describes a BMT scenario to verify the proper operation of the table management function in a SINGLE environment.

 

 

Method

Verify recovery by original query on dropped (DDL) tables

 

Execution

Execution OrderScenario
1Activate parameter in environment file (TIP) providing recovery function for dropped (DDL) tables (USE_RECYCLEBIN)
2Create test table (TIBERO.FLASHBACK_TEST)
3Insert data into test table (TIBERO.FLASHBACK_TEST)
4Query row count of test table (TIBERO.FLASHBACK_TEST)
5Drop test table (TIBERO.FLASHBACK_TEST)
6Check dropped test table (TIBERO.FLASHBACK_TEST) in Recycle bin
7Recover dropped test table (TIBERO.FLASHBACK_TEST)
8Query row count of test table (TIBERO.FLASHBACK_TEST)
9Deactivate parameter in environment file (TIP) providing recovery function for dropped (DDL) tables (USE_RECYCLEBIN)

 

Results

Execution OrderScenario
1Activate parameter in environment file (TIP) providing recovery function for dropped (DDL) tables (USE_RECYCLEBIN)

conn SYS/TIBERO
tbSQL 7
TmaxData Corporation Copyright (c) 2008-. All rights reserved.
Connected to Tibero.


ALTER SYSTEM SET USE_RECYCLEBIN=Y;


SYSTEM ALTERED.

2Create test table (TIBERO.FLASHBACK_TEST)

conn TEST/TEST
CONNECTED TO TEST.


CREATE TABLE FLASHBACK_TEST (C1 NUMBER, C2 NUMBER, C3 NUMBER);


TABLE 'TEST.FLASHBACK_TEST' CREATED.

3Insert data into test table (TIBERO.FLASHBACK_TEST)

declare 
begin 
for i in 1..10000 loop 
insert into test.flashback_test values (i,i,i); 
end loop; 
end; 
/  


PSM COMPLETED.


COMMIT;
COMMIT COMPLETED.

4Query row count of test table (TIBERO.FLASHBACK_TEST)
SELECT COUNT(*) FROM FLASHBACK_TEST;
COUNT(*)
----------
10000
1 ROW SELECTED.
5Drop test table (TIBERO.FLASHBACK_TEST)

DROP TABLE TEST.FLASHBACK_TEST;

TABLE 'TEST.FLASHBACK_TEST' DROPPED.
 

SELECT COUNT(*) FROM FLASHBACK_TEST;
 

TBR-8033: SPECIFIED SCHEMA OBJECT WAS NOT FOUND.   
AT LINE 1, COLUMN 23:
SELECT COUNT(*) FROM TEST.FLASHBACK_TEST
                                   ^

6Check dropped test table (TIBERO.FLASHBACK_TEST) in Recycle bin

COL OBJECT_NAME FOR A20
COL ORIGINAL_NAME FOR A20
COL TS_NAME FOR A20
COL SPACE FOR A30
SELECT * FROM USER_RECYCLEBIN;


OBJECT_NAME          ORIGINAL_NAME        TYPE    TS_NAME              CREATETIME          DROPTIME               DROPTSN BASE_OBJECT            SPACE                         
-------------------- -------------------- ------- -------------------- ------------------- ------- ------------ ---------- ---------------------- ------------------------------
TIBERO_TBL279100     FLASHBACK_TEST       TABLE   USR                  2017-05-19:10:33:06 2017-05-19:10:35:15    1509783                        1 ROW SELECTED.

7Recover dropped test table (TIBERO.FLASHBACK_TEST)
FLASHBACK TABLE FLASHBACK_TEST TO BEFORE DROP;
FLASHBACKED.
8Query row count of test table (TIBERO.FLASHBACK_TEST)
SELECT COUNT(*) FROM FLASHBACK_TEST;
COUNT(*)
----------
10000
1 ROW SELECTED.
9Deactivate parameter in environment file (TIP) providing recovery function for dropped (DDL) tables (USE_RECYCLEBIN)

conn SYS/TIBERO
Connected to Tibero.


ALTER SYSTEM SET USE_RECYCLEBIN=N;


System altered.

 

Check Range Partition Table

 

Execution

Execution OrderScenario                                                                                                                 
1Create partitioned table
2Insert data
3Add partition
4Drop partition
5Rename partition
6Merge partitions
7Split partition
8Exchange partition
9Change partition tablespace
10Truncate partition data

 

Results

Execution OrderScenario
1Create partitioned table

tbsql sys/tibero

tbSQL 7

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.

Connected to Tibero.

 

grant create tablespace to test;

Granted.

 

grant connect , resource to test;

Granted.

 

conn test/test
Connected to Tibero.
 

CREATE TABLESPACE TEST_PART1 DATAFILE 'TEST_PART1.DBF' SIZE 100M;

Tablespace 'TEST_PART1' created.
 

CREATE TABLESPACE TEST_PART2 DATAFILE ' TEST_PART2.DBF' SIZE 100M;

Tablespace 'TEST_PART2' created.

 

CREATE TABLESPACE TEST_PART3 DATAFILE 'TEST_PART3.DBF' SIZE 100M;

Tablespace 'TEST_PART3' created.

 

CREATE TABLESPACE TEST_PART4 DATAFILE ' TEST_PART4.DBF' SIZE 100M;

Tablespace 'TEST_PART4' created.
 

CREATE TABLE TEST.RANGE_PART
(RANGE_NO NUMBER,
RANGE_YEAR INT NOT NULL,
RANGE_MONTH INT NOT NULL,
RANGE_DAY INT NOT NULL,
RANGE_NAME VARCHAR2(30),
RANGE NUMBER)
PARTITION BY RANGE (RANGE_YEAR, RANGE_MONTH, RANGE_DAY)
(PARTITION RANGE_Q1 VALUES LESS THAN (2005, 01, 01) TABLESPACE TEST_PART1,
PARTITION RANGE_Q2 VALUES LESS THAN (2005, 07, 01) TABLESPACE TEST_PART2,
PARTITION RANGE_Q3 VALUES LESS THAN (2006, 01, 01) TABLESPACE TEST_PART3,
PARTITION RANGE_Q4 VALUES LESS THAN (2006, 07, 01) TABLESPACE TEST_PART4 ); 

Table 'TEST.RANGE_PART' created

 

2Insert data

NSERT INTO RANGE_PART VALUES(1, 2004, 06, 12, 'SCOTT', 2500); 

INSERT INTO RANGE_PART VALUES(2, 2005, 06, 17, 'JONES', 4300); 

INSERT INTO RANGE_PART VALUES(3, 2005, 12, 12, 'MILLER', 1200); 

INSERT INTO RANGE_PART VALUES(4, 2006, 06, 22, 'FORD', 5200); 

INSERT INTO RANGE_PART VALUES(5, 2005, 01, 01, 'LION', 2200); 

COMMIT; 

 

Commit completed.

 

SQL> SELECT * FROM TEST.RANGE_PART;

 RANGE_NO RANGE_YEAR RANGE_MONTH  RANGE_DAY RANGE_NAME     RANGE
---------- ---------- ----------- ---------- ------------------------------ ----------
        1       2004           6         12 SCOTT                                2500
        2       2005           6         17 JONES                                4300
        5       2005           1          1 LION                                 2200
        3       2005          12         12 MILLER                               1200
        4       2006           6         22 FORD                                 5200
 

3Add partition

CREATE TABLESPACE TEST_PART_MAX DATAFILE 'PART_MAX.DBF' SIZE 100M;

Tablespace 'TEST_PART_MAX' created.

 

CREATE TABLESPACE TEST_PART_MAX DATAFILE 'PART_MAX.DBF' SIZE 100M;

Tablespace 'TEST_PART_MAX' created.

4Drop partition

ALTER TABLE RANGE_PART DROP PARTITION RANGE_MAX; 

TABLE 'RANGE_PART' ALTERED

5Rename partition

SELECT * FROM TEST1.TBL; 

ID 

----------

1 

 

1 ROW SELECTED.

6Merge partitions

ALTER TABLE RANGE_PART MERGE PARTITIONS RANGE_Q1, RANGE_Q2 INTO PARTITION RANGE_Q2 UPDATE INDEXES; 

 

TABLE 'RANGE_PART' ALTERED

7Split partition
ALTER TABLE RANGE_PART SPLIT PARTITION RANGE_Q2 AT (2005, 01, 01) INTO (PARTITION RANGE_Q1, PARTITION RANGE_Q2); TABLE 'RANGE_PART' ALTERED.
8Exchange partition

** Create table to move partition data to a regular table ** 

CREATE TABLE RANGE_PART_EX (RANGE_NO NUMBER, RANGE_YEAR INT NOT NULL, RANGE_MONTH INT NOT NULL, RANGE_DAY INT NOT NULL, RANGE_NAME VARCHAR2(30), RANGE NUMBER) TABLESPACE TEST_PART1; 

TABLE 'RANGE_PART_EX' CREATED. 

 

** Change partition data to regular table ** 

ALTER TABLE RANGE_PART EXCHANGE PARTITION RANGE_Q1 WITH TABLE RANGE_PART_EX; 

TABLE 'RANGE_PART' ALTERED. 

 

** Query data of partitioned table ** 

SELECT RANGE_NO FROM RANGE_PART PARTITION (RANGE_Q1);

0 ROW SELECTED

 

** Query data of regular table where partition data was moved ** 

SELECT RANGE_NO FROM RANGE_PART_EX; 

RANGE_NO 

---------------- 

1 

1 ROW SELECTED.

9Change partition tablespace

ALTER TABLE RANGE_PART MOVE PARTITION RANGE_Q3 TABLESPACE TEST_PART_MAX; 

TABLE 'RANGE_PART' ALTERED.

10Truncate partition data

ALTER TABLE RANGE_PART TRUNCATE PARTITION RANGE_Q3; 

TABLE 'RANGE_PART' ALTERED. 

 

SELECT * FROM RANGE_PART PARTITION (RANGE_Q3); 

0 ROW SELECTED.

 

Check Hash Partition Table

 

Execution

Execution OrderScenario
1Create partitioned table
2Insert data
3Drop partition (not supported)
4Rename partition
5Exchange partition
6Change partition tablespace
7Truncate partition data

 

Results

Execution OrderScenario
1Create partitioned table

tbsql test/test

tbSQL 7

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.

Connected to Tibero.

 

CREATE TABLE TEST.HASH_PART
(HASH_NO NUMBER,
HASH_YEAR CHAR(4) NOT NULL,
HASH_MONTH CHAR(2) NOT NULL,
HASH_DAY CHAR(2) NOT NULL,
HASH_NAME VARCHAR2(30),
HASH NUMBER)
PARTITION BY HASH (HASH_NO)
(PARTITION HASH_PART1 TABLESPACE TEST_PART1,
PARTITION HASH_PART2 TABLESPACE TEST_PART2,
PARTITION HASH_PART3 TABLESPACE TEST_PART3,
PARTITION HASH_PART4 TABLESPACE TEST_PART4);   

Table 'TEST.HASH_PART' created.
 

2Insert data

INSERT INTO TEST.HASH_PART VALUES(1, 2004, 06, 12, 'SCOTT', 2500);
INSERT INTO TEST.HASH_PART VALUES(2, 2005, 06, 17, 'JONES', 4300);
INSERT INTO TEST.HASH_PART VALUES(3, 2005, 12, 12, 'MILLER', 1200);
INSERT INTO TEST.HASH_PART VALUES(4, 2006, 06, 22, 'FORD', 5200);
INSERT INTO TEST.HASH_PART VALUES(5, 2005, 01, 01, 'LION', 2200);
INSERT INTO TEST.HASH_PART VALUES(6, 2006, 12, 22, 'TIGER', 3300);
 

COMMIT;

Commit completed.

 

SELECT * FROM TEST.HASH_PART;

  HASH_NO HASH_YEAR HASH_MONTH HASH_DAY HASH_NAME    HASH
---------- --------- ---------- -------- ------------------------------ ----------
        2 2005      6          17       JONES                                4300
        3 2005      12         12       MILLER                               1200
        5 2005      1          1        LION                                 2200
        1 2004      6          12       SCOTT                                2500
        6 2006      12         22       TIGER                                3300
        4 2006      6          22       FORD                                 5200

6 rows selected.

 

3Drop partition (not supported)

ALTER TABLE TEST.HASH_PART DROP PARTITION HASH_PART4;


TBR-7250: Table is not partitioned by RANGE or LIST method.

4Rename partition
ALTER TABLE TEST.HASH_PART RENAME PARTITION HASH_PART4 TO
HASH_PART_FOUR;

Table 'TEST.HASH_PART' altered.
5Exchange partition

** Create table to move partition data to a regular table **

CREATE TABLE TEST.HASH_PART_EX
(HASH_NO NUMBER,
HASH_YEAR CHAR(4) NOT NULL,
HASH_MONTH CHAR(2) NOT NULL,
HASH_DAY CHAR(2) NOT NULL,
HASH_NAME VARCHAR2(30),
HASH NUMBER)
TABLESPACE TEST_PART1;

Table 'TEST.HASH_PART_EX' created.

 

** Change partition data to regular table **

ALTER TABLE TEST.HASH_PART EXCHANGE PARTITION HASH_PART2 WITH TABLE TEST.HASH_PART_EX;
 

Table 'TEST.HASH_PART' altered.

 

** Query data of partitioned table **

SELECT COUNT(*) FROM TEST.HASH_PART PARTITION(HASH_PART2); 

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

1 row selected.



** Query data of regular table where partition data was moved **

SELECT COUNT(*) FROM TEST.HASH_PART_EX;

 COUNT(*)
----------
        2

1 row selected.

6Change partition tablespace

ALTER TABLE TEST.HASH_PART MOVE PARTITION HASH_PART3 TABLESPACE TEST_PART4;
 

Table 'TEST.HASH_PART' altered.
 

7Truncate partition data

SELECT COUNT(*) FROM TEST.HASH_PART PARTITION(HASH_PART_FOUR);

 COUNT(*)
----------
        1

1 row selected.
 

ALTER TABLE TEST.HASH_PART TRUNCATE PARTITION HASH_PART_FOUR;

Table 'TEST.HASH_PART' altered.

 

SELECT COUNT(*) FROM TEST.HASH_PART PARTITION(HASH_PART_FOUR);

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

1 row selected.

 

Check List Partition Table

 

Execution

Execution OrderScenario
1Create partitioned table
2Insert data
3Add partition
4Drop partition
5Rename partition
6Exchange partition
7Change partition tablespace
8Truncate partition data

 

Results

Execution OrderScenario
1Create partitioned table

CREATE TABLE TEST.LIST_PART
(LIST_NO NUMBER NOT NULL,
LIST_NAME VARCHAR2(10),
LIST_JOB VARCHAR2(9),
LIST_MGR NUMBER(4),
LIST_HIREDATE DATE,
LIST_SAL NUMBER(7, 2),
LIST_COMM NUMBER(7, 2),
LIST_DEPTNO NUMBER(2))
PARTITION BY LIST (LIST_JOB)
(PARTITION LIST_PART1 VALUES ('MANAGER') TABLESPACE TEST_PART1,
PARTITION LIST_PART2 VALUES ('SALESMAN') TABLESPACE TEST_PART2,
PARTITION LIST_PART3 VALUES ('ANALYST') TABLESPACE TEST_PART3,
PARTITION LIST_PART4 VALUES ('PRESIDENT', 'CLERK') TABLESPACE TEST_PART4);

Table 'TEST.LIST_PART' created.
 

2Insert data

INSERT INTO LIST_PART VALUES(1, 'SMITH', 'CLERK', 7902, SYSDATE, 800, NULL,20);
INSERT INTO LIST_PART VALUES(2, 'ALLEN', 'SALESMAN', 7698, SYSDATE, 1600,300, 30);
INSERT INTO LIST_PART VALUES(3, 'WARD', 'SALESMAN', 7698, SYSDATE, 1250,500, 30);
INSERT INTO LIST_PART VALUES(4, 'JONES', 'MANAGER', 7839, SYSDATE, 2975,NULL, 20);
INSERT INTO LIST_PART VALUES(5, 'MARTIN', 'SALESMAN', 7698, SYSDATE, 1250,1400, 30);
INSERT INTO LIST_PART VALUES(6, 'BLAKE', 'MANAGER', 7839, SYSDATE, 2850,NULL, 30);INSERT INTO LIST_PART VALUES(7, 'CLARK', 'MANAGER', 7839, SYSDATE, 2450,NULL, 10);
INSERT INTO LIST_PART VALUES(8, 'SCOTT', 'ANALYST', 7566, SYSDATE, 3000,NULL, 20);
INSERT INTO LIST_PART VALUES(9, 'KING', 'PRESIDENT', NULL, SYSDATE, 5000, NULL,10);
INSERT INTO LIST_PART VALUES(10, 'TURNER', 'SALESMAN', 7698,SYSDATE, 1500,0, 30);
INSERT INTO LIST_PART VALUES(11, 'ADAMS', 'CLERK', 7788,SYSDATE,1100,NULL,20);
INSERT INTO LIST_PART VALUES(12, 'JAMES', 'CLERK', 7698, SYSDATE, 950,NULL, 30);
INSERT INTO LIS   2 T_PART VALUES(13, 'FORD', 'ANALYST', 7566, SYSDATE, 3000,NULL, 20);
INSERT INTO LIST_PART VALUES(14, 'MILLER', 'CLERK', 7782, SYSDATE, 1300,NULL, 10);
 

COMMIT;

 

SELECT LIST_NO FROM LIST_PART PARTITION (LIST_PART1);

  LIST_NO
----------
        4
        6
        7

3 rows selected.

 

SELECT LIST_NO FROM LIST_PART PARTITION (LIST_PART2);

  LIST_NO
----------
        2
        3
        5
       10

4 rows selected.

 

SELECT LIST_NO FROM LIST_PART PARTITION (LIST_PART3);

  LIST_NO
----------
        8
       13

2 rows selected.
 

SELECT LIST_NO FROM LIST_PART PARTITION (LIST_PART4);

  LIST_NO
----------
        1
        9
       11
       12
       14

5 rows selected.

3Add partition

ALTER TABLE LIST_PART ADD PARTITION LIST_PART_MAX VALUES ('DUMMY') TABLESPACE TEST_PART_MAX;

Table 'LIST_PART' altered.

4Drop partition

ALTER TABLE LIST_PART DROP PARTITION LIST_PART_MAX ;

Table 'LIST_PART' altered.

5Rename partition
ALTER TABLE TEST.LIST_PART RENAME PARTITION LIST_PART4 TO LIST_PART_FOUR;

Table 'TEST.LIST_PART' altered.
6Exchange partition

** Create table to move partition data to a regular table **

CREATE TABLE TEST.LIST_PART_EX
(LIST_NO NUMBER NOT NULL,
LIST_NAME VARCHAR2(10),
LIST_JOB VARCHAR2(9),
LIST_MGR NUMBER(4),
LIST_HIREDATE DATE,
LIST_SAL NUMBER(7, 2),
LIST_COMM NUMBER(7, 2),
LIST_DEPTNO NUMBER(2));


Table 'TEST.LIST_PART_EX' created.

 

SELECT COUNT(*) FROM TEST.LIST_PART PARTITION(LIST_PART3);

 COUNT(*)
----------
        2

1 row selected.
 

** Change partition data to regular table **

ALTER TABLE TEST.LIST_PART EXCHANGE PARTITION LIST_PART3 WITH TABLE TEST.LIST_PART_EX;
 

Table 'TEST.LIST_PART' altered.
 

** Query data of partitioned table **

SELECT COUNT(*) FROM TEST.LIST_PART PARTITION(LIST_PART3);
 
 COUNT(*)
----------
        0

1 row selected.
 

** Query data of regular table where partition data was moved **

SELECT COUNT(*) FROM TEST.LIST_PART_EX;

 COUNT(*)
----------
        2

1 row selected.

7Change partition tablespace

ALTER TABLE TEST.LIST_PART MOVE PARTITION LIST_PART1 TABLESPACE TEST_PART2;

Table 'TEST.LIST_PART' altered.

8Truncate partition data

SELECT COUNT(*) FROM TEST.LIST_PART PARTITION(LIST_PART2);
 COUNT(*)
----------
        4

1 row selected.
 

ALTER TABLE TEST.LIST_PART TRUNCATE PARTITION LIST_PART2;

Table 'TEST.LIST_PART' altered.

 

SELECT COUNT(*) FROM TEST.LIST_PART PARTITION(LIST_PART2);

 COUNT(*)
----------
        0
1 row selected.

 

Related to