Oracle Database 11g Data Dictionary Reports – Part A – Database, Database Parameters and Sessions Reports

Data Dictionary is one of the most important parts of oracle database. The data dictionary is a read-only set of tables which provides information about database. Information and knowledge about the database and data is a key asset for BI Architect/Developer.

This is an attempt to provide introduction to the oracle data dictionary and set of standard data dictionary reports (with SQL syntax) which might be useful for better understanding of the data/database.

I would also like to write about the option to provide a subject area and set of reports within OBIEE 11g around the data dictionary reports in my future blogs.

Oracle Data Dictionary

A data dictionary tables and views which are stored in database’s SYSTEM tablespace contains

·         The definitions of all schema objects in the database (tables, views, indexes, clusters, synonyms, sequences, procedures, functions, packages, triggers, and so on)

·         How much space has been allocated for, and is currently used by, the schema objects

·         Default values for columns

·         Integrity constraint information

·         The names of Oracle Database users

·         Privileges and roles each user has been granted

·         Auditing information, such as who has accessed or updated various schema objects

·         Other general database information

Data Dictionary Reports

The data dictionary reports about the database and its objects can be grouped into the following categories.

·         Database Reports

·         Database Administration Reports

·         Table reports

·         PL/SQL reports

·         Security Reports

·         XML reports

·         Jobs Reports

·         Streams Reports

·         All Objects Reports

·         Data Dictionary Reports

·         OLAP reports

This blog is an attempt to provide insight about the database parameters and session reports build by using database dictionary. User can create many more reports as and when needed to improve the understanding about overall database/data.

Note – You do need the right access to execute these reports provided below.

Database Reports

National Language Support Parameters

SQL Script

Select parameter "Parameter", value "Value" from v$nls_parameters order by 1

Standard Output

National Language Support Parameters

SQL Script

Select banner "Your_Database_Settings" from v$version

Standard Output

Database Administration Reports

Database Parameters

All Parameters

SQL Script

SELECT name "Paramater_Name",

  DECODE(type, 1, 'Boolean', 2, 'String', 3, 'Integer', 4, 'Parameter file', 5, 'Reserved', 6, 'Big Integer') type,

  value "Value",

  isdefault "Default",

  isses_modifiable "Session_Modifiable",

  issys_modifiable "System_Modifiable",

  description "Description"

FROM v$parameter

WHERE (:PARAMETER_NAME                      IS NULL

OR instr(lower(name),lower(:PARAMETER_NAME)) > 0)

AND SUBSTR(name,1,2)                        != '__'

ORDER BY name

Standard Output

Non- Default Parameters

SQL Script

SELECT name "Paramater_Name",

  DECODE(type, 1, 'Boolean', 2, 'String', 3, 'Integer', 4, 'Parameter file', 5, 'Reserved', 6, 'Big Integer') type,

  value "Value",

  isses_modifiable "Session_Modifiable",

  issys_modifiable "System_Modifiable",

  description "Description"

FROM v$parameter

WHERE (:PARAMETER_NAME                      IS NULL

OR instr(lower(name),lower(:PARAMETER_NAME)) > 0)

AND isdefault = 'FALSE'

AND SUBSTR(name,1,2)!= '__'

ORDER BY name

Standard Output

Locks by User

SQL Script

