oracle

oracle session与sql历史查询

session

记录session信息的动态视图为

SQL> desc v$session
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SADDR                                              RAW(8)
 SID                                                NUMBER
 SERIAL#                                            NUMBER
 AUDSID                                             NUMBER
 PADDR                                              RAW(8)
 USER#                                              NUMBER
 USERNAME                                           VARCHAR2(30)
 COMMAND                                            NUMBER
 OWNERID                                            NUMBER
 TADDR                                              VARCHAR2(16)
 LOCKWAIT                                           VARCHAR2(16)
 STATUS                                             VARCHAR2(8)
 SERVER                                             VARCHAR2(9)
 SCHEMA#                                            NUMBER
 SCHEMANAME                                         VARCHAR2(30)
 OSUSER                                             VARCHAR2(30)
 PROCESS                                            VARCHAR2(24)
 MACHINE                                            VARCHAR2(64)
 PORT                                               NUMBER
 TERMINAL                                           VARCHAR2(30)
 PROGRAM                                            VARCHAR2(48)
 TYPE                                               VARCHAR2(10)
 SQL_ADDRESS                                        RAW(8)
 SQL_HASH_VALUE                                     NUMBER
 SQL_ID                                             VARCHAR2(13)
 SQL_CHILD_NUMBER                                   NUMBER
 SQL_EXEC_START                                     DATE
 SQL_EXEC_ID                                        NUMBER
 PREV_SQL_ADDR                                      RAW(8)
 PREV_HASH_VALUE                                    NUMBER
 PREV_SQL_ID                                        VARCHAR2(13)
 PREV_CHILD_NUMBER                                  NUMBER
 PREV_EXEC_START                                    DATE
 PREV_EXEC_ID                                       NUMBER
 PLSQL_ENTRY_OBJECT_ID                              NUMBER
 PLSQL_ENTRY_SUBPROGRAM_ID                          NUMBER
 PLSQL_OBJECT_ID                                    NUMBER
 PLSQL_SUBPROGRAM_ID                                NUMBER
 MODULE                                             VARCHAR2(64)
 MODULE_HASH                                        NUMBER
 ACTION                                             VARCHAR2(64)
 ACTION_HASH                                        NUMBER
 CLIENT_INFO                                        VARCHAR2(64)
 FIXED_TABLE_SEQUENCE                               NUMBER
 ROW_WAIT_OBJ#                                      NUMBER
 ROW_WAIT_FILE#                                     NUMBER
 ROW_WAIT_BLOCK#                                    NUMBER
 ROW_WAIT_ROW#                                      NUMBER
 TOP_LEVEL_CALL#                                    NUMBER
 LOGON_TIME                                         DATE
 LAST_CALL_ET                                       NUMBER
 PDML_ENABLED                                       VARCHAR2(3)
 FAILOVER_TYPE                                      VARCHAR2(13)
 FAILOVER_METHOD                                    VARCHAR2(10)
 FAILED_OVER                                        VARCHAR2(3)
 RESOURCE_CONSUMER_GROUP                            VARCHAR2(32)
 PDML_STATUS                                        VARCHAR2(8)
 PDDL_STATUS                                        VARCHAR2(8)
 PQ_STATUS                                          VARCHAR2(8)
 CURRENT_QUEUE_DURATION                             NUMBER
 CLIENT_IDENTIFIER                                  VARCHAR2(64)
 BLOCKING_SESSION_STATUS                            VARCHAR2(11)
 BLOCKING_INSTANCE                                  NUMBER
 BLOCKING_SESSION                                   NUMBER
 FINAL_BLOCKING_SESSION_STATUS                      VARCHAR2(11)
 FINAL_BLOCKING_INSTANCE                            NUMBER
 FINAL_BLOCKING_SESSION                             NUMBER
 SEQ#                                               NUMBER
 EVENT#                                             NUMBER
 EVENT                                              VARCHAR2(64)
 P1TEXT                                             VARCHAR2(64)
 P1                                                 NUMBER
 P1RAW                                              RAW(8)
 P2TEXT                                             VARCHAR2(64)
 P2                                                 NUMBER
 P2RAW                                              RAW(8)
 P3TEXT                                             VARCHAR2(64)
 P3                                                 NUMBER
 P3RAW                                              RAW(8)
 WAIT_CLASS_ID                                      NUMBER
 WAIT_CLASS#                                        NUMBER
 WAIT_CLASS                                         VARCHAR2(64)
 WAIT_TIME                                          NUMBER
 SECONDS_IN_WAIT                                    NUMBER
 STATE                                              VARCHAR2(19)
 WAIT_TIME_MICRO                                    NUMBER
 TIME_REMAINING_MICRO                               NUMBER
 TIME_SINCE_LAST_WAIT_MICRO                         NUMBER
 SERVICE_NAME                                       VARCHAR2(64)
 SQL_TRACE                                          VARCHAR2(8)
 SQL_TRACE_WAITS                                    VARCHAR2(5)
 SQL_TRACE_BINDS                                    VARCHAR2(5)
 SQL_TRACE_PLAN_STATS                               VARCHAR2(10)
 SESSION_EDITION_ID                                 NUMBER
 CREATOR_ADDR                                       RAW(8)
 CREATOR_SERIAL#                                    NUMBER
 ECID                                               VARCHAR2(64)

