Script for check all the enabled auditing on Database

--Check the parameter is enabled or disable for Audit
select name || '=' || value PARAMETER from sys.v_$parameter where name like '%audit%';
--Statement Audits Enabled on this Database
column user_name format a10
column audit_option format a40
select * from sys.dba_stmt_audit_opts;
--Privilege Audits Enabled on this Database
select * from dba_priv_audit_opts;
-- Object Audits Enabled on this Database
select (owner ||'.'|| object_name) object_name,
alt, aud, com, del, gra, ind, ins, loc, ren, sel, upd, ref, exe
from dba_obj_audit_opts
where alt != '-/-' or aud != '-/-'
or com != '-/-' or del != '-/-'
or gra != '-/-' or ind != '-/-'
or ins != '-/-' or loc != '-/-'
or ren != '-/-' or sel != '-/-'
or upd != '-/-' or ref != '-/-'
or exe != '-/-';

--Default Audits Enabled on this Database
select * from all_def_audit_opts;

Cursor environment mismatch V$SQL_SHARED_CURSOR

SELECT
SS1.SQL_TEXT,
SS1.SQL_ID,
SS1.CHILD_NUMBER,
SS2.CHILD_NUMBER,
SS1.SQLTYPE,
SS2.SQLTYPE,
SS1.PARSING_SCHEMA_NAME,
SS2.PARSING_SCHEMA_NAME
FROM
V$SQL SS1,
V$SQL SS2
WHERE
SS1.SQL_ID=SS2.SQL_ID
AND SS1.SQLTYPE < SS2.SQLTYPE
ORDER BY
SS1.SQL_TEXT,
SS1.CHILD_NUMBER,
SS2.CHILD_NUMBER;

SELECT
SSC.*
FROM
V$SQL S,
V$SQL_SHARED_CURSOR SSC
WHERE
S.SQL_ID='xxxxxxxxxxxxx'
AND S.SQL_ID=SSC.SQL_ID
AND S.CHILD_ADDRESS=SSC.CHILD_ADDRESS
ORDER BY
SSC.CHILD_NUMBER;

SELECT
  S.CHILD_NUMBER,
  SBM.POSITION,
  SBM.DATATYPE,
  SBM.MAX_LENGTH,
  SBM.BIND_NAME
FROM
  V$SQL S,
  V$SQL_BIND_METADATA SBM
WHERE
  S.SQL_ID='xxxxxx'
  AND S.CHILD_ADDRESS=SBM.ADDRESS
ORDER BY
  S.CHILD_NUMBER,
  SBM.POSITION;

 

source : https://hoopercharles.wordpress.com/2010/07/23/sql_type_mismatch-in-vsql_shared_cursor/

Stats Oracle SYSTEME et schema

Voir si les stats Oracle sont calculées sur mon schéma : 

 

set lines 150
set pages 3000
col TABLE_NAME for a30
col INDEX_NAME for a30
col COLUMN_NAME for a30

define schema=&schema

spool stats_&schema

-- Voir les stats sur mes tables
select table_name, num_rows, BLOCKS, STALE_STATS from DBA_TAB_STATISTICS where owner = '&schema' order by table_name;

--Voir les stats sur mes indexes
select INDEX_NAME, TABLE_NAME, BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, NUM_ROWS, LAST_ANALYZED from DBA_IND_STATISTICS where OWNER = '&schema' order by TABLE_NAME, INDEX_NAME;

--Voir les stats sur mes colonnes
select TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, DENSITY, LAST_ANALYZED, AVG_COL_LEN from DBA_TAB_COL_STATISTICS where owner = '&schema' order by TABLE_NAME, COLUMN_NAME;

spool off
exit

-- Mise en place des statistiques systèmes et os (sans mode 'concurrent true' !)
execute sys.dbms_stats.gather_system_stats('Start');
execute sys.dbms_stats.gather_system_stats('Stop');
execute sys.dbms_stats.gather_system_stats('interval',interval => 15); # durée de 60 mins
execute sys.dbms_stats.delete_system_stats();

-- Vérifier si les statistiques sont calculées
select pname, pval1 from sys.aux_stats$ where sname = 'SYSSTATS_MAIN' order by 1;

 

Autre source d'infos : 

https://fadace.developpez.com/oracle/statistiques-systeme/

http://dbaoraclesql.canalblog.com/archives/2019/03/30/37218417.html