Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, kakali.

Asked: February 05, 2001 - 2:38 am UTC

Answered by: Tom Kyte - Last updated: January 24, 2006 - 9:00 pm UTC

Category: Database - Version: Oracle 8i

Viewed 10K+ times! This question is

You Asked

My question is regarding the drop column command which can be used in 8i to drop the column...

One command is drop column column_name

Another one is through alter table command

alter table table_name set unused column column_name;

alter table table_name drop unused columns;

What's the difference between the two ways..

Rgds
kakali



and we said...

They are both done via the alter table command.

alter table T drop column....
alter table T set unused.....

The drop column physically removes the column from the table. It rewrites the entire table.

The set unused "flag" deletes the column. It is effectively gone as far as you are concerned but the space is not reclaimed.

and you rated our response

  (38 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

impact to RBS and redo

April 04, 2002 - 4:21 pm UTC

Reviewer: an from DE, U.S.A

Hi Tom:

what different between those two alter command will impact rbs and redo?
thanks,
an

Tom Kyte

Followup  

April 04, 2002 - 7:13 pm UTC

alter set unused -- thats a flag delete. no effect on undo/redo really.

alter drop -- thats a rewrite, potentially big effect on undo/redo.

Need More Info.

April 05, 2002 - 1:04 am UTC

Reviewer: Parag from India

Can you be more Precise with your answere Tom.

Tom Kyte

Followup  

April 05, 2002 - 9:15 am UTC

not really. which part isn't making sense --

set unused = "flag" delete. No work done to the table, just zap the data dictionary, column appears to have gone missing -- data is not rewritten. no undo, no redo to speak of.


drop column = "really" delete. table is effectively rewritten. lots of undo, lots of redo.

really don't know how I could be more "precise"

Drop columns in unused tables

April 05, 2002 - 6:11 am UTC

Reviewer: Reddy from Bangalore,India,

Hi tom

I am using somany tables. If I want to drop all columns which is not usefull. how can i drop. Here I would like to that table created before 4months, these table columns only should drop which is not used.

Thanks



Tom Kyte

Followup  

April 05, 2002 - 9:34 am UTC

If you have sooo many tables with columns you do not use, I rather question your development environment. What leads you to be in such a position?

Anyway, If you read the alter table command, you'll learn all about "drop column" and "set unused".

As far as Oracle is concerned there is no such thing as a "column that is not used". It will be up to you to identify those columns which are not used by your application (and if you cannot do that, stop development, get it under control)

alter table drop unused columns

April 06, 2002 - 11:52 pm UTC

Reviewer: Reddy from Bangalore, India

Hi tom

I have some confusion.

pls look up this.

SQL>DESC BUDG_INDIRECT_COST_MAST_TBL;
 Name        Null?    Type
----------------------------
VOUCH_NO     NOT NULL VARCHAR2(50)
VOUCH_DT              DATE
PHY_DATE              DATE
LOG_DATE              DATE
PROC_DATE             DATE

SQL> Select PHY_DATE,LOG_DATE,PROC_DATE FROM     
     BUDG_INDIRECT_COST_MAST_TBL;
   
    PHY_DATE   LOG_DATE    PROC_DATE
    --------   --------    ----------



SQL>
SQL> ALTER TABLE BUDG_INDIRECT_COST_MAST_TBL DROP UNUSED COLUMNS;

Tabe altered.
SQL>DESC BUDG_INDIRECT_COST_MAST_TBL;
 Name        Null?    Type
----------------------------
VOUCH_NO     NOT NULL VARCHAR2(50)
VOUCH_DT              DATE
PHY_DATE              DATE
LOG_DATE              DATE
PROC_DATE             DATE

SQL> Select PHY_DATE,LOG_DATE,PROC_DATE FROM     
     BUDG_INDIRECT_COST_MAST_TBL;
   
    PHY_DATE   LOG_DATE    PROC_DATE
    --------   --------    ----------



SQL>

my doubt why this alter command not drop columns, if not why message "Table altered" came.

Thanks in advance

Reddy
 

Tom Kyte

Followup  

April 07, 2002 - 11:22 am UTC

What you showed doesn't demonstrate what you are trying to show.

drop unused is not there to remove all columns that contain just NULLS.  drop unused (as per the documentation) :

<quote>
DROP UNUSED COLUMNS

removes from the table all columns currently marked as unused. Use
this statement when you want to reclaim the extra disk space from
unused columns in the table. If the table contains no unused columns,
the statement returns with no errors.
</quote>

A column that is marked UNUSED is "flag" deleted -- you WILL NOT see it in a describe by definition (it is as good as gone, it just is consuming space at that point).

Here is an example that shows this command in action:


ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t
  2  pctfree 0 pctused 99
  3  as
  4  select * from all_objects
  5  /
Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec show_space( 'T' )
Free Blocks.............................0
Total Blocks............................256
Total Bytes.............................2097152
Unused Blocks...........................44
Unused Bytes............................360448
Last Used Ext FileId....................7
Last Used Ext BlockId...................64137
Last Used Block.........................20

PL/SQL procedure successfully completed.

<b>so, here we have a table that is "packed" -- 256-44 blocks are used (212 of them)</b>


ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t set unused ( owner, object_name, object_id, created, last_ddl_time );

Table altered.

Elapsed: 00:00:00.38

<b>we marked all of the non-null columns UNUSED.  Notice that this took no time at all.  Far too little time to have actually REMOVED them.  This was a data dictionary update only.  We can see that easily by dumping the space again:</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec show_space( 'T' )
Free Blocks.............................0
Total Blocks............................256
Total Bytes.............................2097152
Unused Blocks...........................44
Unused Bytes............................360448
Last Used Ext FileId....................7
Last Used Ext BlockId...................64137
Last Used Block.........................20

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

<b>see, no change in utilization.... So, lets DROP UNUSED:</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t drop unused columns;

Table altered.

Elapsed: 00:00:28.74

<b>well, that took quite a bit of time -- lets see what happened with the space:</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec show_space( 'T' )
Free Blocks.............................211
Total Blocks............................256
Total Bytes.............................2097152
Unused Blocks...........................44
Unused Bytes............................360448
Last Used Ext FileId....................7
Last Used Ext BlockId...................64137
Last Used Block.........................20

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

<b>that shows the entire table was rewritten -- EVERY block is on the free list now (there is one block overhead for the table itself -- 211+1 = 212, all blocks accounted for)</b>


The database does not magically all by itself determine which columns are "unused" (what if you have an application that had an insert into t (object_name) values ( null ) statement - that column is MOST CERTAINLY used.  NULL does not imply "not used", rather it means "value is not known").

YOU have to tell US what is not used, we do not tell you.

 

Be careful with DROP COLUMN

April 08, 2002 - 4:49 am UTC

Reviewer: Adrian Billington from UK

Drop column generates proportionately massive amounts of redo and in my experience is a horrible way to remove columns. The table is locked until the column is removed and its data deleted (all rows are re-written to exclude the drop column data). On big tables this can be horrible. You can force regular checkpoints to avoid blowing your rollback, but the whole process can be painful.

Your alternatives are to set all the columns you want to drop to unused and drop them all in one go. Personally, I prefer to set all the unused columns and then either perform a CTAS to create a new copy of the table without the unused columns or even an ALTER TABLE MOVE which is a very quick way of re-writing the data without the unused columns. Either way you will need to consider your indexes.

If it's a small table with just one or two columns to remove, then fine, go ahead and drop, but if the table is of any size at all, then use with caution...

Regards

Adrian

Tom Kyte

Followup  

April 08, 2002 - 8:51 am UTC

It surprises me, especially after seeing all of the information presented here, that the above comment is not "intuitively obvious".

I've said it quite a few times -- DROP COLUMN rewrites the entire table. This operation OF COURSE must be protected by undo and redo (in the event of a server/instance failure -- you'd be really truly mad at us if we didn't).

The alter table MOVE is a much better approach overall them CTAS as it retains grants, constraints, triggers, indexes, etc...


Followup

April 08, 2002 - 9:39 am UTC

Reviewer: Adrian Billington from UK

Tom

All of the above might be "intuitively obvious" to us as we have both experienced the ups and downs of the drop / unused commands first hand. I was merely trying to assist some of the reviewers with my experience and to offer some friendly advice in addition to your technical answers. When people see the DROP COLUMN command for the first time, they go "oh great we can use that" without sometimes being aware of the underlying implications. Just because a feature is there doesn't always make it the right choice.

I agree that the MOVE methodology is the preferred option...

Regards

Adrian

Tom Kyte

Followup  

April 08, 2002 - 9:48 am UTC

I wasn't pinging you. I've just been surprised by the back and forth I've had to go on with something as simple as "drop column" and "drop unused".

Sorry if you took offense, I was sort of trying to commiserate with you - not ping you on being blatantly obvious...

Healthy exhange

April 08, 2002 - 11:03 am UTC

Reviewer: A reader

Tom

None taken merely a healthy exhange of opinions ;)

