-
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
-
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.
-
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
-
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
-
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]
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|