公司Test数据库是报表统计数据库，需要整理，其中有4个表空间需要删除，因为这4个表空间有新的表空间代替，用不到了，

需要删除的表空间如下：

用户SMS_BM的默认表空间：TS_SMS_BM_DATA

索引表空间：TS_SMS_BM_INDEX

用户SMS_BU的默认表空间：TS_SMS_BU_DATA

索引表空间：TS_SMS_BU_INDEX

新的表空间如下：

用户BM_SMS的默认表空间：TS_BM_SMS_DATA

索引表空间：TS_BM_SMS_INDEX

用户BU_SMS的默认表空间：TS_BU_SMS_DATA

索引表空间：TS_BU_SMS_INDEX

我做了一件比较傻的操作

我先删除SMS_BM用户下的所有对象

select 'drop '||'object_type||' '||object_name||';' from user_objects;

然后删除SMS_BM用户

drop user sms_bm cascade;

然后删除表空间

drop tablespace TS_SMS_BM_DATA including contents;

drop tablespace TS_SMS_BM_INDEX including contents;

然而，在删除表空间TS_SMS_BM_INDEX 的时候，报错：无法删除用于强制唯一/主关键字的索引

还好我在删除之前做了备份

exp sms_bm/smsbm@test buffer=9999999 file=I:\orabak\TS_SMS_BM_1,I:\orabak\TS_SMS_BM_2,I:\orabak\TS_SMS_BM_3,I:\orabak\TS_SMS_BM_4,I:\orabak\TS_SMS_BM_5,I:\orabak\TS_SMS_BM_6,I:\orabak\TS_SMS_BM_7,I:\orabak\TS_SMS_BM_8,I:\orabak\TS_SMS_BM_9,I:\orabak\TS_SMS_BM_10,I:\orabak\TS_SMS_BM_11,I:\orabak\TS_SMS_BM_12,I:\orabak\TS_SMS_BM_13 filesize=1024M
exp sms_bu/smsbu@test buffer=9999999 file=I:\orabak\TS_SMS_BU_1,I:\orabak\TS_SMS_BU_2,I:\orabak\TS_SMS_BU_3,I:\orabak\TS_SMS_BU_4,I:\orabak\TS_SMS_BU_5,I:\orabak\TS_SMS_BU_6,I:\orabak\TS_SMS_BU_7,I:\orabak\TS_SMS_BU_8,I:\orabak\TS_SMS_BU_9,I:\orabak\TS_SMS_BU_10,I:\orabak\TS_SMS_BU_11,I:\orabak\TS_SMS_BU_12 filesize=1024M

现在就得把数据再导回去

1，新建表空间

Create Tablespace Ts_Sms_Bm_Data
    Logging
    DataFile 'E:\OraData\Ts_Sms_Bm_Data_01.Ora' Size 1024M,
             'E:\OraData\Ts_Sms_Bm_Data_02.Ora' Size 1024M,
             'E:\OraData\Ts_Sms_Bm_Data_03.Ora' Size 1024M,
             'E:\OraData\Ts_Sms_Bm_Data_04.Ora' Size 1024M,
             'E:\OraData\Ts_Sms_Bm_Data_05.Ora' Size 1024M,
             'E:\OraData\Ts_Sms_Bm_Data_06.Ora' Size 1024M,
             'E:\OraData\Ts_Sms_Bm_Data_07.Ora' Size 1024M,
             'E:\OraData\Ts_Sms_Bm_Data_08.Ora' Size 1024M,
             'F:\OraData\Ts_Sms_Bm_Data_09.Ora' Size 1024M,
             'F:\OraData\Ts_Sms_Bm_Data_10.Ora' Size 1024M
    Extent Management Local

2，建用户并赋权限

SQL> create user sms_bm identified by smsbm
default tablespace Ts_Sms_Bm_Data
temporary tablespace Ts_Test_Temp;

SQL> grant connect,resource to sms_bm;

授权成功。

SQL> grant unlimited tablespace to sms_bm;

授权成功。

3，导入数据

C:\>imp system/testsystem@test buffer=99999
99 fromuser=sms_bm touser=sms_bm file=i:\orabak\TS_SMS_BM_1.DMP,i:\orabak\TS_SMS
_BM_2.DMP,i:\orabak\TS_SMS_BM_3.DMP,i:\orabak\TS_SMS_BM_4.DMP,i:\orabak\TS_SMS_B
M_5.DMP,i:\orabak\TS_SMS_BM_6.DMP

4，查询一下，所有的对象都恢复了，并且看到索引PK_BATCH_PHONE的所有者是BM_SMS,所以要把这个索引

移到TS_BM_SMS表空间

SQL> select OWNER,INDEX_NAME,TABLE_OWNER from dba_indexes where TABLESPACE_NAME='TS_SMS_BM_INDEX';

OWNER INDEX_NAME TABLE_OWNER
------------------------------ ------------------------------ ------------------------------

BM_SMS                         PK_BATCH_PHONE              BM_SMS
SMS_BM                         ALL_BILL_PHONE                 SMS_BM
SMS_BM                         PK_AREA                        SMS_BM
SMS_BM                         PK_BATCH_PHONE_HISTORY         SMS_BM
SMS_BM                         PK_BATCH_SMS                   SMS_BM
SMS_BM                         PK_BATCH_STATE                 SMS_BM

alter index bm_sms.PK_BATCH_PHONE rebuild tablespace ts_bm_sms_index;

5，这时候就可以删除表空间了

SQL> drop user sms_bm cascade;

User dropped

SQL> drop tablespace TS_SMS_BM_DATA including contents;

Tablespace dropped

SQL> drop tablespace TS_SMS_BM_INDEX including contents;

Tablespace dropped

ITPub博客

删除表空间

最新文章