ALTER SYSTEM SET timed_statistics = TRUE DEFERRED; provided statistics collection parameters SELECT name, value FROM v $ parameter WHERE isdefault = 'FALSE';!! Query has been modified parameter You can change the value of a parameter system-wide by using theALTER SYSTEM command. A value of DEFERRED or IMMEDIATE in theISSYS_MODIFIABLE column shows that the parameter can be dynamicallychanged by using the command ALTER SYSTEM. DEFERRED indicates thatthe change you make does not take effect until a new session is started. Theexisting sessions will use the current value. IMMEDIATE indicates that as soonas you change the value of the parameter, it is available to all sessions in theinstance. A session can be a job or a task that Oracle manages. When you login to the database by using SQL * Plus or any client tool , you start a session.Sessions are discussed in the next section. Here is an example of modifyinga parameter by using ALTER SYSTEM.SQL> ALTER SYSTEM SET log_archive_destIf you want the user to complete the current transaction and then term inatetheir session, you can use the DISCONNECT SESSION option of the ALTERSYSTEM command If the session has no pending or active transactions, thiscommand has the same effect as KILL SESSION Here is an example:.. ALTER SYSTEM DISCONNECT SESSION '9,3' POST_TRANSACTION; you can also use the IMMEDIATE clause with the KILL SESSION orDISCONNECT SESSION to roll back ongoing transactions, release all sessionlocks, recover the entire session state, and return control to you immediately.Here are some examples: ALTER SYSTEM DISCONNECT SESSION '9,3' Immediate; alter system kill session '9, 3' immediate;
When closing the database, Oracle writes the redo buffer to the redo logfiles and the changed data in the database buffer cache to the data files, andcloses the data files and redo log files. The control file remains open, butthe database is not available for normal operations. After closing the database, the instance dismounts the database. The control file is closed at thistime. The memory allocated and the background processes still remain.The final stage is the instance shutdown. The SGA is removed frommemory and the background processes are terminated when the instance isshut down.SHUTDOWN ABORTWhen any of the other three shutdown options does not work, you can bringdown the database abruptly by using the SHUTDOWN ABORT command. Aninstance recovery is needed when you start up the database next time. whenyou issue SHUTDOWN ABORT, Oracle Does The Following: Terminates All Current SQL Statements That Are Being Processed Disconnects All Connected Users Terminates THE Instance Immediate ly Will not roll back uncommitted transactionsWhen the database is started up after a SHUTDOWN ABORT, Oracle has toroll back the uncommitted transactions by using the online redo log files.The Oracle Managed Files (OMF) feature of Oracle9i addresses this issue.You can use two New Initialization Parameters to Define The Location of Filesin The OPERATING SYSTEM:
DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n.The parameter DB_CREATE_FILE_DEST specifies the default locationfor new datafiles. The actual operating system file is created with the prefixora_ and a suffix of .dbf. If the CREATE DATABASE command (or any othercommands that use the OMF initialization parameters) fails, the associateddata files are removed from the server file system.The parameter DB_CREATE_ONLINE_LOG_DEST_n specifies as many asfive locations for online redo log files and control files. The online redo logfiles have a suffix of .log, and the control files have a suffix of .ctl.You do not have to use both parameters, and you can dynamically changethe values of these parameters with the ALTER SYSTEM command.The dynamic view V $ PWFILE_USERS has the username and a valueof TRUE in column SYSDBA if the SYSDBA privilege is granted, or a value OFTRUE IN Column Sysoper if The Sysoper Privilege is granted.
