Tracnac’s gists

Tracnac / Procédure Kill session sous SYS.md

Created September 23, 2022 17:33

Procédure Kill session sous SYS #oracle #sql

create or replace procedure kill_all_sessions
( schema_in in varchar2 , status out number )
as
lv_status varchar2(30);
pn_sid number;
pn_serial number;
cursor c1 is select sid,serial# from v$session where username = upper(schema_in);
godlike exception;
PRAGMA EXCEPTION_INIT(godlike, -20000);

Tracnac / Procédure Kill session.md

Last active September 23, 2022 17:33

Procédure Kill session #oracle #sql

create or replace procedure sys.kill_session( p_sid in number, p_serial# in number )
as
begin
for x in ( select 1
from v$session
where username = 'USERNAME'
and sid = p_sid
and serial# = p_serial# )
loop

Tracnac / Quick Trace.md

Last active September 23, 2022 17:29

Quick Trace #oracle

alter session set tracefile_identifier='MY_TRACE';
alter session set events '10053 trace name context forever';

select sysdate from dual;


alter session set events '10053 trace name context off';

Tracnac / Quick trigger after logon.md

Last active September 23, 2022 17:21

Quick trigger after logon #oracle #sql

create or replace trigger enable_sql_trace after logon on database
declare
vsql varchar2(255);
begin
if (dbms_session.is_role_enabled('SQL_NO_EXPAND'))
then
vsql := q'"alter session set "_no_or_expansion"=TRUE"';
execute immediate vsql;
end if;

Tracnac / Récupérer l'addresse IP de sa box.md

Created September 23, 2022 17:19

Récupérer l'addresse IP de sa box #shell

dig +short myip.opendns.com @resolver1.opendns.com

Tracnac / Rename DG ASM.md

Last active September 23, 2022 17:18

Rename DG ASM #oracle #asm

Dismount DG

renamedg -phase BOTH -dgname=DATA_0033 -newdgname=DATA

Mount DG

Tracnac / Reset Dead Cow Optimizer.md

Last active September 23, 2022 17:17

Reset Dead Cow Optimizer #oracle #performance

alter system reset "_optimizer_mjc_enabled" scope=spfile sid='*';
alter system reset "_b_tree_bitmap_plans" scope=spfile sid='*';
alter system reset "_gby_hash_aggregation_enabled" scope=spfile sid='*';
alter system reset "_optimizer_cost_based_transformation" scope=spfile sid='*';
alter system reset "_optimizer_cost_model" scope=spfile sid='*';
alter system reset "_subquery_pruning_enabled" scope=spfile sid='*';
alter system reset "_push_join_predicate" scope=spfile sid='*';
alter system reset "_unnest_subquery" scope=spfile sid='*';
alter system reset "_simple_view_merging" scope=spfile sid='*';

Tracnac / RMAN Define Sequence SCN and Thread.md

Created September 23, 2022 17:15

RMAN Define Sequence, SCN and Thread #oracle #rman

-— V$ARCHIVED_LOG
--
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-RR HH24:MI:SS';


SELECT THREAD#, SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG
WHERE '04-JUN-11 13:13:05' BETWEEN FIRST_TIME AND NEXT_TIME;

Tracnac / RMAN Duplicate from Active database.md

Last active September 23, 2022 17:14

RMAN Duplicate from Active database #oracle #rman

Prérequis :

tnsping source and dest : OK sys account unlock and password set orapwd file for both spfile for both reset controlfile from dest startup nomount dest

Tracnac / Shrink Log SQLServer.md

Last active September 23, 2022 17:13

Shrink Log SQLServer #mssql

-— Shrink Log Sqlserver ---
SELECT
'USE [' + d.name + N']' + CHAR(13) + CHAR(10)
+ 'DBCC SHRINKFILE (N''' + mf.name + N''' , 0, TRUNCATEONLY)'
+ CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
FROM
sys.master_files mf
JOIN sys.databases d
ON mf.database_id = d.database_id