DBA_ERRORS

Thank you for visiting Spatial DBA - Oracle and ArcSDE.

Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.

====================================================================



Oracle 11gR1
DBA_ERRORS describes the current errors on all stored objects in the database. Its columns are the same as those in ALL_ERRORS.

Related Views

· ALL_ERRORS describes the current errors on the stored objects accessible to the current user.

· USER_ERRORS describes the current errors on the stored objects owned by the current user. This view does not display the OWNER column.

Column	Datatype	NULL	Description
OWNER	VARCHAR2(30)	NOT NULL	Owner of the object
NAME	VARCHAR2(30)	NOT NULL	Name of the object
TYPE	VARCHAR2(12)		Type of the object: · VIEW · PROCEDURE · FUNCTION · PACKAGE · PACKAGE BODY · TRIGGER · TYPE · TYPE BODY · LIBRARY · JAVA SOURCE · JAVA CLASS · DIMENSION
SEQUENCE	NUMBER	NOT NULL	Sequence number (for ordering purposes)
LINE	NUMBER	NOT NULL	Line number at which the error occurred
POSITION	NUMBER	NOT NULL	Position in the line at which the error occurred
TEXT	VARCHAR2(4000)	NOT NULL	Text of the error
ATTRIBUTE	VARCHAR2(9)		Indicates whether the error is an error (ERROR) or a warning (WARNING)
MESSAGE_NUMBER	NUMBER		Numeric error number (without any prefix)

Note:

1.

select * from DBA_ERRORS;

OWNER	NAME	TYPE	SEQUENCE	LINE	POSITION	TEXT	ATTRIBUTE	MESSAGE_ NUMBER
BUS	V_FEATURES	VIEW	1	0	0	ORA-00942: table or view does not exist	ERROR	0
PERFSTAT	SP920	PACKAGE BODY	12	198	6	PL/SQL: Statement ignored	ERROR	0
PERFSTAT	SP920	PACKAGE BODY	11	198	6	PLS-00306: wrong number or types of arguments in call to 'STAT_CHANGES'	ERROR	306
SYS	CWM2_OLAP_UTILITY	PACKAGE BODY	20	98	23	PL/SQL: SQL Statement ignored	ERROR	0

Oracle data dictionary views

DBA_ADVISOR_USAGE

Oracle 11gR1

DBA_ADVISOR_USAGE displays the usage information for each type of advisor in the database.

Column	Datatype	NULL	Description
ADVISOR_ID	NUMBER	NOT NULL	Type of the advisor
LAST_EXEC_TIME	DATE	NOT NULL	Date of the last execution
NUM_EXECS	NUMBER	NOT NULL	Cumulative number of executions

Note:

1.

select d.advisor_name,u.*

from DBA_ADVISOR_USAGE u, DBA_ADVISOR_DEFINITIONS d

where u.advisor_id = d.advisor_id;

ADVISOR_NAME	ADVISOR_ID	LAST_EXEC_TIME	NUM_EXECS
ADDM	1	2008-02-05 2:34:11 PM	20
SQL Access Advisor	2	2006-06-10 10:00:50 AM	0
Undo Advisor	3	2008-04-16 2:02:16 PM	1
SQL Tuning Advisor	4	2006-06-10 10:00:50 AM	0
Segment Advisor	5	2008-08-27 10:40:36 PM	2408
SQL Workload Manager	6	2006-06-10 10:00:50 AM	0
Tune MView	7	2006-06-10 10:00:50 AM	0

2. There are views named dba_feature_usage_statistics and dba_advisor_usage which record usage of the new features. These reports indicate that the user needs to purchase the license for the additional products. In other words, usage of features like AWR, ASH and ADDM is recorded in the database.

Oracle data dictionary views

DBA_ADVISOR_COMMANDS

Oracle 11gR1

DBA_ADVISOR_COMMANDS displays information about the commands used by all advisors in the database for specifying recommendation actions. In addition to the set of commands in the COMMAND column of V$SESSION, the following additional commands are defined:

· RUN ADVISOR

· CHECK EXECUTION PLAN

· ALTER PARAMETER

· ENABLE TRACE

Column	Datatype	NULL	Description
COMMAND_ID	NUMBER		Identifier of the command
COMMAND_NAME	VARCHAR2(64)		Name of the command

Note:

1.

select * from DBA_ADVISOR_COMMANDS;

COMMAND_ID	COMMAND_NAME
0	UNDEFINED
1	RUN SQL TUNING ADVISOR
2	CREATE INDEX
3	CREATE MATERIALIZED VIEW
4	CREATE MATERIALIZED VIEW LOG
5	NEVER CREATE INDEX
6	NEVER CREATE MATERIALIZED VIEW
7	NEVER CREATE MATERIALIZED VIEW LOG
8	NEVER DROP INDEX
9	NEVER DROP MATERIALIZED VIEW
10	NEVER MODIFY INDEX
11	SET INDEX MAXIMUM COUNT
12	SET INDEX SCHEMA
13	SET INDEX TABLESPACE
14	SET MATERIALIZED VIEW SCHEMA
15	SET MATERIALIZED VIEW TABLESPACE
16	SET STORAGE SIZE
17	DROP INDEX
18	DROP MATERIALIZED VIEW
19	DROP MATERIALIZED VIEW LOG
20	RETAIN INDEX
21	RETAIN MATERIALIZED VIEW
22	RETAIN MATERIALIZED VIEW LOG
23	CREATE SUB MATERIALIZED VIEW
24	DROP SUB MATERIALIZED VIEW
25	CREATE REWRITE EQUIVALENCE
26	DROP REWRITE EQUIVALENCE
27	ALTER MATERIALIZED VIEW LOG
28	GATHER TABLE STATISTICS
29	GATHER INDEX STATISTICS
30	SET UNDO_RETENTION
31	SET UNDO TABLESPACE SIZE
32	ACCEPT SQL PROFILE
33	REWRITE QUERY
34	RUN SEGMENT ADVISOR
35	ALTER PARAMETER
36	SHRINK SPACE
37	REORG OBJECT

Oracle data dictionary views

DBA_ADVISOR_DEFINITIONS

Oracle 11gR1

DBA_ADVISOR_DEFINITIONS displays the properties of all advisors in the database. The view contains one row for each task, representing the current state of the task as well as execution-specific data such as progress monitoring and completion status.

Column	Datatype	NULL	Description
ADVISOR_ID	NUMBER	NOT NULL	Unique identifier for the advisor
ADVISOR_NAME	VARCHAR2(30)	NOT NULL	Name of the advisor
PROPERTY	NUMBER	NOT NULL	Properties: · Bit 0: - Indicates whether the advisor runs in COMPREHENSIVE mode (1) or not (0) · Bit 1: - Indicates whether the advisor runs in LIMITED mode (1) or not (0) · Bit 2: - Indicates whether the advisor is resumable (1) or not (0) · Bit 3: - Indicates whether the advisor accepts user directives (1) or not (0)

Note:

1.

select * from DBA_ADVISOR_DEFINITIONS;

ADVISOR_ID	ADVISOR_NAME	PROPERTY
1	ADDM	1: 00000001
2	SQL Access Advisor	15: 00001111
3	Undo Advisor	1: 00000001
4	SQL Tuning Advisor	7: 00000111
5	Segment Advisor	3: 00000011
6	SQL Workload Manager	0: 00000000
7	Tune MView	31:00011111

Oracle data dictionary views