SQL & PL/SQL :: DBMS_METADATA.GET_DDL Fails With ORA-31603 On Dblink

Feb 21, 2012

I have user U1 with dblink DBL1 (private dblink, not public).When I logged in with U1 and try to execute the following statement :

SELECT SYS.DBMS_METADATA.GET_DDL('DB_LINK',OBJECT_NAME)
FROM SYS.USER_OBJECTS
WHERE OBJECT_TYPE = 'DATABASE LINK'
AND OBJECT_NAME = 'DBL1';

I get ORA-31603 error:

ORA-31603: object "DBL1" of type DB_LINK not found in schema "U1"
ORA-06512: at "SYS.DBMS_METADATA", line 4018
ORA-06512: at "SYS.DBMS_METADATA", line 5843
ORA-06512: at line 1

DBL1 script is as follows:

CREATE DATABASE LINK DBL1
CONNECT TO U1 IDENTIFIED BY XX USING 'TNS1';

when I select from user_objects with the following query , it is there.

SELECT *
FROM SYS.USER_OBJECTS
WHERE OBJECT_TYPE = 'DATABASE LINK'
AND OBJECT_NAME = 'DBL1';

DBMS_METADATA.GET_DDL works for every other objects (tables for example) in this schema except for dblinks.

View 10 Replies


ADVERTISEMENT

ORA-31603 On Dbms_metadata.get_dll For Dblinks Only

Feb 22, 2012

I have user U1 with dblink DBL1 (private dblink, not public).

CODECREATE DATABASE LINK DBL1
CONNECT TO U1 IDENTIFIED BY XX USING 'TNS1';

I have user U2 with dblink DBL2 (private dblink, not public).

CODECREATE DATABASE LINK DBL2
CONNECT TO U2 IDENTIFIED BY XX USING 'TNS2';

Both dblinks works fine, it means I can do select.

When I logged in with U1 and try to execute the following statement :

CODESELECT SYS.DBMS_METADATA.GET_DDL('DB_LINK',OBJECT_NAME)
FROM SYS.USER_OBJECTS
WHERE OBJECT_TYPE = 'DATABASE LINK'
AND OBJECT_NAME   = 'DBL1';

I get ORA-31603 error:

CODEORA-31603: object "DBL1" of type DB_LINK not found in schema "U1"
ORA-06512: at "SYS.DBMS_METADATA", line 4018
ORA-06512: at "SYS.DBMS_METADATA", line 5843
ORA-06512: at line 1

same happens when I pass user name to get_ddl:

CODESELECT SYS.DBMS_METADATA.GET_DDL('DB_LINK',OBJECT_NAME, [b]USER[/b])
FROM SYS.USER_OBJECTS
WHERE OBJECT_TYPE = 'DATABASE LINK'
AND OBJECT_NAME   = 'DBL1';

When I select from user_objects with the following query , it is there.

CODESELECT *
FROM SYS.USER_OBJECTS
WHERE OBJECT_TYPE = 'DATABASE LINK'
AND OBJECT_NAME   = 'DBL1';

DBMS_METADATA.GET_DDL works for every other objects of U1 (tables, views, ... for example) except for dblinks. DBMS_METADATA.GET_DDL works for every objects of U2, includes DBL2.

I tried to add "select catalog role" to U1, even when I know that I need it only for objects from other users.

I tried to create DBL2 in U1 - same results.

I tried to re-create dblink using full connection string - with same result.

View 1 Replies View Related

SQL & PL/SQL :: ORA-00904 / DBMS_METADAGE.GET_DDL / Invalid Identifier

May 31, 2010

I create a table:

create table myTable (
id number primary key,
name varchar2(100),
doc_content clob,
creation_date date default sysdate)
/

And then used dbms_metadata.get_ddl to retrieve its DDL:

select dbms_metadata.get_ddl('TABLE', 'MYTABLE') myDDL
2 from dual
3 /
I got the following error.
ERROR at line 1:
ORA-00904: "DBMS_METADAGE"."GET_DDL": invalid identifier

run catproc.sql to create pl/sql package But the error still occurs. I'm trying to understand dbms_metadata.

View 1 Replies View Related

SQL & PL/SQL :: ORA-31603 When Using DBMS_JOB.SUBMIT

Jul 5, 2013

I'm using dbms_metadata.get_ddl inside a package and I have a problem: if I execute the package directly, like "exec pkg_util.read_ddl('TA_DL_IDP', 'RMI_KUNDE') it works fine.

