DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: initial size of the table

  1. #1
    Join Date
    Nov 2000
    Posts
    33
    in a tablespace i have a data file of 600mb where only 11 mb is used. and in same tablespace i have another data file of 200 mb which is 120 mb used

    when i create a table with a storage clause of initial 400 mb it gives message that unable to create initial size though in my first data file 589 mb is unused available.

    why its not creating initial size of 400mb in first data file ?

    can i specify data file of a tablespace while creating a table ?

    can i re create the first data file to remove the frgmentation of the data file ?


    Thanks

  2. #2
    Join Date
    Feb 2001
    Location
    Kolkata- India
    Posts
    356

    Smile

    U can take a export (compress = Y)of the schemas in the particular tablespace and recreate it. Import the data backup intot the schema and try to create the table.

  3. #3
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    You cannot specify datafile for table creation as datafile is at physical level and table, tablespaces are at logical level. Seems you have the same kinda scenario what I have mentioned in the following thread.

    The only option you have is reorganizing your tablespace. either using exp/imp or OEM REORG utility.

    [url]http://www.dbasupport.com/forums/showthread.php?threadid=5723[/url]
    Reddy,Sam

  4. #4
    Join Date
    Feb 2001
    Posts
    41
    do a alter tablespace storage and change autoextend on and after creating the table way you want alter the tbsp again to off the autoextent.
    Best Regards,
    Harsh Shah

  5. #5
    Join Date
    Apr 2000
    Location
    Upper Marlboro, MD, USA
    Posts
    10

    Cool

    Try alter tablespace tablespace_name coalesce; retry your statement. If it still does work you'll need to perform a reorg (export/import).

    [QUOTE][i]Originally posted by anujpathak [/i]
    [B]in a tablespace i have a data file of 600mb where only 11 mb is used. and in same tablespace i have another data file of 200 mb which is 120 mb used

    when i create a table with a storage clause of initial 400 mb it gives message that unable to create initial size though in my first data file 589 mb is unused available.

    why its not creating initial size of 400mb in first data file ?

    can i specify data file of a tablespace while creating a table ?

    can i re create the first data file to remove the frgmentation of the data file ?


    Thanks
    [/B][/QUOTE]

  6. #6
    Join Date
    Feb 2001
    Posts
    17
    The first datafile may be fragmented, for which it is not able to create the initial extent.You can find out from the dba_free_space view, the no. of continous free space available.Since there is no continous free space of 400 MB it is giving this problem.

    If you have another datafile in which the continous space is available, you can specify after the table creation the datafile the table has to use, using the :

    alter table <tablename> allocate extent 'datafile' size;


    Ramki

Posting Permissions

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


Click Here to Expand Forum to Full Width