Re: Oracle table creation script question

From: Doug O'Leary <dkoleary_at_mediaone.net>
Date: Fri, 15 Jun 2001 03:06:29 GMT
Message-ID: <MPG.159338ca7a433b0698979d@news.randori.com>

>
>The main reasons are:
>1. Ability to name the constraint
>2. Ability to define storage and tablespace parameters
>3. Ease of dropping and rebuilding the primary key at a later date as
>the DDL is independent of the table creation

OK; I'm confused.

SQL> create table cr_index
  2 ( num1 number(2),

  3     char1 varchar2(5),
  4     constraint cr_index_pk primary key (num1)
  5             using index tablespace indx 
  6             storage (initial 1k next 1k pctincrease 0)

  7 );

Table created.

SQL> column segment_name format a15
SQL> column tablespace_name format a10
SQL> column segment_type format a10
SQL> select segment_name, segment_type, tablespace_name

  2 from user_extents
  3 where segment_name = upper('cr_index_pk');

SEGMENT_NAME SEGMENT_TY TABLESPACE
--------------- ---------- ----------
CR_INDEX_PK INDEX INDX SQL> You can apparently name the constraint and define storage location/parameters for the resuling index. Dropping the index, as I understand it, requires dropping the primary key; however, that can be done as well -

SQL> alter table cr_index
  2 drop primary key;

Table altered.

SQL> select segment_name, segment_type, tablespace_name   2 from user_extents
  3 where segment_name = upper('cr_index_pk');

no rows selected

So, even #3 is apparently possible. Why then would you want to make extra steps?

I'm honestly not trying to be a smartass; I'd like to know. I'm a sysadmin by trade and a dba wannabe...

Thanks for any info.

Doug

-- 
-------------------
Douglas K. O'Leary
Senior System Administrator
dkoleary_at_mediaone.net

Received on Thu Jun 14 2001 - 22:06:29 CDT