Andrew Fraser DBA

The sm$ views are an easy way of seeing tablespace space usage. There is also an sm$ts_free view.

set pages 9999
col tot_mb form 999,999
col use_mb form 999,999
col pct_used form 999
select t.tablespace_name, t.bytes/1024/1024 tot_mb, u.bytes/1024/1024 use_mb, 100*u.bytes/t.bytes pct_used
from sys.sm$ts_avail t, sys.sm$ts_used u
where t.tablespace_name = u.tablespace_name(+)
order by 4 desc
/
-- two queries below for ancient dictionary managed tablespaces only
select owner, segment_name, extents, pct_increase, max_extents, next_extent/1024/1024, tablespace_name
from dba_segments
where extents > max_extents - 5
/
select owner, segment_name, extents, pct_increase, max_extents, next_extent/1024/1024, tablespace_name
from dba_segments s
where next_extent > ( select max(f.bytes) from dba_free_space f where f.tablespace_name = s.tablespace_name)
/

– *Update* – and also database level space:

select sum(bytes)/1024/1024/1024 allocated_gb
from (
  select sum(bytes) bytes from dba_data_files
  union all
  select sum(bytes) bytes from dba_temp_files
  union all
  select sum(l.bytes) bytes from v$log l, v$logfile f where l.group# = f.group#
)
/
select sum(bytes)/1024/1024/1024 used_gb
from sys.sm$ts_used
/

– *Update2* – generate script to add 1000m to each datafile:

set pages 9999 lines 112
select 'alter database datafile '''||file_name||''' resize '||(bytes+1000*1024*1024)/1024/1024||' m ;'
from dba_data_files where tablespace_name in ('TS_INDEX1')
order by 1
/

Very useful SQL, from Tom Kyte:

select decode(count(*), 1, 'spfile', 'pfile' ) from v$spparameter where rownum=1 and isspecified='TRUE' ;

If it it using an spfile, an easy way to see the parameters is to generate an init.ora copy:

SQL> conn / as sysdba

(or connect as sysoper)

SQL> create pfile = '/tmp/init.ora' from spfile;

This shell script runs against every database that is up and running (has an entry in the ps list). Which I think is better than searching through oratab (since not all of those may be up and running, esp. on dev machines).

You can run any SQL within it, but this particular version prints out the total oracle memory (sga and pga) use. That is useful because there is often a risk of more memory being allocated to oracle than actual physical memory on the server. That is especially true of dev servers with many databases running.

export ORAENV_ASK=NO
for ORACLE_SID in `ps -ef|grep pmon|grep -v grep|awk -F_ '{print $3}'`
do
. oraenv
sqlplus -s "/ as sysdba" <<e1
set pages 0 head off feed off
select '$ORACLE_SID sga', sum(value)/1024/1024 mb from v\$sga
/
select '$ORACLE_SID pga', sum(value)/1024/1024 mb from v\$sesstat s, v\$statname n
where s.statistic# = n.statistic#
and n.name = 'session pga memory'
group by '$ORACLE_SID pga'
/
–- select '$ORACLE_SID sga_max_size', value/1024/1024 mb from v\$parameter where name = 'sga_max_size';
–- select '$ORACLE_SID pga_target', value/1024/1024 mb from v\$parameter where name = 'pga_aggregate_target';
e1
done

The results are best pasted into a spreadsheet where there are many databases.

On 9i on Solaris, sga_max_size preallocates the memory at OS leve, making it largely useless, so there would be worth also checking that, with e.g. the commented out line above.

One thing to watch is that to query a v$ or sm$ view direct from a shell script, you have to escape out the $, hence the “v$” above rather than "v$".

Array use in this script (rather than clumsier writing to temporary files) came from Daniel D’Souza.