In sweet memories of my ever loving brother "kutty thambi " ARUN KUMAR

Useful Queries

How do you know the database name from normal user(connected) in the database:-
SQL> conn ram/ram;
Connected.
SQL> select name from v$database;
select name from v$database
                 *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> show parameter db_name
ORA-00942: table or view does not exist

SQL> select sys_context('userenv','db_name') from dual;

SYS_CONTEXT('USERENV','DB_NAME')
--------------------------------------------------------------------------------
arvind

SQL> select ora_database_name from dual;

ORA_DATABASE_NAME
--------------------------------------------------------------------------------
ARVIND.REGRESS.RDBMS.DEV.US.ORACLE.COM

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
ARVIND.REGRESS.RDBMS.DEV.US.ORACLE.COM

How to find the user objects created by him/her in his/her schema:-
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TEST                           TABLE
AA                             TABLE

SQL> select * from cat;

TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
TEST                           TABLE
AA                             TABLE

SQL> select * from user_catalog;

TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
TEST                           TABLE
AA                             TABLE

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
TEST
AA

SQL> select table_name from tabs;

TABLE_NAME
------------------------------
TEST
AA

SQL> select object_name,object_type from user_objects;

OBJECT_NAME     OBJECT_TYP
--------------- ----------
TEST            TABLE
SYS_C005421     INDEX
AA              TABLE

SQL> select segment_name from user_segments;

SEGMENT_NAME
-------------------------------------------------------------------
AA
SYS_C005421
TEST

SQL> select segment_name from user_extents;

SEGMENT_NAME
-------------------------------------------------------------------
TEST
AA
SYS_C005421



showing the table structure
SQL>  select dbms_metadata.get_ddl('TABLE','DEMO','RAJESH') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','DEMO','RAJESH')
--------------------------------------------------------------------------------
  CREATE TABLE "RAJESH"."DEMO"
   (    "NAME" VARCHAR2(9),
        "DBID" NUMBER
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" 
The following sql which show you the allocated, Free and Used space in each of the datafiles;
SELECT SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024 allocated_mb,
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))
used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;

FILE_NAME                                ALLOCATED_MB    USED_MB FREE_SPACE_MB
---------------------------------------- ------------ ---------- -------------
+DATA/rock/datafile/sysaux.262.725656507          250     240.25          9.75
+DATA/rock/datafile/system.271.725656495          480        471             9
+DATA/rock/datafile/undotbs1.268.7256565           25     5.9375       19.0625
+DATA/rock/datafile/undotbs2.266.7256572           25     8.3125       16.6875
+DATA/rock/datafile/users.263.725656523             5         .5           4.5 
Monitor long running operations using v$session_longops
SELECT SID, SERIAL#, opname, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) COMPLETE
FROM   V$SESSION_LONGOPS
WHERE
TOTALWORK != 0
AND    SOFAR != TOTALWORK
order by 1;
Note: the same query can be used to monitor RMAN backup status
SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
       ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
  AND OPNAME NOT LIKE '%aggregate%'
  AND TOTALWORK != 0
  AND SOFAR  != TOTALWORK
;

       SID    SERIAL# OPNAME                    SOFAR  TOTALWORK   COMPLETE
---------- ---------- -------------------- ---------- ---------- ----------
       604      13371 Table Scan                 6311      24498      25.76
       685       1586 Table Scan                 6333      24498      25.85  

how to find a session with high archive logs
You can query v$sess_io and v$session to findout the the session which is generating lot of redo. i.e. lot of block changes occuring.

SELECT s.sid, s.serial#, s.username, s.program, i.block_changes
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
ORDER BY 5 desc

Also query v$transaction and v$session to find out the session that is generating lot of undo information.
oracle processes
SQL> select nm, max(description) descript
  2  from
  3  (select regexp_replace( name,'[0-9a-z]','#') nm, description
  4  from v$bgprocess)
  5  group by nm
  6  order by nm;

NM       DESCRIPT
-------- ----------------------------------------------------------------
ARB#     ASM Rebalance 9
ARBA     ASM Rebalance 10
ARC#     Archival Process 9
ASMB     ASM Background
CJQ#     Job Queue Coordinator
CKPT     checkpoint
CTWR     Change Tracking Writer
DBW#     db writer process 9
DIAG     diagnosibility process
DMON     DG Broker Monitor Process
EMN#     Event Monitor Process 0

NM       DESCRIPT
-------- ----------------------------------------------------------------
FMON     File Mapping Monitor Process
GMON     diskgroup monitor
INSV     Data Guard Broker INstance SlaVe Process
LCK#     Lock Process 0
LGWR     Redo etc.
LMD#     global enqueue service daemon 0
LMON     global enqueue service monitor
LMS#     global cache service process 9
LNS#     Network Server 9
LSP#     Set Guard Standby Information for Logical Standby
MMAN     Memory Manager

NM       DESCRIPT
-------- ----------------------------------------------------------------
MMNL     Manageability Monitor Process 2
MMON     Manageability Monitor Process
MRP#     Managed Standby Recovery
NSV#     Data Guard Broker NetSlave Process 9
PMON     process cleanup
PSP#     process spawner 0
QMNC     AQ Coordinator
RBAL     ASM Rebalance master
RECO     distributed recovery
RSM#     Data Guard Broker Resource Guard Process 1
RVWR     Recovery Writer

NM       DESCRIPT
-------- ----------------------------------------------------------------
SMON     System Monitor Process

34 rows selected.


Query to find backupset sizes
SQL> select ctime "Date"
  2       , decode(backup_type, 'L', 'Archive Log', 'D', 'Full', 'Incremental') backup_type
  3       , bsize "Size MB"
  4  from (select trunc(bp.completion_time) ctime
  5      , backup_type
  6      , round(sum(bp.bytes/1024/1024),2) bsize
  7     from v$backup_set bs, v$backup_piece bp
  8     where bs.set_stamp = bp.set_stamp
  9     and bs.set_count  = bp.set_count
 10     and bp.status = 'A'
 11     group by trunc(bp.completion_time), backup_type)
 12  order by 1, 2;
 
Date      BACKUP_TYPE    Size MB
--------- ----------- ----------
03-JUL-10 Archive Log       7.31
03-JUL-10 Full             29.81
03-JUL-10 Incremental    2853.85
04-JUL-10 Archive Log       3.59
04-JUL-10 Full              7.45
04-JUL-10 Incremental       3.05



Startup time

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

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
/

Distribution of objects and data
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
/

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
/

Is java installed in the database?
This will return 9000'ish if it is...

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

Display character set information

select * from nls_database_parameters
/

Show all used features

select    name
,    detected_usages
from    dba_feature_usage_statistics
where     detected_usages > 0
/

Tablespace usage
 
set pages 999
col tablespace_name format a40
col "size MB" format 999,999,999
col "free MB" format 99,999,999
col "% Used" format 999
select  tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
, decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
, decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
               100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
 from  dba_data_files group by tablespace_name union all
 select  tablespace_name || '  **TEMP**'
 , sum(bytes)/1024/1024 used_mb
 from  dba_temp_files group by tablespace_name) tsu
, (select tablespace_name, sum(bytes)/1024/1024 free_mb
 from  dba_free_space group by tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
order by 4
/ 
free counters
 
Share/Bookmark