Oracle DBA Scripts Collection – A Must-Have Toolkit for Every DBA

Oracle DBA Scripts Collection – A Must-Have Toolkit for Every DBA

As Oracle Database Administrators, we perform repetitive monitoring, troubleshooting, tuning, and maintenance activities daily. Instead of reinventing the wheel every time, having a ready-made, organized script repository can significantly improve productivity and standardization.

In this article, I am sharing a very useful GitHub repository that can serve as a reference toolkit for DBAs:

GitHub Repository:
https://github.com/amanpandey1729/oracle-dba-scripts

---

About the Repository

This repository contains categorized Oracle DBA scripts covering monitoring, performance tuning, security checks, backup validation, tablespace management, memory diagnostics, OS checks, and more.

The scripts are well organized into folders, making it easy to locate the required utility based on your task.

---

Major Script Categories

• Monitoring – Session checks, blocking sessions, active sessions, resource usage
• Performance Tuning – Slow SQL detection, optimizer statistics, execution plan insights
• Tablespace Management – Tablespace usage, free space monitoring
• Memory Management – SGA, PGA analysis
• Backup and Recovery – Backup validation scripts
• Security – User privileges, roles, security checks
• OS Linux Checks – CPU, disk, memory health scripts
• Jobs and Scheduler – Job monitoring and failure checks

---

How to Use These Scripts

Step 1: Clone the repository

git clone https://github.com/amanpandey1729/oracle-dba-scripts.git

Step 2: Navigate to required folder based on your task.

Step 3: Review and customize scripts as per your environment (SID, DB_NAME, paths, etc.).

Step 4: Test in Non-Production before using in Production.

---

Why Every DBA Should Bookmark This

• ✔ Saves time during incident troubleshooting
• ✔ Helps standardize health checks across environments
• ✔ Good learning material for junior DBAs
• ✔ Ready reference during audits
• ✔ Can be integrated with cron / scheduler jobs

---

Important Note

All credits go to the original repository author. This article is shared as a reference and learning resource for the Oracle DBA community.

Always review scripts before running in production environments.

---

My Recommendation

I recommend maintaining your own customized DBA toolkit by combining:

• Open-source script collections like this
• Your internal automation scripts
• Enterprise-specific health check standards

This will help you build a strong, reusable DBA operations framework.

---

Happy Learning & Happy DBA Life! 🚀

Oracle Database Scripts – Full Script Listing

Oracle Database Scripts Repository

This repository contains a large collection of Oracle DBA utility scripts covering performance tuning, monitoring, RMAN, RAC, ASM, Data Guard, AWR, ASH, and general administration.

GitHub Repository:
https://github.com/fatdba/Oracle-Database-Scripts

Download Full Repository:
Click Code → Download ZIP from GitHub.

---

Complete Script Listing (Generated from Downloaded ZIP)

Click Here to Expand Full Script List

