Home » RDBMS Server » Server Administration » While moving objects from one tablespace to another tablespace ....!!
While moving objects from one tablespace to another tablespace ....!! [message #58364] Tue, 19 August 2003 09:46 Go to next message
raman
Messages: 66
Registered: February 2000
Member
Hi

I have been trying to move the objects from one tablespace to another, as it got fragmented heavily.

Here, I am moving all the indexes to another tablespace, by calling INDX1.

But, I am unable to move some of them as it contains LOB objects etc.,

What'd be the solution?

-raman
Re: While moving objects from one tablespace to another tablespace ....!! [message #58365 is a reply to message #58364] Tue, 19 August 2003 09:58 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
lob indexes and lob columns  should be in the same tablespace.
so you have to move the lob column (lob data) NOT the index

Re: While moving objects from one tablespace to another tablespace ....!! [message #58366 is a reply to message #58365] Tue, 19 August 2003 10:21 Go to previous messageGo to next message
raman
Messages: 66
Registered: February 2000
Member
Hello Mahesh

Sorry, I have not undestood your answer...!

Here is what I am doing:

spool on
select 'alter index '||owner||'.'||index_name||' rebuild tablespace INDX1 ;' from dba_indexes where tablespace_name='INDX' order by owner, index_name;
spool off

Then I am trying execute the spooled file. Some of the indexes are giving problem like :

ORA-02327: cannot create index on expression with datatype LOB

ORA-02327: cannot create index on expression with datatype ADT

Any idea?

-raman
Re: While moving objects from one tablespace to another tablespace ....!! [message #58367 is a reply to message #58366] Tue, 19 August 2003 11:02 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
a lob data ( table) and lob index should be in the same tablespace.
YOU CANNOT MOVE JUST A LOB-INDEX or REBUILD THE LOB INDEX.

----------------------------------------------------------------------

dbadmin@mutation_mutation > create table test (c1 blob);

Table created.

dbadmin@mutation_mutation > select table_name,tablespace_name from user_tables where table_name='TEST';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TEST                           USERS

dbadmin@mutation_mutation > select index_name,tablespace_name from user_indexes where table_name='TEST';

INDEX_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
SYS_IL0000007051C00001$$       USERS

-- this will fail

dbadmin@mutation_mutation > alter index SYS_IL0000007051C00001$$ rebuild tablespace tools;
alter index SYS_IL0000007051C00001$$ rebuild tablespace tools
*
ERROR at line 1:
ORA-02327: cannot create index on expression with datatype LOB

-- Will not work even for the same tablesapce 
dbadmin@mutation_mutation >  alter index SYS_IL0000007051C00001$$ rebuild tablespace USERS;
 alter index SYS_IL0000007051C00001$$ rebuild tablespace USERS
*
ERROR at line 1:
ORA-02327: cannot create index on expression with datatype LOB

Re: While moving objects from one tablespace to another tablespace ....!! [message #58369 is a reply to message #58367] Tue, 19 August 2003 12:57 Go to previous messageGo to next message
raman
Messages: 66
Registered: February 2000
Member
Thanks Mahesh....
Re: While moving objects from one tablespace to another tablespace ....!! [message #58374 is a reply to message #58365] Wed, 20 August 2003 09:45 Go to previous messageGo to next message
raman
Messages: 66
Registered: February 2000
Member
Hello Mahesh

It's me again...

How can I move a LOB column into other tablespace?

-raman
Re: While moving objects from one tablespace to another tablespace ....!! [message #58375 is a reply to message #58374] Wed, 20 August 2003 10:05 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
-- i am moving the table with lob data from USERS to TOOLS tablespace
dbadmin@mutation_mutation > create table test (c1 blob);

Table created.

dbadmin@mutation_mutation > select tablespace_name,table_name from user_tables
  2  where table_name='TEST';

TABLESPACE_NAME                TABLE_NAME
------------------------------ ------------------------------
USERS                          TEST

dbadmin@mutation_mutation > select tablespace_name,index_name from user_indexes
  2  where table_name='TEST';

TABLESPACE_NAME                INDEX_NAME
------------------------------ ------------------------------
USERS                          SYS_IL0000007055C00001$$

"afiedt.buf" 3 lines, 87 characters

  1  alter table test move tablespace tools
  2* lob(c1) store as logsegment (tablespace tools)
dbadmin@mutation_mutation > /

Table altered.

"afiedt.buf" 2 lines, 80 characters

  1   select tablespace_name,table_name from user_tables
  2*     where table_name='TEST'
dbadmin@mutation_mutation > /

TABLESPACE_NAME                TABLE_NAME
------------------------------ ------------------------------
TOOLS                          TEST

dbadmin@mutation_mutation > select tablespace_name,index_name from user_indexes
  2   where table_name='TEST';

TABLESPACE_NAME                INDEX_NAME
------------------------------ ------------------------------
TOOLS                          SYS_IL0000007055C00001$$

Re: While moving objects from one tablespace to another tablespace ....!! [message #58376 is a reply to message #58375] Wed, 20 August 2003 10:38 Go to previous messageGo to next message
raman
Messages: 66
Registered: February 2000
Member
If that is the case....

I am thinking of creating a seperate tablespace for LOBS/DOMAIN type....rather than keeping them in INDX tablespace...

What do you suggest?

-raman
Re: While moving objects from one tablespace to another tablespace ....!! [message #58377 is a reply to message #58376] Wed, 20 August 2003 12:10 Go to previous message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
it is upto u~.
lobs are just pointers!

Previous Topic: Archive ReDoLog
Next Topic: import in lower version
Goto Forum:
  


Current Time: Fri Sep 20 09:42:39 CDT 2024