| Importing from 9i to 11g with compressed extents creating extents on target [message #481348] |
Tue, 02 November 2010 12:54  |
chris32680
Messages: 92
Registered: January 2006
Location: Charlotte
|
Member |
|
|
Good afternoon,
We are migrating from a 9i db to 11g and we've been testing our apps on a similar (but not exact) machine as our production box.
Normally when we take a full export of the production data (on 9i) and import it into another 9i DB, the tables and indexes are created with the initial size large enough to hold the entire table. We also do our export with the compress extents param set to 'Y'.
However, we've noticed that when we import our data into the 11g DB, that tables are being created with multiple extents...sometimes up to 10 or 15. This seems to happen even with tables that don't even have extents on db that the export was taken from.
There ARE some differences in our 11g DB that i imagine might be the culprit, i've just been unable to narrow one of them down.
the differences i know of are:
a) the target DB has locally managed tablespaces while the source 9i DB had dictionary managed tablespaces
b) the block size is larger on the target 11g DB. 8192 vs 2048
c) the nchar character set on the source DB is AL16UTF16 and the target is UTF8 (we actually only have an nchar column in one of our tables...and also, the UTF8 setting was actually a mistake that we're correcting this weekend with a fresh DB and fresh import)
d) ??
does anyone have any idea what would cause the import to produce all these extra tablespaces? Or have a recommendation of what to research to figure it out?
Thanks a ton!
|
|
|
|
|
|
|
|
| Re: Importing from 9i to 11g with compressed extents creating extents on target [message #481351 is a reply to message #481348] |
Tue, 02 November 2010 13:01  |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
chris32680 wrote on Tue, 02 November 2010 13:54Good afternoon,
does anyone have any idea what would cause the import to produce all these extra tablespaces? Or have a recommendation of what to research to figure it out?
Easy answer. sorry to sound blunt, but who cares. Thousands, even ten of thousands of extents make no difference. This is something that went away with Oracle 8i. Why you have DMT in 9i is a good question.
I'd say really, move on and ignore extents. You'll spend many wasted days trying to come up with a combination of export, import, pre-creations to do what you want.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Importing from 9i to 11g with compressed extents creating extents on target [message #481361 is a reply to message #481354] |
Tue, 02 November 2010 13:18 |
chris32680
Messages: 92
Registered: January 2006
Location: Charlotte
|
Member |
|
|
Thanks for the responses.
I think my question came across too much as 'how can we prevent this' instead of 'why does it do this'. It's not that we don't want to let Oracle manage it the best way, we just like to understand why. I think the belief (for us) had always been that having a lot of extents would affect performance (even if minimally), and when doing a fresh import, we always assumed (because it had in the past) that the tables would be created in one large chunk with just the next extent allocated.
So from what I'm gathering, we need to read up on how post-8i, Oracle manages extents and what effect, if any, it has on performance.
Thanks again.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Importing from 9i to 11g with compressed extents creating extents on target [message #481493 is a reply to message #481485] |
Wed, 03 November 2010 15:18 |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Forget it, man. It's deprecated.
jw> create table t1(c2 date) storage (maxextents 1);
Table created.
jw> alter table t1 allocate extent;
Table altered.
jw> alter table t1 allocate extent;
Table altered.
jw> select count(*) from user_extents where segment_name='T1';
COUNT(*)
----------
3
jw> select max_extents from user_segments where segment_name='T1';
MAX_EXTENTS
-----------
2147483645
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
