Startup time
SQL> select to_char(startup_time, 'HH24:MI DD-MON-YY') "Startup time" from v$instance;
Startup time
---------------
13:13 28-AUG-11
How large is the database
col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p;
Database Size Used space Free space
-------------------- -------------------- --------------------
1 GB 1 GB 0 GB
Distribution of objects and data
set pages 999
col "size MB" format 999,999,999
col "Objects" format 999,999,999
select obj.owner "Owner"
, obj_cnt "Objects"
, decode(seg_size, NULL, 0, seg_size) "size MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj
, (select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg
where obj.owner = seg.owner(+)
order by 3 desc ,2 desc, 1
/
Owner Objects size MB
------------------------------ ------------ ------------
SYS 22,904 531
SYSMAN 1,321 51
XDB 680 49
MDSYS 885 33
SYSTEM 454 23
OLAPSYS 720 16
WMSYS 242 7
CTXSYS 339 5
EXFSYS 281 4
DBSNMP 46 2
ORDSYS 1,669 1
DMSYS 189 1
SCOTT 9 1
OUTLN 8 1
TSMSYS 3 1
PUBLIC 19,981 0
ORDPLUGINS 10 0
SI_INFORMTN_SCHEMA 8 0
Show the ten largest objects in the database
col owner format a15
col segment_name format a30
col segment_type format a15
col mb format 999,999,999
select owner
, segment_name
, segment_type
, mb
from (
select owner
, segment_name
, segment_type
, bytes / 1024 / 1024 "MB"
from dba_segments
order by bytes desc
)
where rownum < 11
/
OWNER SEGMENT_NAME SEGMENT_TYPE MB
--------------- ------------------------------ --------------- ------------
SYS IDL_UB1$ TABLE 167
SYS SOURCE$ TABLE 41
MDSYS SYS_LOB0000046123C00006$$ LOBSEGMENT 18
SYS IDL_UB2$ TABLE 16
SYS C_TOID_VERSION# CLUSTER 16
SYS PK_C0 INDEX 10
SYS PK_CS INDEX 10
SYS PK_O INDEX 10
SYS PK_CT INDEX 10
SYS SYS_LOB0000046606C00004$$ LOBSEGMENT 9
10 rows selected.
Is java installed in the database?
select count(*) from all_objects where object_type like '%JAVA%'and owner = 'SYS';
COUNT(*)
----------
15741
Display character set information
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8ISO8859P1
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 10.2.0.1.0
Show all used features
NAME DETECTED_USAGES
-------------------------------------------------- ---------------
Partitioning (system) 1
Protection Mode - Maximum Performance 1
Streams (system) 1
Streams (user) 1
Virtual Private Database (VPD) 1
Automatic Segment Space Management (system) 1
Automatic Segment Space Management (user) 1
Automatic SQL Execution Memory 1
Automatic Undo Management 1
Character Set 1
Dynamic SGA 1
Locally Managed Tablespaces (system) 1
Locally Managed Tablespaces (user) 1
Administration - Session
Show all connected users
set lines 100 pages 999
col ID format a15
select username
, sid || ',' || serial# "ID"
, status
, last_call_et "Last Activity"
from v$session
where username is not null
order by status desc
, last_call_et desc
/
USERNAME ID STATUS Last Activity
------------------------------ --------------- -------- -------------
WEBUSER 116,18996 INACTIVE 1766
WEBUSER 132,33364 INACTIVE 1727
WEBUSER 127,4392 INACTIVE 1709
WEBUSER 124,20787 INACTIVE 1682
SYS 143,10 INACTIVE 436
ORA_BLA 126,37072 INACTIVE 3
ORA_BLA 115,6676 INACTIVE 3
ORA_BLA 139,5349 INACTIVE 3
REP_USER 141,3881 ACTIVE 0
REP_USER 144,17898 ACTIVE 0
Time since last user activity
set lines 100 pages 999
select username
, floor(last_call_et / 60) "Minutes"
, status
from v$session
where username is not null
order by last_call_et
/
USERNAME Minutes STATUS
------------------------------ ---------- --------
SYS 0 ACTIVE
ORA_BLA 1 INACTIVE
ORA_BLA 1 INACTIVE
ORA_BLA 1 INACTIVE
SYS 8 INACTIVE
WEBUSER 29 INACTIVE
WEBUSER 29 INACTIVE
WEBUSER 30 INACTIVE
WEBUSER 30 INACTIVE
REP_USER 34734 INACTIVE
Sessions sorted by logon time
set lines 100 pages 999
col ID format a15
col osuser format a15
col login_time format a14
select username
, osuser
, sid || ',' || serial# "ID"
, status
, to_char(logon_time, 'hh24:mi dd/mm/yy') login_time
, last_call_et
from v$session
where username is not null
order by login_time
/
USERNAME OSUSER ID STATUS LOGIN_TIME LAST_CALL_ET
--------------- --------------- ---------- -------- -------------- ------------
ORA_BLA 115,6676 INACTIVE 06:51 19/05/08 181
REP_USER oracle 141,3881 ACTIVE 08:17 19/05/08 0
REP_USER oracle 144,17898 ACTIVE 08:18 19/05/08 0
REP_USER oracle 129,8407 ACTIVE 08:23 19/05/08 0
REP_USER oracle 145,19887 ACTIVE 08:28 19/05/08 0
ORA_BLA 139,5349 INACTIVE 09:51 19/05/08 181
ORA_BLA 126,37072 INACTIVE 09:51 19/05/08 181
WEBUSER oracle 116,18996 INACTIVE 10:51 19/05/08 1944
WEBUSER oracle 127,4392 INACTIVE 10:52 19/05/08 1887
WEBUSER oracle 124,20787 INACTIVE 10:52 19/05/08 1860
WEBUSER oracle 132,33364 INACTIVE 10:52 19/05/08 1905
SYS oracle 131,34710 ACTIVE 10:56 19/05/08 0
SYS oracle 143,10 INACTIVE 14:13 16/04/08 614
Show user info including os pid
col "SID/SERIAL" format a10
col username format a15
col osuser format a15
col program format a40
select s.sid || ',' || s.serial# "SID/SERIAL"
, s.username
, s.osuser
, p.spid "OS PID"
, s.program
from v$session s
, v$process p
Where s.paddr = p.addr
order by to_number(p.spid)
/
SID/SERIAL USERNAME OSUSER OS PID PROGRAM
---------- --------------- --------------- ------------ ----------------------------------------
170,1 oracle 8339 oracle@dg1.ecsme.com (PMON)
169,1 oracle 8341 oracle@dg1.ecsme.com (PSP0)
168,1 oracle 8343 oracle@dg1.ecsme.com (MMAN)
166,1 oracle 8345 oracle@dg1.ecsme.com (DBW0)
167,1 oracle 8347 oracle@dg1.ecsme.com (LGWR)
162,1 oracle 8349 oracle@dg1.ecsme.com (CKPT)
165,1 oracle 8351 oracle@dg1.ecsme.com (SMON)
163,1 oracle 8353 oracle@dg1.ecsme.com (RECO)
164,1 oracle 8355 oracle@dg1.ecsme.com (CJQ0)
161,1 oracle 8357 oracle@dg1.ecsme.com (MMON)
160,1 oracle 8359 oracle@dg1.ecsme.com (MMNL)
154,1 oracle 8382 oracle@dg1.ecsme.com (QMNC)
150,1 oracle 8400 oracle@dg1.ecsme.com (q000)
146,1 oracle 8404 oracle@dg1.ecsme.com (q001)
159,16 SYS oracle 9791 sqlplus@dg1.ecsme.com (TNS V1-V3)
148,195 oracle 9888 oracle@dg1.ecsme.com (J000)
Show a users current sql
Select sql_text
from v$sqlarea
where (address, hash_value) in
(select sql_address, sql_hash_value
from v$session
where username like '&username')
/
Enter value for username: SYS
old 6: where username like '&username')
new 6: where username like 'SYS')
SQL_TEXT
----------------------------------------------------------------------------------------------------
Select sql_text from v$sqlarea where (address, hash_value) in (select sql_address, sql_hash_value
from v$session where username like 'SYS')
Session status associated with the specified os process id
select s.username
, s.sid
, s.serial#
, p.spid
, last_call_et
, status
from V$SESSION s
, V$PROCESS p
where s.PADDR = p.ADDR
and p.spid='&pid'
/
USERNAME SID SERIAL# SPID LAST_CALL_ET STATUS
--------------- ---------- ---------- ------------ ------------ --------
SYS 159 16 9791 0 ACTIVE
All active sql
set feedback off
set serveroutput on size 9999
column username format a20
column sql_text format a55 word_wrapped
begin
for x in
(select username||'('||sid||','||serial#||') ospid = '|| process ||
' program = ' || program username,
to_char(LOGON_TIME,' Day HH24:MI') logon_time,
to_char(sysdate,' Day HH24:MI') current_time,
sql_address,
sql_hash_value
from v$session
where status = 'ACTIVE'
and rawtohex(sql_address) <> '00'
and username is not null ) loop
for y in (select sql_text
from v$sqlarea
where address = x.sql_address ) loop
if ( y.sql_text not like '%listener.get_cmd%' and
y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%' ) then
dbms_output.put_line( '--------------------' );
dbms_output.put_line( x.username );
dbms_output.put_line( x.logon_time || ' ' || x.current_time || ' SQL#=' || x.sql_hash_value);
dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
end if;
end loop;
end loop;
end;
/
Display any long operations
set lines 100 pages 999
col username format a15
col message format a40
col remaining format 9999
select username
, to_char(start_time, 'hh24:mi:ss dd/mm/yy') started
, time_remaining remaining
, message
from v$session_longops
where time_remaining = 0
order by time_remaining desc
/
List open cursors per user
set pages 999
select sess.username
, sess.sid
, sess.serial#
, stat.value cursors
from v$sesstat stat
, v$statname sn
, v$session sess
where sess.username is not null
and sess.sid = stat.sid
and stat.statistic# = sn.statistic#
and sn.name = 'opened cursors current'
order by value
/
USERNAME SID SERIAL# CURSORS
--------------- ---------- ---------- ----------
SYS 152 122 1
SYS 159 16 4
or
set lines 100 pages 999
select count(hash_value) cursors
, sid
, user_name
from v$open_cursor
group by
sid
, user_name
order by
cursors
/
CURSORS SID USER_NAME
---------- ---------- ------------------------------
1 150 SYS
1 148 SYS
1 156 SYS
2 149 SYS
5 164 SYS
5 145 SYS
7 147 SYS
7 155 SYS
7 159 SYS
8 143 SYS
15 161 SYS
17 153 SYS
22 158 SYS
38 152 SYS
Administration - Init Parameters
Show latent parameter changes
select name,value from v$parameter where ismodified != 'FALSE';
Show non-default parameters
set pages 999 lines 100
col name format a30
col value format a50
select name,value from v$parameter where isdefault = 'FALSE' and value is not null order by name;
or for name='parameter' style...
set pages 999 lines 100
select name || '=' || decode(type, 2, '''') || value || decode(type, 2, '''') parameter from v$parameter where isdefault = 'FALSE'
and value is not null order by name;
Reset/Unset a spfile parameter
Setting a parameter to =' ' often isn't enough. Do this instead...
alter system reset
/
The sid='*' bit is always necessary, even in non RAC database.
