Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    143

    Unanswered: Help needed with tablepsace usage

    I have an issue where I have a tablespace called BIO_4M_DAT that is sized at 3 GB (out of which 13 MB is used - > 0.43% full), hence it is almost empty. Also, since there is no more space on the disk drives, I cannot increase the size of this tablespace from 3GB.
    My database block size is 8K
    and this tablepsace BIO_4M_DAT has
    EXTENT_MANAGEMENT set to LOCAL
    UNIFORM ALLOCATION 4KB

    I am just trying to create a table with the following statement :
    create table BIO_CHEM
    (
    col1 integer not null,
    col2 number(28) not null,
    col3 varchar(12) not null,
    col4 integer not null,
    col5 number,
    col6 varchar2(64),
    col7 varchar2(64),
    col8 char(6),
    col9 varchar2(32),
    col10 char(48),
    col11 integer,
    col12 char(4),
    col13 varchar2(256),
    col14 varchar2(32),
    col15 varchar2(64),
    col16 varchar2(1024)
    ) partition by hash(col2) partitions 1024
    tablespace BIO_4M_DAT pctfree 0
    storage (initial 4M next 4M pctincrease 0)


    But create fails saying

    ORA-01658: unable to create INITIAL extent for segment in tablespace BIO_4M_DAT

    What could be taking up so much space that is draining the 3 GB ? Is there a work-around I can use to get this create table run successfully, perhaps by changing any create table parameters ?

    Any help, suggestions on this is appreciated.
    Thanks

  2. #2
    Join Date
    Sep 2004
    Posts
    17
    I don't know partitioning exactly, but your statement (and oracle doc about this) tells me, that you will create 1024 partitions each with initial extent of 4M. You will need 4GB in the tablespace. So degrease the amount or the size of your partitions. Even if you take a initial size of 3M and the tablespace is fully used by this table a partition can't contain more data than 3M

    Alex

  3. #3
    Join Date
    Feb 2004
    Posts
    143
    Thanks much for your response. I tried creating the table even with an initial extent size of 1M and next extent size of 1M but still it doesn't see to fit in 3GB. Is there anything else I can try out ?

  4. #4
    Join Date
    Aug 2003
    Posts
    62
    Is you UNIFORM SIZE at 4K or 4M ?

    If you don't specify the storage clause for you partition, the tablespace uniform size clause is take.
    Each table partition will be 4K inital size and 4K next.

    If the Uniform size is 4M, you need 4G even if you create it whith smaller initial size.

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Your highwater mark for the tablespace is probably in the upper blocks ...
    Find the objects in the tablespace in the upper blocks and move them ..
    indexes ... rebuild ... tables - alter table move / export, import ...
    The attached script willl map the tablespace blocks ... You can
    coalesce the tablespace to make the free space contiguous ... The
    free space at the end of the tablespace can be removed with

    alter tablespace xxx datafile 'c:\....' resize xx;

    You might just try the alter tablespace xxxx coalesce first, but if you need
    to scale down the tablespace, look at the free space and block mapping.

    HTH
    Gregg
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •