在创建加密表空间以后，准备将需要加密的table move到加密表空间中时，发现
如下对象是曾经drop过的，但是使用purge dba_recyclebin 发现不管用，如下：
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.
另外一点是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.
发表回复