Abdul Hafeez Kalsekar -- Oracle database tuning case studies

 REM SRDC_SCHEDULER_JOB.sql  REM collect Job Queue parameters,Notification configuration for troubleshooting scheduler notification issues Rem Rem Copyright (c) 2006, 2015 Oracle. All rights reserved. Rem Rem   NAME Rem      srdc_scheduler_job.sql - script to collect diagnostic details for troubleshooting scheduler notification issues Rem Rem   NOTES Rem      * This script collects the data related to Scheduler & AQ  notification  Rem * The script creates a spool output. Upload it to the Service Request Rem      * This script contains some checks which might not be relevant for all versions. Rem      * This script will *not* update any data. Rem      * This script must be run using SQL*PLUS. Rem      * You must be connected AS SYSDBA to run this script. Rem Rem Rem Rem    MODIFIED   (MM/DD/YY) Rem    slabraha   ...

  We have move to Exacc , using OS authentication for application user was challenge  and we have to arrange other Secure External Password Store  . Thanks to Oracle Doc 340559.1   that shared steps on using Oracle wallet on client side .  When clients are configured to use the secure external password store, applications can connect to a database with the following CONNECT statement syntax, without specifying database login credentials: connect /@db_connect_string   where db_connect_string is a valid connect string to access the intended database. In this case, the database credentials, username and password,  are securely stored in an Oracle wallet created for this purpose. The autologin feature of this wallet is turned on so the system does not need a password to open the wallet. From the wallet, it gets the credentials to access the database for the user they represent. Configuring Clients to Use the External Password Store  1) Create a w...

  In this Document Goal Solution References APPLIES TO: Oracle Database - Standard Edition - Version 10.2.0.5 and later Oracle Database - Enterprise Edition - Version 10.2.0.5 and later Oracle Database Exadata Cloud Machine - Version N/A and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Oracle Database Cloud Exadata Service - Version N/A and later Information in this document applies to any platform.   GOAL Password credentials for connecting to databases can now be stored in a client-side Oracle wallet, a secure software container used to store authentication and signing credentials. This wallet usage can simplify large-scale deployments that rely on password credentials for  connecting to databases. When this feature is configured, application code, batch jobs, and scripts no longer need embedded user names and passwords. Risk is reduced because such passwords are no longer exposed in the clear, and password management policies are more ea...

  Recently we have been reported   "ORA-00910:specified length too long for its datatype"   by application team .    Upon checking we planned to upgrade MAX_STRING_SIZE to EXTENDED.   We cant set  only at pdb level  too   and changes are replicated to  standby through redo logs if changes are  done only at PDB . We will be  changing  MAX_STRING_SIZE only at pdb level  In order to expand the maximum value of varchar2 (also nvarchar2 and raw) datatypes in Oracle 12c and beyond, the max_string_size parameter needs to be set to "extended".  This will change the maximum value from 4k (4096) to 32k (32767). WARNING:  You MUST run utl32k.sql immediately after changing max_string_size=extended, else you risk invalidating the database columns. Notes  :  1) The COMPATIBLE initialization parameter must be set to 12.0.0.0 or higher to set MAX_STRING_SIZE = EXTENDED. 2) The utl32k.sql script...

SELECT h.SQL_ID,         s.SQL_TEXT,        h.EXECUTIONS,        h.ELAPSED_TIME,        h.CPU_TIME,        h.BUFFER_GETS,        h.DISK_READS,        h.ROWS_PROCESSED,        h.MODULE FROM (     SELECT SQL_ID,             LISTAGG(TEXT, '') WITHIN GROUP (ORDER BY PIECE) AS SQL_TEXT     FROM V$SQLTEXT     GROUP BY SQL_ID ) s JOIN DBA_HIST_SQLSTAT h ON s.SQL_ID = h.SQL_ID WHERE h.SNAP_ID BETWEEN :start_snap_id AND :end_snap_id ORDER BY h.BUFFER_GETS DESC; SELECT      s.SQL_ID,     t.SQL_TEXT,     s.EXECUTIONS,     s.ELAPSED_TIME,     s.CPU_TIME,     s.BUFFER_GETS,     s.DISK_READS,     s.ROWS_PROCESSED,     s.MODULE FROM      DBA_HIST_SQLSTAT s JOIN  ...