Function Based Indexes and Global Temporary Tables

A nonunique index can be used to enforce a primary key or unique constraint. In Oracle8i indexes can be rebuilt without locking the table. The DROP COLUMN option of the ALTER TABLE command is restartable. The MOVE option of the ALTER TABLE command retains the constraints of the table. Data rows in the global temporary table are always deleted when A user session is terminated 1.    As user Scott, create a table with three columns.  Create an index on all three columns in the order they appear in the table.  Then add a primary key constraint using the first two columns with the second column of the table appearing first.  Verify that only one index, the index being used to enforce the constraint, has been defined for the table. Hint: if the columns in the table were labeled A, B, and C, the index would be on (A, B, C) while the constraint would be on columns (B, A). Solution:

  connect scott/tiger     CREATE TABLE acct ( acct_no       NUMBER(10), customer_id           NUMBER(10), acct_comment VARCHAR2(200), CONSTRAINT pk_cid_aid  PRIMARY KEY(customer_id, acct_no) DISABLE ) / CREATE INDEX I_ANO_CNO_ACOMM ON acct(acct_no, customer_id, acct_comment) ONLINE / ALTER TABLE acct ENABLE CONSTRAINT pk_cid_aid /   select index_name, table_name from user_indexes where table_name = 'ACCT' /

2. As user Scott create a table containing three columns.  Remove the third column using one of the new methods introduced in Oracle8i.  Verify that the column is no longer part of the table. Solution:

connect scott/tiger     CREATE TABLE acct_col ( acct_col_no         NUMBER(10), customer_id           NUMBER(10), acct_col_comment    VARCHAR2(200) ) / ALTER TABLE acct_col SET UNUSED COLUMN acct_col_comment /   desc acct_col   SELECT * FROM user_unused_col_tabs / ALTER TABLE acct_col DROP UNUSED COLUMNS / SELECT * FROM user_unused_col_tabs /  

3. As user SYS create a global temporary table containing three columns.  The inserted rows should remain available until explicitly deleted or the session ends.  Make the table available to anyone who wishes to use it.  The users should not have to know the table owner in order to make use of it. Solution:

connect / as sysdba   CREATE GLOBAL TEMPORARY TABLE emp_temp_X (eno NUMBER, ename VARCHAR2(20), sal NUMBER) ON COMMIT PRESERVE ROWS;   connect / AS SYSDBA   CREATE PUBLIC SYNONYM emp_temp for emp_temp_x / GRANT ALL ON emp_temp TO PUBLIC / col object_name format a20 SELECT owner, object_name, object_type FROM dba_objects WHERE object_name LIKE '%EMP_TEMP%' / connect scott/tiger   desc emp_temp   select * from emp_temp /