Adrian

Reader

July 08, 2002 - 5:14 am UTC

Reviewer: A reader

Tom,

Did the following test:

created t as select * from dba_users for a few times

SQL>alter table t add (x number(1));

04:21:31 SQL> desc t
 Name                              Null?    Type
 ------------------------------- -------- ---------
 OWNER                                    VARCHAR2(30)
 OBJECT_NAME                              VARCHAR2(128)
 SUBOBJECT_NAME                           VARCHAR2(30)
 OBJECT_ID                                NUMBER
 DATA_OBJECT_ID                           NUMBER
 OBJECT_TYPE                              VARCHAR2(18)
 CREATED                                  DATE
 LAST_DDL_TIME                            DATE
 TIMESTAMP                                VARCHAR2(19)
 STATUS                                   VARCHAR2(7)
 TEMPORARY                                VARCHAR2(1)
 GENERATED                                VARCHAR2(1)
 SECONDARY                                VARCHAR2(1)
 X                                        NUMBER(1)

Total number of blocks = 23856 (8k)


02:41:56 SQL> select count(*) from t where x is null;

  COUNT(*)
----------
   1985024





03:57:12 SQL> update /*+ parallel(t, 2) */ t t set x=0 where x is null and rownum < 500000;

499999 rows updated.

Elapsed: 00:03:45.54

Execution Plan
----------------------------------------------------------
   0      UPDATE STATEMENT Optimizer=CHOOSE (Cost=2295 Card=99252 Byte
          s=1290276)

   1    0   UPDATE OF 'T'
   2    1     COUNT (STOPKEY)
   3    2       TABLE ACCESS* (FULL) OF 'T' (Cost=2295 Card=99252 Byte :Q28000
          s=1290276)



   3 PARALLEL_TO_SERIAL            SELECT /*+ NO_EXPAND ROWID(A1) */ A1.ROWID,A
                                   1."X" FROM "T" A1 WHERE ROWID BETWEE



