Oracle reserved words

col keyword for a30
select *
from v$reserved_words
where keyword like upper('%&word%')
order by keyword;

create csv file glogin

SET UNDERLINE OFF
SET COLSEP ','
SET LINES 100 PAGES 100
SET FEEDBACK off
--(If you don’t want column headings in CSV file)
SET HEADING off
Spool C:\Export\EMP.csv
--Now the actual query
SELECT * FROM EMP;
Spool OFF

Oracle Outstanding Alert

set linesize 145
set pagesize 1000
set trimout on
set trimspool on
Set Feedback off
set timing off
set verify off


prompt

prompt -- ----------------------------------------------------------------------- ---

prompt -- Outstanding Alert ---

prompt -- ----------------------------------------------------------------------- ---

prompt

set linesize 200
column ct format a18 heading "Creation Time"
column instance_name format a8 heading "Instance|Name"
column object_type format a14 heading "Object|Type"
column message_type format a9 heading "Message|Type"
column message_level format 9999 heading "Mess.|Lev."
column reason format a30 heading "Reason"
column suggested_action format a75 heading "Suggested|Action"

Select
To_Char(Creation_Time, 'DD-MM-YYYY HH24:MI') ct
, instance_name
, object_type
, message_type
, message_level
, reason
, suggested_action
From dba_outstanding_alerts
Order By Creation_Time
;


Prompt

Oracle table full information

prompt --------CUT-----------------------------CUT-----------------------CUT-----------------------------CUT---------------------

set verify off  AUTOTRACE OFF  TIMING OFF  linesize 200
COLUMN COMMENTS 	FORMAT A50
COLUMN column_name 	FORMAT A35
COLUMN Data_Type 	FORMAT A15
COLUMN DATA_DEFAULT 	FORMAT A20
COLUMN "PK Column" 	FORMAT A35
COLUMN "FK Column" 	FORMAT A20

UNDEF Owner
ACCEPT Owner PROMPT 'Enter Owner :'

UNDEF Table_Name
ACCEPT Table_Name PROMPT 'Enter Table Name :'

--define Table_Name='xxxx'
--define Owner='XXXX'

SET HEADING OFF

PROMPT
PROMPT Comments for Table &Table_Name.
SELECT COMMENTS FROM ALL_TAB_COMMENTS
WHERE TABLE_NAME = UPPER('&Table_Name')
AND Owner = UPPER('&Owner') ;

SET HEADING ON

PROMPT
PROMPT Column Details for Table &Table_Name.

SELECT
ROWNUM "Sr No", T.COLUMN_NAME , T.Data_Type , T.DATA_LENGTH,DECODE(T.Nullable, 'N' , 'NOT NULL' , 'Y', ' ') NULLABLE , T.Data_Default , C.Comments FROM ALL_TAB_COLS T , All_Col_Comments C
WHERE 1=1
and T.OWNER = C.OWNER
AND T.TABLE_NAME = C.TABLE_NAME
AND T.COLUMN_NAME = C.COLUMN_NAME
AND T.TABLE_NAME = UPPER('&Table_Name')
AND T.Owner = UPPER('&Owner') ;


PROMPT
PROMPT PRIMARY KEY for Table &Table_Name.

select COLUMN_NAME FROM ALL_CONS_COLUMNS
WHERE TABLE_NAME = UPPER('&Table_Name.')
AND Owner = UPPER('&Owner.')
AND CONSTRAINT_NAME = ( SELECT CONSTRAINT_NAME
FROM ALL_CONSTRAINTS
WHERE TABLE_NAME = UPPER('&Table_Name')
AND CONSTRAINT_TYPE = 'P'
AND Owner = UPPER('&Owner')
)
ORDER BY POSITION
/

PROMPT
PROMPT INDEXES for Table &Table_Name.

BREAK ON INDEX_NAME ON UNIQUENESS SKIP 1

SELECT I.INDEX_NAME , C.COLUMN_NAME , I.UNIQUENESS FROM ALL_IND_COLUMNS C , ALL_INDEXES I WHERE C.INDEX_NAME = I.INDEX_NAME
AND C.TABLE_NAME = I.TABLE_NAME
AND I.TABLE_NAME = UPPER('&Table_Name')
AND I.Owner = UPPER('&Owner')
AND C.Table_Owner = UPPER('&Owner')
AND NOT EXISTS ( SELECT 'X' FROM ALL_CONSTRAINTS WHERE CONSTRAINT_NAME = I.INDEX_NAME
AND Owner = UPPER('&Owner.')
)
ORDER BY INDEX_NAME , COLUMN_POSITION
/

CLEAR BREAKS

PROMPT
PROMPT FOREIGN KEYS for Table &Table_Name.

BREAK ON CONSTRAINT_NAME ON TABLE_NAME ON R_CONSTRAINT_NAME SKIP 1
COLUMN POSITION NOPRINT

SELECT UNIQUE A.CONSTRAINT_NAME,C.COLUMN_NAME "FK Column" ,B.TABLE_NAME || '.' || B.COLUMN_NAME "PK Column",A.R_CONSTRAINT_NAME , C.POSITION
FROM ALL_CONSTRAINTS A, ALL_CONS_COLUMNS B, ALL_CONS_COLUMNS C
WHERE A.R_CONSTRAINT_NAME=B.CONSTRAINT_NAME
AND B.OWNER=UPPER('&OWNER')
AND A.CONSTRAINT_NAME=C.CONSTRAINT_NAME
AND A.OWNER=C.OWNER
AND A.OWNER = B.OWNER
AND A.TABLE_NAME=C.TABLE_NAME
AND B.POSITION=C.POSITION
AND A.TABLE_NAME LIKE UPPER('&TABLE_NAME')
ORDER BY A.CONSTRAINT_NAME, C.POSITION
/


