-
rename datafile created with special charactor
Need help to rename the datafile created with spl char at the end like ***index03.dbf\12. I am unable to query the views for this file too. Please help.
Thanks.
Thanigaivasan.
Thanigaivasan
-
In sqlplus: SQL> set escape \
Escape the special chars with \ and rename file.
Remember the Golden Rule - He who has the gold makes the rules!
===================
Kris109
Ph.D., OCP 8i, 9i, 10g, 11g DBA
-
Hi Kris,
Thanks and when I try this...
SQL> set escape \
SQL>select name from v$datafile where name like '/u12/oradata/trps/ts_incdnt_txt_procs_index_03.dbf'
no row selected
but....for
SQL> select name from v$datafile where name like '%txt_procs_index_03%';
NAME
--------------------------------------------------------------------------------
/u12/oradata/trps/ts_incdnt_txt_procs_index_03.dbf SQL> select name from v$datafile where name like '%txt_procs_index_03.dbf';
no rows selected
Why it is not recognizing the file. Am I wrong?.
Thanks,
-
Try ****ndex03.dbf\\12(Escape the char '\') after switching escape on in sqlplus.
Reddy,Sam
-
Not working for me...
SQL> set escape on
SQL> select name from v$datafile where name like '%ndex03.dbf\\12';
no rows selected
SQL> c/%/***
1* select name from v$datafile where name like '***ndex03.dbf\\12'
SQL> /
no rows selected
Thanks,
Thanigaivasan.
-
Any update on this?.
Thanks,
Thanigaivasan.
-
Found the solution...for this. So thought of sharing with all.
do at os level..
cp file_name.dbf* file_name.dbf
now you will have two files in same directory..
ls -lrtb
-------- 2040 file_name.dbf\12
-------- 2040 file_name.dbf
at sqlplus...
SQL>alter database rename datafile 'file_name.dbf
' to 'file_name.dbf';
SQL>select file_name from dba_data_files;
you are fine now...
Please note the "'" at the end of file_name.dbf is in 2nd line...
that way it matches the file_name and allows to rename the file...
to do that after typing file_name.dbf(after typing f in dbf) press contrl j. that generates second line and allows to rename. It worked to select the file from dba_data_files too.
I did not mention all the pre steps like shutdown/startup mount etc...because we all know that. And also we know renaming is possible in 9i in open mode too.
Thanks,
Thanigaivasan.
-
hello...
very simple....dont do much..just do simple stuffs.
make the tablespace readonly or if you can shutdown do it.
copy the file from wrongfilename to newfile name.
bring the database up if you have shutdown or else make the tablespace online.
Take the file#(file number)
then rename with the file number.
like
alter database rename 1 to index1.dbf
this will work.
cheers
-
Hi Bolero,
The renaming in open mode is a trick...Actual steps are..
1.alter tablespace XYZ read only;
2.cp the file to new file in OS level
3.alter tablespace XYZ offline
4. aleter database rename datafile 'file_name' to 'new_file_name';
5.alter tablespace XYZ online;
6. alter tablespace XYZ read write;
7. Check the status of XYZ tablespace by querying dba_tablespaces.
8. Check file status from dba_data_files.
we cannot rename datafile without making the tablespace offline...in open mode.
Did you try renaming the datafile using file# any time?. IS this the correct syntax please?.
Thanks,
Thanigaivasan.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|