Re: No full pathname in field file_name of dba_data_files

php_at_yeah.net (wangz) wrote in message news:<6f77ff2a.0201131916.5ce13216_at_posting.google.com>...
> Some backup tools need to extract full path from dba_data_files
> to backup the datafile,but some record of dba_data_files in my
> oracle database only show filename,no path is include.
> can anybody tell me what it occured,and how can I restore
> the field only include filename to filename with full path.
>
>
> Os: WINNT4.0
> Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production
>
> SQL> select file_name,tablespace_name from dba_data_files;
>
> FILE_NAME TABLESPACE_NAME
> -------------------------------------- --------------------
> C:\ORANT\DATABASE\SYS1ORCL.ORA SYSTEM
> C:\ORANT\DATABASE\RBS1ORCL.ORA RBS
> C:\ORANT\DATABASE\USR1ORCL.ORA USR
> C:\ORANT\DATABASE\TMP1ORCL.ORA TEMPORARY
> C:\ORANT\DATABASE\INDX1ORCL.ORA INDX
> C:\ORANT\DATABASE\DRORCL.ORA DRSYS
> C:\ORANT\DATABASE\IMAGE.ORA XFB_IMAGE
> MIG_DATA MIG_DATA
> MIGTMP MIGTMP
> MIGDATA1 MIG_DATA
> INX_MIG.ORA INX_MIG
> MIG_FLOW1.ORA MIG_FLOW
> MIG_FLOW2.ORA MIG_FLOW
>
>
> Thanks!
> Wangz

Hi,

Looks like when these datafiles were added, the full pathname was not provided. If I remember right, on Windows NT for 8.0.5, these files will be created in
%ORACLE_HOME%\database (the same directory that your other files are in). You can rename these files to show the full pathname in dba_data_files. Renaming them will update the controlfile with the full path. Login to server manager, do a shutdown immediate and then :

SVRMGR> startup mount
SVRMGR> alter database rename file 'migdata' to 'c:\orant\database\migdata.ora';

.
.
.
SVRMGR> alter database rename file 'mig_flow2.ora'
        to 'c:\orant\database\mig_flow2.ora';

Once you have renamed all the files, you can open the database. SVRMGR> alter database open;

Now check your dba_data_files. Make sure you take a backup of the files before doing this.

Murali
http://www.dbaquest.com/ Received on Mon Jan 14 2002 - 13:40:40 CST