Oracle Apps DBA stuff

Tuesday, March 28, 2023

mode

SELECT a.ksppinm "Parameter", b.KSPPSTDF "Default Value",

b.ksppstvl "Session Value",

c.ksppstvl "Instance Value",

decode(bitand(a.ksppiflg/256,1),1,'TRUE','FALSE') IS_SESSION_MODIFIABLE,

decode(bitand(a.ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE') IS_SYSTEM_MODIFIABLE

FROM x$ksppi a,

x$ksppcv b,

x$ksppsv c

WHERE a.indx = b.indx

AND a.indx = c.indx

AND a.ksppinm LIKE '/_%actualization%' escape '/';

Sunday, March 19, 2023

Finding Undocumented ( Hidden ) Parameter in pfile

egrep -i '__db_cache_size|__java_pool_size|__large_pool_size|__oracle_base|__pga_aggregate_target|__sga_target|__shared_io_pool_size|__shared_pool_size|__streams_pool_size|_always_anti_join|_always_semi_join|_b_tree_bitmap_plans|_bloom_filter_enabled|_bloom_pruning_enabled|_complex_view_merging|_convert_set_to_join|_cost_equality_semi_join|_cpu_to_io|_dimension_skip_null|_eliminate_common_subexpr|_enable_type_dep_selectivity|_fast_full_scan_enabled|_first_k_rows_dynamic_proration|_gby_hash_aggregation_enabled|_generalized_pruning_enabled|_globalindex_pnum_filter_enabled|_gs_anti_semi_join_allowed|_improved_outerjoin_card|_improved_row_length_enabled|_index_join_enabled|_ksb_restart_policy_times|_ksb_restart_policy_times|_ksb_restart_policy_times|_ksb_restart_policy_times|_left_nested_loops_random|_local_communication_costing_enabled|_minimal_stats_aggregation|_mmv_query_rewrite_enabled|_new_initial_join_orders|_new_sort_cost_estimate|_nlj_batching_enabled|_optim_adjust_for_part_skews|_optim_enhance_nnull_detection|_optim_new_default_join_sel|_optim_peek_user_binds|_optimizer_adaptive_cursor_sharing|_optimizer_better_inlist_costing|_optimizer_cbqt_no_size_restriction|_optimizer_complex_pred_selectivity|_optimizer_compute_index_stats|_optimizer_connect_by_combine_sw|_optimizer_connect_by_cost_based|_optimizer_correct_sq_selectivity|_optimizer_cost_based_transformation|_optimizer_cost_hjsmj_multimatch|_optimizer_cost_model|_optimizer_dim_subq_join_sel|_optimizer_distinct_elimination|_optimizer_enable_density_improvements|_optimizer_enable_extended_stats|_optimizer_enhanced_filter_push|_optimizer_extend_jppd_view_types|_optimizer_extended_cursor_sharing|_optimizer_extended_cursor_sharing_rel|_optimizer_extended_stats_usage_control|_optimizer_filter_pred_pullup|_optimizer_fkr_index_cost_bias|_optimizer_group_by_placement|_optimizer_improve_selectivity|_optimizer_join_elimination_enabled|_optimizer_join_order_control|_optimizer_join_sel_sanity_check|_optimizer_max_permutations|_optimizer_mode_force|_optimizer_multi_level_push_pred|_optimizer_native_full_outer_join|_optimizer_new_join_card_computation|_optimizer_null_aware_antijoin|_optimizer_or_expansion|_optimizer_order_by_elimination_enabled|_optimizer_outer_to_anti_enabled|_optimizer_push_down_distinct|_optimizer_push_pred_cost_based|_optimizer_rownum_bind_default|_optimizer_rownum_pred_based_fkr|_optimizer_skip_scan_enabled|_optimizer_sortmerge_join_inequality|_optimizer_squ_bottomup|_optimizer_star_tran_in_with_clause|_optimizer_system_stats_usage|_optimizer_transitivity_retain|_optimizer_undo_cost_change|_or_expand_nvl_predicate|_ordered_nested_loop|_parallel_broadcast_enabled|_partition_view_enabled|_pivot_implementation_method|_pre_rewrite_push_pred|_pred_move_around|_push_join_predicate|_push_join_union_view|_push_join_union_view2|_px_minus_intersect|_px_pwg_enabled|_px_ual_serial_input|_query_rewrite_setopgrw_enable|_remove_aggr_subquery|_right_outer_hash_enable|_selfjoin_mv_duplicates|_sql_model_unfold_forloops|_sqltune_category_parsed|_subquery_pruning_enabled|_subquery_pruning_mv_enabled|_table_scan_cost_plus_one|_union_rewrite_for_gs||_unnest_subquery|_use_column_stats_for_function'

SELECT name, value from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\' order by name;

Friday, March 3, 2023

adpatch

#!/bin/bash

# Set database connection details
db_user="APPS"
db_pass="yourpassword"
db_host="yourhostname"
db_port="1521"
db_sid="yoursid"

# Set email address
email="youremail@example.com"

# Query ad_applied_patches table
patches=$(sqlplus -s $db_user/$db_pass@$db_host:$db_port/$db_sid <<EOF
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
SELECT patch_name || ' - ' || patch_type || ' - ' || creation_date FROM ad_applied_patches;
EXIT;
EOF)

# Format output as HTML table
output="<html><body><h1>List of applied patches:</h1><table><tr><th>Patch Name</th><th>Patch Type</th><th>Creation Date</th></tr>"
while read -r line; do
    IFS='-' read -ra arr <<< "$line"
    output+="<tr>"
    for i in "${arr[@]}"; do
        output+="<td>$i</td>"
    done
    output+="</tr>"
done <<< "$patches"
output+="</table></body></html>"

# Send email using mailx command with -S option instead of -a option
echo "$output" | mailx -S "Content-type: text/html;" -s "Patches Report" $email

Sunday, February 26, 2023

profile changes

#Script used to monitor for application profile changes #Threshold is the number of days to query for profile changes #For example, if you set it to 7, all profile changes that #have occurred in the past 7 days will be displayed. THRESHOLD=$1 LOGFILE=/tmp/profile_changes_$ORACLE_SID.txt sqlplus -s apps/apps << EOF set heading off spool $LOGFILE select '$ORACLE_SID - Profile Changes Past '|| 'Threshold of $THRESHOLD days - '||count(1) from fnd_profile_option_values where last_update_date > (sysdate-$THRESHOLD) having count(1) > $THRESHOLD union select 'no rows' from fnd_profile_option_values where last_update_date <= (sysdate-$THRESHOLD) having count(1) <= $THRESHOLD; spool off exit EOF RETURN_CODE=`grep "Threshold" $LOGFILE | wc -l` if [ $RETURN_CODE -eq 0 ] then exit 0 else exit 1 fi

latest

# Specify profile option names and expected values as arrays

PROFILE_OPTION_NAMES=(

"POS_INTERNAL_URL"

"POS_EXTERNAL_URL"

"APPS_AUTH_AGENT"

)

PROFILE_OPTION_VALUES=(

"http://internal.example.com"

"https://external.example.com"

"http://auth.example.com"

)

# Loop through profile option names and check their values

for (( i=0; i<${#PROFILE_OPTION_NAMES[@]}; i++ )); do

PROFILE_OPTION_NAME="${PROFILE_OPTION_NAMES[i]}"

EXPECTED_VALUE="${PROFILE_OPTION_VALUES[i]}"

# Get current value of profile option

CURRENT_VALUE=$(sqlplus -s username/password << EOF

set heading off feedback off verify off

select PROFILE_OPTION_VALUE from FND_PROFILE_OPTION_VALUES where PROFILE_OPTION_NAME='$PROFILE_OPTION_NAME';

exit;

EOF

)

# Check if the value matches the expected value

if [[ "$CURRENT_VALUE" == "$EXPECTED_VALUE" ]]; then

# Value is correct

MESSAGE+="Profile option '$PROFILE_OPTION_NAME' has the expected value '$EXPECTED_VALUE'<br>"

else

# Value is incorrect

MESSAGE+="Profile option '$PROFILE_OPTION_NAME' has an incorrect value. Expected '$EXPECTED_VALUE', but found '$CURRENT_VALUE'<br>"

COLOR="red"

done

LATEST PROFLE OPTION

#!/bin/bash

# Source the R12.2 application environment file

. /path/to/R12.2/envfile

# Specify profile option names and expected values as associative array

declare -A PROFILE_OPTIONS=(

["POS_INTERNAL_URL"]="http://internal.example.com"

["POS_EXTERNAL_URL"]="https://external.example.com"

["APPS_AUTH_AGENT"]="http://auth.example.com"

)

# Set email addresses and subject for report

EMAIL_RECIPIENT="youremail@example.com"

EMAIL_SUBJECT="Profile Option Validation Report"

# Set report file name and path

REPORT_FILE_NAME="profile_option_report.html"

REPORT_FILE_PATH="/path/to/report/directory/$REPORT_FILE_NAME"

# Initialize message and color variables

MESSAGE=""

COLOR="green"

# Loop through profile option names and check their values

for PROFILE_OPTION_NAME in "${!PROFILE_OPTIONS[@]}"; do

EXPECTED_VALUE="${PROFILE_OPTIONS[$PROFILE_OPTION_NAME]}"

# Get current value of profile option

CURRENT_VALUE=$(sqlplus -s username/password << EOF

set heading off feedback off verify off

select PROFILE_OPTION_VALUE from FND_PROFILE_OPTION_VALUES where PROFILE_OPTION_NAME='$PROFILE_OPTION_NAME';

exit;

EOF

)

# Check if the value matches the expected value

if [[ "$CURRENT_VALUE" == "$EXPECTED_VALUE" ]]; then

# Value is correct

MESSAGE+="Profile option '$PROFILE_OPTION_NAME' has the expected value '$EXPECTED_VALUE'<br>"

else

# Value is incorrect

MESSAGE+="Profile option '$PROFILE_OPTION_NAME' has an incorrect value. Expected '$EXPECTED_VALUE', but found '$CURRENT_VALUE'<br>"

COLOR="red"

done

# Create HTML report

echo "<html><body><h2>$MESSAGE</h2></body></html>" > "$REPORT_FILE_PATH"

# Add CSS style to report based on color

echo "<style>h2 {color: $COLOR;}</style>" >> "$REPORT_FILE_PATH"

# Send report via email

mailx -a "Content-Type: text/html" -s "$EMAIL_SUBJECT" "$EMAIL_RECIPIENT" < "$REPORT_FILE_PATH"

Profile options validation script

#!/bin/bash

# Source the R12.2 application environment file

. /path/to/R12.2/envfile

# Specify profile option name and expected value

PROFILE_OPTION_NAME="EXAMPLE_PROFILE_OPTION"

EXPECTED_VALUE="EXPECTED_VALUE"

# Set email addresses and subject for report

EMAIL_RECIPIENT="youremail@example.com"

EMAIL_SUBJECT="Profile Option Validation Report"

# Set report file name and path

REPORT_FILE_NAME="profile_option_report.html"

REPORT_FILE_PATH="/path/to/report/directory/$REPORT_FILE_NAME"

# Get current value of profile option

CURRENT_VALUE=$(sqlplus -s username/password << EOF

set heading off feedback off verify off

select PROFILE_OPTION_VALUE from FND_PROFILE_OPTION_VALUES where PROFILE_OPTION_NAME='$PROFILE_OPTION_NAME';

exit;

EOF

)

# Check if the value matches the expected value

if [[ "$CURRENT_VALUE" == "$EXPECTED_VALUE" ]]; then

# Value is correct

MESSAGE="Profile option '$PROFILE_OPTION_NAME' has the expected value '$EXPECTED_VALUE'"

COLOR="green"

else

# Value is incorrect

MESSAGE="Profile option '$PROFILE_OPTION_NAME' has an incorrect value. Expected '$EXPECTED_VALUE', but found '$CURRENT_VALUE'"

COLOR="red"

# Create HTML report

echo "<html><body><h2>$MESSAGE</h2></body></html>" > "$REPORT_FILE_PATH"

# Add CSS style to report based on color

echo "<style>h2 {color: $COLOR;}</style>" >> "$REPORT_FILE_PATH"

# Send report via email

mailx -a "Content-Type: text/html" -s "$EMAIL_SUBJECT" "$EMAIL_RECIPIENT" < "$REPORT_FILE_PATH"

Tuesday, February 14, 2023

findout the errors from the log files

#!/bin/bash

# Define the location of the log files

LOG_DIR=/var/log

# Define the recipient email address

EMAIL_ADDRESS=user@example.com

# Search for error, warning, and failed messages in log files

ERRORS=$(grep -rniE 'error|warning|failed' $LOG_DIR | grep -v 'grep' | awk -F ":" '{print $1 " - Line " $2 ": " $3}')

if [ -n "$ERRORS" ]; then

# Send an email notification with the error messages

subject="Log file scan - $(date +%F)"

message="The following errors, warnings, or failures were found in log files:

$ERRORS"

echo "$message" | mail -s "$subject" $EMAIL_ADDRESS

else

# Send an email notification indicating no errors were found

subject="Log file scan - $(date +%F)"

message="No errors, warnings, or failures were found in log files."

echo "$message" | mail -s "$subject" $EMAIL_ADDRESS

Sunday, February 5, 2023

GREP THE S_PARAMETERS FROM CONTEXT_FILE

#!/bin/bash

. $HOME/EBSapps.env run

# Define the path to the directory containing the XML files

dir_path='$CONTEXT_FILE'

# Define the recipient email address

email_address='recipient@example.com'

# Create a temporary file to store the HTML output

output_file=$(mktemp)

# Write the HTML header to the output file

echo '<html>' > $output_file

echo '<head>' >> $output_file

echo '<style>' >> $output_file

echo 'table {' >> $output_file

echo ' border-collapse: collapse;' >> $output_file

echo '}' >> $output_file

echo 'th, td {' >> $output_file

echo ' border: 1px solid black;' >> $output_file

echo ' padding: 8px;' >> $output_file

echo '}' >> $output_file

echo '</style>' >> $output_file

echo '</head>' >> $output_file

echo '<body>' >> $output_file

# Write the table header to the output file

echo '<table>' >> $output_file

echo '<tr>' >> $output_file

echo '<th>File Name</th>' >> $output_file

echo '<th>Line Number</th>' >> $output_file

echo '<th>s_ Parameter</th>' >> $output_file

echo '</tr>' >> $output_file

# Loop through each XML file in the directory

for file in $dir_path/*.xml; do

# Get the filename without the path

filename=$(basename $file)

# Search the file for lines containing "s_"

lines=$(grep -n "s_" $file)

# Loop through each line

while read -r line; do

# Get the line number and the contents of the line

line_number=$(echo $line | cut -d ":" -f 1)

contents=$(echo $line | cut -d ":" -f 2-)

# Write a table row to the output file

echo '<tr>' >> $output_file

echo "<td>$filename</td>" >> $output_file

echo "<td>$line_number</td>" >> $output_file

echo "<td>$contents</td>" >> $output_file

echo '</tr>' >> $output_file

done <<< "$lines"

done

# Write the HTML footer to the output file

echo '</table>' >> $output_file

echo '</body>' >> $output_file

echo '</html>' >> $output_file

# Send the HTML output as an email

mail -a "Content-Type: text/html" -s "s_ Parameters from XML Files" $email_address < $output_file

# Clean up the temporary file

rm $output_file

concurrent manager status shell

#!/bin/bash

# Define the SQL query

SQL_QUERY="SELECT cm.CONCURRENT_PROGRAM_NAME,

cmr.STATUS,

cmr.ACTUAL_START_DATE,

cmr.ACTUAL_COMPLETION_DATE

FROM fnd_concurrent_requests cmr,

fnd_concurrent_programs cm

WHERE cmr.CONCURRENT_PROGRAM_ID = cm.CONCURRENT_PROGRAM_ID

AND cmr.REQUEST_ID = (SELECT MAX(REQUEST_ID)

FROM fnd_concurrent_requests

WHERE concurrent_program_id = cm.concurrent_program_id);"

# Execute the SQL query and write the results to a file

sqlplus -s <username>/<password>@<database> << EOF > concurrent_manager_status.txt

SET HEADING OFF

SET FEEDBACK OFF

SET LINESIZE 1000

SET PAGESIZE 0

$SQL_QUERY

EOF

# Define the recipient email address

EMAIL_TO=<recipient_email_address>

# Define the subject and message for the email

SUBJECT="Concurrent Manager Status"

MESSAGE="Attached is the current status of the concurrent manager."

# Send the email with the attachment

mail -s "$SUBJECT" -a concurrent_manager_status.txt "$EMAIL_TO" <<< "$MESSAGE"

# Clean up the temporary file

rm concurrent_manager_status.txt

Saturday, February 4, 2023

Invalid

#!/bin/bash

# Connect to the database and retrieve the list of invalid objects

invalid_objects=$(sqlplus -S user/password@database <<EOF

SET HEADING OFF

SET FEEDBACK OFF

SET PAGESIZE 0

SELECT object_name, object_type, status FROM dba_objects WHERE status = 'INVALID';

EXIT;

EOF

)

# Create the HTML file

cat > invalid_objects.html <<EOF

<html>

<head>

<title>List of Invalid Objects in Oracle Database</title>

</head>

<body>

<h1>List of Invalid Objects in Oracle Database</h1>

<tr>

<th>Object Name</th>

<th>Object Type</th>

<th>Status</th>

</tr>

$(echo "$invalid_objects" | while read line; do

object_name=$(echo $line | awk '{print $1}')

object_type=$(echo $line | awk '{print $2}')

status=$(echo $line | awk '{print $3}')

echo "<tr><td>$object_name</td><td>$object_type</td><td>$status</td></tr>"

done)

</table>

</body>

</html>

EOF

Friday, February 3, 2023

Validate Multiple log files

#!/bin/bash

# Define the log file directory

log_file_dir="/path/to/log/files"

# Define the HTML file name and header

html_file="log_file_validation.html"

html_header="<html><head><title>Log File Validation Report</title></head><body>"

html_footer="</body></html>"

# Define the recipient email address

recipient_email="recipient@example.com"

# Initialize the HTML file

echo "$html_header" > "$html_file"

# Validate the log files

echo "<h2>Log File Validation Report</h2>" >> "$html_file"

echo "<table border='1'>" >> "$html_file"

echo "<tr><th>File Name</th><th>Result</th></tr>" >> "$html_file"

for log_file in "$log_file_dir"/*.log; do

if [ -f "$log_file" ]; then

log_file_size=$(wc -c "$log_file" | awk '{print $1}')

if [ "$log_file_size" -gt 0 ]; then

validate_result="Success"

else

validate_result="Failed"

else

validate_result="Failed"

echo "<tr><td>$log_file</td><td>$validate_result</td></tr>" >> "$html_file"

done

echo "</table>" >> "$html_file"

# Add the HTML file footer

echo "$html_footer" >> "$html_file"

# Send the HTML file as an email attachment

mail -s "Log File Validation Report" -a "$html_file" "$recipient_email" <<EOF

Please find the attached log file validation report.

EOF

sanity part 2

#!/bin/bash

# Define the target database details

target_db_name="TARGET_DB"

target_db_user="TARGET_USER"

target_db_pass="TARGET_PASS"

# Define the HTML file name and header

html_file="table_validation.html"

html_header="<html><head><title>Table Validation Report</title></head><body>"

html_footer="</body></html>"

# Define the recipient email address

recipient_email="recipient@example.com"

# Initialize the HTML file

echo "$html_header" > "$html_file"

# Validate tables in the target database

tables_to_validate=$(sqlplus -s "$target_db_user/$target_db_pass@$target_db_name" <<EOF

set heading off;

set feedback off;

set verify off;

select table_name from user_tables;

exit;

EOF

)

# Write the table validation result to the HTML file

echo "<h2>Table Validation Report</h2>" >> "$html_file"

echo "<table border='1'>" >> "$html_file"

echo "<tr><th>Table Name</th><th>Result</th></tr>" >> "$html_file"

for table in $tables_to_validate; do

validate_result=$(sqlplus -s "$target_db_user/$target_db_pass@$target_db_name" <<EOF

set heading off;

set feedback off;

set verify off;

declare

l_result varchar2(20);

begin

execute immediate 'analyze table $table validate structure';

l_result := 'Success';

exception

when others then

l_result := 'Failed';

end;

select l_result from dual;

exit;

EOF

)

echo "<tr><td>$table</td><td>$validate_result</td></tr>" >> "$html_file"

done

echo "</table>" >> "$html_file"

# Add the HTML file footer

echo "$html_footer" >> "$html_file"

# Send the HTML file as an email attachment

mail -s "Table Validation Report" -a "$html_file" "$recipient_email" <<EOF

Please find the attached table validation report.

EOF

oracle database clone sanity in html output

#Here's a shell script that performs a basic clone sanity check for an Oracle database and outputs the result in an HTML file#

#!/bin/bash

# Define the target database details

target_db_name="TARGET_DB"

target_db_user="TARGET_USER"

target_db_pass="TARGET_PASS"

# Define the HTML file name and header

html_file="clone_sanity_check.html"

html_header="<html><head><title>Clone Sanity Check Report</title></head><body>"

html_footer="</body></html>"

# Initialize the HTML file

echo "$html_header" > "$html_file"

# Perform the database clone sanity check

# Check the target database connection

target_conn_check=$(sqlplus -s "$target_db_user/$target_db_pass@$target_db_name" <<EOF

set heading off;

set feedback off;

set verify off;

select 'Success' from dual;

exit;

EOF

)

# Write the target connection check result to the HTML file

echo "<h2>Target Database Connection Check</h2>" >> "$html_file"

if [ "$target_conn_check" == "Success" ]; then

echo "<p style='color: green;'>Successful</p>" >> "$html_file"

else

echo "<p style='color: red;'>Failed</p>" >> "$html_file"

# Add the HTML file footer

echo "$html_footer" >> "$html_file"

Monday, November 28, 2022

Database Initialization Parameters for Oracle 19c

1 Required Parameters

The following list describes database initialization parameters required for this specific release of the Oracle Database 19c and is in addition to the common database initialization parameters which can be referred to in note 396009.1.

####################################################################
#
# Oracle E-Business Suite Release 12
# Release-Specific Database Initialization Parameters for 19c
#
####################################################################
compatible = 19.0.0 #MP
optimizer_adaptive_plans = TRUE #MP
optimizer_adaptive_statistics = FALSE #MP
pga_aggregate_limit = 0 #MP
temp_undo_enabled = FALSE
_pdb_name_case_sensitive = TRUE #MP
event='10946 trace name context forever, level 8454144' #MP
_optimizer_gather_stats_on_conventional_dml = FALSE #MP
_optimizer_use_stats_on_conventional_dml = FALSE #MP
optimizer_real_time_statistics = FALSE #MP

###############################################################################
#
# End of Release-Specific Database Initialization Parameters Section for 19c
#
###############################################################################

2 Parameter Removal List for Oracle Database 19c

If they exist, you should remove the following parameters from your database initialization parameters file for Oracle Database 19c.

Note: Parameters may appear on a "removal list" because they are obsolete; because the default value is required and no other value may be set; or to cater for certain special cases where a non-default value has to be set to meet specific needs (currently, there is only one such case, which is described in Section 6).

_kks_use_mutex_pin
_shared_pool_reserved_min_alloc
_sqlexec_progression_cost
exafusion_enabled
exclude_seed_cdb_view
global_context_pool_size
max_enabled_roles
o7_dictionary_accessibility
olap_page_pool_size
optimizer_adaptive_features
optimizer_features_enable
parallel_automatic_tuning
parallel_degree_level
parallel_io_cap_enabled
parallel_server
parallel_server_instances
plsql_compiler_flags
plsql_native_library_dir
plsql_native_library_subdir_count
plsql_optimize_level
standby_archive_dest
timed_statistics
use_indirect_data_buffers
utl_file_dir

Thursday, November 24, 2022

DB LINKS

The following script queries the DBA_DB_LINKS view to access link information:

COL OWNER FORMAT a10

COL USERNAME FORMAT A8 HEADING "USER"

COL DB_LINK FORMAT A30

COL HOST FORMAT A7 HEADING "SERVICE"

SELECT * FROM DBA_DB_LINKS

COL DB_LINK FORMAT A25

COL OWNER_ID FORMAT 99999 HEADING "OWNID"

COL LOGGED_ON FORMAT A5 HEADING "LOGON"

COL HETEROGENEOUS FORMAT A5 HEADING "HETER"

COL PROTOCOL FORMAT A8

COL OPEN_CURSORS FORMAT 999 HEADING "OPN_CUR"

COL IN_TRANSACTION FORMAT A3 HEADING "TXN"

COL UPDATE_SENT FORMAT A6 HEADING "UPDATE"

COL COMMIT_POINT_STRENGTH FORMAT 99999 HEADING "C_P_S"

SELECT * FROM V$DBLINK

Example 32-1 Determining the Host of Outgoing Database Links

This query results show the connect data, including the host name, for each outgoing

database link.

SELECT DB_LINK, DBMS_TNS.RESOLVE_TNSNAME(HOST) FROM DBA_DB_LINKS;

SELECT DB_NAME, HOST_NAME, FIRST_LOGON_TIME, LAST_LOGON_TIME

FROM DBA_DB_LINK_SOURCES;

Monday, November 7, 2022

Performance Tunning

SQL History:

All in one:

set lines 1000 pages 9999

COL instance_number FOR 9999 HEA 'Inst';

COL end_time HEA 'End Time';

COL plan_hash_value HEA 'Plan|Hash Value';

COL executions_total FOR 999,999 HEA 'Execs|Total';

COL rows_per_exec HEA 'Rows Per Exec';

COL et_secs_per_exec HEA 'Elap Secs|Per Exec';

COL cpu_secs_per_exec HEA 'CPU Secs|Per Exec';

COL io_secs_per_exec HEA 'IO Secs|Per Exec';

COL cl_secs_per_exec HEA 'Clus Secs|Per Exec';

COL ap_secs_per_exec HEA 'App Secs|Per Exec';

COL cc_secs_per_exec HEA 'Conc Secs|Per Exec';

COL pl_secs_per_exec HEA 'PLSQL Secs|Per Exec';

COL ja_secs_per_exec HEA 'Java Secs|Per Exec';

SELECT 'gv$dba_hist_sqlstat' source,h.instance_number,

TO_CHAR(CAST(s.begin_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') snap_time,

TO_CHAR(CAST(s.end_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') end_time,

h.sql_id,

h.plan_hash_value,

h.executions_total,

TO_CHAR(ROUND(h.rows_processed_total / h.executions_total), '999,999,999,999') rows_per_exec,

TO_CHAR(ROUND(h.elapsed_time_total / h.executions_total / 1e6, 3), '999,990.000') et_secs_per_exec,

TO_CHAR(ROUND(h.cpu_time_total / h.executions_total / 1e6, 3), '999,990.000') cpu_secs_per_exec,

TO_CHAR(ROUND(h.iowait_total / h.executions_total / 1e6, 3), '999,990.000') io_secs_per_exec,

TO_CHAR(ROUND(h.clwait_total / h.executions_total / 1e6, 3), '999,990.000') cl_secs_per_exec,

TO_CHAR(ROUND(h.apwait_total / h.executions_total / 1e6, 3), '999,990.000') ap_secs_per_exec,

TO_CHAR(ROUND(h.ccwait_total / h.executions_total / 1e6, 3), '999,990.000') cc_secs_per_exec,

TO_CHAR(ROUND(h.plsexec_time_total / h.executions_total / 1e6, 3), '999,990.000') pl_secs_per_exec,

TO_CHAR(ROUND(h.javexec_time_total / h.executions_total / 1e6, 3), '999,990.000') ja_secs_per_exec

FROM dba_hist_sqlstat h, dba_hist_snapshot s WHERE h.sql_id = '&sql_id'

AND h.executions_total > 0 AND s.snap_id = h.snap_id

AND s.dbid = h.dbid AND s.instance_number = h.instance_number

UNION ALL

SELECT 'gv$sqlarea_plan_hash' source,h.inst_id,

TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') snap_time,

TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') end_time,

h.sql_id, h.plan_hash_value, h.executions,

TO_CHAR(ROUND(h.rows_processed / h.executions), '999,999,999,999') rows_per_exec,

TO_CHAR(ROUND(h.elapsed_time / h.executions / 1e6, 3), '999,990.000') et_secs_per_exec,

TO_CHAR(ROUND(h.cpu_time / h.executions / 1e6, 3), '999,990.000') cpu_secs_per_exec,

TO_CHAR(ROUND(h.USER_IO_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') io_secs_per_exec,

TO_CHAR(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') cl_secs_per_exec,

TO_CHAR(ROUND(h.APPLICATION_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') ap_secs_per_exec,

TO_CHAR(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') cc_secs_per_exec,

TO_CHAR(ROUND(h.PLSQL_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') pl_secs_per_exec,

TO_CHAR(ROUND(h.JAVA_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') ja_secs_per_exec

FROM gv$sqlarea_plan_hash h

WHERE h.sql_id = '&&sql_id'

AND h.executions > 0

order by source ;

AWR DATA:

set lines 1000 pages 9999

COL instance_number FOR 9999 HEA 'Inst';

COL end_time HEA 'End Time';

COL plan_hash_value HEA 'Plan|Hash Value';

COL executions_total FOR 999,999 HEA 'Execs|Total';

COL rows_per_exec HEA 'Rows Per Exec';

COL et_secs_per_exec HEA 'Elap Secs|Per Exec';

COL cpu_secs_per_exec HEA 'CPU Secs|Per Exec';

COL io_secs_per_exec HEA 'IO Secs|Per Exec';

COL cl_secs_per_exec HEA 'Clus Secs|Per Exec';

COL ap_secs_per_exec HEA 'App Secs|Per Exec';

COL cc_secs_per_exec HEA 'Conc Secs|Per Exec';

COL pl_secs_per_exec HEA 'PLSQL Secs|Per Exec';

COL ja_secs_per_exec HEA 'Java Secs|Per Exec';

SELECT h.instance_number,

TO_CHAR(CAST(s.begin_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') snap_time,

TO_CHAR(CAST(s.end_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') end_time,

h.sql_id, h.plan_hash_value, h.executions_total,