Search code examples
oracleoracle8idatabase-metadata

Alternative for DBMS_METADATA.GET_DLL in Oracle 8i

In our project we have one database running on oracle 8i (i know this version is ancient but ...)

I want to check the DDL statement for one PUBLIC database link i tried :

select dbms_metadata.get_ddl('DB_LINK', 'LINKNAME', 'PUBLIC') from dual;

select dbms_metadata.get_ddl('DB_LINK', 'LINKNAME', 'PUBLIC') from dual;
                     *
ERROR at line 1:
ORA-00904: invalid column name

As per my knowledge it seems like oracle 8i does not support dbms_metadata. Please correct me if i am wrong.

Table dba_db_links gave me bit information but missing SID of target database.

Is there any way to get DDL statement on Oracle 8i apart from exporting the database/schema ?

Solution

  • You can query the data dictionary:

    SELECT 'CREATE PUBLIC DATABASE LINK "'||DB_LINK||'" CONNECT TO '||USERNAME||' IDENTIFIED BY "<PWD>" USING '''||HOST||''';' AS cmd
FROM DBA_DB_LINKS
WHERE owner = 'PUBLIC'
    AND DB_LINK = 'LINKNAME';

    If you miss information try to select table sys.link$ instead.