./++
./+imp_scr_oracle_daily
./+plusenv.sql
./+simplecool.sql
./activesessions_ash.sql
./Admin_General/+login.sql
./Admin_General/+plusenv.sql
./Admin_General/+simplecool.sql
./Admin_General/archive_new.sql
./Admin_General/archive.sql
./Admin_General/Bigtable_fortest.sql
./Admin_General/blocking_local.sql.com
./Admin_General/columns_usage.sql
./Admin_General/create_schedule_job.sql
./Admin_General/current_redologfile_usage.sql
./Admin_General/datafiles_fora_tablespace.sql
./Admin_General/dba_schedulerjobs.sql
./Admin_General/dbgrowth_permonth.sql
./Admin_General/dblink_checker_withJob.sql
./Admin_General/dblink_details.sql
./Admin_General/dbms_jobs_broken.sql
./Admin_General/dbserverdetails.sql
./Admin_General/dbtime.sql
./Admin_General/dbupgdiag.sql
./Admin_General/ddl_schema.sql
./Admin_General/ddl-objects.sql
./Admin_General/Fat200objects.sql
./Admin_General/file_block.sql
./Admin_General/file_border.sql
./Admin_General/find_objects_1.sql
./Admin_General/find_objects_2.sql
./Admin_General/find_objects.sql
./Admin_General/find_x$_basetables.sql
./Admin_General/flashback_main.sql
./Admin_General/get_dependent_ddl.sql
./Admin_General/hidden_params.sql
./Admin_General/hugepages.sh
./Admin_General/iostat_summary.sql
./Admin_General/jobs.sql
./Admin_General/killer.sql
./Admin_General/latest_tab_stats.sql
./Admin_General/logmining.sql
./Admin_General/materializedview.sql
./Admin_General/object_blocks.sql
./Admin_General/object_changes.sql
./Admin_General/object_dependencies.sql
./Admin_General/object_details.sql
./Admin_General/object_growth_trend.sql
./Admin_General/object_username.sql
./Admin_General/objectid.sql
./Admin_General/objects_changed.sql
./Admin_General/objects_matching_pattern.sql
./Admin_General/oem_informationpublishereport.sql
./Admin_General/oracle_license_info.sql
./Admin_General/Oracle_Test1.sql
./Admin_General/params.sql
./Admin_General/partitions_drop_future.sql
./Admin_General/partitions_expimp_stats.sql
./Admin_General/partitions_rowcount_each.sql
./Admin_General/partitions_synopsys_incremental.sql
./Admin_General/privileges_copy.sql
./Admin_General/redostats.sql
./Admin_General/resize-datafiles.sql
./Admin_General/running_jobs.sql
./Admin_General/scheduler_shellscript_dbmsscheduler.sql
./Admin_General/securefile_testdata.sql
./Admin_General/segments_inside_datafile.sql
./Admin_General/sequences.sql
./Admin_General/sessions_active.sql
./Admin_General/sessions_main.sql
./Admin_General/sessionusage_upanddownstats.sql
./Admin_General/setnlsdateformat.sql
./Admin_General/shrink_datafiles.sql
./Admin_General/sid_from_ospid.sql
./Admin_General/smon_rollback_monitoring.sql
./Admin_General/states.sql
./Admin_General/tab_ind_detailsschema.sql
./Admin_General/table_set_preference.sql
./Admin_General/tablespace_boundary.sql
./Admin_General/tablespace_free-sum.sql
./Admin_General/tablespaceinfo.sql
./Admin_General/tmp_stats.sql
./Admin_General/top_ts_segments.sql
./Admin_General/top_workarea.sql
./Admin_General/top10.sql
./Admin_General/trace_all_session_ofauser.sql
./Admin_General/tracking_database_usage.sql
./Admin_General/undo_another.sql
./Admin_General/undo_somemore.sql
./Admin_General/undo_stats.sql
./Admin_General/undomain.sql
./Admin_General/unregister_mview_snap.sql
./Admin_General/unused_space.sql
./Admin_General/what_has_changed.sql
./all_stalestats.sql
./archive_new.sql
./archive.sql
./ash_cachebufferchain.sql
./ash_event_trend.sql
./ash_last_x_mins.sql
./ash_last_x_secs.sql
./ash_latch.sql
./ash_object_enq_contention.sql
./ash_pio_events.sql
./ash_sid.sql
./ash_sqlid_mins.sql
./ash_top_events.sql
./ash_toppga_2mb.sql
./asm_diskdrop.sql
./ASM/+login.sql
./ASM/+plusenv.sql
./ASM/asm_diskdrop.sql
./ASM/asmdiskgroup.sql
./ASM/asmfilevolinfo.sql
./ASM/asmspaceused.sql
./asmdiskgroup.sql
./asmfilevolinfo.sql
./asmspaceused.sql
./autostatsgatherdet.sql
./awr_manualsnap.sql
./awr_modify_topnsql.sql
./awretention.sql
./BackupAndRecovery/+login.sql
./BackupAndRecovery/+plusenv.sql
./BackupAndRecovery/rman_bkpdetails.sql
./BackupAndRecovery/rman_datafile_backup.sql
./BackupAndRecovery/rman_everything.sql
./BackupAndRecovery/rman_fulldb_backupscript.sql
./BackupAndRecovery/rman_incremental_backup.sql
./BackupAndRecovery/rman_main.sql
./BackupAndRecovery/rman_monitoring.sql
./BackupAndRecovery/rman_tablespace_backup.sql
./BackupAndRecovery/rman1.sql
./BackupAndRecovery/rmanbest.sql
./Bigtable_fortest.sql
./bindvariables_fromawr.sql
./blocking_local.sql
./check_dataguard_config.sql
./check_modify_awrretention.sql
./coe_xfr_sql_profile.sql
./columns_usage.sql
./cpu_stats.sql
./cpubusytime_db.sql
./cpuusage.sql
./create_schedule_job.sql
./create_sql_patch.sql
./current_redologfile_usage.sql
./cursor_notsharing_reason.sql
./datampump_monitoring.sql
./datapump_10046trace_process.sql
./datapump_cleanup_orphanedjobs.sql
./datapump_samplescript.sh
./DataPump/data_pump_monitor_core.sql
./DataPump/datampump_monitoring.sql
./DataPump/datapump_10046trace_process.sql
./DataPump/datapump_cleanup_orphanedjobs.sql
./DataPump/datapump_samplescript.sh
./DataPump/partitions_expimp_stats.sql
./db_hang.sql
./dba_hist_snapshot.sql
./dba_hist_sqlbind.sql
./dba_schedulerjobs.sql
./dbgrowth_permonth.sql
./dblink_details.sql
./dbms_jobs_broken.sql
./dbserverdetails.sql
./dbtime.sql
./dbupgdiag.sql
./ddl_schema.sql
./ddl-objects.sql
./disable_resourcemanager.sql
./dop_tablesindexes.sql
./drop_phv_baseline.sql
./drop_sql_baseline.sql
./drop_sql_profile.sql
./drop_sqlplan_baselines.sql
./dynamic_sampling_objects.sql
./enq_tx_indexcontention_split.sql
./exadata_cellperf.sql
./Exadata/+login.sql
./Exadata/+plusenv.sql
./Exadata/exadata_cellperf.sql
./failover_stats.sql
./Fat200objects.sql
./file_block.sql
./file_border.sql
./find_objects_1.sql
./find_objects_2.sql
./find_objects.sql
./find_sql_2.sql
./find_sql_pio_1.sql
./find_sql_pio.sql
./find_sql.sql
./find_x$_basetables.sql
./fklocking.sql
./flashback_main.sql
./flushsql_cursor.sql
./FULL_FILE_LIST.txt
./gather_stats.sql
./generate_set_tablestats.sql
./get_dependent_ddl.sql
./get_optimizer_trace.sql
./getbinds-sqlid.sql
./goldengate_healthcheck.sql
./GoldenGate/+login.sql
./GoldenGate/+plusenv.sql
./GoldenGate/goldengate_healthcheck.sql
./hidden_params.sql
./hidden.sql
./high_version_count.sql
./hints.sql
./hot_librarycache_objects.sql
./hourly_segment_stats.sql
./hugepages.sh
./idx_2.sql
./idx_3.sql
./idx_fragmentation.sql
./idx_main.sql
./idx_split_2.sql
./idx_split_session.sql
./idx_split.sql
./idx_usage.sql
./indexlargerthantable.sql
./iointensive_sql_mem.sql
./iostat_summary.sql
./iousage_tempfiles.sql
./latchdetails.sql
./librarycache_locks.sql
./lockingmother.sql
./logfilesync_lfsdiag.sql
./mismatch.sql
./mutex_sleep.sql
./mypoormanscript_fancy.sql
./mypoormanscript.sh
./mypoormanscript.sql
./mysid.sql
./Nonindexedfkconstraints.sql
./object_dependencies.sql
./objects_waiting_miss_sleep.sql
./OFE_12.1.0.2_in_12.2.0.1.sql
./oracle_license_info.sql
./Oracle_Test1.sql
./outlinedata_for_spmsqlbaselines.sql
./p_ash_blockers.sql
./p_ash_events.sql
./parentchild_rel.sql
./partitions_drop_future.sql
./partitions_expimp_stats.sql
./partitions_rowcount_each.sql
./partitions_synopsys_incremental.sql
./pdhc_v1.2.sql
./PerformanceTuning_Mgmt/+login.sql
./PerformanceTuning_Mgmt/+plusenv.sql
./PerformanceTuning_Mgmt/active_sql_workarea.sql
./PerformanceTuning_Mgmt/activesessions_ash.sql
./PerformanceTuning_Mgmt/all_stalestats.sql
./PerformanceTuning_Mgmt/archive_new.sql
./PerformanceTuning_Mgmt/ash_cachebufferchain.sql
./PerformanceTuning_Mgmt/ash_event_trend.sql
./PerformanceTuning_Mgmt/ash_last_x_mins.sql
./PerformanceTuning_Mgmt/ash_last_x_secs.sql
./PerformanceTuning_Mgmt/ash_latch.sql
./PerformanceTuning_Mgmt/ash_object_enq_contention.sql
./PerformanceTuning_Mgmt/ash_pio_events.sql
./PerformanceTuning_Mgmt/ash_sid.sql
./PerformanceTuning_Mgmt/ash_sqlid_mins.sql
./PerformanceTuning_Mgmt/ash_top_events.sql
./PerformanceTuning_Mgmt/ash_toppga_2mb.sql
./PerformanceTuning_Mgmt/auto_stats_collection.sql
./PerformanceTuning_Mgmt/autostatsgatherdet.sql
./PerformanceTuning_Mgmt/awr_manualsnap.sql
./PerformanceTuning_Mgmt/awr_modify_topnsql.sql
./PerformanceTuning_Mgmt/awretention.sql
./PerformanceTuning_Mgmt/bindvariables_fromawr.sql
./PerformanceTuning_Mgmt/check_modify_awrretention.sql
./PerformanceTuning_Mgmt/coe_xfr_sql_profile.sql
./PerformanceTuning_Mgmt/columns_usage.sql
./PerformanceTuning_Mgmt/cpu_stats.sql
./PerformanceTuning_Mgmt/cpubusytime_db.sql
./PerformanceTuning_Mgmt/cpuusage.sql
./PerformanceTuning_Mgmt/create_sql_patch.sql
./PerformanceTuning_Mgmt/cursor_notsharing_reason.sql
./PerformanceTuning_Mgmt/db_hang.sql
./PerformanceTuning_Mgmt/dba_hist_snapshot.sql
./PerformanceTuning_Mgmt/dba_hist_sqlbind.sql
./PerformanceTuning_Mgmt/dbgrowth_permonth.sql
./PerformanceTuning_Mgmt/dbtime.sql
./PerformanceTuning_Mgmt/disable_resourcemanager.sql
./PerformanceTuning_Mgmt/dop_tablesindexes.sql
./PerformanceTuning_Mgmt/drop_phv_baseline.sql
./PerformanceTuning_Mgmt/drop_sql_baseline.sql
./PerformanceTuning_Mgmt/drop_sql_profile.sql
./PerformanceTuning_Mgmt/drop_sqlplan_baselines.sql
./PerformanceTuning_Mgmt/dynamic_sampling_objects.sql
./PerformanceTuning_Mgmt/enq_tx_indexcontention_split.sql
./PerformanceTuning_Mgmt/find_sql_2.sql
./PerformanceTuning_Mgmt/find_sql_pio_1.sql
./PerformanceTuning_Mgmt/find_sql_pio.sql
./PerformanceTuning_Mgmt/find_sql.sql
./PerformanceTuning_Mgmt/fklocking.sql
./PerformanceTuning_Mgmt/flushsql_cursor.sql
./PerformanceTuning_Mgmt/gather_stats.sql
./PerformanceTuning_Mgmt/generate_set_tablestats.sql
./PerformanceTuning_Mgmt/get_optimizer_trace.sql
./PerformanceTuning_Mgmt/getbinds-sqlid.sql
./PerformanceTuning_Mgmt/high_version_count.sql
./PerformanceTuning_Mgmt/hints.sql
./PerformanceTuning_Mgmt/hot_librarycache_objects.sql
./PerformanceTuning_Mgmt/hourly_segment_stats.sql
./PerformanceTuning_Mgmt/idx_2.sql
./PerformanceTuning_Mgmt/idx_3.sql
./PerformanceTuning_Mgmt/idx_fragmentation.sql
./PerformanceTuning_Mgmt/idx_main.sql
./PerformanceTuning_Mgmt/idx_split_2.sql
./PerformanceTuning_Mgmt/idx_split_session.sql
./PerformanceTuning_Mgmt/idx_split.sql
./PerformanceTuning_Mgmt/idx_usage.sql
./PerformanceTuning_Mgmt/indexlargerthantable.sql
./PerformanceTuning_Mgmt/iointensive_sql_mem.sql
./PerformanceTuning_Mgmt/iousage_tempfiles.sql
./PerformanceTuning_Mgmt/latch_waits.sql
./PerformanceTuning_Mgmt/latchdetails.sql
./PerformanceTuning_Mgmt/latchholder.sql
./PerformanceTuning_Mgmt/libcache_lock_bysid.sql
./PerformanceTuning_Mgmt/libcache_lock_waits.sql
./PerformanceTuning_Mgmt/libcache_pin_waits.sql
./PerformanceTuning_Mgmt/libcache_pins_bysid.sql
./PerformanceTuning_Mgmt/Librarycache_lock_holders.sql
./PerformanceTuning_Mgmt/librarycache_locks.sql
./PerformanceTuning_Mgmt/locked_objects_summary.sql
./PerformanceTuning_Mgmt/locked_objects.sql
./PerformanceTuning_Mgmt/lockedstats.sql
./PerformanceTuning_Mgmt/lockingmother.sql
./PerformanceTuning_Mgmt/logfilesync_lfsdiag.sql
./PerformanceTuning_Mgmt/mismatch.sql
./PerformanceTuning_Mgmt/module_pio.sql
./PerformanceTuning_Mgmt/mutex_sleep.sql
./PerformanceTuning_Mgmt/mutex.sql
./PerformanceTuning_Mgmt/mysid.sql
./PerformanceTuning_Mgmt/Nonindexedfkconstraints.sql
./PerformanceTuning_Mgmt/objects_waiting_miss_sleep.sql
./PerformanceTuning_Mgmt/OFE_12.1.0.2_in_12.2.0.1.sql
./PerformanceTuning_Mgmt/outlinedata_for_spmsqlbaselines.sql
./PerformanceTuning_Mgmt/p_ash_blockers.sql
./PerformanceTuning_Mgmt/p_ash_events.sql
./PerformanceTuning_Mgmt/parentchild_rel.sql
./PerformanceTuning_Mgmt/pga_size_estim.sql
./PerformanceTuning_Mgmt/pgaintensivesql.sql
./PerformanceTuning_Mgmt/pretty_metadata.sql
./PerformanceTuning_Mgmt/procwatcher_prw.sql
./PerformanceTuning_Mgmt/px_query_details.sql
./PerformanceTuning_Mgmt/px_query_monitoring.sql
./PerformanceTuning_Mgmt/PXHCDR.sql
./PerformanceTuning_Mgmt/rat_approx_replayprogress.sql
./PerformanceTuning_Mgmt/rat_blocking_sessions.sql
./PerformanceTuning_Mgmt/rat_replay_divergence.sql
./PerformanceTuning_Mgmt/rat_waitevents.sql
./PerformanceTuning_Mgmt/redundantindex.sql
./PerformanceTuning_Mgmt/segments_stats_daily.sql
./PerformanceTuning_Mgmt/sequenceexhaustion80.sql
./PerformanceTuning_Mgmt/session-wait-by-sid.sql
./PerformanceTuning_Mgmt/sessions_librarycachelock.sql
./PerformanceTuning_Mgmt/sga-details.sql
./PerformanceTuning_Mgmt/share.sql
./PerformanceTuning_Mgmt/shared_sqlcursor_reason.sql
./PerformanceTuning_Mgmt/sharedpool_advisory.sql
./PerformanceTuning_Mgmt/sharedpool_lru.sql
./PerformanceTuning_Mgmt/sharedpool_pinned.sql
./PerformanceTuning_Mgmt/sharedpool_stats.sql
./PerformanceTuning_Mgmt/sharedpool_statssubpool.sql
./PerformanceTuning_Mgmt/sharedpool_subpool.sql
./PerformanceTuning_Mgmt/showlock.sql
./PerformanceTuning_Mgmt/snap-session-waits.sql
./PerformanceTuning_Mgmt/sp-reserved.sql
./PerformanceTuning_Mgmt/sparse_Indexes.sql
./PerformanceTuning_Mgmt/sql_changed_elapsedtime.sql
./PerformanceTuning_Mgmt/sql_from_procedures.sql
./PerformanceTuning_Mgmt/sql_plan_baselines.sql
./PerformanceTuning_Mgmt/sql_planflip_new.sql
./PerformanceTuning_Mgmt/sqlflip_newbetter.sql
./PerformanceTuning_Mgmt/sqlflip1.sql
./PerformanceTuning_Mgmt/sqlflip2.sql
./PerformanceTuning_Mgmt/sqlhistory.sql
./PerformanceTuning_Mgmt/sqlid_info.sql
./PerformanceTuning_Mgmt/sqlid_main.sql
./PerformanceTuning_Mgmt/sqlid_phv.sql
./PerformanceTuning_Mgmt/sqlid_topworkarea.sql
./PerformanceTuning_Mgmt/sqloffload_iosaved.sql
./PerformanceTuning_Mgmt/sqlprofile_hints.sql
./PerformanceTuning_Mgmt/sqls_doing_fulltablescan.sql
./PerformanceTuning_Mgmt/sqlstats_history_sqlid.sql
./PerformanceTuning_Mgmt/sqlstats_main.sql
./PerformanceTuning_Mgmt/STA_SQLID_Cursor.sql
./PerformanceTuning_Mgmt/stale_stats.sql
./PerformanceTuning_Mgmt/stats_delete.sql
./PerformanceTuning_Mgmt/stats_get_preference.sql
./PerformanceTuning_Mgmt/stats_incremental.sql
./PerformanceTuning_Mgmt/stats_lock.sql
./PerformanceTuning_Mgmt/table_ind_pkfkcons_DDL.sql
./PerformanceTuning_Mgmt/table_stats_details.sql
./PerformanceTuning_Mgmt/tempintensivesql.sql
./PerformanceTuning_Mgmt/test.sql
./PerformanceTuning_Mgmt/top_sql_cpu.sql
./PerformanceTuning_Mgmt/top_sqls.sql
./PerformanceTuning_Mgmt/top10activesessions.sql
./PerformanceTuning_Mgmt/trace-onoff.sql
./PerformanceTuning_Mgmt/tracemy_sqlid.sql
./PerformanceTuning_Mgmt/treedump.sql
./PerformanceTuning_Mgmt/wait_Chains.sql
./PerformanceTuning_Mgmt/waiteventcontributors.sql
./PerformanceTuning_Mgmt/waits_lfs.sql
./PerformanceTuning_Mgmt/waits_sum.sql
./PerformanceTuning_Mgmt/waitstats.sql
./PerformanceTuning_Mgmt/whattypeof_histogram.sql
./PerformanceTuning_Mgmt/workarea.sql
./PerformanceTuning_Mgmt/xplan_adv_sqlid.sql
./PerformanceTuning_Mgmt/xplan_awr_phash.sql
./PerformanceTuning_Mgmt/xplan_awr.sql
./PerformanceTuning_Mgmt/xplan_objectname.sql
./PerformanceTuning_Mgmt/xplan_phash.sql
./PerformanceTuning_Mgmt/xplan_sid.sql
./PerformanceTuning_Mgmt/xplan_sqlhash.sql
./PerformanceTuning_Mgmt/xplan_sqlid_incursor.sql
./pgaintensivesql.sql
./privileges_copy.sql
./procwatcher_prw.sql
./px_query_details.sql
./px_query_monitoring.sql
./PXHCDR.sql
./rac_clock_sync.sql
./rac_diag.sql
./rac_longops.sql
./rac_main.sql
./RAC/+login.sql
./RAC/+plusenv.sql
./RAC/rac_clock_sync.sql
./RAC/rac_diag.sql
./RAC/rac_longops.sql
./RAC/rac_main.sql
./RAC/racdbstatus.sql
./racdbstatus.sql
./rat_approx_replayprogress.sql
./rat_blocking_sessions.sql
./rat_replay_divergence.sql
./rat_waitevents.sql
./redostats.sql
./redundantindex.sql
./rman_bkpdetails.sql
./rman_datafile_backup.sql
./rman_fulldb_backupscript.sql
./rman_incremental_backup.sql
./rman_main.sql
./rman_monitoring.sql
./rman_tablespace_backup.sql
./rman1.sql
./rowlocking.sql
./scheduler_shellscript_dbmsscheduler.sql
./segments_inside_datafile.sql
./segments_stats_daily.sql
./sequenceexhaustion80.sql
./sessions_active.sql
./sessions_librarycachelock.sql
./sessions_main.sql
./sessionusage_upanddownstats.sql
./sga-details.sql
./sharedpool_advisory.sql
./showlock.sql
./sid_from_ospid.sql
./smon_rollback_monitoring.sql
./sparse_Indexes.sql
./sql_changed_elapsedtime.sql
./sql_from_procedures.sql
./sql_planflip_new.sql
./sqlflip_newbetter.sql
./sqlflip1.sql
./sqlflip2.sql
./sqlhistory.sql
./sqlid_info.sql
./sqlid_main.sql
./sqlid_phv.sql
./sqloffload_iosaved.sql
./sqls_doing_fulltablescan.sql
./sqlstats_history_sqlid.sql
./STA_SQLID_Cursor.sql
./standby_core_collection.sql
./standby_monitorlag_rac.sql
./standby_single.sql
./Standby/+login.sql
./Standby/+plusenv.sql
./Standby/check_dataguard_config.sql
./Standby/DGDiagnostic.sh
./Standby/failover_stats.sql
./Standby/quick_gap_check.sql
./Standby/srdc_DGlogicalStby_diag.sql
./Standby/srdc_DGPhyStby_diag.sql
./Standby/srdc_DGPrimary_diag.sql
./Standby/standby_core_collection.sql
./Standby/standby_monitor.sql
./Standby/standby_monitorlag_rac.sql
./Standby/standby_single.sql
./Standby/standbygap.sql
./standbygap.sql
./stats_delete.sql
./stats_get_preference.sql
./stats_incremental.sql
./stats_lock.sql
./table_ind_pkfkcons_DDL.sql
./tablespaceinfo.sql
./tempintensivesql.sql
./Tools_And_Monitoring/+login.sql
./Tools_And_Monitoring/+plusenv.sql
./Tools_And_Monitoring/ashtop.sql
./Tools_And_Monitoring/dashtop.sql
./Tools_And_Monitoring/mypoormanscript_fancy.sql
./Tools_And_Monitoring/mypoormanscript.sh
./Tools_And_Monitoring/mypoormanscript.sql
./Tools_And_Monitoring/pdhc_v1.2.sql
./Tools_And_Monitoring/snapper.sql
./top_sqls.sql
./top10activesessions.sql
./trace_all_session_ofauser.sql
./tracemy_sqlid.sql
./tracking_database_usage.sql
./undo_somemore.sql
./undomain.sql
./wait_Chains.sql
./waiteventcontributors.sql
./what_has_changed.sql
./whattypeof_histogram.sql

