{{ !articles[0].partner.isSponsoringArticle ? "Platinum" : "Portal" }} Partner
java,sql,persistence,tips and tricks

Inspecting Your Oracle Database

 Some quick tips on how to inspect what you have in your Oracle database. 

-- show all schemas
select distinct owner from dba_segments where owner in
     (select username from dba_users where default_tablespace not in ('SYSTEM','SYSAUX'));

-- show all tables from a schema/owner
select * from all_tables where owner = 'HR';

-- show table description

-- show all users
select * from all_users;
select username, * from dba_users;

-- See who is taking up a DB lock
select c.*, b.* from v$lock a, dba_locks b, v$session c 
  where a.id1 = b.lock_id1 and b.session_id = c.sid

-- See internal SQL id
select * from v$sql

-- See Oracle latches:
select * from v$latch

-- See Library Cache of SGA (System Global Area) like buffer cache size:
select * from v$sgastat

-- See tx locks (TX) and DML locks (TM):
select * from v$lock where type in ('TX', 'TM')
select * from dba_locks where lock_type in ('Transaction', 'DML')

Published at DZone with permission of {{ articles[0].authors[0].realName }}, DZone MVB. (source)

Opinions expressed by DZone contributors are their own.

{{ tag }}, {{tag}},

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}
{{ parent.authors[0].realName || parent.author}}

{{ parent.authors[0].tagline || parent.tagline }}

{{ parent.views }} ViewsClicks