Tablespace has 3 datafiles - import uses which?

[JMac]

I exported a db into a replacement database recently. The original database had one datafile per tablespace, but the new one has 3 per tablespace spread over 3 different disks.

Example: One TBS had 3 x 100mB files. The Import filled one file 100%, the others: 25% and 27% (I had expected an even spread of 50% for each datafile).

Another TBS - 3 x 400Mb. Had a spread over the disks of 73% - 24% and 26%

A third TBS - 3 x 350Mb had a spread of 21% - 47% - 21%

Can anyone xplain to me how Oracle decides which disks to use? Is Import different from the normal load balancing I might expect?

Its 9.2.0.1 on Win NT4 and the TBSs are locally managed with uniform extent sizes of 1Mb

[hrishy]

Hi

Just wundering does it really matter ?How the data distribution is ?

If you want to spread ur data evenly precreate ur tables and other objects..and import with ignore=y

regards
Hrishy

[JMac]

Well, no, maybe not. I'm just interested in why one file filled 100% when the other TBSs have a more even spread of data.

I might prefer to have an even spread. There's no partitioning and I might want the access to any row to be more balanced and not 'stress' any particular datafile or disk.

[rajabalachandra]

what was the value for the export parameter
compress, when exporting.

compress=Y will consolidate extents.

[julian]

Oracle tries to spread the data equally among the files. Round-robin fashion.

[JMac]

Oracle tries to spread the data equally among the files. Round-robin fashion.

Which is what I thought.

One issue might (or might not!) be the DB block size.

The exporting DB had a block size of 6K, the importing one had a block size of 8K. To get round this I pre-created the tablespaces in the target DB, and set IGNORE=Y so as to ignore the errors when the FULL import tried to create Tablespaces of 6k blocks.

However, the fact that the spread is not consistent and one TBS has a 100% full datafile leaves me to believe this is not the case.

Export Params:
FULL=Y
GRANTS=Y
CONSISTENT=Y
ROWS=Y
COMPRESS=N

Import Params:

IGNORE=Y
FULL=Y
ROWS=Y
INDEXES=Y
GRANTS=Y

[DaPi]

Originally posted by julian
Oracle tries to spread the data equally among the files. Round-robin fashion.

From what I observed with a MOVE, it would seem that this is "simplistic" in its implementation. It looked as if, for each table, Oracle always started with the same file - so that if all your tables had only one extent, they would all be in the first file (not sure how "first" is defined).

[julian]

Yes, true. Oracle only "tries" to spread the data evenly through the files.

[abhaysk]

Originally posted by DaPi
(not sure how "first" is defined).

Min of File ID..?