Monday, July 25, 2011

Getting datafile's mount points

Lets say that you have a policy for naming your filesystem mount points of your Oracle database, like mounting your filesystems in directories at the top of root, and you have a lot of filesystems for your database files. Then if you want to know the filesystem mount points you can extract them querying dba and v$ views with substr and instr functions like this:

SQL> select distinct substr(FILE_NAME,1,instr(FILE_NAME,'/',2)-1) as mount from dba_data_files union
select distinct substr(FILE_NAME,1,instr(FILE_NAME,'/',2)-1) as mount from dba_temp_files union
select distinct substr(MEMBER,1,instr(MEMBER,'/',2)-1) as mount from v$logfile union
select distinct substr(NAME,1,instr(NAME,'/',2)-1) as mount from v$controlfile order by 1;

MOUNT
---------------------------------------
/control1
/control2
/data1
/data2
/index1
/index2
/redologs1
/redologs2
/system
/temp
/undo

11 rows selected.

No comments:

Post a Comment