Thursday, December 11, 2008

DBA_REGISTRY

Thank you for visiting Spatial DBA - Oracle and ArcSDE.

Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.
==================================================================
DBA_REGISTRY displays information about the components loaded into the database.

Related View
USER_REGISTRY displays information about the components loaded into the database that are owned by the current user.
Column
Datatype
NULL
Description
COMP_ID
VARCHAR2(30)
NOT NULL
Component identifier
COMP_NAME
VARCHAR2(255)

Component name
VERSION
VARCHAR2(30)

Component version loaded
STATUS
VARCHAR2(11)

Component status:
·         INVALID
·         VALID
·         LOADING
·         LOADED
·         UPGRADING
·         UPGRADED
·         DOWNGRADING
·         DOWNGRADED
·         REMOVING
·         REMOVED
MODIFIED
VARCHAR2(20)

Time when the component was last modified
NAMESPACE
VARCHAR2(30)
NOT NULL
Component namespace
CONTROL
VARCHAR2(30)
NOT NULL
User that created the component entry
SCHEMA
VARCHAR2(30)
NOT NULL
User that contains the objects for the component
PROCEDURE
VARCHAR2(61)

Validation procedure
STARTUP
VARCHAR2(8)

Indicates whether the component requires a startup after the upgrade (REQUIRED) or not
PARENT_ID
VARCHAR2(30)

Parent component identifier

Note:
1.       scripts using DBA_REGISTRY
--script to determine the status of the Oracle JVM and required packages

select comp_name, version, status
from   dba_registry;

select owner, status, count(*)
from   all_objects
where  object_type like '%JAVA%' group by owner, status;

select owner, object_type, count(*)
from   all_objects
where  object_type like '%JAVA%' and status <> 'VALID'
group by owner, object_type;

select owner, status, object_type, object_name
from   all_objects
where  object_name like'%DBMS_JAVA%';

select owner, status, object_type, object_name
from   all_objects
where  object_name like'%INITJVMAUX%';

select *
from v$sgastat
where POOL = 'java pool' or NAME = 'free memory';

show parameter pool_size

show parameter sga

select owner, object_type, status, dbms_java.longname(object_name)
from all_objects
where object_type like '%JAVA%' and status <> 'VALID';


2.       Example in Oracle Enterprise Edition
select comp_id, comp_name, version, status, schema, procedure
from   DBA_REGISTRY;

COMP_ID
COMP_NAME
VERSION
STATUS
SCHEMA
PROCEDURE
CATALOG
Oracle Database Catalog Views
10.2.0.2.0
VALID
SYS
DBMS_REGISTRY_SYS.VALIDATE_CATALOG
CATPROC
Oracle Database Packages and Types
10.2.0.2.0
VALID
SYS
DBMS_REGISTRY_SYS.VALIDATE_CATPROC
OWM
Oracle Workspace Manager
10.2.0.1.0
VALID
WMSYS
VALIDATE_OWM
JAVAVM
JServer JAVA Virtual Machine
10.2.0.2.0
VALID
SYS
INITJVMAUX.VALIDATE_JAVAVM
XML
Oracle XDK
10.2.0.2.0
VALID
SYS
XMLVALIDATE
CATJAVA
Oracle Database Java Packages
10.2.0.2.0
VALID
SYS
DBMS_REGISTRY_SYS.VALIDATE_CATJAVA
ORDIM
Oracle interMedia
10.2.0.2.0
VALID
ORDSYS
VALIDATE_ORDIM
SDO
Spatial
10.2.0.2.0
VALID
MDSYS
VALIDATE_SDO
CONTEXT
Oracle Text
10.2.0.2.0
VALID
CTXSYS
VALIDATE_CONTEXT
XDB
Oracle XML Database
10.2.0.2.0
VALID
XDB
DBMS_REGXDB.VALIDATEXDB
WK
Oracle Ultra Search
10.2.0.2.0
VALID
WKSYS
VALIDATE_WK
APS
OLAP Analytic Workspace
10.2.0.2.0
VALID
SYS
APS_VALIDATE
XOQ
Oracle OLAP API
10.2.0.2.0
VALID
SYS
XOQ_VALIDATE

