oracle 复习手记 01

SQL> desc v$parameter
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NUM                                                NUMBER
 NAME                                               VARCHAR2(64)
 TYPE                                               NUMBER
 VALUE                                              VARCHAR2(512)
 ISDEFAULT                                          VARCHAR2(9)
 ISSES_MODIFIABLE                                   VARCHAR2(5)
 ISSYS_MODIFIABLE                                   VARCHAR2(9)
 ISMODIFIED                                         VARCHAR2(10)
 ISADJUSTED                                         VARCHAR2(5)
 DESCRIPTION                                        VARCHAR2(64)
 UPDATE_COMMENT                                     VARCHAR2(255)

SQL>

 

SQL> col name format a20;

SQL> col value format a30;
SQL> 

 

查找spfille位置:

[oracle@ora9i ~]$ cd $ORACLE_HOME/dbs
[oracle@ora9i dbs]$ ll
total 40
-rw-r--r--  1 oracle oinstall 12920 Mar  8  2002 initdw.ora
-rw-r--r--  1 oracle oinstall  8385 Mar  9  2002 init.ora
-rw-r--r--  1 oracle oinstall     0 Aug 22  2011 initora9i.ora
-rw-rw----  1 oracle oinstall    24 Apr 20 23:55 lkORA9I
-rwSr-----  1 oracle oinstall  1536 Aug 22  2011 orapwora9i
-rw-r-----  1 oracle oinstall  2560 Aug 22  2011 spfileora9i.ora
[oracle@ora9i dbs]$

[oracle@ora9i dbs]$   env |grep ORACLE
ORACLE_SID=ora9i
ORACLE_BASE=/u01/oracle
ORACLE_HOME=/u01/oracle/92

 

SQL> show parameter fast_;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     300
fast_start_parallel_rollback         string      LOW
SQL> alter system set fast_start_mttr_target=250;

System altered.

 

 

SQL> select addr,status from v$transaction; //查未提交的事务

SQL> show parameter dump; //查参数为dump

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_core_dump                 string      partial
background_dump_dest                 string      /u01/oracle/admin/ora9i/bdump
core_dump_dest                       string      /u01/oracle/admin/ora9i/cdump
max_dump_file_size                   string      UNLIMITED
shadow_core_dump                     string      partial
user_dump_dest                       string      /u01/oracle/admin/ora9i/udump

SQL> desc dba_users; //查看用户表

SQL> select username,account_status from dba_users; //查看数据库用户状态

SQL> alter user hr account unlock; //解锁用户

SQL> alter user hr identified by hr; //重新设置密码

 

 

SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45

SELECT s.inst_id,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program
FROM   gv$session s
       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND';

   INST_ID        SID    SERIAL# SPID       USERNAME   PROGRAM
---------- ---------- ---------- ---------- ---------- ---------------------------------------------
         1         30         15 3859       TEST       [e-mail] (TNS V1-V3)
         1         23        287 3834       SYS        [e-mail] (TNS V1-V3)
         1         40        387 4663                  [e-mail] (J000)
         1         38        125 4665                  [e-mail] (J001)

SQL>

ALTER SYSTEM KILL SESSION

The basic syntax for killing a session is shown below.

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';