SELECT name "Paramater_Name",

  DECODE(type, 1, 'Boolean', 2, 'String', 3, 'Integer', 4, 'Parameter file', 5,

SELECT p.username username ,

  p.pid pid ,

  s.sid sid ,

  s.serial# serial ,

  p.spid spid ,

  s.username ora ,

  DECODE(l2.type, 'TX','TRANSACTION ROW-LEVEL' , 'RT','REDO-LOG' , 'TS','TEMPORARY SEGMENT ' , 'TD','TABLE LOCK' , 'TM','ROW LOCK' , l2.type ) vlock,

  DECODE(l2.type, 'TX','DML LOCK' , 'RT','REDO LOG' , 'TS','TEMPORARY SEGMENT' , 'TD',DECODE(l2.lmode+l2.request , 4,'PARSE '

  || u.name

  || '.'

  || o.name , 6,'DDL' , l2.lmode+l2.request), 'TM','DML '

  || u.name

  || '.'

  || o.name , l2.type ) type ,

  DECODE(l2.lmode+l2.request , 2 ,'RS' , 3 ,'RX' , 4 ,'S' , 5 ,'SRX' , 6 ,'X' , l2.lmode+l2.request ) lmode ,

  DECODE(l2.request , 0,NULL , 'WAIT' ) wait

FROM v$process p ,

  v$_lock l1,

  v$lock l2,

  v$resource r ,

  sys.obj$ o ,

  sys.user$ u ,

  v$session s

WHERE s.paddr = p.addr

AND s.saddr   = l1.saddr

AND l1.raddr  = r.addr

AND l2.addr   = l1.laddr

AND l2.type  <> 'MR'

AND r.id1     = o.obj# (+)

AND o.owner#  = u.user# (+)

  --AND  u.name = 'GME'

AND (:USER_NAME IS NULL

OR s.username LIKE upper(:USER_NAME))

ORDER BY p.username,

  p.pid,

  p.spid,

  ora,

  DECODE(l2.type, 'TX','TRANSACTION ROW-LEVEL' , 'RT','REDO-LOG' , 'TS','TEMPORARY SEGMENT ' , 'TD','TABLE LOCK' , 'TM','ROW LOCK' , l2.type )

Standard Output

Sessions

Active Session Count

SQL Script

Select inst_id, count(case when status='ACTIVE' then 1 else null end) active,

count(*) total from gv$session where type !='BACKGROUND' GROUP by inst_id

Standard Output

Active Sessions

SQL Script

SELECT inst_id,

  program,

  module,

  event,

  SQL_ID,

  machine,

  lpad( TO_CHAR( TRUNC(24   *(sysdate-s.logon_time)) )

  || TO_CHAR(TRUNC(sysdate) + (sysdate-s.logon_time) , ':MI:SS' ) , 10, ' ') AS UP_time

FROM gv$session s

WHERE type! ='BACKGROUND'

AND status  ='ACTIVE'

AND sql_id IS NOT NULL

Standard Output

Background Sessions

SQL Script

WITH vs AS

  (SELECT rownum rnum,sid,status,username,last_call_et,command,machine,osuser,module,action,resource_consumer_group,client_info,client_identifier,type,terminal FROM v$session WHERE type = 'BACKGROUND')

SELECT vs.sid,

  vs.username,

  CASE

    WHEN vs.status = 'ACTIVE'

    THEN last_call_et

    ELSE NULL

  END seconds_in_wait,

  DECODE(vs.command, 0,NULL, 1,'CRE TAB', 2,'INSERT', 3,'SELECT', 4,'CRE CLUSTER', 5,'ALT CLUSTER', 6,'UPDATE', 7,'DELETE', 8,'DRP CLUSTER', 9,'CRE INDEX', 10,'DROP INDEX', 11,'ALT INDEX', 12,'DROP TABLE', 13,'CRE SEQ', 14,'ALT SEQ', 15,'ALT TABLE', 16,'DROP SEQ', 17,'GRANT', 18,'REVOKE', 19,'CRE SYN', 20,'DROP SYN', 21,'CRE VIEW', 22,'DROP VIEW', 23,'VAL INDEX', 24,'CRE PROC', 25,'ALT PROC', 26,'LOCK TABLE', 28,'RENAME', 29,'COMMENT', 30,'AUDIT', 31,'NOAUDIT', 32,'CRE DBLINK', 33,'DROP DBLINK', 34,'CRE DB', 35,'ALTER DB', 36,'CRE RBS', 37,'ALT RBS', 38,'DROP RBS', 39,'CRE TBLSPC', 40,'ALT TBLSPC', 41,'DROP TBLSPC', 42,'ALT SESSION', 43,'ALT USER', 44,'COMMIT', 45,'ROLLBACK', 46,'SAVEPOINT', 47,'PL/SQL EXEC', 48,'SET XACTN', 49,'SWITCH LOG', 50,'EXPLAIN', 51,'CRE USER', 52,'CRE ROLE', 53,'DROP USER', 54,'DROP ROLE', 55,'SET ROLE', 56,'CRE SCHEMA', 57,'CRE CTLFILE', 58,'ALTER TRACING', 59,'CRE TRIGGER', 60,'ALT TRIGGER', 61,'DRP TRIGGER', 62,'ANALYZE TAB', 63,'ANALYZE IX', 64,

  'ANALYZE CLUS', 65,'CRE PROFILE', 66,'DRP PROFILE', 67,'ALT PROFILE', 68,'DRP PROC', 69,'DRP PROC', 70,'ALT RESOURCE', 71,'CRE SNPLOG', 72,'ALT SNPLOG', 73,'DROP SNPLOG', 74,'CREATE SNAP', 75,'ALT SNAP', 76,'DROP SNAP', 79,'ALTER ROLE', 85,'TRUNC TAB', 86,'TRUNC CLUST', 88,'ALT VIEW', 91,'CRE FUNC', 92,'ALT FUNC', 93,'DROP FUNC', 94,'CRE PKG', 95,'ALT PKG', 96,'DROP PKG', 97,'CRE PKG BODY', 98,'ALT PKG BODY', 99,'DRP PKG BODY', TO_CHAR(vs.command)) command,

  vs.machine "Machine",

  vs.osuser,

  lower(vs.status) status,

  vs.module "Module",

  vs.action "Action",

  vs.resource_consumer_group,

  vs.client_info,

  vs.client_identifier

FROM vs

ORDER BY 1

Standard Output

Inactive Sessions

SQL Script

WITH vs AS(SELECT rownum rnum,sid,status,username,last_call_et,command,machine,osuser, module,action,resource_consumer_group,client_info,client_identifier,type,terminal FROM v$session WHERE status != 'ACTIVE')SELECT vs.sid, vs.username,CASE WHEN vs.status = 'ACTIVE' THEN last_call_et ELSE NULL END seconds_in_wait, DECODE(vs.command, 0,NULL, 1,'CRE TAB', 2,'INSERT', 3,'SELECT', 4,'CRE CLUSTER', 5,'ALT CLUSTER', 6,'UPDATE', 7,'DELETE', 8,'DRP CLUSTER', 9,'CRE INDEX', 10,'DROP INDEX', 11,'ALT INDEX', 12,'DROP TABLE', 13,'CRE SEQ', 14,'ALT SEQ', 15,'ALT TABLE', 16,'DROP SEQ', 17,'GRANT', 18,'REVOKE', 19,'CRE SYN', 20,'DROP SYN', 21,'CRE VIEW', 22,'DROP VIEW', 23,'VAL INDEX', 24,'CRE PROC', 25,'ALT PROC', 26,'LOCK TABLE', 28,'RENAME', 29,'COMMENT', 30,'AUDIT', 31,'NOAUDIT', 32,'CRE DBLINK', 33,'DROP DBLINK', 34,'CRE DB', 35,'ALTER DB', 36,'CRE RBS', 37,'ALT RBS', 38,'DROP RBS', 39,'CRE TBLSPC', 40,'ALT TBLSPC', 41,'DROP TBLSPC', 42,'ALT SESSION', 43,'ALT USER', 44,'COMMIT', 45,'ROLLBACK', 46,'SAVEPOINT', 47,'PL/SQL EXEC', 48,'SET XACTN', 49,'SWITCH LOG', 50,'EXPLAIN', 51,'CRE USER', 52,'CRE ROLE', 53,'DROP USER', 54,'DROP ROLE', 55,'SET ROLE', 56,'CRE SCHEMA', 57,'CRE CTLFILE', 58,'ALTER TRACING', 59,'CRE TRIGGER', 60,'ALT TRIGGER', 61,'DRP TRIGGER', 62,'ANALYZE TAB', 63,'ANALYZE IX', 64,'ANALYZE CLUS', 65,'CRE PROFILE', 66,'DRP PROFILE', 67,'ALT PROFILE', 68,'DRP PROC', 69,'DRP PROC', 70,'ALT RESOURCE', 71,'CRE SNPLOG', 72,'ALT SNPLOG', 73,'DROP SNPLOG', 74,'CREATE SNAP', 75,'ALT SNAP', 76,'DROP SNAP', 79,'ALTER ROLE', 85,'TRUNC TAB', 86,'TRUNC CLUST', 88,'ALT VIEW', 91,'CRE FUNC', 92,'ALT FUNC', 93,'DROP FUNC', 94,'CRE PKG', 95,'ALT PKG', 96,'DROP PKG', 97,'CRE PKG BODY', 98,'ALT PKG BODY', 99,'DRP PKG BODY', TO_CHAR(vs.command)) command, lower(vs.machine) machine, vs.machine machine2, vs.osuser,lower(vs.status) status,vs.module "Module",vs.action "Action", vs.resource_consumer_group,vs.client_info, vs.client_identifier FROM vs WHERE vs.USERNAME      IS NOT NULL AND NVL(vs.osuser,'x') <> 'SYSTEM' AND vs.type            <> 'BACKGROUND' ORDER BY 1

Standard Output

Session Counts by OS User

SQL Script

SELECT osuser "OS_User", COUNT(*) "Count" FROM v$session GROUP BY osuser ORDER BY 1

Standard Output

Session Counts by Status

SQL Script

SELECT status "Status",COUNT(DISTINCT osuser) "Distinct_OS_Users", type "Type", COUNT(*) "Count" FROM v$session GROUP BY status,type ORDER BY 1

Standard Output

Sessions

SQL Script

WITH vs AS (SELECT rownum rnum,sid,serial#,status,username,last_call_et,command, machine,osuser,module,action,resource_consumer_group,client_info,client_identifier,type,terminal FROM v$session)SELECT vs.sid ,serial# serial,vs.username "Username",CASE WHEN vs.status = 'ACTIVE'THEN last_call_et ELSE NULL END "Seconds in Wait",DECODE(vs.command, 0,NULL, 1,'CRE TAB', 2,'INSERT', 3,'SELECT', 4,'CRE CLUSTER', 5,'ALT CLUSTER', 6,'UPDATE', 7,'DELETE', 8,'DRP CLUSTER', 9,'CRE INDEX', 10,'DROP INDEX', 11,'ALT INDEX', 12,'DROP TABLE', 13,'CRE SEQ', 14,'ALT SEQ', 15,'ALT TABLE', 16,'DROP SEQ', 17,'GRANT', 18,'REVOKE', 19,'CRE SYN', 20,'DROP SYN', 21,'CRE VIEW', 22,'DROP VIEW', 23,'VAL INDEX', 24,'CRE PROC', 25,'ALT PROC', 26,'LOCK TABLE', 28,'RENAME', 29,'COMMENT', 30,'AUDIT', 31,'NOAUDIT', 32,'CRE DBLINK', 33,'DROP DBLINK', 34,'CRE DB', 35,'ALTER DB', 36,'CRE RBS', 37,'ALT RBS', 38,'DROP RBS', 39,'CRE TBLSPC', 40,'ALT TBLSPC', 41,'DROP TBLSPC', 42,'ALT SESSION', 43,'ALT USER', 44,'COMMIT', 45,'ROLLBACK', 46,'SAVEPOINT', 47,'PL/SQL EXEC', 48,'SET XACTN', 49,'SWITCH LOG', 50,'EXPLAIN', 51,'CRE USER', 52,'CRE ROLE', 53,'DROP USER', 54,'DROP ROLE', 55,'SET ROLE', 56,'CRE SCHEMA', 57,'CRE CTLFILE', 58,'ALTER TRACING', 59,'CRE TRIGGER', 60,'ALT TRIGGER', 61,'DRP TRIGGER', 62,'ANALYZE TAB', 63,'ANALYZE IX', 64,'ANALYZE CLUS', 65,'CRE PROFILE', 66,'DRP PROFILE', 67,'ALT PROFILE', 68,'DRP PROC', 69,'DRP PROC', 70,'ALT RESOURCE', 71,'CRE SNPLOG', 72,'ALT SNPLOG', 73,'DROP SNPLOG', 74,'CREATE SNAP', 75,'ALT SNAP', 76,'DROP SNAP', 79,'ALTER ROLE', 79,'ALTER ROLE', 85,'TRUNC TAB', 86,'TRUNC CLUST', 88,'ALT VIEW', 91,'CRE FUNC', 92,'ALT FUNC', 93,'DROP FUNC', 94,'CRE PKG', 95,'ALT PKG', 96,'DROP PKG', 97,'CRE PKG BODY', 98,'ALT PKG BODY', 99,'DRP PKG BODY', TO_CHAR(vs.command)) "Command", vs.machine "Machine", vs.osuser "OS User",lower(vs.status) "Status", vs.module "Module",vs.action "Action", vs.resource_consumer_group, vs.client_info, vs.client_identifier FROM vs WHERE vs.USERNAME IS NOT NULL AND NVL(vs.osuser,'x') <> 'SYSTEM' AND vs.type            <>'BACKGROUND' ORDER BY 1

Standard Output

Sessions by Module

SQL Script

SELECT NVL(module,'Unidentified') "Module",COUNT(*) "Session_Count" FROM v$session GROUP BY NVL(module,'Unidentified') ORDER BY 1

Standard Output

Sessions by Username

SQL Script

SELECT NVL(username,'Unidentified') "Username", COUNT(*) "Session_Count" FROM v$session GROUP BY NVL(username,'Unidentified') ORDER BY 1

Standard Output

System Session

SQL Script

SELECT status "Status",COUNT(DISTINCT osuser) "Distinct_OS_Users", type "Type", COUNT(*) "Count" FROM v$session GROUP BY status,type ORDER BY 1 WITH vs AS(SELECT rownum rnum,sid,status,username,last_call_et,command,machine,osuser,module,action, resource_consumer_group,client_info,client_identifier,type,terminal FROM v$session WHERE osuser = 'SYSTEM') SELECT vs.sid, vs.username, CASE WHEN vs.status = 'ACTIVE' THEN last_call_et ELSE NULL END seconds_in_wait, DECODE(vs.command, 0,NULL, 1,'CRE TAB', 2,'INSERT', 3,'SELECT', 4,'CRE CLUSTER', 5,'ALT CLUSTER', 6,'UPDATE', 7,'DELETE', 8,'DRP CLUSTER', 9,'CRE INDEX', 10,'DROP INDEX', 11,'ALT INDEX', 12,'DROP TABLE', 13,'CRE SEQ', 14,'ALT SEQ', 15,'ALT TABLE', 16,'DROP SEQ', 17,'GRANT', 18,'REVOKE', 19,'CRE SYN', 20,'DROP SYN', 21,'CRE VIEW', 22,'DROP VIEW', 23,'VAL INDEX', 24,'CRE PROC', 25,'ALT PROC', 26,'LOCK TABLE', 28,'RENAME', 29,'COMMENT', 30,'AUDIT', 31,'NOAUDIT', 32,'CRE DBLINK', 33,'DROP DBLINK', 34,'CRE DB', 35,'ALTER DB', 36,'CRE RBS', 37,'ALT RBS', 38,'DROP RBS', 39,'CRE TBLSPC', 40,'ALT TBLSPC', 41,'DROP TBLSPC', 42,'ALT SESSION', 43,'ALT USER', 44,'COMMIT', 45,'ROLLBACK', 46,'SAVEPOINT', 47,'PL/SQL EXEC', 48,'SET XACTN', 49,'SWITCH LOG', 50,'EXPLAIN', 51,'CRE USER', 52,'CRE ROLE', 53,'DROP USER', 54,'DROP ROLE', 55,'SET ROLE', 56,'CRE SCHEMA', 57,'CRE CTLFILE', 58,'ALTER TRACING', 59,'CRE TRIGGER', 60,'ALT TRIGGER', 61,'DRP TRIGGER', 62,'ANALYZE TAB', 63,'ANALYZE IX', 64,'ANALYZE CLUS', 65,'CRE PROFILE', 66,'DRP PROFILE', 67,'ALT PROFILE', 68,'DRP PROC', 69,'DRP PROC', 70,'ALT RESOURCE', 71,'CRE SNPLOG', 72,'ALT SNPLOG', 73,'DROP SNPLOG', 74,'CREATE SNAP', 75,'ALT SNAP', 76,'DROP SNAP', 79,'ALTER ROLE', 85,'TRUNC TAB', 86,'TRUNC CLUST', 88,'ALT VIEW', 91,'CRE FUNC', 92,'ALT FUNC', 93,'DROP FUNC', 94,'CRE PKG', 95,'ALT PKG', 96,'DROP PKG', 97,'CRE PKG BODY', 98,'ALT PKG BODY', 99,'DRP PKG BODY', TO_CHAR(vs.command)) command,vs.machine "Machine", vs.osuser,lower(vs.status) status, vs.module "Module",vs.action "Action",vs.resource_consumer_group, vs.client_info, vs.client_identifier FROM vs ORDER BY 1