`
- 浏览:
1128669 次
-
DBA_FREE_SPACE的file_id和relative_fno问题
客户在检查数据库时发现了一个异常,在DBA_FREE_SPACE中的file_id和relative_fno不一致,即存在如下现象:
SQL> select * from dba_data_files where file_id <> relative_fno;
no rows selected
SQL> select count(*) from dba_free_space where file_id <> relative_fno;
COUNT(*)
----------
1194
看到这个问题,第一直接是跟recyclebin有关,因为DBA_FREE_SPACE和recyclebin相关的问题太多了,在我的书里已经写过几个相关的案例了。DBA_FREE_SPACE的视图定义如下,Oracle将recyclebin的对象也纳入进来:
create or replace view DBA_FREE_SPACE
(TABLESPACE_NAME, FILE_ID, BLOCK_ID,
BYTES, BLOCKS, RELATIVE_FNO)
as
select ts.name, fi.file#, f.block#,
f.length * ts.blocksize, f.length, f.file#
from sys.ts$ ts, sys.fet$ f, sys.file$ fi
where ts.ts# = f.ts#
and f.ts# = fi.ts#
and f.file# = fi.relfile#
and ts.bitmapped = 0
union all
select /*+ ordered use_nl(f) use_nl(fi) */
ts.name, fi.file#, f.ktfbfebno,
f.ktfbfeblks * ts.blocksize, f.ktfbfeblks, f.ktfbfefno
from sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fi
where ts.ts# = f.ktfbfetsn
and f.ktfbfetsn = fi.ts#
and f.ktfbfefno = fi.relfile#
and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
union all
select /*+ ordered use_nl(u) use_nl(fi) */
ts.name, fi.file#, u.ktfbuebno,
u.ktfbueblks * ts.blocksize, u.ktfbueblks, u.ktfbuefno
from sys.recyclebin$ rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fi
where ts.ts# = rb.ts#
and rb.ts# = fi.ts#
and rb.file# = fi.relfile#
and u.ktfbuesegtsn = rb.ts#
and u.ktfbuesegfno = rb.file#
and u.ktfbuesegbno = rb.block#
and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
union all
select ts.name, fi.file#, u.block#,
u.length * ts.blocksize, u.length, u.file#
from sys.ts$ ts, sys.uet$ u, sys.file$ fi, sys.recyclebin$ rb
where ts.ts# = u.ts#
and u.ts# = fi.ts#
and u.segfile# = fi.relfile#
and u.ts# = rb.ts#
and u.segfile# = rb.file#
and u.segblock# = rb.block#
and ts.bitmapped = 0
/
在我的一个10.2.0.3的数据库中,发现同样存在类似的问题:
$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Dec 22 13:57:26 2010
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters and Data Mining options
SQL> select count(*) from dba_free_space where file_id<>RELATIVE_FNO;
COUNT(*)
----------
3
将DBA_FREE_SPACE的语句解析出来,更改一下条件执行:
SQL> select /*+ ordered use_nl(u) use_nl(fi) */
2 ts.name, fi.file#, u.ktfbuebno,
3 u.ktfbueblks * ts.blocksize, u.ktfbueblks, u.ktfbuefno
4 from sys.recyclebin$ rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fi
5 where ts.ts# = rb.ts#
6 and rb.ts# = fi.ts#
7 and rb.file# = fi.relfile#
8 and u.ktfbuesegtsn = rb.ts#
9 and u.ktfbuesegfno = rb.file#
10 and u.ktfbuesegbno = rb.block#
11 and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
12 and fi.file# <> u.ktfbuefno;
NAME FILE# KTFBUEBNO U.KTFBUEBLKS*TS.BLOCKSIZE KTFBUEBLKS KTFBUEFNO
--------------------- ---------- ---------- ------------------------- ---------- ----------
SMSNP 11 3085273 65536 8 9
SMSNP 6 332961 65536 8 9
SMSNP 12 3107745 65536 8 6
我们发现正式回收站带来的3个异常区间,由于这里的相对文件号来自ktfbuefno,所以和其他视图并不一致相等。尝试清空回收站,问题消除:
SQL> show user
USER is "SYS"
SQL> purge dba_recyclebin;
DBA Recyclebin purged.
SQL> select /*+ ordered use_nl(u) use_nl(fi) */
2 ts.name, fi.file#, u.ktfbuebno,
3 u.ktfbueblks * ts.blocksize, u.ktfbueblks, u.ktfbuefno
4 from sys.recyclebin$ rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fi
5 where ts.ts# = rb.ts#
6 and rb.ts# = fi.ts#
7 and rb.file# = fi.relfile#
8 and u.ktfbuesegtsn = rb.ts#
9 and u.ktfbuesegfno = rb.file#
10 and u.ktfbuesegbno = rb.block#
11 and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
12 and fi.file# <> u.ktfbuefno;
no rows selected
SQL> select count(*) from dba_free_space where file_id<>RELATIVE_FNO;
COUNT(*)
----------
0
记录一下。
分享到:
Global site tag (gtag.js) - Google Analytics
相关推荐
FILINFO fno; //文件信息结构体 int i=0; char *pathname; char *newfile; int year=0,month=0,day=0,hour=0,minute=0,second=0; int year_old=0,month_old=0,day_old=0,hour_old=0,minute_old=0,second_old=0...
2.3.2 使用 DBMS_SPACE.FREE _BLOCK 2.4 管理锁争用 2.4.1 怎样查找产生锁的 SQL 语句 2.4.2 怎样释放锁 2.4.3 怎样删除用户会话 2.4.4 怎样从数据字典中提取视图创建 命令 2.4.5 怎样从数据字典中提取索引...
BUILD_ID=57513AA3 OUT_DIR= ============================================ rootroot@cm-System-Product-Name:/home/wwt/softap_ap6212a0_tinav2.1$ make -j12 rootroot@cm-System-Product-Name:/home/wwt/...
leddrv.c 编译方式 arm-elf-gcc -D__KERNEL__ -I你的uClinux目录/linux-2.4.x/include-Wall -Wstrict-prototypes -Wno-trigraphs -O2 -fno-strict-aliasing -fno-common -fno-common -pipe -fno-builtin -D__linux__...
“-fstrict-aliasing”表示启用严格别名规则,“-fno-strict-aliasing”表示禁用严格别名规则,当gcc的编译优化参数为“-O2”、“-O3”和“-Os”时,默认会打开“-fstrict-aliasing”。 什么是严格别名规则?gcc对...
get_bootstrap_dba: compat header size:12 CLUSTER C_USER# file_no: 1 block_no: 177 TABLE OBJ$ file_no: 1 block_no: 241 CLUSTER C_OBJ# file_no: 1 block_no: 49 CLUSTER C_OBJ# file_no: 1 block_no: 49 ...
推荐使用工具gdb+gperftools ...-fno-builtin-malloc -fno-builtin-calloc -fno-builtin-realloc -fno-builtin-free 链接选项加上: -ltcmalloc_debug 使用gdb调试,在越界的地方就会停下来。
create table parking_space( --停车位信息 pno char(4)primary key, fno char(2), hno char(4), carno char(8), park_fee int check(park_fee>0) not null, foreign key(fno,hno)references household(fno,...
利用CNN模拟偏微分算子
Substitute for bad_weak_ptr object in the case of -fno-exceptions.Empty helper class except when the template argument is _S_mutex.
BA 4602 (EX) FLUDEX Couplings Types FNO, FNA, FND, FNDB and FNDS [手册]pdf,
-fno-builtin :不适用内建函数(内建函数就是编译器提供的函数,比如说strlen,你想重写strlen,不使用编译器的strlen,这时候就需要用到这个命令) -mcmodel=large :好像是把临时变量可用的空间变大,看了好多...
使用大量的有限元法求解偏微分方程,是学习PCA特征提取的很好的学习资料,从先验概率中采样,计算权重。
所以启用了GCC的编译选项-fno-asynchronous-unwind-tables,直接预处理为没有cfi宏的汇编指令。 这里就是包含*no.s形式的文件。 最后的LEAVE指令,等效于“MOV ESP,EBP”和“POP EBP”两条指令。 这个指令调整了...
社区
CXXFLAGS="-O3 -felide-constructors -fno-exceptions -fno-rtti" cmake . -LH|more //CMake下查看MySQL的编译配置 cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DEXTRA_CHARSETS=all make make ...
详细介绍了gcc可能使用的各个参数,包含部分实例。
gcc -pthread -fno-strict-aliasing -DNDEBUG -g -fwrapv -O2 -Wall -Wstrict-prototypes -fPIC -DUSE__THREAD -DHAVE_SYNC_SYNCHRONIZE -I/usr/include/ffi -I/usr/include/libffi -I/usr/include/python2.7 -c c/_...