FAQ
I have a question re creation of indexes in a specific tablespace.

How can I amend the following to ensure any idexes are created in a
tablespaced called INDEXES, rather than the default tablespace?

CREATE TABLE TBLCOLUMNS(

ID INTEGER NOT NULL,

SQLTYPE INTEGER,

PSIZE INTEGER,

COLTYPE VARCHAR2(50),

NULABLE VARCHAR2(3),

NAME VARCHAR2(20),

ID_PARENT INTEGER,

PRIMARY KEY (ID), FOREIGN KEY (ID_PARENT) REFERENCES TABLTABL

E);

John

Search Discussions

  • Malcolm arnold at Nov 10, 2005 at 4:38 am

    How can I amend the following to ensure any idexes are created in a
    tablespaced called INDEXES, rather than the default tablespace?
    You have to create the table, then add the primary key in separate DDLs:

    CREATE TABLE TBLCOLUMNS(

    ID INTEGER NOT NULL,

    SQLTYPE INTEGER,

    PSIZE INTEGER,

    COLTYPE VARCHAR2(50),

    NULABLE VARCHAR2(3),

    NAME VARCHAR2(20),

    ID_PARENT INTEGER)

    alter table TBLCOLUMNS
    add PRIMARY KEY (ID)
    using index tablespace my_tablespace

    alter table TBLCOLUMNS
    add FOREIGN KEY (ID_PARENT) REFERENCES TABLTABLE(ID)

    Malcolm.
  • David Sharples at Nov 10, 2005 at 4:55 am
    no you dont
    CREATE TABLE access_level(
    access_level NUMBER(1, 0) NOT NULL,
    access_level_desc VARCHAR2(100),
    status NUMBER(1, 0),
    CONSTRAINT pk_access_level PRIMARY KEY (access_level)
    USING INDEX

    TABLESPACE xxx
    )
    TABLESPACE yyy;
    On 11/10/05, malcolm arnold wrote:

    How can I amend the following to ensure any idexes are created in a
    tablespaced called INDEXES, rather than the default tablespace?
    You have to create the table, then add the primary key in separate DDLs:
    --
    http://www.freelists.org/webpage/oracle-l
  • Mark Bole at Nov 10, 2005 at 5:07 am

    malcolm arnold wrote:

    How can I amend the following to ensure any idexes are created in a
    tablespaced called INDEXES, rather than the default tablespace?

    You have to create the table, then add the primary key in separate DDLs:

    CREATE TABLE TBLCOLUMNS(
    ID INTEGER NOT NULL,
    SQLTYPE INTEGER,
    PSIZE INTEGER,
    COLTYPE VARCHAR2(50),
    NULABLE VARCHAR2(3),
    NAME VARCHAR2(20),
    ID_PARENT INTEGER)
    alter table TBLCOLUMNS
    add PRIMARY KEY (ID)
    using index tablespace my_tablespace

    alter table TBLCOLUMNS
    add FOREIGN KEY (ID_PARENT) REFERENCES TABLTABLE(ID)
    Or, you can put it all in one statement (referencing tablespace
    INDEXES), see the example in the docs.

    http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/clauses3a.htm#1002835

    You can create the index with whatever storage parameters you want
    directly within the create table statement. Name the index (constraint)
    yourself to avoid using system-generated names.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedNov 10, '05 at 4:21a
activeNov 10, '05 at 5:07a
posts4
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase