Lab 8:   Physical Storage

For Questions 1 and 2 , use the data dictionary table:  USER_SEGMENTS to find the answer;  note that the segment name is the same as the object name.
  1. What tablespace is the MANAGER table in?  What tablespace is the MANAGER table primary key index in?

  2. Drop the primary key on the MANAGER table (use the CASCADE clause to drop the foreign key on PORTFOLIO).  Re-create it, placing it in tablespace TS_DAT2355D_nnn_IDX (replace nnn with your section number - for example 010)..

  3. Create an index on the manager table on manager name;  place the index in tablespace TS_DAT2355D_nnn_IDX.

    4. Note:  to confirm that the indexes are placed in the correct tablespace, look in the data dictionary tables USER_SEGMENTS or USER_INDEXES.

  4. Drop the PORTFOLIO table and the HOLDING table.  Run the script file: lab8.sql to create the PORTFOLIO table and HOLDING table and load the data.  Display the rowids for the rows in each table.  Use the following commands;  you should spool the output so you can compare it with the results produced in Question #6.

         select portfoliono, rowid from portfolio order by portfoliono;

    5.       select stocksymbol, portfoliono, rowid from holding order by portfoliono;

    Are the parent and child rows stored in the same block?

  5. Drop the PORTFOLIO and HOLDING tables.  Create a cluster for PORTFOLIO and HOLDING on portfolio number - you need to create the cluster, create the cluster index and then create the two tables specifying the cluster key.

  6. Run the INSERT statements in lab8.sql again to add the same rows to the PORTFOLIO and HOLDING tables;  display the ROWIDs again. Are the parent and child rows stored in the same block?