If I submit the same procedure using DBMS_JOB.SUBMIT, I get an error: Err_ -31603 - MSG: ORA-31603: object "RMI_KUNDE" of type TABLE not found in schema "TA_DL_IDP"

I know that in order to use dbms_metadata.get_ddl I need to have SELECT_CATALOG_ROLE and I know about the necessity to give explicit grants to objects rather than using roles when running pl/sql code from inside a package, but this is different: the different behavior is between running the same package in foreground and submitting it using DBMS_JOB.SUBMIT.

View 1 Replies View Related

SQL & PL/SQL :: DDL Though Dbms_metadata

Dec 1, 2011

I have extracted ddl for table using dbms_metedata packages. it always provide douple quotes(") in for every word in ddl. I need to remove " from the ddl scripts.

View 8 Replies View Related

SQL & PL/SQL :: Schema Compare Using Dbms_metadata.compare_alter

Apr 6, 2010

I want to compare two schema and find out object differences and generate DDL script to make schema1 like schema2. I was wondering what is the easiest way to use dbms_metadata.compare_alter to compare all tables. I know its possible through procedure but I am new to sql.

View 2 Replies View Related

SYS.DBMS_METADATA - Error After Migrate To 11gr2

Feb 21, 2013

I am receiving a SYS.DBMS_METADATA error after migrating to 11gr2 RAC from 10gr2 RAC. its worked well in 10g but now throwing error.

onnected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> select object_name,object_type from dba_objects where object_name='STUDENT';
OBJECT_NAME          OBJECT_TYPE
-------------------- -------------------
STUDENT            SYNONYM
STUDENT            MATERIALIZED VIEW
STUDENT            TABLE
[code]....

ORA-06512: at line 1How could we resolve this error. I have already given select_catalog_role to SCOTT but id did not work.

View 5 Replies View Related

Server Administration :: Dbms_metadata Giving Incorrect Results?

Jun 20, 2011

nlsb> select file_name,bytes from dba_data_files where tablespace_name='ARIAN_DATA';

FILE_NAME BYTES
------------------------------------------------------------ ----------
/data950/nlsb/nlsb/datafile/o1_mf_arian_da_6wf27lcn_.dbf 5368709120
/data950/nlsb/nlsb/datafile/o1_mf_arian_da_6wf1txnm_.dbf 8589934592
/data950/nlsb/nlsb/datafile/o1_mf_arian_da_6wf1tr6v_.dbf 8589934592
/data950/nlsb/nlsb/datafile/o1_mf_arian_da_6wf1tonc_.dbf 8589934592

[code].....

dbms_metadata is giving me code that will create more datafiles than the original, and furthermore won't run: firstly, because two files are named with a null string, and secondly because it includes RESIZE commands which won't work because they nominate OMF file names. Or is dbms_metadata unreliable?

View 1 Replies View Related

SQL & PL/SQL :: Revoke Fails Even With SYS?

May 17, 2010

I want to revoke some privileges (given from user, say A, on table TABLE to user B) with SYS, and i've got an error : It says that I can't revoke a privilege that I didn't give.

In DBA_TAB_PRIVS, there's no information about that : I can't know who gives the privilege (when I try to perform the REVOKE statment with OWNER, GRANTOR, I've got the same error).

View 20 Replies View Related

Replication :: Add_Master_Database Fails

Sep 22, 2008

I'm trying to configure and run replication. I have two PC's running as Oracle servers, having both of them the same configuration:

Windows XP Pro
Oracle 11g Enterprise Edition

First server is called Oracle1, and database name is orcl1. Second server name is Oracle2 and database name is orcl2..I'm running the following

SYSTEM/oracle1
alter system set global_names=true;
alter system set job_queue_processes=100;
ALTER DATABASE RENAME global_name TO orcl1.world;

SYSTEM/oracle2
alter system set global_names=true;
alter system set job_queue_processes=100;
ALTER DATABASE RENAME global_name TO orcl2.world;

SYSTEM/oracle1

CREATE PUBLIC DATABASE LINK orcl2.world USING '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=Oracle2)(Port=1521)))(CONNECT_DATA=(SID=orcl2.world)))';

CREATE USER repadmin IDENTIFIED BY clave
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;
[code]...

It looks like database orcl1 can't access orcl2.

View 5 Replies View Related

Registration Of 11.2 Database Fails Against 12.1 CRS

Jul 9, 2013

I am not able to register the database with 12cR1 CRS . Gettting the error as follows,  

bash-3.2$ /opt/oracle/product/11.2.0/db3/bin/srvctl add database -d   11gr2tstdb  -o /opt/oracle/product/11.2.0/db3PRCR-1006 : Failed to add resource ora.nishtest.db for nishtestPRCD-1184 : Failed to upgrade configuration of database type to version 11.2.0.3.0PRCR-1071 : Failed to register or update resource type ora.database.typeCRS-0245:  User doesn't have enough privilege to perform the operation

View 6 Replies View Related

RAC & Failsafe :: Oracle 10g TAF Fails For TOAD

Oct 8, 2011

We are into pre-production stage for Oracle RAC implementation. I have setup Oracle 10g 2 node RAC, currently i have been experiencing TAF failures for TOAD client. TOAD client session simply disconnects rather than failing over to the next node, It is noticed that TAF succesfully works for SQL client from the same client machine. Attached herewith are following from both nodes :

1) Listener file node1, node2
2) TNS file node1, node2
3) TNS file client
4) Output of show parameter listener from node1, node2
5) Output of lsnrctl services node1, node2

