What is sys.x$ktfbhc.ktfbhcsz and sys.x$ktfbfe.ktfbfeblks ?
Date: Wed, 4 Mar 2009 12:30:49 -0500
Message-ID: <087851E0987542069B6012B12517953B_at_gmei>
Hi,
Oracle 10.2
I have found that dba_data_files and dba_free_space report different values when there is no objects in a tablespace. Digging a bit deeper I found the difference is due to the value of sys.x$ktfbhc.ktfbhcsz and sys.x$ktfbfe.ktfbfeblks. My question is why they give me different answer?
SQL> select * from v$version;
BANNER
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Solaris: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production
- no objects in TEST_DATA tablespace: SUPPORT_at_armstrong-SQL> select count(*) from dba_segments where tablespace_name='TEST_DATA';
COUNT(*)
0
- but these two views show some difference: SUPPORT_at_armstrong-SQL> select to_char(sum(bytes)) from dba_data_files where tablespace_name='TEST_DATA';
TO_CHAR(SUM(BYTES))
19327352832
SUPPORT_at_armstrong-SQL> select to_char(sum(bytes)) from dba_free_space where tablespace_name='TEST_DATA';
TO_CHAR(SUM(BYTES))
19326763008
- getting the definitions of these views, query the sys tables directly, I got from dba_data_files:
SQL> select ts.name,hc.ktfbhcsz, ts.blocksize 2 from sys.v$dbfile v, sys.file$ f, sys.x$ktfbhc hc, sys.ts$ ts, x$kccfe fe
3 where v.file# = f.file# 4 and f.spare1 is NOT NULL 5 and v.file# = hc.ktfbhcafno
6 and hc.ktfbhctsn = ts.ts#
7 and fe.fenum = f.file#
8 and ts.ts#=40;
NAME KTFBHCSZ BLOCKSIZE
------------------------------ ---------- ----------
TEST_DATA 131072 16384 TEST_DATA 131072 16384 TEST_DATA 131072 16384 TEST_DATA 131072 16384 TEST_DATA 131072 16384 TEST_DATA 131072 16384 TEST_DATA 131072 16384 TEST_DATA 131072 16384 TEST_DATA 131072 16384
9 rows selected.
SQL> select to_char(sum(hc.ktfbhcsz *ts.blocksize)) 2 from sys.v$dbfile v, sys.file$ f, sys.x$ktfbhc hc, sys.ts$ ts, x$kccfe fe
3 where v.file# = f.file# 4 and f.spare1 is NOT NULL 5 and v.file# = hc.ktfbhcafno
6 and hc.ktfbhctsn = ts.ts#
7 and fe.fenum = f.file#
8 and ts.ts#=40;
TO_CHAR(SUM(HC.KTFBHCSZ*TS.BLOCKSIZE))
19327352832
- getting the definitions of these views, query the sys tables directly, I got from dba_free_space :
SQL> select /*+ ordered use_nl(f) use_nl(fi) */
2 ts.name, 3 f.ktfbfeblks , ts.blocksize
4 from sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fi where ts.ts# = f.ktfbfetsn
5 and f.ktfbfetsn = fi.ts#
6 7 and f.ktfbfefno = fi.relfile# 8 and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0 9 and ts.ts#=40;
NAME KTFBFEBLKS BLOCKSIZE
------------------------------ ---------- ----------
TEST_DATA 131068 16384 TEST_DATA 131068 16384 TEST_DATA 131068 16384 TEST_DATA 131068 16384 TEST_DATA 131068 16384 TEST_DATA 131068 16384 TEST_DATA 131068 16384 TEST_DATA 131068 16384 TEST_DATA 131068 16384
9 rows selected.
SQL> select /*+ ordered use_nl(f) use_nl(fi) */
to_char(sum(f.ktfbfeblks * ts.blocksize))
2 3 from sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fi
4 where ts.ts# = f.ktfbfetsn
5 and f.ktfbfetsn = fi.ts#
and f.ktfbfefno = fi.relfile#
6 7 and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ =
0
and ts.ts#=40; 8
TO_CHAR(SUM(F.KTFBFEBLKS*TS.BLOCKSIZE))
19326763008
TIA.
Guang
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Mar 04 2009 - 11:30:49 CST