Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2010

    Smile Unanswered: change in one schema should make the changes in other schemas


    I have 55 schemas on my Oracle DB. Since it's a maintenance project, the packages get altered in every 2-3 days. The problem is I update the same thing in all 55 schemas manually and it takes my lifetime on making the same change eveywhere : (

    I am very weak in Unix and have no idea about its scope.
    Is there a way to update packages of all schemas on updating / altering the package in a single schema?
    Any help would be highly appreciated and very helpful.

    Note - all 55 schemas have different username and password.

    Thank you in advance : )

  2. #2
    Join Date
    Dec 2012
    Hello sunny_007,

    a shell script is a good idea if you use a unix operating system.
    In a loop you can call "sqlplus" for each user.

    You didn't write how you update each package manually.
    Are the updates already sql commands provided in a file (good idea), or do you type in every sql command in all schemas?

    But you can also perform the updates on an other host (and of course using an other operating system) if your firewalls an the oracle database server allow that and an oracle client on the remote host is available.

    If you use a script (a shell script or whatever) you must either connect as each username/password or connect as superuser (e.g. "SYS").
    if you connect as "SYS" all sql commands must provide the schema name for each object.

    And of course, it's very important to check for warnings and errors after performing the update - in every schema.

    If the packages to update in all schemas are completely identical (= no user specific code), you can also provide a package in a special schema other than the 55 user and grant usage for other roles or for "public".
    So you only have to perform the update in the special schema.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts