Home » SQL & PL/SQL » SQL & PL/SQL » Partition Removing (Oracle 11g )
Partition Removing [message #656321] Mon, 03 October 2016 03:27 Go to next message
saipradyumn
Messages: 320
Registered: October 2011
Location: Hyderabad
Senior Member
Hi All,

Can we remove the only the partition from the table with out effecting the original tables data.

Thanks
Sai Pradyumn
Re: Partition Removing [message #656322 is a reply to message #656321] Mon, 03 October 2016 03:54 Go to previous messageGo to next message
John Watson
Messages: 7262
Registered: January 2010
Location: Global Village
Senior Member
Your question is not clear (not to me, anyway) can you explain in greater detail what you want to do?

[Updated on: Mon, 03 October 2016 03:54]

Report message to a moderator

Re: Partition Removing [message #656323 is a reply to message #656321] Mon, 03 October 2016 03:57 Go to previous messageGo to next message
Michel Cadot
Messages: 65387
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If there is no data in the partition you can drop it without affecting the table data.
If there are data in the partition, dropping it will also delete the data.
If you don't want this then you can merge partitions (if your partitioning type allows it).

Re: Partition Removing [message #656324 is a reply to message #656322] Mon, 03 October 2016 04:05 Go to previous messageGo to next message
saipradyumn
Messages: 320
Registered: October 2011
Location: Hyderabad
Senior Member

I have a table with Partitioing.I need to remove the partitions on that table. Table is having the some data. Either Drop partition or Truncate partition
will effct the data . But as per requirement data has to be there in the table. We can do this kind of implementation by using merge partition . In such cases
at least one partition has to be there in the table .

But I want to all remove all of my partitions from the table .

Where ever i execute the follwoibng query to has to give the results


SELECT *  FROM USER_TABLES   WHERE PARTITIONED='NO AND TABLE_NAME ='REQUIRED_TABLE_NAME';


Does the oracle supports this kind of functionality ?

Re: Partition Removing [message #656325 is a reply to message #656324] Mon, 03 October 2016 04:09 Go to previous messageGo to next message
Michel Cadot
Messages: 65387
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
But I want to all remove all of my partitions from the table .
You can't.
Note that removing all the partitions is removing the table and so lose all data.

In short you want to convert a partitioned table into a non-partitioned one, doesn't it?
Your solutions are CTAS and DBMS_REDEFINTION.

Re: Partition Removing [message #656326 is a reply to message #656325] Mon, 03 October 2016 05:07 Go to previous messageGo to next message
saipradyumn
Messages: 320
Registered: October 2011
Location: Hyderabad
Senior Member

I have a table initially HASH partiotined on one of key column.
I just want to add the LIST partitioning on one mroe column along with existing HASH partitioing .
Can we do this by alter the table structure with out DBMS_REDIFINANTION.
Re: Partition Removing [message #656329 is a reply to message #656326] Mon, 03 October 2016 06:33 Go to previous messageGo to next message
Michel Cadot
Messages: 65387
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No you can't.

Re: Partition Removing [message #656342 is a reply to message #656329] Mon, 03 October 2016 11:09 Go to previous messageGo to next message
Bill B
Messages: 1718
Registered: December 2004
Senior Member
SEE THE FOLLOWING LINK

http://www.dbforums.com/showthread.php?996504-how-to-add-partition-in-existing-table
Re: Partition Removing [message #657220 is a reply to message #656342] Wed, 02 November 2016 01:21 Go to previous message
saipradyumn
Messages: 320
Registered: October 2011
Location: Hyderabad
Senior Member

Thanks for your information Michel .

My requirement is only to remove the partition .As you explained I am unable to drop only the partition.
That's why I had dropped the table and recreated with the same structure with the help of DBMS_METADATA.GET_DDL
Previous Topic: Query Help
Next Topic: ORA-06502 with UTL_RAW
Goto Forum:
  


Current Time: Thu Feb 22 00:07:15 CST 2018

Total time taken to generate the page: 0.09129 seconds