Script to check oracle profile parametets including password function details

 WITH P1 AS (

SELECT name,  REGEXP_SUBSTR(SUBSTR(REPLACE(REPLACE(text, chr(10), ''),'=>','='),42), 'chars = ([0-9]+)', 1, 1, NULL, 1) AS PASSWORD_LENGTH  FROM dba_source

WHERE "NAME" in (SELECT REPLACE(limit, chr(10)) FROM dba_profiles WHERE resource_name = 'PASSWORD_VERIFY_FUNCTION')

AND text LIKE '%complexity_check%'

),P2 AS (

SELECT PROFILE,

MAX(DECODE(RESOURCE_NAME,'FAILED_LOGIN_ATTEMPTS',5))            FAILED_LOGIN_ATTEMPTS           ,

MAX(DECODE(RESOURCE_NAME,'PASSWORD_LIFE_TIME',LIMIT))           PASSWORD_LIFE_TIME                      ,

MAX(DECODE(RESOURCE_NAME,'PASSWORD_REUSE_TIME',LIMIT))          PASSWORD_REUSE_TIME                     ,

MAX(DECODE(RESOURCE_NAME,'PASSWORD_REUSE_MAX',LIMIT))           PASSWORD_REUSE_MAX                      ,

MAX(DECODE(RESOURCE_NAME,'PASSWORD_VERIFY_FUNCTION',LIMIT)) PASSWORD_VERIFY_FUNCTION    ,

MAX(DECODE(RESOURCE_NAME,'PASSWORD_LOCK_TIME',LIMIT))           PASSWORD_LOCK_TIME                      ,

MAX(DECODE(RESOURCE_NAME,'PASSWORD_GRACE_TIME',LIMIT))          PASSWORD_GRACE_TIME             ,

MAX(DECODE(RESOURCE_NAME,'INACTIVE_ACCOUNT_TIME',LIMIT))        INACTIVE_ACCOUNT_TIME           ,

MAX(DECODE(RESOURCE_NAME,'PASSWORD_ROLLOVER_TIME',LIMIT))       PASSWORD_ROLLOVER_TIME          ,

MAX(DECODE(RESOURCE_NAME,'SESSIONS_PER_USER',LIMIT))            SESSIONS_PER_USER

FROM dba_profiles

-- where  RESOURCE_TYPE='PASSWORD'

GROUP BY PROFILE

), P3 AS (

SELECT USERNAME,  PROFILE, ORACLE_MAINTAINED,account_status FROM DBA_USERS

)

SELECT

                SYS_CONTEXT('USERENV','DB_NAME') DB_NAME

    ,   to_char(sysdate,'DD-MON-YYYY HH24:MI:ss') AS REPORT_DATE

    ,   P2.PROFILE

  ,   P2.FAILED_LOGIN_ATTEMPTS

    ,   P2.PASSWORD_LOCK_TIME

  ,   P2.PASSWORD_LIFE_TIME

      ,       P2.PASSWORD_REUSE_MAX

      ,       P2.PASSWORD_REUSE_TIME

      ,       P2.PASSWORD_GRACE_TIME

      ,       P2.PASSWORD_VERIFY_FUNCTION

      ,       P2.SESSIONS_PER_USER

      ,       P2.INACTIVE_ACCOUNT_TIME

      ,       P1.PASSWORD_LENGTH

      ,       P3.ORACLE_MAINTAINED

        ,       LISTAGG(P3.USERNAME,', ') AS AFFECTED_USERS

    , p3.account_status ACCOUNT_STATUS

FROM P2

LEFT JOIN  P1 ON P1.NAME = P2.PASSWORD_VERIFY_FUNCTION

LEFT JOIN P3 ON P3.PROFILE = P2.PROFILE

WHERE P3.USERNAME IS NOT NULL and P3.ORACLE_MAINTAINED ='N'

