Friday, 18 May 2012

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

No comments:

Post a Comment