---

Note: This list was generated from the downloaded repository package. All credits belong to the original author of the repository.

 

Check the Oracle database is Exadata

How do you check that the Oracle database is running on Exadata?

For checking we have to run the following command in SQLPLUS:

select decode(count(cell_path),0,'Non-Exadata','Exadata') "System" from v$cell;	

 

HOW TO MATCH OS PROCESS WITH ORACLE SESSION

Check for OS PID running on the database through TOP command.

SET LONG 200000000
SET LINES 300
SET PAGES 999
COL OSUSER FOR A6
COL DBUSER FOR A6
COL SID FOR 9999
COL SERIAL# FOR 9999999
COL PID FOR A8
COL SQL_ID FOR A15
COL STATUS FOR A10
COL CONNECTED_FROM FOR A20
COL MODULE FOR A10
COL PORT FOR 9999999
COL PROGRAM FOR A20
COL ACTIVE_TIME_SEC FOR 9999999999
COL PARSING_SCHEMA_NAME FOR A20 
COL SQL_FULLTEXT FOR A60

SELECT S.OSUSER,
       S.USERNAME DBUSER,
       S.SID,
       S.SERIAL#,
       P.SPID PID,
       S.SQL_ID,
       S.STATUS,
       S.MACHINE CONNECTED_FROM,
       S.PORT,
       S.PROGRAM,
       S.LAST_CALL_ET ACTIVE_TIME_SEC,
       Q.SQL_FULLTEXT
