This discussion is archived
5 Replies Latest reply: Feb 18, 2013 7:02 AM by Aman.... RSS

Deferred Doubt ??

845641 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.... Oracle ACE
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points