Skip to content

EvDBT

Evergreen Database Technologies, Inc.

Menu
  • About
  • Articles
  • Books
  • DBAKevlar
  • Papers/Presentations
  • Scripts
  • Videos

Scripts

Oracle SQL*Plus scripts

Icon

ash_xplan.sql

1 file(s) 20.68 KB
Download
Icon

ash_xplan_output.txt

1 file(s) 54.80 KB
Download
Icon

awr_evtrends.sql

1 file(s) 17.47 KB
Download
Icon

awr_parm_changes.sql

1 file(s) 2.56 KB
Download
Icon

awr_stattrends.sql

1 file(s) 5.44 KB
Download
Icon

betwixt.sql

1 file(s) 2.20 KB
Download
Icon

carl.sql

1 file(s) 7.68 KB
Download
Icon

cbo_stats.sql

1 file(s) 7.46 KB
Download
Icon

chk_obj_recompile.sql

1 file(s) 5.12 KB
Download
Icon

dba_column_usage.sql

1 file(s) 3.14 KB
Download
Icon

estimate_mttr.sql

1 file(s) 14.20 KB
Download
Icon

exchpart.sql

1 file(s) 28.69 KB
Download
Icon

gen_analyze_dd.sql

1 file(s) 1.86 KB
Download
Icon

gen_rebuild_idx.sql

1 file(s) 1.38 KB
Download
Icon

gen_recompile.sql

1 file(s) 1.48 KB
Download
Icon

infinite_dual.sql

1 file(s) 1.71 KB
Download
Icon

nondefparm.sql

1 file(s) 1.58 KB
Download
Icon

parm.sql

1 file(s) 1.50 KB
Download
Icon

rman_chk_catalog.sql

1 file(s) 12.96 KB
Download
Icon

rman_chk_nocatalog.sql

1 file(s) 9.06 KB
Download
Icon

rman_chk_test.sql

1 file(s) 0.73 KB
Download
Icon

run_top_stmt5.sql

1 file(s) 1.39 KB
Download
Icon

sqlhistory.sql

1 file(s) 8.87 KB
Download
Icon

stdby_stats.sql

1 file(s) 10.56 KB
Download
Icon

top_stmt5_10g.sql

1 file(s) 15.08 KB
Download
Icon

tracetrg.sql

1 file(s) 1.57 KB
Download
Icon

unparm.sql

1 file(s) 1.58 KB
Download
Icon

wasted_spc.sql

1 file(s) 14.93 KB
Download
Icon

who_is_using.sql

1 file(s) 3.00 KB
Download

UNIX/Linux Shell scripts

Icon

fileprobe.sh

1 file(s) 22.68 KB
Download
Icon

oramem.sh

1 file(s) 9.27 KB
Download
Icon

oraprobe.sh

1 file(s) 11.20 KB
Download
Icon

rman_chk.sh

1 file(s) 9.92 KB
Download
Icon

run_awr.sh

1 file(s) 11.87 KB
Download
Icon

stdby_applylogs.sh

1 file(s) 8.55 KB
Download
Icon

stdby_chk.sh

1 file(s) 9.47 KB
Download
Icon

stdby_init.sh

1 file(s) 14.99 KB
Download
Icon

stdby_ship.sh

1 file(s) 12.35 KB
Download
Icon

tnsprobe.sh

1 file(s) 6.73 KB
Download

DUMP_DATA package for dump to CSV

Icon

dump_data.sql

1 file(s) 12.12 KB
Download
Icon

csv.pl

1 file(s) 1.83 KB
Download
Icon

csv.sql

1 file(s) 0.88 KB
Download
Icon

csv.sh

1 file(s) 0.36 KB
Download

