| Migrate database from 4KB block size to 8Kb block size [message #63273] |
Thu, 23 September 2004 07:37  |
rodger
Messages: 10
Registered: August 2002
|
Junior Member |
|
|
Migrate database from 4KB block size to 8Kb block size
I have an old database that has been migrated through the oracle versions. It is now on 9i, however the database is still operating with 4kb block size. Performance is an issue and I have a couple of questions:
1. Will changing the block size to 8kb improve performance,
2. How does the block size affect performance? (The application accesses the tables predominantly via indexes so full table scans are not carried out.)
3. How would I change the block size? I tried importing (using imp) the data into an 8kb block size database but Oracle would not allow this.
Any information is much appreciated
Thanks
|
|
|
|
| Re: Migrate database from 4KB block size to 8Kb block size [message #63274 is a reply to message #63273] |
Thu, 23 September 2004 07:59  |
croK
Messages: 170
Registered: April 2002
|
Senior Member |
|
|
You are not right. Why do you say that oracle would not allow importing into new oracle database with 8k blok size?
That is the correct procedure.
You must full export from old db, and full import into new db..no matter what block size is new db.
That is the only way to increase db block size: NEW DATABASE CREATION.
I mean, it is the simplest way.
You can also do select * from all your tables to ascii files, and then use sqlloader to insert data into the new database.
You can also create database links in new database, and do insert into tables as select * from tables from old database. You must do as much inserts as tableas you have. Then re-create indexes..etc...
Of course, bigger datablocks will lead to increase queries performance. I'm currently using my all new databases with 16k blcock size. That's my choice.
Best luck.
Remote DBA for hire.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
