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

Search

Welcome to Tibero GTS!

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

Document Type | Technical Information

Category | Administration

Applicable Product Versions | Tibero5, Tibero6, Tibero7

Document Number | TSETI010

 

Overview

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

 

 

Method

Verification of Recovery Using Original Query for Dropped (DDL) Tables

Execution

Execution OrderScenario
1Activate the parameter providing recovery function for dropped (DDL) tables in the environment file (TIP) (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 the parameter providing recovery function for dropped (DDL) tables in the environment file (TIP) (USE_RECYCLEBIN)

Result

Execution OrderScenario
1Activate the parameter providing recovery function for dropped (DDL) tables in the environment file (TIP) (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 the parameter providing recovery function for dropped (DDL) tables in the environment file (TIP) (USE_RECYCLEBIN)

conn SYS/TIBERO
Connected to Tibero.


ALTER SYSTEM SET USE_RECYCLEBIN=N;


System altered.

 

Range Partition Table Verification

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

 

Result

Execution OrderScenario
1Create partitioned table

tbsql SYS/TIBERO
GRANT CREATE TABLESPACE TO TEST;
Granted.
conn TEST/TEST
Connected to Tibero.
**Drop tablespace xxx including contents and datafiles;
 

*********************************  T  A  C  ********************************

CREATE TABLESPACE TEST_PART1 DATAFILE '/DEV/RAW/RAW60' SIZE 100M;
TABLESPACE 'TEST_PART1' CREATED.


CREATE TABLESPACE TEST_PART2 DATAFILE '/DEV/RAW/RAW61' SIZE 100M;
TABLESPACE 'TEST_PART2' CREATED.


CREATE TABLESPACE TEST_PART3 DATAFILE '/DEV/RAW/RAW62' SIZE 100M;
TABLESPACE 'TEST_PART3' CREATED.


CREATE TABLESPACE TEST_PART4 DATAFILE '/DEV/RAW/RAW63' 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

INSERT 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 '/DEV/RAW/RAW64' SIZE 100M;
TABLESPACE 'TEST_PART_MAX' CREATED.

 

ALTER TABLE RANGE_PART
ADD PARTITION RANGE_MAX VALUES LESS THAN (MAXVALUE, MAXVALUE, 
MAXVALUE )
TABLESPACE TEST_PART_MAX;


TABLE 'RANGE_PART' ALTERED.

4Drop partition

ALTER TABLE RANGE_PART DROP PARTITION RANGE_MAX; 

TABLE 'RANGE_PART' ALTERED

5Rename partition
ALTER TABLE RANGE_PART RENAME PARTITION RANGE_Q4 TO RANGE_FOUR;
TABLE 'RANGE_PART' ALTERED
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. 

 

** Exchange 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 partition table ** 

SELECT RANGE_NO FROM RANGE_PART PARTITION (RANGE_Q1);

0 ROW SELECTED

 

** Query data of regular table where partition data 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.

 

Hash Partition Table Verification

Execution

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

 

Result

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.

 

** Exchange 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 partition table **

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

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

1 row selected.



** Query data of regular table where partition data 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.

 

List Partition Table Verification

Execution

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

 

Result

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.
 

** Exchange 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 partition table **

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

1 row selected.
 

** Query data of regular table where partition data 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