标签：10 14 sys 索引 ORCL 表与 2022 空间 scott
文档课题：表与索引分布在不同表空间，删除其中一个表空间的测试.
数据库：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，此前创建的索引也一起被删除.
标签：10,
14,
sys,
索引,
ORCL,
表与,
2022,
空间,
scott
From： https://blog.51cto.com/u_12991611/5757969