Re: Where are nested tables stored

The "definition" of all user created objects are stored in the system tablespace.
Remove a tablespace will only remove the objects that contain data, tables or materialized views, in that tablespace. "TYPE", or functions or procedures, will remain. If you however drop the user, all objects belong to that user will be removed from the database. The data in the "storage table" that you specify during the nested table creation is stored in the same tablespace as the parent table - for non-partitioned table. For partitioned tables, they are created in the default tablespace. You can of course control where the parent table is created by using the tablespace clause in your table creation statement. Here is an example:

SQL> create tablespace tbs1 datafile 'd:\temp\tbs1.dbf' size 1m reuse;

Tablespace created.

SQL> create user usr1 identified by usr1 default tablespace users;

User created.

SQL> grant connect, resource to usr1;

Grant succeeded.

SQL> conn usr1/usr1_at_athena
Connected.

SQL> create or replace type address_o as object (season varchar2(10), address varchar2(100));
  2 /

Type created.

SQL> create or replace type address_t as table of address_o;   2 /

Type created.

SQL> create or replace type nT_type as object (name varchar2(30), address address_t);
  2 /

Type created.

• Note that "home_table" is the storage table, home is the parent table. SQL> create table home of nt_type 2 nested table address store as home_table 3 tablespace users;

Table created.

SQL>
SQL> create table t1 (c1 number(2))
  2 tablespace tbs1;

Table created.

SQL>
SQL> create or replace function test_func   2 return number
  3 as
  4 begin
  5 return 1;
  6 end;
  7 /

Function created.

SQL> insert into home values(nt_type('CCHANG', address_t(address_o('summer','Anchorage'),   2 address_o('Winter','Bahama'))));

1 row created.

SQL> insert into home values(nt_type('JYang', address_t(address_o('summer','New York'),   2 address_o('Winter','Puerto Rico'))));

1 row created.

SQL>
SQL> conn sys/athena_at_athena as sysdba Connected.
SQL> select substr(object_name,1,20), object_type from dba_objects where owner='USR1';

SUBSTR(OBJECT_NAME,1 OBJECT_TYPE

-------------------- ------------------
ADDRESS_O            TYPE
ADDRESS_T            TYPE
HOME                 TABLE
HOME_TABLE           TABLE
NT_TYPE              TYPE
SYS_C003779          INDEX
SYS_C003780          INDEX
T1                   TABLE
TEST_FUNC            FUNCTION

9 rows selected.

• Note that parent table and storage table are in the same tablespace
• Regular table T1 is in a differnet tablespace SQL> select substr(segment_name,1,20), tablespace_name from dba_segments where owner='USR1';

SUBSTR(SEGMENT_NAME, TABLESPACE_NAME

-------------------- ------------------------------
HOME_TABLE           USERS
HOME                 USERS
T1                   TBS1
SYS_C003779          USERS
SYS_C003780          USERS

SQL> drop tablespace tbs1 including contents;

Tablespace dropped.

• The "TYPE" and "function" remain after tablespace tbs1 is dropped. SQL> select substr(object_name,1,20), object_type from dba_objects where owner='USR1';

SUBSTR(OBJECT_NAME,1 OBJECT_TYPE

-------------------- ------------------
ADDRESS_O            TYPE
ADDRESS_T            TYPE
HOME                 TABLE
HOME_TABLE           TABLE
NT_TYPE              TYPE
SYS_C003779          INDEX
SYS_C003780          INDEX
TEST_FUNC            FUNCTION

8 rows selected.

• Note that when the tablespace tbs1 is dropped, only the table (T1) in that
• tablespace was removed SQL> select substr(segment_name,1,20), tablespace_name from dba_segments where owner='USR1';

SUBSTR(SEGMENT_NAME, TABLESPACE_NAME

-------------------- ------------------------------
HOME_TABLE           USERS
HOME                 USERS
SYS_C003779          USERS
SYS_C003780          USERS

SQL> drop user usr1 cascade;

User dropped.

• Everything is gone after you drop the user. SQL> select substr(object_name,1,20), object_type from dba_objects where owner='USR1';

no rows selected

SQL> select substr(segment_name,1,20), tablespace_name from dba_segments where owner='USR1';

no rows selected

SQL>

• Jusung Yang

pwhelan_at_mcw.edu (Perry Whelan) wrote in message news:<8004a67e.0211162310.16fb5699_at_posting.google.com>...
> Hi,
>
> > What were the actual VALUES of "TYPE" & "OBJECT"?
> > Please post the SQL & the results that make you conclude they still reside
> > in the DB.
>
> CREATE TYPE SYS_GID_OTHER_TY
> AS OBJECT (SYS_GID_OTHER VARCHAR2(10));
>
> CREATE TYPE SYS_GID_OTHER_TAB
> AS TABLE OF SYS_GID_OTHER_TY;
>
> After I DROP the users default tablespace I (as the user):
>
> select OBJECT_NAME from user_objects;
>
> and get:
> SYS_GID_OTHER_TAB
> SYS_GID_OTHER_TY
> among other things... So I ask where is it being stored? And more
> importantly, where is the data thats entered into it being stored?
>
> Thanks!
> Perry
Received on Sun Nov 17 2002 - 20:28:16 CET