locked
SQL Linked Server to Oracle - How to Update Index on Oracle??? RRS feed

  • Question

  • User-1104215994 posted

    Hi,

    I am using SQL Server 2008 R2 with a linked server to Oracle 10g. I can query a table from SQL Server which is in Oracle as follows:

    Select * from [Database]..[Schema].[Table]

    I wonder if I can somehow update an Index which is in Oracle from SQL Server.

    Here is the Index:

    DROP INDEX Schema.APP_PARMS_PK;

    CREATE UNIQUE INDEX Schema.APP_PARMS_PK ON Schema.APP_PARMS
    (PARM_NM)
    LOGGING
    TABLESPACE IL_INDX
    PCTFREE    0
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          128K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               )
    NOPARALLEL;

    Wednesday, August 3, 2011 9:32 AM

Answers

  • User269602965 posted

    You would have to use Oracle Client logged in as the table owner to create an index,

    or

    the Oracle DBA would have to grant create any index to another Oracle schema owner (user), which most would not do for security reasons.

    +++++++++++

    You could execute the DDL request (create index) via an Oracle Stored Procedure from .NET, again connected as a user with the proper create table/index object privileges.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 3, 2011 3:37 PM