在创建加密表空间以后，准备将需要加密的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.
复制「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
相关阅读
【纯干货】Oracle 19C RU 19.27 发布，如何快速升级和安装？
Lucifer三思而后行
786次阅读
2025-04-18 14:18:38
Oracle RAC 一键安装翻车？手把手教你如何排错！
Lucifer三思而后行
664次阅读
2025-04-15 17:24:06
Oracle数据库一键巡检并生成HTML结果，免费脚本速来下载!
陈举超
591次阅读
2025-04-20 10:07:02
【ORACLE】你以为的真的是你以为的么?--ORA-38104: Columns referenced in the ON Clause cannot be updated
DarkAthena
548次阅读
2025-04-22 00:13:51
【活动】分享你的压箱底干货文档，三篇解锁进阶奖励！
墨天轮编辑部
532次阅读
2025-04-17 17:02:24
【ORACLE】记录一些ORACLE的merge into语句的BUG
DarkAthena
510次阅读
2025-04-22 00:20:37
一页概览：Oracle GoldenGate
甲骨文云技术
496次阅读
2025-04-30 12:17:56
火焰图--分析复杂SQL执行计划的利器
听见风的声音
470次阅读
2025-04-17 09:30:30
OR+DBLINK的关联SQL优化思路
布衣
382次阅读
2025-05-05 19:28:36
3月“墨力原创作者计划”获奖名单公布
墨天轮编辑部
382次阅读
2025-04-15 14:48:05
TA的专栏
Roger's Database Notes
收录77篇内容
