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
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_
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
A. The users with DBA privilege remain connected, and others are
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
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
14. A. STARTUP FORCE will terminate the current instance and start
up the database. It is equivalent to issuing SHUTDOWN ABORT and
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
4. A. The alert log stores information about block corruption errors,
internal errors, and the non-default initialization parameters used at
instance start-up. The alert log also records information about database
start-up, shutdown, archiving, recovery, tablespace modifications,
undo segment modifications, and data file modifications.