Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Problem moving a table to another tablespace (NOT a newbie)

Problem moving a table to another tablespace (NOT a newbie)

From: Terry Phelps <tgphelps50_at_yahoo.com>
Date: Tue, 21 Aug 2001 02:18:09 GMT
Message-ID: <Bxjg7.3093$W6.411658423@newssvr17.news.prodigy.com>


This started as a small problem, I thought, but it has become a tough nut to crack. I have a TAR open with Oracle, but they are NOT helpful. Here's a quick summary: I'm trying to move the standard Oracle table AQ$_QUEUES into the SYSTEM tablespace, where it belongs. I'm running 8.1.7 on AIX.

Background: Somehow, SYSTEM's default tablespace was set to a user tablespace, and not to the SYSTEM tablespace. Then, during an Oracle upgrade, I presume, the table AQ$_QUEUES got created. A few days ago I noticed that it wasn't in SYSTEM where it surely belongs, and I was emptying this user tablespace, prior to removing it. I looked at the Oracle script catqueue.sql, and it creates the system.aq$_queues table without specifying a tablespace. There were other tables with names like aq$_* and def$_* in the user tablespace, too, and I have moved all of these to SYSTEM with no problem. But aq$_queues has me stumped.

I first tried the simple way: I did an "alter table aq$_queues move ...", and this was successful. I thought. But then I saw that there is a "lobsegment" that contains a VARRAY column from this table, and this segment was still in the wrong tablespace. If you're familiar with this table, the column is called "subscribers". I have read the SQL docs for the "alter table" statement, but can't find a syntactically valid way to move that segment into SYSTEM. If you can tell me what the syntax is, if such syntax exists, I'm home free.

From the docs, it looks like the following should work, but it gets a syntax error:
alter table aq$_queues move
varray SUBSCRIBERS store as lob
(TABLESPACE SYSTEM); I eventually gave up, and decided I'd do an old trusty export/import. So I tried to export it. Nope, it said, the table is marked "not exportable". I've never heard of that, and now I don't know what to do.

I don't use the AQ mechanism at all, and the table aq$_queues is empty, and I'm open to any reasonable mechanism to get that table and all of its segments into the SYSTEM tablespace. Can I make the table exportable? Is there a script that recreates the whole AQ family of objects from scratch? Is there an "alter table" variation that saves me?

Dang. This should be easy. If you can help, please do. Received on Mon Aug 20 2001 - 21:18:09 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US