Home » SQL & PL/SQL » SQL & PL/SQL » How to change schema's tablespace
How to change schema's tablespace [message #328148] Thu, 19 June 2008 02:25 Go to next message
raj_roshan_009
Messages: 29
Registered: May 2008
Junior Member
Hi All,

I want to Know...
How to change a particular schema's dafault and temporary tablespace.


Also let me know...
how to move one table from one tablespace to another.

Thanks in advance.
Roshan.

[Updated on: Thu, 19 June 2008 02:26]

Report message to a moderator

Re: How to change schema's tablespace [message #328154 is a reply to message #328148] Thu, 19 June 2008 02:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
1/ ALTER USER
2/ ALTER TABLE MOVE

Both in SQL Reference.

Regards
Michel
Re: How to change schema's tablespace [message #328157 is a reply to message #328148] Thu, 19 June 2008 02:39 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Look up the syntax for alter user in the SQL Reference
Re: How to change schema's tablespace [message #328167 is a reply to message #328154] Thu, 19 June 2008 03:33 Go to previous messageGo to next message
raj_roshan_009
Messages: 29
Registered: May 2008
Junior Member
is it not possible to move a table from one tablespace to another when table has a colume of LONG datatype.

what should i do if table has LONG datatype.

Please help me..

Thanks
Roshan.
Re: How to change schema's tablespace [message #328172 is a reply to message #328167] Thu, 19 June 2008 04:04 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Upgrade to CLOB/BLOB.

Failing that, you're going to have to either:
1) Create the new table by hand, copy all the non-long data over, and then write a piece of pl/sql to do a row by row update of the long column,
2) Do an export of the table, and import it into a different schema, set up to use a different default tablespace

3) Upgrade to CLOB/BLOB.

3 is the best option.
Seriously.
Previous Topic: CURSOR & REF CursoR
Next Topic: Pls tell me the difference between these 2 queries...??
Goto Forum:
  


Current Time: Thu Feb 13 10:28:53 CST 2025