SQL Zone is brought to you in partnership with:

My name is Zemian Deng, and I am a Senior Application Engineer working at Oracle for the Enterprise Knowledge Management product. NOTE: The views expressed on my blog and social network are my own and do not necessarily reflect the views of my employer. Zemian is a DZone MVB and is not an employee of DZone and has posted 79 posts at DZone. You can read more from them at their website. View Full User Profile

Inspecting Your Oracle Database

12.11.2012
| 1598 views |
  • submit to reddit

 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
desc HR.EMPLOYEES;

-- 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 Zemian Deng, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)