xoxomos
asked on
Tablespace Size Question
I recently did a full expdp/impdp on a database. One of the tablespaces started at 88G, then after import the size was 48G. The datafiles on this tablespace have been doing quite a bit of 100MB autoexpands for some time. I'm wondering if somehow all those small autoexpands caused the datafiles to take up more space?
If you are asking about DIFFERENCES between size of tablespace-s in extracted and source tablespace then answer is FRAGMENTATION. Your source tablespace was defragmented and after importing data is automatically DEFRAGMENTED.
This is just a shot. Hope this help.
This is just a shot. Hope this help.
ASKER
Given the datafiles must have auto-extended 100MB at a time for hundreds or thousands of times I was thinking fragmentation myself. Yes, on the database I exported FROM, the tablespace size was 88+GB. Once I imported it onto a second server the size is down to about 48GB. I compared table by table, rowcount by rowcount and all the tables were there except for a small external table i had not created a directory for in the second server.
Rowcount was 99+ percent the same and where there were differences they were just a few rows which probably were added to the source after the export.
Total size of source database is 420G.
Rowcount was 99+ percent the same and where there were differences they were just a few rows which probably were added to the source after the export.
Total size of source database is 420G.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much all.
"One block can be full of data or not. And this can be the reason of your difference."
I believe I'm understanding what you're saying here, but if you know of a link that addresses this i would very much appreciate :-)
"One block can be full of data or not. And this can be the reason of your difference."
I believe I'm understanding what you're saying here, but if you know of a link that addresses this i would very much appreciate :-)
Here you can find detail description
http://docs.oracle.com/cd/B10501_01/server.920/a96524/c03block.htm
http://docs.oracle.com/cd/B10501_01/server.920/a96524/c03block.htm
ASKER
Mil gracias!
didn't get this , what you want to say...
what is the total size of database?
In case files got auto-expanded, they will grow to an extent specified by the clause MAXSIZE, if you have specified this max size to unlimited, then they will grow to a extent of 32 gb or 64gb at max., depending on the parameter db_block_size of your database..
if the tablespace is of 88GB, you have to check the datafiles which of them is holding that much data, as you said if autoextend is on, then probably they would have got extended, but you have to check the maxsize parameter.. also check each data file size how much had it grown in actual....