Skip to Main Content
  • Questions

  • moving IOT tables containing nested tables into another tablespace

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, alex.

Asked: September 11, 2004 - 4:49 pm UTC

Last updated: July 23, 2020 - 12:48 pm UTC

Version: 9.0.2

Viewed 1000+ times

You Asked

Structure :

CREATE TYPE BUGET" AS OBJECT ( "ID" NUMBER, "VAL"
VARCHAR2(1000), "STATUS" NUMBER);

CREATE TYPE "BUGET_TAB" AS
TABLE OF "BUGET";



CREATE TABLE "a" ("ID" NUMBER NOT NULL, "BUGET"
"BUGET_TAB"
)

TABLESPACE "TBS1"
STORAGE ( INITIAL 100M )
NESTED TABLE BUGET STORE AS BUGET_NT1 ((PRIMARY KEY(Nested_table_id,id)) ORGANIZATION INDEX PCTTHRESHOLD 20 compress 1) RETURN AS VALUE;


table a 600.000 records.
buget_nt1 14.000.000 records.

I want to move buget_nt1 into another tablespace (TBS2).
How can i do this !?
thx.







and Tom said...

alter table buget_nt1 move tablespace tools
*
ERROR at line 1:
ORA-22931: MOVE of nested table to a different tablespace not supported


ops$tkyte@ORA9IR2> !oerr ora 22931
22931, 00000,"MOVE of nested table to a different tablespace not supported"
// *Cause: Attempt to move a nested table to a different tablespace.
// *Action: Nested tables always colocate in the same tablespace as the parent.
// A nested table can be moved to a different tablespace only by moving
// its containing table to the target tablespace.


UPDATE 23 July 2020

As noted in the comment below, it is now possible to move a nested table to another tablespace (since at least 11.2, probably earlier)

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

This is not correct (anymore?)

J. Sieben, July 23, 2020 - 8:10 am UTC

As the documentation states, moving a table with a nested table does not move the nested table as well. This is documented:

If the TABLESPACE clause is not specified, then the storage table of the nested table is created in the tablespace where the parent table is created. For multilevel nested tables, Oracle creates the child table in the same tablespace as its immediately preceding parent table.

You can issue an ALTER TABLE.. MOVE statement to move a table to a different tablespace. If you do this on a table with nested table columns, only the parent table moves; no action is taken on the storage tables of the nested table. To move a storage table for a nested table to a different tablespace, issue ALTER TABLE.. MOVE on the storage table. For example:

ALTER TABLE people_tab MOVE TABLESPACE system; -- moving table
ALTER TABLE people_column_nt MOVE TABLESPACE example; -- moving storage table

Now the people_tab table is in the system tablespace and the nested table storage is stored in the example tablespace.

And this works.
Chris Saxon
July 23, 2020 - 12:48 pm UTC

Thanks for the correction, I've updated the answer.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.