GROUP BY

        P2.PROFILE,  P2.FAILED_LOGIN_ATTEMPTS,  P2.PASSWORD_LIFE_TIME,  P2.PASSWORD_REUSE_TIME,  P2.PASSWORD_REUSE_MAX

    ,   P2.PASSWORD_VERIFY_FUNCTION,  P1.PASSWORD_LENGTH,  P2.PASSWORD_LOCK_TIME     ,   P2.INACTIVE_ACCOUNT_TIME

    ,   P2.PASSWORD_GRACE_TIME    ,   P3.ORACLE_MAINTAINED , P2.SESSIONS_PER_USER  ,p3.account_status

ORDER BY

       P2.PROFILE , P3.ACCOUNT_STATUS -- P3.ORACLE_MAINTAINED

;

Oracle sql Script to check Users with default password report with html

 cat def_passwd.sql

SET HEADING ON TERMOUT OFF SERVEROUTPUT OFF FEEDBACK OFF ECHO OFF PAGESIZE 1000

SET MARKUP HTML ON ENTMAP OFF SPOOL ON -

TABLE BORDER='1'

SET TERMOUT OFF

SET ECHO OFF

SET SERVEROUTPUT OFF

set feedback off

set echo off pagesize 1000 markup html on ENTMAP OFF spool on -

TABLE BORDER='1'

set hea on

set echo off

spool def_psswd.html app

SELECT SYS_CONTEXT('USERENV','DB_NAME') DB_NAME, dp.USERNAME DB_USER_WITH_DEFAULT_PASSWORD , ACCOUNT_STATUS, LOCK_DATE, EXPIRY_DATE,PASSWORD_CHANGE_DATE

FROM DBA_USERS_WITH_DEFPWD dp

join dba_users du on du.username = dp.username ;

select SYS_CONTEXT('USERENV','DB_NAME') DB_NAME,OWNER, NAME FUNCTION_NAME,   substr(TEXT,42) PASSWORD_COMPLEXCITY from (

SELECT OWNER, NAME, TYPE, LINE, replace(TEXT, chr(10), '') as TEXT FROM DBA_SOURCE WHERE /*owner = 'SYS' AND*/ name IN

    (SELECT REPLACE(limit, chr(10)) FROM dba_profiles WHERE resource_name = 'PASSWORD_VERIFY_FUNCTION')

ORDER BY NAME, LINE ) where lower(trim(text)) like '%complexity_check%';

SELECT SYS_CONTEXT('USERENV','DB_NAME') DB_NAME, PROFILE, RESOURCE_NAME, LIMIT fROM DBA_PROFILES WHERE RESOURCE_NAME='PASSWORD_LOCK_TIME';

spool off ;

exit ;

