oracle 触发器和索引
来源:互联网 发布:javascript语言精粹pdf 编辑:程序博客网 时间:2024/06/05 17:13
--创建表用于存储登陆或登出的统计信息
CREATE TABLE stats$user_log
(
user_id VARCHAR2 (30),
session_id NUMBER (8),
HOST VARCHAR2 (30),
last_program VARCHAR2 (48),
last_action VARCHAR2 (32),
last_module VARCHAR2 (32),
logon_day DATE,
logon_time VARCHAR2 (10),
logoff_day DATE,
logoff_time VARCHAR2 (10),
elapsed_minutes NUMBER (8)
);
--创建登陆之后的触发器
CREATE OR REPLACE TRIGGER logon_audit_trigger
AFTER LOGON
ON DATABASE
BEGIN
INSERT INTO stats$user_log
VALUES (USER,
SYS_CONTEXT ('USERENV', 'SESSIONID'),
SYS_CONTEXT ('USERENV', 'HOST'),
NULL,
NULL,
NULL,
SYSDATE,
TO_CHAR (SYSDATE, 'hh24:mi:ss'),
NULL,
NULL,
NULL);
END;
/
--创建登出之后的触发器
CREATE OR REPLACE TRIGGER logoff_audit_trigger
BEFORE LOGOFF
ON DATABASE
BEGIN
-- ***************************************************
-- Update the last action accessed
-- ***************************************************
UPDATE stats$user_log
SET last_action =
(SELECT action
FROM v$session
WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = audsid)
WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = session_id;
--***************************************************
-- Update the last program accessed
-- ***************************************************
UPDATE stats$user_log
SET last_program =
(SELECT program
FROM v$session
WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = audsid)
WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = session_id;
-- ***************************************************
-- Update the last module accessed
-- ***************************************************
UPDATE stats$user_log
SET last_module =
(SELECT module
FROM v$session
WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = audsid)
WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = session_id;
-- ***************************************************
-- Update the logoff day
-- ***************************************************
UPDATE stats$user_log
SET logoff_day = SYSDATE
WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = session_id;
-- ***************************************************
-- Update the logoff time
-- ***************************************************
UPDATE stats$user_log
SET logoff_time = TO_CHAR (SYSDATE, 'hh24:mi:ss')
WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = session_id;
-- ***************************************************
-- Compute the elapsed minutes
-- ***************************************************
UPDATE stats$user_log
SET elapsed_minutes = ROUND ( (logoff_day - logon_day) * 1440)
WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = session_id;
END;
/
2、结果样例
[sql] view plain copy print?
--查看用户的登入登出信息
SQL> select * from sys.stats$user_log where rownum<3;
USER_ID SESSION_ID HOST LAST_PROGRAM LAST_MODULE LOGON_DAY LOGON_TIME LOGOFF_DA LOGOFF_TIM ELP_MINS
---------- ---------- --------------- ---------------- ---------------- --------- ---------- --------- ---------- --------
GX_ADMIN 5409517 v2012DB01u JDBC Thin Client JDBC Thin Client 24-OCT-13 12:20:30 24-OCT-13 16:20:30 240
GX_ADMIN 5409518 v2013DB01u JDBC Thin Client JDBC Thin Client 24-OCT-13 12:22:23 24-OCT-13 16:22:30 240
--汇总用户登陆时间
SQL> SELECT user_id, TRUNC (logon_day) logon_day, SUM (elapsed_minutes) total_time
2 FROM sys.stats$user_log
3 GROUP BY user_id, TRUNC (logon_day) ORDER BY 2;
USER_ID LOGON_DAY TOTAL_TIME
------------------------------ --------- ----------
GX_ADMIN 24-OCT-13 960
SYS 24-OCT-13
GX_ADMIN 25-OCT-13 2891
GX_WEBUSER 25-OCT-13
SYS 25-OCT-13
GX_WEBUSER 26-OCT-13
GX_ADMIN 26-OCT-13 2880
SYS 26-OCT-13
GX_WEBUSER 27-OCT-13
GX_ADMIN 27-OCT-13 2640
GX_WEBUSER 28-OCT-13
--Author : Leshami
--Blog : http://blog.csdn.net/leshami
--基于日期时间段的用户登陆数
SQL> select trunc (logon_day) logon_day,substr(logon_time,1,2) hour,count(user_id) as number_of_logins
2 from sys.stats$user_log
3 group by trunc (logon_day) ,substr(logon_time,1,2) order by 1,2;
LOGON_DAY HOUR NUMBER_OF_LOGINS
--------- ------ ----------------
24-OCT-13 12 2
24-OCT-13 16 3
24-OCT-13 20 2
24-OCT-13 22 2
24-OCT-13 23 1
25-OCT-13 00 2
25-OCT-13 03 104
25-OCT-13 04 2
25-OCT-13 06 2
25-OCT-13 10 2
CREATE TABLE stats$user_log
(
user_id VARCHAR2 (30),
session_id NUMBER (8),
HOST VARCHAR2 (30),
last_program VARCHAR2 (48),
last_action VARCHAR2 (32),
last_module VARCHAR2 (32),
logon_day DATE,
logon_time VARCHAR2 (10),
logoff_day DATE,
logoff_time VARCHAR2 (10),
elapsed_minutes NUMBER (8)
);
--创建登陆之后的触发器
CREATE OR REPLACE TRIGGER logon_audit_trigger
AFTER LOGON
ON DATABASE
BEGIN
INSERT INTO stats$user_log
VALUES (USER,
SYS_CONTEXT ('USERENV', 'SESSIONID'),
SYS_CONTEXT ('USERENV', 'HOST'),
NULL,
NULL,
NULL,
SYSDATE,
TO_CHAR (SYSDATE, 'hh24:mi:ss'),
NULL,
NULL,
NULL);
END;
/
--创建登出之后的触发器
CREATE OR REPLACE TRIGGER logoff_audit_trigger
BEFORE LOGOFF
ON DATABASE
BEGIN
-- ***************************************************
-- Update the last action accessed
-- ***************************************************
UPDATE stats$user_log
SET last_action =
(SELECT action
FROM v$session
WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = audsid)
WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = session_id;
--***************************************************
-- Update the last program accessed
-- ***************************************************
UPDATE stats$user_log
SET last_program =
(SELECT program
FROM v$session
WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = audsid)
WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = session_id;
-- ***************************************************
-- Update the last module accessed
-- ***************************************************
UPDATE stats$user_log
SET last_module =
(SELECT module
FROM v$session
WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = audsid)
WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = session_id;
-- ***************************************************
-- Update the logoff day
-- ***************************************************
UPDATE stats$user_log
SET logoff_day = SYSDATE
WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = session_id;
-- ***************************************************
-- Update the logoff time
-- ***************************************************
UPDATE stats$user_log
SET logoff_time = TO_CHAR (SYSDATE, 'hh24:mi:ss')
WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = session_id;
-- ***************************************************
-- Compute the elapsed minutes
-- ***************************************************
UPDATE stats$user_log
SET elapsed_minutes = ROUND ( (logoff_day - logon_day) * 1440)
WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = session_id;
END;
/
2、结果样例
[sql] view plain copy print?
--查看用户的登入登出信息
SQL> select * from sys.stats$user_log where rownum<3;
USER_ID SESSION_ID HOST LAST_PROGRAM LAST_MODULE LOGON_DAY LOGON_TIME LOGOFF_DA LOGOFF_TIM ELP_MINS
---------- ---------- --------------- ---------------- ---------------- --------- ---------- --------- ---------- --------
GX_ADMIN 5409517 v2012DB01u JDBC Thin Client JDBC Thin Client 24-OCT-13 12:20:30 24-OCT-13 16:20:30 240
GX_ADMIN 5409518 v2013DB01u JDBC Thin Client JDBC Thin Client 24-OCT-13 12:22:23 24-OCT-13 16:22:30 240
--汇总用户登陆时间
SQL> SELECT user_id, TRUNC (logon_day) logon_day, SUM (elapsed_minutes) total_time
2 FROM sys.stats$user_log
3 GROUP BY user_id, TRUNC (logon_day) ORDER BY 2;
USER_ID LOGON_DAY TOTAL_TIME
------------------------------ --------- ----------
GX_ADMIN 24-OCT-13 960
SYS 24-OCT-13
GX_ADMIN 25-OCT-13 2891
GX_WEBUSER 25-OCT-13
SYS 25-OCT-13
GX_WEBUSER 26-OCT-13
GX_ADMIN 26-OCT-13 2880
SYS 26-OCT-13
GX_WEBUSER 27-OCT-13
GX_ADMIN 27-OCT-13 2640
GX_WEBUSER 28-OCT-13
--Author : Leshami
--Blog : http://blog.csdn.net/leshami
--基于日期时间段的用户登陆数
SQL> select trunc (logon_day) logon_day,substr(logon_time,1,2) hour,count(user_id) as number_of_logins
2 from sys.stats$user_log
3 group by trunc (logon_day) ,substr(logon_time,1,2) order by 1,2;
LOGON_DAY HOUR NUMBER_OF_LOGINS
--------- ------ ----------------
24-OCT-13 12 2
24-OCT-13 16 3
24-OCT-13 20 2
24-OCT-13 22 2
24-OCT-13 23 1
25-OCT-13 00 2
25-OCT-13 03 104
25-OCT-13 04 2
25-OCT-13 06 2
25-OCT-13 10 2
25-OCT-13 14 2
CREATE [UNIQUE] | [BITMAP] INDEX index_name --unique表示唯一索引
ON table_name([column1 [ASC|DESC],column2 --bitmap,创建位图索引
[ASC|DESC],…] | [express])
[TABLESPACE tablespace_name]
[PCTFREE n1] --指定索引在数据块中空闲空间
[STORAGE (INITIAL n2)]
[NOLOGGING] --表示创建和重建索引时允许对表做DML操作,默认情况下不应该使用
[NOLINE]
[NOSORT]; --表示创建索引时不进行排序,默认不适用,如果数据已经是按照该索引顺序排列的可以使用
阅读全文
0 0
- oracle 触发器和索引
- Oracle中查看无效的对象、约束、触发器和索引
- oracle触发器和事物
- oracle触发器和事物
- oracle 索引,视图,存储过程,触发器 函数
- Oracle索引、视图、存储过程、触发器案例
- Oracle索引、视图、存储过程、触发器
- Oracle数据库基础(五)--索引,触发器
- Oracle 视图 序列 索引 触发器 定时器
- Oracle中的包、触发器 以及索引
- Oracle中查看无效的对象、约束、触发器和索引(Helloblock写作)
- Oracle中查看无效的对象、约束、触发器和索引(转自Helloblock)
- 查看表的索引和触发器
- 查看表的索引和触发器
- 触发器、存储结构和索引知识
- sqlite 视图、触发器、索引和事务总结
- MYSQL数据库学习----索引和触发器
- sqlite 视图、触发器、索引和事务总结
- iOS获取设备信息,MAC地址,IP地址,设备名称
- leetcode 15. 3Sum
- 国内开源镜像站点汇总
- 年利率、七日年化收益率、万份收益
- Error:Failed to resolve: com.android.support:support-annotations:26.0.2
- oracle 触发器和索引
- php获取一年或一月或一周的时间期间
- vue中把字符串带有html标签转为html渲染页面(视频门户网站)
- 在linear linked list里,如何display unique data
- 2017.10.23 硬币购物 失败总结
- 初识bower,简单用法
- iOS 手机网站支付转Native支付(使用WKUIDelegate协议获取url)
- SpringMVC + Spring + MyBatis 学习笔记:提交数据遭遇基础类型和日期类型报400错误解决方法
- redis学习系列(七)--redis-database