# OCP 考试日记 20041012 031

ALTER SYSTEM SET timed_statistics =TRUE DEFERRED;   设置统计信息收集参数!
SELECT name, value  FROM v$parameter WHERE isdefault = ‘FALSE’; 查询已经修改的参数! You can change the value of a parameter system-wide by using the ALTER SYSTEM command. A value of DEFERRED or IMMEDIATE in the ISSYS_MODIFIABLE column shows that the parameter can be dynamically changed by using the command ALTER SYSTEM. DEFERRED indicates that the change you make does not take effect until a new session is started. The existing sessions will use the current value. IMMEDIATE indicates that as soon as you change the value of the parameter, it is available to all sessions in the instance. A session can be a job or a task that Oracle manages. When you log in 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 modifying a parameter by using ALTER SYSTEM. SQL> ALTER SYSTEM SET log_archive_dest If you want the user to complete the current transaction and then terminate their session, you can use the DISCONNECT SESSION option of the ALTER SYSTEM command. If the session has no pending or active transactions, this command 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 or DISCONNECT SESSION to roll back ongoing transactions, release all session locks, 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 log files and the changed data in the database buffer cache to the data files, and closes the data files and redo log files. The control file remains open, but the database is not available for normal operations. After closing the database, the instance dismounts the database. The control file is closed at this time. The memory allocated and the background processes still remain. The final stage is the instance shutdown. The SGA is removed from memory and the background processes are terminated when the instance is shut down. SHUTDOWN ABORT When any of the other three shutdown options does not work, you can bring down the database abruptly by using the SHUTDOWN ABORT command. An instance recovery is needed when you start up the database next time. When you issue SHUTDOWN ABORT, Oracle does the following:  Terminates all current SQL statements that are being processed  Disconnects all connected users  Terminates the instance immediately  Will not roll back uncommitted transactions When the database is started up after a SHUTDOWN ABORT, Oracle has to roll 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 files in the operating system: DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_ LOG_DEST_n. The parameter DB_CREATE_FILE_DEST specifies the default location for new datafiles. The actual operating system file is created with the prefix ora_ and a suffix of .dbf. If the CREATE DATABASE command (or any other commands that use the OMF initialization parameters) fails, the associated data files are removed from the server file system. The parameter DB_CREATE_ONLINE_LOG_DEST_n specifies as many as five locations for online redo log files and control files. The online redo log files have a suffix of .log, and the control files have a suffix of .ctl. You don’t have to use both parameters, and you can dynamically change the values of these parameters with the ALTER SYSTEM command. The dynamic view V$PWFILE_USERS has the username and a value
of TRUE in column SYSDBA if the SYSDBA privilege is granted, or a value of
TRUE in column SYSOPER if the SYSOPER privilege is granted.

B. Only one file destination is allowed. Control files and redo log files
use the same parameter; the parameter DB_CREATE_ONLINE_LOG_
DEST_n (n can have values from 1 to 5).

18. When you issue the command ALTER SYSTEM ENABLE RESTRICTED
SESSION, what happens to the users who are connected to the
database?
A. The users with DBA privilege remain connected, and others are
disconnected.
B. The users with RESTRICTED SESSION remain connected, and
others are disconnected.
*C. Nothing happens to the existing users. They can continue working.
D. The users are all

16. What is the primary benefit of using an SPFILE to maintain the
parameter file?
A. The SPFILE can be mirrored across several drives, unlike PFILEs.
*B. Changes to the database configuration can be made persistent
across shutdown and startup.
C. Because the SPFILE is binary, the DBA will be less likely to edit it.
D. The ALTER SYSTEM command cannot modify the contents of an
SPFILE.

14. A. STARTUP FORCE will terminate the current instance and start
up the database. It is equivalent to issuing SHUTDOWN ABORT and
STARTUP OPEN.

11. B. The RESTRICTED SESSION privilege is required to access a database
that is in restricted mode. You start up the database in restricted
mode by using STARTUP RESTRICT, or you change the database to
restricted mode by using ALTER SYSTEM ENABLE RESTRICTED
SESSION.