Script Oracle User Size including table, index, lob and partitions

 WITH

    table_segs AS (

        SELECT owner, segment_name AS table_name, SUM(bytes) AS bytes

        FROM dba_segments

        WHERE segment_type = 'TABLE'

        GROUP BY owner, segment_name

    ) ,index_segs AS (

        SELECT i.owner, i.table_name, SUM(s.bytes) AS bytes

        FROM dba_indexes i

        JOIN dba_segments s           ON i.owner = s.owner AND i.index_name = s.segment_name

        WHERE s.segment_type = 'INDEX'

        GROUP BY i.owner, i.table_name

    )  ,table_part_segs AS (

        SELECT p.table_owner AS owner, p.table_name, SUM(s.bytes) AS bytes

        FROM dba_segments s

        JOIN dba_tab_partitions p           ON s.owner = p.table_owner AND s.segment_name = p.partition_name

        WHERE s.segment_type = 'TABLE PARTITION'

        GROUP BY p.table_owner, p.table_name

    ) ,index_part_segs AS (

        SELECT i.owner, i.table_name, SUM(s.bytes) AS bytes

        FROM dba_segments s

        JOIN dba_ind_partitions ip   ON s.owner = ip.index_owner AND s.segment_name = ip.partition_name

        JOIN dba_indexes i           ON ip.index_owner = i.owner AND ip.index_name = i.index_name

        WHERE s.segment_type = 'INDEX PARTITION'

        GROUP BY i.owner, i.table_name

    ),lob_segs AS (

        SELECT l.owner, l.table_name, SUM(s.bytes) AS bytes

        FROM dba_lobs l

        JOIN dba_segments s       ON l.owner = s.owner AND l.segment_name = s.segment_name

        WHERE s.segment_type = 'LOBSEGMENT'

        GROUP BY l.owner, l.table_name

    ),lob_ind AS (

        SELECT l.owner, l.table_name, SUM(s.bytes) AS bytes

        FROM dba_lobs l

        JOIN dba_segments s       ON l.owner = s.owner AND l.segment_name = s.segment_name

        WHERE s.segment_type = 'LOBINDEX'

        GROUP BY l.owner, l.table_name

    ), tot as (

select * from (

SELECT

sys_context ('USERENV', 'DB_NAME')db_name,

    t.owner,

    t.table_name,

    t.num_rows record_count,

    ROUND(NVL(ts.bytes, 2) / 1073741824, 2) AS table_size_GB,

    ROUND(NVL(ix.bytes, 0) / 1073741824, 2) AS index_size_GB,

    ROUND(NVL(ls.bytes, 2) / 1073741824, 2) AS lob_segment_size_GB,

    ROUND(NVL(li.bytes, 2) / 1073741824, 2) AS lob_index_size_GB,

    ROUND(NVL(tp.bytes, 0) / 1073741824, 2) AS table_part_size_GB,  

    ROUND(NVL(ip.bytes, 0) / 1073741824, 2) AS index_part_size_GB,

    ROUND((NVL(ts.bytes, 2)  + NVL(tp.bytes, 0)  + NVL(ip.bytes, 0)  + NVL(ix.bytes, 0) + NVL(ls.bytes, 2) + nvl(li.bytes,2)) / 1073741824, 2) AS total_size_GB

FROM dba_tables t

LEFT JOIN table_segs ts         ON t.owner = ts.owner AND t.table_name = ts.table_name

LEFT JOIN index_segs ix         ON t.owner = ix.owner AND t.table_name = ix.table_name

LEFT JOIN table_part_segs tp    ON t.owner = tp.owner AND t.table_name = tp.table_name

LEFT JOIN index_part_segs ip    ON t.owner = ip.owner AND t.table_name = ip.table_name

LEFT JOIN lob_segs ls           ON t.owner = ls.owner AND t.table_name = ls.table_name

left join lob_ind li            ON t.owner = li.owner AND t.table_name = li.table_name

  )

  where

  owner in (select username from dba_users where oracle_maintained='N') and

  ( total_size_gb >= 1 /*OR record_count >= 10000 */)

  ORDER BY owner , total_size_GB DESC

  )

  select DB_NAME, OWNER, TABLE_NAME, RECORD_COUNT,TOTAL_SIZE_GB, TABLE_SIZE_GB, INDEX_SIZE_GB, LOB_SEGMENT_SIZE_GB, LOB_INDEX_SIZE_GB, TABLE_PART_SIZE_GB, INDEX_PART_SIZE_GB

  From tot 

  ;

Oracle Top TABLES BY Size including lob, indexes

 

WITH

    table_segs AS (

        SELECT owner, segment_name AS table_name, SUM(bytes) AS bytes

        FROM dba_segments

        WHERE segment_type = 'TABLE'

        GROUP BY owner, segment_name

    ) /* ,

    table_part_segs AS (

        SELECT p.table_owner AS owner, p.table_name, SUM(s.bytes) AS bytes

        FROM dba_segments s

        JOIN dba_tab_partitions p           ON s.owner = p.table_owner AND s.segment_name = p.partition_name

        WHERE s.segment_type = 'TABLE PARTITION'

        GROUP BY p.table_owner, p.table_name

    ) ,

    index_segs AS (

        SELECT i.owner, i.table_name, SUM(s.bytes) AS bytes

        FROM dba_indexes i

        JOIN dba_segments s           ON i.owner = s.owner AND i.index_name = s.segment_name

        WHERE s.segment_type = 'INDEX'

        GROUP BY i.owner, i.table_name

    ),

    index_part_segs AS (

        SELECT i.owner, i.table_name, SUM(s.bytes) AS bytes

        FROM dba_segments s

        JOIN dba_ind_partitions ip   ON s.owner = ip.index_owner AND s.segment_name = ip.partition_name

        JOIN dba_indexes i           ON ip.index_owner = i.owner AND ip.index_name = i.index_name

        WHERE s.segment_type = 'INDEX PARTITION'

        GROUP BY i.owner, i.table_name

    ), */

    ,lob_segs AS (

        SELECT l.owner, l.table_name, SUM(s.bytes) AS bytes

        FROM dba_lobs l

        JOIN dba_segments s       ON l.owner = s.owner AND l.segment_name = s.segment_name

        WHERE s.segment_type LIKE 'LOB%'

        GROUP BY l.owner, l.table_name

    )

