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

Thread: Database Link - Error ORA-02021

  1. #1
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598

    Database Link - Error ORA-02021

    Hi All,

    I am getting "ORA-02021: DDL operations are not allowed on a remote database" when I try to execute a procedure in remore database which creates table, indexess aft the process is successfullty completed it drops those tables created.

    this is workin in our production env.

    NO i need to know wht all the privs required.

    Thanks,
    Jegan
    Cheers!
    OraKid.

  2. #2
    Join Date
    Jan 2003
    Location
    Hull, UK
    Posts
    220
    hi

    how about having a procedure like this...

    create or replace procedure
    trunc_tableinremotedb(p_tablename varchar2) as

    begin
    execute immediate 'truncate table' || p_tablename

    exception when others then.

    ....
    ...

    end trunc_tableinremotedb;

    begin
    trunc_tableinremotedb@db_link('remote_tablename');
    end;

    hope i m making some sense here...

    let me know if this is what u r looking for.

    Rgds

    Srini

  3. #3
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Thanks.
    sur will try and revert.

    Is there some other method?
    Cheers!
    OraKid.

  4. #4
    Join Date
    Jan 2003
    Location
    Hull, UK
    Posts
    220
    AFAIK theres no other way to achieve it.....

    S

  5. #5
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Thanks guys

    Thru this procedure I am able to truncate the table in remote DB using DB LINK

    16:15:10 SQL> create or replace procedure db_link_trunc_table_procedure as
    16:15:29 2 /* Local Variables */
    16:15:29 3 c_handle INTEGER;
    16:15:29 4 feedback INTEGER;
    16:15:29 5 BEGIN
    16:15:29 6 c_handle := DBMS_SQL.open_cursor@edwd01.h3guk.com;
    16:15:29 7
    16:15:29 8 DBMS_SQL.parse@edwd01.h3guk.com( c_handle
    16:15:30 9 , 'TRUNCATE table test_dblink_trunc@edwd01.h3guk.com'
    16:15:30 10 , DBMS_SQL.V7);
    16:15:30 11
    16:15:30 12 feedback := DBMS_SQL.execute@edwd01.h3guk.com(c_handle);
    16:15:30 13
    16:15:30 14 DBMS_SQL.close_cursor@edwd01.h3guk.com(c_handle);
    16:15:30 15
    16:15:30 16 END;
    Cheers!
    OraKid.

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