oracle关闭tde,Oracle TDE 实施中遇到的小问题

在创建加密表空间以后，准备将需要加密的table move到加密表空间中时，发现

如下对象是曾经drop过的，但是使用purge dba_recyclebin 发现不管用，如下：

SQL> select owner,segment_name,segment_type

2 from dba_segments

3 where segment_name like '{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}BIN${39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}';

OWNER SEGMENT_NAME SEGMENT_TYPE

-------------------- -------------------------------------------------- ------------------------------------

SYS RECYCLEBIN$ TABLE

SYS RECYCLEBIN$_OBJ INDEX

SYS RECYCLEBIN$_TS INDEX

SYS RECYCLEBIN$_OWNER INDEX

DMSB01 BIN$eaUSockPX4jgRAAhWnkSBA==$0 INDEX

DMSB01 BIN$eaUnQVOZBL3gRAAhWnkSBA==$0 INDEX

DMSB01 BIN$eaUSockGX4jgRAAhWnkSBA==$0 INDEX

DMSB01 BIN$eaUSockYX4jgRAAhWnkSBA==$0 INDEX

DMSB01 BIN$eaUnQVOPBL3gRAAhWnkSBA==$0 INDEX

9 rows selected.

SQL> drop index "BIN$eaUSockPX4jgRAAhWnkSBA==$0";

drop index "BIN$eaUSockPX4jgRAAhWnkSBA==$0"

*

ERROR at line 1:

ORA-02429: cannot drop index used for enforcement of unique/primary key

SQL> SELECT table_name, index_type

2 FROM dba_indexes

3 WHERE index_name IN (SELECT segment_name

4 FROM dba_segments

5 WHERE segment_name LIKE '{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}BIN${39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}' AND owner = 'DMSB01');

TABLE_NAME INDEX_TYPE

------------------------------ ------------------

DROP1_CSTMSLD NORMAL

CSTMSLH NORMAL

CSTMSLE NORMAL

CSTMSLD NORMAL

CSTMSSH NORMAL

SQL> show user

USER is "DMSB01"

SQL> alter table DROP1_CSTMSLD modify primary key disable;

Table altered.

SQL> alter table CSTMSLH modify primary key disable;

Table altered.

SQL> alter table CSTMSLE modify primary key disable;

Table altered.

SQL> alter table CSTMSLD modify primary key disable;

Table altered.

SQL> alter table CSTMSSH modify primary key disable;

Table altered.

SQL> drop index "BIN$eaUSockPX4jgRAAhWnkSBA==$0" ;

Index dropped.

SQL> drop index "BIN$eaUnQVOZBL3gRAAhWnkSBA==$0" ;

Index dropped.

SQL> drop index "BIN$eaUSockGX4jgRAAhWnkSBA==$0" ;

Index dropped.

SQL> drop index "BIN$eaUSockYX4jgRAAhWnkSBA==$0" ;

Index dropped.

SQL> drop index "BIN$eaUnQVOPBL3gRAAhWnkSBA==$0" ;

Index dropped.

SQL> select owner,segment_name,segment_type

2 from dba_segments

3 where segment_name like '{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}BIN${39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}';

OWNER SEGMENT_NAME SEGMENT_TYPE

-------------------- ------------------------- ------------------------------------

SYS RECYCLEBIN$ TABLE

SYS RECYCLEBIN$_OBJ INDEX

SYS RECYCLEBIN$_TS INDEX

SYS RECYCLEBIN$_OWNER INDEX

SQL> alter table DROP1_CSTMSLD modify primary key enable;

Table altered.

SQL> alter table CSTMSLH modify primary key enable;

Table altered.

SQL> alter table CSTMSLE modify primary key enable;

Table altered.

SQL> alter table CSTMSLD modify primary key enable;

Table altered.

SQL> alter table CSTMSSH modify primary key enable;

Table altered.

另外一点是rebuild index时，发现数据量较大，而目前存储空间不足，由于将部分表

move了以后，原表空间是可以缩小的，但是问题是如何知道该表空间的那些数据文件

可以进行resize 呢？如何知道每个datafile 的使用情况呢？ 通过如下加班实现:

SQL> select *

2 from (select /*+ ordered use_hash(a,b,c) */

3 a.file_id,

4 a.file_name,

5 a.filesize,

6 b.freesize,

7 (a.filesize - b.freesize) usedsize,

8 c.hwmsize,

9 c.hwmsize - (a.filesize - b.freesize) unsedsize_belowhwm,

10 a.filesize - c.hwmsize canshrinksize

11 from (select file_id,

12 file_name,

13 round(bytes / 1024 / 1024) filesize

14 from dba_data_files) a,

15 (select file_id, round(sum(dfs.bytes) / 1024 / 1024) freesize

16 from dba_free_space dfs

17 group by file_id) b,

18 (select file_id, round(max(block_id) * 8 / 1024) HWMsize

19 from dba_extents

20 group by file_id) c

21 where a.file_id = b.file_id

22 and a.file_id = c.file_id

23 order by unsedsize_belowhwm desc)

