Data Files on Disks

[Upeshp]

Hi,

I am using oracle 8.0.4 DB on Tru64 Unix. My file system type on Unix is 'advfs' . having 6 disks. Now My question is,

How do i find out the datafiles are on which disks ??
Is there any sql query which gives me above result ??

Please let me know, it will be a great help.

Thanks,

[hany]

hi,
actually i'm not sure about unix, i'm using windows, but i really don't think that there's a difference in this point.
did you try to query v$datafile?
like
select name
from v$datafile;

Hope This Helps

[julian]

select name from v$datafile
union all
select name from v$tempfile;

[pando]

from oracle you cannot find where the file are located in which physical disk, you can only know in what filesystem/mount point the files are located

if it's advfs I am pretty sure what you see are logical disks

[Upeshp]

Dear Hany and Julian,

First for Hany, no v$datafile doesn't give the physical disk name. so it cannot work.

Second for Julian, What is v$tempfile in oracle 8.0.4,

What i want is physical disk name vs. datafiles name.

any other solution please.....?

anyway thanks for your reply.

[julian]

What is v$tempfile in oracle 8.0.4

Nothing. I v$tempfile is only in 8i and above. I did not pay attention to your Oracle version, sorry.

[ctcyl]

Dear Friends..

As far as Win-NT is concern, You can check the disk and also complete path of the datafile by V$datafile.

I think same will also work with Unix.

Naresh Kakrani
DBA
Reliance Telecom Ltd

[Upeshp]

Hi Pando,

In advfs what we see is fileset name and file domain name, there may be more than one domain on One disk.



------------------------------------------------------------------------

ctcyl Dear Friends..

As far as Win-NT is concern, You can check the disk and also complete path of the datafile by V$datafile.

I think same will also work with Unix.

------------------------------------------------------------------------

No Naresh, It is not same in Unix.



Ok, IF not thru. sql query then is there any other way with less labour work.

Thanks

[pando]

that´s what I am saying, you see logical disk in advfs and not physical disk and Oracle does not tell you physical disks

You have to find out yourself at OS level the mounting points of every filesystem

How many QBB do you have?
For example we have 6 QBB and we see something like


