Hi I want to MOVE the lobsegment

[louisea]

Hi

I want to MOVE the lobsegment and lobindex of an Oracle spacial object (in 8.1.6.3.4 NT sp6a) to another tablesapce

eg. select table_name, column_name from user_lobs
where table_name ='AQ$_QUEUES'

TABLE_NAME = AQ$_QUEUES
Columns Name = SUBSCRIBERS

alter table AQ$_QUEUES move lob(SUBSCRIBERS) store as (tablespace system);

(I get the following error,
* ERROR at line 1: ORA-22917: use VARRAY to define the storage clause for this column or attribute)

Has anyone any ideas on how to solve this?

Thanks
Louise

 

[karluk]

The table system.aq$_queues has a column named "subscribers", which is defined as type sys.aq$_subscribers. The aq$_subscribers type, in turn, is defined as "varray(1024) of sys.aq$_agent. So, if this is the column you are trying to modify, it is indeed a varray column. It probably shows up in user_lobs because of the way it is stored.

I am not convinced that you need to do any manipulation of this column at all. I see the following restriction in the "alter table" documentation of the sql reference.

"Restriction: You cannot specify the TABLESPACE clause of LOB_parameters as part of this clause. The LOB tablespace for a varray defaults to the containing table's tablespace."

So it looks as if the tablespace of the varray can't be different than the tablespace of the entire table. Moreover, you seem to be trying to move it to the system tablespace and, from what I'm seeing in my database, it's already there. At least that's the tablespace that shows up when I query dba_segments.

 

[louisea]

Hi Karl

Your right I don't know how this happened but the table was created in the system tablespace and the "lob" segement in a data tablespace. Maybe the users default tablespace was set incorrectly when catproc.sql was run... not 100%sure.

Unfortunatley now I need to defrag my tablespace and do a user export but I can't drop and recreate this tablespace until I managed to move these queue tables.

I think from the restriction you posted the only way around this is to drop and recreate these tables. I'm a bit uncertain of the repercussions of doing this though. AQ seems to stand for advanced queuing which I don't think we are utilising BUT!... it's a test environment so fingers crossed.

Thanks for your help

Louise