FROM
       V$SESSION S,
       V$PROCESS P,
       V$SQL Q
WHERE
       S.PADDR=P.ADDR AND
       S.SQL_ID=Q.SQL_ID AND 
       S.SQL_CHILD_NUMBER=Q.CHILD_NUMBER AND
       P.SPID=&PID;

Script to check the row count of partitions for a table

Script to check the row count of partitions for a table

count_tab.sql

set serverout on size 1000000set verify offdeclaresql_stmt varchar2(1024);row_count number;cursor get_tab isselect table_name,partition_namefrom dba_tab_partitionswhere table_owner=upper('&&TABLE_OWNER') and table_name='&&TABLE_NAME';begindbms_output.put_line('Checking Record Counts for table_name');dbms_output.put_line('Log file to numrows_part_&&TABLE_OWNER.lst ....');dbms_output.put_line('....');for get_tab_rec in get_tab loopBEGINsql_stmt := 'select count(*) from &&TABLE_OWNER..'||get_tab_rec.table_name||' partition ( '||get_tab_rec.partition_name||' )';
EXECUTE IMMEDIATE sql_stmt INTO row_count;dbms_output.put_line('Table '||rpad(get_tab_rec.table_name||'('||get_tab_rec.partition_name||')',50)||' '||TO_CHAR(row_count)||' rows.');exception when others thendbms_output.put_line('Error counting rows for table '||get_tab_rec.table_name);END;end loop;end;/set verify on

