Solved

Oracle Database link issues

Posted on 2015-02-02
24
240 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 73

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 76

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 73

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
 
LVL 76

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 73

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 73

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 73

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 76

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 76

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 73

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 76

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 76

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 76

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 73

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

706 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now