3. Oracle Standard Edition: For components that are not supported by the Standard Edition, i.e., Oracle Data Mining, Oracle OLAP Catalog, Oracle OLAP Analytic Workspace, Oracle OLAP API, Oracle Spatial, the STATUS in the DBA_REGISTRY view is set to OPTION OFF.
select comp_id, comp_name, version, status, schema, procedure
from   DBA_REGISTRY;

COMP_ID
COMP_NAME
VERSION
STATUS
SCHEMA
PROCEDURE
CATALOG
Oracle Database Catalog Views
10.2.0.2.0
VALID
SYS
DBMS_REGISTRY_SYS.VALIDATE_CATALOG
CATPROC
Oracle Database Packages and Types
10.2.0.2.0
VALID
SYS
DBMS_REGISTRY_SYS.VALIDATE_CATPROC
OWM
Oracle Workspace Manager
10.2.0.1.0
VALID
WMSYS
VALIDATE_OWM
JAVAVM
JServer JAVA Virtual Machine
10.2.0.2.0
VALID
SYS
INITJVMAUX.VALIDATE_JAVAVM
XML
Oracle XDK
10.2.0.2.0
VALID
SYS
XMLVALIDATE
CATJAVA
Oracle Database Java Packages
10.2.0.2.0
VALID
SYS
DBMS_REGISTRY_SYS.VALIDATE_CATJAVA
ORDIM
Oracle interMedia
10.2.0.2.0
VALID
ORDSYS
VALIDATE_ORDIM
SDO
Spatial
9.2.0.7.0
OPTION OFF
MDSYS

CONTEXT
Oracle Text
10.2.0.2.0
VALID
CTXSYS
VALIDATE_CONTEXT
XDB
Oracle XML Database
10.2.0.2.0
VALID
XDB
DBMS_REGXDB.VALIDATEXDB

Oracle data dictionary views

Oracle dynamic performance views
Last updated: 2009-10-22 Thursday

Posted by Admin at 12/11/2008 03:46:00 PM

Labels: ,

Wednesday, December 10, 2008

DBA_REFS

DBA_REFS describes the REF columns and REF attributes in object type columns of all the objects in the database. Its columns are the same as those in "ALL_REFS".

Related Views

· ALL_REFS describes the REF columns and REF attributes in object type columns accessible to the current user.

· USER_REFS describes the REF columns and REF attributes in object type columns 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 table

TABLE_NAME

VARCHAR2(30)

NOT NULL

Name of the table

COLUMN_NAME

VARCHAR2(4000)

Name of the REF column or attribute. If it is not a top-level attribute, the value of COLUMN_NAME should be a path name starting with the column name.

WITH_ROWID

VARCHAR2(3)

Indicates whether the REF value is stored with ROWID (YES) or not (NO)

IS_SCOPED

VARCHAR2(3)

Indicates whether the REF column is scoped (YES) or not (NO)

SCOPE_TABLE_OWNER

VARCHAR2(30)

Owner of the scope table, if it exists and is accessible by the user

SCOPE_TABLE_NAME

VARCHAR2(30)

Name of the scope table, if it exists and is accessible by the user

OBJECT_ID_TYPE

VARCHAR2(33)

Indicates whether the object ID (OID) is USER-DEFINED or SYSTEM GENERATED

Note:

1. select * from DBA_REFS;

OWNER

TABLE_

NAME

COLUMN_

NAME

WITH_

ROWID

IS_

SCOPED

SCOPE_

TABLE_

OWNER

SCOPE_

TABLE_

NAME

OBJECT_

ID_

TYPE

XDB

XDB$ALL_MODEL

"XMLDATA"."PARENT_SCHEMA"

YES

NO

SYSTEM GENERATED

XDB

XDB$ANY

"XMLDATA"."PROPERTY"."PARENT_SCHEMA"

YES

NO

SYSTEM GENERATED

XDB

XDB$ANY

"XMLDATA"."PROPERTY"."PROPREF_REF"

YES

NO

SYSTEM GENERATED

XDB

XDB$ANY

