enable row movement in 9i
raul, February 20, 2005 - 3:30 pm UTC
I was under the impression that 'enable row movement' is a feature meant for partitioned tables.
I just learnt it could be used for ordinary non-partitioned tables as well.
From the SQL reference manual:
<<
row_movement_clause
The row_movement_clause lets you specify whether Oracle can move a table row. It is possible for a row to move, for example, during data segment compression or an update operation on partitioned data.
>>
Can you explain where and for achieving what "row movement" could be used in a non-partitioned table?
Thanks.
February 20, 2005 - 4:28 pm UTC
10g -- alter table shrink... flashback table .....
more and more operations in the future will be moving rows, it was initially introduced for partitioning and is now being used for other things.
"index range scans to either be unchanged or better"
Connor, February 21, 2005 - 12:36 am UTC
Of course, if your range scan data happened to be nicely clustered *before* you shrink and those rows get splattered all the place into the free space holes after the shrink....well then, I don't think 'unchanged' or 'better' would be the appropriate terms :-)
February 21, 2005 - 10:06 am UTC
true, if you had densely packed blocks with lots of "pretty much empty blocks", and the densely packed blocks were organized by index key value -- spraying them about the top of the table could hurt the clustering factor...
but it would only be for one of the indices typically ;)
questions on enable row movement,
sns, November 17, 2005 - 7:48 am UTC
How do we know whether the table is created with ENABLE ROW MOVEMENT option?
Can we alter the existing table (both partitioned and non-partitioned) to have to ENABLE ROW MOVEMENT feature?
Is it necessary for the table to have ENABLE ROW MOVEMENT in order to perform alter table shrink?
Our ETL process deletes/inserts (they don't use merge which I wonder) millions of records each day and in the course of time the tablespace gets filled up. After rebuilding the table, most of the time the table become half or even less of its size.
In this situation, can we implement the alter tale shrink command right after the deletes are done? I know the tablespace should have ASSM feature.
Thanks,
November 17, 2005 - 8:21 am UTC
why don't they truncate and insert /*+ append */ ?
select row_movement from user_tables;
cont....
sns, November 17, 2005 - 10:25 am UTC
They can't. The delete does not "deletes" all the records (may be million rows out of 100 million rows).
Could you please answer my other questions?
Thanks,
November 18, 2005 - 7:25 am UTC
yes you can use shrink/shrink compact
Alexander the ok, November 07, 2006 - 1:24 pm UTC
Tom,
I can't find anywhere what enabling row movement does, or why you need it to use flashback table. Could you explain briefly please? Thanks.
November 07, 2006 - 4:45 pm UTC
well, it basically enabled rows to move (eg: rowids to change).
we use it when you update a partition key and the row MOVES from partition 1 to partition 2.
we use it when you alter table t shrink space compact, since we MOVE rows from the "bottom" of the table to the "top"
We use it in flashback table because we achieve that by:
a) deleting all rows modified or inserted since the time you want to flashback to
b) inserting all rows modified or deleted since the time you want to flashback to
so, if the KING record in EMP was updated - we delete and insert it - it'll have a new rowid, it "moved"
What runs the shrink?
Peter Mahaffey, November 22, 2006 - 10:57 am UTC
As far as it goes I am very happy with this answer. However, ther is one bit missing. What process actually runs the shrink process. Is it run in background. Under simple testing it appears to be an almost instant fix, which I cannot beleive unless it is being run in background by SMON maybe?
If this is the case, would there be an impact when running his command on a number of tables? Actually, I think I know the obvious answer - yes. Any idea what sort of impact?
Oh. if you read this - thanks for a good presentation at the UKOUG.
November 24, 2006 - 12:41 pm UTC
the shrink is run in your server process.
do it on a big table, it can take some amount of time.
A reader, November 28, 2008 - 7:29 am UTC
To what scale update to a partition key column would affect the performance in datawarehouse environment.
November 28, 2008 - 5:32 pm UTC
anywhere from 0% to more than 0%.
Not sure what you mean - or why you think it would affect the performance. There are many many cases to consider.
Say I just did an update of the partition key to itself plus 1.
And no rows moved.
And no rows migrated or chained.
Then, the impact would be about 0%.
Now, say they ALL migrated. The impact would be about 0% for full scans and a lot more for a index read.
And so on, you'd have to tell us what you think would happen to the data after the update - why the update of a partition key causes you concern in this case.
one more question on row movement
Jose Canciani, February 19, 2009 - 9:03 am UTC
Hello Tom.
I wonder if you don't use any of the processes that "move" data, is there any penalty on having the table with row_movement enable during normal operation? Maybe I'm missing something, but what would be the purpose of having it "disabled"? Or is this one of those things that were inherit from previous versions?
Thank you!
Jose.
Still not clear
Anil, March 08, 2009 - 4:02 pm UTC
Hello Tom,
In response to one o question you have said,
<<Start>>
well, it basically enabled rows to move (eg: rowids to change).
we use it when you update a partition key and the row MOVES from partition 1 to partition 2.
we use it when you alter table t shrink space compact, since we MOVE rows from the "bottom" of the
table to the "top"
We use it in flashback table because we achieve that by:
a) deleting all rows modified or inserted since the time you want to flashback to
b) inserting all rows modified or deleted since the time you want to flashback to
so, if the KING record in EMP was updated - we delete and insert it - it'll have a new rowid, it
"moved"
<<End>>
I'm not clear as why we need the row enable movement, when we are deleting and inserting the row? As such when we do the entire physical addrees of the row changes.
Also is any performance problem if I enable the row enable movement for a table and never use one of the method.
Thanks
March 08, 2009 - 5:10 pm UTC
the delete+insert is IMPLICIT
*you* do not delete+insert, *you* update a row, and that causes us to delete+insert and change the rowid.
In the past (forever, before enable row movement) a rowid was assigned when you were inserted and never ever changed. Now, a simple update could change your rowid. We make you give us permission to do that.
If you never actually cause a row to be moved, then there is zero overhead. All enable row movmement does is give us permission to move a row, if you don't ask us to move the row - nothing extra "happens"
Keeping "row movement" enabled by default
Logan Palanisamy, March 09, 2009 - 11:40 pm UTC
Tom,
Is there anything wrong or any overhead in having "row movement" enabled for many of our tables? Sometimes we delete as much as 50% of the rows from many of our tables over a period of time, and we would like to lower the HWM periodically using "shrink space".
Since we don't know which tables might need this "compaction" at the time of creation, we like to create them with "row movement" enabled.
Just want to see if there is anything wrong with this approach.
March 10, 2009 - 2:47 am UTC
Is there anything wrong? Only you can answer that, do you have any application that expects rowid to be constant for a row? If so - then it is wrong, if not - then it is likely OK.
Any overhead? No, it is just permission to change a rowid if you do an operation that would cause a rowid to change - like alter table t shrink space compact, flashback table t to..., update t set partition_key = some_value_that_moves_row;
You can alter a table instantly to enable row movement - regardless of table size - it'll be instantaneous - so you do not need to do it ahead of time if you don't want, easy enough to add later....
ENABLE ROW MOVEMENT and Flashback
Bob Watkins, March 10, 2009 - 9:24 am UTC
Hi, Tom.
In your followup to Logan today, you said:
"...you do not need to do it ahead of time if you don't want, easy enough to add later.... "
Can you clarify the effect of this on Flashback Table? I had thought that Flashback could only be done as far back as your Undo Tablespace size permits, or until the last DDL on the table, whichever is shortest. So, waiting until a problem occurred that warranted Flashback Table wouldn't work, because you couldn't get back behind your recent ALTER TABLE x ENABLE ROW MOVEMENT to access prior values.
In that case it seems to me that the ENABLE ROW MOVEMENT should have been in place for some time.
March 11, 2009 - 2:39 am UTC
enable row movement is simply *PERMISSION* to move a row.
It doesn't matter if that permission has been in place for 5 years, or 5 milliseconds. It just needs to be in place.
You can
a) flashback table - get the "sorry, row movement not enabled"
b) enable row movement
c) flashback table - success
without any loss of ability to flashback. This ALTER is not a DDL statement that prevents flashing back - only an alter that actually changes something (not all DDL prevents flashing back)
When I demonstrate this concept, my script includes:
flashback table emp to scn &S;
pause
clear screen
alter table emp enable row movement;
pause
clear screen
flashback table emp to scn &S;
pause
clear screen
I have it fail the first time, to give me something to frame a discussion around what "row movement is/does" and then enable it and then show the flashback working.
Thanks
Bob Watkins, March 18, 2009 - 11:58 am UTC
That really helped clarify the point for me, Tom. Thanks!
BobW
Is this a bug related to en/dis-abling row movement?
Paul Barrett, September 10, 2009 - 5:28 pm UTC
If a DML statement is being performed on a table and if you issue the "alter table <table name> enable/disable row movement;" statement on that table you will get the below error during the DML process:
Session A:
SQL> declare
2
3 begin
4
5 for i in 1..100000 loop
6 insert into paulb.tab2 (col1, col2)
7 values (to_char(i), i);
8 end loop;
9 rollback;
10 end;
11 /
In Session B while the PL/SQL block runs in Session A:
SQL> alter table PAULB.tab2 disable row movement;
alter table PAULB.tab2 disable row movement
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
Also, if you issue the following statement "alter table <table name> enable/disable row movement;" it will invalidate any stored procedure that references that table. But, once you reference that invalid stored procedure, Oracle recompiles it.
SQL> select owner, object_name, object_type, status
2 from dba_objects
3 where owner = 'PAULB';
OWNER OBJECT_NAME OBJECT_TYPE STATUS
---------- --------------- ------------------- -------
PAULB TAB1_IDX INDEX VALID
PAULB TAB1 TABLE VALID
PAULB TAB2 TABLE VALID
PAULB INSERT_TAB2 PROCEDURE VALID
SQL>
SQL>
SQL> select owner, table_name, row_movement
2 from all_tables
3 where owner = 'PAULB';
OWNER TABLE_NAME ROW_MOVE
---------- ------------------------------ --------
PAULB TAB1 ENABLED
PAULB TAB2 DISABLED
SQL>
SQL>
SQL> alter table PAULB.tab2 enable row movement;
SQL> select owner, table_name, row_movement
2 from all_tables
3 where owner = 'PAULB';
OWNER TABLE_NAME ROW_MOVE
---------- ------------------------------ --------
PAULB TAB1 ENABLED
PAULB TAB2 ENABLED
SQL> select owner, object_name, object_type, status
2 from dba_objects
3 where owner = 'PAULB';
OWNER OBJECT_NAME OBJECT_TYPE STATUS
---------- --------------- ------------------- -------
PAULB TAB1_IDX INDEX VALID
PAULB TAB1 TABLE VALID
PAULB TAB2 TABLE VALID
PAULB INSERT_TAB2 PROCEDURE INVALID
SQL>
SQL> desc paulb.insert_tab2
PROCEDURE paulb.insert_tab2
SQL> select owner, object_name, object_type, status
2 from dba_objects
3 where owner = 'PAULB';
OWNER OBJECT_NAME OBJECT_TYPE STATUS
---------- --------------- ------------------- -------
PAULB TAB1_IDX INDEX VALID
PAULB TAB1 TABLE VALID
PAULB TAB2 TABLE VALID
PAULB INSERT_TAB2 PROCEDURE VALID
SQL>
It is best that after enabling or disabling table row movement that utlrp.sql is run to recompile all invalid objects.
The enabling and disabling of row movement is not causing a structural change to the table, so why does it invalidates the dependent stored procedures?
September 14, 2009 - 11:44 am UTC
I see no bug here - DDL and DML are pretty much "NOT compatible"
I have never understood the amount of time, energy and fear people expend with invalid procedures. As you noted, they "fix" themselves. So no, I would not worry about it.
Dependencies were not very fine grained in the past, they have gotten so in current releases
ops$tkyte%ORA11GR1> create table t ( x int );
Table created.
ops$tkyte%ORA11GR1> create or replace procedure p
2 as
3 begin
4 for x in (select * from t) loop null; end loop;
5 end;
6 /
Procedure created.
ops$tkyte%ORA11GR1> select status from user_objects where object_name = 'P';
STATUS
-------
VALID
ops$tkyte%ORA11GR1> alter table t enable row movement;
Table altered.
ops$tkyte%ORA11GR1> select status from user_objects where object_name = 'P';
STATUS
-------
VALID
Oracle invalidates packages while enabling/disabling row movement in 10g but fixed in 11g
Peter, September 15, 2009 - 3:22 pm UTC
Tom,
When I ran the above test prior to your reply above on Oracle 10.2.0.3, I get the same result where Oracle invalidates the stored procedures that references the table that had row movement enable/disabled. But when I perform the test on Oracle 11g it does not invalidate the stored procedures. So would you say this is a enhancement or bug fix?
We have experienced this feature before, where after enabling/disabling row movement on a table it invalidated couple of our stored procedures and Oracle wasn't able to recompile them and our application started to fail. I was told it had something to do with BEA Weblogic and the stored procedures.
Good to see it is better in 11g.
September 15, 2009 - 4:30 pm UTC
... So would you say this
is a enhancement or bug fix?
...
enhancement - dependency tracking has been going further and further down in detail with the releases. 11g added a lot in that way.
... and Oracle
wasn't able to recompile them and our application started to fail. I was told
it had something to do with BEA Weblogic and the stored procedures.
...
no, Oracle was able to recompile them, BEA Weblogic and the specific feature you used within it (not sure what you were doing or using) didn't react properly. Automatic recompilation is a natural thing, happens very easily - it takes someone going out of their way to make it "apparent" like weblogic did.
About internal insert/delete of shrink opearation
Parthasarathi A, December 02, 2009 - 5:22 am UTC
I have a trigger (TYPE:BEFORE EACH ROW EVENT:INSERT) on a table. Now I am planning to shrink that table using ALTER TABLE .. SHRINK SPACE; command.
As this online shrink command does internel insert/delete operations , I want toknow whether this operation will invoke the trigger or not
December 04, 2009 - 8:01 am UTC
It will not, internal commands that move rows do not fire triggers like that.
for example:
update partitioned_table set partition_key = new_value_that_MOVES_the_row;
will actually delete and then insert the row to update it - but only the update trigger will fire, not insert, not delete.
alter table t shrink space compact will just move the rows, not fire any triggers
An exception to this 'rule' would be the flashback table command which *can* fire triggers if you want when it does its mass delete and insert.
Alter table <tablename> shrink releases space ?
A reader, March 01, 2010 - 3:02 pm UTC
Tom,
Does shrinking of a table releases space? I tried doing it. But it did not release any space.
SQL> insert into test select * from user_objects ;
6433 rows created.
SQL> /
6433 rows created.
SQL> commit ;
Commit complete.
SQL> insert into test select * from test ;
12866 rows created.
SQL> /
25732 rows created.
SQL> /
51464 rows created.
SQL> /
/
102928 rows created.
SQL>
205856 rows created.
SQL>
SQL> commit ;
Commit complete.
SQL> select sum(bytes/1024/1024) from dba_segments where segment_name='TEST';
SUM(BYTES/1024/1024)
--------------------
38.125
SQL>
SQL> delete from test ;
411712 rows deleted.
SQL> SQL> commit ;
Commit complete.
SQL> select sum(bytes/1024/1024) from dba_segments where segment_name='TEST';
SUM(BYTES/1024/1024)
--------------------
38.125
SQL> alter table test shrink space compact ;
Table altered.
SQL> select sum(bytes/1024/1024) from dba_segments where segment_name='TEST';
SUM(BYTES/1024/1024)
--------------------
38.125
-Ravi
March 02, 2010 - 7:17 am UTC
you did a shrink space compact, that just does the reorg part, puts all of the rows at the front of the table.
Now, alter table test shrink space;
A reader, June 03, 2010 - 4:03 pm UTC
Tom,
I was trying to implement flashback table on 11gr2 database. enable row movement has already been enabled on this table. Table has round 47000 rows. Rows are from all_objects.
The command
flashback table emp to scn &S;
is giving an error as shown below:
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-12801: error signaled in parallel query server P100
ORA-00018: maximum number of sessions exceeded
Also processes are hitting max limit. I can find that from v$process. Count of v$processes = 149. In our case process parameter is set to 150. V$session shows that there is only one user session. The 111 processes as shown by v$process seem to be parallel processes numbers P001 - P111 .
After a few minutes, processes will return to normal.
I just wanted to take your quick opinion. Can you Please let me know based on this information, why parallel procces are increasing beyond the limit here.
Thanks.
June 08, 2010 - 12:05 pm UTC
what are parallel max servers set to
A reader, June 08, 2010 - 2:41 pm UTC
Thanks Tom.
parallel_max_servers = 135 on this database.
and processes = 150. They are still the default values from the 11gR2 installation.
Processes count hit 149 and 111 of them were parallel processes P000 .. P 111.
I think, the parallel processes were trying to go all the way up to 135 (parallel_max_servers ) . process count from v$process was 149.
I believe because parallel_max_servers was set to 135, and processes was only set to 150, flashback could not fire up more parallel processes required for the operation, and it died. If that is true, what would be the best option, increase processes parameter to a large value or decrease parallel_max_servers to a non-default lesser value. Please let me know. Thanks.
June 10, 2010 - 11:16 am UTC
your belief is correct - your processes setting is too low for the parallel max servers you have (unless you are pretty much the only thing in the database - then it might be *just* enough)
either
a) increase processes, doubtful you want to do that
b) decrease max parallel servers to be about 4x the number of cpus (likely you want to do that)
c) not use parallel for this operation
How ROWID works in SELECT
Abhisek, July 16, 2010 - 4:01 am UTC
Hi Tom,
My problem is related with the common "SELECT columsn from Table" query.
In case my table table1 has five columns col1, col2,col3,col4,col5
Primary key as col1,col2,col3
now when I am doing select col3, rowid, col4,col5 from table1:
I am getting result where rows ordered by col3 and not by ROWID .. I thought it should order it by rowid if no order by clause is provided..
Can you please suggest how the rows are selected if we dont give any order by clause. I am unclear about it.
July 19, 2010 - 1:21 pm UTC
if there is no order by clause provided - you have NO REASON TO EXPECT THE ROWS IN ANY ORDER.
if you select col3, col4, col5, rowid from table and the primary key is col1, col2, col3 - I would expect a full scan of the table, I would not expect to see the index used AT ALL.
CHRIS K HARGREAVES, January 22, 2011 - 12:58 am UTC
CONN HR/HR
CREATE TABLE TEST
TABLESPACE USERS
PCTFREE 80
AS
SELECT * FROM HR.EMPLOYEES;
ALTER TABLE TEST
enable row movement;
ALTER TABLE TEST
shrink space;
INCREDIBLE !!!!! IT DOES NOTHING
February 01, 2011 - 9:43 am UTC
what would you expect it to do???????
come on - give us a clue. Do you expect it to magically make a full table smaller?
shrinking space will reorganize a table - if the table isn't in need of reorganization because it is already well packed - what do you expect it to do???
@Chris : complements about space and SHRINKing
Jose Laurindo Chiappa, February 01, 2011 - 1:41 pm UTC
Chris, your basic misconception was the supposition that SHRINK means to release all and any unused space and no, it is not what it does - space not used due to YOUR demand, space reserved EXPLICITLY for you, is not released : when not using the Automatic space options, Oracle will "think" that you know what you are doing, it will honour your will.... When you explicitly commanded "reserve 80% of my block space" it will obey, if no other options in contrary exists... See (I will use a table with more lines to get more than one extent, and sorry about the system messages in Portuguese, but they are irrelevant to the case in point anyway) :
HR@o10gr2:SQL>select block_size from user_tablespaces where tablespace_name='USERS';
BLOCK_SIZE
----------
8192
HR@o10gr2:SQL>CREATE TABLE TEST_BIG TABLESPACE USERS PCTFREE 80 AS (SELECT * FROM ALL_OBJECTS);
Tabela criada.
HR@o10gr2:SQL>ANALYZE TABLE TEST_BIG COMPUTE STATISTICS;
Tabela analisada.
==> See below, the row of data is only @98 bytes , and the block size is 8192 bytes, so you would expect dozens of rows in each block, BUT no, we got only 15 rows ... Why ? Why you COMMANDED it by specifying PCTFREE, so Oracle obeys, and SHRINK will NOT consider this wastage - remember, the bd will suppose that you Know what you are doing, will honor your choice...
HR@o10gr2:SQL>SELECT AVG_ROW_LEN, NUM_ROWS,BLOCKS,NUM_ROWS/BLOCKS ROWS_BY_BLOCK,PCT_FREE, PCT_USED FROM USER_TABLES WHERE TABLE_NAME='TEST_BIG';
AVG_ROW_LEN NUM_ROWS BLOCKS ROWS_BY_BLOCK PCT_FREE PCT_USED
----------- -------- ------ ------------------ -------- --------
98 41215 2711 15,202877167097012 80
HR@o10gr2:SQL>ALTER TABLE TEST_BIG ENABLE ROW MOVEMENT;
Tabela alterada.
HR@o10gr2:SQL>ALTER TABLE TEST_BIG SHRINK SPACE;
Tabela alterada.
HR@o10gr2:SQL>ANALYZE TABLE TEST_BIG COMPUTE STATISTICS;
Tabela analisada.
HR@o10gr2:SQL>SELECT AVG_ROW_LEN, NUM_ROWS,BLOCKS,NUM_ROWS/BLOCKS ROWS_BY_BLOCK,PCT_FREE, PCT_USED FROM USER_TABLES WHERE TABLE_NAME='TEST_BIG';
AVG_ROW_LEN NUM_ROWS BLOCKS ROWS_BY_BLOCK PCT_FREE PCT_USED
----------- -------- ------ ------------------ --------- ---------
98 41215 2711 15,202877167097012 80
==> See ? You commanded 80%, 80% it stays... Let´s change it :
HR@o10gr2:SQL>ALTER TABLE TEST_BIG MOVE TABLESPACE USERS PCTFREE 1;
Tabela alterada.
HR@o10gr2:SQL>ANALYZE TABLE TEST_BIG COMPUTE STATISTICS;
Tabela analisada.
HR@o10gr2:SQL>SELECT AVG_ROW_LEN, NUM_ROWS,BLOCKS,NUM_ROWS/BLOCKS ROWS_BY_BLOCK,PCT_FREE, PCT_USED FROM USER_TABLES WHERE TABLE_NAME='TEST_BIG';
AVG_ROW_LEN NUM_ROWS BLOCKS ROWS_BY_BLOCK PCT_FREE PCT_USED
----------- -------- ------ ------------------ -------- --------
98 41215 540 76,324074074074074 1
==> ok ? See that the PCTFREE will continue to be respected, see the new SHRINK attempt not "working" :
HR@o10gr2:SQL>ALTER TABLE TEST_BIG SHRINK SPACE;
Tabela alterada.
HR@o10gr2:SQL>SELECT AVG_ROW_LEN, NUM_ROWS,BLOCKS,NUM_ROWS/BLOCKS ROWS_BY_BLOCK,PCT_FREE, PCT_USED FROM USER_TABLES WHERE TABLE_NAME='TEST_BIG';
AVG_ROW_LEN NUM_ROWS BLOCKS ROWS_BY_BLOCK PCT_FREE PCT_USED
----------- -------- ------ ------------------ -------- --------
98 41215 540 76,324074074074074 1
==> So now you would ask, What kind of present but not used space will be released ? One example is space not in use reserved due to DELETEs, you DO NOT demmanded this space to be here, so SHRINK will release it if possible, example :
HR@o10gr2:SQL>DELETE FROM TEST_BIG WHERE ROWNUM < 20000;
19999 linhas deletadas.
HR@o10gr2:SQL>COMMIT;
Commit concluído.
HR@o10gr2:SQL>ANALYZE TABLE TEST_BIG COMPUTE STATISTICS;
Tabela analisada.
==> See, the space from DELETEs is (IMPLICITLY) reserved but not used , this is the expected behavior, lesser rows are "occupying" the Same 540 blocks :
HR@o10gr2:SQL>SELECT AVG_ROW_LEN, NUM_ROWS,BLOCKS,NUM_ROWS/BLOCKS ROWS_BY_BLOCK,PCT_FREE, PCT_USED FROM USER_TABLES WHERE TABLE_NAME='TEST_BIG';
AVG_ROW_LEN NUM_ROWS BLOCKS ROWS_BY_BLOCK PCT_FREE PCT_USED
----------- -------- ------ ------------------ -------- --------
99 21216 540 39,288888888888889 1
==> Let´s see action from SHRINK :
HR@o10gr2:SQL>ALTER TABLE TEST_BIG SHRINK SPACE;
Tabela alterada.
HR@o10gr2:SQL>ANALYZE TABLE TEST_BIG COMPUTE STATISTICS;
Tabela analisada.
HR@o10gr2:SQL>SELECT AVG_ROW_LEN, NUM_ROWS,BLOCKS,NUM_ROWS/BLOCKS ROWS_BY_BLOCK,PCT_FREE, PCT_USED FROM USER_TABLES WHERE TABLE_NAME='TEST_BIG';
AVG_ROW_LEN NUM_ROWS BLOCKS ROWS_BY_BLOCK PCT_FREE PCT_USED
----------- -------- ------ ------------------ -------- --------
99 21216 273 77,714285714285714 1
ok ?
Regards,
J. Laurindo Chiappa
Oracle DBA
ORA-00054: resource busy and acquire with NOWAIT specified in oracle 9i
Karthick, July 24, 2011 - 6:50 pm UTC
Just kill the process whatever running behind the oracle logs and reinitiate the session.
while using linux just give the following commands:
ps -a
kill -9 (oracle process id)
then reinitiate the session it will work...
July 27, 2011 - 7:56 pm UTC
umm, no, do not do that.
You do NOT want to kill arbitrary Oracle processes.
And what if the person doing the thing that is blocking you - SHOULD be doing that then. Killing them would be rather rude don't you think.
I'm sort of a fan of figuring out "what the heck is going on and reacting in an appropriate fashion based on that", rather than "shoot first, ask questions later"
A reader, November 01, 2011 - 3:16 am UTC
Per my understanding, 'move' is to solve the 'row chain' issue or we want to change table to another tablespace, while 'shrink' is to free the unused space and re-adjust the HWM to speed up full table scan.
But i am curious:
1> why 'shrink' does not invalidate associated indexes while 'move' does?
2> why only 'shrink' need 'alter table tname enable row movement'?
Per my knowledge:
1> rowid is constructed as file_number+block_number+location_in_the_block+object_number(mainly for cluster tables)
2> any operation result in rowid change will invalidate index as index is a map between rowid and index column value
I am thinking both of them will do row move and result in rowid change:
Move: it is even possible to move to other tablespace, of course data block changed, why no need to enable row movement?
Shrink: suppose after a heavy OLTP DML operation, there are only 2 rows left, one is on block 1 another is on block 2, and one block is totally fit for holding 2 rows whithout exceed the
pctfree, then after shrink, they should be in same block, then block_number part of rowid change, why not invalidate indexes?
November 01, 2011 - 3:45 am UTC
1) alter table t move performs a full scan of the entire segment and creates a brand new segment using direct path writes (no buffer cache, not an insert) in completely different blocks - when it has completed the full scan - it drops the old segment and renames this new temporary segment.
hence, every rowid for every row in the table is changed - and the alter table t mve - since it is not deleting the rows and reinserting them - it is doing a direct path load - it does not maintain the indexes. So, every index would need to be rebuilt since the rowids they contain point to the old segment that doesn't exist anymore.
Alter table t shrink on the other hand reads the table from the bottom up (like a reverse full scan) and when it finds a row - it deletes that row and does a positioned insert of the row at the top of the table. So, it does a delete + positioned insert - which does maintain the indexes.
Alter table t move was designed to be an offline operation. Alter table t shrink was designed to be an online operation. If you want to effects of alter table t move in an online fashion - you would use dbms_redefinition.
2) because alter table t shrink actually does move the row in the same table. Alter table t move is a complete reorganization - you get an entirely new segment with a new data_object_id and all. MOVE is not doing a delete+insert, it is doing a complete and utter rebuild of *everything*. it does not move rows in an existing segment, it builds a brand spanking new segment.
Considerations
Tim, November 01, 2011 - 11:52 am UTC
What things should we be considering when deciding on using ALTER TABLE SHRINK vs. the CREATE TABLE AS SELECT/RENAME options?
If we can afford to do the CREATE/RENAME method, are there any advantages to using ALTER TABLE SHRINK instead?
November 02, 2011 - 4:06 am UTC
alter table shrink:
o online operation
o works in place (no extra storage)
o generates redo and undo for the delete/insert of the row and index maintenance
create/rename:
o offline operation
o needs 2x the storage
o skips undo, can skip redo if you want
o you need to create indexes, grants, constraints, etc, etc, etc
better then create/rename "alter table t move"
o offline
o needs 2x the storage
o skips undo, can skip redo if you want
o you just need to rebuild the indexes (which are all listed in the dictionary) afterwards
Now you know the differences - you can apply them to your circumstances. If you have a maintenance window - alter table t move, alter index i rebuild might be appropriate. If you have continuous availability as a goal - alter table t shrink.
A reader, March 06, 2012 - 4:37 am UTC
Hi Tom
does the shrink function effect on partion table in 11g
March 06, 2012 - 7:12 am UTC
Why does shrink impact performance while shrink is running?
Otown DBA, May 18, 2012 - 11:01 am UTC
Hi Tom,
I'm hoping you might be able to help me with this issue. Unfortunately, its an anecdotal one, so you may not like it, but its all I have.
I wanted to run some shrinks, but both another DBA and our technical user interface report that they tried it, but got immediate response from the users who complained about performance going to heck the second they kicked them off.
The shrinks were going against MLOG$ indexes, not sure if that means anything.
It left such a lasting impact that we are not allowed to run shrinks anymore except during maintenance outages.
Any help or explanations would be most appreciated. Realize if you don't respond, since I ca't offer any evidennce.
May 21, 2012 - 8:07 am UTC
but got immediate response from the users
who complained about performance going to heck the second they kicked them off.
could easily happen, sure.
If you are at or near capacity on a machine and you do something that
a) consumes more cpu
b) consumes a large amount of read IO
c) consumes a large amount of write IO
it could have a measurable immediately impact on existing response times.
Meaning, you just took the machine over the edge.
also, the shrink command - the one that releases space (not just compact) - will lock the table for a period of time to reset the high water mark. On a busy transactional system - that could be a problem.
Which features hurt from row movement enabled on a table?
Merk, July 09, 2012 - 10:58 am UTC
Hi Tom
Can you please list the features that may not work properly once row movement is enabled on a table?
The only ones we can think of is select ... where rowid = '...' and maybe materialized views based on rowids.
We plan to create a job which will automatically shrink the tables with large amounts of free space. But what do we need to look out for?
Best regards,
Merk
July 11, 2012 - 1:14 pm UTC
only things that fetch and store a rowid.
*store* a rowid, not just fetch it and use it.
So, if you do not store them - the only thing that may happen is an update may not be able to find a row.
For example, if you select the rowid out and hold it in your session and later go to update by rowid (and primary key!! and primary key!!! you cannot just update by rowid - if you do that, you could end up updating the wrong row), then if the row moves, you might not find it again.
ENABLE ROW MOVEMENT
Prabhat K Sharma, July 23, 2012 - 4:11 pm UTC
Hi Tom,
I have this question related to ENABLE_ROW_MOVEMENT. We have a partitioned table which is partitioned on a date column. For every new calendar year, a new partition is created. At present we have partitions for 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, and 2013. Each of these partition has its own tablespace. No tow partition share the same tablespace.
We have a process which deletes substantial number of rows (2 millions) rows from the partitions 2004, and 2005. Will this cause any changes in the the rowids of the existing rows in the tablespaces 2011 and 2012? Table is of Automatic Segment Space Managment type.
Thanks,
Prabhat
July 30, 2012 - 9:04 am UTC
no, rowids only change when rows move.
if you delete rows, they do not move, they disappear.
deleting 2,000,000 rows might want to be reconsidered. It might be much more efficient to create a table as select <rows to keep> from a partition - index that, and then exchange it in - rather than deleting.
Prabaht K Sharma, August 08, 2012 - 9:59 am UTC
Hi Tom,
I had asked you the following question and the response to your given below the question in bold.
However my question was not about the rows which are deleted but about the rows which are in the other partitions. Again, the question is, will the row ids of the rows in partitions 2011 and 2012 (which are in tablespaces 2011 and 2012) change if we delete about 2 millions of rows in partitions 2004 and 2005 (which are in tablespaces 2004 and 2005)? My understanding is during normal delete the row ids will not change even if the row movement is enabled. But I could be wrong and I request your help to understand it better.
Thanks,
Hi Tom,
I have this question related to ENABLE_ROW_MOVEMENT. We have a partitioned table which is
partitioned on a date column. For every new calendar year, a new partition is created. At present
we have partitions for 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, and 2013. Each of
these partition has its own tablespace. No tow partition share the same tablespace.
We have a process which deletes substantial number of rows (2 millions) rows from the partitions
2004, and 2005. Will this cause any changes in the the rowids of the existing rows in the
tablespaces 2011 and 2012? Table is of Automatic Segment Space Managment type.
Thanks,
Prabhat
Followup July 30, 2012 - 9am Central time zone:
no, rowids only change when rows move.
if you delete rows, they do not move, they disappear.
deleting 2,000,000 rows might want to be reconsidered. It might be much more efficient to create a table as select <rows to keep> from a partition - index that, and then exchange it in - rather than deleting.
August 17, 2012 - 1:18 pm UTC
a delete will not cause a rowid to change.
I answered that already - did you not see it:
no, rowids only change when rows move.
you asked "will they change" and I said "no" - just no.
I went on further to say "rowids only change when rows move" and "if you delete, the DO NOT move"
furthering the answer of NO.
Enable row movement and shrink
Prabu, September 12, 2014 - 9:47 am UTC
Hi Tom,
Can this enable row movement and shrink table be done for one partition after another?
alter table <table_name> enable row movement;
alter table <table_name> shrink space compact;
alter table <table_name> shrink space;
We have table with around 600GB size and want to shrink it since there were many deletes performed but never the shrink table was done. Now we want to do it partition by partition to reduce resource usage. Could you please provide your suggestion?