OraFAQ Forum: SQL & PL/SQL » Create table with two tablespaces

Home » SQL & PL/SQL » SQL & PL/SQL » Create table with two tablespaces (oracle 11g)

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

Create table with two tablespaces [message #643742]

Fri, 16 October 2015 10:16

raopatwariyahoocom
Messages: 39
Registered: October 2011
Location: GA

Member

Hello Experts,I am trying to create table with two table spaces not able TO figure out, can you please help. Thanks in advance.
CREATE TABLE RD_test
(
FILEID NUMBER NOT NULL
, TIME_KEY NUMBER(8, 0) NOT NULL
,cycle_code NUMBER,
month_year DATE
)
TABLESPACE (SU_BILLING01
SU_BILLING01);

Report message to a moderator

Re: Create table with two tablespaces [message #643743 is a reply to message #643742]

Fri, 16 October 2015 10:17

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

raopatwariyahoocom wrote on Fri, 16 October 2015 08:16

Hello Experts,I am trying to create table with two table spaces not able TO figure out, can you please help. Thanks in advance.
CREATE TABLE RD_test
(
FILEID NUMBER NOT NULL
, TIME_KEY NUMBER(8, 0) NOT NULL
,cycle_code NUMBER,
month_year DATE
)
TABLESPACE (SU_BILLING01
SU_BILLING01);

can not be done

Report message to a moderator

Re: Create table with two tablespaces [message #643744 is a reply to message #643743]

Fri, 16 October 2015 10:18

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

Senior Member

unless it is a partitioned table Smile

Report message to a moderator

Re: Create table with two tablespaces [message #643746 is a reply to message #643744]

Fri, 16 October 2015 10:44

raopatwariyahoocom
Messages: 39
Registered: October 2011
Location: GA

Member

its partitioned

Report message to a moderator

Re: Create table with two tablespaces [message #643747 is a reply to message #643746]

Fri, 16 October 2015 10:45

raopatwariyahoocom
Messages: 39
Registered: October 2011
Location: GA

Member

i did not put the whole this it has 31 partitions and sub partitioned

Report message to a moderator

Re: Create table with two tablespaces [message #643749 is a reply to message #643747]

Fri, 16 October 2015 10:52

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

Then you can use the correct statement to assign the partitions to the desired tablespaces.

https://docs.oracle.com/cd/E11882_01/server.112/e25523/part_admin001.htm#i1006455

Report message to a moderator

Re: Create table with two tablespaces [message #643750 is a reply to message #643747]

Fri, 16 October 2015 10:54

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

Senior Member
Account Moderator

So have a look at CREATE TABLE syntax, each partition can have its own tablespace.

Report message to a moderator

Re: Create table with two tablespaces [message #643751 is a reply to message #643742]

Fri, 16 October 2015 11:20

raopatwariyahoocom
Messages: 39
Registered: October 2011
Location: GA

Member

My idea is, if you mention multiple table spaces like TABLESPACE (SU_BILLING01 SU_BILLING01) then altername patition will pick the tablespace. The reason which I am ging this route, ther a heavy maintance of partition dropping and adding. It should be in a position to allot table space alternatively

Report message to a moderator

Re: Create table with two tablespaces [message #643752 is a reply to message #643751]

Fri, 16 October 2015 11:22

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

Senior Member

Look up the use of subpartition templates.

Report message to a moderator

Previous Topic:	To replace value where the date falls
Next Topic:	in, out parameters

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jul 13 20:11:12 CDT 2026