Extra column in Materialized view but not in the query definition
- From: Sami Seerangan <dba.orcl@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 28 Aug 2005 01:13:43 -0400
Could someone tell me how "TAX_CATEGORY_VALUE.ALIAS" column is listed in my
Materialized View.
There are some packages refering to TAX_CATEGORY_VALUE.ALIAS column.
The package is VALID since we have this column listed in 8i, however after
migrating to 9i the package becomes INVALID because the column is not listed
anymore.
From 8i:
========
SQL> select owner,object_name,object_type from dba_objects where
object_name='TAX_CATEGORY_VALUE';
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
------------------
PUBLIC TAX_CATEGORY_VALUE SYNONYM
HIBM_TAXONOMY TAX_CATEGORY_VALUE TABLE
HIBM_TAXONOMY TAX_CATEGORY_VALUE UNDEFINED
SQL> select query from dba_mviews where MVIEW_NAME='TAX_CATEGORY_VALUE';
QUERY
--------------------------------------------------------------------------------
SELECT "TAX_CATEGORY_VALUE"."ID" "ID","TAX_CATEGORY_VALUE"."NAME_ID"
"NAME_ID","
TAX_CATEGORY_VALUE"."VALUE" "VALUE","TAX_CATEGORY_VALUE"."IS_ACTIVE"
"IS_ACTIVE"
FROM "TAX_CATEGORY_VALUE"@Q2HIBM.US.HSBC.COM
<http://Q2HIBM.US.HSBC.COM>"TAX_CATEGORY_VALUE"
SQL> desc TAX_CATEGORY_VALUE
Name Null? Type
----------------------------------------- --------
----------------------------
ID NOT NULL NUMBER(10)
NAME_ID NOT NULL NUMBER(10)
VALUE NOT NULL VARCHAR2(100)
IS_ACTIVE NUMBER(38)
ALIAS NUMBER(10)
SQL>
From 9i:
=======
SQL> select owner,object_name,object_type from dba_objects where
object_name='TAX_CATEGORY_VALUE';
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
------------------
PUBLIC TAX_CATEGORY_VALUE SYNONYM
HIBM_TAXONOMY TAX_CATEGORY_VALUE TABLE
HIBM_TAXONOMY TAX_CATEGORY_VALUE MATERIALIZED VIEW
SQL>
SQL> desc TAX_CATEGORY_VALUE
Name Null? Type
----------------------------------------- --------
----------------------------
ID NOT NULL NUMBER(10)
NAME_ID NOT NULL NUMBER(10)
VALUE NOT NULL VARCHAR2(100)
IS_ACTIVE NUMBER(38)
Other related posts:
- » Extra column in Materialized view but not in the query definition
