Oracle TDE 实施中遇到的小问题
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: Oracle TDE 实施中遇到的小问题
|
1 2 |
在创建加密表空间以后，准备将需要加密的table move到加密表空间中时，发现 如下对象是曾经drop过的，但是使用purge dba_recyclebin 发现不管用，如下： |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 |
SQL> select owner,segment_name,segment_type 2 from dba_segments 3 where segment_name like '%BIN$%'; 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 '%BIN$%' 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 '%BIN$%'; 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. |
|
1 2 3 |
另外一点是rebuild index时，发现数据量较大，而目前存储空间不足，由于将部分表 move了以后，原表空间是可以缩小的，但是问题是如何知道该表空间的那些数据文件 可以进行resize 呢？如何知道每个datafile 的使用情况呢？ 通过如下加班实现: |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 |
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. |
Leave a Reply
You must be logged in to post a comment.