5 Replies Latest reply: Feb 18, 2013 9:02 AM by Aman.... RSS

    Deferred Doubt ??

    845641
      what is so special about deferred here ? Is it applicable for other parameters also ? For me alter system set audit_file_dest = '/u01/mydir ' deferred WORKS but not alter system set audit_file_dest = '/u01/mydir ' SCOPE=SPFILE, WHY ???????
        • 1. Re: Deferred Doubt ??
          sb92075
          842638 wrote:
          what is so special about deferred here ? Is it applicable for other parameters also ? For me alter system set audit_file_dest = '/u01/mydir ' deferred WORKS but not alter system set audit_file_dest = '/u01/mydir ' SCOPE=SPFILE, WHY ???????
          How do I ask a question on the forums?
          SQL and PL/SQL FAQ

          how can we reproduce what you report?

          Handle:     842638
          Status Level:     Newbie
          Registered:     Mar 8, 2011
          Total Posts:     169
          Total Questions:     82 (55 unresolved)


          why so many unanswered questions?
          • 2. Re: Deferred Doubt ??
            Nicolas.Gasparotto
            In the alter system command, if you don't specify the scope it is going to memory+spfile. Deferred is regarding the memory. If you specify a scope to spfile, it's only be taken in account on the next database startup onwards, no need to defer.

            Nicolas.
            • 3. Re: Deferred Doubt ??
              Fran
              You are using the same parameter, and of course it will work
              SQL> alter system set audit_file_dest ='C:\USERS\MIBBDD\ADMIN\FRAN\ADUMP' DEFERRED;
              
              Sistema modificado.
              
              SQL> alter system set audit_file_dest ='C:\USERS\MIBBDD\ADMIN\FRAN\ADUMP' DEFERRED SCOPE=SPFILE;
              
              Sistema modificado.
              
              SQL> alter system set audit_file_dest ='C:\USERS\MIBBDD\ADMIN\FRAN\ADUMP' SCOPE=SPFILE;
              
              Sistema modificado.
              if you check in documentation:
              http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams004.htm
              you can see:
              Some initialization parameters can be modified using the ALTER SESSION or ALTER SYSTEM statements while an instance is running. Use the following syntax to modify initialization parameters:
              
              ALTER SESSION SET parameter_name = value
              ALTER SYSTEM SET parameter_name = value [DEFERRED]
              • 4. Re: Deferred Doubt ??
                845641
                Thanks fran, i read the doc and it says alter system without deferred means for connected sessions and but with deferred means for next connecting sessions onwards this parameter will be reflected without bouncing the database.
                • 5. Re: Deferred Doubt ??
                  Aman....
                  842638 wrote:
                  what is so special about deferred here ? Is it applicable for other parameters also ? For me alter system set audit_file_dest = '/u01/mydir ' deferred WORKS but not alter system set audit_file_dest = '/u01/mydir ' SCOPE=SPFILE, WHY ???????
                  From http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams004.htm
                  The ALTER SYSTEM ... DEFERRED statement does not modify the global value of the parameter for existing sessions, but the value will be modified for future sessions that connect to the database. The value of the following initialization parameters can be changed with ALTER SYSTEM ... DEFERRED:
                  Sorry, before posting, I didn't refresh the page and Fran has already posted the same link. Apologies for the duplicate reply.

                  Aman....

                  Edited by: Aman.... on Feb 18, 2013 8:32 PM