Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Ram.

Asked: August 01, 2017 - 7:41 am UTC

Last updated: August 04, 2017 - 1:35 am UTC

Version: >=11.2.0.3

Viewed 1000+ times

You Asked

Hi,

1) Can you please explain the internals of a table move within the tablespace using the below command ? Trying to understand how exactly it happens at the block level.

alter table 'TABLE NAME' move;

2) Do we need to add extra space to the tablespace during the move ? If yes, why ?

3) Why archive generation is high during the move ? Have observed this.



and Chris said...

1. Doing this moves the blocks to a new segment. You can see this by seeing the rowids change before and after the move:

create table t as
  select rownum x from dual connect by level <= 1000;

select min(rowid), max(rowid) from t;

MIN(ROWID)          MAX(ROWID)          
AAAcpaAABAAARTfAAA  AAAcpaAABAAARTyAFV  

alter table t move;

select min(rowid), max(rowid) from t;

MIN(ROWID)          MAX(ROWID)          
AAAcpbAABAAART1AAA  AAAcpbAABAAART2AFV  


2. Maybe. You're moving the data to a new location, so for a period of time you may need enough space to store two copies of the table:

https://asktom.oracle.com/pls/apex/f?p=100:11:::NO:RP:P11_QUESTION_ID:9527343800346989243

3. Amongst other things, when you move the rows to a new segment Oracle Database has to update all the associated indexes to point to the new locations. Which generates redo.

Rating

  (1 rating)

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

Comments

Ram Dittakavi, August 01, 2017 - 5:14 pm UTC

1) So, move of a table which doesn't have any indexes won't generate redo at all ?

2) Can you explain more on how this new segment is created and the move takes place ? Will it create some initial extents and then try moving the data from old segment which is why need extra storage ?
Connor McDonald
August 04, 2017 - 1:35 am UTC

1) No this is not correct - we still must protect the move with redo

SQL> create table t as select * from dba_objects;

Table created.

SQL>
SQL> @mystat
Enter value for statname: redo size

NAME                                                    VALUE
-------------------------------------------------- ----------
redo size                                            12765736
redo size for lost write detection                          0
redo size for direct writes                          12601256

3 rows selected.

SQL>
SQL> alter table t move;

Table altered.

SQL>
SQL> @mystat
Enter value for statname: redo size

NAME                                                    VALUE
-------------------------------------------------- ----------
redo size                                            25476080
redo size for lost write detection                          0
redo size for direct writes                          25202600

3 rows selected.



2) We temporarily will have 2 copies of the data, hence the need for extra space

TABLE_MOVE

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database