Re: 7 to 8 upgrade - system views

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: 2000/04/28
Message-ID: <956946765.22439.0.pluto.d4ee154e@news.demon.nl>#1/1

Just FYI: the catalog6.sql script creates the Oracle 6 dicitionary views in an Oracle 7 database. So you are already one release behind and that's why you are now running into problems: V6 views are not supported anymore under Oracle 8

So here's your translation
 SELECT OWNER

             , OBJECT_NAME
            , OBJECT_TYPE

FROM ALL_OBJECTS
WHERE OWNER <> 'SYS' AND OWNER <> 'SYSTEM' AND (OBJECT_TYPE = 'TABLE' OR OBJECT_TYPE = 'VIEW') ORDER BY OBJECT_TYPE DESC, OWNER, OBJECT_NAME /

 SELECT OWNER

              , TABLE_NAME
              , COLUMN_NAME
              , DATA_TYPE
              , DATA_LENGTH
              , DATA_PRECISION
              , DATA_SCALE
              , NULLABLE
              , COLUMN_ID

FROM
ALL_TAB_COLUMNS
WHERE OWNER = XXXX AND TABLE_NAME = XXXX  ORDER BY COLUMN_ID
/

 SELECT INDEX_NAME

             , TABLE_OWNER
             , TABLE_NAME
             , COLUMN_NAME
             , COLUMN_POSITION

FROM ALL_IND_COLUMNS
WHERE TABLE_OWNER = XXXX AND TABLE_NAME = XXXX ORDER BY INDEX_NAME, COLUMN_POSITION
/

Hth,

Sybrand Bakker, Oracle DBA

Randy Park <rjpark_at_mindspring.com.nospaam> schreef in berichtnieuws 8ecjuv$979$1_at_slb2.atl.mindspring.net...
> We are soon upgrading from Oracle7 to Oracle8 on our
> OpenVMS system. I've been told that one application
> that reads several system views will not work on
> Oracle8 because system views have changed. I work with
> Oracle for only a couple of weeks a year, so please
> be patient with me.
>
> The application reads the system views to determine
> which tables and views a user has access to. Once
> the user has chosen a table or view, the application
> then reads the system views again to determine which
> columns and indexes the user has access to.
>
> The following SQL statements worked on Oracle 6 and
> Oracle7 (after the CATALOG6.SQL procedure was performed).
> The first selects the available tables and views, the second
> the available columns for a chosen table, and the third
> the available indexes for a chosen table.
>
> SELECT OWNER, TABLE_NAME, TABLE_TYPE FROM ACCESSIBLE_TABLES
> WHERE OWNER <> 'SYS' AND OWNER <> 'SYSTEM'
> AND (TABLE_TYPE = 'TABLE' OR TABLE_TYPE = 'VIEW')
> ORDER BY TABLE_TYPE DESC, OWNER, TABLE_NAME
>
> SELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH,
> DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID FROM
> ACCESSIBLE_COLUMNS WHERE OWNER = XXXX AND TABLE_NAME = XXXX
> ORDER BY COLUMN_ID
>
> SELECT INDEX_NAME, TABLE_OWNER, TABLE_NAME, COLUMN_NAME,
> COLUMN_POSITION FROM ALL_IND_COLUMNS
> WHERE TABLE_OWNER = XXXX AND TABLE_NAME = XXXX
> ORDER BY INDEX_NAME, COLUMN_POSITION
>
> Does anyone know an appropriate translation of these SQL
> statements for Oracle8 system views?
>
> Thanks in advance.
>
>
Received on Fri Apr 28 2000 - 00:00:00 CDT