DBA_UPDATABLE_COLUMNS

Oracle 11gR1

DBA_UPDATABLE_COLUMNS describes all columns in a join view that can be updated by the database administrator, subject to appropriate privileges. Its columns are the same as those in ALL_UPDATABLE_COLUMNS.

Related Views

· ALL_UPDATABLE_COLUMNS describes all columns in a join view that are updatable by the current user, subject to appropriate privileges.

· USER_UPDATABLE_COLUMNS describes all columns owned by the current user that are in a join view and are updatable by the current user, subject to appropriate privileges.

Column	Datatype	NULL	Description
OWNER	VARCHAR2(30)	NOT NULL	Table owner
TABLE_NAME	VARCHAR2(30)	NOT NULL	Table name
COLUMN_NAME	VARCHAR2(30)	NOT NULL	Column name
UPDATABLE	VARCHAR2(3)		Indicates whether the column is updatable
INSERTABLE	VARCHAR2(3)		Indicates whether the column is insertable
DELETABLE	VARCHAR2(3)		Indicates whether the column is deletable

Note:

1.

select * from DBA_UPDATABLE_COLUMNS

where owner = 'USERA' and table_name = 'MY_VIEW';

OWNER	TABLE_NAME	COLUMN_NAME	UPDATABLE	INSERTABLE	DELETABLE
USERA	MY_VIEW	CONTACT_ID	YES	YES	YES
USERA	MY_VIEW	FIRST_NAME	YES	YES	YES
USERA	MY_VIEW	LAST_NAME	YES	YES	YES
USERA	MY_VIEW	EMAIL	YES	YES	YES
USERA	MY_VIEW	WHO_CREATED	YES	YES	YES
USERA	MY_VIEW	WHEN_CREATED	YES	YES	YES
USERA	MY_VIEW	WHO_UPDATED	YES	YES	YES
USERA	MY_VIEW	WHEN_UPDATED	YES	YES	YES

2. A join view is defined as a view that has more than one table or view in its FROM clause (a join) and that does not use any of these clauses: DISTINCT, aggregation, GROUP BY, START WITH, CONNECT BY, ROWNUM, and set operations (UNION ALL, INTERSECT, and so on).

An updatable join view is a join view that involves two or more base tables or views, where UPDATE, INSERT, and DELETE operations are permitted. The data dictionary views ALL_UPDATABLE_COLUMNS, DBA_UPDATABLE_COLUMNS, and USER_UPDATABLE_COLUMNS contain information that indicates which of the view columns are updatable. In order to be inherently updatable, a view cannot contain any of the following constructs:

■ A set operator

■ A DISTINCT operator

■ An aggregate or analytic function

■ A GROUP BY, ORDER BY, CONNECT BY, or START WITH clause

■ A collection expression in a SELECT list

■ A subquery in a SELECT list

■ Joins (with some exceptions)

Views that are not updatable can be modified using INSTEAD OF triggers.

Oracle data dictionary views