24 where file_id in (select file_id

25 from dba_data_files

26 where tablespace_name = 'DMSB_TS01')

27 order by file_id;

FILE_ID FILE_NAME FILESIZE FREESIZE USEDSIZE HWMSIZE UNSEDSIZE_BELOWHWM CANSHRINKSIZE

-------- --------------------------------------- -------- ---------- ---------- ---------- ------------------ -------------

5 /dms/oradata/DMSBHMC/datafile/DMSB_TS01 4096 2177 1919 3097 1178 999

6 /dms/oradata/DMSBHMC/datafile/DMSB_TS02 4096 2221 1875 3053 1178 1043

7 /dms/oradata/DMSBHMC/datafile/DMSB_TS03 4096 2244 1852 2894 1042 1202

8 /dms/oradata/DMSBHMC/datafile/DMSB_TS04 4096 2292 1804 2845 1041 1251

9 /dms/oradata/DMSBHMC/datafile/DMSB_TS05 4096 1421 2675 4021 1346 75

10 /dms/oradata/DMSBHMC/datafile/DMSB_TS06 4096 1452 2644 3989 1345 107

11 /dms/oradata/DMSBHMC/datafile/DMSB_TS07 4096 1503 2593 3935 1342 161

12 /dms/oradata/DMSBHMC/datafile/DMSB_TS08 4096 1523 2573 3855 1282 241

13 /dms/oradata/DMSBHMC/datafile/DMSB_TS09 4096 1615 2481 3750 1269 346

15 /dms/oradata/DMSBHMC/datafile/DMSB_TS10 4096 1674 2422 3628 1206 468

20 /dms/oradata/DMSBHMC/datafile/DMSB_TS11 4096 1848 2248 3454 1206 642

21 /dms/oradata/DMSBHMC/datafile/DMSB_TS12 4096 1867 2229 3432 1203 664

23 /dms/oradata/DMSBHMC/datafile/DMSB_TS13 4096 1964 2132 3335 1203 761

25 /dms/oradata/DMSBHMC/datafile/DMSB_TS14 4096 2095 2001 3195 1194 901

14 rows selected.

++++++ 从上可以看出，如果我们需要对某个datafile进行resize，那么必须大于HWMSIZE值。++++++

++++++ resize以后的情况如下：++++++

FILE_ID FILE_NAME FILESIZE FREESIZE USEDSIZE HWMSIZE UNSEDSIZE_BELOWHWM CANSHRINKSIZE

------- ---------------------------------------- -------- ---------- ---------- ---------- ------------------ -------------

5 /dms/oradata/DMSBHMC/datafile/DMSB_TS01 3100 1242 1858 3097 1239 3

6 /dms/oradata/DMSBHMC/datafile/DMSB_TS02 3072 1258 1814 3053 1239 19

7 /dms/oradata/DMSBHMC/datafile/DMSB_TS03 3000 1209 1791 2894 1103 106

8 /dms/oradata/DMSBHMC/datafile/DMSB_TS04 3000 1257 1743 2845 1102 155

9 /dms/oradata/DMSBHMC/datafile/DMSB_TS05 4025 1482 2543 4021 1478 4

10 /dms/oradata/DMSBHMC/datafile/DMSB_TS06 4096 1584 2512 3989 1477 107

11 /dms/oradata/DMSBHMC/datafile/DMSB_TS07 4096 1632 2464 3935 1471 161

12 /dms/oradata/DMSBHMC/datafile/DMSB_TS08 3858 1416 2442 3855 1413 3

13 /dms/oradata/DMSBHMC/datafile/DMSB_TS09 3755 1404 2351 3750 1399 5

15 /dms/oradata/DMSBHMC/datafile/DMSB_TS10 3630 1321 2309 3628 1319 2

20 /dms/oradata/DMSBHMC/datafile/DMSB_TS11 3455 1304 2151 3452 1301 3

21 /dms/oradata/DMSBHMC/datafile/DMSB_TS12 3440 1291 2149 3431 1282 9

23 /dms/oradata/DMSBHMC/datafile/DMSB_TS13 3340 1287 2053 3335 1282 5

25 /dms/oradata/DMSBHMC/datafile/DMSB_TS14 3200 1262 1938 3195 1257 5

14 rows selected.