Profile example

 SQL> set lines 200

SQL> select * from dba_profiles where PROFILE='<profile_name>';

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT

------------------------------ -------------------------------- -------- ----------------------------------------

<profile_name>         COMPOSITE_LIMIT                  KERNEL   DEFAULT

<profile_name>         SESSIONS_PER_USER                KERNEL   DEFAULT

<profile_name>         CPU_PER_SESSION                  KERNEL   DEFAULT

<profile_name>         CPU_PER_CALL                     KERNEL   DEFAULT

<profile_name>         LOGICAL_READS_PER_SESSION        KERNEL   DEFAULT

<profile_name>         LOGICAL_READS_PER_CALL           KERNEL   DEFAULT

<profile_name>         IDLE_TIME                        KERNEL   DEFAULT

<profile_name>         CONNECT_TIME                     KERNEL   DEFAULT

<profile_name>         PRIVATE_SGA                      KERNEL   DEFAULT

<profile_name>         FAILED_LOGIN_ATTEMPTS            PASSWORD UNLIMITED

<profile_name>         PASSWORD_LIFE_TIME               PASSWORD UNLIMITED

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT

------------------------------ -------------------------------- -------- ----------------------------------------

<profile_name>         PASSWORD_REUSE_TIME              PASSWORD UNLIMITED

<profile_name>         PASSWORD_REUSE_MAX               PASSWORD UNLIMITED

<profile_name>         PASSWORD_VERIFY_FUNCTION         PASSWORD FN_PASSWORDVERIFY

<profile_name>         PASSWORD_LOCK_TIME               PASSWORD UNLIMITED

<profile_name>         PASSWORD_GRACE_TIME              PASSWORD UNLIMITED

16 rows selected.

SQL>

OS level CPU check

 CPU Check - GV$SESSION;

OS level CPU check 

col sid format 9999999

col spid format a10

col machine format a25

col username format a15

col "OS User" format a15

col Program format a20

col LOGON_TIME format a30

SELECT s.sid, s.serial#,p.spid "OS Pid",machine, s.username "Username",

s.osuser "OS User", s.program "Program",to_char(s.logon_time, 'dd-mon-yy hh24:mi') LOGON_TIME, a.sql_id,s.PREV_SQL_ID

FROM gv$session s, gv$sqlarea a, gv$process p

WHERE s.sql_hash_value = a.hash_value (+)

AND s.sql_address = a.address (+)

AND s.paddr = p.addr

and s.sid in (select s.sid from gv$session s, gv$process p where s.paddr = p.addr and p.spid in ('16146','422','8201','17056'));

CPU Check - V$SESSION;

col sid format 9999999

col spid format a10

col machine format a25

col username format a15

col "OS User" format a15

col Program format a20

col LOGON_TIME format a30

SELECT s.sid, s.serial#,p.spid "OS Pid",machine, s.username "Username",

s.osuser "OS User", s.program "Program",to_char(s.logon_time, 'dd-mon-yy hh24:mi') LOGON_TIME, a.sql_id,s.PREV_SQL_ID

FROM v$session s, v$sqlarea a, v$process p

WHERE s.sql_hash_value = a.hash_value (+)

AND s.sql_address = a.address (+)

AND s.paddr = p.addr

and s.sid in (select s.sid from v$session s, v$process p where s.paddr = p.addr and p.spid in ('16146','422','8201','17056'));

UNDO Percentage Query

 

UNDO Percentage Query 

select a.tablespace_name, SIZEMB, USAGEMB, (SIZEMB - USAGEMB) FREEMB

from (select sum(bytes) / 1024 / 1024 SIZEMB, b.tablespace_name

from dba_data_files a, dba_tablespaces b

where a.tablespace_name = b.tablespace_name

and b.contents = 'UNDO'

group by b.tablespace_name) a,

(select c.tablespace_name, sum(bytes) / 1024 / 1024 USAGEMB

from DBA_UNDO_EXTENTS c

where status <> 'EXPIRED'

group by c.tablespace_name) b

where a.tablespace_name = b.tablespace_name;