Mehdi mehdip2007

🎯

Focusing

Pretend your a loser but in the end you may win.

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 / 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 / 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 / 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 / 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 / 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 / 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"),