Shulou(Shulou.com)06/02 Report--

Alter table movealter table move has two main functions. 1. Used to move tables to other tablespaces. 2. It is used to reduce storage fragmentation in the table and optimize storage space and performance. For the first point. Create a table in the system table space. SQL> create table t as select * from dba_objects;

Table created.SQL> select table_name,tablespace_name

2 from user_tables

3 where table_name = 'T';

TABLE_NAME TABLESPACE_NAME

--------------- ---------------

T SYSTEM creates an index on it.

SQL> create index t_idx on t (object_id);

Index created.

SQL> select index_name,status

2 from user_indexes

3 where table_name = 'T';

INDEX_NAME STATUS

------------------------------------------------------------ ---------

T_IDX VALID We know that we should not store business or administratively unrelated data in the system table space. * **************************************************************************************************************** Note: Users should have sufficient table space quota in the target table space. SQL> conn t/test

Connected.

SQL> create table emp as select * from hr.employees;

Table created.

SQL> alter table emp move tablespace system;

alter table emp move tablespace system

*

ERROR at line 1:

ORA-01950: no privileges on tablespace 'SYSTEM'**********************************************************SQL> alter table t move tablespace users;

Table altered. The index is now invalid. Because indexes locate records by rowid, when tables are migrated to other tablespaces, or alter tables... Move is no longer effective when cleaning up debris. SQL> select index_name,status

2 from user_indexes

3 where table_name = 'T';

INDEX_NAME STATUS

------------------------------------------------------------ ----------

T_IDX UNUSABLE rebuild index. SQL> alter index t_idx rebuild tablespace users;

Index altered.SQL> select index_name,tablespace_name,status

2 from user_indexes

3 where table_name = 'T';

INDEX_NAME TABLESPACE_NAME STATUS

-------------------- --------------- ----------------

T_IDX USERS VALID

Also note: in the alter table... move... Statement will place an X lock on the target object. If the object to be moved is large, it will take a long time, so the X lock will take a long time. If an X lock has been placed on the target table, you need to wait until X is released before you can execute the statement. *********************************************************************** Note: For IOT tables we can use alter table... move ... The online clause. At this point the target table is available and select,DML operations can be performed on it. SQL> create table t_emp(

2 employee_id number(6),

3 last_name varchar2(20),

4 first_name varchar2(25),

5 constraint t_emp_pk primary key(employee_id))

6 organization index;

Table created.

SQL> insert into t_emp select employee_id,last_name,first_name

2 from hr.employees;

108 rows created.

SQL> commit;

Commit complete.

SQL> alter table t_emp move tablespace users online;

Table altered. When executing the above alter table t_emp move tablespace users online; statement, t_emp table can be accessed normally in other sessions. ************************************************************************************************************************************************************************************************************************ Only for the second point. There are some tables that are deleted frequently and rarely inserted. At this time, there will be more fragments in the segment where the table is located. We can use alter table move to sort them. This can reduce the high watermark, reduce the number of blocks read by full table scan, and improve the efficiency of the data buffer cache, because the cached data block actually has content. SQL> create table t as select object_id,object_name

2 from dba_objects;

Table created.

SQL> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed. The stored procedure I used below was created by downloading procedure code from asktom. Total Blocks represents the total number of blocks allocated to the table. Unused Blocks represents the number of never used blocks above the high water mark. ref:http://asktom.oracle.com/pls/asktom/f? p=100:11:::::P11_QUESTION_ID:231414051079SQL> set serveroutput on

SQL> exec show_space('T')

Free Blocks............................. 0

Total Blocks............................ 384

Total Bytes............................. 3145728

Unused Blocks........................... 68

Unused Bytes............................ 557056Last Used Ext FileId.................... 1

Last Used Ext BlockId................... 134528

Last Used Block......................... 60

PL/SQL procedure successfully completed.SQL> delete from t

2 where object_id

commit;

Commit complete.

SQL> exec show_space ('T ')--delete does not free storage space and does not reduce hwm.

Free Blocks............................. 161

Total Blocks............................ 384

Total Bytes............................. 3145728

Unused Blocks........................... 68

Unused Bytes............................ 557056

Last Used Ext FileId.................... 1

Last Used Ext BlockId................... 134528

Last Used Block......................... 60

PL/SQL procedure successfully completed. Execute alter table... Move cleans up storage fragmentation in tables. Essentially, t is read and table t is rebuilt in the same table space as table t. Delete the original table after the move operation is complete. Additional storage overhead is required, so the current table space size is at least twice the table size.

SQL> alter table t move;

Table altered.

SQL> exec show_space('T')

Free Blocks............................. 0

Total Blocks............................ 256

Total Bytes............................. 2097152

Unused Blocks........................... 101

Unused Bytes............................ 827392

Last Used Ext FileId.................... 1

Last Used Ext BlockId................... 134656

Last Used Block......................... 27

PL/SQL procedure successfully completed.hwm is dropped and some blocks allocated to tables are also reclaimed. ************************************************************************ Note: There is an alternative table... move can lower the hwm of the table, but it cannot release blocks already assigned to it, so it may clearly see that Total Blocks has been reduced. We can also determine this by looking at user_extents. Delete and alter table not executed... Move the previous situation. SQL> select segment_name,bytes from

2 user_extents

3 where segment_name = 'T';

SEGMENT_NAME BYTES

-------------------- ----------

T 65536

T 65536

T 65536

T 65536

T 65536

T 65536

T 65536

T 65536

T 65536

T 65536

T 65536

SEGMENT_NAME BYTES

-------------------- ----------

T 65536

T 65536

T 65536

T 65536

T 65536

T 1048576

T 1048576

18 rows selected. Execute delete and alter table... After the move. SQL> select segment_name,bytes from

2 user_extents

3 where segment_name = 'T';

SEGMENT_NAME BYTES

-------------------- ----------

T 65536

T 65536

T 65536

T 65536

T 65536

T 65536

T 65536

T 65536

T 65536

T 65536

T 65536

SEGMENT_NAME BYTES

-------------------- ----------

T 65536

T 65536

T 65536

T 65536

T 65536

T 1048576

17 rows selected.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.