1 Reply Latest reply: Feb 22, 2012 3:32 AM by Susil Kumar Nagarajan RSS

    Dbms Advisor - Segment Advisor Task over db links

    918883
      Good morning to everybody, i'm wondering why if is it possible to execute dbms advisor (segment advisor) task from a database to others one with PL SQL. My problem is that i have 13 Databases on different Servers and i have 13 db links. Now i have to call dmbs_advisor package from the first database on these 13 different databases and put the advisor returned data to the first one. I'm looked for the solution on internet but nobody solved this. Is there a way to achieve this goal?

      Thanks in advance,
      cheers

      Alberto

      Edited by: user1631132 on 21-feb-2012 1.42

      Edited by: user1631132 on 21-feb-2012 1.46
        • 1. Re: Dbms Advisor - Segment Advisor Task over db links
          Susil Kumar Nagarajan
          It is complex when you are doing it from database.
          Try it from back-end using OS commands with SQLPLUS.
          Spool the output in 13 different files to categorise the output by instances.

          Assuming , you have a procedure or anonymous block of code in an SQL file for Segment advice.

          Shell$ sqlplus -s user/password@instance1 @<sqlfile.sql>
          Shell$ sqlplus -s user/password@instance2 @<sqlfile.sql>
          Shell$ sqlplus -s user/password@instance3 @<sqlfile.sql>
          Shell$ sqlplus -s user/password@instance4 @<sqlfile.sql>
          Shell$ sqlplus -s user/password@instance5 @<sqlfile.sql>
          ..........................................
          ..........................................
          ..........................................
          ..........................................
          ..........................................
          Shell$ sqlplus -s user/password@instance13 @<sqlfile.sql>

          Note: You must have the TNS configurations set up on the database for all instances from where you are executing SQLPLUS

          Edited by: Susil Kumar Nagarajan on Feb 22, 2012 1:31 AM