Jared,
Thanks, but what I was wondering is how the views got created in the
first place. If the field name needs an alias (i.e. substr(x,i,j)) and you
include this alias in the CREATE VIEW ddl, it will also appear in
dba_views.text. If you don't include the alias in the DDL, the view won't be
created. Yet we have views with no aliases in dba_views.text, yet these aliased
(column names) are in column_name.dba_tab_columns. Also, if I just run the
script from dba_views.text I get the error saying I need the alias. How did
these views get into the database?
Henry
The DBA_VIEWS view does not include the column names in the
view.
If you are really diligent,
you could probably tear apart a trace file and
learn how oracle is reassembling the view from the DD.
A hard parse
on a view can produce
250k of trace file.
It is easier
to recreate by generating the ddl from a combination of
all_tab_columns and all_views.
Been there, done that. :)
Jared
|
| "Poras, Henry R."
<Henry_Poras@dfci.harvard.edu>
Sent by: ml-errors@fatcity.com
12/03/2003 11:49 AM
Please respond to ORACLE-L
|
To:
Multiple recipients of list ORACLE-L
<ORACLE-L@fatcity.com>
cc:
Subject: View definition
oddities |
I found something strange when trying to copy a view from one database
to
another. I spooled the text from dba_views and ran the CREATE VIEW AS
...
statement. There was an error because one of the fields needed an alias
(it was
SUBSTR(xxx)). I went back to the source database and poked around a
bit. The
view definition from dba_views had no field aliases, but
dba_tab_columns did.
For example, dba _views would show something
like:
View_name: test
Text: SELECT SUBSTR(name1,3) FROM emp
While
dba_tab_columns would have:
Table_name: emp
Column_id: 1
Column_name:
ename
as if the view_text was SELECT SUBSTR(name1,3) ename FROM
emp
What's up here?
Henry
--
Please see the official
ORACLE-L FAQ: http://www.orafaq.net
--
Author: Poras, Henry
R.
INET: Henry_Poras@dfci.harvard.edu
Fat City Network
Services -- 858-538-5051 http://www.fatcity.com
San Diego,
California -- Mailing list and web hosting
services
---------------------------------------------------------------------
To
REMOVE yourself from this mailing list, send an E-Mail message
to:
ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in
the message
BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing
list you want to be removed from). You may
also send the HELP command
for other information (like
subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Poras, Henry R.
INET: Henry_Poras_at_dfci.harvard.edu
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Thu Dec 04 2003 - 10:09:26 CST