set echo off
Set pages 999
set long 90000

SELECT DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT',UPPER('&Table_Name'),UPPER('&Owner')) from dual
/

select dbms_metadata.get_ddl('TABLE',UPPER('&Table_Name'),UPPER('&Owner') ) from dual
/


COLUMN POSITION NOPRINT
CLEAR BREAKS

PROMPT
PROMPT CONSTRAINTS for Table &Table_Name.

SELECT CONSTRAINT_NAME , SEARCH_CONDITION FROM ALL_CONSTRAINTS
WHERE TABLE_NAME = UPPER('&Table_Name.')
AND Owner = UPPER('&Owner')
AND CONSTRAINT_TYPE NOT IN ( 'P' , 'R');
/




select NUM_ROWS from all_tables
where TABLE_NAME = UPPER('&Table_Name')
and Owner = UPPER('&Owner')
/

SET FEEDBACK ON

PROMPT
PROMPT Tables That REFER to Table &Table_Name.

BREAK ON TABLE_NAME ON CONSTRAINT_NAME skip 1

SELECT C.TABLE_NAME , C.CONSTRAINT_Name , CC.COLUMN_NAME "FK Column" FROM ALL_CONSTRAINTS C , All_Cons_colUMNs CC
WHERE C.Constraint_Name = CC.Constraint_Name
AND R_CONSTRAINT_NAME = ( SELECT CONSTRAINT_NAME FROM ALL_CONSTRAINTS
WHERE TABLE_NAME = UPPER('&Table_Name')
AND CONSTRAINT_TYPE = 'P'
AND Owner = UPPER('&Owner')
)
AND C.Owner = UPPER('&Owner')
/

CLEAR BREAKS


PROMPT
PROMPT PARTITIONED COLUMNS for Table &Table_Name.

SELECT COLUMN_NAME , COLUMN_POSITION FROM All_Part_Key_Columns
WHERE NAME = UPPER('&Table_Name')
AND Owner = UPPER('&Owner') ;


PROMPT
PROMPT PARTITIONS for Table &Table_Name.

-- SELECT PARTITION_NAME , NUM_ROWS
-- FROM All_Tab_Partitions
-- WHERE TABLE_NAME = UPPER('Table_Name')
-- AND Table_Owner = UPPER('Owner') ;


break on report
compute sum of NUM_ROWS on report
select partition_position, partition_name, num_rows from all_tab_partitions
where table_name = UPPER('&Table_Name')
AND Table_Owner = UPPER('&Owner') ;

prompt if table is partitioned num_rows is blank ... then run following command
prompt execute dbms_stats.gather_table_stats(UPPER('..Owner.'),UPPER('..Table_Name.'));




PROMPT
PROMPT TRIGGERS for Table &Table_Name.

SELECT Trigger_Name FROM All_Triggers
WHERE TABLE_NAME = UPPER('&Table_Name')
AND Owner = UPPER('&Owner') ;

PROMPT
PROMPT DEPENDANTS for Table &Table_Name.

BREAK ON TYPE SKIP 1

SELECT TYPE , NAME FROM ALL_DEPENDENCIES
WHERE REFERENCED_NAME = UPPER('&Table_Name')
AND Owner = UPPER('&Owner')
ORDER BY TYPE ;

CLEAR BREAKS

SET TERMOUT ON
UNDEF Owner
UNDEF Table_Name

prompt --------CUT-----------------------------CUT--------------------------CUT-----------------------------CUT---------------------

List Processes based on %CPU and Memory Usage

#! /bin/bash
#List processes based on %cpu and memory usage

echo "Start Time" `date`
# By default, it display the list of processes based on the cpu and memory usage #
if [ $# -eq 0 ]
then

echo "List of processes based on the %cpu Usage"
ps -e -o pcpu,cpu,nice,state,cputime,args --sort pcpu # sorted based on %cpu
echo "List of processes based on the memory Usage"
ps -e -orss=,args= | sort -b -k1,1n # sorted bases rss value

# If arguements are given (mem/cpu)
else
case "$1" in
mem)
echo "List of processes based on the memory Usage"
ps -e -orss=,args= | sort -b -k1,1n
;;
cpu)
echo "List of processes based on the %cpu Usage"
ps -e -o pcpu,cpu,nice,state,cputime,args --sort pcpu
;;
*)
echo "Invalid Argument Given \n"
echo "Usage : $0 mem/cpu"
exit 1
esac

fi
echo "End Time" `date`
exit 0



===


$ processes.sh

$ processes.sh mem

$ processes.sh cpu

who is using high CPU percentage

apt-amd-02:/home/anujs/Downloads # cat uniqU.sh

#! /bin/bash

w > /tmp/a

echo "Total number of unique users logged in currently"
cat /tmp/a| sed '1,2d' | awk '{print $1}' | uniq | wc -l
echo ""

echo "List of unique users logged in currently"
cat /tmp/a | sed '1,2d'| awk '{print $1}' | uniq
echo ""

echo "The user who is using high %cpu"
cat /tmp/a | sed '1,2d' | awk '$7 > maxuid { maxuid=$7; maxline=$0 }; END { print maxuid, maxline }'

echo ""
echo "List of users logged in and what they are doing"
cat /tmp/a

find out Free memory ( shell script )

$ cat mem.sh
#! /bin/bash

# Total memory space details

echo "Memory Space Details"
free -t -m | grep "Total" | awk '{ print "Total Memory space : "$2 " MB";
print "Used Memory Space : "$3" MB";
print "Free Memory : "$4" MB";
}'

echo "Swap memory Details"
free -t -m | grep "Swap" | awk '{ print "Total Swap space : "$2 " MB";
print "Used Swap Space : "$3" MB";
print "Free Swap : "$4" MB";
}'