Summary of environment :

OS - Windows 2003 entp edition sp2, 32 bit
Database server - 10.2.0.5.0

View 2 Replies View Related

SQL & PL/SQL :: DELETE Fails When INSERT In Progress?

Jan 20, 2012

I have a large 10G database, with records being inserted almost constantly. In order to "UPDATE" records, I need to be able to DELETE the old record and INSERT the new record (even while records are being inserted into the table). It appears that DELETES won't work while records are being inserted (1.5 million records per day). Is there a way around this (which I'm assuming at this point is a contention issue)? The INSERT uses the NOPARALLEL hint; no hints on the DELETE.

View 7 Replies View Related

Broken DB Links - Call Fails With A TNS

Mar 28, 2012

I've begun experiencing a very odd issue with one instance and all of their their db links. When I'm connected via telnet to the unix server, and I connect:

connect myuser/password via sql plus, I connect fine, and the link works.

but when I connect this way:

connect myuser/password@db.tnsname.entry.com using the TNS name specified, I again connect fine but the link calls fails with a TNS cannot resolve service name issue.

This is an existing instance with existing db links, but I repeat the issue on this machine alone with new links. the fully qualified name is the same name entry I'm using to create the link, both when it succeeds and when it fails. This is an 11g instance and the TNSnames file is in the ASM home.Also, i f I connect

connect sys as sysdba I get success as well. This behavior happens with both private and public database links. I'm not aware of any setting changes within this instance, and I've modified (after backing up) the TNSNames file with values from another that works perfectly well. Connecting via remote sqlplus or other JDBC connections gives the same error. Success connecting locally but failure connecting to DB Links when I'm remote. Connecting to the instance shows no issue.

Another instance on the network? An IP config issue in DNS? We have 100's of servers so it's needle in a haystack time. Can I run some sort of trace to see what it's calling to when it works and when it doesn't? (It selects another db's single table and I've confirmed the target is correct and the data is correct).

View 4 Replies View Related

SQL & PL/SQL :: Execute Immediate With DBLink

Feb 23, 2011

I need to write several variations of this statement below to query a remote database, but am having trouble with the syntax.

execute immediate 'select count(*) from ' || v_tablename || '@dblink ' || into v_rowcount;
PLS-00103: Encountered the symbol "INTO"....

What's the correct way to format this kind of dynamic query?

View 4 Replies View Related

PL/SQL :: ORA 02070 And DBLink?

Jul 20, 2012

i am using 11g and pl sqlk developper, here is the script snippet

MERGE INTO GDIEXP.OEM_SCHEMAS@OX10CC30_GDI.LOTO_QUEBEC.COM O
USING (
SELECT T.GRANTEE, I.INSTANCE_NAME
FROM DBA_ROLE_PRIVS T, V$INSTANCE I
WHERE T.GRANTED_ROLE = 'PRIVPROPRIETAIRES') DRP

[code]...

An oracle 02070 is popping up, i don't what's the real reason behind it, i only know it works when i don't use the db link. What can i do ?

View 3 Replies View Related

Using SQLDriverConnect With Special Characters In Password Fails

Jul 28, 2013

on a windows 2008 machine that has the Oracle instant client 11_1 installed (11.01.00.06) i am calling SQLDriverConnect in the following way:

std::string str = "Driver={Oracle in instantclient11_1};Dbq=//10.10.101.66:1521/orcl;;Uid=myUser;pwd=a;b";
odbcRc = SQLDriverConnect(hdbc, NULL, (SQLTCHAR*)str.c_str(), str.size(), (SQLTCHAR*)szConnStrOut, 1024, &cbConnStrOut, SQL_DRIVER_NOPROMPT)) == SQL_ERROR);

