将用户的数据从一个表空间移动到另一表空间

1、查询用户数据

sql> select segment_name,tablespace_name,extents,blocks from dba_segments where owner='SCOTT';
SEGMENT_NAME TABLESPACE_NAME EXTENTS BLOCKS
------------------------------ ------------------------------ ------------------------------ ----------
DEPT USERS 1 8
EMP USERS 1 8
SALGRADE USERS 1 8
APPUSER USERS 1 8
TRA_SCHEDULE_INFO USERS 35 2560
TRA_SCHEDULE_DETAIL USERS 106 35456
T USERS 1 8
EMP_TRAN USERS 1 8
PK_DEPT USERS 1 8
PK_EMP USERS 1 8
10 rows selected

2、查看用户数据都是什么类型的
sql> select object_name,object_type from dba_objects where owner='SCOTT';
OBJECT_NAME OBJECT_TYPE
-------------------------------------------------------------------------------- -------------------
PK_DEPT INDEX
DEPT TABLE
EMP TABLE
PK_EMP INDEX
BONUS TABLE
SALGRADE TABLE
APPUSER TABLE
TRA_SCHEDULE_INFO TABLE
TRA_SCHEDULE_DETAIL TABLE
T TABLE
EMP_TRAN TABLE
11 rows selected
3、查看数据索引属于哪个表
sql> select index_name,table_name from dba_indexes where owner='SCOTT';
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
PK_DEPT DEPT
PK_EMP EMP

4、移动表T到lianxi表空间


sql> alter table scott.T
2 move tablespace lianxi;
Table altered
5、移动后查看 t表已改变空间
sql> select segment_name,tablespace_name from dba_segments where owner='SCOTT';
SEGMENT_NAME TABLESPACE_NAME
-------------------------------------------------------------------------------- ------------------------------
DEPT USERS
EMP USERS
SALGRADE USERS
APPUSER USERS
TRA_SCHEDULE_INFO USERS
TRA_SCHEDULE_DETAIL USERS
T LIANXI
EMP_TRAN USERS
PK_DEPT USERS
PK_EMP USERS
10 rows selected

6、移动后索引无效，查看
sql> select index_name,status,tablespace_name from dba_indexes where owner='SCOTT';

7、重建索引 alter index scott.t rebuild tablespace indx;