View generate_series.sql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
select datname || ' = port=9876 dbname=' || datname from pg_database | |
order by datname; |
View jboss_as7-standalone.sh
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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