Filesystem 1024-blocks Used Available Capacity Mounted on
cluster_root#root 512000 185277 318800 37% /
root1_domain#root 131072 57414 68320 46% /cluster/members/member1/boot_partition
cluster_usr#usr 6654624 4143471 2174000 66% /usr
cluster_var#var 1544688 803694 732200 53% /var
/proc 0 0 0 100% /proc
carga_dom#dw_carga 17778192 17753748 0 100% /dw_carga
carga_tmp_dom#dw_carga_tmp 35556384 11599975 23949848 33% /dw_carga_tmp
carga_tmp_dom2#dw_carga_tmp2 17778192 4510477 13247864 26% /dw_carga_tmp2
cargap_domain#dw_cargap_ora115 53321552 11938560 12742952 49% /dw_cargap_ora115
cargap_domain#dw_cargap_ora203 53321552 6513080 12742952 34% /dw_cargap_ora203
cargap_domain#dw_cargap_ora204 53321552 1058184 12742952 8% /dw_cargap_ora204
cargap_domain#dw_cargap_ora305 53321552 5667480 12742952 31% /dw_cargap_ora305
cargap_domain#dw_cargap_ora306 53321552 5538192 12742952 31% /dw_cargap_ora306
cargap_domain#dw_cargap_ora512 53321552 9855856 12742952 44% /dw_cargap_ora512
dw_carga_cmd#carga_cmd 17769176 4498805 13260616 26% /cmd
negop_domain#dw_negop_ora113 142190816 12686774 9581304 57% /dw_negop_ora113
negop_domain#dw_negop_ora114 142190816 11260856 9581304 55% /dw_negop_ora114
negop_domain#dw_negop_ora122 142190816 15678915 9581304 63% /dw_negop_ora122
negop_domain#dw_negop_ora202 142190816 9911432 9581304 51% /dw_negop_ora202
negop_domain#dw_negop_ora203 142190816 5870960 9581304 38% /dw_negop_ora203
negop_domain#dw_negop_ora302 142190816 6377544 9581304 40% /dw_negop_ora302
negop_domain#dw_negop_ora304 142190816 14154770 9581304 60% /dw_negop_ora304
negop_domain#dw_negop_ora413 142190816 8870192 9581304 49% /dw_negop_ora413
negop_domain#dw_negop_ora422 142190816 7906744 9581304 46% /dw_negop_ora422
negop_domain#dw_negop_ora424 142190816 6348912 9581304 40% /dw_negop_ora424
negop_domain#dw_negop_ora425 142190816 8526656 9581304 48% /dw_negop_ora425
negop_domain#dw_negop_ora513 142190816 12295800 9581304 57% /dw_negop_ora513
negop_domain#dw_negop_ora514 142190816 6348912 9581304 40% /dw_negop_ora514
negop_domain#dw_negop_ora522 142190816 6348924 9581304 40% /dw_negop_ora522
trafp_domain#dw_trafp_ora111 195512376 4352160 39545808 10% /dw_trafp_ora111
trafp_domain#dw_trafp_ora112 195512376 8937936 39545808 19% /dw_trafp_ora112
trafp_domain#dw_trafp_ora116 195512376 9267328 39545808 19% /dw_trafp_ora116
trafp_domain#dw_trafp_ora121 195512376 5403000 39545808 13% /dw_trafp_ora121
trafp_domain#dw_trafp_ora201 195512376 20133576 39545808 34% /dw_trafp_ora201
trafp_domain#dw_trafp_ora206 195512376 20009360 39545808 34% /dw_trafp_ora206
trafp_domain#dw_trafp_ora301 195512376 4120768 39545808 10% /dw_trafp_ora301
trafp_domain#dw_trafp_ora303 195512376 5284160 39545808 12% /dw_trafp_ora303
trafp_domain#dw_trafp_ora411 195512376 18534600 39545808 32% /dw_trafp_ora411
trafp_domain#dw_trafp_ora412 195512376 2660552 39545808 7% /dw_trafp_ora412
trafp_domain#dw_trafp_ora414 195512376 7756264 39545808 17% /dw_trafp_ora414
trafp_domain#dw_trafp_ora415 195512376 2099280 39545808 6% /dw_trafp_ora415
trafp_domain#dw_trafp_ora421 195512376 2406512 39545808 6% /dw_trafp_ora421
trafp_domain#dw_trafp_ora423 195512376 5241280 39545808 12% /dw_trafp_ora423
trafp_domain#dw_trafp_ora511 195512376 9267312 39545808 19% /dw_trafp_ora511
trafp_domain#dw_trafp_ora516 195512376 6056688 39545808 14% /dw_trafp_ora516
trafp_domain#dw_trafp_ora521 195512376 2416792 39545808 6% /dw_trafp_ora521
trafp_domain#dw_trafp_ora523 195512376 19719084 39545808 34% /dw_trafp_ora523
trafp_domain#dw_trafp_ora524 195512376 2099272 39545808 6% /dw_trafp_ora524
avanp_domain1#dw_avanp 35556384 11872518 23676240 34% /dw_avanza
avanp_domain2#dw_avanp_ora001 35556384 18574864 16975696 53% /dw_avanza_ora001
avanp_domain3#dw_avanp_ora002 35556384 22207872 13342304 63% /dw_avanza_ora002
avanp_domain4#dw_avanp_ora003 35556384 26897368 8653832 76% /dw_avanza_ora003
avanp_domain5#dw_avanp_ora004 35556384 11403552 24147008 33% /dw_avanza_ora004
trafp_domain#dw_trafp_opsc 6815744 20 6815724 1% /usr/users/opsc
trafp_domain#dw_trafp_fraude 819200 20 819180 0% /usr/users/fraude
trafp_domain#dw_trafp_perf 819200 173441 645759 22% /usr/users/perf
trafp_domain#dw_trafp_bscs 819200 44 819156 1% /usr/users/bscs
trafp_domain#dw_trafp_cm 819200 456 818744 1% /usr/users/cm
trafp_domain#dw_trafp_mtdatos 819200 404 818796 1% /usr/users/mtdatos
trafp_domain#dw_trafp_sapmdw 819200 20 819180 0% /usr/users/sapmdw
trafp_domain#dw_trafp_sapdwh 1765376 20 1765356 1% /usr/users/sapdwh
trafp_domain#dw_trafp_clarify 3862528 114 3862414 1% /usr/users/clarify
root2_domain#root 131072 78640 47088 63% /cluster/members/member2/boot_partition


Now I am npt expert in Tru64 so you better ask you unix admin of how to find out mounting points or try http://www.unix.com

[Edited by pando on 08-19-2002 at 03:31 AM]