Oracle 9.2 - using IMP with rows=n...how come the USERS.dbf datafile grew to 7GB and there is no data in any tables?

Posted on 2007-08-09
Medium Priority
Last Modified: 2013-12-19
I recevied an export DMP file that was created with rows=n. I am importing that DMP file via IMP and my USERS datafile starts at .5 GB and after the IMP is done with ROWS=N (no data), the datafile grew to 7GB.

When you look at the stats, it shows Rows for most tables as >0, indicating there are rows in the tables, but when you do SELECT COUNT(1) there are none.  A COMPUTE STATISTICS then calcs all those Rows as 0, proving there is no data in the tables, but yet the datafile still sits at 7GB.  Why does it grow during IMP when there is no data being imported?
Question by:nshilling
LVL 14

Accepted Solution

sathyagiri earned 375 total points
ID: 19665384
It's probably because of the storage definition of the tables. When you create a table in Oracle without any data, it will automatically allocate space for the initial extent.. SO if you had 100's of tables imported  each with a initial extent of 50M, then your data file would have grown to 5GB ..
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 19665611
sathyagiri is correct and should get the points.

Just wanted to add:
When you export you have the option 'compress extents'.  If you selected 'Y' then Oracle looks at the total table size and makes the 'initial' extent that size for import.  This guarantees the table will be contiguous on import.

So, it doesn't have to be 100's of tables in the export.  It could be just 1 big table.

Add 'show=y' to the import and look at the output.  you'll see the 'create table' statements but it won't do anything.

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses
Course of the Month13 days, 20 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question