Row Count Of All Partitions Of A Table

 

Find Row Count Of All Partitions Of A Table

Query :- partition_count.sql 

set serverout on size 1000000

set verify off

declare

sql_stmt varchar2(1024);

row_count number;

cursor get_tab is

select table_name,partition_name

from dba_tab_partitions

where table_owner=upper('&&TABLE_OWNER') and table_name='&&TABLE_NAME';

begin

dbms_output.put_line('Checking Record Counts for table_name');

dbms_output.put_line('Log file to numrows_part_&&TABLE_OWNER.lst ....');

dbms_output.put_line('....');

for get_tab_rec in get_tab loop

BEGIN

sql_stmt := 'select count(*) from &&TABLE_OWNER..'||get_tab_rec.table_name

||' partition ( '||get_tab_rec.partition_name||' )';

EXECUTE IMMEDIATE sql_stmt INTO row_count;

dbms_output.put_line('Table '||rpad(get_tab_rec.table_name

||'('||get_tab_rec.partition_name||')',50)

||' '||TO_CHAR(row_count)||' rows.');

exception when others then

dbms_output.put_line

('Error counting rows for table '||get_tab_rec.table_name);

END;

end loop;

end;

/

set verify on

SQL> @partition_count.sql

Enter value for table_owner: ABCD

