OraFAQ Forum: SQL & PL/SQL » How to add partition by list clause on existing empty table

Home » SQL & PL/SQL » SQL & PL/SQL » How to add partition by list clause on existing empty table (oracle 10g)

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

How to add partition by list clause on existing empty table [message #615112]

Fri, 30 May 2014 06:30

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

I have created a table emp1
using following command:-

> create table emp1 as select * from emp
where 1=2

> select * from emp1

Now i have empty table emp1

Now i want to add "partition by list" clause on emp1 table.
and i want to pass column which is varchar2 datatype as parameter inside parameter by list.

Please tell what's the query to do that.
Thanks

Report message to a moderator

Re: How to add partition by list clause on existing empty table [message #615113 is a reply to message #615112]

Fri, 30 May 2014 06:38

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

Senior Member

Look up the syntax, it is all in the docs. Or experiment. For example, I did not know if this would work until I tried it just now:

orclz> create table emp1 partition by hash (empno) partitions 4 as select * from emp where 1=2;

Table created.

orclz>

Report message to a moderator

Re: How to add partition by list clause on existing empty table [message #615114 is a reply to message #615112]

Fri, 30 May 2014 06:48

Solomon Yakobson
Messages: 3270
Registered: January 2010
Location: Connecticut, USA

Senior Member

jgjeetu wrote on Fri, 30 May 2014 07:30

Now i want to add "partition by list" clause on emp1 table.

You can't partition existing non-partitioned table (empty or not). But you can partition (see John's example) table while doing CTAS.

SY.

Report message to a moderator

Re: How to add partition by list clause on existing empty table [message #615116 is a reply to message #615113]

Fri, 30 May 2014 06:54

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

thanks bro your query is working , but my requirement is i want to create a new table , after that i want to partition that table.

Report message to a moderator

Re: How to add partition by list clause on existing empty table [message #615117 is a reply to message #615114]

Fri, 30 May 2014 06:54

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

really ?

Report message to a moderator

Previous Topic:	Spliting string more than 32767 characters
Next Topic:	how to write an excel in PLSQL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 16 15:46:03 CDT 2024