select * from (

SELECT

    t.owner,

    t.table_name,

    t.num_rows record_count,

    ROUND(NVL(ts.bytes, 2) / 1073741824, 2) AS table_size_GB,

    ROUND(NVL(ls.bytes, 2) / 1073741824, 2) AS log_segment_size_GB,

    /*ROUND(NVL(tp.bytes, 0) / 1073741824, 2) AS table_partition_size_GB,

    --ROUND(NVL(ix.bytes, 0) / 1073741824, 2) AS index_size_GB,

    ROUND(NVL(ip.bytes, 0) / 1073741824, 2) AS index_partition_size_GB,

*/

    ROUND((NVL(ts.bytes, 2) /* + NVL(tp.bytes, 0) + NVL(ix.bytes, 0) + NVL(ip.bytes, 0)  */ + NVL(ls.bytes, 2)) / 1073741824, 2) AS total_size_GB

FROM dba_tables t

LEFT JOIN table_segs ts         ON t.owner = ts.owner AND t.table_name = ts.table_name

/*LEFT JOIN table_part_segs tp    ON t.owner = tp.owner AND t.table_name = tp.table_name

LEFT JOIN index_segs ix         ON t.owner = ix.owner AND t.table_name = ix.table_name

LEFT JOIN index_part_segs ip    ON t.owner = ip.owner AND t.table_name = ip.table_name

*/LEFT JOIN lob_segs ls           ON t.owner = ls.owner AND t.table_name = ls.table_name

  )

  where owner in (select username from dba_users where oracle_maintained='N')

  and (record_count >= 10000 or total_size_gb >= 0.01)

  ORDER BY total_size_GB DESC;

Hourly SGA and PGA Memory Adbisary / Pressure Report - Html

 - - Required diag and Tuning Pack to be enabled

SELECT

        SYS_CONTEXT('USERENV','DB_NAME') DB_NAME ,

    TO_CHAR(report_date, 'mm dd yyyy DY') AS report_date,param_name,

    current_target_gb existing,

--      max(A_TAR_GB) MAX_MEM,

--      max(A_PCT) MAX_PCT,

        --max(A_TAR_GB) || '<br><br>('|| max(regexp_substr(A_PCT,'[0-9]+'))|| ')' MAX_ADV ,

        max(A_TAR_GB) || '<br><br><span style="font-weight: bold;color: blue;">('|| max(A_PCT2)|| '%)</span>' MAX_ADV ,

