Useful command line alias for Oracle and Linux

User specific aliases

Useful commands

PURPOSE: All documents are provided on this Blog just for educational purposes only.  Please make sure that you run it in your test environment before to move on to production environment.

Useful command line alias for Oracle and Linux.

# Agent Commands:

alias agentstart='$ORACLE_BASE/agent/agent_inst/bin/emctl start agent'

alias agentstatus='$ORACLE_BASE/agent/agent_inst/bin/emctl status agent'

alias agentstop='$ORACLE_BASE/agent/agent_inst/bin/emctl stop agent'

# OEM Commands:

alias oemstart='$ORACLE_BASE/middleware/bin/emctl start oms'

alias oemstatus='$ORACLE_BASE/middleware/bin/emctl status oms'

alias oemstop='$ORACLE_BASE/middleware/bin/emctl stop oms'

alias oemstopall='$ORACLE_BASE/middleware/bin/emctl stop oms -all'

# DB related:

alias cdnet='cd $ORACLE_HOME/network'

alias cdtns='cd $ORACLE_HOME/network/admin'

alias dbs='cd $ORACLE_HOME/dbs'

alias home='cd $ORACLE_HOME'

alias setdb_home='cd /users/oracle/;. set_home_env'

alias oraclebase='cd $ORACLE_BASE'

alias usr='cd /users/oracle'

alias cddba='cd /users/oracle/dba'

alias cds='cd /users/oracle/dba/scripts'

alias cdd='cd /oracle/download'

alias cdtrace='cd $ORACLE_BASE/diag/rdbms/${ORACLE_SID,,}/${ORACLE_SID}/trace'

alias talertasm='tail -f $ORACLE_BASE/diag/asm/${ORACLE_SID,,}/${ORACLE_SID}/trace/alert_${ORACLE_SID}.log'

alias talertdb='tail -f $ORACLE_BASE/diag/rdbms/${ORACLE_SID,,}/${ORACLE_SID}/trace/alert_${ORACLE_SID}.log'

alias viasmalert='vi $ORACLE_BASE/diag/asm/${ORACLE_SID,,}/${ORACLE_SID}/trace/alert_${ORACLE_SID}.log'

alias vidbalert='vi $ORACLE_BASE/diag/rdbms/${ORACLE_SID,,}/${ORACLE_SID}/trace/alert_${ORACLE_SID}.log'

# ASM related srvctl:

alias dbstop='srvctl stop database -d ${ORACLE_SID}'

alias dbstart='srvctl start database -d ${ORACLE_SID}'

alias dbstatus='srvctl status database -d ${ORACLE_SID}'

alias lstatus='srvctl status  listener -l LISTENER'

alias lstart='srvctl start listener -l LISTENER'

alias lstop='srvctl stop listener -l LISTENER'

alias asmstatus='srvctl status asm'

alias asmstop='srvctl stop asm –f'

alias asmstart='srvctl start asm'

alias crsstopall='crsctl stop res –all'

alias cresstop='crsctl stop has'

alias crsstat='crsctl stat res -t'

alias crsstart='crsctl start has'

# Linux related:

##calculation

alias bc='bc -l'

## pass options to free ##

alias meminfo='free -m -l -t'

## get top process eating memory

alias psmem='ps auxf | sort -nr -k 4'

alias psmem10='ps auxf | sort -nr -k 4 | head -10'

## get top process eating cpu ##

alias pscpu='ps auxf | sort -nr -k 3'

alias pscpu10='ps auxf | sort -nr -k 3 | head -10'

## Get server cpu info ##

alias cpuinfo='lscpu'

alias meminfo='lsmem'

##Clear Bash history completely

alias clear_history='history -cw'

##Remove a certain line from Bash history

alias clear_history_line='history -dw 352'

##Clear current session history

alias clear_session_history='history -r'

##Don’t save command in Bash history

alias no_save='<space>command'

# Useful commands 

User profile DEFAULT:

set lines 200

set pages 50

col PROFILE for a40

col LIMIT for a40

select * from dba_profiles where resource_type='PASSWORD' and PROFILE='DEFAULT' order by resource_name;

