14 Replies Latest reply: Jul 19, 2012 1:43 PM by EdStevens RSS

    alter database statement

    945465
      Hi all,
      I am using oracle 10g on linux OS.
      I am trying to rename the database.

      There are around 230 dbf's in the database, and I want to update the location of the datafiles in the control file.
      So, I need to update the controlfile.

      Can you pls help me with an sql statement to update the location of datafiles in the controlfile with a single statement(where all datafiles reside in the same directory --
      From ---- /d01/oracle10g/oradata/dbf to ---- /d012/oraclenew/oradata/newdbf/
      pls help thanx in advance..
        • 1. Re: alter database statement
          asifkabirdba
          This kind of update is not possible in the controlfile. You need to create new tablespace and move objects into the new tablespace and drop the old tablespace.

          Also, you can move datafiles into new mount point using commands which will update the controlfile.



          Regards
          Asif Kabir
          • 2. Re: alter database statement
            Osama_Mustafa
            Check the following
            http://www.oracle-base.com/articles/misc/renaming-or-moving-oracle-files.php

            Osama..
            • 3. Re: alter database statement
              945465
              hi asif,
              do you say that the only way to update the control file is to use the ALTER DATABASE RENAME FILE ...statement?

              and no way to make an update to a control file at one go(where all files reside in a single folder.............)
              • 4. Re: alter database statement
                asifkabirdba
                do you say that the only way to update the control file is to use the ALTER DATABASE RENAME FILE ...statement?


                Ans : Yes

                You don't edit controlfiles manually so this is the only way.


                Regards
                Asif Kabir
                • 5. Re: alter database statement
                  Aman....
                  Are you trying to create a duplicate database of the current one with a new name and a new location?

                  Aman....
                  • 6. Re: alter database statement
                    945465
                    hi Osama,

                    I already know the alter database statement to update a control file.

                    All I want to know is how to make the update with a single sql statement where all files reside in a single folder.

                    pls help.

                    thanx in advance..
                    • 7. Re: alter database statement
                      945465
                      not exactly,
                      but i want to copy all the files to a new location and update the control file.
                      • 8. Re: alter database statement
                        Hemant K Chitale
                        You have to issue a separate ALTER DATABASE command for each datafile. However, these SQL commands can be "generated".
                        Use SQL generating SQL.

                        Something like
                        set pages0
                        spool rename_files.sql
                        select 'alter database rename file  ' || '''' || file_name || '''' || '  to  ' || '''' || replace(file_name,'oracle10g','oraclenew') || '''' || ' ;'
                        from dba_data_files
                        order by 1
                        TEST the query and see that it generates the correct syntax and statements.

                        Hemant K Chitale
                        • 9. Re: alter database statement
                          Aman....
                          So why you can't take a backup of this database and using the backup, create a duplicate database on the new location and then, using the NID command, rename it?

                          Aman....
                          • 10. Re: alter database statement
                            Osama_Mustafa
                            Aman.... wrote:
                            So why you can't take a backup of this database and using the backup, create a duplicate database on the new location and then, using the NID command, rename it?

                            Aman....
                            alot of work i think
                            • 11. Re: alter database statement
                              mBk77
                              just do it wrote:
                              Hi all,
                              I am using oracle 10g on linux OS.
                              I am trying to rename the database.

                              There are around 230 dbf's in the database, and I want to update the location of the datafiles in the control file.
                              So, I need to update the controlfile.

                              Can you pls help me with an sql statement to update the location of datafiles in the controlfile with a single statement(where all datafiles reside in the same directory --
                              From ---- /d01/oracle10g/oradata/dbf to ---- /d012/oraclenew/oradata/newdbf/
                              pls help thanx in advance..
                              You can backup your controlfile to trace
                              Alter database backup controlfile to trace as '/d012/createcontrol.sql'
                              shutdown the db

                              Edit the controlfile manually(change the names and locations of the datafiles)

                              stratup mount

                              run the create controlfile script(trace backup of controlfile) to create the new controlfile..

                              open the database with resetlogs...
                              • 12. Re: alter database statement
                                945465
                                thanks for the idea.
                                I will try it and let you know the result.
                                • 13. Re: alter database statement
                                  945465
                                  hi hemant,
                                  thanks for the statement.
                                  I will try it and check out the results and errors.
                                  • 14. Re: alter database statement
                                    EdStevens
                                    mBk77 wrote:
                                    just do it wrote:
                                    Hi all,
                                    I am using oracle 10g on linux OS.
                                    I am trying to rename the database.

                                    There are around 230 dbf's in the database, and I want to update the location of the datafiles in the control file.
                                    So, I need to update the controlfile.

                                    Can you pls help me with an sql statement to update the location of datafiles in the controlfile with a single statement(where all datafiles reside in the same directory --
                                    From ---- /d01/oracle10g/oradata/dbf to ---- /d012/oraclenew/oradata/newdbf/
                                    pls help thanx in advance..
                                    You can backup your controlfile to trace
                                    Alter database backup controlfile to trace as '/d012/createcontrol.sql'
                                    shutdown the db

                                    Edit the controlfile manually(change the names and locations of the datafiles)

                                    stratup mount
                                    uh, wouldn't that be 'startup NOmount'? with startup mount, you already have the control file open ....

                                    >
                                    run the create controlfile script(trace backup of controlfile) to create the new controlfile..

                                    open the database with resetlogs...