拣选常用字段进行展示(加粗字体可见sys用户于15年6月8日登陆)

sql语句:

select sid,username,status,logon_time
from v$session;

 

具体执行:

SQL> select sid,username,status,logon_time
  2  from v$session;

       SID USERNAME                       STATUS   LOGON_TIM
---------- ------------------------------ -------- ---------
         1                                ACTIVE   08-JUN-15
         2                                ACTIVE   08-JUN-15
         3                                ACTIVE   08-JUN-15
         4                                ACTIVE   08-JUN-15
         5                                ACTIVE   08-JUN-15
         6                                ACTIVE   08-JUN-15
         7                                ACTIVE   08-JUN-15
         8                                ACTIVE   08-JUN-15
         9                                ACTIVE   08-JUN-15
        11                                ACTIVE   08-JUN-15
        15                                ACTIVE   08-JUN-15

       SID USERNAME                       STATUS   LOGON_TIM
---------- ------------------------------ -------- ---------
        17                                ACTIVE   08-JUN-15
       125 SYS                            ACTIVE   08-JUN-15
       126                                ACTIVE   08-JUN-15
       127                                ACTIVE   08-JUN-15
       128                                ACTIVE   08-JUN-15
       129                                ACTIVE   08-JUN-15
       130                                ACTIVE   08-JUN-15
       131                                ACTIVE   08-JUN-15
       132                                ACTIVE   08-JUN-15
       136                                ACTIVE   08-JUN-15
       137                                ACTIVE   08-JUN-15

22 rows selected.

sql 历史

v$sql及v$sqlarea存放着统计信息在调优时使用居多,但其sql是不全的,如果想获得完整的sql需使用v$sqltext。

关于这三个视图的区别与用途见引用文章:http://blog.itpub.net/38542/viewspace-885839/

正在执行的sql

sql语句:

select a.username, a.sid,b.SQL_TEXT, b.SQL_FULLTEXT
from v$session a, v$sqlarea b
where a.sql_address = b.address;

具体执行:

SQL> column username format a10
SQL> column sid format a10
SQL> column sql_text format a20
SQL> column sql_fulltext format a20
SQL> select a.username, a.sid,b.SQL_TEXT, b.SQL_FULLTEXT
  2  from v$session a, v$sqlarea b
  3  where a.sql_address = b.address;

USERNAME          SID SQL_TEXT             SQL_FULLTEXT
---------- ---------- -------------------- --------------------
SYS        ########## select a.username, a select a.username, a
                      .sid,b.SQL_TEXT, b.S .sid,b.SQL_TEXT, b.S
                      QL_FULLTEXT from v$s QL_FULLTEXT
                      ession a, v$sqlarea  from v$session a, v$
                      b where a.sql_addres sqlarea
                      s = b.address

执行过的sql

sql语句:

#查询ARCHIVE用户的包含“select 1”的SQL
select sql_text,last_active_time from v$sqlarea t where t.PARSING_SCHEMA_NAME in ('ARCHIVE') and sql_text like '%select 1%';
#查询ARCHIVE用户执行的命令,根据执行时间降序排列
select sql_text from v$sqlarea t where t.PARSING_SCHEMA_NAME in ('ARCHIVE') order by t.LAST_ACTIVE_TIME desc;
#查询所有执行过的sql:“SELECT 1 FROM DUAL”
select sql_text,sql_id from v$sqltext where sql_text like 'SELECT 1 FROM DUAL';