alter profile DEFAULT limit PASSWORD_LIFE_TIME UNLIMITED;

execute DBMS_AUTO_TASK_ADMIN.DISABLE;

check user status:

select created,expiry_date,account_status from dba_users where username='username';

select username, DEFAULT_TABLESPACE, created from dba_users where username='username';

Get user grants just for Open users 

col GRANTEE format a30

col PRIVILEGE format a30

col ACCOUNT_STATUS format a20

set linesize 200;

set pagesize 200;

select account_status, GRANTEE, PRIVILEGE  from dba_users

inner join DBA_SYS_PRIVS on GRANTEE = username

where account_status='OPEN'

and

GRANTEE not in ('SYS'

,'SYS$UMF'

,'AUDSYS'

,'DBA'

,'APEX_050000'

,'WMSYS'

,'EXP_FULL_DATABASE'

,'SYSBACKUP'

,'OEM_MONITOR'

,'SYSTEM'

,'XDB'

,'SYSRAC'

,'APEX_210200'

,'DATAPUMP_EXP_FULL_DATABASE'

,'IMP_FULL_DATABASE'

,'DBSNMP'

,'DATAPUMP_IMP_FULL_DATABASE'

,'ANONYMOUS'

,'AQ_ADMINISTRATOR_ROLE'

,'SYSDG'

,'DBSNMP'

,'APPQOSSYS'

,'EM_EXPRESS_ALL'

,'ORACLE_OCM'

,'APPQOSSYS'

,'OEM_ADVISOR'

,'EM_EXPRESS_ALL'

,'SYSKM');

Find out the privileges.

select grantor || ' granted ' || privilege || ' on ' || table_name || ' owned by '|| owner || ' to '|| grantee

from dba_tab_privs where grantee like upper('&GRANTEE_NAME') order by privilege;

#Where GRANTEE_NAME is the user you want to find out the privileges.

Startup time of DB:

SET LINES 200
SET PAGES 999
COLUMN INSTANCE_NAME FOR A20
SELECT INSTANCE_NAME,TO_CHAR(STARTUP_TIME, 'HH24:MI DD-MON-YY') "STARTUP TIME"
FROM V$INSTANCE;

How to find the Last login date time for Database users

select USERNAME,LAST_LOGIN from dba_users;

History of startup time:

COL INSTANCE_NAME FOR A10
SELECT INSTANCE_NAME,TO_CHAR(STARTUP_TIME, 'HH24:MI DD-MON-YY') FROM DBA_HIST_DATABASE_INSTANCE ORDER BY STARTUP_TIME DESC;

Startup DB detail:

