Home » RDBMS Server » Server Administration » missing entry in sys.seg$ view
| missing entry in sys.seg$ view [message #177509] |
Thu, 15 June 2006 03:54  |
pallaviyd
Messages: 9
Registered: June 2006
|
Junior Member |
|
|
Hello !
This is an urgent issue.
Please refer the below queries.Do let me know whats the problem out here?
SQL> select OWNER,HEADER_FILE,HEADER_BLOCK
2 from dba_segments
3 where segment_name='TSNPJOBLOG';
OWNER HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
MVIEW_OWNER 76 120872
and
2) select nvl(FILE#,0),nvl(BLOCK#,0),TYPE#,TS#,MAXEXTS,EXTENTS
from sys.seg$
where file#=76 and block#=120872;
no rows selected
Thanks,
Pallavi.
|
|
|
|
| Re: missing entry in sys.seg$ view [message #177550 is a reply to message #177509] |
Thu, 15 June 2006 08:00  |
aciolac
Messages: 242
Registered: February 2006
|
Senior Member |
|
|
Make one simple test:
SQL>select *
from sys.seg$
where (file#,block#)in(select HEADER_FILE,HEADER_BLOCK
from dba_segments
where segment_name='TSNPJOBLOG')
If the segment 'TSNPJOBLOG' really exist in database, and select return a row(s), this mean that yor database is consistent, and there are some problems in your selects.
|
|
|
|
| Re: missing entry in sys.seg$ view [message #177991 is a reply to message #177509] |
Sun, 18 June 2006 23:37 |
pallaviyd
Messages: 9
Registered: June 2006
|
Junior Member |
|
|
select * from sys.seg$ where (file#,block#)in(select
HEADER_FILE,HEADER_BLOCK
from dba_segments where segment_name='TSNPJOBLOG');
no rows selected
select file#,ts#,STATUS,ONLINE_TIME,BLOCK_SIZE from v$datafile;
76 9 ONLINE 16-JAN-2005 4096
select file#,status$,ts#,relfile# from sys.file$;
76 2 9 193
select owner,object_type,status,last_ddl_time,generated from dba_objects where object_name='TSNPJOBLOG';
OWNER OBJECT_TYPE STATUS LAST_DDL_TI G
----------------------------- ------------------ ------- ------MVIEW_OWNER TABLE VALID 27-JAN-2006 N
I have also run dbverify on file_id 76 still not able to conclude what is the exact problem.
|
|
|
|
|
|
| Re: missing entry in sys.seg$ view [message #178020 is a reply to message #178018] |
Mon, 19 June 2006 02:09 |
pallaviyd
Messages: 9
Registered: June 2006
|
Junior Member |
|
|
1) My database version is 9.2.0.5.0
2) select owner,object_type,status,last_ddl_time,generated from dba_objects where object_name='TSNPJOBLOG';
SQL>
OWNER OBJECT_TYPE STATUS LAST_DDL_TI G
------------------------------ ------------------ ------- ------ MVIEW_OWNER TABLE VALID 27-JAN-2006 N
Thanks,
Pallavi.
|
|
|
|
|
|
|
|
| Re: missing entry in sys.seg$ view [message #178039 is a reply to message #178035] |
Mon, 19 June 2006 03:11 |
aciolac
Messages: 242
Registered: February 2006
|
Senior Member |
|
|
|
I want to be sure that this dba_segments and sys.seg$ have some sense in 9.2.0 as in 8.1.7(where I am tested my select). If this problem occurs also by other tables, very possible that by changing of version, Oracle are changed in some manner meaning of dba_segments and sys.seg$. If this table is single, with indicated problem, possible that there are something incorrect in Dictionary.
|
|
|
|
| Re: missing entry in sys.seg$ view [message #178042 is a reply to message #177509] |
Mon, 19 June 2006 03:56 |
pallaviyd
Messages: 9
Registered: June 2006
|
Junior Member |
|
|
Ok.
The part I am stuck is,for this segment when header_block and header_file is found from dba_segments,the file# in sys.seg$ actually show relative file number in sys.seg$.
Let me just tell you again.
SQL> select OWNER,HEADER_FILE,HEADER_BLOCK
2 from dba_segments
3 where segment_name='TSNPJOBLOG';
OWNER HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
MVIEW_OWNER 76 120872
SQL> select nvl(FILE#,0),nvl(BLOCK#,0),TYPE#,TS#,MAXEXTS,EXTENTS
2 from sys.seg$
3 where file#='76' or block#='120872'
4 and nvl(MAXEXTS-EXTENTS,0)<=4 and nvl(MAXEXTS-EXTENTS,0)>=0;
NVL(FILE#,0) NVL(BLOCK#,0) TYPE# TS# MAXEXTS EXTENTS
------------ ------------- ---------- ---------- ---------- -----193 120872 5 9 5 1
SQL> select file#,status$,ts#,relfile# from sys.file$;
76 2 9 193
So what I want to say is file# of sys.seg$ shows relative file number for file no 76. Is it unusual ? some Dictionary problem or this can happen.
Thanks,
Pallavi.
|
|
|
|
| Re: missing entry in sys.seg$ view [message #178043 is a reply to message #178042] |
Mon, 19 June 2006 04:05 |
aciolac
Messages: 242
Registered: February 2006
|
Senior Member |
|
|
Sorry, on question. As I understand, You are write that select
SQL> select nvl(FILE#,0),nvl(BLOCK#,0),TYPE#,TS#,MAXEXTS,EXTENTS
2 from sys.seg$
3 where file#='76' or block#='120872'
4 and nvl(MAXEXTS-EXTENTS,0)<=4 and nvl(MAXEXTS-EXTENTS,0)>=0;
return values
NVL(FILE#,0) NVL(BLOCK#,0) TYPE# TS# MAXEXTS EXTENTS
------------ ------------- ---------- ---------- ---------- -----193 120872 5 9 5 1
My question = how select by file#='76' in WHERE clause can return row, where NVL(FILE#,0)=193 ?
|
|
|
|
| Re: missing entry in sys.seg$ view [message #178046 is a reply to message #177509] |
Mon, 19 June 2006 04:22 |
pallaviyd
Messages: 9
Registered: June 2006
|
Junior Member |
|
|
Because block#='120872' is there in file#= 193 instead of 76.
So there is no entry for file#=76 which is actually its relative file no.
Because in
SQL> select file#,BLOCK#,TS#,EXTENTS,MAXEXTS from sys.seg$;
there was not a single row for file# 76.
Thanks,
Pallavi.
|
|
|
|
| Re: missing entry in sys.seg$ view [message #178047 is a reply to message #178046] |
Mon, 19 June 2006 04:31 |
aciolac
Messages: 242
Registered: February 2006
|
Senior Member |
|
|
|
Sorry, but I doesn't understand something. Number of block is relative to file number. This mean that file 76 must have their block with number '120872'(of course if he are so big), and file 193 have his block with number '120872'. There doesn't exist globally order id of blocks except DBA(Data Block Address). Did You want to say, that '120872' is the DBA of block, where segment 'TSNPJOBLOG' starts?
|
|
|
|
| Re: missing entry in sys.seg$ view [message #178049 is a reply to message #177509] |
Mon, 19 June 2006 04:34 |
pallaviyd
Messages: 9
Registered: June 2006
|
Junior Member |
|
|
Yes you are correct because header_block and header_file show this no in dba_segments but the same is not there in sys.seg$
so is it something like from 9i there are some changes with sys.seg$ view significance?
|
|
|
|
| Re: missing entry in sys.seg$ view [message #178060 is a reply to message #178049] |
Mon, 19 June 2006 05:04 |
aciolac
Messages: 242
Registered: February 2006
|
Senior Member |
|
|
DBA has 4 bytes in lenght. Upper 10 bits represent relative file number, lower 22 bits represent block number.DBA usually are write in hexadecilam format. The number '120872' seems not to be DBA, because indifferent of type of number ( hexadecimal or decimal), mean that file number, which contain that segment is 0(usually first file in tablespace SYSTEM). Please verify yet once all Your results.
1) Give me result of select:
SQL> select OWNER,HEADER_FILE,HEADER_BLOCK
from dba_segments
where segment_name='TSNPJOBLOG';
2) Give me results of select:
SQL>select *
from sys.seg$
where file#=76;--if upper select are returned file_id=76
3) Give me result of select:
select *
from dba_extents
where file_id=76
and ((block_id<=120872)and((block_id+bytes/your_block_size)>=120872))
where your_block_size is the size of datablock of tablespace where are file 76.
|
|
|
|
| Re: missing entry in sys.seg$ view [message #178064 is a reply to message #177509] |
Mon, 19 June 2006 05:19 |
pallaviyd
Messages: 9
Registered: June 2006
|
Junior Member |
|
|
SQL> select OWNER,HEADER_FILE,HEADER_BLOCK
2 from dba_segments
3 where segment_name='TSNPJOBLOG';
OWNER HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
MVIEW_OWNER 76 120872
and
Fifth query
SQL> select nvl(FILE#,0),nvl(BLOCK#,0),TYPE#,TS#,MAXEXTS,EXTENTS
2 from sys.seg$
3 where file#='76' and block#='120872';
no rows selected
|
|
|
|
Goto Forum:
Current Time: Fri Sep 20 11:30:51 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
