文档课题：表与索引分布在不同表空间，删除其中一个表空间的测试.
数据库：oracle 11.2.0.4 64位
1、环境准备
sys@ORCL 2022-10-14 09:49:15> create tablespace app1tbs datafile '/u01/app/oracle/oradata/orcl/app1tbs.dbf' size 10m;

Tablespace created.

sys@ORCL 2022-10-14 09:50:51> create tablespace idxtbs datafile '/u01/app/oracle/oradata/orcl/idxtbs.dbf' size 15m;

Tablespace created.

sys@ORCL 2022-10-14 09:53:02> alter user scott account unlock;

User altered.

sys@ORCL 2022-10-14 09:53:19> alter user scott identified by tiger;

User altered.

sys@ORCL 2022-10-14 09:53:31> create table scott.app1_emp tablespace app1tbs as select * from scott.emp;

Table created.

sys@ORCL 2022-10-14 09:55:18> create index scott.idx_emp_ename on scott.app1_emp(ename) tablespace idxtbs;

Index created.

sys@ORCL 2022-10-14 10:06:27> conn scott/tiger
Connected.
scott@ORCL 2022-10-14 10:07:32> col index_name for a15
scott@ORCL 2022-10-14 10:07:40> col table_name for a15
scott@ORCL 2022-10-14 10:07:48> col tablespace_name for a15
scott@ORCL 2022-10-14 10:07:56> select index_name,table_name,tablespace_name,status,last_analyzed from user_indexes

INDEX_NAME      TABLE_NAME      TABLESPACE_NAME STATUS   LAST_ANALYZED
--------------- --------------- --------------- -------- -------------------
PK_EMP          EMP             USERS           VALID    2022-10-11 22:00:04
PK_DEPT         DEPT            USERS           VALID    2022-10-11 22:00:04
IDX_EMP_ENAME   APP1_EMP     IDXTBS          VALID    2022-10-14 09:56:19
说明：如上所示，表APP1_EMP对应的表空间为APPTBS1，该表上的索引IDX_EMP_ENAME对应的表空间为IDXTBS.
2、删除测试
现测试删除表空间IDXTBS.
sys@ORCL 2022-10-14 10:09:35> drop tablespace idxtbs including contents and datafiles;

Tablespace dropped.
sys@ORCL 2022-10-14 10:06:27> conn scott/tiger
Connected.
scott@ORCL 2022-10-14 10:10:40> select index_name,table_name,tablespace_name,status,last_analyzed from user_indexes;

INDEX_NAME      TABLE_NAME      TABLESPACE_NAME STATUS   LAST_ANALYZED
--------------- --------------- --------------- -------- -------------------
PK_EMP          EMP             USERS           VALID    2022-10-11 22:00:04
PK_DEPT         DEPT            USERS           VALID    2022-10-11 22:00:04

结论：如上所示，sys用户成功删除表空间IDXTBS，此前创建的索引也一起被删除.