Re: Nested Table Question

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Wed, 30 May 2007 21:33:17 +0200
Message-ID: <465DD17D.5000501@gmail.com>

amerar_at_iwc.net schrieb:
> Hi,
>
> I've never dealt with Nested Tables. I've read a bit on them, and it
> says you cannot move them. The nested table is supposed to reside in
> the same tablespace as its master table, but this is not so.

Could you show, who and where say it?

SQL> CREATE OR REPLACE TYPE nt IS TABLE OF NUMBER;

   2 /

Type created.

SQL> select table_name,tablespace_name,nested from tabs where table_name like 'N%';

no rows selected

SQL> CREATE TABLE nt_container(ID NUMBER,nt_column nt)

   2 NESTED TABLE nt_column STORE AS nested_table    3 /

Table created.

SQL> insert into nt_container values(1,nt(1,2,3,4,5));

1 row created.

SQL> insert into nt_container values(2,nt(1,2,3,4,5));

1 row created.

SQL> commit;

Commit complete.

SQL> select table_name,tablespace_name,nested from tabs where table_name like 'N%';

TABLE_NAME                     TABLESPACE_NAME                NES
------------------------------ ------------------------------ ---
NESTED_TABLE                   USERS                          YES
NT_CONTAINER                   USERS                          NO

SQL> alter table nested_table move tablespace example;

Table altered.

SQL> select table_name,tablespace_name,nested from tabs where table_name like 'N%';

TABLE_NAME                     TABLESPACE_NAME                NES
------------------------------ ------------------------------ ---
NESTED_TABLE                   EXAMPLE                        YES
NT_CONTAINER                   USERS                          NO

SQL> select * from v$version;

BANNER

---

Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production PL/SQL Release 8.1.7.4.0 - Production
CORE 8.1.7.0.0 Production
TNS for Linux: Version 8.1.7.4.0 - Production NLSRTL Version 3.4.1.0.0 - Production

Best regards

Maxim Received on Wed May 30 2007 - 14:33:17 CDT