Alternative for DBMS_METADATA.GET_DLL in Oracle 8i

Question

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 ?

Seriously... *8i*? That's previous century! Upvoting, since I would also like to know what people used to do in 8i days, because as far as I can say, you will get all the details from your **source control**. — Lalit Kumar B, Oct 27 '15 at 07:29
Hopefully your question does not mean "What was the password used for this DB-Link?". The password you will not get in any case. — Wernfried Domscheit, Oct 27 '15 at 07:34
@LalitKumarB : Will check for source control. If you have any link regarding this can u please share ? — Nagendra Nigade, Oct 27 '15 at 07:38
@NagendraNigade Source/version control is maintained by every team. So if you have it, then check for the database source code. — Lalit Kumar B, Oct 27 '15 at 08:41
PS: if you need password for db link then use google. There is some x$_.. view where you will find it in plain text. — ibre5041, Oct 27 '15 at 08:56

score 2 · Accepted Answer · answered Oct 27 '15 at 07:45

2

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.

answered Oct 27 '15 at 07:45

Wernfried Domscheit

54,457
9
76
110

Yeah...i missed the column `host` in `dba_db_links`. Done. – Nagendra Nigade Oct 27 '15 at 07:51

score 2 · Answer 2 · answered Oct 27 '15 at 08:36

2

You can use exp/imp tools. Export that database (or schema) using exp rows=now and then import it back into the database using imp show=yes.

This will not make any changes in the database, but all DDLs will be put into the logfile.

answered Oct 27 '15 at 08:36

ibre5041

4,903
1
20
35

Yes , This will be very helpful for tables. – Nagendra Nigade Oct 27 '15 at 09:20

Alternative for DBMS_METADATA.GET_DLL in Oracle 8i

2 Answers2