Export and import creates a larger table space than expected

[SpiritOfLennon]

Hi,
I have a live tablespace that once exported from the database and imported into a test database is about 50% larger than the original tablespace. Can anyone help me to understand what could be causing this phenomenon please?
Regards

SOL
I'm only guessing but my guess work generally works for me.

 

[Dagon]

What size did you make the test tablespace ? Was it originally the same size as production and then auto-extended after you imported the tables, or was it actually set up larger to start with ? Did you pre-create tables and give them different storage parameters ? Look at sys.dba_tables and check the storage parameters are the same between development and production. Also, are the db block sizes the same between the two tablespaces ?

 

[SpiritOfLennon]

Hi,
Thanks for the reply.

For one of the tables I have the following differences in dba_tables

Initial Extent
17547264 59064320 Next Extent
29458432
44187648

Blocks
6404 2695
Empty Blocks
805
8110

Avg Space
5138
1292

Avg Space Freelist Blocks
8085
7137

Num freelist Blocks
3628
2

Sample Size
1111
3616

So there is something weird there.
I was under the impression that the db_block_size was set across the whole instance? So it should be the same in this case.
The table space has been there for a while now. I simply drop all the tables and then do a clean import every time the test database needs updating. Could that be causing the problem? If I drop and recreate the tablespace from scratch will i get a different result?
Regards


SOL
I'm only guessing but my guess work generally works for me.

 

[Beilstwh]

Dropping the tables will NOT shrink the data files. If they had grown on test, then they will stay that size. One way to handle this is to alter the size of the datafile down before you reimport.

Bill
Oracle DBA/Developer
New York State, USA