• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 395
  • Last Modified:

Oracle Database link issues

Experts,

I am trying to create a table from my database to a different database. but it isn't working.  I created a database link which is talking to the database just fine.  I am using the following code.

EXEC DBMS_UTILITY_EXEC_DDL_STATEMENT@MYDBLNK('CREATE TABLE MYTABLENAME AS (SELECT * FROM MY TABLE2)');

Eror code:
 TABLE DOESN'T EXIST

and It has dba privileges... Any ideas on what i am doing wrong?
0
Maliki Hassani
Asked:
Maliki Hassani
  • 10
  • 7
  • 7
2 Solutions
 
sdstuberCommented:
does the table really exist?

is TABLE2 owned by the link user?

if not, does the link user have a private synonym pointing to TABLE2?

if not, is there a public synonym on the remote site pointing to TABLE2?

if not, then prefix the table with the schema name that owns the table
0
 
slightwv (䄆 Netminder) Commented:
You are executing that create table on the remote database so BOTH tables need to reside on the remote database.

Either use datapump export with the network_link option or use the sqlplus copy option.
0
 
sdstuberCommented:
also, I'm assuming the "it" in your question is the user in the link,  NOT the user you are using to run that EXEC command
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
slightwv (䄆 Netminder) Commented:
I suppose you could do it in two steps:
Create the remote table then insert into remote_table@MYDBLNK select * from local_table;
0
 
Maliki HassaniAuthor Commented:
Thanks, I added the prefix to the database table and it is working partially.  It shows 200 rows selected but getting an error :
ORA-02021: DDL operations are not allowed on a remote database
ORA-06512: at "SYS.DBMS_UTILITY", line 574
ORA-06512: at line 1
02021. 00000 -  "DDL operations are not allowed on a remote database"
*Cause:    An attempt was made to use a DDL operation on a remote database.
           For example, "CREATE TABLE tablename@remotedbname ...".
*Action:   To alter the remote database structure, you must connect to the
           remote database with the appropriate privileges.
0
 
Maliki HassaniAuthor Commented:
answer is yes: Sdstuber - also, I'm assuming the "it" in your question is the user in the link,  NOT the user you are using to run that EXEC command
0
 
Maliki HassaniAuthor Commented:
SQLPLUS Copy...  Can you provide more details?  This maybe an option for me.
0
 
sdstuberCommented:
what is the statement that you executed that generated the error.

also,  if the intent is create a table on the remote system by selecting from another table on that same remote system then don't put @ inside the procedure call
0
 
Maliki HassaniAuthor Commented:
sdstuber, it is on a different  remote system.

This is the change to my query statment:

EXEC DBMS_UTILITY_EXEC_DDL_STATEMENT@MYDBLNK('CREATE TABLE username.table_name@MYTABLENAME AS (SELECT * FROM MY username.table_name@TABLE2)');
0
 
sdstuberCommented:
CREATE TABLE username.table_name@MYTABLENAME


this means you are creating a table called "table_name" owned by user "username" on a remote sytem called "MYTABLENAME"

that doesn't sound like something you want to do


SELECT * FROM MY username.table_name@TABLE2

this is invalid syntax



assuming table2 and mytablename are the tables (not the links)
and assuming both of those tables are on the same remote sytem  (that is, you are not working on 3 databases, but only 2)

then try this...


EXEC DBMS_UTILITY_EXEC_DDL_STATEMENT@MYDBLNK('CREATE TABLE username.MYTABLENAME AS (SELECT * FROM MY username.TABLE2)');
0
 
Maliki HassaniAuthor Commented:
Here is the code that I am using.
EXEC DBMS_UTILITY_EXEC_DDL_STATEMENT@BENCOM('CREATE TABLE TAMPABAY.TEST_NOC AS (SELECT * FROM NOCREPORTS.VIEW_TKT_CHG_MGMT_MAPPING)')

Error code:

