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

Local Administrator
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)
- Permalink
- Nov 19, 2018
Local Administrator
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:

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

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.

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.

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)
- Permalink
- Nov 19, 2018

Page tree

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

2 Comments

Local Administrator

Local Administrator

Load Definitions Utility for Oracle