Oracle Commands

to get user_name and connect string as your SQL prompt:-

add below line to $ORACLE_HOME/sqlplus/admin/glogin.sql file...
set sqlprompt "&&_USER@&&_CONNECT_IDENTIFIER SQL>"

Instance has crashed and resources were not released:-

$ sysresv -i
IPC Resources for ORACLE_SID "orcl":
Shared Memory:
ID KEY
32768 0xdc70f4e4
Semaphores:
ID KEY
98304 0xb11a5934
Oracle Instance not alive for sid "orcl"
Remove ipc resources for sid "orcl" (y/n)? y
Done removing ipc resources for sid "orcl"

To find a free space in datafile wise: -

set linesize 150
column file_name format a50
column tablespace_name format a10
column free_space_in_mb format 9999.9999
select ddf.file_name,ddf.tablespace_name,
sum(dfs.bytes)/1024/1024 free_space_in_mb
from dba_data_files ddf, dba_free_space dfs
where ddf.file_id = dfs.file_id
and ddf.tablespace_name like '&tablespace_name_in_caps'
group by ddf.file_name,ddf.tablespace_name;


Displays Actual size, used & freespace within the datafiles: -

TTI "Allocated, Used & Free space within datafiles"

COLUMN free_space_mb format 999999.90
COLUMN allocated_mb format 999999.90
COLUMN used_mb format 999999.90

SELECT SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024 allocated_mb,
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))
used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;

to kill inactive session: -
set heading off
set echo off
spool kill.sql
select 'alter system kill session '''||sid||','||serial#||''';' from v$session where STATUS='INACTIVE';
spool off
@kill.sql

to get Current date in filename while spooling: -
set term OFF echo OFF doc OFF
set hea OFF feed OFF ver OFF
ttitle OFF
spool tst1.sql
select 'spool tst'||to_char(sysdate,'ddmmyy')||'.sql' from dual;
spool off;
@tst1.sql
select sysdate from dual;
spool off

To get the client address: -
select sys_context('USERENV','IP_ADDRESS') from dual;

To get the server address: -
select utl_inaddr.get_host_address from dual;

To get the IP address of another server: -
select utl_inaddr.get_host_address ('') from dual;

To get the server name from its IP: -
select utl_inaddr.get_host_name ('') from dual;

So to get all clients ip: -
select sid, machine, utl_inaddr.get_host_address(machine) ip
from v$session
where type='USER' and username is not null
/

Script to calculate the row length (approximately): -
1) As a sys user: -
select sum(de.bytes)/dt.num_rows "Avg Row Size"
from dba_extents de, dba_tables dt
where de.owner like 'OWNER_NAME' and de.segment_name like 'SEGMENT_NAME'
and de.segment_name=dt.table_name
group by dt.num_rows;

2) As a normal user: -
select sum(ue.bytes)/ut.num_rows "Avg Row Size"
from user_extents ue, user_tables ut
where ue.segment_name like 'SEGMENT_NAME' and ue.segment_name=ut.table_name
group by ut.num_rows;