tmuth

----------------------------------------------------------------------------------------
--
-- File name:   plan-capture.sql
-- Purpose:     Run a .sql script and capture v$mystat, 2 versions of dbms_xplan, 
--              and Active SQL Monitor report
-- Author:      Tyler Muth - http://tylermuth.wordpress.com
-- Parameters:  
--          1:  sql script to run containing the query
--          2:  output file name prefix. This is used for the name of the spool file and sqlmon html files
--          3:  An additional script to run first. I use this for "alter session" parameters. I created

tmuth

# bin SQL by elapsed per exec seconds
brks <- as.vector(c(0.1,0.25,0.5,0.75,1,1.5,5,10,60,600,3600,21600)) #establish bins for SQL
DT_SQL_BY_SNAPID[,bin:=findInterval(ELAP_P_EXEC, brks)] #create a new column with those bin index numbers
DT_SQL_BY_SNAPID[,bin2:=as.character(brks[1+findInterval(ELAP_P_EXEC, brks,all.inside=FALSE)])]  #create another new column with the bin values
DT_SQL_BY_SNAPID[is.na(bin2),bin2:=paste0((brks[length(brks)]),"+")] #anything over the top bin will be NA, so change it to "top bin+"

tmuth

----------------------------------------------------------------------------------------
--
-- File name:   top-n-timed-events-by-snapid-awr.sql
-- Purpose:     List the top N events per snapshot from the AWR repository, by snap_id. 
--              The data is aggregated for all nodes in a cluster, though it wouldn't
--              be hard to add instance_number as a dimension.
--
-- Author:      Tyler D. Muth
--
-- Usage:       There are 3 bind variables in this script, all are required:

tmuth

# Use impdp to write DDL of an export out to a file.
# In this case, the source was SQLT > SQL Test Case Builder
impdp system/password@//localhost:1521/noncdb directory=TCB_IMP_DIR dumpfile=sqlt_s83913_tcb_dpexp.dmp TRANSFORM=SEGMENT_ATTRIBUTES:n sqlfile=objects.sql
# Note there are lots of filters you could apply to impdp to target just schemas and/or object types, etc


# Lets get an idea of the schemas referenced in the export:
grep -ioE "\"[[:alnum:]]{1,30}\"\." objects.sql | sort |uniq -c

# Count of objects by schema:

tmuth

#!/bin/bash
# parameter 1: file to split
# parameter 2: number of lines per file

echo "About to split file $1"
echo "every $2 lines"

md5sum $1 > $1.md5

split -l $2 $1 $1.split.

tmuth

#/bin/bash
file_name=""
if [ $# -gt 0 ]
then
    file_name="_$1"
fi

 
while read line; do
    (ssh -n -q root@$line 'find /opt/oracle.oswatcher/osw/archive  \

tmuth

<macrodef name="pandoc-convert">
	<attribute name="input" />
	<attribute name="output" />
	<sequential>
		<exec executable="pandoc" dir=".">
			<arg line="--from=markdown_github " />
			<arg line="--standalone" />
			<arg line="-o @{output}" />
			<arg line="@{input}" />
		</exec>

tmuth

DF_SQL_BY_SNAPID.top_sql.sd.elap <- DF_SQL_BY_SNAPID %>%
  group_by(PARSING_SCHEMA_NAME,SQL_ID,COMMAND_NAME) %>%
  summarise(
            elapsed.avg=mean(ELAP_S),
            elapsed.stddev=sd(ELAP_S)
  ) %>%
  ungroup() %>%
  arrange(desc(elapsed.stddev)) %>%
  head(30)

tmuth

-- row by row
for c1 in (select rowid rid, YEAR1,
                    QUARTER,
                    ...
      from flights_400k
	  )
  loop
-- moderately complex PL/SQL logic 
update flights_400k
   set DEPARTUREDELAYGROUPS = l_group,

tmuth

set define "&" concat "."
set serveroutput on verify off echo off;

column PX_DEGREE_IN noprint new_value PX_DEGREE_NUM
column PX_LOCAL_IN noprint new_value PX_LOCAL

accept PX_DEGREE  prompt 'Parallel Degree for Query, DML and DDL. Append an L to set parallel_force_local=true: '

select
        regexp_replace('&PX_DEGREE','([[:digit:]]+).*','\1') PX_DEGREE_IN,