Skip to content

Instantly share code, notes, and snippets.

View mehdip2007's full-sized avatar
🎯
Focusing

Mehdi mehdip2007

🎯
Focusing
View GitHub Profile
@mehdip2007
mehdip2007 / session_id_info.sql
Created April 5, 2020 09:53
I will share useful query which every database administrator should know specially those who work with oracle.
/* Script to give statistics of user please specify SID */
REM Usage @sid sidno
REM Gives SQL,last execution time and locks being held
REM This script is for DB where timed_statistics = false
REM If timed_statistics = true alter script to use
REM process last non-idle time value from v$sesstat
REM see example in idle.sql
set serveroutput on veri off pages 24 lines 132
declare
@mehdip2007
mehdip2007 / Table_Size.sql
Last active April 5, 2020 10:18
Query table size including partitions, indexes
SELECT owner,
table_name,
TRUNC(sum(bytes)/1024/1024) Meg
FROM
(SELECT segment_name table_name,
owner,
bytes
FROM dba_segments
WHERE segment_type = 'TABLE'
UNION
@mehdip2007
mehdip2007 / Find_Locked_tables.sql
Created April 5, 2020 10:22
find tables which locked generally or specific table
SELECT oracle_username || ' (' || s.osuser || ')' username
, s.sid || ',' || s.serial# sess_id
, owner || '.' || object_name object
, object_type
, CURSOR((SELECT DECODE (l.block, 0, 'Not Blocking', 1, 'Blocking', 2, 'Global') FROM v$lock l WHERE l.id1 = v.object_id)) status
, DECODE (v.locked_mode, 0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
@mehdip2007
mehdip2007 / Tablespace_Growth.sql
Created April 5, 2020 10:25
You can use the following snippet to check how much your tablespace is growing
SELECT to_char(creation_time,
'MM-RRRR') "Month", sum(bytes)/1024/1024/1024 "Growth IN GB"
FROM sys.v_$datafile
WHERE to_char(creation_time,'RRRR')='2014'
GROUP BY to_char(creation_time, 'MM-RRRR')
ORDER BY to_char(creation_time, 'MM-RRRR'); -----SELECT b.tsname tablespace_name,
MAX (b.used_size_mb) cur_used_size_mb,
ROUND (AVG (inc_used_size_mb),
2) avg_increas_mb
FROM
@mehdip2007
mehdip2007 / Drop-Create-Enable-Schedule-Job.sql
Created April 5, 2020 10:31
The snippet is related to creating, dropping, enabling a job in oracle database
// Drop Part
BEGIN
SYS.DBMS_SCHEDULER.DROP_JOB
(job_name => 'ABL_DBOBJECTS.RECON_REVOKING_DUNNING');
END;
/
// Creation Part
BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
@mehdip2007
mehdip2007 / Fragment-Index.sql
Created April 5, 2020 10:38
When you table get a changes a lot your table index might get fragment, so u can use the below query to find the fragmented index.
select
'exec analyzedb.reorg_a_table4('||' '||rtrim(t.table_owner)|| ' ' ||','|| ' ' ||
rtrim(t.table_name)||' '||');',
t.table_owner||'.'||t.table_name name,
a.num_rows,
sum(t.inserts) ins,
sum(t.updates) upd,
sum(t.deletes) del,
sum(t.updates)+sum(t.inserts)+sum(t.deletes) tot_chgs,
to_char((sum(t.deletes)/(decode(a.num_rows,0,1,a.num_rows)))*100.0,'999999.99') per_del,
@mehdip2007
mehdip2007 / migrate.py
Created January 21, 2023 17:36
Migration from Oracle to Hive issue
from pyspark import SparkContext, SparkConf
from pyspark.sql import SparkSession, Row, HiveContext
from pyspark.sql.functions import *
from pyspark.sql.types import *
import multiprocessing as mp
conf = SparkConf().setAll([('spark.executor.cores', '4'),
('spark.cores.max', '8'),
("spark.yarn.am.memory", "8g"),