Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Oracle Database link issues

Posted on 2015-02-02
24
Medium Priority
?
330 Views
Last Modified: 2015-02-12
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
Comment
Question by:Maliki Hassani
  • 10
  • 7
  • 7
24 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 40584859
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40584862
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 40584865
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40584872
I suppose you could do it in two steps:
Create the remote table then insert into remote_table@MYDBLNK select * from local_table;
0
 

Author Comment

by:Maliki Hassani
ID: 40584889
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
 

Author Comment

by:Maliki Hassani
ID: 40584894
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
 

Author Comment

by:Maliki Hassani
ID: 40584909
SQLPLUS Copy...  Can you provide more details?  This maybe an option for me.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40584912
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
 

Author Comment

by:Maliki Hassani
ID: 40584926
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
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1000 total points
ID: 40584945
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
 

Author Comment

by:Maliki Hassani
ID: 40585004
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 40585015
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40585024
Are both tables on the remote system or is one table local and you want to 'move' it to the remote system?
0
 

Author Comment

by:Maliki Hassani
ID: 40585041
the last question is correct.  Different systems..  the goal is to have these 2 tables on 1 database.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40585057
>>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
 
LVL 74

Expert Comment

by:sdstuber
ID: 40585062
>>> 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
 

Author Comment

by:Maliki Hassani
ID: 40585086
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40585093
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
 

Author Comment

by:Maliki Hassani
ID: 40585136
is it possible to create a function that inserts to a different database using the database links feature?
0
 

Author Comment

by:Maliki Hassani
ID: 40585152
I will need this table updating every 15 minutes.  If it is possible to use db links then this will work.
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 total points
ID: 40585161
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40585165
>>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
 

Author Closing Comment

by:Maliki Hassani
ID: 40586212
Thank you for the help.  Looks like I had the logic wrong with my query.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40586229
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question