Statistics
----------------------------------------------------------
          0  recursive calls
    2031184  db block gets
       7412  consistent gets
      11853  physical reads
  249434528  redo size
       1187  bytes sent via SQL*Net to client
        781  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
     499999  rows processed

04:01:23 SQL> commit;

Commit complete.

Elapsed: 00:00:00.01

Questions:

1. why the 2, 031, 184 for update of  

Reader

July 08, 2002 - 5:17 am UTC

Reviewer: A reader

Tom,

Did the following test:

created t as select * from dba_users for a few times

SQL>alter table t add (x number(1));

04:21:31 SQL> desc t
 Name                              Null?    Type
 ------------------------------- -------- ---------
 OWNER                                    VARCHAR2(30)
 OBJECT_NAME                              VARCHAR2(128)
 SUBOBJECT_NAME                           VARCHAR2(30)
 OBJECT_ID                                NUMBER
 DATA_OBJECT_ID                           NUMBER
 OBJECT_TYPE                              VARCHAR2(18)
 CREATED                                  DATE
 LAST_DDL_TIME                            DATE
 TIMESTAMP                                VARCHAR2(19)
 STATUS                                   VARCHAR2(7)
 TEMPORARY                                VARCHAR2(1)
 GENERATED                                VARCHAR2(1)
 SECONDARY                                VARCHAR2(1)
 X                                        NUMBER(1)

Total number of blocks = 23856 (8k)


02:41:56 SQL> select count(*) from t where x is null;

  COUNT(*)
----------
   1985024





03:57:12 SQL> update /*+ parallel(t, 2) */ t t set x=0 where x is null and rownum < 500000;

499999 rows updated.

Elapsed: 00:03:45.54

Execution Plan
----------------------------------------------------------
   0      UPDATE STATEMENT Optimizer=CHOOSE (Cost=2295 Card=99252 Byte
          s=1290276)

   1    0   UPDATE OF 'T'
   2    1     COUNT (STOPKEY)
   3    2       TABLE ACCESS* (FULL) OF 'T' (Cost=2295 Card=99252 Byte :Q28000
          s=1290276)



   3 PARALLEL_TO_SERIAL            SELECT /*+ NO_EXPAND ROWID(A1) */ A1.ROWID,A
                                   1."X" FROM "T" A1 WHERE ROWID BETWEE



Statistics
----------------------------------------------------------
          0  recursive calls
    2031184  db block gets
       7412  consistent gets
      11853  physical reads
  249434528  redo size
       1187  bytes sent via SQL*Net to client
        781  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
     499999  rows processed

04:01:23 SQL> commit;

Commit complete.

Elapsed: 00:00:00.01

Questions:

1. why the 2, 031, 184 "db block gets"for update of 499999 
   rows

2. Are there ways to tune initiallizing of the the
   new column. It takes lot of  time and consumes lot
   of redo.

Thanks 

Reader

July 08, 2002 - 5:21 am UTC

Reviewer: A reader

In the previous posting,

I also created a temporary index t_x on t(x), to keep
track of the batch of rows that have been already updated

Tom Kyte

Followup  

July 08, 2002 - 7:42 am UTC

Not that your question has anything at all to do with anything else on this page but.... it is the index maintainence. Every row updated updated your index right -- index blocks split, move about -- you could have 2 or 3 blocks updated in an index for every updated row.

You have at least 1,000,000 db block gets (499,999 rows updated + 499,999 index blocks). Since x started at NULL (eg: not in the index, NULL entries are not made) every update you do adds (increases the size of) your index. All of the values are the same -- hence they all tend to goto the same place in the index -- hence the index on x is way hot in one spot, splitting and splitting and splitting.


My advice -- it looks like you are trying to "break an update up" into smaller chunks. Just bite the bullet, size your rollback correctly and do it in one statement. The amount of time you've spent going down this path probably cost the equivalent of 10 36gig SCSI drives. It would have been cheaper just to buy them, use them once for the mass update and throw them away afterwards.

Reader

July 08, 2002 - 8:45 am UTC

Reviewer: A reader

Tom,

Thanks very much for the explanation. I'll increase the
RBS, "set transaction ..." and do the updates. It takes
about 30 minutes to update 5 million rows with 5 new
columns.

I was thinking in terms of increasing the degree of
parallelism, which would re-write the blocks in parallel.
Would it have an improve the processing

In your web-site, I have read that, each time a row is
updated, Oracle get the block in CU mode and then gives it
back. Would it have any benefit, if when adding a column
set default value to 0. From what I tested, it seems there
is not much of a difference, if updated subsequently or
declare the column with default value. Does Oracle
handle the re-write of db blocks the same way in both
scenarios

Thanks

Tom Kyte

Followup  

July 08, 2002 - 2:50 pm UTC

In 9iR1 and before, parallel update works only on PARTITIONED data -- in your example, the FULL scan is parallelized --- but the update itself is serial.

It seems that it would be more work to add a column with a default of 0 and then update it. The alter table would take that much longer (adding a NULLABLE, DEFAULT NULL column to a table is virtually instantaneous, adding a column with a non-null default value takes considerably longer as it has to rewrite the table)



drop columns

February 10, 2003 - 1:14 pm UTC

Reviewer: mo

Tom:

Is there a way to drop multiple columns in SQL statement or you have to do one column at a time?


Tom Kyte

Followup  

February 11, 2003 - 7:42 am UTC

when you checked the docs for ALTER TABLE -- what did they tell you?

Drop column

December 14, 2003 - 12:14 am UTC

Reviewer: A reader from Houston

Tom,
We need to drop a column on a table having close to a million records. The DBA says that there will be empty spaces once the column drop is done and an export/import of the table needs to be done after the column drop. My understanding was that the space on the whole table gets re-organized once the column is dropped(and there is no need to do an export/import)?
Part of my understanding based on
<quote>
>></code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1381803609490, <code>
>>The drop column physically removes the column from the table. It rewrites the entire table.
</quote>

My question
1) Once a column is dropped from a table, is there any command that needs to be executed to organize the space in the table? Should an export/import of the table be done after a column drop?
2) Will query execution be slow after a column drop?
3) What are the things that happen on the DB when a column is dropped from a table?

Thanks a lot for having such a wonderful forum.

Tom Kyte

Followup  

December 14, 2003 - 9:55 am UTC

well -- if you have a version of Oracle that can do a drop column
AND
the dba wants to export/import to reorg an object
THEN
the dba needs to get a refresher :)


alter table T move;
alter index I rebuild;

that is what the DBA really really wants to do.  Not only will it be faster (can be done nologging, without undo generation using direct path operations rather then row by row inserts with impo) -- but it will be incredibably SAFE (might you lose a grant, index, constraint, something by taking the data OUT of the database and putting it back IN........


but, lets see what happens when we drop a column.  I'll use manual segment space mgmt here (not assm) since thats what most people are probably using but....




ops$tkyte@ORA920> drop table big_table;
 
Table dropped.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> create table big_table
  2  pctfree 10 pctused 90
  3  as
  4  select *
  5    from big_table.big_table
  6   where rownum <= 1000000;
 
Table created.
 
<b>I used pctused of 90 so as to demonstrate what'll happen when we drop the column.  If I left it at the default of 40 -- none of my blocks in my example would have gone on the freelist list (since the blocks were all nicely packed full -- not normal in the real world... anyway..</b>

ops$tkyte@ORA920>
ops$tkyte@ORA920> analyze table big_table compute statistics for table;
 
Table analyzed.
 
ops$tkyte@ORA920> begin
  2  print_table( '
  3  select TABLE_NAME, PCT_FREE, PCT_USED,
  4         NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE,
  5         AVG_ROW_LEN, AVG_SPACE_FREELIST_BLOCKS, NUM_FREELIST_BLOCKS
  6    from user_tables where table_name = ''BIG_TABLE''
  7    ' );
  8  end;
  9  /
TABLE_NAME                    : BIG_TABLE
PCT_FREE                      : 10
PCT_USED                      : 90
NUM_ROWS                      : 1000000
BLOCKS                        : 12637
EMPTY_BLOCKS                  : 674
AVG_SPACE                     : 857
AVG_ROW_LEN                   : 89
AVG_SPACE_FREELIST_BLOCKS     : 0
NUM_FREELIST_BLOCKS           : 0
-----------------
 
PL/SQL procedure successfully completed.

<b>remember those numbers.....</b>
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> alter table big_table drop column object_name;
 
Table altered.
 
ops$tkyte@ORA920> analyze table big_table compute statistics for table;
 
Table analyzed.
 
ops$tkyte@ORA920> begin
  2  print_table( '
  3  select TABLE_NAME, PCT_FREE, PCT_USED,
  4         NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE,
  5         AVG_ROW_LEN, AVG_SPACE_FREELIST_BLOCKS, NUM_FREELIST_BLOCKS
  6    from user_tables where table_name = ''BIG_TABLE''
  7    ' );
  8  end;
  9  /
TABLE_NAME                    : BIG_TABLE
PCT_FREE                      : 10
PCT_USED                      : 90
NUM_ROWS                      : 1000000
BLOCKS                        : 12637
EMPTY_BLOCKS                  : 674
AVG_SPACE                     : 2765
AVG_ROW_LEN                   : 65
AVG_SPACE_FREELIST_BLOCKS     : 2765
NUM_FREELIST_BLOCKS           : 12637
-----------------
 
PL/SQL procedure successfully completed.

<b>see how every block jumped onto the freelist.  they are available for reuse -- new rows.  the table did not shrink here but each and every block was rewritten.  the space reclaimed.  and the block put onto the freelist.

This might not be 100% desirable -- but it is only a 1million row table (not very large).  Over time, the freespace would be reused as new inserts are made.

Using the alter table move:</b>

ops$tkyte@ORA920>
ops$tkyte@ORA920> alter table big_table MOVE;
 
Table altered.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> analyze table big_table compute statistics for table;
 
Table analyzed.
 
ops$tkyte@ORA920> begin
  2  print_table( '
  3  select TABLE_NAME, PCT_FREE, PCT_USED,
  4         NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE,
  5         AVG_ROW_LEN, AVG_SPACE_FREELIST_BLOCKS, NUM_FREELIST_BLOCKS
  6    from user_tables where table_name = ''BIG_TABLE''
  7    ' );
  8  end;
  9  /
TABLE_NAME                    : BIG_TABLE
PCT_FREE                      : 10
PCT_USED                      : 90
NUM_ROWS                      : 1000000
BLOCKS                        : 9262
EMPTY_BLOCKS                  : 977
AVG_SPACE                     : 838
AVG_ROW_LEN                   : 65
AVG_SPACE_FREELIST_BLOCKS     : 0
NUM_FREELIST_BLOCKS           : 0
-----------------
 
PL/SQL procedure successfully completed.


<b>we shrink the table.  BUT, if you are going to "shrink the table", if that is the goal -- DO NOT USE drop, do this instead:</b>


ops$tkyte@ORA920> drop table big_table;
 
Table dropped.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> create table big_table
  2  pctfree 10 pctused 90
  3  as
  4  select *
  5    from big_table.big_table
  6   where rownum <= 1000000;
 
Table created.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> analyze table big_table compute statistics for table;
 
Table analyzed.
 
ops$tkyte@ORA920> begin
  2  print_table( '
  3  select TABLE_NAME, PCT_FREE, PCT_USED,
  4         NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE,
  5         AVG_ROW_LEN, AVG_SPACE_FREELIST_BLOCKS, NUM_FREELIST_BLOCKS
  6    from user_tables where table_name = ''BIG_TABLE''
  7    ' );
  8  end;
  9  /
TABLE_NAME                    : BIG_TABLE
PCT_FREE                      : 10
PCT_USED                      : 90
NUM_ROWS                      : 1000000
BLOCKS                        : 12637
EMPTY_BLOCKS                  : 674
AVG_SPACE                     : 857
AVG_ROW_LEN                   : 89
AVG_SPACE_FREELIST_BLOCKS     : 0
NUM_FREELIST_BLOCKS           : 0
-----------------
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> alter table big_table <b>set unused column object_name;</b>
 
Table altered.
 
ops$tkyte@ORA920> analyze table big_table compute statistics for table;
 
Table analyzed.
 
ops$tkyte@ORA920> begin
  2  print_table( '
  3  select TABLE_NAME, PCT_FREE, PCT_USED,
  4         NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE,
  5         AVG_ROW_LEN, AVG_SPACE_FREELIST_BLOCKS, NUM_FREELIST_BLOCKS
  6    from user_tables where table_name = ''BIG_TABLE''
  7    ' );
  8  end;
  9  /
TABLE_NAME                    : BIG_TABLE
PCT_FREE                      : 10
PCT_USED                      : 90
NUM_ROWS                      : 1000000
BLOCKS                        : 12637
EMPTY_BLOCKS                  : 674
AVG_SPACE                     : 857
AVG_ROW_LEN                   : 89
AVG_SPACE_FREELIST_BLOCKS     : 0
NUM_FREELIST_BLOCKS           : 0
-----------------
 
PL/SQL procedure successfully completed.
 
<b>see, nothing changed -- but the set unused was like "really fast".  now, we'll rebuild easily:</b>


ops$tkyte@ORA920>
ops$tkyte@ORA920> alter table big_table MOVE;
 
Table altered.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> analyze table big_table compute statistics for table;
 
Table analyzed.
 
ops$tkyte@ORA920> begin
  2  print_table( '
  3  select TABLE_NAME, PCT_FREE, PCT_USED,
  4         NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE,
  5         AVG_ROW_LEN, AVG_SPACE_FREELIST_BLOCKS, NUM_FREELIST_BLOCKS
  6    from user_tables where table_name = ''BIG_TABLE''
  7    ' );
  8  end;
  9  /
TABLE_NAME                    : BIG_TABLE
PCT_FREE                      : 10
PCT_USED                      : 90
NUM_ROWS                      : 1000000
BLOCKS                        : 9398
EMPTY_BLOCKS                  : 841
AVG_SPACE                     : 836
AVG_ROW_LEN                   : 66
AVG_SPACE_FREELIST_BLOCKS     : 0
NUM_FREELIST_BLOCKS           : 0
-----------------
 
PL/SQL procedure successfully completed.

<b>and there we go.  No chance to lose grants, constraints, indexes, nothing.  Data is transactionally "protected" (anytime the data LEAVES my database, I can easily lose it.  maybe the dmp file goes bad.  maybe I do something wrong.  If I leave it in the database -- I'm protected).  

It is only 1,000,000 rows -- not very big, this only takes a couple of seconds.</b>
 

Reader

December 14, 2003 - 11:37 am UTC

Reviewer: A reader

<quote>
done nologging, without undo generation using direct path operations rather
then row by row inserts with impo
<quote>

Is this direct path operation bypass buffer cache

Tom Kyte

Followup  

December 14, 2003 - 12:27 pm UTC

yes.

Drop column

December 14, 2003 - 11:58 am UTC

Reviewer: Houston

Tom,
Thank you for the excellent explanation. Couldn't asked for more.
The version of Oracle we are using is 9.2.0.4. If shrinking the table is not the objective, then we don't have to issue
>>alter table T move;
Right?

If we have to do a
alter table T move;
Does this generate any redo log? Is the redo generated significant? We cannot go with the option of nologging or no undo generation - because we have a stand by db and a replicated db.

Thank you

Tom Kyte

Followup  

December 14, 2003 - 12:33 pm UTC

it generates redo, yes (else the standby would not get the changes!)

it generates enough redo to redo the operation. is that significant? guess it depends on your definition of significant.

the drop column itself generates redo.

Given that we are probably talking about a couple of meg AT MOST here, suggest you consider just setting the column unused and forgetting about it. fast, no redo to speak of, column gone



dropping a a column

December 14, 2003 - 9:21 pm UTC

Reviewer: A reader from Houston

Thank you for making the concept clear

what if the column to be dropped is a clob

December 19, 2003 - 8:44 am UTC

Reviewer: A reader from Houston

Tom,
Since LOB columns are not managed through freelists, Does 'alter table big_table MOVE;' make the NUM_FREELIST_BLOCK to 0 when a lob column is dropped? Should export/import be used to reorg the object with the column being dopped is a clob?

Thank you


Tom Kyte

Followup  

December 20, 2003 - 9:18 am UTC

lobs are not moved when the table is moved -- lobs are stored in a separate segment, you move them all by themselves.


you use alter move to "reorg" -- don't use export import.

dropping a lob

December 21, 2003 - 12:41 am UTC

Reviewer: A reader from Houston

Tom,
>>lobs are not moved when the table is moved
If lobs are not moved when we do
alter table big_table MOVE;

Then if we drop a lob column how is the space, how is the space occupied by the lob column from the table recovered? (ie. what is the equivalent of table shrinking in this situation?)
Can you please explain
>>you move all by themselves

Thank you

Tom Kyte

Followup  

December 21, 2003 - 10:23 am UTC

what does "dropping a column" have to do with "moving a table" and why do you think they are related?

the act of dropping a column frees the storage in the table for reuse in the table (table blocks might go onto the freelist).  The log segment is "dropped" at that point.  The lob INDEX and lob DATA is just "dropped".

you have table data (includes the lob locator).
you have a lob index (separate segment, not in table).
you have lob data pages (separate segment again, not in table).

dropping a lob column clears the lob locator out, drops the index segment and data pages for the lob (as it does when you "drop the table")

see:

ops$tkyte@ORA920> create table t ( x int, y clob, z blob );
 
Table created.
 
ops$tkyte@ORA920> set echo off
 
OBJECT_TYPE  OBJECT_NAME                    S TABLESPACE_NAME
------------ ------------------------------ - ------------------------------
LOB          SYS_LOB0000059367C00002$$        USERS
             SYS_LOB0000059367C00003$$        USERS
 
TABLE        T                                USERS
 
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> alter table t drop column y;
 
Table altered.
 
ops$tkyte@ORA920> set echo off
 
OBJECT_TYPE  OBJECT_NAME                    S TABLESPACE_NAME
------------ ------------------------------ - ------------------------------
LOB          SYS_LOB0000059367C00002$$        USERS
 
TABLE        T                                USERS
 
 
ops$tkyte@ORA920> alter table t drop column z;
 
Table altered.
 
ops$tkyte@ORA920> set echo off
 
OBJECT_TYPE  OBJECT_NAME                    S TABLESPACE_NAME
------------ ------------------------------ - ------------------------------
TABLE        T                                USERS
 
 
ops$tkyte@ORA920>

<b> the segments just "disappear", that is how the lob space is recovered</b>
 

What happens if we use ASSM

June 14, 2004 - 5:36 pm UTC

Reviewer: Arun Gupta from Harrisburg, PA USA

Tom,
The example you worked out for the question titled "Drop column December 14, 2003", you have used manual space management. If I use assm, does the example/results still apply?

My second question is that if someone asks to add/drop columns regularly from tables, what kind of trouble they are headed for?

Thanks


Tom Kyte

Followup  

June 15, 2004 - 8:15 am UTC

yes, it still applies.


if someone regularly adds/drops a column from a table, one is in a situation I've never heard of. I would imagine lots of issues -- like not having any applications work for more then a day or two as the columns they need come and go.

what are the circumstances that would get you into that situation.

word of warning

June 15, 2004 - 5:02 pm UTC

Reviewer: Jeff from Milwaukee WI USA

It's been a couple years, so my memory is a little fuzzy on the details, but there is what I presume to be a bug that can potentially corrupt data after setting a column unused in version 8.1.7.3 on VMS (at least).

Specifically, some time after doing "alter table t set unused" on several tables, I performed ALTER TABLE T MOVE commands to relocate tables to a new LMT tablespace. On a few of the tables after this, some of the columns in the table contained garbage data. These were columns located after the unused columns in the row, and my conclusion after investigation was that data from the "unused" columns were being read as if they were in the still remaining columns - that is, the "offsets" for the unused data were incorrect when processing the MOVE. I think this had somethign to do with VARCHAR2 and Numeric columns that abutted each other.

I ended up having to restore backups and execute an "alter table t drop unused columns". Once I did that, then I was able to sucessfully do the table move without corruption.





Recover unused column and data

October 28, 2004 - 12:38 pm UTC

Reviewer: Laxman Kondal from Springfield, VA

Hi Tom

After executing 'alter table emp set unsed(sal, comm)' is there any command/way to bring it back with old data rather creating new column and adding data?

Thanks.

Tom Kyte

Followup  

October 28, 2004 - 7:20 pm UTC

No there isn't. once you set unused, the data is as good as *gone*

it is a 'faster drop column'.

Drop Column

December 26, 2004 - 1:06 am UTC

Reviewer: Binh Nguyen Van

Hi Tom
I just install Oracle 10g in my windowxp computer. I could not able to drop column. Very time I tried to drop a column it is give me an error message like this:
ORA-12988: cannot drop column from table owned by SYS
I did connect to sqlplus by sysdba, why it gives me that message? Would you please give me some help?

Thanks

Binh Nguyen Van

Tom Kyte

Followup  

December 26, 2004 - 12:22 pm UTC

ORA-12988: cannot drop column from table owned by SYS


Hmmm, seems obvious to me? You are SYS, SYS is not allowed to do that.


You should never use SYS, SYSTEM or any of the supplied oracle admin accounts to play with.


create your own account, use it. period. do not do things as SYS, sys is special, sys is magical, sys is special, sys is governed by a set of rules different from the rest of the world. Use "as sysdba" only to perform maintenance operations like shutdown, startup and the such.

Thank you for quick response

December 26, 2004 - 9:56 pm UTC

Reviewer: Binh Nguyen Van

I did create a for myself one account name is student. From that account I create one table with five columns for practice. I tried to drop the table, it is successfuly drop the table. I did recreate another table with the same structure and tried to drop a column, but It gave me an error: ORA-12988: cannot drop column from table owned by SYS

the comand I use is ALTER TABLE faculty DROP COLUMN f_rank;
Would you please tell me what I do wrong?

Thanks

Binh Nguyen Van

Tom Kyte

Followup  

December 27, 2004 - 9:53 am UTC

*you are logged in as SYS*


you are logged in AS SYS

you are not logged in AS STUDENT


do this:

SQL> show user 

Thank

December 28, 2004 - 4:02 pm UTC

Reviewer: A reader

Thank you very much, I got it and here is my problem.
At first I log in as Student, second I have connected to the sysdba. After that I have been been created table. So that I belong to the sys without my intend. I realy apprecite your help.

Thanks

Binh Nguyen Van

can not drop column, table is owned by sys schema

December 31, 2004 - 8:56 am UTC

Reviewer: Kishor Bhalwankar from India

Tom,
Please explain following behaviour

SQL> sho user
USER is "SYS"

SQL> create table t1 as select * from user_tables where 1 =2 ;

Table created.

SQL> alter table t1
  2  drop column compression
  3  /
alter table t1
*
ERROR at line 1:
ORA-12988: cannot drop column from table owned by SYS


SQL> alter table t1
  2  set unused column compression
  3  /
alter table t1
*
ERROR at line 1:
ORA-12988: cannot drop column from table owned by SYS


SQL> drop table t1 ;

Table dropped.

SQL> sho user
USER is "SYS"
 

Tom Kyte

Followup  

December 31, 2004 - 11:18 am UTC

sorry if this sounds "smart #$@" but, what part of the error message (which is un-characteristically perhaps "crystal clear") isn't understandable?


"Cannot drop column from table owned by SYS"

<quote from directly above>#B
do not do things as SYS, sys is
special, sys is magical, sys is special, sys is governed by a set of rules
different from the rest of the world. Use "as sysdba" only to perform
maintenance operations like shutdown, startup and the such.
</quote>

A reader

December 31, 2004 - 2:12 pm UTC

Reviewer: A reader

Have a happy new year :)

Create Procedure in Oracle Procedure Builder

January 03, 2005 - 1:22 am UTC

Reviewer: Binh Nguyen Van

Hi Tom,
I have been trying very hard with simple program in the Oracle book. Here is the code:
PROCEDURE create_new_order_line
(current_inv_id NUMBER, current_quantity NUMBER)
IS
BEGIN
INSERT INTO order_line VALUES
(order_id_sequence.currval, current_inv_id, current_quantity);
COMMIT;
END;

This code will compiles good in SQL*plus, but could not compile in Procedure Builder. Whenever I tried to test the code, it gives me an error message: Error 201

Any help will be appreciated.

Thanks

Binh Nguyen Van

Tom Kyte

Followup  

January 03, 2005 - 8:27 am UTC

sorry, never have used procedure builder. don't know what error "201" is.

Sequence Problem

January 08, 2005 - 4:11 pm UTC

Reviewer: Binh from Iowa USA

Hi Tom

I tried a simple program in SQL*Plus but it gave me an error like that:

SQL> SELECT inv_id_sequence.NEXTVAL
  2  INTO :inventory.inv_id
  3  FROM DUAL;
SELECT inv_id_sequence.NEXTVAL
*
ERROR at line 1:
ORA-01008: not all variables bound

Would you please let me know what is the problem is that? 

Thanks 

Binh Nguyen Van 

Tom Kyte

Followup  

January 08, 2005 - 5:03 pm UTC

with sqlplus, only INPUT binds are accepted in SQL statements. INPUT and OUTPUT binds in plsql. (select ... INTO ... is not sql, the into is a nicety provided by plsql or pro*c)

you would have to:

begin
select s.nextval into :bv from dual;
end;
/

will work, or

exec select s.nextval into :bv from dual;

as that is basically a short cut for begin .... end;



sqlplus and bind variables in 10g

January 09, 2005 - 10:53 am UTC

Reviewer: Alberto Dell'Era from Milan, Italy

> SQL> SELECT inv_id_sequence.NEXTVAL
>  2  INTO :inventory.inv_id
>  3  FROM DUAL;
> SELECT inv_id_sequence.NEXTVAL
> *
> ERROR at line 1:
> ORA-01008: not all variables bound

Small quirk - at least in 10g, sqlplus will accept "into":

tr10046@ORACLE10> select 1,2 into :x,:y from dual;

         1          2
---------- ----------
         1          2

But actually "into ..." is simply ignored:

tr10046@ORACLE10> variable x number
tr10046@ORACLE10> exec :x := 42;

PL/SQL procedure successfully completed.

tr10046@ORACLE10> select 1 into :x from dual;

         1
----------
         1

tr10046@ORACLE10> print x

         X
----------
        42

It is probably there to allow pl/sql power-users to drag&drop static statements into sqlplus more easily ;) 

Sequence Problem

January 09, 2005 - 9:48 pm UTC

Reviewer: Binh Nguyen Van

Hi Tom
Thank you very much, it is very helpful from your help.

Thanks

Binh Nguyen Van

Oracle 8.1.7.3/8.1.7.4 bug with "alter table x move"

September 30, 2005 - 4:38 pm UTC

Reviewer: Rory from Nebraska, USA

Just ran into this with a large, partitioned table.

As per Metalink Note 2159936.8
This problem is introduced in 8174.
ALTER TABLE SET UNUSED followed by operations such as
SPLIT PARTITION, MOVE TABLE etc.. can lead to data
corruption or a dump.

So, while I agree that "set unused"/"alter table move" is the best way, try at your own risk on 8i.

My only symptom was a different field suddenly went NULL. Users barely caught it before it went into production.

Moving of table

November 06, 2005 - 7:53 am UTC

Reviewer: Ram from India

Hi Tom,
Can u please explain what exactly happens with

alter table <table_name) move;

Regards,
Ram


Tom Kyte

Followup  

November 06, 2005 - 8:34 am UTC

"U" isn't here (I have yet to meet them! I am eager to meet them based on the numerous requests I get though...). So, I'll do it.


That is a "reorganization", it rebuilds the table, optitionally with new storage parameters and other attributes.


it is sort of equivalent to:

create table new_table as select * from table_name;
drop table table_name;
rename new_table to table_name;

but keeps the index definitions in place (they must be rebuilt after this) and the grants and so on.

Dropping a particular value

November 06, 2005 - 8:05 am UTC

Reviewer: Kumar from India

Hi Tom,
Is it possible to drop only a particular field in a table?
I have table x which has 2 columns, say, a primary key, and b. I would like to delete a particular value of b in table x

select * from x;

a b
--- ---
1 abc
2 def
3 ghi
10 xyz

I would like to delete only value xyz, is it possible?


Tom Kyte

Followup  

November 06, 2005 - 8:51 am UTC

I don't know what you mean by "delete a particular value"

you delete rows:

delete from t where b = 'xyz';


you can remove the value of a attribute:

update t set b = null where b = 'xyz';


it is likely one of those two things you are asking for.

Deleting a value

November 07, 2005 - 7:18 am UTC

Reviewer: Kumar from India

I actually wanted to delete only that value. I had this option of setting it to null, but, wanted to know, if it was possible through delete command.
Anyway, thanks.


Tom Kyte

Followup  

November 07, 2005 - 10:36 am UTC

the concept of "delete only that value" does not exist, not sure what you even mean by that.

January 05, 2006 - 7:28 am UTC

Reviewer: Adam Hudspith from United Kingdom

Hi Tom,

We have a product supplied by Quest Software (Space Manager) that we use to reorganize one of our OLTP databases online.

We have recently upgraded Space Manager (SM) to the latest version. A new (non configurable option) of the latest version is to issue this as the last command:

ALTER TABLE RETAIL.HOF_PPI_XML_PRICES DROP UNUSED COLUMNS;

The way SM works is to …. Create a copy table from the original … create dependant objects on that table … hold the table in sync with the original (post row changes) … rename them both … drop the original … recompile dependencies.

Given this … if the original table had an unused column it wouldn’t be created in the new table in the first place … so I am having difficulty understanding why this statement is being executed.

Under normal circumstances I would ignore the statement as it has no affect … however on larger tables it is locking up the table for unacceptably long periods of time whilst it does (apparently) nothing.

Do you agree with me that in this case the DROP UNUSED COLUMNS statement is redundant and I should get onto Quest to remove this ‘feature’?

Thanks in advance.

Adam

Tom Kyte

Followup  

January 05, 2006 - 10:52 am UTC

you could just use the "for free" dbms_redefinition that comes with the enterprise database.


And you would really have to ask Quest to answer why they do what they do. Likely they are using ROWIDS to manually sync the tables and they are dropping the rowid column they have added.

Just like we do in dbms_redefinition if you attempt to online redefine a table without a primary key.

Estimating amount of space recovered

January 24, 2006 - 4:12 pm UTC

Reviewer: Kevin Muhm from St. Louis, MO USA

Is there any way to estimate how much space may be reclaimed by the "alter column set unused/alter table move" method discussed earlier?

Thanks,

Tom Kyte

Followup  

January 24, 2006 - 9:00 pm UTC

you might be able to get a "swag" by taking

select sum(vsize(column)) from t;

before doing it - or just having a good idea of the average size of the column and the number of non-null entries.

Drop Column

January 27, 2007 - 12:02 pm UTC

Reviewer: Hitesh Bajaj from INDIA

Hi tom,

We have two partitioned tables which are huge and have sizes of 263 and 158G.

We have set unused some of the columns from both tables and now we need to get some free space on the disk for new rows to get inserted on the blocks available on the free lists.

My questions:

1. should I go in for
alter table move
OR
alter table drop unused columns
alter table drop unused columns checkpoint 1000

which would be faster and generate less undo/redo.

2.while the operation is going on will it effects any new inserts/updates/deletes happening on the table. Just for precaution measure I am asking this question.

Thanks

Drop column effects

February 04, 2007 - 6:20 am UTC

Reviewer: Hitesh Bajaj from INDIA

Hi tom,

Can you please answer the above question.

Much appreciated.

Thanks