What is sys.x$ktfbhc.ktfbhcsz and sys.x$ktfbfe.ktfbfeblks ?
- From: "Guang Mei" <gmei@xxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 4 Mar 2009 12:30:49 -0500
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@armstrong-SQL> select count(*) from dba_segments where
tablespace_name='TEST_DATA';
COUNT(*)
----------
0
-- but these two views show some difference:
SUPPORT@armstrong-SQL> select to_char(sum(bytes)) from dba_data_files where
tablespace_name='TEST_DATA';
TO_CHAR(SUM(BYTES))
----------------------------------------
19327352832
SUPPORT@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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