Error starting at line 1 in command:
EXEC DBMS_UTILITY_EXEC_DDL_STATEMENT@BENCOM('CREATE TABLE TAMPABAY.TEST_NOC AS (SELECT * FROM NOCREPORTS.VIEW_TKT_CHG_MGMT_MAPPING)');
Error report:
ORA-06550: line 1, column 7:
PLS-00114: identifier 'DBMS_UTILITY_EXEC_DDL_STATEMEN' too long
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:
0
 
sdstuberCommented:
you've got an underscore where you need a period

between the package name and the procedure name

EXEC DBMS_UTILITY.EXEC_DDL_STATEMENT@BENCOM('CREATE TABLE TAMPABAY.TEST_NOC AS (SELECT * FROM NOCREPORTS.VIEW_TKT_CHG_MGMT_MAPPING)')
0
 
slightwv (䄆 Netminder) Commented:
Are both tables on the remote system or is one table local and you want to 'move' it to the remote system?
0
 
Maliki HassaniAuthor Commented:
the last question is correct.  Different systems..  the goal is to have these 2 tables on 1 database.
0
 
slightwv (䄆 Netminder) Commented:
>>the last question is correct.  Different systems..  the goal is to have these 2 tables on 1 database.

So, you have a table on databaseA that you want recreated on databaseB?

If so:
Try datapump using the network_link option.

Here is the sqlplus copy command:
https://docs.oracle.com/cd/E11882_01/server.112/e16604/apb.htm#i641251

Disclaimer:
SQLPLUS not pl/sql, stored procedure, nothing but the sqlplus program itself.  It will work nowhere else.  Well maybe SQL Developer/Toad but I'm not sure.  I never use a GUI.
0
 
sdstuberCommented:
>>> the last question is correct.  Different systems..  the goal is to have these 2 tables on 1 database.

I'm afraid I still don't understand

Do you have this?

3 databases

A - execute the procedure here
B - select from table here
C - create table  here


or this...

2 databases

A - execute the procedure here
B - select from table here
A - create table here


or this...

2 databases

A - execute the procedure here
A - select from table here
B - create table here


or this...

2 databases

A - execute procedure here
B - select from table here
B - create table here
0
 
Maliki HassaniAuthor Commented:
sorry for the confusion.. been a long day.  
2 databases

A - execute the procedure here
A - select from table here
B - create table here
0
 
slightwv (䄆 Netminder) Commented:
If this is a one-time thing and doesn't need to be done in code, I would go for the quick and dirty and use sqlplus and the COPY command.

If this needs to be done in code and more frequently, I would look at datapump.
0
 
Maliki HassaniAuthor Commented:
is it possible to create a function that inserts to a different database using the database links feature?
0
 
Maliki HassaniAuthor Commented:
I will need this table updating every 15 minutes.  If it is possible to use db links then this will work.
0
 
slightwv (䄆 Netminder) Commented:
You can easily insert into a table from a remote table using a link.  

I mentioned that above:
pre-create the table in the remote database then:
insert into MYTABLENAME@MYDBLNK SELECT * FROM MY TABLE2;

Where it failed before was you were executing the DDL on the remote database contained in the string.  That includes the select.

You just cannot issue a CTAS remotely the way you were trying.

Unless perhaps:  Does the link exist on the remote database that points back to the local one?

If so, you might be able to do:
EXEC DBMS_UTILITY_EXEC_DDL_STATEMENT@MYDBLNK('CREATE TABLE MYTABLENAME AS (SELECT * FROM MY TABLE2@MYLOCALDATABASE)');

Open in new window


But the link MYLOCALDATABASE needs to be create in the database that is pointed to by MYDBLNK.
0
 
slightwv (䄆 Netminder) Commented:
>>I will need this table updating every 15 minutes.  If it is possible to use db links then this will work.

What is the exact requirement?  There may be other ways like replication, materialized views, etc...
0
 
Maliki HassaniAuthor Commented:
Thank you for the help.  Looks like I had the logic wrong with my query.
0
 
sdstuberCommented:
if this is a repeating process to populate the data every 15 minutes then creating the table each time is NOT the best way to do it.

better is to delete or truncate the table then insert.

as noted by slightwv  that is exactly what materialized views are for
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 10
  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now