oracle table删除列,如何快速删除大表的某一列？

加个checkpoint....

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).