Oracle unused column / drop unused column

Dropping unused columns:

Since someof those 'other' changes can be problematic if done online it is generally advisable to do a thorough analysis to make sure you have identified ALL of the desired changes so you can do then all at once. Often the best solution is to recreate the table and data using CTAS.

1. a normal 'drop unused' rewrites every block. It does NOT move rows to other blocks - it essentially just squeezes the row contents to eliminate any unused column content.

2. a normal 'drop unused', even when using checkpoints, leaves the table 'unusable' until the ENTIRE table has been processed. That means you MUST complete the operation before you can resume normal reuse of that table

3. a normal 'drop unused' will NOT free space that can be used by other objects - the same rows occupy the same blocks - the rows are just 'smaller'. So there is NO space to free.

Creating a new table (e.g. CTAS) allows you to use bulk loading and parallel operations and WILL move rows to new blocks. It can also take care of those 'other' changes I mentioned with little or no additional overhead.

Quelle: https://community.oracle.com/message/12540173

und hier das Beispiel von Tom Kyte:

if the goal is to reclaim space then the choice would probably be e) set unused all columns and then DROP with checkpoints. that'll rewrite each block (it will not move the rows off of the block they are on -- that would force us to rebuild all indexes as well if we did that). so you should end up with more blocks in the freelists after you are finished. the table will be very much as it was before, just each block will have more free space and (depending on your pctused of course). Here is an example (search for dbms_space on my web site for show_space): ops$tkyte@DEV816> create table t ( x int, y char(2000), z char(2000) ); Table created. ops$tkyte@DEV816> insert into t select rownum, 'x', 'y' from all_objects where rownum < 1001; 1000 rows created. ops$tkyte@DEV816> exec show_space( 'T' ) Free Blocks.............................5 Total Blocks............................1024 Total Bytes.............................8388608 Unused Blocks...........................19 Unused Bytes............................155648 Last Used Ext FileId....................7 Last Used Ext BlockId...................203209 Last Used Block.........................45 PL/SQL procedure successfully completed. that shows the table is using 1005 blocks and 5 of those blocks are on the freelist 1* alter table t set unused column x ops$tkyte@DEV816> / Table altered. ops$tkyte@DEV816> alter table t set unused column y; Table altered. ops$tkyte@DEV816> exec show_space('T') Free Blocks.............................5 Total Blocks............................1024 Total Bytes.............................8388608 Unused Blocks...........................19 Unused Bytes............................155648 Last Used Ext FileId....................7 Last Used Ext BlockId...................203209 Last Used Block.........................45 PL/SQL procedure successfully completed. that shows that setting the columns unused didn't do anything spacewise -- we are still at the same point ops$tkyte@DEV816> alter table t drop unused columns checkpoint 500; Table altered. ops$tkyte@DEV816> exec show_space('T') Free Blocks.............................1004 Total Blocks............................1024 Total Bytes.............................8388608 Unused Blocks...........................19 Unused Bytes............................155648 Last Used Ext FileId....................7 Last Used Ext BlockId...................203209 Last Used Block.........................45 PL/SQL procedure successfully completed. That shows that we now have 1004 blocks on the free list (pretty much EVERY block is).

Quelle: https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:623063677753