Display all connected sessions

src: http://www.praetoriate.com/oracle_tips_session_details.htm

---

rem session.sql - displays all connected sessions

set echo off;
set termout on;
set linesize 80;
set pagesize 60;
set newpage 0;

select
rpad(c.name||':',11)||rpad(' current logons='||
(to_number(b.sessions_current)),20)||'cumulative logons='||
rpad(substr(a.value,1,10),10)||'highwater mark='||
b.sessions_highwater Information
from
v$sysstat a,
v$license b,
v$database c
where
a.name = 'logons cumulative'
;

ttitle "dbname Database|UNIX/Oracle Sessions";

set heading off;
select 'Sessions on database '||substr(name,1,8) from v$database;
set heading on;

select
substr(a.spid,1,9) pid,
substr(b.sid,1,5) sid,
substr(b.serial#,1,5) ser#,
substr(b.machine,1,6) box,
substr(b.username,1,10) username,
--       b.server,
substr(b.osuser,1,8) os_user,
substr(b.program,1,30) program
from v$session b, v$process a
where
b.paddr = a.addr
and type='USER'
order by spid;

ttitle off;

spool off;

Show the used space of an object

Show the used space of an object:

src: asktom.oracle.com

---

create or replace
procedure show_space
( p_segname in varchar2,
  p_owner   in varchar2 default user,
  p_type    in varchar2 default 'TABLE',
  p_partition in varchar2 default NULL )
authid current_user
as
    l_free_blks                 number;

    l_total_blocks              number;
    l_total_bytes               number;
    l_unused_blocks             number;
    l_unused_bytes              number;
    l_LastUsedExtFileId         number;
    l_LastUsedExtBlockId        number;
    l_LAST_USED_BLOCK           number;
    procedure p( p_label in varchar2, p_num in number )
    is
    begin
        dbms_output.put_line( rpad(p_label,40,'.') ||
                              p_num );
    end;
begin
    for x in ( select tablespace_name
                 from dba_tablespaces
                where tablespace_name = ( select tablespace_name
                                            from dba_segments
                                           where segment_type = p_type
                                             and segment_name = p_segname
                                  and SEGMENT_SPACE_MANAGEMENT  'AUTO' )
             )
    loop
    dbms_space.free_blocks
    ( segment_owner     => p_owner,
      segment_name      => p_segname,
      segment_type      => p_type,
      partition_name    => p_partition,
      freelist_group_id => 0,
      free_blks         => l_free_blks );
    end loop;

    dbms_space.unused_space
    ( segment_owner     => p_owner,
      segment_name      => p_segname,
      segment_type      => p_type,
          partition_name    => p_partition,
      total_blocks      => l_total_blocks,
      total_bytes       => l_total_bytes,
      unused_blocks     => l_unused_blocks,
      unused_bytes      => l_unused_bytes,
      LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
      LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
      LAST_USED_BLOCK => l_LAST_USED_BLOCK );

    p( 'Free Blocks', l_free_blks );
    p( 'Total Blocks', l_total_blocks );
    p( 'Total Bytes', l_total_bytes );
    p( 'Total MBytes', trunc(l_total_bytes/1024/1024) );
    p( 'Unused Blocks', l_unused_blocks );
    p( 'Unused Bytes', l_unused_bytes );
    p( 'Last Used Ext FileId', l_LastUsedExtFileId );
    p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
    p( 'Last Used Block', l_LAST_USED_BLOCK );
end;
/

Display datafile usage

Display datafile usage.

src: http://www.oracle-consultant.co.uk

---

column tsname       format a20         heading 'Tablespace Name'
column flname       format a70         heading 'Filename'
column siz          format 999,999,990 heading 'File Size|(MB)'
column maxsiz       format 999,999,990 heading 'Max Size|(MB)'
column pctmax       format 990         heading 'Pct|Max'

set linesize  1000
set trimspool on
set pagesize  32000
set verify    off
set feedback  off

PROMPT
PROMPT ***********************
PROMPT *** DATAFILE STATUS ***
PROMPT ***********************

select file_name                                          flname
,      tablespace_name                                    tsname
,      bytes/1024/1024                                    siz
,      decode(maxbytes,0,0,maxbytes/1024/1024)            maxsiz
,      decode(maxbytes,0,0,bytes/maxbytes*100)            pctmax
from   dba_data_files
/

column tsname      clear
column flname      clear
column siz         clear
column maxsiz      clear
column pctmax      clear

Display tablespace usage

This script shows the tablespace usage.
src: http://www.oracle-consultant.co.uk

---

break on report
compute sum of tbs_size_mb on report
compute sum of used        on report
compute sum of avail       on report

column tsname       format a20         heading ‘Tablespace Name’
column tbs_size_mb  format 999,999     heading ‘Size|(MB)’
column used         format 999,999     heading ‘Used|(MB)’
column avail        format 999,999     heading ‘Free|(MB)’
column used_visual  format a11         heading ‘Used’
column pct_used     format 999         heading ‘% Used’

set linesize  1000
set trimspool on
set pagesize  32000
set verify    off
set feedback  off

PROMPT
PROMPT *************************
PROMPT *** TABLESPACE STATUS ***
PROMPT *************************

SELECT   df.tablespace_name                           tsname
,        sum(df.bytes)/1024/1024                      tbs_size_mb
,        nvl(sum(e.used_bytes)/1024/1024,0)           used
,        nvl(sum(f.free_bytes)/1024/1024,0)           avail
,        rpad(’ ‘||rpad(’X',round(sum(e.used_bytes)
*10/sum(df.bytes),0), ‘X’),11,’-')           used_visual
,        nvl((sum(e.used_bytes)*100)/sum(df.bytes),0) pct_used
FROM     sys.dba_data_files df
,        (SELECT   file_id
,        sum(nvl(bytes,0)) used_bytes
FROM     sys.dba_extents
GROUP BY file_id) e
,        (SELECT   max(bytes) free_bytes
,        file_id
FROM     dba_free_space
GROUP BY file_id) f
WHERE    e.file_id(+) = df.file_id
AND      df.file_id   = f.file_id(+)
GROUP BY df.tablespace_name
ORDER BY 6
/

clear breaks