Home » SQL & PL/SQL » SQL & PL/SQL » ORACLE :::Is there any other way to drop the column with out uncompression table in oracle database (oracle 12c)
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 Go to next message
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

Is there any other way to drop the column with out uncompression table in oracle database?. [message #679670 is a reply to message #679669] Thu, 12 March 2020 16:27 Go to previous messageGo to next message
sivaraman.pvi@gmail.com
Messages: 4
Registered: March 2020
Junior Member
Can you please help us on this issue

[Updated on: Fri, 13 March 2020 10:52]

Report message to a moderator

Re: Is there any other way to drop the column with out uncompression table in oracle database?. [message #679680 is a reply to message #679670] Mon, 16 March 2020 08:57 Go to previous messageGo to next message
Bill B
Messages: 1955
Registered: December 2004
Senior Member
Of course their going to fail. You are partitioning on POOL_ID and you dropped the POOL_ID column. Of course it will fail. It's typically not a good idea to drop the primary key of a table
Re: Is there any other way to drop the column with out uncompression table in oracle database?. [message #679681 is a reply to message #679680] Mon, 16 March 2020 09:12 Go to previous messageGo to next message
sivaraman.pvi@gmail.com
Messages: 4
Registered: March 2020
Junior Member
Thanks. I am not dropping POOL_ID and I am dropping only the E_POOL_ID.

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 column E_POOL_ID,the partitioned compression table(s) column automatically going to dba_unused_col_tabs data dictionary.
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 Go to previous messageGo to next message
Bill B
Messages: 1955
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
Re: Is there any other way to drop the column with out uncompression table in oracle database?. [message #679697 is a reply to message #679693] Tue, 17 March 2020 09:03 Go to previous messageGo to next message
sivaraman.pvi@gmail.com
Messages: 4
Registered: March 2020
Junior Member
Can you please explain more about this "refreshed the tables (I was using toad)"
Re: Is there any other way to drop the column with out uncompression table in oracle database?. [message #679701 is a reply to message #679697] Tue, 17 March 2020 12:41 Go to previous message
Bill B
Messages: 1955
Registered: December 2004
Senior Member
When your using a tool such as sql developer or toad it keeps the last information on table structure so it doesn't have to keep hitting the database to check on structure. by refreshing I simply click on a button in the toad application to requery the table structure so it will be correct. You can do the same thing by disconnecting and reconnecting to the database. In toad it is above the query window, it's the middle button of 3 arrows

[Updated on: Tue, 17 March 2020 12:42]

Report message to a moderator

Previous Topic: different rows in set of data (using lead or alternative)
Next Topic: XS entity with this name already exists.
Goto Forum:
  


Current Time: Sun May 31 16:01:04 CDT 2020