How can we load definition of an ORACLE table with different owners?

Author: parcaya@compuamerica.com.ve (parcaya)

The facility “Load Definitions” assume the owner where you are logged. We wish to upload the definition of a lot of tables that are from different owners.

2 Comments

  1. As long as the tables are "private" to the other owners, the only way is to logon as this user.

    AFAIK, if they are "public" (SYNONYM??), they should come up on your selection.

    Once, there was a small SQL to be executed to merge private Tables and Synonyms together in a view.
    Maybe you can extend this view so the other tables would show up as well.

    Uli

    From the V8 doc:

    The ora4xloa.sql or ora3xloa.sql script is also provided to create a
    view for the implementation of the Load Definitions utility.

    Rem
    Rem    Description
    Rem    -----------
    Rem    DBMS                      : Oracle
    Rem    Uniface Driver Version    : U3.x
    Rem    Uniface Version           : 8.3
    Rem    File Version              : 24-May-2002
    Rem
    Rem    Load Definitions sql script
    Rem    ---------------------------
    Rem    Creates or upgrades the UNIFACE_TABLES view in ORACLE for the
    Rem    implementation of Load Definitions.
    Rem
    Rem    Copyright (c) 2000-2002, Compuware Europe B.V.
     

    CREATE OR REPLACE VIEW UNIFACE_TABLES AS
    SELECT USER_TABLES.TABLE_NAME,
        USER_TABLES.TABLESPACE_NAME,
        USER_TABLES.CLUSTER_NAME,
        USER_TABLES.PCT_FREE,
        USER_TABLES.PCT_USED,
        USER_TABLES.INI_TRANS,
        USER_TABLES.MAX_TRANS,
        USER_TABLES.INITIAL_EXTENT,
        USER_TABLES.NEXT_EXTENT,
        USER_TABLES.MIN_EXTENTS,
        USER_TABLES.MAX_EXTENTS,
        USER_TABLES.PCT_INCREASE,
        USER_TABLES.BACKED_UP
    FROM USER_TABLES
    UNION
    SELECT USER_VIEWS.VIEW_NAME,
        'VIEW',
        '',
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        'F'
        FROM USER_VIEWS;

    GRANT SELECT ON UNIFACE_TABLES TO PUBLIC;
     


    Author: ulrich-merkel (ulrichmerkel@web.de)
  2. Perhaps you have a chance using pure $ORA assignment to logon as the different users.

    Just put all your other links in ASN to $ORA1where you can use your "NORMAL" logon.

    Uli

     

    A more current help file (9.3) reads:

    Load Definitions Utility for Oracle

    The Load Definitions utility loads the definitions of entities, fields, and keys.

    By default, the Load Definitions utility loads tables, but not views. If you want to load both tables and views, do the following:

    1. Run the ora6xloa.sql script, which is available in the installation directory. The script creates a view that enables Uniface to load both tables and views. This view must be present in every Oracle schema on which you want to run the Load Definitions utility. You can accomplish this in one of the following ways:
      • Create the view in every schema
      • Create the view in one schema, and create a public synonym for the view
      • Create the view in one schema, and create private synonyms for the view in the schemas on which you want to run the Load Definitions utility
    2. Add the following line to your assignment file:
      USER_TABLES.ORA6 = UNIFACE_TABLES.*
      


      You may specify this assignment in the usys.asn assignment file in the adm directory (or userver.asn in the adm directory.
    3. Ensure that the logon path $ORA is defined in the assignment file. This is necessary when loading the schema objects of the user specified in this logon path.
    4. Run the Load Definitions utility on the logon path $ORA.

    The Load Definitions utility loads tables as modifiable entities, and it loads views as non-modifiable entities. However, some views are modifiable. For this reason, you should review the application model which is loaded by the Load Definitions utility.


    Author: ulrich-merkel (ulrichmerkel@web.de)