1 2 Previous Next 29 Replies Latest reply on Jul 14, 2011 4:44 PM by jgarry Go to original post
      • 15. Re: Resizing or switching UNDO-Tablespace
        773819
        Hi

        As per me its not oracle problem.In windows to drop os level oracle datafile you need to shutdown database and then only you can drop that file.In linux you can drop it directly while database is runnig.so my suggestion will be shut down your database if you can and then drop that perticular datafile.
        • 16. Re: Resizing or switching UNDO-Tablespace
          S-Max
          @CKPT,

          the tablespace UNDOTBS1 was successfully dropped on oracle level wird statement:
          SQL> DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;
          
          Tablespace dropped.
          The UNDOTBS1 does not exests more:
          SQL> select * from v$tablespace;
          
                 TS# NAME                           INC BIG FLA
          ---------- ------------------------------ --- --- ---
                   0 SYSTEM                         YES NO  YES
                   2 SYSAUX                         YES NO  YES
                   3 TEMP                           YES NO  YES
                   4 USERS                          YES NO  YES
                   5 BASIC_DATA                     YES NO  YES
                   6 BASIC_IDX                      YES NO  YES
                   7 MOST                           YES NO  YES
                   8 PERS_D                         YES NO  YES
                   9 PERS_IDX                       YES NO  YES
                  10 R_DATA                         YES NO  YES
                  11 R_IDX                          YES NO  YES
          
                 TS# NAME                           INC BIG FLA
          ---------- ------------------------------ --- --- ---
                  12 T_DATA                         YES NO  YES
                  13 T_IDX                          YES NO  YES
                  14 T_D_G                          YES NO  YES
                  15 T_IDX_G                        YES NO  YES
                  16 US_DATA                        YES NO  YES
                  17 TMP                            YES NO  YES
                  19 T_DFC                          YES NO  YES
                  21 MEFS                           YES NO  YES
                  22 APEX                           YES NO  YES
                  23 UNDOTBS2                       YES NO  YES
          
          21 rows selected.
          Therefore I say "the tablespace was dropped successfully"!

          But on the operating system (Windows 2003) level i can see the datafile in the directory and cannot delete it...

          There is "Avira Antivir" running.

          Edited by: user610409 on 14.07.2011 00:22
          • 17. Re: Resizing or switching UNDO-Tablespace
            S-Max
            @amit*,

            yes this can be the solution. But actually I cannot restart the database...
            • 18. Re: Resizing or switching UNDO-Tablespace
              773819
              then you can make that file offline by using command and then manually drop that file.
              • 19. Re: Resizing or switching UNDO-Tablespace
                S-Max
                How can I make the file offline in the database, that don't know the datafile?
                • 20. Re: Resizing or switching UNDO-Tablespace
                  CKPT
                  But on the operating system (Windows 2003) level i can see the datafile in the directory and cannot delete it...
                  so that undotbs01 contains only one datafile? or remainig all are deleted.
                  stop antivirus for a while, and then try to drop the file.
                  • 21. Re: Resizing or switching UNDO-Tablespace
                    S-Max
                    Correct, the UNDOTBS1 contains only one datafile.

                    I will contact the customer Windows Admin and then update the thread.

                    Thank you!
                    • 22. Re: Resizing or switching UNDO-Tablespace
                      773819
                      you can make that datafile offline in this way

                      ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' OFFLINE;
                      • 23. Re: Resizing or switching UNDO-Tablespace
                        S-Max
                        I get ORA-01516 for this statement.

                        The database does not use the datafile.
                        • 24. Re: Resizing or switching UNDO-Tablespace
                          Fran
                          ORA-01516: nonexistent log file, datafile, or tempfile "string"
                          Cause:      An attempt was made to use ALTER DATABASE to rename a log file, datafile, or tempfile; or to change attributes of a datafile or tempfile (e.g., resize, autoextend, online/offline, etc.); or to re-create a datafile. The attempt failed because the specified file is not known to the database's control file or is not of a type supported by the request.
                          Action:      Specify the name or number of an existing file of the correct type, as appropriate. Check the relevant V$ table for a list of possible files.

                          Maybe you delete the datafile or your rute is incorrect. Please check it.

                          Even check:
                          Error ORA-01516

                          And of course you must read:
                          http://download.oracle.com/docs/cd/B13789_01/server.101/b10739/undo.htm
                          • 25. Re: Resizing or switching UNDO-Tablespace
                            585179
                            user610409 wrote:
                            I get ORA-01516 for this statement.

                            The database does not use the datafile.

                            Hi,

                            The undo has been removed from database but it seems some process still holding the file hence you can't delete it manually.

                            Use unlocker program to see which process holding it, review if it can be killed, then manually delete the undo file


                            Cheers
                            • 26. Re: Resizing or switching UNDO-Tablespace
                              773819
                              hi

                              I think you have used this statement as it is.just change the file name and path as per your file directory and name
                              • 27. Re: Resizing or switching UNDO-Tablespace
                                S-Max
                                No, I have put the correct filename. I'm not so stupid ; - )

                                But I have restarted the database. After it I was able to delete the file from operating system directory.

                                Many thanks to all!
                                • 28. Re: Resizing or switching UNDO-Tablespace
                                  797960
                                  Sometimes restart can be the only solution when your database hosted on windows system. The command which you are running for taking a file offline was giving you a correct error as it was aleready deleted with tablespace. I am sure if you would had taken the trace of your controlfile, you couldnt see the undo file as it was already deleted with tablespace along with the entry had also removed from controlfile.
                                  • 29. Re: Resizing or switching UNDO-Tablespace
                                    jgarry
                                    user610409 wrote:
                                    No, I have put the correct filename. I'm not so stupid ; - )
                                    But you never asked the smart question: why is the undo that size?

                                    If it is because of a periodic or uncommon but necessary procedure, you would be better off leaving it there, rather than discovering later you needed it.

                                    If it was a mistake, how did you determine that it was a mistake? Does anyone involved understand what an undo tablespace does and why one would want it to be large? Mine is 32G and I still have to do resource limiting things. Right now 108M is used in it.

                                    Would you tell your mechanic to disconnect your gas tank because there is a warning light on the dash? Should he listen to you?

                                    >
                                    But I have restarted the database. After it I was able to delete the file from operating system directory.

                                    Many thanks to all!
                                    1 2 Previous Next