Oracle : report roles assigned to users (SQL)

select
  username,
  default_tablespace    dts,
  temporary_tablespace  tts,
  profile prof,
  granted_role || ' ' ||
  decode(admin_option,'YES','- A',' ') ||
  decode(granted_role,'YES','- G',' ') role
from
  dba_users,
  dba_role_privs
where
  dba_users.username = dba_role_privs.grantee and
  username not in ('PUBLIC')
order by
  1,2,3,4;

Linux : 1-liners needed by a DBA

1. Convert variable data from upper/lowercase to lower/uppercase:

   STRING=ExaMple
   echo $STRING | tr '[:lower:]' '[:upper:]'  ==> EXAMPLE
   echo $STRING | tr '[:upper:]' '[:lower:]'  ==> example

   Another example (shell script : test.sh)  :-
   #!/bin/bash
   ############################################################
   #      Example : Convert case of a string                  #
   ############################################################
   HOSTNAME=`hostname|awk -F\. '{print $1}'|tr '[:lower:]' '[:upper:]'`
   echo "Upper case HOSTNAME : ${HOSTNAME}"
   HOSTNAME=`hostname|awk -F\. '{print $1}'|tr '[:upper:]' '[:lower:]'`
   echo "Lower case HOSTNAME : ${HOSTNAME}"

2.  Top 10 memory consuming processes
     ps uax --sort=-rss|head -10

3.  Top 10 CPU intensive processes
     ps uax --sort=-pcpu|head -10

Oracle : one liners needed by a DBA

Below is the reposirory of 1-liners (commands and some formatting) that we keep requiring most of the the time. I'll try to keep growing this repository

1. List datafile belonging to a tablespace
    set linesize 150
    set pagesize 200
    set echo off
    set verify off
    col file_name format a80
    select file_name , bytes/1024/1024 size_in_mb from dba_data_files where tablespace_name like upper('&1');

2. List all the data files.
    select name from v$datafile;

3. Versions:
     select * from v$version;
     select * from v$database;
     select * from product_component_version;
     select version from v$instance;
     select * from sys.gv_$version;
     select * from sys.product_component_version;
     select * from sys.sm_$version;
     select * from sys.gv_$instance;

4.  unzip Opatch to ORACLE_HOME
     unzip p6880880_112000_Linux-x86-64.zip -d $ORACLE_HOME
5.  Opatch Prereq Check
     opatch prereq CheckConflictAgainstOHWithDetail -ph ./

6. Apply patch

    opatch apply

Redhat : How to add swap space

I needed to temporarily increase the swap space by 16GB. Simplest and the quickest method is to add a swap file for the duration and then remove it after the task is over.

First of all identify a large enough partition on the internal disk and then go about the task as below..

1. Locate the partition where the file can be created, say /u06/swap
2. make a directory : /u06/swap
3. create the swap file using the command "dd" : dd if=/dev/zero of=/u06/swap/swapfile bs=1024 count=16384000
4. make the file as swapfile : mkswap /u06/swap/swapfile
5. To make the file available after reboot , add it to the /etc/fstab file: /u06/swap/swapfile  swap  swap  defaults  0 0
6. Activate the swap space : swapon -a /tmp/swapfile
7. check the swap space : swapon -s

I followed redhat documentation , but the link below explains methods precisely the way I created the swap space. The link also shows how to drop the swap space.

http://www.technofunction.com/2010/08/adding-swap-space-in-redhatfedora-linux-manually-using-file-and-partition/


How to move swapfile from one location to another

1. Locate the file where the created swapfile exists

2. Turnoff swap from swapfile

   swapoff /mnt/dallas_prod_old/swapfile_dir/swapfile

3. 

Oracle : tablespace used , ordered by %used_space (SQL)

A simple script to see permanent / temporary tablespace usage in Oracle
=================================================
set linesize 250
set pagesize 200
set echo off
select b.tablespace_name,b.total_space_in_mb,(b.total_space_in_mb - nvl(a.free_space_in_mb,0)) used_space_in_mb,
nvl(a.free_space_in_mb,0) free_space_MB, round(((b.total_space_in_mb - nvl(a.free_space_in_mb,0)) / b.total_space_in_mb) * 100,2) as "%_USED_SPACE"
from
(select sum(bytes)/1024/1024 free_space_in_mb, tablespace_name from dba_free_space group by tablespace_name) a,
(select sum(bytes)/1024/1024 total_space_in_mb,tablespace_name from dba_data_files group by tablespace_name) b
where a.tablespace_name(+) = b.tablespace_name order by "%_USED_SPACE" desc
;
select b.tablespace_name,b.total_space_in_mb,(b.total_space_in_mb - nvl(a.free_space_in_mb,0)) used_space_in_mb,
nvl(a.free_space_in_mb,0) free_space_MB, round(((b.total_space_in_mb - nvl(a.free_space_in_mb,0)) / b.total_space_in_mb) * 100,2)
as "%_USED_SPACE" from
(select sum(bytes_free)/1024/1024 free_space_in_mb, tablespace_name from v$temp_space_header group by tablespace_name) a,
(select sum(bytes)/1024/1024 total_space_in_mb,tablespace_name from dba_temp_files group by tablespace_name) b
where a.tablespace_name(+) = b.tablespace_name order by "%_USED_SPACE" desc ;