--max(A_PCT2) max2,

    MAX(CASE WHEN TO_CHAR(report_date, 'HH24') = '00' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "00",

    MAX(CASE WHEN TO_CHAR(report_date, 'HH24') = '01' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "01",

    MAX(CASE WHEN TO_CHAR(report_date, 'HH24') = '02' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "02",

    MAX(CASE WHEN TO_CHAR(report_date, 'HH24') = '03' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "03",

    MAX(CASE WHEN TO_CHAR(report_date, 'HH24') = '04' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "04",

    MAX(CASE WHEN TO_CHAR(report_date, 'HH24') = '05' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "05",

    MAX(CASE WHEN TO_CHAR(report_date, 'HH24') = '06' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "06",

    MAX(CASE WHEN TO_CHAR(report_date, 'HH24') = '07' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "07",

    max(CASE WHEN TO_CHAR(report_date, 'HH24') = '08' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "08",

    max(CASE WHEN TO_CHAR(report_date, 'HH24') = '09' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "09",

    max(CASE WHEN TO_CHAR(report_date, 'HH24') = '10' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "10",

    max(CASE WHEN TO_CHAR(report_date, 'HH24') = '11' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "11",

    max(CASE WHEN TO_CHAR(report_date, 'HH24') = '12' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "12",

    max(CASE WHEN TO_CHAR(report_date, 'HH24') = '13' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "13",

    max(CASE WHEN TO_CHAR(report_date, 'HH24') = '14' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "14",

    max(CASE WHEN TO_CHAR(report_date, 'HH24') = '15' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "15",

    max(CASE WHEN TO_CHAR(report_date, 'HH24') = '16' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "16",

    max(CASE WHEN TO_CHAR(report_date, 'HH24') = '17' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "17",

    max(CASE WHEN TO_CHAR(report_date, 'HH24') = '18' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "18",

    max(CASE WHEN TO_CHAR(report_date, 'HH24') = '19' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "19",

    max(CASE WHEN TO_CHAR(report_date, 'HH24') = '20' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "20",

    max(CASE WHEN TO_CHAR(report_date, 'HH24') = '21' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "21",

    max(CASE WHEN TO_CHAR(report_date, 'HH24') = '22' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "22",

    max(CASE WHEN TO_CHAR(report_date, 'HH24') = '23' THEN TO_CHAR(A_TAR_GB) || '<br><br>(' || A_PCT || ')' ELSE '0' END) AS "23"

FROM (

    SELECT

        al.execution_start AS report_date,aa.ATTR1 param_name,

        ROUND(aa.num_attr1 / 1073741824) AS current_target_gb,

        ROUND(aa.num_attr2 / 1073741824) AS A_TAR_GB,

        CASE

        WHEN ROUND(((aa.num_attr2 - aa.num_attr1) / aa.num_attr1) * 100) >= 75 THEN '<span style="font-weight: bold;color: red;">'    || ROUND(((aa.num_attr2 - aa.num_attr1) / aa.num_attr1) * 100) || '%</span>'

        WHEN ROUND(((aa.num_attr2 - aa.num_attr1) / aa.num_attr1) * 100) >= 50 THEN '<span style="font-weight: bold;color: maroon;">' || ROUND(((aa.num_attr2 - aa.num_attr1) / aa.num_attr1) * 100) || '%</span>'

        WHEN ROUND(((aa.num_attr2 - aa.num_attr1) / aa.num_attr1) * 100) >= 25 THEN '<span style="font-weight: bold;color: purple;">' || ROUND(((aa.num_attr2 - aa.num_attr1) / aa.num_attr1) * 100) || '%</span>'

        WHEN ROUND(((aa.num_attr2 - aa.num_attr1) / aa.num_attr1) * 100) >= 1  THEN '<span style="font-weight: bold;color: orange;">' || ROUND(((aa.num_attr2 - aa.num_attr1) / aa.num_attr1) * 100) || '%</span>'

        ELSE

            '<span style="font-weight: bold;color: green;">' || ROUND(((aa.num_attr2 - aa.num_attr1) / aa.num_attr1) * 100) || '</span>'

        END AS A_PCT ,

                '<span style="font-weight: bold;color: green;">'||'0'||'</span>' sero,

ROUND(((aa.num_attr2 - aa.num_attr1) / aa.num_attr1) * 100) A_PCT2

    FROM

        dba_advisor_actions aa

    JOIN   dba_advisor_findings af ON aa.owner = af.owner AND aa.task_name = af.task_name AND aa.execution_name = af.execution_name

    JOIN   dba_advisor_log al      ON al.owner = af.owner AND al.task_name = af.task_name

    WHERE

        af.finding_name in ( 'Undersized SGA','Undersized PGA')

        AND aa.attr1 in('sga_target','pga_aggregate_target')

        AND TRUNC(al.execution_start) >= TRUNC(SYSDATE) - 7

)

GROUP BY

    TO_CHAR(report_date, 'mm dd yyyy DY'), current_target_gb,param_name ,sero

ORDER BY

  --  report_date DESC

to_date(report_date,'mm dd yyyy DY') DESC ;