OraFAQ Forum: SQL & PL/SQL » Partition Removing

Home » SQL & PL/SQL » SQL & PL/SQL » Partition Removing (Oracle 11g )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Partition Removing [message #656321]

Mon, 03 October 2016 03:27

saipradyumn
Messages: 419
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

Report message to a moderator

Re: Partition Removing [message #656322 is a reply to message #656321]

Mon, 03 October 2016 03:54

John Watson
Messages: 9002
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

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

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).

Report message to a moderator

Re: Partition Removing [message #656324 is a reply to message #656322]

Mon, 03 October 2016 04:05

saipradyumn
Messages: 419
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 ?

Report message to a moderator

Re: Partition Removing [message #656325 is a reply to message #656324]

Mon, 03 October 2016 04:09

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: Partition Removing [message #656326 is a reply to message #656325]

Mon, 03 October 2016 05:07

saipradyumn
Messages: 419
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.

Report message to a moderator

Re: Partition Removing [message #656329 is a reply to message #656326]

Mon, 03 October 2016 06:33

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

No you can't.

Report message to a moderator

Re: Partition Removing [message #656342 is a reply to message #656329]

Mon, 03 October 2016 11:09

Bill B
Messages: 1971
Registered: December 2004

Senior Member

SEE THE FOLLOWING LINK

http://www.dbforums.com/showthread.php?996504-how-to-add-partition-in-existing-table

Report message to a moderator

Re: Partition Removing [message #657220 is a reply to message #656342]

Wed, 02 November 2016 01:21

saipradyumn
Messages: 419
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

Report message to a moderator

Previous Topic:	Query Help
Next Topic:	ORA-06502 with UTL_RAW

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 21 05:21:14 CDT 2026