DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Gather Table stats is failing with ORA-20001: DATASOURCE_SQL_REM CONNECTION is an inv

  1. #1
    Join Date
    Aug 2007
    Location
    Cyberjaya,kuala lumpur
    Posts
    340

    Gather Table stats is failing with ORA-20001: DATASOURCE_SQL_REM CONNECTION is an inv

    SQL> show user
    USER is "WMOS_ARCHB"

    SQL> Begin
    2 For tableList In (Select Owner, Table_Name From All_Tables
    3 Where Owner IN(('WMOS_ARCHB'))
    4 and temporary = 'N'
    5 Order By Owner, Table_Name)
    6 Loop
    7 dbms_stats.gather_table_stats(tableList.Owner,
    8 tableList.Table_Name,
    9 estimate_percent=>100,
    10 method_opt => 'for all columns size auto',
    11 cascade => true);
    End Loop;
    12 13 End;
    14 /
    Begin
    *
    ERROR at line 1:
    ORA-20001: DATASOURCE_SQL_REM CONNECTION is an invalid identifier
    ORA-06512: at "SYS.DBMS_STATS", line 18408
    ORA-06512: at "SYS.DBMS_STATS", line 18429
    ORA-06512: at line 7
    Thanks/Gopu

  2. #2
    Join Date
    Aug 2007
    Location
    Cyberjaya,kuala lumpur
    Posts
    340
    instead of apostrophe?
    we checked Like this:

    Begin
    For tableList In (Select Owner, Table_Name From All_Tables
    Where Owner IN(('WMOS_ARCHB'))
    and temporary = 'N'
    Order By Owner, Table_Name)
    Loop
    dbms_stats.gather_table_stats('tableList.Owner',
    "tableList.Table_Name",
    estimate_percent=>100,
    method_opt => 'for all columns size auto',
    cascade => true);
    End Loop;
    End;



    SQL> exec dbms_stats.gather_table_stats(null,'"TEST TEST"');

    PL/SQL procedure successfully completed.

    But Since we are taking the table list through the variable “tableList” it doesn’t work..
    Thanks/Gopu

Posting Permissions

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


Click Here to Expand Forum to Full Width