Please use the below.
CREATE TABLE DBA.STATS$TOTALUSERS
(
MEASURED_DATE DATE,
TOTAL_USERS NUMBER,
ACTIVE_USERS NUMBER
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE PUBLIC SYNONYM STATS$TOTALUSERS FOR DBA.STATS$TOTALUSERS;
GRANT DELETE, INSERT, SELECT, UPDATE ON DBA.STATS$TOTALUSERS TO PUBLIC;
====
CREATE TABLE DBA.STATS$USERSESSIONS
(
START_DATE DATE,
SYSTEM_SESSIONS NUMBER,
ACTIVE_USERS NUMBER,
INACTIVE_USERS NUMBER,
DEDICATED_SERVERS NUMBER,
USER_SESSIONS NUMBER,
TRANSACTIONS NUMBER
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE INDEX DBA.STATS$USERSESSIONS_STDTE_I ON DBA.STATS$USERSESSIONS
(START_DATE)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE PUBLIC SYNONYM STATS$USERSESSIONS FOR DBA.STATS$USERSESSIONS;
GRANT DELETE, INSERT, SELECT, UPDATE ON DBA.STATS$USERSESSIONS TO PUBLIC;
======
CREATE TABLE DBA.STATS$VMSTAT2
(
START_DATE DATE,
DURATION NUMBER,
SERVER_NAME VARCHAR2(20 BYTE),
RUNQUE_WAITS NUMBER,
BUSY_WAITS NUMBER,
SWAP NUMBER,
FREE NUMBER,
BUFF NUMBER,
CACHE NUMBER,
SWAP_IN NUMBER,
SWAP_OUT NUMBER,
BLOCKS_IN NUMBER,
BLOCKS_OUT NUMBER,
DEVICE_INTERRUPT NUMBER,
CONTEXT_SWITCHES NUMBER,
USER_CPU NUMBER,
SYSTEM_CPU NUMBER,
IDLE_CPU NUMBER,
WAIT_CPU NUMBER
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE INDEX DBA.STATS$VMSTAT_STDTE_I ON DBA.STATS$VMSTAT2
(START_DATE)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE PUBLIC SYNONYM STATS$VMSTAT2 FOR DBA.STATS$VMSTAT2;
GRANT SELECT ON DBA.STATS$VMSTAT2 TO PUBLIC;
===========
Grant select on v_$session to DBA;
Procedure :
create or replace PROCEDURE DBA.vmstat3
(START_DATE DATE, DURATION NUMBER, SERVER_NAME VARCHAR2,
RUNQUE NUMBER, BUSY_WAITS NUMBER, SWAP NUMBER,
FREE NUMBER, BUFF NUMBER, CACHE NUMBER,
SWAP_IN NUMBER, SWAP_OUT NUMBER, BLOCKS_IN NUMBER,
BLOCKS_OUT NUMBER, DEVICE_INTERRUPT NUMBER, CONTEXT_SWITCHES NUMBER,
USER_CPU NUMBER, SYSTEM_CPU NUMBER, IDLE_CPU NUMBER,
WAITS_CPU NUMBER)
AS
active_users number;
total_users number;
BEGIN
INSERT INTO DBA.STATS$VMSTAT2 VALUES
(START_DATE,
DURATION ,
SERVER_NAME ,
RUNQUE,
BUSY_WAITS ,
SWAP ,
FREE,
BUFF ,
CACHE ,
SWAP_IN,
SWAP_OUT ,
BLOCKS_IN ,
BLOCKS_OUT,
DEVICE_INTERRUPT ,
CONTEXT_SWITCHES ,
USER_CPU,
SYSTEM_CPU ,
IDLE_CPU ,
WAITS_CPU );
select count(*) into active_users From v$session where username <> ‘ ‘ and status = ‘ACTIVE’;
select count(*) into total_users From v$session where username <> ‘ ‘ ;
INSERT INTO DBA.STATS$TOTALUSERS
(measured_date,total_users,active_users)
values
(START_DATE,total_users,active_users);
INSERT INTO DBA.STATS$USERSESSIONS select sysdate,sum(decode(type,’BACKGROUND’,1,0)) system_sessions,
sum(decode(type,’BACKGROUND’,0,decode(status,’ACTIVE’,1,0))) active_users,
sum(decode(type,’BACKGROUND’,0,decode(status,’ACTIVE’,0,1))) inactive_users,
sum(decode(type,’BACKGROUND’,0,decode(server,’DEDICATED’,1,0))) dedicated_servers,
sum(decode(type,’BACKGROUND’,0,1)) user_sessions,
sum(decode(taddr,”,0,decode(status,’ACTIVE’,1,0))) transactions
from v$session;
commit;
END;
/
Crontab schedule time.
01,05,10,15,20,25,31,35,40,45,50,55 * * * * /oracle/oraprocs/get_vmstat.ksh 1>/oracle/oraprocs/logs/get_vmstat.log 2>/oracle/oraprocs/logs/getvmstat.err