as long as the password contains no special chars (namely semicolon) it works fine. but when I try to use special chars in the password It stops working, with an invalid username/password error.

State: 28000 Native error: 1017 Message: [Oracle][ODBC][Ora]ORA-01017: invalid username/password; logon denied

i tried different variations of adding " (\") over the password, over the entire pwd=a;b or braces { but nothing seems to work.the thing is that using ODBC works! what I did was: open ODBC, go to System DSN tab, add "Oracle in instantclient11_1", then fill the following information:

Data source name: Anything
TNS Service Name: //10.10.101.66:1521/orcl
User ID: myUser

then i press test connection, enter the password "a;b" and it works!

View 3 Replies View Related

Alter Table Fails - Ran Out Of Undo Space?

Jun 11, 2009

I want to alter a very large table.

ALTER TABLE MYTABLE ADD
(
ENTRY_TSTMP DATE DEFAULT SYSDATE NOT NULL
)

My table is very large and I am getting an error saying I am out of undo space.

The dba says the undo space is as big as the table.

View 1 Replies View Related

Restoring Oracle Home Patch Fails

Apr 29, 2011

[URL]......

The oracle documents mentions this file

$ORACLE_HOME/.patch_storage/<patch-id_timestamp>/restore.sh

But what I find in that directory is

$ORACLE_HOME/.patch_storage/<patch-id_timestamp>/rolback.sh

Where is the restore.sh file that the document is talking about.

View 1 Replies View Related

SQL & PL/SQL :: Data Insert Fails From Web Form To Database?

Mar 24, 2011

I have a pl/sql package called advisory_form. It has 3 procs. First one asks for the term it calls another web form to get it then asks for student id. Post id to second procedure and web form shows students course information with a cursor also three empty items. When advisor fills this items for each course the data should be inserted into a table.I try to do this with a loop but it didn't work properly. The data which I get from cursor is inserted properyl but when it comes to filled text boxes and select list it does not work. Some data is lost. I am adding screenshot of my web form and the data inserted to table and of course my package code.

This is what I want to insert.

CREATE OR REPLACE package body advisory_form is

countc number;
pidm number;

procedure p_sel_tid(term in term.term_code%type default null) is

hold_term varchar2(30);
begin
if not tmain.F_Vuser(pidm) then return;
end if;

if term is null then

hold_term := tmain.f_getp(pidm, 'term');
else
tmain.p_setp(pidm, 'term', term);
hold_term := term;
end if;

if hold_term is null then

bmain.p_fsterm(calling_proc_name => 'advisory_form.p_sel_tid');
return;
end if;
tmain.P_Open('advisory_form.p_sel_tid');
tmain.P_Disp('advisory_form.p_sel_tid');

[code].....

View 1 Replies View Related

Server Utilities :: CONCAT Fails In SQLLoader?

Mar 2, 2010

I'm trying to concatenate a local phone number field. The LDAP system only has the last 5 digits but for the directory database we need all 7 digits.I've tried every combination I can think of to get the concatenation to work but every combination results in just the first two digits being imported, e.g.,

LOCAL_NUM "'20'||:local_num",

results in just 20 being imported. Every iteration I've tried that didn't result in an error imported only the 20 and ignored the ||. I've also tried calling the CONCAT directly, e.g.,

LOCAL_NUM "CONCAT('20', :local_num)",

result is the same.The problem seems to be that the loader is ignoring the concatenate statement all together. I've tried the statements outside of the loader via sqlplus with expected result so I'm confused as to why it's not working within the loader.

View 2 Replies View Related

Server Administration :: User Login Fails?

Sep 27, 2011

I just create a user and tried to log in but it gives the following error, why?

SQL*Plus: Release 10.1.0.4.2 - Production on Tue Sep 27 10:57:55 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> create user user1 identified by user1;

User created.

SQL> conn user1
Enter password: *****
ERROR:
ORA-12560: TNS:protocol adapter error

Warning: You are no longer connected to ORACLE.

View 26 Replies View Related

PL/SQL :: Sending Mail When DBMS-SCHEDULER Job Fails?

Feb 1, 2013

I have scheduled a job as below.
DECLARE
   n1 NUMBER :=7369;
BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'APPS.SCHE_JOB_TEST'
    
[code]...

I want to maintain the log table for the job with the following fields.

JOBNAME          RECORDS_DELETED   JOB_START_TIME     JOB_END_TIME         JOB_STATUS     ERROR_MSG
SCHE_JOB_TEST    1                02/02/2013 00:00:00  02/02/2013 00:05:00   completed  null

