Oracle: check real table size on disk

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 ALL
 SELECT i.table_name, i.owner, s.bytes
 FROM dba_indexes i, dba_segments s
 WHERE s.segment_name = i.index_name
 AND   s.owner = i.owner
 AND   s.segment_type = 'INDEX'
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.segment_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBSEGMENT'
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.index_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBINDEX')
WHERE owner in ('SCHEMA_NAME')
GROUP BY table_name, owner
HAVING SUM(bytes)/1024/1024 > 10  /* Ignore really small tables */
ORDER BY SUM(bytes) desc ;

How to check UNDO tablespace total and occupied size

select tablespace_name,sum(bytes) total_bytes from dba_data_files where tablespace_name like '%UNDO%' group by tablespace_name;

select tablespace_name,sum(bytes) occupied_bytes from dba_segments where tablespace_name like '%UNDO%' group by tablespace_name;

How to ssh to host and set terminal title to hostname:path_on_the_host in a single command

 1. Create file ~/ssht.sh with content:

#!/bin/bash
SETTP='MY_PROMPT="$HOSTNAME:$PWD\$ "'
SETTP="$SETTP;"'MY_TITLE="\[\e]0;$HOSTNAME:$PWD\a\]"'
SETTP="$SETTP;"'PS1="$MY_TITLE$MY_PROMPT"'
ssh -t <YOUR_NICKNAME>@$1 "export PROMPT_COMMAND='eval '\\''$SETTP'\\'; bash --login"

2. Make it executable:

chmod ugo+x ~/ssht.sh

3. add alias to this script into your .profile:

alias ssht="~/ssht.sh"

4. Use:

ssht <HOST_NAME>

Oracle: how to remove duplicate constraints of 'CHECK' type

create or replace function get_search_condition(ownerr in varchar2, p_cons_name in varchar2) return varchar2 authid current_user
 is
   l_search_condition user_constraints.search_condition%type;
 begin
  select search_condition into l_search_condition from all_constraints where constraint_name = p_cons_name and owner = ownerr;
  return l_search_condition;
end;
/

--select 'ALTER TABLE ' || owner ||'.'|| table_name || ' DROP CONSTRAINT ' ||  regexp_replace(constraints, '^\w+; ', '') || ';' from (
  select owner, table_name, search_condition
  , listagg(constraint_name, '; ') within group (order by constraint_name) constraints
  from (
    select owner, table_name, regexp_replace(lower(get_search_condition(owner, constraint_name)), '\s+', ' ') search_condition, constraint_name
      from all_constraints where owner in (
      select owner from dba_tables where owner not in ('SYS', 'SYSTEM', 'APEX_040000', 'MDSYS', 'CTXSYS', 'FLOWS_FILES', 'OUTLN', 'XDB')
      ) and constraint_type = 'C' and search_condition IS NOT NULL
      order by table_name, constraint_name
  ) group by owner, table_name, search_condition having (count(constraint_name) > 1)
--)
;
drop function get_search_condition;

Remove obsolete GIT branches

I don't know an option to do this in a single command so I am doing it like below:

git checkout master
comm -23 <( git branch | grep -v "/" | grep -v "*" | sort ) <( git branch -r | awk -F '/' '{print $2}' | sort ) | awk '{print "git branch -D " $1}'
# prints 'git branch -D' commands for every obsolete branch. The next step is to apply them manually.

You could modify command to remove obsolete branches automatically (on your own risk):

comm -23 <( git branch | grep -v "/" | grep -v "*" | sort ) <( git branch -r | awk -F '/' '{print $2}' | sort ) | xargs git branch -D 

Another option to remove obsolete branches is:

git for-each-ref --format='%(refname:short) %(upstream)' refs/heads/ | awk '$2 !~/^refs\/(remotes|heads)/' | xargs git branch -D

Linux: how to kill all process instances by process name

ps -elf | grep <process_name> | grep -v grep | awk '{print $4}' | xargs kill -9

Example:

ps -elf | grep maven | grep -v grep | awk '{print $4}' | xargs kill -9 

Oracle: working with DBMS STATS

Useful queries:

Check when statistics was gathered:
SELECT owner, table_name, last_analyzed FROM all_tables where owner='<SCHEMA_NAME>' ORDER BY last_analyzed DESC NULLS LAST; --Tables.
SELECT owner, index_name, last_analyzed FROM all_indexes where owner='<SCHEMA_NAME>' ORDER BY last_analyzed DESC NULLS LAST; -- Indexes.