Skip to content

Instantly share code, notes, and snippets.

View fljdin's full-sized avatar

Florent Jardin fljdin

13 followers · 11 following
View GitHub Profile
@fljdin
fljdin / cache hit ratio.sql
Created May 22, 2019 15:57
A health check playbook for your Postgres database https://www.citusdata.com/blog/2019/03/29/health-checks-for-your-postgres-database https://github.com/heroku/heroku-pg-extras/tree/master/commands
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;
@fljdin
fljdin / awk meminfo enhance.sh
Created January 11, 2019 16:20
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 June 21, 2019 16:07
Perform health check on clusterware and associated databases
#! /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 April 12, 2023 07:44
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.)

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 September 28, 2018 15:59
Add a new job with sql agent start schedule
-- 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 + '%';
@fljdin
fljdin / Character set name
Created May 29, 2018 09:59
SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;
@fljdin
fljdin / Object privileges.sql
Last active September 21, 2017 09:07
Listing privileges
select
case when level = 1 then own || '.' || obj || ' (' || typ || ')' else
lpad (' ', 2*(level-1)) || obj || nvl2 (typ, ' (' || typ || ')', null)
end
from
(
/* THE OBJECTS */
select
null p1,
null p2,
@fljdin
fljdin / get_table_definitions.sql
Last active September 21, 2017 09:11
set long 1000
set pagesize 0
select DBMS_METADATA.GET_DDL('OPS$DBO.INDEX','') from DUAL;
@fljdin
fljdin / get_index_columns.sql
Last active September 21, 2017 09:11
set line 200
col columns for a100
select i.table_name, i.index_name, listagg(i.column_name, ',') within group (order by i.column_position) columns
from dba_ind_columns i
left join dba_constraints c on i.index_name = c.index_name
where i.index_owner = '&owner' and i.table_name = '&table'
group by i.table_name, i.index_name;
@fljdin
fljdin / schedules for statspack.sql
Last active September 21, 2017 09:12
SET lines 250
COL enabled FOR a8
COL last_start_date FOR a18
COL next_run_date FOR a18
SELECT owner, job_name, enabled,
to_char(last_start_date, 'YYYY-MM-DD HH24:MI:SS') last_date,
to_char(next_run_date, 'YYYY-MM-DD HH24:MI:SS') next_date
FROM dba_scheduler_jobs
WHERE owner = 'PERFSTAT' AND job_name LIKE 'SP_%';