您现在的位置是：亿华云 > 热点

Oracle 表移动后必须做的事

亿华云2025-10-09 14:50:11【热点】9人已围观

简介概述移动一张表实际上是一个重组过程，数据库会将原来的数据复制到新的地方。但是如果你发现这个表在移动后性能下降了，可能是你的索引没有重建。本文将指导您找到依赖索引并重建它们。将表从示例移动到用户SQL&

概述

移动一张表实际上是表移必须一个重组过程，数据库会将原来的动后数据复制到新的地方。但是表移必须如果你发现这个表在移动后性能下降了，可能是动后你的索引没有重建。本文将指导您找到依赖索引并重建它们。网站模板表移必须

将表从示例移动到用户SQL> select tablespace_name from dba_tables where owner = HR and 动后table_name = EMPLOYEES;

TABLESPACE_NAME

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

EXAMPLE

SQL> alter table hr.employees move tablespace users;

Table altered.

SQL> select tablespace_name from dba_tables where owner = HR and table_name = EMPLOYEES;

TABLESPACE_NAME

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

USERS

查看哪些索引取决于此表SQL> column index_name format a30;

SQL> column tablespace_name format a30;

SQL> column status format a10;

SQL> select index_name, tablespace_name, status from dba_indexes where owner = HR and table_name = EMPLOYEES;

INDEX_NAME TABLESPACE_NAME STATUS

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

EMP_JOB_IX EXAMPLE UNUSABLE

EMP_DEPARTMENT_IX EXAMPLE UNUSABLE

EMP_MANAGER_IX EXAMPLE UNUSABLE

EMP_NAME_IX EXAMPLE UNUSABLE

EMP_EMAIL_UK EXAMPLE UNUSABLE

EMP_EMP_ID_PK EXAMPLE UNUSABLE

6 rows selected.

如您所见，所有依赖索引都是表移必须UNUSABLE。这意味着，动后数据库不会自动重建它们。表移必须你必须自己做。动后

编写所有重建语句，表移必须然后执行它们SQL> select alter index || owner || . ||index_name || rebuild tablespace users; as SQL_TO_BE_EXECUTED from dba_indexes where owner = HR and 动后table_name = EMPLOYEES;

SQL_TO_BE_EXECUTED

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

alter index EMP_JOB_IX rebuild tablespace users;

alter index EMP_DEPARTMENT_IX rebuild tablespace users;

alter index EMP_MANAGER_IX rebuild tablespace users;

alter index EMP_NAME_IX rebuild tablespace users;

alter index EMP_EMAIL_UK rebuild tablespace users;

alter index EMP_EMP_ID_PK rebuild tablespace users;

6 rows selected.

或者您可以重建原始表空间的索引。

SQL> select alter index || owner || . ||index_name || rebuild tablespace || tablespace_name || ; as SQL_TO_BE_EXECUTED from dba_indexes where owner = HR and 表移必须table_name = EMPLOYEES;

SQL_TO_BE_EXECUTED

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

alter index HR.EMP_DEPARTMENT_IX rebuild tablespace EXAMPLE;

alter index HR.EMP_NAME_IX rebuild tablespace EXAMPLE;

alter index HR.EMP_MANAGER_IX rebuild tablespace EXAMPLE;

alter index HR.EMP_EMP_ID_PK rebuild tablespace EXAMPLE;

alter index HR.EMP_EMAIL_UK rebuild tablespace EXAMPLE;

alter index HR.EMP_JOB_IX rebuild tablespace EXAMPLE;

6 rows selected.

请注意，服务器租用我们在新表空间USERS中重建索引。动后也就是表移必须说，对于索引，REBUILD相当于表中的MOVE

重建后检查状态SQL> select index_name, tablespace_name, status from dba_indexes where owner = HR and table_name = EMPLOYEES;

INDEX_NAME TABLESPACE_NAME STATUS