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

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