Enter value for table_name: ABCD

Checking Record Counts for table_name

Log file to numrows_part_ABCDlst ....

....

Table ABCD(P_ADCH_CL_201301)                  118150 rows.

Table ABCD(SYS_P12259)                        327701 rows.

Table ABCD(SYS_P12264)                        324872 rows.

Table ABCD(SYS_P12269)                        315435 rows.

Table ABCD(SYS_P12274)                        333191 rows.

Table ABCD(SYS_P12279)                        369279 rows.

----------

----------

----------

PL/SQL procedure successfully completed.

How to flush one Cursor out of the Shared Pool

 

How to flush one Cursor out of the Shared Pool

flush_cursor.sql

REM Flushes one cursor out of the shared pool. Works on 11g+

REM To create DBMS_SHARED_POOL, run the DBMSPOOL.SQL script.

REM The PRVTPOOL.PLB script is automatically executed after DBMSPOOL.SQL runs.

REM These scripts are not run by as part of standard database creation.

SPO flush_cursor_&&sql_id..txt;

PRO *** before flush ***

SELECT inst_id, loaded_versions, invalidations, address, hash_value

FROM gv$sqlarea WHERE sql_id = '&&sql_id.' ORDER BY 1;

SELECT inst_id, child_number, plan_hash_value, executions, is_shareable