"XMLDATA"."PROPERTY"."SMPL_TYPE_DECL"

YES

NO

SYSTEM GENERATED

XDB

XDB$ANY

"XMLDATA"."PROPERTY"."TYPE_REF"

YES

NO

SYSTEM GENERATED

XDB

XDB$ANYATTR

"XMLDATA"."PROPERTY"."PARENT_SCHEMA"

YES

NO

SYSTEM GENERATED

XDB

XDB$ANYATTR

"XMLDATA"."PROPERTY"."PROPREF_REF"

YES

NO

SYSTEM GENERATED

XDB

XDB$ANYATTR

"XMLDATA"."PROPERTY"."SMPL_TYPE_DECL"

YES

NO

SYSTEM GENERATED

XDB

XDB$ANYATTR

"XMLDATA"."PROPERTY"."TYPE_REF"

YES

NO

SYSTEM GENERATED

Oracle data dictionary views

Oracle dynamic performance views

Posted by Admin at 12/10/2008 02:42:00 PM 0 comments

Labels: ,

DBA_REFRESH

DBA_REFRESH describes all refresh groups in the database. Its columns are the same as those in "ALL_REFRESH".

Related Views

· ALL_REFRESH describes all the refresh groups accessible to the current user.

· USER_REFRESH describes all refresh groups owned by the current user.

Column

Datatype

NULL

Description

ROWNER

VARCHAR2(30)

NOT NULL

Owner of the refresh group

RNAME

VARCHAR2(30)

NOT NULL

Name of the refresh group

REFGROUP

NUMBER


Internal identifier of the refresh group

IMPLICIT_DESTROY

VARCHAR2(1)


Indicates whether the refresh group is destroyed when its last item is subtracted (Y) or not (N)

PUSH_DEFERRED_RPC

VARCHAR2(1)


Indicates whether changes are pushed from the snapshot to the master before refresh (Y) or not (N)

REFRESH_AFTER _ERRORS

VARCHAR2(1)


Indicates whether to proceed with refresh despite errors when pushing deferred RPCs (Y) or not (N)

ROLLBACK_SEG

VARCHAR2(30)


Name of the rollback segment to use while refreshing

JOB

NUMBER


Identifier of the job used to refresh the group automatically

NEXT_DATE

DATE


Date that this job will next be refreshed automatically, if not broken

INTERVAL

VARCHAR2(200)


A date function used to compute the next NEXT_DATE

BROKEN

VARCHAR2(1)


Indicates whether the job is broken and will never be run (Y) or not (N)

PURGE_OPTION

NUMBER(38)


Method for purging the transaction queue after each push (1 indicates quick purge option; 2 indicates precise purge option)

PARALLELISM

NUMBER(38)


Level of parallelism for transaction propagation

HEAP_SIZE

NUMBER(38)


Size of the heap

Note:

1. Check for refresh Jobs. A refresh job is associated with each refresh group at a materialized view site. Each refresh job refreshes the refresh group at the specified interval. This SQL query summarizes the status of each refresh job at the current materialized view site.

SELECT j.job, j.priv_user, r.rowner || '.' || r.rname "Refresh Group",

decode(j.broken, 'Y', 'Broken', 'Normal') "Status",

to_char(j.next_date, 'dd-Mon-yyyy HH:MI:SS AM') "Start",

j.interval "Interval"

FROM dba_refresh r, dba_jobs j

WHERE r.job = j.job

ORDER BY 1;

2. Summary: This SQL script lists general information about the current materialized view site.

SELECT a.mvGROUP "# of Materialized View Groups",

b.mv "# of Materialized Views",

c.rGROUP "# of Refresh Groups"

FROM

(SELECT count(s.gname) mvGROUP FROM sys.dba_repsites s

WHERE s.snapmaster = 'Y') a,

(SELECT count(*) mv FROM sys.dba_snapshots) b,

(SELECT count(*) rGROUP FROM sys.dba_refresh) c;

Oracle data dictionary views

Oracle dynamic performance views

Posted by Admin at 12/10/2008 11:50:00 AM 0 comments

Labels: ,

Subscribe to: Posts (Atom)