Re: create view?ora-00957 duplicate column name?

lfree wrote:
> select * from v$version
>
> BANNER
> ----------------------------------------------------------------
> Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
> PL/SQL Release 10.2.0.2.0 - Production
> CORE 10.2.0.2.0 Production
> TNS for Linux: Version 10.2.0.2.0 - Production
> NLSRTL Version 10.2.0.2.0 - Production
>
> 5 rows selected.
>
> I reference document of www.ixora.com.au:
> http://www.ixora.com.au/scripts/sql/create_xviews.sql
> select
> 'create or replace view X_$' || substr(name, 3) ||
> ' as select * from ' || name || ';'
> from
> sys.v_$fixed_table
> where
> name like 'X$%'
> /
> I find to create view X_$KMGSCT and X_$KQRFP has erroe:
> create or replace view X_$KMGSCT as select * from X$KMGSCT;
> create or replace view X_$KQRFP as select * from X$KQRFP;
>
> ora-00957 duplicate column name
> I use sqlplus login sys as dba, run `DESC X$KMGSCT` :
> SQL> DESC X$KMGSCT
> ADDR RAW(8)
> INDX NUMBER
> INST_ID NUMBER
> GRANTYPE NUMBER
> GRANSIZE NUMBER
> COMPONENT VARCHAR2(64)
> OPCODE NUMBER
> OPMODE NUMBER
> PARNO NUMBER
> CURSIZE NUMBER
> USERSIZE NUMBER
> INITSIZE NUMBER
> TARGSIZE NUMBER
> MINSIZE NUMBER
> MAXSIZE NUMBER
> OPERCNT NUMBER
> LASTOPER NUMBER
> LASTMODE NUMBER
> LASTTIME DATE
> STARTTIME DATE
> LASTTIME DATE
>
> I find field LASTTIME has two. why?

The accompanying text on the ixora website probably says it all.

fixed_view_text.sql
This script extracts the SQL statement text for all the V$ views. The V$ views are based on the X$ tables, and reading the SQL statements for these views is the best way to develop an initial understanding of the X$ tables.
Download script for: 7.3, 8.0 or 8.1

Nothing has been done on this script since 8i, so you would need to verify whether it is still compatible after *2 new major* releases. Apart from that it looks appropiate to submit this remark directly to Steve Adams, being the author of the Ixora website.

-- 
Sybrand Bakker
Senior Oracle DBA