1 查某用户的数据都存在哪写表空间中
SQL> select distinct tablespace_name
  2  from dba_segments
  3  where wner='RISENET_JXFGW';

TABLESPACE_NAME
------------------------------
RISENET_JXFGW
RISENET
USERS
SYSTEM

SQL> l
  1  select segment_name,tablespace_name,extents,blocks
  2  from dba_segments
  3* where wner='RISENET_JXFGW'  and tablespace_name='USERS' and segment_name=
INFO_USER_NEWFILES'
SQL> /

SEGMENT_NAME
-------------------------------------------------------------------------------

TABLESPACE_NAME                   EXTENTS     BLOCKS
------------------------------ ---------- ----------
INFO_USER_NEWFILES
USERS                                   1          8

2 哪些段是表，那些段是索引？
SQL> l
  1  select object_id,object_name,object_type
  2  status,created
  3  from dba_objects
  4  where wner='RISENET_JXFGW'
  5* and object_type='INDEX'

3 某表在什么表空间
SQL> select table_name,tablespace_name,status from dba_tables
  2  where wner='RISENET_JXFGW'
  3  and table_name='INFO_USER_NEWFILES';

TABLE_NAME                     TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ --------
INFO_USER_NEWFILES             USERS                          VALID

4 某索引基于哪个表
SQL> l
  1  select index_name,table_name,tablespace_name,status
  2  from dba_indexes
  3  where wner='RISENET_JXFGW'
  4* and index_name='PK_INFO_USER_QSWNZXGH_2316'
SQL> /

INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
TABLESPACE_NAME                STATUS
------------------------------ --------
PK_INFO_USER_QSWNZXGH_2316     INFO_USER_QSWNZXGH
RISENET_JXFGW                  VALID

5 某表在哪表空间
SQL> select table_name,tablespace_name
  2  from dba_tables
  3  where table_name='INFO_USER_QSWNZXGH';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
INFO_USER_QSWNZXGH             RISENET_JXFGW

6 把表移动到user表空间
SQL> alter table risenet_jxfgw.INFO_USER_QSWNZXGH
  2  move tablespace users;

表已更改。

SQL> select table_name,tablespace_name
  2  from dba_tables
  3  where table_name='INFO_USER_QSWNZXGH';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
INFO_USER_QSWNZXGH             USERS

7  查看改表的索引是否有效？
SQL> l
  1  select index_name,table_name,tablespace_name,index_type,
  2  uniqueness,status
  3  from dba_indexes
  4* where index_name='PK_INFO_USER_QSWNZXGH_2316'
SQL> /

INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
TABLESPACE_NAME                INDEX_TYPE                  UNIQUENES STATUS
------------------------------ --------------------------- --------- --------
PK_INFO_USER_QSWNZXGH_2316     INFO_USER_QSWNZXGH
RISENET_JXFGW                  NORMAL                      UNIQUE    UNUSABLE

可以看到status是unusable无用.

8 表移动到另一表空间后，索引如果变成unusable,则需要重建
SQL> alter index risenet_jxfgw.PK_INFO_USER_QSWNZXGH_2316 rebuild
  2  tablespace users;

索引已更改。

SQL> select index_name,table_name,tablespace_name,index_type,
  2  uniqueness,status
  3  from dba_indexes
  4  where index_name='PK_INFO_USER_QSWNZXGH_2316';

INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
TABLESPACE_NAME                INDEX_TYPE                  UNIQUENES STATUS
------------------------------ --------------------------- --------- --------
PK_INFO_USER_QSWNZXGH_2316     INFO_USER_QSWNZXGH
USERS                          NORMAL                      UNIQUE    VALID