FROM gv$sql WHERE sql_id = '&&sql_id.' ORDER BY 1, 2;

BEGIN

 FOR i IN (SELECT address, hash_value

 FROM gv$sqlarea WHERE sql_id = '&&sql_id.')

 LOOP

 SYS.DBMS_SHARED_POOL.PURGE(i.address||','||i.hash_value, 'C');

 END LOOP;

END;

/

PRO *** after flush ***

SELECT inst_id, loaded_versions, invalidations, address, hash_value

FROM gv$sqlarea WHERE sql_id = '&&sql_id.' ORDER BY 1;

SELECT inst_id, child_number, plan_hash_value, executions, is_shareable

FROM gv$sql WHERE sql_id = '&&sql_id.' ORDER BY 1, 2;

UNDEF sql_id;

SPO OFF;




=========================================================




Script below is an enhanced version, where besides using DBMS_SHARED_POOL.PURGE, we also create a dummy SQL patch, then drop it. This method seems to completely flush parent and child cursors. Why using this method instead?: We are implementing SQL Plan Management (SPM), and we have found that in some cases, some child cursors are still shared several hours after a SQL Plan Baseline (SPB) is created. We could argue a possible bug and pursue as such, but in the meantime my quick and dirty workaround is: whenever I want to flush an individual parent cursor for one SQL, and all of its child cursors, I just execute script below passing SQL_ID.

