View generate_series.sql
-- http://blog.jooq.org/2013/11/19/how-to-create-a-range-from-1-to-10-in-sql/ | |
IF EXISTS (SELECT * | |
FROM dbo.sysobjects | |
WHERE id = object_id (N'[dbo].[generate_series]') | |
AND OBJECTPROPERTY(id, N'IsTableFunction') = 1) | |
DROP FUNCTION [dbo].[generate_series] | |
GO | |
CREATE FUNCTION [dbo].[generate_series] ( @p_start INT, @p_end INT) |
View date_interval.sql
IF EXISTS (SELECT * | |
FROM dbo.sysobjects | |
WHERE id = object_id (N'[dbo].[date_interval]') | |
AND OBJECTPROPERTY(id, N'IsTableFunction') = 1) | |
DROP FUNCTION [dbo].[date_interval] | |
GO | |
CREATE FUNCTION [dbo].[date_interval] ( @p_start datetime, @p_end datetime) | |
RETURNS @DATES TABLE ( generated_date datetime ) | |
AS |
View pg_repack helpers and bloat stuff views
-- the original | |
-- avaliable on: https://wiki.postgresql.org/wiki/Show_database_bloat | |
CREATE VIEW vw_bloat_objects AS | |
SELECT | |
current_database(), schemaname, tablename, /*reltuples::bigint, relpages::bigint, otta,*/ | |
ROUND(CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS tbloat, | |
CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END AS wastedbytes, | |
iname, /*ituples::bigint, ipages::bigint, iotta,*/ | |
ROUND(CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages/iotta::numeric END,1) AS ibloat, | |
CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes |
View jboss-domain.sh
#!/bin/bash | |
# | |
# chkconfig: 345 60 40 | |
# | |
# JBoss AS7+/EAP 6+ Control Script for DomainMode on EL6 platform | |
# | |
# Developped by: | |
# Sebastian Webber - http://swebber.me | |
# Last update: | |
# 2014-10-30 |
View lista-ds.sh
#!/bin/bash | |
## lista_ds.sh | |
## Lists datasources usage on JBoss AS 4,5 and 6 or JBoss EAP 5 | |
### SAMPLE OUTPUT: | |
### INSTANCIA | PORTA | DS | MaxConnectionsInUseCount | AvailableConnectionCount | | |
### ---------------------|----------|----------------------|---------------------------|---------------------------| | |
### jboss-server-1 | 1199 | OracleDS | 8 | 50 | |
View baixa_hq.sh
#!/bin/bash | |
### http://blog.edwards-research.com/2010/01/quick-bash-trick-looping-through-output-lines/ | |
SEPARADOR="|" | |
function formata_nro() { | |
printf "%0${1}d" ${2} | |
} |
View gen_pgauth.sh
#!/bin/bash | |
export AUTH_FILE=/etc/pgbouncer/pgbouncer.auth | |
TMPFILE=$( mktemp ) | |
trap 'rm -f "$TMPFILE"' EXIT | |
psql -Upostgres -p 9876 -qAtX -c 'SELECT $$"$$ || replace( usename, $$"$$, $$""$$) || $$" "$$ || replace( passwd, $$"$$, $$""$$ ) || $$"$$ from pg_shadow where passwd is not null order by 1' > "$TMPFILE" | |
if [[ ! -s "$TMPFILE" ]] | |
then |
View make_pgbouncer_conn.sql
select datname || ' = port=9876 dbname=' || datname from pg_database | |
order by datname; |
View jboss_as7-standalone.sh
#!/bin/bash | |
# | |
# chkconfig: 2345 90 10 | |
# | |
# JBoss Standalone Control Script | |
# | |
# Developped by: | |
# Sebastian Webber - sebastian@swebber.me | |
# Last update: | |
# 2013-08-01 |
View top-10-tables_by-size.sql
WITH table_stats AS ( | |
SELECT | |
schemaname, | |
tablename, | |
pg_relation_size(schemaname || '.'|| tablename) as table_size, | |
(pg_total_relation_size(schemaname || '.'|| tablename) - pg_relation_size(schemaname || '.'|| tablename)) as index_size, | |
pg_total_relation_size(schemaname || '.'|| tablename) as total_size | |
FROM | |
pg_tables | |
) |
OlderNewer