Anyway, I would like to change to a 8k block size to reduce the hassle of dealing with problems and to actually use compressed backups and FLASHBACK DATABASE.
Here's how I intend on doing it:
- Check for Transportable Tablespace violations. Clean up any violations.
- Define a DB_8K_CACHE_SIZE.
- Create equivalent 8K tablespaces. Resize the new tablespaces to cope with the table transfers.
- LONG / LONG RAWS. Many aplication versions ago, some columns were supposed to be converted from LONG RAW to BLOB. But at this customer, they weren't, and now it's my headache. Oracle can convert LONG RAW to BLOB by using the ALTER TABLE MOVE clause, and I've tested that the application still works with BLOB (Powerbuilder handles it internally according to the devs).
- Convert some of the tables' columns to BLOB.
- For those columns where I can't convert to LOB and I'm forced to maintain as LONG or LONG RAW, export drop, re-create (new tablespace) and import.About 4 tables, which isn't too much work.
- Move the LOB data to 8k-tablespaces. I wrote a script for this.
- Move all tables to the 8k-equivalent tablespaces.
- Rebuild all indexes to a new 8k tablespace.
- Set all 8k tablespaces to read only.
- Export using expdp for transport.
- Export the software code by using exp rows=n.
- Create a shiny, new db with 8k blocksize.
- Create the application-specific db roles and schema users.
- Create the directories.
- Attach the transportable tablespaces (application-specific).
- Change the default tablespaces per schema user.
- Import the software code fromuser / touser on the 8k db.
- Recreate the application user Oracle accounts.
Now, I could do a full export and a fromuser/touser import to a new db with a 8k block size. It will probably take around 9 hours. But that wouldn't fix the problem of LONG columns where they shouldn't be.
I have checked that, a table can have columns which reside simultaneously on 6k and 8k blocksize tablespaces.
No comments:
Post a Comment