不再演示。

 

 

 

oracle后台进程

实例与server的概念:

Oracle实例:

Oracle实例包括一组后台进程以及这些进程所共享的内存,这些后台进程及其共享内存合起来就构成了Oracle实例.

Oracle数据库服务器:

Oracle实例和数据库合起来又构成了Oracle数据库服务器.

后台进程

1. DBWR 数据库写入进程
DBWR The Database Writer Process 进程负责将脏数据块从数据缓存(database block buffer)写回磁盘.当一个事务修改数据
块中的数据以后,不需要立即将数据块写回磁盘。由于Oracle所采用的先进机制,修改后的数据可以不用立即写回,并且及时出现故障
也不会丢失。因此,DBWR可以采取更有效写回方式,而不用再事务提交完之后立即写回。DBWR通常定时写回数据,除非数据缓冲区
需要清空或已满。
数据写回时,采用了最近最少使用原则(least-recently-used).对于支持异步I/O的系统,只需使用一个BWR进程即可,对不支持
异步IO的可以通过增加DBWR的个数来提升效率。

服务器参数文件SPFILE的参数:DB_WRITER_PROCESSES。修改它可以允许使用多个DBWR进程,但是不能超过物理cpu核数。
2. LGWR 日志写入进程
LGWR (the log writer process)负责将日志缓冲区中的数据写入重做日志。由于COMMIT操作依赖于LGWR写回日志(commit操
作触发LGWR将日志缓冲区数据写入UNDO日志),因此系统性能容易受到LGWR的影响。
3. PMON 进程监视进程
PMON(the Process Monitor Process)进程负责监视数据库的处理情况,并负责清除死掉的进程。PMON还负责重启失败的调度进
程(dispatcher process)
4. SMON 系统监视进程
SMON(the System Monitor Process)进程负责在实例启动时恢复实例,包括清除临时段以及恢复因系统崩溃而中断的事物。
SMON还可以通过合并空闲分区的方式清除数据库中的碎片
5.CKPT 检查点进程
CKPT(the Checkpoint Process)进程负责向DBWR进程发送信号,要求执行一次检查点,并更新数据库的所有数据和控制文件。
CheckPoint(检查点),是指有DBWR进程将所有修改过的数据缓冲区写回数据文件。CKPT是可选的,如果没有CKPT进程,则有LGWR代
行这些任务。
6. ARCHn 归档进程
ARCH(the Archiver Process )负责将再现重做日志复制到归档存储器。进档RDBMS运行在ARCHIVELOG模式时ARCH才有效,如果
系统没有运行在ARCHIVELOG模式,则系统失效后可能无法恢复。最后让系统运行在ARCHIVELOG模式下,此时归档进程可能有多个。
SQL>archive log list //查看是否为归档模式
可以再数据库Mount 模式下开启归档
SQL>alter database archivelog; //开启
SQL>alter database noarchivelog;//关闭
7. RECO 恢复进程
RECO (the Recovery Process)进程用于清除分布式数据库中的未决(pending)事务,它负责分布式事务中本地部分的提交与回滚
8. Dnnn 调度进程
如果选择了Multithreaded Server选项,则对使用的每个通信协议至少有一个调度进程。调度进程(the Dispatcher Process,Dnnn)
负责将用户进程的请求转发到一个可用的共享服务器进程,并负责将用户进程的请求转发到一个可用的共享服务器进程,并负责回送响应
消息。
9. Snnn 共享服务器进程
当使用共享服务器配置或MTS时,会用到共享服务器进程(the Shared Servers Process ,Snnn).这些进程如同专用服务器进程
(Dedicated Server Process),但可以被多个用户
共享。
10. LMS Oracle RAC 锁管理服务
使用RAC时,该服务用语管理集群中不同节点间的锁.
11. QMNn 队列管理进程
使用高级队列选项时(advanced queueing option),队列管理进程 (Queue Manager Process )用于管理作业队列,队列数目可以
达到10个,QMN0——QMN9

 

