Hi All,
Some of my tables are accidentally created in the SYSTEM tablespace.
Is there any way to move the records and the table to some other tablespace?
regards,
Karthik
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Karthikeyan S
INET: [EMAIL PROTECTED]
Fat
Title: RE: Moving data between tablespaces
alter table table_name move tablespace_name;
rgds
amar
http://amzone.netfirms.com
-Original Message-
From: Karthikeyan S [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, September 17, 2002 12:58 PM
To: Multiple recipients of list ORACLE-L
Karthik,
Look at the ALTER TABLE {table_name} MOVE {tablespace}; command.
It will do exactly what you want.
You can also ALTER INDEX {index_name} REBUILD {tablespace} to move indexes.
Hope this helps.
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Tuesday,
Amar / Thomas,
Thanks a lot for your help.
As you said, ALTER TABLE {table_name} MOVE {tablespace}, solved the problem.
regards,
Karthik
-Original Message-
Sent: Tuesday, September 17, 2002 5:10 PM
To: [EMAIL PROTECTED]
Cc: Karthikeyan S
Karthik,
Look at the ALTER TABLE
Thomas / Amar,
I moved the tables from SYSTEM to the DATA tablespace. But now I am getting the
following error.
ORA-01502: index 'ETAIL_TEST_NEW.AGENT_PK' or partition of such index is in unusable
state
Is it because of moving the table to a different tablespace or is it something else?
TIA
Title: RE: Moving data between tablespaces[Scanned]
Please rebuild the indexes for the move tables.
Once a table is moved indexes, if any, on it become invalid.
rgds
amar
http://amzone.netfirms.com
-Original Message-
From: Karthikeyan S [mailto:[EMAIL PROTECTED]]
Sent: Tuesday
Indexes store the rowids. Right?
When the table row moved to a different place, the internal 'rowid' for it
changed, making the rowid in indexes useless (or unusable).
Since the 'alter table move' command does not automatically rebuild the
indexes with new rowids, the DBA has to do it (at
Karthik
Lesson #2 on moving tablespaces. Afterward, you must perform
ALTER INDEX REBUILD
This corrects the problem you are experiencing.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
-Original Message-
Sent: Tuesday, September
de 2002 16:04
Para: Multiple recipients of list ORACLE-L
Asunto: RE: Moving data between tablespaces[Scanned]
Thomas / Amar,
I moved the tables from SYSTEM to the DATA tablespace. But now I am getting
the following error.
ORA-01502: index 'ETAIL_TEST_NEW.AGENT_PK' or partition of such index
rebuild the index.
-Original Message-
Sent: Tuesday, September 17, 2002 10:04 AM
To: Multiple recipients of list ORACLE-L
Thomas / Amar,
I moved the tables from SYSTEM to the DATA tablespace. But now I am getting
the following error.
ORA-01502: index 'ETAIL_TEST_NEW.AGENT_PK' or
You now need to rebuild the index
-Original Message-
Sent: 17 September 2002 15:04
To: Multiple recipients of list ORACLE-L
Thomas / Amar,
I moved the tables from SYSTEM to the DATA tablespace. But now I am getting
the following error.
ORA-01502: index 'ETAIL_TEST_NEW.AGENT_PK' or
Karthik,
It's because you moved the table.
Think about it - the index is nothing more than a list of ROWID's. The
ROWID's in the index are pointing to the old location of the table. Thus,
the index is invalid.
Run the second part of my suggestion - either move the index to a new
tablesspace,
Karthik,
You need to rebuild your indexes after moving your table as they are invalidated with
the move.
HTH,
--
Alan Davey
[EMAIL PROTECTED]
212-604-0200 x106
On 9/17/2002 10:03 AM, Karthikeyan S [EMAIL PROTECTED] wrote:
Thomas / Amar,
I moved the tables from SYSTEM to the DATA
Hi,
If you have Oracle 8i, you can do:
alter table OWNER.TABLE_NAME
move tablespace TABLESPACE_NAME;
and after you have to do:
Alter index OWNER.INDEX_NAME
rebuild tablespaces TABLESPACE_NAME;
Natalia Lorena Laracca
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL
]
w-in.comcc: (bcc: Jack van
Zanen/nlzanen1/External/MEY/NL)
Sent by: Subject: RE: Moving data between
tablespaces[Scanned]
[EMAIL PROTECTED
Hi Karthik,
When you moved the table it caused any indexes on this table to be
marked as unusable. This
is because the rows in the table now have different rowid's. You will
need to rebuild the indexes
as well. They don't have to be moved to a different tablespace but they
will have to be
16 matches
Mail list logo
