Home » SQL & PL/SQL » SQL & PL/SQL » information (oracle 8 )
information [message #653513] |
Fri, 08 July 2016 16:07 |
|
ceciaide
Messages: 8 Registered: April 2016 Location: mexico
|
Junior Member |
|
|
how to get the information
access by day in BD
Users registered
concurrent accessse per day
Transactions per second
Transactions per day
Transactions Per week
on oracle version 8.1.7.4.0
thanks
|
|
|
|
|
Re: information [message #653561 is a reply to message #653514] |
Mon, 11 July 2016 10:22 |
|
ceciaide
Messages: 8 Registered: April 2016 Location: mexico
|
Junior Member |
|
|
hello
BlackSwan
I try to create a script to obtain information from the database version 8
how many users access a day to the database
select count(*) "Users" from dba_users;
how many users have the database
select SESSIONS_CURRENT "Users_Current" from v$license;
only need to find the script to obtain the information.
Access by day
Transactions per second
Transactions per day
Transactions Per week
i find a script similar
set heading off
COLUMN short_name format a20
COLUMN per_sec format 999,999,999,990
select lpad(short_name, 20, ' ') short_name, per_sec
from
(select short_name, max(decode(typ, 1, value*60*60*24)) per_sec, max(m_rank) m_rank
from
(select /*+ use_hash(s) */
m.short_name, s.value * coeff value, typ, m_rank
from v$sysmetric s,
(
select 'Logons Per Sec' metric_name, 'Access by day: ' short_name, 1 coeff, 1 typ, 11 m_rank from dual union all
select 'User Transaction Per Sec' metric_name, 'Transacc. by day: ' short_name, 1 coeff, 1 typ, 14 m_rank from dual) m
where m.metric_name = s.metric_name
and s.intsize_csec > 5000
and s.intsize_csec < 7000)
group by short_name)
order by m_rank;
select lpad(short_name, 20, ' ') short_name, per_sec
from
(select short_name, max(decode(typ, 1, value*60*60*24*7)) per_sec, max(m_rank) m_rank
from
(select /*+ use_hash(s) */
m.short_name, s.value * coeff value, typ, m_rank
from v$sysmetric s,
(
select 'User Transaction Per Sec' metric_name, 'Transacc.by week: ' short_name, 1 coeff, 1 typ, 14 m_rank from dual) m
where m.metric_name = s.metric_name
and s.intsize_csec > 5000
and s.intsize_csec < 7000)
group by short_name)
order by m_rank;
but it does not work
because the version is 8
|
|
|
|
Re: information [message #653563 is a reply to message #653562] |
Mon, 11 July 2016 10:59 |
|
ceciaide
Messages: 8 Registered: April 2016 Location: mexico
|
Junior Member |
|
|
hello
the version: 8.1.7.4.0
the information
only need to find the script to obtain the information.
Access by day
Transactions per second
Transactions per day
Transactions Per week
im find to script similar but does not work for the version oracle 8
set heading off
COLUMN short_name format a20
COLUMN per_sec format 999,999,999,990
select lpad(short_name, 20, ' ') short_name, per_sec
from
(select short_name, max(decode(typ, 1, value*60*60*24)) per_sec, max(m_rank) m_rank
from
(select /*+ use_hash(s) */
m.short_name, s.value * coeff value, typ, m_rank
from v$sysmetric s,
(
select 'Logons Per Sec' metric_name, 'Access by day: ' short_name, 1 coeff, 1 typ, 11 m_rank from dual union all
select 'User Transaction Per Sec' metric_name, 'Transacc. by day: ' short_name, 1 coeff, 1 typ, 14 m_rank from dual) m
where m.metric_name = s.metric_name
and s.intsize_csec > 5000
and s.intsize_csec < 7000)
group by short_name)
order by m_rank;
select lpad(short_name, 20, ' ') short_name, per_sec
from
(select short_name, max(decode(typ, 1, value*60*60*24*7)) per_sec, max(m_rank) m_rank
from
(select /*+ use_hash(s) */
m.short_name, s.value * coeff value, typ, m_rank
from v$sysmetric s,
(
select 'User Transaction Per Sec' metric_name, 'Transacc.by week: ' short_name, 1 coeff, 1 typ, 14 m_rank from dual) m
where m.metric_name = s.metric_name
and s.intsize_csec > 5000
and s.intsize_csec < 7000)
group by short_name)
order by m_rank;
|
|
|
|
|
|
|
|
Re: information [message #653574 is a reply to message #653513] |
Mon, 11 July 2016 14:21 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
You will be able to get the information you need from statspack. You do have statspack in your release. Detail of how to use it is in the file $ORACLE_HOME/rdbms/admin/spdoc.txt
|
|
|
|
Goto Forum:
Current Time: Fri Jun 28 09:47:53 CDT 2024
|