Truncate Partition Table with GLOBAL INDEX and LOCAL INDEX

1. Create 4 tablespace

SQL> create tablespace part1 datafile '/d01/oracle/database/oradata/DUNGDB/file1.dbf' size 1M;
Tablespace created.

SQL> create tablespace part2 datafile '/d01/oracle/database/oradata/DUNGDB/file2.dbf' size 1M;
Tablespace created.

SQL> create tablespace part3 datafile '/d01/oracle/database/oradata/DUNGDB/file3.dbf' size 1M;
Tablespace created.

SQL> create tablespace part4 datafile '/d01/oracle/database/oradata/DUNGDB/file4.dbf' size 1M;
Tablespace created.


2. Create partition table TEST_PARTITION

SQL> CREATE TABLE test_partition
(test_id NUMBER(5),
test_name VARCHAR2(30),
test_date DATE)
PARTITION BY RANGE(test_date)
(PARTITION tb_part1 VALUES LESS THAN(TO_DATE('20/10/2010','DD/MM/YYYY')) TABLESPACE part1 ,
PARTITION tb_part2 VALUES LESS THAN(TO_DATE('21/10/2010','DD/MM/YYYY')) TABLESPACE part2 ,
PARTITION tb_part3 VALUES LESS THAN(TO_DATE('22/10/2010','DD/MM/YYYY')) TABLESPACE part3 ,
PARTITION tb_part4 VALUES LESS THAN(TO_DATE('23/10/2010','DD/MM/YYYY')) TABLESPACE part4);

Table created.


3. Create GLOBAL INDEX and PRIMARY KEY

SQL> CREATE UNIQUE INDEX test_id_ix ON test_partition(test_id) GLOBAL;
Index created.


SQL> Alter table test_partition add (
Constraint test_id_pk primary key (test_id)
Using index test_id_ix);
Table altered.


4. Insert data to table TEST_PARTITION

SQL> INSERT INTO test_partition VALUES (1,'Test case 1',TO_DATE('19/10/2010 10:10:10','DD/MM/YYYY HH24:MI:SS'));
1 row created.

SQL> INSERT INTO test_partition VALUES (2,'Test case 2',TO_DATE('19/10/2010 10:10:10','DD/MM/YYYY HH24:MI:SS'));
1 row created.

SQL> INSERT INTO test_partition VALUES (3,'Test case 3',TO_DATE('20/10/2010 10:10:10','DD/MM/YYYY HH24:MI:SS'));
1 row created.

SQL> INSERT INTO test_partition VALUES (4,'Test case 4',TO_DATE('20/10/2010 10:10:10','DD/MM/YYYY HH24:MI:SS'));
1 row created.

SQL> INSERT INTO test_partition VALUES (5,'Test case 5',TO_DATE('21/10/2010 10:10:10','DD/MM/YYYY HH24:MI:SS'));
1 row created.

SQL> INSERT INTO test_partition VALUES (6,'Test case 6',TO_DATE('21/10/2010 10:10:10','DD/MM/YYYY HH24:MI:SS'));
1 row created.

SQL> INSERT INTO test_partition VALUES (7,'Test case 7',TO_DATE('22/10/2010 10:10:10','DD/MM/YYYY HH24:MI:SS'));
1 row created.

SQL> INSERT INTO test_partition VALUES (8,'Test case 8',TO_DATE('22/10/2010 10:10:10','DD/MM/YYYY HH24:MI:SS'));
1 row created.

SQL> commit;
Commit complete.


SQL> SELECT partition_name, tablespace_name, high_value
FROM user_tab_partitions
WHERE TABLE_NAME='TEST_PARTITION';

PARTITION_NAME TABLESPACE_NAME HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
TB_PART1 PART1 TO_DATE(' 2010-10-20 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TB_PART2 PART2 TO_DATE(' 2010-10-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TB_PART3 PART3 TO_DATE(' 2010-10-22 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TB_PART4 PART4 TO_DATE(' 2010-10-23 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA



5. Truncate 1 partition of table and try to insert data to table

SQL> ALTER TABLE TEST_PARTITION TRUNCATE PARTITION tb_part1;
Table truncated.


SQL> select * from TEST_PARTITION;

TEST_ID TEST_NAME TEST_DATE
---------- ------------------------------ ---------
3 Test case 3 20-OCT-10
4 Test case 4 20-OCT-10
5 Test case 5 21-OCT-10
6 Test case 6 21-OCT-10
7 Test case 7 22-OCT-10
8 Test case 8 22-OCT-10

6 rows selected.

SQL> INSERT INTO TEST_PARTITION VALUES (1,'Test case 1',TO_DATE('19/10/2010 10:10:10','DD/MM/YYYY HH24:MI:SS'));
INSERT INTO TEST_PARTITION VALUES (1,'Test case 1',TO_DATE('19/10/2010 10:10:10','DD/MM/YYYY HH24:MI:SS'))
*
ERROR at line 1:
ORA-01502: index 'SYS.TEST_ID_IX' or partition of such index is in unusable state



SQL> select INDEX_NAME,STATUS
2 from user_indexes
3 where INDEX_NAME='TEST_ID_IX';

INDEX_NAME STATUS
------------------------------ --------
TEST_ID_IX UNUSABLE


6. Index need to rebuild

SQL> alter index TEST_ID_IX rebuild;

Index altered.


SQL> select INDEX_NAME,STATUS
2 from user_indexes
3 where INDEX_NAME='TEST_ID_IX';

INDEX_NAME STATUS
------------------------------ --------
TEST_ID_IX VALID



SQL> INSERT INTO TEST_PARTITION VALUES (1,'Test case 1',TO_DATE('19/10/2010 10:10:10','DD/MM/YYYY HH24:MI:SS'));

1 row created.

SQL> INSERT INTO TEST_PARTITION VALUES (2,'Test case 2',TO_DATE('19/10/2010 10:10:10','DD/MM/YYYY HH24:MI:SS'));

1 row created.

SQL> commit;

Commit complete.

SQL> select *
2 from TEST_PARTITION;

TEST_ID TEST_NAME TEST_DATE
---------- ------------------------------ ---------
1 Test case 1 19-OCT-10
2 Test case 2 19-OCT-10
3 Test case 3 20-OCT-10
4 Test case 4 20-OCT-10
5 Test case 5 21-OCT-10
6 Test case 6 21-OCT-10
7 Test case 7 22-OCT-10
8 Test case 8 22-OCT-10

8 rows selected.


7. Try again with LOCAL INDEX (Local index can't used to build Primary key)

SQL> Alter table TEST_PARTITION drop primary key;

Table altered.

SQL> DROP INDEX test_id_ix;

Index dropped.

SQL> CREATE INDEX test_id_ix ON test_partition(test_id) LOCAL;

Index created.

SQL> ALTER TABLE TEST_PARTITION TRUNCATE PARTITION tb_part1;

Table truncated.

SQL> select *
2 from TEST_PARTITION;

TEST_ID TEST_NAME TEST_DATE
---------- ------------------------------ ---------
3 Test case 3 20-OCT-10
4 Test case 4 20-OCT-10
5 Test case 5 21-OCT-10
6 Test case 6 21-OCT-10
7 Test case 7 22-OCT-10
8 Test case 8 22-OCT-10

6 rows selected.

SQL> select INDEX_NAME,STATUS
2 from user_indexes
3 where INDEX_NAME='TEST_ID_IX';

INDEX_NAME STATUS
------------------------------ --------
TEST_ID_IX N/A

SQL> INSERT INTO TEST_PARTITION VALUES (1,'Test case 1',TO_DATE('19/10/2010 10:10:10','DD/MM/YYYY HH24:MI:SS'));

1 row created.

SQL> INSERT INTO TEST_PARTITION VALUES (2,'Test case 2',TO_DATE('19/10/2010 10:10:10','DD/MM/YYYY HH24:MI:SS'));

1 row created.

SQL> commit;

Commit complete.

SQL> select INDEX_NAME,STATUS
2 from user_indexes
3 where INDEX_NAME='TEST_ID_IX';

INDEX_NAME STATUS
------------------------------ --------
TEST_ID_IX N/A


8. Clear test case

SQL> DROP INDEX test_id_ix;
Index dropped.

SQL> DROP TABLE TEST_PARTITION CASCADE CONSTRAINTS PURGE;
Table dropped.

SQL> DROP TABLESPACE part1 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> DROP TABLESPACE part2 INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.

SQL> DROP TABLESPACE part3 INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.

SQL> DROP TABLESPACE part4 INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.