DBA

I rely on dba_hist_sql_plan for me to track query performance for each sql_id, however, this time the query plan hash value shows in v$sql, but not recorded in dba_hist_sql_plan, though AWR snapshots are take every 1 hour. When I open myOracle support ticket, the support engineer told me that " The PHV 1779814119 was first loaded on 2023-08-20/08:36:37, and it's still the current execution plan, while PHV 521264029 was historically used and appears in the view dba_hist_sql_plan Once there is a new current PHV different than 1779814119 , you will find this in the historical sql plans when it's flushed by the mmon slaves as part of the AWR activities " I think that's a wrong statement. ChatGPT tells me that topNsql of AWR might be the answer, googling on topNsql results in a few findings such as this one below. I did not test it yet because I do not have DBA permission on the database, so in theory this could be the issue.  https://ironmandba.wordpress.com/modifying...

I use these query to get APEX meta information about APEX version, workspace, application, pages, and table/queries the page are using.  col comp_name format a30 col procedure format a20 col username format a30 col proxy format a30 col client format a30 col APPLICATION_ID  heading "APP_ID" format 9999 select comp_name,version,schema,procedure from dba_registry where comp_name like '%APEX%'; select username,created from dba_users where username like '%APEX%'; select owner,count(*) from dba_objects where owner like 'APEX%' group by owner; select owner,object_type,count(*) from dba_objects where owner like 'APEX_LISTENER'  group by owner,object_type; --select synonym_name,TABLE_OWNER,TABLE_NAME from dba_synonyms where OWNER='APEX_LISTENER'; select * from proxy_users where proxy like '%ORDS%' or proxy like '%APEX%'; -- list workspace and applications SELECT    --  w.workspace_id,     w.workspace,     a.application_id,     a....

I use this to display all ORDS settings that I need to know: -- Run this part as dba user, until you see another comment about running as ORDS schema user set echo on set linesize 240 col object_type format a20 col object_name format a40 col proxy,client format a20 col parsing_schema format a20 col status format a10 col pre_hook format a10 col updated_by format a15 col created_by format a15 col auto_rest_auth format a10 COLUMN name FORMAT A60 COLUMN privilege_name FORMAT A60 COLUMN role_name FORMAT A50 COLUMN name FORMAT A40 COLUMN pattern FORMAT A40 COLUMN name FORMAT A20 col client_name format a20 COLUMN client_name FORMAT A30 COLUMN role_name FORMAT A20 set long 5000 column PARSING_SCHEMA format a20 col name format a60 COLUMN name FORMAT A40 COLUMN uri_prefix FORMAT A80 COLUMN uri_template FORMAT A20 COLUMN source_type FORMAT A30 COLUMN source FORMAT A60 col base_path format a30 col name format a40 col pattern format a20 col source format a20 col source_type format a40 col client_sc...

  #!/bin/bash # This script invokes rman to perform a daily # incremental backup of db # if ARC key is used it makes an archive log backup if a threshold is exceeded # It checks for errors and sends email if any. # It cleans up this script log files older then the log file retention period, Check "set up variables" section. # set up a debug mode ##set -x host_name=$(hostname) opsys=$(uname) script=$(basename $0) if [ ${opsys} = 'Linux' ]; then     scriptpath=$(dirname $(readlink -f $0)) else     scriptpath=$(dirname $0) fi scriptNoExt=$(echo ${script} | cut -d \. -f 1) paramFilePath=${scriptpath}/${scriptNoExt}.param oraSetFile="${HOME}/.oraset_ss" #---------------- # Functions usage () {     echo "Usage: ${script} db_instance_name type_of_backup"     echo "Where type_of_backup = [empty|ARC|DELARC|DELBACKUPS]"     echo "    empty - backup the whole database"     echo "    ARC - backup and clean up archi...

ORDS Installation/config ${ORDS_HOME}/bin/ords --config ${ORDS_CONFIG} install ORDS version: version from database and ords binary should match select ords.installed_version from dual; ${ORDS_HOME}/bin/ords --version sqldeveloper cli to show ORDS enabled schemas and modules C:\Downloads\Oracle\sqlcl-23.1.0.089.0929\sqlcl\bin>sql jiulusun/$passwd@//MYIncorp-scan:1521/webd SQL> rest schemas C:\Downloads\Oracle\sqlcl-23.1.0.089.0929\sqlcl\bin>sql testuser1/testuser1@//MYIncorp-scan:1521/webd SQL> rest  modules To change the ords_public_user password in wallet file ${ORDS_HOME}/bin/ords config --db-pool webd secret db.password start and stop: more  /MYIncorpmw/scripts/stop_ords_dev.sh #!/bin/bash export PATH=/usr/sbin:/usr/local/bin:/usr/bin:/usr/local/sbin:$PATH kill $(ps -ef | grep "ords_dev/ords/ords.war" | grep -v grep | awk '{print $2}') [Wed Jun 07 10:18:49]oracle@vm-km-ords2-t.MYIncorp.local:/MYIncorpmw/scripts /MYIncorpmw/o...

 This is helpful -- https://www.mssqltips.com/sqlservertip/7696/sql-server-oracle-cached-query-statistics-execution-plans/?utm_content=head SELECT     DB_NAME(ISNULL([t].[dbid],     (SELECT CAST([value] AS SMALLINT) FROM [sys].[dm_exec_plan_attributes]([st].[plan_handle]) WHERE [attribute] = 'dbid'))) [DatabaseName],    ISNULL(OBJECT_NAME([t].[objectid], [t].[dbid]),'{AdHocQuery}') [Proc/Func],    MIN(SUBSTRING([t].[text], ([st].[statement_start_offset]/2)+1, ((CASE [st].[statement_end_offset] WHEN -1 THEN DATALENGTH([t].[text]) ELSE [st].[statement_end_offset] END - [st].[statement_start_offset])/2)+1)) [Text],    MAX([st].[max_rows]) [Rows],    SUM([st].[execution_count]) [Runs],    SUM([st].[execution_count])/(SELECT MAX(v) FROM (VALUES (DATEDIFF(ss,MIN([st].[creation_time]),GETDATE())), (1)) AS VALUE(v)) [Calls/Sec],    SUM([st].[max_elapsed_time])/1000000 [TimeSec],    SUM([st]....

Download autoit and install run au4Info_x64.exe go to the microsoft teams meeting chat room, click on the chat room. the autoIT (au4Info_x64.exe)  will capture the window title as "mylastname, myfirstname (BC xxx inc) (You) | Microsoft Teams", get it also in summary tab so that I can copy the title. run SciTE/SciTE.exe, create new file with the content below, replace the windows title with what was captured in the step above.  ; Wait for 5 seconds to open Microsoft Teams, click on the typing message field Sleep(5000) While True ; Loop indefinitely WinWait("mylastname, myfirstname (BC xxx inc) (You) | Microsoft Teams") WinActivate("mylastname, myfirstname (BC xxx inc) (You) | Microsoft Teams") WinWaitActive("mylastname, myfirstname (BC xxx inc) (You) | Microsoft Teams") Send("H ")    Sleep(240000) WEnd SciTE.exe-> tools-> go