SET LINE 60
COLUMN HOSTNAME FOR A60
COLUMN INSTANCE_NAME FOR A60
COLUMN STIME FOR A60
COLUMN UPTIME FOR A60
SELECT
'HOSTNAME : ' || HOST_NAME
,'INSTANCE NAME : ' || INSTANCE_NAME
,'STARTED AT : ' || TO_CHAR(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') STIME
,'UPTIME : ' || FLOOR(SYSDATE - STARTUP_TIME) || ' DAYS(S) ' ||
TRUNC( 24*((SYSDATE-STARTUP_TIME) -
TRUNC(SYSDATE-STARTUP_TIME))) || ' HOUR(S) ' ||
MOD(TRUNC(1440*((SYSDATE-STARTUP_TIME) -
TRUNC(SYSDATE-STARTUP_TIME))), 60) ||' MINUTE(S) ' ||
MOD(TRUNC(86400*((SYSDATE-STARTUP_TIME) -
TRUNC(SYSDATE-STARTUP_TIME))), 60) ||' SECONDS' UPTIME
FROM
SYS.V_$INSTANCE;

Instance using pfile or spfile - check

SQL> SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type"

   FROM sys.v_$parameter WHERE name = 'spfile';  2

Init F

------

PFILE

Getting Long Active Sessions

set linesize 300

col TERMINAL format a10

col USERNAME format a30

col PROGRAM format a10;

SELECT USERNAME, 

       TERMINAL, 

       PROGRAM, 

       SQL_ID, 

       LOGON_TIME, 

       ROUND((SYSDATE-LOGON_TIME)*(24*60),1) as MINUTES_LOGGED_ON, 

       ROUND(LAST_CALL_ET/60,1) as Minutes_FOR_CURRENT_SQL  

  From v$session 

 WHERE STATUS='ACTIVE' 

   AND USERNAME IS NOT NULL

ORDER BY MINUTES_LOGGED_ON DESC;

00_who_is_that_SQL_ID.sql

set linesize 150

select MACHINE, SERIAL#, SID from v$session where SQL_ID='&SQL_ID';

01_who_is_that_SID.sql

set lines 200

col username for a10

col osuser for a10

col machine for a10

col program for a10

col resource_consumer_group for a10

col client_info for a10

select  serial#,

 username,

 osuser,

 machine,

 program,

 resource_consumer_group,

 client_info

from v$session where sid=&sid;

02_What_did_that_SID_do.sql

set lines 200

col username for a10

col osuser for a10

col machine for a10

col program for a10

col resource_consumer_group for a10

col client_info for a10

select distinct sql_id, session_serial# from v$active_session_history

where sample_time >  sysdate - interval '5' minute

and session_id=&sid;

03_Retrieve_the_SQL_from_the_Library_Cache.sql

set pagesize 200

col sql_text for a80

select sql_text from v$sql where sql_id='&sqlid';

04_bind_variables_string.sql

set linesize 150

col name format a10

col VALUE_STRING  format a20

SELECT NAME,POSITION,DATATYPE_STRING,VALUE_STRING FROM v$sql_bind_capture WHERE sql_id='&sqlid';

05_Retrieve_SQL_from_sqltxt.sql

SELECT SQL_ID FROM V$SQLSTATS WHERE SQL_TEXT like '%put text you want to retrieve%';

Find from history who allocated TEMP Tablespace

select * from v$active_session_history where TEMP_SPACE_ALLOCATED is not null order by TEMP_SPACE_ALLOCATED desc;

# who is the user:

select user_id, username from dba_users where user_id='91';

Kill all active and inactive oracle sessions

SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' 
FROM v$session where username='YOUR_USER';

Get plan_hash_value 

select LAST_LOAD_TIME,LAST_ACTIVE_TIME,sql_id, plan_hash_value, exact_matching_signature,
sql_plan_baseline from v$sql where sql_id='30u7wxd0rhdy' order by LAST_LOAD_TIME;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('30u7wxd0rhdy','2253306759'));

Get size of all tables in the DB

select owner, table_name, round((num_rows*avg_row_len)/(1024*1024)) MB 
from all_tables 
where owner not like 'SYS%'  -- Exclude system tables.
and num_rows > 0  -- Ignore empty Tables.
order by MB desc -- Biggest first.
;

check space utilization for db_recovery_file_dest_size:
col name format a32
col size_mb format 999,999,999
col used_mb format 999,999,999
col pct_used format 999
select
name,
ceil( space_limit / 1024 / 1024) size_mb,
ceil( space_used / 1024 / 1024) used_mb,
decode( nvl( space_used, 0),0, 0,
ceil ( ( space_used / space_limit) * 100) ) pct_used
from
v$recovery_file_dest
order by
name desc;
-- NO NEED DB Restart for the following change
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE =<size> SCOPE=BOTH;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE =3500G SCOPE=BOTH;
check when the last Analyzed/Statistics_Schemas completed.

col TABLE_NAME format a30;
set pagesize 200
select t.table_name, t.last_analyzed
from dba_tables t
where t.owner = 'EDDYUSER'
order by t.last_analyzed desc;

select dbms_stats. get_stats_history_availability from dual;



How to temporarily disable access to Oracle database
NOTE: Existing sessions are not terminated.
ALTER SYSTEM enable RESTRICTED SESSION;
ALTER SYSTEM DISABLE RESTRICTED SESSION;

# Kill the multiple session in a single shoot
select sid, serial#.program.module from v$session;

select 'alter system kill session '''||sid||','||serial#||''';' from v$session;