SQL & PL/SQL :: DDL Though Dbms_metadata
Dec 1, 2011I 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
ADVERTISEMENT
SQL & PL/SQL :: Schema Compare Using Dbms_metadata.compare_alter
Apr 6, 2010I 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 RelatedORA-31603 On Dbms_metadata.get_dll For Dblinks Only
Feb 22, 2012I 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.
SYS.DBMS_METADATA - Error After Migrate To 11gr2
Feb 21, 2013I 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.
SQL & PL/SQL :: DBMS_METADATA.GET_DDL Fails With ORA-31603 On Dblink
Feb 21, 2012I 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.
Server Administration :: Dbms_metadata Giving Incorrect Results?
Jun 20, 2011nlsb> 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?