Oracle 9 - tablespace default storage

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Jens H. Hamann
    #1

    Oracle 9 - tablespace default storage

    Hi, I am having a strange problem with the storage parameters in Oracle
    9.2.0.1 database.
    I create a tablespace with some default storage parameters but when i am
    adding a table to the tablespace i can not override the NEXT statement?
    No matter what i set the NEXT storage parameters to, it is always the same
    as the tablespace default, the INITIAL is overriden just fine.
    I have included my script below, can someone please point out what could be
    wrong.

    Thanks in advance!
    Jens Hamann

    CREATE TABLESPACE JHH_DATA DATAFILE 'C:\OraDb\JHH_D ATA_00.dbf' SIZE 50M
    AUTOEXTEND ON NEXT 50M MAXSIZE 4000M DEFAULT STORAGE (INITIAL 24K NEXT 24K
    MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0);
    CREATE TABLESPACE JHH_INDEX DATAFILE 'C:\OraDb\JHH_I DX00.dbf' SIZE 50M
    AUTOEXTEND ON NEXT 50M MAXSIZE 4000M DEFAULT STORAGE (INITIAL 24K NEXT 24K
    MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0);
    CREATE TEMPORARY TABLESPACE JHH_TEMP TEMPFILE 'C:\OraDb\JHH_T EMP00.dbf' SIZE
    50M AUTOEXTEND ON NEXT 50M MAXSIZE 4000M;
    CREATE USER JHH IDENTIFIED BY JHH;
    GRANT DBA, CONNECT TO JHH;
    ALTER USER JHH DEFAULT TABLESPACE JHH_DATA TEMPORARY TABLESPACE JHH_TEMP;
    ALTER USER JHH QUOTA UNLIMITED ON JHH_DATA QUOTA UNLIMITED ON JHH_INDEX
    QUOTA UNLIMITED ON JHH_TEMP;

    CREATE TABLE JHH.LARGE_TABLE (
    COLUMN_1 NUMBER (5) NOT NULL,
    COLUMN_2 NUMBER (5),
    COLUMN_3 VARCHAR2 (50),
    CONSTRAINT PK_LARGE_TABLE
    PRIMARY KEY ( COLUMN_1 )
    USING INDEX
    TABLESPACE JHH_INDEX PCTFREE 10
    STORAGE ( INITIAL 10M NEXT 10M PCTINCREASE 0 ))
    TABLESPACE JHH_DATA
    PCTFREE 10 PCTUSED 40
    INITRANS 1 MAXTRANS 255
    STORAGE (
    INITIAL 10M NEXT 10M PCTINCREASE 0
    MINEXTENTS 1 MAXEXTENTS 2147483645 )
    NOCACHE;

    CREATE TABLE JHH.SMALL_TABLE (
    COLUMN_1 NUMBER (5) NOT NULL,
    COLUMN_2 NUMBER (5),
    COLUMN_3 VARCHAR2 (50),
    CONSTRAINT PK_SMALL_TABLE
    PRIMARY KEY ( COLUMN_1 )
    USING INDEX
    TABLESPACE JHH_INDEX PCTFREE 10
    STORAGE ( INITIAL 64K NEXT 64K PCTINCREASE 0 ))
    TABLESPACE JHH_DATA
    PCTFREE 10 PCTUSED 40
    INITRANS 1 MAXTRANS 255
    STORAGE (
    INITIAL 64K NEXT 64K PCTINCREASE 0
    MINEXTENTS 1 MAXEXTENTS 2147483645 )
    NOCACHE;


    Tags: None
    • sybrandb@yahoo.com
      #2
      Re: Oracle 9 - tablespace default storage

      "Jens H. Hamann" <jhh@agramkow.d kwrote in message news:<40350c8d$ 0$1558$edfadb0f @dread14.news.t ele.dk>...
      Hi, I am having a strange problem with the storage parameters in Oracle
      9.2.0.1 database.
      I create a tablespace with some default storage parameters but when i am
      adding a table to the tablespace i can not override the NEXT statement?
      No matter what i set the NEXT storage parameters to, it is always the same
      as the tablespace default, the INITIAL is overriden just fine.
      I have included my script below, can someone please point out what could be
      wrong.
      >
      Thanks in advance!
      Jens Hamann
      >
      CREATE TABLESPACE JHH_DATA DATAFILE 'C:\OraDb\JHH_D ATA_00.dbf' SIZE 50M
      AUTOEXTEND ON NEXT 50M MAXSIZE 4000M DEFAULT STORAGE (INITIAL 24K NEXT 24K
      MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0);
      CREATE TABLESPACE JHH_INDEX DATAFILE 'C:\OraDb\JHH_I DX00.dbf' SIZE 50M
      AUTOEXTEND ON NEXT 50M MAXSIZE 4000M DEFAULT STORAGE (INITIAL 24K NEXT 24K
      MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0);
      CREATE TEMPORARY TABLESPACE JHH_TEMP TEMPFILE 'C:\OraDb\JHH_T EMP00.dbf' SIZE
      50M AUTOEXTEND ON NEXT 50M MAXSIZE 4000M;
      CREATE USER JHH IDENTIFIED BY JHH;
      GRANT DBA, CONNECT TO JHH;
      ALTER USER JHH DEFAULT TABLESPACE JHH_DATA TEMPORARY TABLESPACE JHH_TEMP;
      ALTER USER JHH QUOTA UNLIMITED ON JHH_DATA QUOTA UNLIMITED ON JHH_INDEX
      QUOTA UNLIMITED ON JHH_TEMP;
      >
      CREATE TABLE JHH.LARGE_TABLE (
      COLUMN_1 NUMBER (5) NOT NULL,
      COLUMN_2 NUMBER (5),
      COLUMN_3 VARCHAR2 (50),
      CONSTRAINT PK_LARGE_TABLE
      PRIMARY KEY ( COLUMN_1 )
      USING INDEX
      TABLESPACE JHH_INDEX PCTFREE 10
      STORAGE ( INITIAL 10M NEXT 10M PCTINCREASE 0 ))
      TABLESPACE JHH_DATA
      PCTFREE 10 PCTUSED 40
      INITRANS 1 MAXTRANS 255
      STORAGE (
      INITIAL 10M NEXT 10M PCTINCREASE 0
      MINEXTENTS 1 MAXEXTENTS 2147483645 )
      NOCACHE;
      >
      CREATE TABLE JHH.SMALL_TABLE (
      COLUMN_1 NUMBER (5) NOT NULL,
      COLUMN_2 NUMBER (5),
      COLUMN_3 VARCHAR2 (50),
      CONSTRAINT PK_SMALL_TABLE
      PRIMARY KEY ( COLUMN_1 )
      USING INDEX
      TABLESPACE JHH_INDEX PCTFREE 10
      STORAGE ( INITIAL 64K NEXT 64K PCTINCREASE 0 ))
      TABLESPACE JHH_DATA
      PCTFREE 10 PCTUSED 40
      INITRANS 1 MAXTRANS 255
      STORAGE (
      INITIAL 64K NEXT 64K PCTINCREASE 0
      MINEXTENTS 1 MAXEXTENTS 2147483645 )
      NOCACHE;
      In locally managed tablespaces next will always be equal to init, by
      design.
      In Oracle 9i locally managed tablespaces are the default.
      IMO, you shouldn't bother, especially if the tablespaces are created
      with autoallocate. You don't want explicit storage clauses anymore:
      they cause maintenance.

      Sybrand Bakker
      Senior Oracle DBA

        Comment

        Previous template Next
        Working...