impact to RBS and redo
an, April 04, 2002 - 4:21 pm UTC
Hi Tom:
what different between those two alter command will impact rbs and redo?
thanks,
an
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.
Parag, April 05, 2002 - 1:04 am UTC
Can you be more Precise with your answere Tom.
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
Reddy, April 05, 2002 - 6:11 am UTC
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
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
Reddy, April 06, 2002 - 11:52 pm UTC
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
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
Adrian Billington, April 08, 2002 - 4:49 am UTC
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
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
Adrian Billington, April 08, 2002 - 9:39 am UTC
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
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
A reader, April 08, 2002 - 11:03 am UTC
Tom
None taken merely a healthy exhange of opinions ;)
Adrian
Reader
A reader, July 08, 2002 - 5:14 am UTC
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
A reader, July 08, 2002 - 5:17 am UTC
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
A reader, July 08, 2002 - 5:21 am UTC
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
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
A reader, July 08, 2002 - 8:45 am UTC
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
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
mo, February 10, 2003 - 1:14 pm UTC
Tom:
Is there a way to drop multiple columns in SQL statement or you have to do one column at a time?
February 11, 2003 - 7:42 am UTC
when you checked the docs for ALTER TABLE -- what did they tell you?
Drop column
A reader, December 14, 2003 - 12:14 am UTC
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.
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
A reader, December 14, 2003 - 11:37 am UTC
<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
December 14, 2003 - 12:27 pm UTC
yes.
Drop column
Houston, December 14, 2003 - 11:58 am UTC
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
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
A reader, December 14, 2003 - 9:21 pm UTC
Thank you for making the concept clear
what if the column to be dropped is a clob
A reader, December 19, 2003 - 8:44 am UTC
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
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
A reader, December 21, 2003 - 12:41 am UTC
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
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
Arun Gupta, June 14, 2004 - 5:36 pm UTC
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
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
Jeff, June 15, 2004 - 5:02 pm UTC
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
Laxman Kondal, October 28, 2004 - 12:38 pm UTC
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.
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
Binh Nguyen Van, December 26, 2004 - 1:06 am UTC
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
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
Binh Nguyen Van, December 26, 2004 - 9:56 pm UTC
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
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
A reader, December 28, 2004 - 4:02 pm UTC
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
Kishor Bhalwankar, December 31, 2004 - 8:56 am UTC
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"
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
A reader, December 31, 2004 - 2:12 pm UTC
Have a happy new year :)
Create Procedure in Oracle Procedure Builder
Binh Nguyen Van, January 03, 2005 - 1:22 am UTC
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
January 03, 2005 - 8:27 am UTC
sorry, never have used procedure builder. don't know what error "201" is.
Sequence Problem
Binh, January 08, 2005 - 4:11 pm UTC
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
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
Alberto Dell'Era, January 09, 2005 - 10:53 am UTC
> 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
Binh Nguyen Van, January 09, 2005 - 9:48 pm UTC
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"
Rory, September 30, 2005 - 4:38 pm UTC
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
Ram, November 06, 2005 - 7:53 am UTC
Hi Tom,
Can u please explain what exactly happens with
alter table <table_name) move;
Regards,
Ram
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
Kumar, November 06, 2005 - 8:05 am UTC
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?
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
Kumar, November 07, 2005 - 7:18 am UTC
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.
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.
Adam Hudspith, January 05, 2006 - 7:28 am UTC
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 wouldnt 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
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
Kevin Muhm, January 24, 2006 - 4:12 pm UTC
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,
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
Hitesh Bajaj, January 27, 2007 - 12:02 pm UTC
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
Hitesh Bajaj, February 04, 2007 - 6:20 am UTC
Hi tom,
Can you please answer the above question.
Much appreciated.
Thanks