Re: Nested Table Question

amerar@xxxxxxx 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
.

Relevant Pages

  • Re: Lucid statement of the MV vs RM position?
    ... there's more than 30 years of production apps out there running ... second language) databases with SQL DBMS's without using SQL against ... so you told me the jury was out regarding modeling and implementing ... thinking behind the origins of SQL, ...
    (comp.databases.theory)
  • Re: Lucid statement of the MV vs RM position?
    ... there's more than 30 years of production apps out there running ... second language) databases with SQL DBMS's without using SQL against ... so you told me the jury was out regarding modeling and implementing ... thinking behind the origins of SQL, ...
    (comp.databases.theory)
  • sys fails fast refreshing
    ... Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production ... SQL> create materialized view log on my_objects with primary key ... Commit complete. ...
    (comp.databases.oracle.misc)
  • Re: 3vl 2vl and NULL
    ... Of course with SQL large portions of the query statement can be used in forming the update. ... language and is loved by many, ... That was frustrating given that it is the data model part of the RDBMS ... take it all the way into production. ...
    (comp.databases.theory)
  • Re: Lucid statement of the MV vs RM position?
    ... there's more than 30 years of production apps out there running ... second language) databases with SQL DBMS's without using SQL against ... so you told me the jury was out regarding modeling and implementing ... fancy footwork with the original definitions. ...
    (comp.databases.theory)