Oracle Common Commands

Oracle Common Commands

• Querying Oracle Versions

Select *  from V$version;

• Compare Command decode

Select decode (sign –1from– takes a smaller value

• View Table Size

Select segment_name,       tablespace_name,       bytes B,       /1024x768  ,       / 1024x768 / 1024x768 MB    from  where='TABLE';

• viewing deadlocks and unlocking

Query deadlock:

SELECT l.session_id Sid,       s.serial#,       l.locked_mode,       l.oracle_username,       l.os_user_name,       S.machine,       s.terminal,       o. object_name ,       s.logon_time  fromWHERE L.object_id= o.object_id   and=ORDER by Sid, s.serial#;

Unlock:

Alter Kill ' sid,serial# '; --combined with the above query deadlock results use

• Query database is using user information

Select A. " Machine ", A." Program ", A." Prev_exec_start ", a.terminal  fromwhere='  Hrpdev';

• View table space Names and sizes

Select round (sum/ (1024x768*1024x7680) ts_size  from  where=Group by T.tablespace_name;

• View table space physical name and size

Select Tablespace_name,        file_id ,        file_name ,        round / (1024x768*0) total_space    from  order by Tablespace_name;

• To view the rollback segment name and size

Select segment_name,       tablespace_name,       r.status,       /1024x768) initialextent,        /1024x768) nextextent,       max_extents,       v.curext curextent  from   where = V.usn (+ ORDER by Segment  _name;

• View Control files

Select  from

• viewing log files

Select  from

• To view the usage of a tablespace

Select sum(bytes)/(1024x768 * 1024x768) asFree_space, Tablespace_name fromDba_free_spaceGroup  byTablespace_name;SelectA.tablespace_name, a.bytes total, b.bytes used, c.bytes free, (b.bytes*  -)/A.bytes "%used ", (C.bytes*  -)/A.bytes "%Free " fromSys.sm$ts_avail A, sys.sm$ts_used B, Sys.sm$ts_free cwhereA.tablespace_name=B.tablespace_name andA.tablespace_name=C.tablespace_name;

• View database objects

Select Count (*Count#  fromgroup by owner, Object_type, Status

Oracle Common Commands