-
creating table on a remote database
Hello all,
I have created a link between 2 databases. On database A , I am logged in as user system and created a link as follows
create database link connect to new-user identified by new-user using ''
On the database B I have created a user named new-user and granted connect,resource,dba to this user. Now from the machine with database A if I run the following
SQL> create table new-user.test@ as select * from users;
it gives me the following error
ERROR at line 1:
ORA-02021: DDL operations are not allowed on a remote database
I have already given dba role to the user new-user on the remote database . What should I have to do to be able to create tables on remote database under the user new-user from the tables on the local database ( P.S. I am system user on the local database )
Thanks a lot for your help
-K
-
Hi
well oracle doesnt allow you to do DDl over a database link..DDL is not allowed..
dbms_job.submit@remote-database( :job_number,
'execute immediate ''create table ....'';' );
commit;
regards
Hrishy
-
Hi
I just discovered that through pl/sql engine we can DO ddl on database links..in oracle8.1.7 i am not sure about 9i though
CREATE OR REPLACE PROCEDURE
create_Remote_Table(p_table_name VARCHAR2) AS
l_sql_error_code PLS_INTEGER;
l_sql_error_message VARCHAR2(512);
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE ' || p_table_name ||' (c1 number)' ;
EXCEPTION
WHEN OTHERS THEN
l_sql_error_code := SQLCODE;
l_sql_error_message := SQLERRM(l_sql_error_code);
DBMS_OUTPUT.ENABLE(5000);
DBMS_OUTPUT.PUT_LINE('OTHER ERROR');
DBMS_OUTPUT.PUT_LINE(l_sql_error_message);
END create_Remote_Table;
BEGIN
create_Remote_Table@db_link(‘my_remote_table’);
END;
regards
Hrishy
-
Originally posted by hrishy
Hi
I just discovered that through pl/sql engine we can DO ddl on database links..in oracle8.1.7 i am not sure about 9i though
CREATE OR REPLACE PROCEDURE
create_Remote_Table(p_table_name VARCHAR2) AS
l_sql_error_code PLS_INTEGER;
l_sql_error_message VARCHAR2(512);
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE ' || p_table_name ||' (c1 number)' ;
EXCEPTION
WHEN OTHERS THEN
l_sql_error_code := SQLCODE;
l_sql_error_message := SQLERRM(l_sql_error_code);
DBMS_OUTPUT.ENABLE(5000);
DBMS_OUTPUT.PUT_LINE('OTHER ERROR');
DBMS_OUTPUT.PUT_LINE(l_sql_error_message);
END create_Remote_Table;
BEGIN
create_Remote_Table@db_link(‘my_remote_table’);
END;
regards
Hrishy
Hrishy :
it cannot create on remote db if u dont have user same as that on curr db for the SP u created........
am using 9i....its giving error....can u post wat u did.....
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Hrishy :
first of all u need to have the procedure on remote DB.....again u will be in fix as u cant perform ddl on remote db thru dblink.....
abhay
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Hi abhay
Yes my proceedure is on the remote DB and i just execute the proceedure from the local Db ..i am sorry i should have mentioned that..
regards
Hrishy
-
conclusion is that u cant perform ddl directly via DB Link..
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
DDL on Linked Server
hai..
will u try this? i found it work
.....
cur INTEGER;
sSQL VARCHAR2(32767);
.....
sSQL := 'CREATE TABLE MY_TABLE (c1 number) ';
cur := BMS_SQL.OPEN_CURSOR@DB_LINK;
DBMS_SQL.PARSE@DB_LINK(cur, sSQL, DBMS_SQL.NATIVE);
DBMS_SQL.CLOSE_CURSOR@DB_LINK(cur);
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|