Import error - ORA-25157 Block size 6144 is not valid

[JMac]

I need to move a database from a server running Win2k and Oracle 9.2.0.3 onto a temporary machine running Win 2003 and Oracle 9.2.0.1

Question 1 - is the error I'm getting because the target database is a lower release of 9i? (I wildly assumed because the version numbers are of the same release (9.2) that it would not matter.)

The import fails creating the first tablespace:

IMP-00017:following statement failed with Oracle error 25157:
"CREATE TABLESPACE" AC_IMP " BLOCK SIZE 6144 DATAFILE "E:\ORACLE ..."etc
IMP-00003: ORACLE error 25157 encountered
ORA-25157:specified block size 6144 is not valid.

My export was a standard:
FULL=y
GRANTS=y
CONSISTENT=y
(no othere params specified)

The import was:
FULL=y
CONSISTENT=y
GRANTS=y


Question 2 = where does the CREATE TABLESPACE get the BLOCK size from?
I've never seen or heard of this type of error before.

[tamilselvan]

Try pre-create tablespaces in the target db, then do import with ignore=Y.

Tamil

[JMac]

Tablespaces were precreated. I should have added that information.

The source DB (9.2.0.3) has a DB block size of 6K (6144).

The target Db (9.2.0.1) has a DB block size of 8K.

These were default values from the DB creation and were never specified explicitly during creation. Has the different version of Windows resulted in the differing block sizes?

I thought Exp/Imp would have ignored things like block size???

If I re-run using Ignore = y then it should ignore that error - which is fine by me as it has no need to create the tablespaces??? yes?



any just Why did the CREATE TABLESPACE command use the block size parameter?

[JMac]

Perhaps if I drop the tablespaces and recreate them with a block size of 6K?

Always took block sizes for granted. Anyone point me at where I can read up on block sizes (in a Windows environment)?

[marist89]

Originally posted by JMac
any just Why did the CREATE TABLESPACE command use the block size parameter?

Because you can create tablespaces with different block sizes than your default block size in 9i.

[JMac]

Yes, understand that Jeff.

Looking at the new server more closely its a Win2k not Win 2003 machine. If I don't specify a block size when creating a DB why (or how) does Oracle choose 6144 or 8192 as the default block size - given that both machines run Windows 2000?

Both machines run Win 2k, but one uses 9.2.0.3 and the other 9.2.0.1

Can I get round my import problem by using IGNORE=Y and pre-creating the tablespaces? Would setting the export buffer size to 8192 help at all? (Clutching at straws with that one )

[slimdave]

How about editing the IMP file with a decent editor and just changing the 6144 to 8192?

[JMac]

Can you suggest a 'good' windows editor that can open a 130Mb .dmp file?

[slimdave]

http://www.ultraedit.com

Everyone dealing with data files on windows should be using this -- you can open any size file with it if you turn on "Disable Line Numbers" and turn on the "Open File without Temp File" options -- it has a great column editting mode, does sorts on flat files, hex editing, reg exp search replace.