Anyways, just wanted to share and document this purge_cursor.sql script for those in similar need. I have developed it on 12.1.0.2, and haven’t tested it on lower or higher versions.

-- purge_cursor.sql

DECLARE

 l_name VARCHAR2(64);

 l_sql_text CLOB;

BEGIN

 -- get address, hash_value and sql text

 SELECT address||','||hash_value, sql_fulltext 

 INTO l_name, l_sql_text 

 FROM v$sqlarea 

 WHERE sql_id = '&&sql_id.';

 -- not always does the job

 SYS.DBMS_SHARED_POOL.PURGE (

 name => l_name,

 flag => 'C',

 heaps => 1

 );

 -- create fake sql patch

 SYS.DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH (

 sql_text => l_sql_text,

 hint_text => 'NULL',

 name => 'purge_&&sql_id.',

 description => 'PURGE CURSOR',

 category => 'DEFAULT',

 validate => TRUE

 );

 -- drop fake sql patch

 SYS.DBMS_SQLDIAG.DROP_SQL_PATCH (

 name => 'purge_&&sql_id.', 

 ignore => TRUE

 );

END;

/

Article by kerryosborne regarding Flush of a Single Statement

• flush the shared pool – not a very appealing option in a production environment (although I see almost the same affect frequently at sites that gathering stats every night).
• modify an object that the statement depends on – I usually would add a comment to one of the tables used by the statement. Unfortunately, all statements that use the table will be flushed, so this technique can also be a little hard on a production system, but it’s certainly better than flushing the whole shared pool. 

Small Script Witten flush_sql10.sql 

----------------------------------------------------------------------------------------

--

-- File name:   flush_sql10.sql

--

-- Purpose:     Flush a single SQL statement.

-

-- Author:      Kerry Osborne

--

-- Usage:       This scripts prompts for two values.

--

--              sql_id: the sql_id of a statement that is in the shared pool (v$sqlarea)

--

--              child_number: a valid child_number for the given statement (v$sql) 

--                            - child_number defaults to 0

--

-- Description: This scripts creates an outline on the specified statement and then 

--              attempts to drop the outline. This has the side effect of flushing the 

--              statement from the shared pool. See kerryosborne.oracle-guy.com for 

--              additional information.

--

---------------------------------------------------------------------------------------

-- this is here to attempt to avoid the "ORA-03113: end-of-file on communication channel" error

-- (per metalink) to workaround Bug 5454975 (supposedly fixed 10.2.0.4)

alter session set use_stored_outlines=true;

set serveroutput on for wrap

set pagesize 9999

set linesize 155

var hval number

accept sql_id -

       prompt 'Enter value for sql_id: ' 

accept child_number -

       prompt 'Enter value for child_number: ' - 

       default 0

DECLARE

   name1 varchar2(30);

   sql_string varchar2(300);

BEGIN

   select hash_value into :hval

   from v$sqlarea 

   where sql_id like '&&sql_id';

   DBMS_OUTLN.create_outline(

    hash_value    => :hval, 

    child_number  => &&child_number);

--

-- The next step is a little dangerous,

-- it drops the last outline created (as long as it was create in the last 5 seconds or so)

-- Also note that it appears the category must be default to flush the statement comepletely

--

   select 'drop outline '||name,name into sql_string, name1

   from dba_outlines 

   where timestamp = (select max(timestamp) from dba_outlines)

   and timestamp > sysdate-(5/86400);

   dbms_output.put_line(' ');

   execute immediate sql_string;

   dbms_output.put_line('SQL Statement '||'&&sql_id'||' flushed.');

END;

/

undef sql_id

undef child_number

Ref- https://carlos-sierra.net/2013/02/28/how-to-flush-one-cursor-out-of-the-shared-pool/