oracle rac 常用命令

原文:http://blog.sina.com.cn/s/blog_683e8ddf0100p0mm.html

1、查看RAC数据库整体状况
$srvctl status database -d orcl
注:orcl为数据库名,可通过查询2获得
2、查询所有配置的数据库
$srvctl config database
3、显示RAC数据库的配置
srvctl config database -d orcl
4、显示指定集群数据库的所有服务
srvctl config service -d orcl
5、查询指定实例状况
srvctl status instance -d orcl -i orcl2

注:orcl2为实例名
6、查询在数据库全局命名服务的状态
srvctl status service -d orcl -s orcltest

注:orcltest为服务名
7、查询指定节点上应用程序的状态
srvctl status nodeapps -n linux1

linux为集群中的一个节点名
8、ASM实例的状态
srvctl status asm -n linux1
注:ASM:Automatic Storage Management
9、查询节点应用程序的配置-(VIP、GSD、ONS、监听器)
srvctl config nodeapps -n linux1 -a -g -s -l
注:VIP:virtual IP
   GSD:Global Service Daemon
   ONS:Oracle Notification Service
10、查询集群中所有正在运行的实例
SELECT
inst_id
, instance_number inst_no
, instance_name inst_name
, parallel
, status
, database_status db_status
, active_state state
, host_name host
FROM gv$instance
ORDER BY inst_id;

其他以后再补充。

oracle 查看用户、表空间及权限

一、查看用户的表空间

表:dba_users

SQL> desc dba_users;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERNAME                                  NOT NULL VARCHAR2(30)
 USER_ID                                   NOT NULL NUMBER
 PASSWORD                                           VARCHAR2(30)
 ACCOUNT_STATUS                            NOT NULL VARCHAR2(32)
 LOCK_DATE                                          DATE
 EXPIRY_DATE                                        DATE
 DEFAULT_TABLESPACE                        NOT NULL VARCHAR2(30)
 TEMPORARY_TABLESPACE                      NOT NULL VARCHAR2(30)
 CREATED                                   NOT NULL DATE
 PROFILE                                   NOT NULL VARCHAR2(30)
 INITIAL_RSRC_CONSUMER_GROUP                        VARCHAR2(30)
 EXTERNAL_NAME                                      VARCHAR2(4000)
 PASSWORD_VERSIONS                                  VARCHAR2(8)
 EDITIONS_ENABLED                                   VARCHAR2(1)
 AUTHENTICATION_TYPE                                VARCHAR2(8)

SQL> select username,default_tablespace from dba_users;

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
PATROL                         TBS_MONITOR

二、查看表空间

表:dba_data_files

SQL> desc dba_data_files
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FILE_NAME                                          VARCHAR2(513)
 FILE_ID                                            NUMBER
 TABLESPACE_NAME                                    VARCHAR2(30)
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 STATUS                                             VARCHAR2(9)
 RELATIVE_FNO                                       NUMBER
 AUTOEXTENSIBLE                                     VARCHAR2(3)
 MAXBYTES                                           NUMBER
 MAXBLOCKS                                          NUMBER
 INCREMENT_BY                                       NUMBER
 USER_BYTES                                         NUMBER
 USER_BLOCKS                                        NUMBER
 ONLINE_STATUS                                      VARCHAR2(7)

SQL> select file_name,tablespace_name,bytes,maxbytes from dba_data_files where tablespace_name='TBS_MONITOR';

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME                     BYTES   MAXBYTES
------------------------------ ---------- ----------
+DGDATA/bj4adb/datafile/tbs_cipa
TBS_MONITOR                    1073741824 1.0737E+10

三、查看用户权限(注意用户名大写,否则查不到)

表:dba_sys_privs

SQL> DESC DBA_SYS_PRIVS;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 GRANTEE                                   NOT NULL VARCHAR2(30)
 PRIVILEGE                                 NOT NULL VARCHAR2(40)
 ADMIN_OPTION                                       VARCHAR2(3)

SQL> select * from dba_sys_privs where grantee='PATROL';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
PATROL                         UNLIMITED TABLESPACE                     NO
PATROL                         CREATE TABLESPACE                        NO
PATROL                         CREATE VIEW                              NO
PATROL                         CREATE TABLE                             NO
PATROL                         CREATE SESSION                           NO

