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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Sep 10, 2012
any way to select the clob value from dblink.?
View 7 Replies
View Related
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