Solved

Oracle Database link issues

Posted on 2015-02-02
24
272 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 77

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
Industry Leaders: 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 77

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 250 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 77

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 77

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 77

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 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 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 77

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

752 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