四、查看用户角色(注意用户名大写,否则查不到)

表:dba_role_privs

SQL> DESC DBA_ROLE_PRIVS;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 GRANTEE                                            VARCHAR2(30)
 GRANTED_ROLE                              NOT NULL VARCHAR2(30)
 ADMIN_OPTION                                       VARCHAR2(3)
 DEFAULT_ROLE                                       VARCHAR2(3)

SQL> select * from dba_role_privs where grantee='PATROL';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
PATROL                         DBA                            NO  YES

 

oracle 使用expdb导出数据

一、查询oracle目录对象对应的目录

查询 dba_directories,获取对象对应的目录信息。

SQL> desc dba_directories;
SQL> select DIRECTORY_NAME from dba_directories;

DIRECTORY_NAME
------------------------------
DMPDIR
DMP_GEJ
XMLDIR
DATA_PUMP_DIR
ORACLE_OCM_CONFIG_DIR

SQL> select DIRECTORY_PATH from dba_directories where DIRECTORY_NAME='DMPDIR';

DIRECTORY_PATH
--------------------------------------------------------------------------------
/exp

于是,dump的目录就是/exp

二、编辑dump脚本

$ cd /exp
$ vi expdp_20141030.sh

#插入以下内容
#登陆expdb软件
expdp system/username@dbname 
#标记需要导出的用户名
schemas=(hehe,heihei,haha) 
#导出的目录
directory=DMPDIR 
#导出的文件名
dumpfile=expdp_20141030.dmp
#到处操作的日志名 
logfile=expdp_20141030.log

 

三、执行并查看

 

 

 

Install oracle 11g client

一、创建用户组及用户

[root@template ~]# groupadd oinstall
[root@template ~]# groupadd dba
[root@template ~]# useradd -g oinstall -G dba oracle
[root@template ~]# passwd oracle

二、创建安装目录

[root@template ~]# mkdir /oracle
[root@template ~]# lvcreate -L 20G -n lv_oracle vg0
[root@template ~]# mkfs.ext4 /dev/vg0/lv_oracle 
[root@template ~]# vim /etc/fstab 
添加一行:
/dev/mapper/vg0-lv_oracle /                       ext4    defaults        1 1

[root@template ~]# mount /dev/vg0/lv_oracle /oracle
[root@template ~]# mkdir -p /oracle/product/11.2/db_1
[root@template ~]# chown -R oralce:oinstall /oracle

三、安装

直接运行解压后的runInstaller。

关于oracle 安装介质的说明:

http://bananalighter.com/oracle-installer-7-zips/

四、环境变量

[root@template ~]# vim /home/oracle/.bash_profile 
添加:
# User specific environment and startup programs
export ORACLE_BASE=/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2/db_1

PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin

export PATH

五、TNS

mkdir -p $ORACLE_HOME/network/admin
cd $ORACLE_HOME/network/admin
vi tnsnames.ora 添加如下内容并且保存
 
ODS18 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = haha)
    )
  )

六、血泪教训

就不要像下面这样屌屌的自己选择自定义安装了,出各种奇怪的错。

QQ截图20141028154757

老老实实的打包安装,或者自己单独装以下三个包:

oracle-instantclient-basic-10.2.0.5-1.x86_64.rpm
oracle-instantclient-sqlplus-10.2.0.5-1.x86_64.rpm
oracle-instantclient-devel-10.2.0.5-1.x86_64.rpm

参考:http://blog.sina.com.cn/s/blog_4ed7040c01011xv6.html

 

oracle 安装介质 7 个包

Oracle11g有多张安装光盘:
文件名称                                                                           说明
p102025301120——Linux-x86-64_1of7.zip             database安装介质
p102025301120——Linux-x86-64_2of7.zip             database安装介质
p102025301120——Linux-x86-64_3of7.zip             grid安装介质
p102025301120——Linux-x86-64_4of7.zip             client安装介质
p102025301120——Linux-x86-64_5of7.zip             gateways安装介质
p102025301120——Linux-x86-64_6of7.zip             example
p102025301120——Linux-x86-64_7of7.zip             deinstall
参考:http://blog.itpub.net/28716724/viewspace-756073/