Skip to content

Instantly share code, notes, and snippets.

Avatar

Florent Jardin fljdin

View GitHub Profile
@fljdin
fljdin / is_public_holiday.sql
Last active Aug 6, 2020
ecrire-ses-tests-unitaires-en-sql
View is_public_holiday.sql
-- Type month_day
DROP TYPE IF EXISTS month_day CASCADE;
CREATE TYPE month_day AS (month int, day int);
-- Function easter_date(date)
CREATE OR REPLACE FUNCTION easter_date(year int)
RETURNS date LANGUAGE plpgsql
AS $$
DECLARE
g integer := year % 19;
View list_index.sql
SELECT i.indrelid::regclass AS "Table", i.indexrelid::regclass AS "Index",
pg_size_pretty(pg_relation_size(i.indexrelid)) AS "Taille de l'index"
FROM pg_index i JOIN (
SELECT indrelid, indclass, indkey, indexprs, indpred
FROM pg_index GROUP BY indrelid, indclass, indkey, indexprs, indpred
HAVING COUNT (*) > 1
) g ON i.indrelid = g.indrelid AND i.indclass = g.indclass AND i.indkey = g.indkey
AND COALESCE(i.indexprs::text,'x') = COALESCE(g.indexprs::text,'x')
AND COALESCE(i.indpred::text,'x') = COALESCE(g.indpred::text,'x')
ORDER BY pg_relation_size(i.indexrelid) DESC, i.indexrelid::regclass;
View testing.h
#ifndef __testing_h__
#define __testing_h__
#include <stdio.h>
#define ANSI_GREEN "\033[0;32m"
#define ANSI_RED "\033[0;31m"
#define ANSI_RESET "\033[0m"
#define log_passed(M,...) fprintf(stderr,\
View backup.sh
#!/bin/bash
echo -n "Starting backup... Are you sure ? y/(n) " ; read response
test ${response:-n} != "y" && exit 1
dotfiles=$(dirname $0)/dotfiles.txt
destination=$(dirname $0)/home
# create dotfiles archives or append new files to it
tar -czf $HOME/dotfiles.tar.gz -C $HOME --files-from=$dotfiles
View get_table_maintenance_info.sql
SELECT schemaname, relname, pg_size_pretty(pg_relation_size(relname::regclass)) relsize, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE greatest(last_autoanalyze,last_analyze) < now()-interval '5d'
AND pg_relation_size(relname::regclass) > 10e6;
View cache hit ratio.sql
SELECT
sum(heap_blks_read) as heap_read,
sum(heap_blks_hit) as heap_hit,
sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM
pg_statio_user_tables;
View awk meminfo enhance.sh
awk '/Hugepagesize:/{p=$2}/ 0 /{next}/ kB$/{v[sprintf("%9d GB %-s",int($2/1024/1024),$0)]=$0;next}{h[$0]=$2}END{for(k in v) print k;for (k in h) print sprintf("%9d GB %-s",p*h[k]/1024/1024,k)}' /proc/meminfo|sort -nr|grep --color=auto -E "^|.*(Huge.*_[TF]|Mem).*:|"
@fljdin
fljdin / check_cluster.sh
Last active Jun 21, 2019
Perform health check on clusterware and associated databases
View check_cluster.sh
#! /usr/bin/env bash
#
# comment : perform health check on clusterware and associated databases
# deploy : chmod +x /usr/local/bin/check_cluster
export version="20170426.0930"
cif () { printf "[INFO]\t%s\n" "$1" ; }
ttl () { printf "\033[1;34m----\t%s\033[0m\n" "$1" ; }
cok () { printf "\033[1;32m[OK]\033[0m\t%s\n" "$1" ; }
@fljdin
fljdin / readme.md
Last active Feb 12, 2019
Outil de mise en synchro et synchro régulière basé sur le logshipping Oracle Database. La réplication impose que les bases sources et cibles aient le mode ARCHIVELOG actifs et que la FRA soit un système de fichier standard (EXT4, ACFS, etc.)
View readme.md

repctl

Outil de mise en synchro et synchro régulière basé sur le logshipping Oracle Database.

La réplication impose que les bases sources et cibles aient le mode ARCHIVELOG actifs et que la FRA soit un système de fichier standard (EXT4, ACFS, etc.)

Configuration

@fljdin
fljdin / start_trace.sql
Created Sep 28, 2018
Add a new job with sql agent start schedule
View start_trace.sql
-- Create the trace
DECLARE @TraceID int
DECLARE @stoptime_init datetime = NULL
DECLARE @maxfilesize_init bigint = 50
DECLARE @filecount_init int = 20
DECLARE @tracefile_init nvarchar(200) = N'D:\MSSQL\TRACE\Trace_1second'
DECLARE @duration bigint = 1000000 -- microseconds
-- Stop and clear it if running
SELECT @TraceID = traceid FROM ::fn_trace_getinfo(NULL) WHERE property = 2 AND CONVERT(varchar, value) LIKE @tracefile_init + '%';
You can’t perform that action at this time.