Home » RDBMS Server » Server Administration » The doubts about column segment_file & relative_fno in v$sort_segment (Oracle 10.2.0.4 HP-UX)
The doubts about column segment_file & relative_fno in v$sort_segment [message #606331] Wed, 22 January 2014 07:54 Go to next message
lylklb
Messages: 10
Registered: January 2014
Junior Member
1). Why the the value of the column segment_file in v$sort_segment do not correspond with any one value of the column file_id in v$tempfile !?
2). What should be the practical meaning of the column RELATIVE_FNO in v$sort_segments, such as to be assoicated with one relevant column in some dictionary view for SELECT query ?


>select TABLESPACE_NAME, current_users cur_users, SEGMENT_FILE SEG_FNO, RELATIVE_FNO REL_FNO, USED_BLOCKS U_BLKS, FREE_BLOCKS F_BLKS,
TOTAL_BLOCKS T_BLKS, TOTAL_EXTENTS T_EXTS, USED_EXTENTS U_EXTS, FREE_EXTENTS F_EXTS, EXTENT_SIZE EXT_SZ from v$sort_segment;
TABLESPACE_NAME CUR_USERS SEG_FNO REL_FNO U_BLKS F_BLKS T_BLKS T_EXTS U_EXTS F_EXTS EXT_SZ
-------------------- ------------- ----------- ------------- ----------- -------------- ------------ ----------- ----------- ---------- ---------
TEMP 9 0 0 2944 12579584 12582528 98301 23 98278 128


>select TABLESPACE_NAME, file_id, name file_name, BYTES_USED/1024/1024 U_MB, BYTES_FREE/1024/1024 F_MB, tf.bytes/1024/1024 T_MB,
(BYTES_FREE/tf.bytes)*100 f_pct, tf.status from v$temp_space_header tsh, v$tempfile tf where tf.file#=file_id;
TABLESPACE_NAME FILE_ID FILE_NAME U_MB F_MB T_MB F_PCT STATUS
------------------ ---------- --------------------------------------------- ------- -------- ------- ------- ---------
TEMP 1 /oradata/u01/temp01.dbf 32768 0 32768 0 ONLINE
TEMP 2 /oradata/u01/temp02.dbf 32768 0 32768 0 ONLINE
TEMP 3 /oradata/u01/temp03.dbf 32768 0 32768 0 ONLINE

[Updated on: Wed, 22 January 2014 07:57]

Report message to a moderator

Re: The doubts about column segment_file & relative_fno in v$sort_segment [message #606333 is a reply to message #606331] Wed, 22 January 2014 08:03 Go to previous messageGo to next message
Michel Cadot
Messages: 59991
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Can you post your queries and result formatted and with ONLY the columns relevant to the question.

Re: The doubts about column segment_file & relative_fno in v$sort_segment [message #606358 is a reply to message #606333] Wed, 22 January 2014 19:43 Go to previous messageGo to next message
lylklb
Messages: 10
Registered: January 2014
Junior Member
TABLESPACE_NAME SEG_FNO REL_FNO
-------------------- ------------- -----------
TEMP 0 0



TABLESPACE_NAME FILE_ID FILE_NAME
------------------ ---------- ---------------------------------------------
TEMP 1 /oradata/u01/temp01.dbf
TEMP 2 /oradata/u01/temp02.dbf
TEMP 3 /oradata/u01/temp03.dbf
Re: The doubts about column segment_file & relative_fno in v$sort_segment [message #606359 is a reply to message #606358] Wed, 22 January 2014 19:50 Go to previous messageGo to next message
BlackSwan
Messages: 23146
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: The doubts about column segment_file & relative_fno in v$sort_segment [message #606361 is a reply to message #606333] Wed, 22 January 2014 20:58 Go to previous messageGo to next message
lylklb
Messages: 10
Registered: January 2014
Junior Member
TABLESPACE_NAME           SEG_FNO      REL_FNO 
-------------------- -------------  ----------- 
TEMP                           0            0 



TABLESPACE_NAME       FILE_ID     FILE_NAME 
------------------  ----------   --------------------------------------------- 
TEMP                      1       /oradata/u01/temp01.dbf 
TEMP                      2       /oradata/u01/temp02.dbf 
TEMP                      3       /oradata/u01/temp03.dbf 

[Updated on: Wed, 22 January 2014 20:59]

Report message to a moderator

Re: The doubts about column segment_file & relative_fno in v$sort_segment [message #607999 is a reply to message #606331] Fri, 14 February 2014 02:10 Go to previous messageGo to next message
lylklb
Messages: 10
Registered: January 2014
Junior Member
no one can explain this !?
Re: The doubts about column segment_file & relative_fno in v$sort_segment [message #608058 is a reply to message #607999] Fri, 14 February 2014 10:17 Go to previous messageGo to next message
tim2boles
Messages: 35
Registered: August 2008
Location: Clarksburg, WV
Member
I don't have the systems to test this out on....and it is just going to be a wild guess.

I am guessing that you have locally managed tablespaces. These fields probably would be populated if you where using dictionary managed tablespaces.

create tablespace temp_data
datafile '/disk01/oradata/test03/temp_data.bdf'
temporary;

This fails in a locally managed system but I think it will still work in a dictionary managed system.

Again...I can not test this...I don't see any documentation that verifies what I am saying....but it is a thought.

Regards
Tim
Re: The doubts about column segment_file & relative_fno in v$sort_segment [message #608100 is a reply to message #606331] Sat, 15 February 2014 07:52 Go to previous message
lylklb
Messages: 10
Registered: January 2014
Junior Member
Anyway, thanks a lot to tim !!!
Previous Topic: DB look like hang or spin, Only sys user can connect to DB but can't query anything
Next Topic: Database look like change to SUSPEND mode (Login only sys as sysdba allow)
Goto Forum:
  


Current Time: Thu Dec 18 19:47:52 CST 2014

Total time taken to generate the page: 0.09593 seconds