Help Requested on Managing Extents in 10g

[cfFran]

In previous versions (Ora 8, 9) I used a CREATE TABLESPACE command numerous times to make our project accounts. The whole command is as follows:
CREATE TABLESPACE myTablespace_TS
DATAFILE 'c:\oracle\oradata\myTablespace_TS.dbf'
SIZE 50M
DEFAULT STORAGE (INITIAL 20K NEXT 20K MAXEXTENTS 249 PCTINCREASE 0);

Now that I downloaded Oracle Express it no longer works. At first, Oracle would not even create the tablespace. I raised the 20K numbers to 40K and it created the tablespace. When I tried to import the old project only about half of total number of tables was imported and then the error "Unable to create INITIAL extent for segment in tablespace myTablespace_TS" showed up. What is Oracle trying to tell me?

I don't really understand this whole 'managing extents' thing. Simple arithmetic tells me that 249 times 40K in nowhere near the 50M allocated for the tablespace. Why is Oracle unable to create the extent for the next table to be imported?

Any suggestions?

Thanks,
cfFran

 

[carp]

What is the INITIAL value for the table?

 

[cfFran]

I hope I understand your question. I think the initial value would be 40K as specified in the DEFAULT STORAGE attribute of the CREATE TABLESPACE statement.

 

[SantaMufasa]

But, cfFran, are all of your 249 tables less than 40K in size? Also, do any of your tables have indexes? If so, each of those objects require 40K, as well.

You can confirm how much storage your objects occupy by issuing this command (while logged into Oracle as the owner of your objects):

SELECT count(*) objects,SUM(bytes)Consumption
  from user_segments;

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[carp]

If you were just issuing a "CREATE TABLE" command with no INITIAL value, you could assume that INITIAL would default to 40k. But since you are importing the table definition, you have to (1) look at how the table is defined in the source database and (2) check to see if you used COMPRESS=Y when you did your export. Either of these can result in the IMP program trying to create a very large initial extent.
Don't forget, your 40K specification at the tablespace level is a DEFAULT, and is only used if no explicit values are provided. Your import WILL provide an explicit value, and I suspect this is where you are running into problems.