OraFAQ Forum: SQL & PL/SQL » ALTER TABLE STATEMENT FOR CLUSTER

Home » SQL & PL/SQL » SQL & PL/SQL » ALTER TABLE STATEMENT FOR CLUSTER (Oracle 19C/Window 10)

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

ALTER TABLE STATEMENT FOR CLUSTER [message #678034]

Fri, 01 November 2019 07:28

Bilal Khan
Messages: 126
Registered: April 2010
Location: Pakistan

Senior Member

Dear All.
I create cluster and then want to assign those cluster to created table but it display error.

SQL> create cluster clusempdept (department_id number);

Cluster created.

 1  alter table employees
  2*  CLUSTER clusempdept(department_id)
SQL> /
 CLUSTER clusempdept(department_id)
 *
ERROR at line 2:
ORA-01735: invalid ALTER TABLE option

please help me in this regards, that how i can alter table and assign cluster to that table.

Report message to a moderator

Re: ALTER TABLE STATEMENT FOR CLUSTER [message #678035 is a reply to message #678034]

Fri, 01 November 2019 07:30

John Watson
Messages: 8077
Registered: January 2010
Location: Global Village

Senior Member

You can't convert a heap table into a clustered table. As you have discovered.

Report message to a moderator

Re: ALTER TABLE STATEMENT FOR CLUSTER [message #678036 is a reply to message #678035]

Fri, 01 November 2019 07:36

Bilal Khan
Messages: 126
Registered: April 2010
Location: Pakistan

Senior Member

thanks for message. please guide me that is it possible to convert existing table into clustered table?

Report message to a moderator

Re: ALTER TABLE STATEMENT FOR CLUSTER [message #678037 is a reply to message #678036]

Fri, 01 November 2019 07:50

BlackSwan
Messages: 26642
Registered: January 2009
Location: SoCal

Senior Member

Bilal Khan wrote on Fri, 01 November 2019 05:36

thanks for message. please guide me that is it possible to convert existing table into clustered table?

IMO, a one column table is pretty useless. What business problem does below solve?

create cluster clusempdept (department_id number);

Report message to a moderator

Re: ALTER TABLE STATEMENT FOR CLUSTER [message #678039 is a reply to message #678036]

Fri, 01 November 2019 08:32

Michel Cadot
Messages: 66720
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Bilal Khan wrote on Fri, 01 November 2019 13:36

thanks for message. please guide me that is it possible to convert existing table into clustered table?

As John said it is not possible.

Report message to a moderator

Re: ALTER TABLE STATEMENT FOR CLUSTER [message #678040 is a reply to message #678039]

Fri, 01 November 2019 08:39

John Watson
Messages: 8077
Registered: January 2010
Location: Global Village

Senior Member

I've been trying to think of a suitable metaphor. The best I have is cooking omelettes. You have two eggs, and beat up one of them to make one omelette, then you beat up the other to make a second omelette. It is impossible to combine them into one big omelette. To do that, you have to mix the eggs together before cooking them.

(ps - I can make a really good frittata).

Report message to a moderator

Re: ALTER TABLE STATEMENT FOR CLUSTER [message #678044 is a reply to message #678040]

Fri, 01 November 2019 22:40

Bilal Khan
Messages: 126
Registered: April 2010
Location: Pakistan

Senior Member

Thanks for every one, it mean that i have to recreate new table and convert into clustered table.

Report message to a moderator

Re: ALTER TABLE STATEMENT FOR CLUSTER [message #678045 is a reply to message #678044]

Sat, 02 November 2019 01:25

Michel Cadot
Messages: 66720
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

No it means you have to create a cluster then a table into it there is no possible conversion.

But the question is: why do you want to create a "clustered table"?
Don't you confuse with SQL Server clustered table which is a complete different thing.

Report message to a moderator

Previous Topic:	Update null value columns based upon previous non null columns
Next Topic:	AWR Report SQL with Bind Variables

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 10 00:31:30 CST 2019