View 5 Replies View Related

Database Creation With DBCA Fails With Error?

Apr 8, 2013

While Creating database on AIX Machine using DBCA utility I am getting following error

Error Details:
-------------------------------------------------------------------------------------------------------------------
Allocating device....
Specifying datafiles...
Specifing datafiles...
Restoring ...

[code]....

View 2 Replies View Related

PL/SQL :: How To Write A Procedure To Get Out Parameter When Condition Fails

Sep 21, 2012

Below is my query it is working fine when condition is satisfied with valid user ID and Password

create or replace
PROCEDURE ISVALIDUSER /*This is Procedure created to validate Login users information Created on 09212012 by Chakravarthy*/
(
pUSERID     IN VARCHAR2,

[code]...

Here I am facing a problem when I am giving invalid User ID or Password it is not passing the -1 into pUSERTYPE which is OUT parameter

View 9 Replies View Related

Getting Data Over SQL Server DBLink

Apr 17, 2012

I just created a dblink from our Oracle 9i database to a SQL Server 2008 R2 Server using oracle heterogeneous services. The dblink seems to be OK and I can query the remote SQL Server database but I only get results for numeric columns.

Say the remote SQL Server table was created like:

CREATE TABLE [MITLOC] (
[MLCONO] [numeric](3, 0) NOT NULL,
[MLWHLO] [nchar](3) NOT NULL,
[MLITNO] [nchar](15) NOT NULL,
[MLWHSL] [nchar](10) NOT NULL,
[MLBANO] [nvarchar](20) NOT NULL,
[MLCAMU] [nvarchar](20) NOT NULL,
[MLFACI] [nchar](3) NOT NULL,
[MLWHLT] [nchar](2) NOT NULL,
[MLSTQT] [numeric](15, 6) NOT NULL)

The remote dblink is named after M3PREP.

Then if I run:

CODEselect MLCONO, MLSTQT from MITLOC@M3PREP => this works OK and I get the two columns.

If I run:

CODEselect * from MITLOC@M3PREP => I get the same results as in the previous query and all nchar, nvarchar type columns are missing, I only get the same two columns.

If I run:

CODEselect MLCONO, MLWHLO from MITLOC@M3PREP=> I get ORA-00904 - the column name entered is either missing or invalid.

I set up my .ora init file like this, I suspect there is something missing here, is it related to the NLS_LANG or so?

CODE#
# HS init parameters
#
HS_FDS_CONNECT_INFO = M3PREP
HS_FDS_TRACE_LEVEL = 4
HS_FDS_TRACE_FILE_NAME = M3PREP.LOG

#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>

View 1 Replies View Related

Way To Reduce Dblink Timeout

Dec 28, 2011

is there any way to reduce the dblink timeout, i need to test if the remote server is up by testing the dblink throw 'select 1 from dual@link_name' but it take too long time 5-6 min., i need to make it 5 sec after that the exception tns timeout appear. I try with some sqlnet.ora parameters like INBOUND_ TIMEOUT but not work for me, also i try using tnsping but it also take long time when the remote server is down.

View 2 Replies View Related

Nested Tables By DBLINK

Feb 1, 2011

There's a table T with a nested table within it on the master database. I need to mantain a materialized view of the table T on a remote database.

I get this error: QUOTE ORA-22804: remote operations not permitted on object tables or user-defined type columnsIs there any recommended workaround of this problem? The remote data must have the same structure as the master one.

The data should be refreshed every day, the data changes moderately, there are more or less 500 records.

View 2 Replies View Related

SQL & PL/SQL :: Selecting Clob Value Over DBLink?

Sep 10, 2012

any way to select the clob value from dblink.?

View 7 Replies View Related

SQL & PL/SQL :: Inserting Data Using DBLINK

Feb 21, 2013

I am inserting XMLTYPE data using DBLINK I am getting the following error.

INSERT INTO APSP.SALES_HISTORY@APSP_LINK
SELECT * FROM KMBS.SALES_HISTORY

ORA-22804: remote operations not permitted on object tables or user-defined type columns

Source table structure

Name Null? Type
----------------------------------------- -------- ----------------------------
SC_NO NOT NULL NUMBER(25)
LT_DATE TIMESTAMP(6)
METHOD XMLTYPE

Target table structure(another DB)

Name Null? Type
----------------------------------------- -------- ----------------------------
SC_NO NOT NULL NUMBER(25)
LT_DATE TIMESTAMP(6)
METHOD XMLTYPE

how to insert XMLTYPE data using DBLINK.

View 16 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved