| ORACLE :::Is there any other way to drop the column with out uncompression table in oracle database [message #679669] |
Thu, 12 March 2020 16:21  |
 |
sivaraman.pvi@gmail.com
Messages: 4
Registered: March 2020
|
Junior Member |
|
|
We have table which is partitioned ( & sub partitioned), compressed and also have a huge volumne of data. While dropping one column(E_POOL_ID) from a existing partitioned table with compression enabled then columns is going to unused columns under dba_unused_col_tabs with column name like SYS_C****. Due to this unused columns(not visible in the actual table) our exchange partition jobs has been failed.
TMP_PART_TEST1------- Partitioned table with compression enabled
TMP_PART_TEST1_STG--- Normal table.This table always truncate and load on monthly basis.
DROP TABLE TMP_PART_TEST1;
CREATE TABLE TMP_PART_TEST1
( POOL_ID NUMBER(15) NOT NULL,
NAME VARCHAR2(25 BYTE),
E_POOL_ID NUMBER(15))
PARTITION BY RANGE (POOL_ID)
(
PARTITION POOL_ID_1 VALUES LESS THAN (2),
PARTITION POOL_ID_2 VALUES LESS THAN (3),
PARTITION POOL_ID_3 VALUES LESS THAN (6)
) ROW STORE COMPRESS ADVANCED;
SELECT * FROM TMP_PART_TEST1;
--No rows
DROP TABLE TMP_PART_TEST1_STG;
CREATE TABLE TMP_PART_TEST1_STG
( POOL_ID NUMBER(15) NOT NULL,
NAME VARCHAR2(25 BYTE)
E_POOL_ID NUMBER(15));
INSERT INTO TMP_PART_TEST1_STG VALUES(1,'TEST1',22);
INSERT INTO TMP_PART_TEST1_STG VALUES(2,'TEST2',23);
INSERT INTO TMP_PART_TEST1_STG VALUES(3,'TEST3',24);
INSERT INTO TMP_PART_TEST1_STG VALUES(4,'TEST4',25);
INSERT INTO TMP_PART_TEST1_STG VALUES(5,'TEST5',26);
COMMIT;
ALTER TABLE TMP_PART_TEST1 exchange partition POOL_ID_1 WITH TABLE TMP_PART_TEST1_STG WITHOUT VALIDATION;
--table altered
SELECT * FROM TMP_PART_TEST1;
--5 rows exchanged
Here I am dropping this column E_POOL_ID in both tables.
ALTER TABLE TMP_PART_TEST1 drop column E_POOL_ID;
ALTER TABLE TMP_PART_TEST1_STG drop column E_POOL_ID;
While dropping columns,the partitioned compression table(s) column is going to dba_unused_col_tabs data dictionary.
select * from dba_unused_col_tabs where table_name='TMP_PART_TEST1';
I found out the solutions, we can uncompress the partitioned table and drop the column and again compression the tables.
But due to the unavaialabity of space in the server we can't the uncompression the table in oracle database since its huge of amount of data.
We have more than 50 years of history records are available in that table.
Is there any other way to drop the column with out uncompression table in oracle database?.
[Updated on: Mon, 16 March 2020 08:52] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Is there any other way to drop the column with out uncompression table in oracle database?. [message #679693 is a reply to message #679681] |
Tue, 17 March 2020 07:43  |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
|
I tired your issue on a 12c database and it worked fine for me. After I drop the columns on the compressed table and refreshed the tables (I was using toad) I added some more test rows in the staging table, reran the exchange partition command and the data in the partitioned table was replaced with no issues. You are correct that you may not drop an unused column in a compressed table. Oracle just doesn't support it, but the exchange partition commands will still work with no issue
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
