SQL Developer Data modeler

SQL Developer Data Modeler is a free data modeling and design tool, proving a full spectrum of data and database modeling tools and utilities, including modeling for Entity Relationship Diagrams (ERD), Relational (database design), Data Type and Multi-dimensional modeling, with forward and reverse engineering and DDL code generation. The Data Modeler imports from and exports to a variety of sources and targets, provides a variety of formatting options and validates the models through a predefined set of design rules.

Database Session Stats

Session

1. Connected users list

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
/

2. 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
/

3. 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
/

4.User information including OS process ID

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)
/

5. Show 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')
/

6. 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'
/

7. Show 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;
/

8. List 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
/

9.  List open cursors per user

a, 
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
/
b,
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
/

Database Users and Roles Stats

Users:

1. List of Users

set pages 999 lines 100
col username	format a20
col status	format a8
col tablespace	format a20
col temp_ts	format a20
select	username
,	account_status status
,	created
,	default_tablespace tablespace
,	temporary_tablespace temp_ts
from	dba_users
order	by username
/

Roles:

1. Find a role

select	*
from 	dba_roles
where	role like '&role'
/

2. List roles granted to a user

select	grantee
,	granted_role
,	admin_option
from	dba_role_privs
where	grantee like upper('&username')
/

3.  List system privileges granted to a role

select	privilege
,	admin_option
from	role_sys_privs
where	role like '&role'
/

4. List table privileges granted to a role

select	owner || '.' || table_name "TABLE"
,	column_name
,	privilege
,	grantable
from	role_tab_privs
where	role like '&role'
/

Links:

Database General Stats

1. Startup Time

Startup information is stored in v$instance.startup_time, and uptime can be easily computed as sysdate – startup_time.

select to_char(startup_time, 'HH24:MI DD-MON-YY') "Startup time"
from v$instance
/

Sample Output:
Startup time
---------------
03:05 09-FEB-06

Another query which outputs the time period since the database was started:
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;

2. Database Products and version Notes

select BANNER product_versions
from v$version;

Sample Output:

PRODUCT_VERSIONS
-----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

3. Database Information

  • Database Name – Name of the database
  • Created – Date/time the database was created
  • Log Mode – Archive log mode: NOARCHIVELOG or ARCHIVELOG
select 	NAME,
	CREATED,
	LOG_MODE,
	CHECKPOINT_CHANGE#,
	ARCHIVE_CHANGE#
from 	v$database;

4. Database Size

SQL query to check how large your database is.

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
/

Retrieve size information in terms of file name and tablespaces:

select 	FILE_NAME,
	d.TABLESPACE_NAME,
	d.BYTES datafile_size,
	nvl(sum(e.BYTES),0) bytes_used,
	round(nvl(sum(e.BYTES),0) / (d.BYTES), 4) * 100 percent_used,
	d.BYTES - nvl(sum(e.BYTES),0) bytes_free
from 	DBA_EXTENTS e,
	DBA_DATA_FILES d
where  	d.FILE_ID = e.FILE_ID (+)
group  	by FILE_NAME,d.TABLESPACE_NAME, d.FILE_ID, d.BYTES, STATUS
order  	by d.TABLESPACE_NAME,d.FILE_ID;

5.  Files and Locations

  • Filename – Name of the file(s)
  • Location – Location of the file(s)
select 	'Archived Log Directory' "Filename",
	value "Location"
from 	v$parameter
where 	name = 'log_archive_dest'
UNION
select 	'Control Files' "Filename",
	value "Location"
from 	v$parameter
where  	name = 'control_files'
UNION
select 	'Datafile' "Filename",
	name "Location"
from   	v$datafile
UNION
select 	'LogFile Member' "Filename",
	member "Location"
from  	v$logfile;

6.  Licence Information

select   SESSIONS_MAX,
         SESSIONS_WARNING,
         SESSIONS_CURRENT,
         SESSIONS_HIGHWATER,
         USERS_MAX
from     sys.v_$license;

7.  Database Options

select   PARAMETER,
         VALUE
from     sys.v_$option;
  • Option – Name of the option
  • Value – Value of the option

8.  INIT.ORA

a,

select	NAME,
	VALUE,
	ISDEFAULT,
	ISSES_MODIFIABLE,
	ISMODIFIED
from 	v$parameter
order  	by NAME;

b,  Show latent parameter changes

select name
,      value
from   v$parameter
where  ismodified != 'FALSE'
/

c,  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
/

9.  Distribution of Objects and Data

a, Which schemas are taking up all of the space.

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
/

b, 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
/

10.  Java Installed or Not

select 	count(*)
from 	all_objects
where 	object_type like '%JAVA%'
and 	owner = 'SYS'
/

If result is 9000, Java is installed.

11.  Character Set information

select * from nls_database_parameters
/

Links: