During tuning a query, I found one table has stale statistics. While running gather stats for that table, got below error. Let me demonstrate with a demo table: SQL> execute…
Read MoreThe simplest method is to add a hint to the query to overcome ORA-00001 Added in 11.2, the ignore_row_on_dupkey_index hint silently ignores duplicate values: insert /*+ ignore_row_on_dupkey_index ( acct (…
Read MoreA virtual column applies a function to a column in the table. The database only computes this at runtime. The value is not stored in the table. So instead of…
Read MoreThe smallest unit of data storage in Oracle Database is the block. This defaults to 8k (8,192 bytes). Each index entry must fit within one block. So the maximum size…
Read MoreImpact: Unknown underscore parameters without proper advice from metalink, would impact the database. Solution: We need to ensure that we have proper advises from Oracle Support on those underscore parameters…
Read MoreBelow script is for finding the Apex page hits ********************************************************************************************************** /home/oracle/backup/scripts/pagehits.sh . /home/oracle/db.env export SC_LOG=/Backup/pagehits/fms_`date +%d_%b_%H_%M_%Y`.log sqlplus -s <<EOF >>/usr/tmp/tmp1.log connect /as sysdba spool \$SC_LOG select flow_id,step_id,count(*) from FLOWS_030100.WWV_FLOW_ACTIVITY_LOG where…
Read MoreBelow script is for finding the Apex Page Time ********************************************************************************************************** /home/oracle/backup/scripts/pagetime.sh . /home/oracle/db.env export SC_LOG=/Backup/pagetime/fms_`date +%d_%b_%Y_%H_%M`.log sqlplus -s <<EOF >>/usr/tmp/tmp2.log connect /as sysdba spool \$SC_LOG select flow_id,step_id,sum(elap)/count(*) “Average” from FLOWS_030100.WWV_FLOW_ACTIVITY_LOG…
Read MoreWe can use the below script for moving files (like export dumps/RMAN backups) to FTP server for safety purpose. ******************************************************************************************************************** cat /TESTDB/app/bkp_scripts/test_sunday_to_ftp.sh . /home/oracle/test.env USERNAME=”—-Enter your username here—-” PASSWORD=”—-Enter your…
Read MoreIntroduction This Post Provides the SQL Query that returns HCM Extract Input Values in BI Report Query SELECT TO_DATE (par_val.flow_param_value, ‘YYYY-MM-DD’) FROM pay_flow_instances flow, pay_flow_parameters flw_par, pay_flow_param_values par_val WHERE…
Read MoreIntroduction This Post Provides the SQL Query that returns Employee’s Supervisor Details Query SELECT papf.person_number employee_number, ppnf.full_name employee_name, papf_s.person_number manager_emp_number, ppnf_s.full_name manager_name FROM per_all_people_f papf, per_person_names_f ppnf, per_all_assignments_m paam,…
Read More