Home » RDBMS Server » Server Administration » HOW TO MOVE TABLE FROM ONE TABLE SPACE TO OTHER TABLE SPACE
HOW TO MOVE TABLE FROM ONE TABLE SPACE TO OTHER TABLE SPACE [message #215807] Tue, 23 January 2007 23:59 Go to next message
sponguru_dba
Messages: 51
Registered: May 2005
Location: Bangalore
Member

hi

how to move table from one tablespace to other tablespace




thanks in Advance

SreenivasaRao
Re: HOW TO MOVE TABLE FROM ONE TABLE SPACE TO OTHER TABLE SPACE [message #215808 is a reply to message #215807] Wed, 24 January 2007 00:00 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
CTAS - CREATE TABLE ..... AS SELECT...
Re: HOW TO MOVE TABLE FROM ONE TABLE SPACE TO OTHER TABLE SPACE [message #215809 is a reply to message #215808] Wed, 24 January 2007 00:01 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
SQL> select tablespace_name
  2  from dba_segments
  3  where segment_name = 'DEPT'
  4  and owner = 'SCOTT';

TABLESPACE_NAME
------------------------------
USERS

SQL> select index_name,table_name,status
  2  from dba_indexes
  3  where owner = 'SCOTT'
  4  and table_name = 'DEPT';

INDEX_NAME                     TABLE_NAME                     STATUS
------------------------------ ------------------------------ --------
PK_DEPT                        DEPT                           VALID

SQL> alter table SCOTT.DEPT move tablespace EXAMPLE;

Table altered.

SQL> select index_name,table_name,status
  2  from dba_indexes
  3  where owner = 'SCOTT'
  4  and table_name = 'DEPT';

INDEX_NAME                     TABLE_NAME                     STATUS
------------------------------ ------------------------------ --------
PK_DEPT                        DEPT                           UNUSABLE

SQL> alter index SCOTT.PK_DEPT rebuild;

Index altered.

SQL> select index_name,table_name,status
  2  from dba_indexes
  3  where owner = 'SCOTT'
  4  and table_name = 'DEPT';

INDEX_NAME                     TABLE_NAME                     STATUS
------------------------------ ------------------------------ --------
PK_DEPT                        DEPT                           VALID

SQL> select tablespace_name
  2  from dba_segments
  3  where segment_name = 'DEPT'
  4  and owner = 'SCOTT';

TABLESPACE_NAME
------------------------------
EXAMPLE

SQL>


regards
Taj

[Updated on: Wed, 24 January 2007 00:06]

Report message to a moderator

Re: HOW TO MOVE TABLE FROM ONE TABLE SPACE TO OTHER TABLE SPACE [message #215974 is a reply to message #215809] Wed, 24 January 2007 08:44 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Be aware that any constraints, views and source code that references the table will be rendered invalid and will also have to be rebuilt.
Re: HOW TO MOVE TABLE FROM ONE TABLE SPACE TO OTHER TABLE SPACE [message #216018 is a reply to message #215807] Wed, 24 January 2007 12:02 Go to previous messageGo to next message
satheeshb
Messages: 5
Registered: July 2006
Junior Member
Hi,
You can use online redefinition using dbms_redefinition if your oracle version support it.

Regards,
Satheesh Babu.S
Bangalore.
Re: HOW TO MOVE TABLE FROM ONE TABLE SPACE TO OTHER TABLE SPACE [message #216232 is a reply to message #215807] Thu, 25 January 2007 13:05 Go to previous message
Sanjay Bajracharya
Messages: 279
Registered: October 2001
Location: Florida
Senior Member
Depends on which version of Oracle u r using ...

I think since 9i, you can move the tablespace ...

SQL>alter table xxx move tablespace yyyy

Maybe you wanna rebuild the indexes after that.

Good luck.
--
Sanjay
Previous Topic: ORA-00604 and ORA-12705 Error on Oracle SQL Developer
Next Topic: opatch error code = 130 ???
Goto Forum:
  


Current Time: Fri Sep 20 02:34:22 CDT 2024