GaussDB T 表空间的删除

GaussDB T 删除表空间，需要注意一个地方：

• 如果表空间是某个用户的默认表空间，直接删除会操作失败。

SQL> create tablespace ts_enmotech datafile 'ts_enmotech_1' size 100m;

Succeed.

SQL> select username,default_tablespace from dba_users
  2 where default_tablespace='TS_ENMOTECH';

USERNAME     DEFAULT_TABLESPACE  
------------ --------------------
SAMWISE      TS_ENMOTECH         

1 rows fetched.

SQL>  select owner,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,BYTES
  2 from dba_segments
  3 where tablespace_name='TS_ENMOTECH';

OWNER           SEGMENT_NAME         SEGMENT_TYPE TABLESPACE_NAME      BYTES               
--------------- -------------------- ------------ -------------------- --------------------

0 rows fetched.

SQL> DROP TABLESPACE TS_ENMOTECH;

GS-00790, Failed to drop tablespace TS_ENMOTECH, because it's the default tablespace for user

SQL> DROP TABLESPACE TS_ENMOTECH INCLUDING CONTENTS AND DATAFILES;

GS-00790, Failed to drop tablespace TS_ENMOTECH, because it's the default tablespace for user

SQL> 

SQL> alter user samwise default tablespace users;

Succeed.

SQL> DROP TABLESPACE TS_ENMOTECH INCLUDING CONTENTS AND DATAFILES;

Succeed.

还测试了下，如果表空间的某个数据文件offline，此时删除表空间，也需要把对象清理完才能删除。

SQL> create tablespace ts_human extents 128 datafile 'ts_human_1' size 128m autoextend on next 128m;

Succeed.

SQL> create table emp
  2 (empno int,
  3 ename varchar2(32) not null,
  4 higest_degree char(8),
  5 gradute_time datetime) tablespace ts_human;

Succeed.

SQL> create index id_emp_empno on emp(empno) tablespace ts_human online;

Succeed.

SQL> alter tablespace ts_human add datafile 'ts_human_2' size 128m;

Succeed.

SQL> select id,tablespace_id,status,type,file_name,bytes from dv_Data_files;

ID           TABLESPACE_ID STATUS               TYPE                 FILE_NAME                                                        BYTES               
------------ ------------- -------------------- -------------------- ---------------------------------------------------------------- --------------------
... ...省略... ...
13           7             ONLINE               FILE                 /u01/gaussdb/1.0.1/data/data/ts_enmotech_1                       104857600           
14           8             ONLINE               FILE                 /u01/gaussdb/1.0.1/data/data/ts_human_1                          134217728           
15           8             ONLINE               FILE                 /u01/gaussdb/1.0.1/data/data/ts_human_2                          134217728           

16 rows fetched.

offline数据文件。注意：gaussdb需要在mount状态才能offline数据文件：

SQL> alter tablespace TS_HUMAN offline datafile 'ts_human_2';

Succeed.

SQL> alter database open;

Succeed.

SQL> select tablespace_name,DATAFILE_COUNT,TOTAL_SIZE,USED_SIZE,STATUS from dba_tablespaces where tablespace_name='TS_HUMAN';

TABLESPACE_NAME       DATAFILE_COUNT TOTAL_SIZE           USED_SIZE            STATUS  
--------------------- -------------- -------------------- -------------------- --------
TS_HUMAN              2              0                    0                    OFFLINE

删除表空间，提示“GS-00742”的错误

SQL> drop tablespace TS_HUMAN including contents and datafiles;

GS-00742, Tablespace TS_HUMAN is not empty, exists object for offlined tablespace.

检查对象情况：

SQL>  select owner,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,BYTES
  2 from dba_segments
  3 where tablespace_name='TS_HUMAN';

OWNER      SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME                                                  BYTES               
---------- ------------ ------------ --------------------------------------------------------------- --------------------
SYS        EMP          TABLE        TS_HUMAN                                                         0                   
SYS        PART         TABLE        TS_HUMAN                                                         0                   
SYS        ID_EMP_EMPNO INDEX        TS_HUMAN                                                         0                   
SYS        LOB_PART_F3  LOB          TS_HUMAN                                                         0                   
SYS        PART         TABLE        TS_HUMAN                                                         0                   
SYS        LOB_PART_F3  LOB          TS_HUMAN                                                         0                   

6 rows fetched.

SQL> drop table emp;

Succeed.

SQL> drop table part;

Succeed.

再次检查：

SQL>  select owner,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,BYTES
  2 from dba_segments
  3 where tablespace_name='TS_HUMAN';

OWNER      SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME                                                  BYTES               
---------- ------------ ------------ ---------------------------------------------------------------- --------------------
SYS        BIN$2050$4E0 TABLE        TS_HUMAN                                                         0                   
SYS        BIN$2051$4E0 TABLE        TS_HUMAN                                                         0                   
SYS        BIN$2051$4E0 INDEX        TS_HUMAN                                                         0                   
SYS        LOB_BIN$2050 LOB          TS_HUMAN                                                         0                   
SYS        BIN$2050$4E0 TABLE        TS_HUMAN                                                         0                   
SYS        LOB_BIN$2050 LOB          TS_HUMAN                                                         0                   

6 rows fetched.

SQL> drop tablespace TS_HUMAN including contents and datafiles;

GS-00742, Tablespace TS_HUMAN is not empty, exists object for offlined tablespace.

清空回收站内容：

SQL> purge recyclebin;

Succeed.

SQL>  select owner,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,BYTES
  2 from dba_segments
  3 where tablespace_name='TS_HUMAN';

OWNER      SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME                                                  BYTES               
---------- ------------ ------------ ---------------------------------------------------------------- --------------------

0 rows fetched.

可以删除了：

SQL> drop tablespace TS_HUMAN including contents and datafiles;

Succeed.