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 / password_limits.sql
Last active September 21, 2017 09:12
Enhanced password policy
select * from user_password_limits;
@fljdin
fljdin / get_comp.sql
Last active September 21, 2017 09:12
ASM Compatibility https://www.pythian.com/blog/oracle-11g-asm-diskgroup-compatibility/
col COMPATIBILITY form a10
col DATABASE_COMPATIBILITY form a10
col NAME form a20
select group_number, name, compatibility, database_compatibility from v$asm_diskgroup;
@fljdin
fljdin / awr audit over past week.sql
Last active June 11, 2019 13:11
Get dbtime (seconds) from statspack or awr
-- Average DB Time over the past week
select instance_number, avg(dbtime_min) avg_dbtime_min
from (
select a.instance_number, begin_interval_time, end_interval_time,
round((a.value-lag(a.value) over (order by a.instance_number, a.snap_id ))/1000000/60,2) dbtime_min
from dba_hist_sys_time_model a, dba_hist_snapshot b
where a.snap_id = b.snap_id and a.instance_number = b.instance_number
and a.stat_name = 'DB time'
)
@fljdin
fljdin / 10g_cluster_snap.sql
Last active February 8, 2019 14:17
Install statspack tools and shedules
-- exécuter un snapshot sur les DEUX INSTANCES
EXEC perfstat.statspack.snap;
variable jobno NUMBER;
BEGIN
FOR inst IN (SELECT dbid, inst_id AS id FROM gv$database) LOOP
STATSPACK.MODIFY_STATSPACK_PARAMETER(
i_snap_level => 7,
i_dbid => inst.dbid,
i_instance_number => inst.id
@fljdin
fljdin / do_things.sql
Last active September 21, 2017 09:12
Increase redolog groups
alter database add logfile ('+DATA', '+DATA') size 100m;
...
alter database drop logfile group 1;
...
alter system switch logfile;
alter system checkpoint;
@fljdin
fljdin / convert_to_mp3.ps1
Last active September 21, 2017 09:13
Bulk convert m4a and aac to mp3
foreach($inputFile in get-childitem -recurse | Where-Object { @('.m4a','.aac') -contains $_.extension })
{
$outputFileName = [System.IO.Path]::GetFileNameWithoutExtension($inputFile.FullName) + ".mp3";
$outputFileName = [System.IO.Path]::Combine($inputFile.DirectoryName, $outputFileName);
$programFiles = ${env:ProgramFiles(x86)};
if($programFiles -eq $null) { $programFiles = $env:ProgramFiles; }
$processName = $programFiles + "\VideoLAN\VLC\vlc.exe"
@fljdin
fljdin / blocking session.sql
Last active November 21, 2019 11:15
Detect blocked and blocking sessions in PostgreSQL 9.6+
SELECT pid, state,
CASE WHEN wait_event is not null
THEN concat(wait_event, ' (', wait_event_type, ')')
ELSE NULL::text END AS lock_intel,
pg_blocking_pids(pid) AS blocked_by,
substr(query, 1, 20)
FROM pg_stat_activity
WHERE state IS NOT NULL;
@fljdin
fljdin / uptime date.sh
Last active September 21, 2017 09:13
Display date of last reboot on unix
date -d "$(cut -f1 -d' ' /proc/uptime) seconds ago"
@fljdin
fljdin / 01 create_unusable_index.sql
Last active August 29, 2015 14:07
Workaround ORA-01652 when creating function-based index on partitioned table
CREATE INDEX &schema.&index_part
ON &schema.&table_name(TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(&column),'YYYYMMDD'),'IYYYIW')))
LOCAL UNUSABLE;
@fljdin
fljdin / postgresql evaluate next working day.sql
Last active August 29, 2015 14:03
Evaluate next french working day from a date and a frequency string
-- -----------------------------------
-- FUNCTION: easter_date
-- -----------------------------------
CREATE OR REPLACE FUNCTION easter_date(year integer)
RETURNS timestamp as $$
DECLARE
g integer := year % 19;
c integer := (year/100)::int;
h integer := ((c - (c/4)::int - ((8*c+13)/25)::int + 19*g + 15) % 30)::int;
i integer := h - (h/28)::int * (1 - (h/28)::int * (29/(h + 1))::int * ((21 - g)/11)::int);