Home » SQL & PL/SQL » SQL & PL/SQL » Changing tablespace (2 merged)
Changing tablespace (2 merged) [message #291129] Thu, 03 January 2008 01:57 Go to next message
Tanveer_ht
Messages: 7
Registered: March 2007
Junior Member
I want to move a Table from System Tablespace to another tablespace eg.tblspace_transaction.

So I want table emp which is now in system tablespace..

To move in another tablespace with name tblspace_transcation.

So i wrote

SQL>ALTER TABLE emp SET TABLESPACE tblspace_transaction;

Getting Error : ORA-02000 : missing unused keyword.



Re: Alter Table Tablespace [message #291131 is a reply to message #291129] Thu, 03 January 2008 01:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ALTER TABLE

Don't try to guess a syntax, read the documentation.

Regards
Michel
Re: Alter Table Tablespace [message #291144 is a reply to message #291129] Thu, 03 January 2008 02:44 Go to previous messageGo to next message
Tanveer_ht
Messages: 7
Registered: March 2007
Junior Member
I want to change the tablespace of Table
and the Syntax is generating error.

ALTER TABLE <nameoftable> SET TABLESPACE <nameoftablespace>;

Re: Alter Table Tablespace [message #291145 is a reply to message #291144] Thu, 03 January 2008 02:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And what is not clear in the link I posted?

Regards
Michel
Re: Alter Table Tablespace [message #291165 is a reply to message #291145] Thu, 03 January 2008 04:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Reported By: Tanveer_ht On: Thu, 03 January 2008 10:56 In: SQL & PL/SQL SQL & PL/SQL Newbies Alter Table Tablespace
Reason There is no Sql Statement for changing Tablespace for Table in It.

You didn't carefully read the link.
I read in the syntax diagram:
Quote:

Syntax
alter_table::=
alter_table_properties::=
column_clauses::=
constraint_clauses::=
alter_table_partitioning ::=
alter_external_table_clauses ::=
move_table_clause ::=
enable_disable_clause::=

Just click on the highlighted line (in the documentation page not here).

Regards
Michel

Re: Alter Table Tablespace [message #291197 is a reply to message #291165] Thu, 03 January 2008 06:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Reported By: Tanveer_ht On: Thu, 03 January 2008 13:07 In: SQL & PL/SQL SQL & PL/SQL Newbies Alter Table Tablespace
Reason I wrote Michel SQL>ALTER TABLE emp move tablespace RFES_TEMP_TBS; ERROR-ORA02195: Attempt to create PERMANENT object in a TEMPORARY tablespace.

Don't report the message, use the "Reply" button.

The error message is clear.

Regards
Michel
Index [message #291328 is a reply to message #291129] Thu, 03 January 2008 22:41 Go to previous messageGo to next message
Tanveer_ht
Messages: 7
Registered: March 2007
Junior Member
I am getting error on changing tablespace for Index.


SQL>ALTER INDEX PK_EMPNO SET TABLESPACE rfes_indx_tbs;
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option


Re: Index [message #291330 is a reply to message #291328] Thu, 03 January 2008 22:43 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
You won't get an error when you use valid syntax which can be found in the SQL Reference manual found at http://tahiti.oracle.com
Re: Index [message #291332 is a reply to message #291330] Thu, 03 January 2008 22:50 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Check the syntax...

http://www.samoratech.com/OraAdmin/swmoveindexandtable.htm
Re: Index [message #291334 is a reply to message #291332] Thu, 03 January 2008 23:01 Go to previous messageGo to next message
Tanveer_ht
Messages: 7
Registered: March 2007
Junior Member
Instead of using REBUILD i was using SET clause.
It's working now..

Thanks&Regards
Re: Index [message #291338 is a reply to message #291328] Thu, 03 January 2008 23:07 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
Please post your solution so that those who visit later can benefit from your solution.
Re: Index [message #291340 is a reply to message #291328] Thu, 03 January 2008 23:16 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

alter index PK_EMPNO rebuild tablespace rfes_indx_tbs;
Re: Index [message #291375 is a reply to message #291328] Fri, 04 January 2008 01:00 Go to previous message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yesterday you tried the same with TABLE and it does not work.
We point you to the documentation (that you seem not able to read).
Same thing for index.
Same thing for any other statement: if you try to guess what is the syntax you will fail almost each time.

Regards
Michel
Previous Topic: Query problem.
Next Topic: 15 minutes gap in date
Goto Forum:
  


Current Time: Mon Dec 05 07:06:47 CST 2016

Total time taken to generate the page: 0.09917 seconds