19 thoughts on “Scripts”

  1. Ravi Narayanaswamy on December 5, 2012 | 3:51 pm

    Hey Tim –

    How are you doing? Long time. Hope all is well with you and family!

    Glad to see your new site… miss the scripts I “borrow” from the site and looking forward to seeing them again! I am looking for the top 10 queries script you used to have for 11gr2. O’well dealing with a company with 2 node RAC with constant Cluster Reconfiguration… and yikes things take longer all the time so looking to identify and start tuning the top queries. Looking forward to your RMOUG 2013 presentations.

    Regards,
    Ravi

    Reply
    • Tim Gorman on March 3, 2013 | 3:49 pm

      Ravi, Good to hear from you! Sorry my papers and scripts are off-line at the moment — I’m just doing a million things at once right now, including learning more about WordPress. I’ll try have the papers and scripts posted in a media-gallery widget over the next 2 weeks.
      More soon…

      Reply
  2. Vie303 on January 16, 2013 | 11:05 am

    About time you upgraded 🙂

    Reply
    • Tim Gorman on January 19, 2013 | 6:20 pm

      I agree. Thanks Vie!

      Reply
  3. NICOLAS on January 23, 2013 | 8:19 am

    TIM, EXCELLENT POST ….. YOUR SCRIPTS ARE VERY USEFULL TO LEARN AND TO RESEARCH IN MY JOB.
    I’M FROM ARGENTINA
    BEST REGARDS AND THANKS A LOT!

    NICOLAS

    Reply
  4. Sahir on January 29, 2013 | 3:40 am

    Hi,
    Very informative and helpful scripts you have posted here.
    I am looking for trigger referred in a post (http://www.evdbt.com/trclvl12.ddl) which is helpful to find the column name in case of ora-01438, but not able to find it. Can you please share it.
    looking forward to hear from you soon.

    Regards,
    Sahir Nadeem

    Reply
    • Tim Gorman on January 29, 2013 | 11:20 am

      Sorry for breaking the link; I switched over from my old HTML-based website to the present one powered by WordPress back in November, and I’m slowly getting the new website re-constituted with all the stuff that was on the old. You should find the TRCLVL12 script available for download on this page now.

      Thanks!!!

      Reply
  5. newbie01.oracle on February 13, 2013 | 4:49 am

    Perhaps it is too much to ask, but would be nice to have the scripts in one zip file 🙂

    Reply
  6. Pingback: Performance Visualization made easy – PerfSheet 2.0 beta | Tanel Poder's blog: IT & Mobile for Geeks and Pros
  7. Sonia on May 9, 2013 | 8:37 am

    Hi Ravi,

    Good collection of scripts !!! I have not gone through all but few of them. Wants to know do you have any script or data for statistics ? or else if you can guide me about what is the optimal way to look for statistics then good for me.

    Thanks, Sonia

    Reply
    • Tim Gorman on May 10, 2013 | 6:04 am

      Sonia,
      There is my bare-bones “cbo_stats.sql” script posted here, but if you want the complete picture on statistics used by and relevant for a specific SQL statement, look toward SQLT by Carlos Sierra of Oracle and available for download from http://support.oracle.com.
      Hope this helps…
      -Tim

      Reply
  8. Sonia on May 10, 2013 | 8:19 am

    Hi Tim,

    Thanks for your reply. From your reply I try to find on metalink but did not find “cbo_Stat.sql” in their search tool. can you guide me with link?

    Thanks again.
    Sonia

    Reply
    • Tim Gorman on May 10, 2013 | 8:49 am

      Sonia,

      “cbo_stats.sql” is my script, available on this page. SQLT (or SQLTXPLAIN) is a package available from Oracle Support on their website.

      Sorry for the confusion…

      -Tim

      Reply
  9. Sonia on May 10, 2013 | 11:04 am

    Hi Tim,

    no issues. Thanks again. will try to understand and implement your script in my test database.

    Reply
  10. Pingback: View V$SQL_OPTIMIZER_ENV | EvDBT
  11. Pingback: November/December Highlights | Ardent Performance Computing
  12. Jari Hämäläinen on January 9, 2014 | 8:45 am

    Hi!

    Could you please add the sphistory.sql script? In the comments of sqlhistory.sql the script is mentioned.
    The AWR script has been really useful for me!

    -Jari

    Reply
    • Tim Gorman on January 12, 2014 | 8:56 pm

      Jari,

      Posted. Thanks!

      -Tim

      Reply
  13. Haris on August 26, 2018 | 2:58 am

    Hi Tim
    Thank you for sharing the useful scripts. I donwloaded ash_xplan.sql and successfully installed. However, I can’t get it working as expected. Below is the errors reported

    linux7-0vbox:SYS@GENP> select * from table(ash_xplan.display_cursor(‘3c1kubcdjnppq’,0,’TYPICAL’));
    ERROR:
    ORA-06502: PL/SQL: numeric or value error
    ORA-06512: at “SYS.ASH_XPLAN”, line 17
    ORA-06512: at “SYS.ASH_XPLAN”, line 375
    ORA-06512: at line 1

    no rows selected
    —–
    The sql_id existed.

    PLAN_TABLE_OUTPUT
    ————————————————————————————————————————————————————————————————————————————————————————————————————
    SQL_ID 3c1kubcdjnppq, child number 0
    ————————————-
    update sys.col_usage$ set equality_preds = equality_preds +
    decode(bitand(:flag,1),0,0,1), equijoin_preds = equijoin_preds
    + decode(bitand(:flag,2),0,0,1), nonequijoin_preds =
    nonequijoin_preds + decode(bitand(:flag,4),0,0,1), range_preds
    = range_preds + decode(bitand(:flag,8),0,0,1), like_preds
    = like_preds + decode(bitand(:flag,16),0,0,1), null_preds
    = null_preds + decode(bitand(:flag,32),0,0,1), timestamp =
    :time where obj# = :objn and intcol# = :coln

    Plan hash value: 477378112

    ———————————————————————————–
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ———————————————————————————–
    | 0 | UPDATE STATEMENT | | | | 1 (100)| |
    | 1 | UPDATE | COL_USAGE$ | | | | |
    |* 2 | INDEX UNIQUE SCAN| I_COL_USAGE$ | 1 | 30 | 1 (0)| 00:00:01 |
    ———————————————————————————–

    Predicate Information (identified by operation id):
    —————————————————

    2 – access(“OBJ#”=:OBJN AND “INTCOL#”=:COLN)

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.

About me

Recent Posts

  • Azure host-caching with Oracle database workloads on Azure VMs July 2, 2021
  • Oracle dNFS on NFS v4.1 June 17, 2021
  • Delphix XPP explained November 14, 2019
  • Oak Table World 2018 October 25, 2018
  • Achieving a balance December 1, 2017
  • Automating Stored Procedures When Refreshing Virtual SQL Server Databases October 31, 2017

Older posts…

Affiliations

EvDBT 2025 . Powered by WordPress