Friends...
DB: 11.2.0.3
OS: Linux
I'm trying to understand how dropping an unused column effects the overall database (undo/redo/table lock)?
I have couple of tables (T1, T2) with 3 unused columns and I'm trying to estimate how much time it will take to drop all this columns since it's production system and needs to inform users.
Some details
1. Table T1: size 30GB (free space 25GB), rows 200 mil
2. Table T2: size 20GB, rows 300 mil
Questions:
1. How long dropping an unused columns on T1 might take, considering table size is 30GB but there is free space of 25GB?
2. I also need to move T1 to temp tablespace and bring it back to original tablespace, so that our system can use 25 extra GB, do you guys think I should move table first then drop unused column?
T1 tablespace is non assm.
3. Will moving table to temp tablespace and bringing it back to original tablespace will remove unused column from the system?
4. How to go with checkpoint with table with so many rows , every 100,000 rows?
5. Does dropping unused column generates too much redo/undo? how to estimate this?
6. Table T2 is 20 GB with 300 million rows, any guess how much time it might take?
7. Can I speedup dropping column process?
8. Will this dropping process will make table unusable for other users?
Sorry... I didn't knew there can be so many questions.. I started writing this and more and more questions came to my mind.
thanks and appreciate all reviews and answers.. hopefully this questions will help more forum users in the future...
Mike...