0

i want to ask about extracting oracle ER Diagram (ERD) from sqlplus. i have a condition that i must access oracle database from my vps (this oracle database at another server). i need to use vps because with vps ,it will provide a domain that will be used for oracle database server to grant access to it database. what i know to generate ER Diagram is from oracle sql Developer .but with my current vps ,it provided CLI only. Unfortunatly sql developer using GUI so i cant use it. So how can i extract ER Diagram from oracle database witohout using sql developer and this solution must not using an app with GUI.

Gery Ruslandi
  • 89
  • 1
  • 9

1 Answers1

0

You can generate a plain text file containing the description for each of your tables, download this file from your remote machine to your local machine and load the content in an ERD tool such as Oracle DataModeler.

A common script to obtain a table description (including your goal, Foreign key constraints) is:

set heading off;
set echo off;
Set pages 1000;
set long 50000;
SET linesize 150;
spool My_ddl.sql;
select DBMS_METADATA.GET_DDL('TABLE','<your table 1>','<schema>') from DUAL;
select DBMS_METADATA.GET_DDL('TABLE','<your table 2>','<schema>') from DUAL;
select DBMS_METADATA.GET_DDL('TABLE','<your table 3>','<schema>') from DUAL;
    .
    .
    .
spool off;

So basically you still are gonna use a GUI tool to build your diagram, but the trick resides in how you get your source scripts.

If you are worried about how to generate the get_ddl instruction for all of your 10,000 tables, fear not, as you can run the following:

SELECT 'SELECT DBMS_METADATA.GET_DDL(''TABLE'','''||table_name||''', ''<LOCAL/FOREIGN SCHEMA>'') FROM dual;' as get_ddls
FROM all_tables
WHERE owner = '<User of the foreign schema where tables are stored>';

and then just copy paste the results to the previous script.

In order to retrieve the file generated server side, you can always ask (kindly, of course) your DBA to hand it over to you, unless you have a way to access directly into the server directories/files.

Good luck.

Jair Hernandez
  • 494
  • 3
  • 7
  • thanks for the answer sir .i really apreciate it . but sir i just have a user that dont have a sys privileged , is user need a special privilege for run this query? and i have another condition that i dont know what a table that my user account granted to access. i just want to get a table that my user account can access it.is it possible to get_ddl only for a table that my user account granted only ? btw ,Thanks a loot , im really helped with your answer . – Gery Ruslandi Jun 22 '17 at 23:34
  • Hello Gery, glad I can help. You will need a special privilege `SELECT_CATALOG_ROLE` to run the `get_ddl` query, but only if tables are in a schema different than yours, in which case you will also need to refer to that specific schema in the `get_ddl` function. I've modified my answer so you can see this change. – Jair Hernandez Jun 23 '17 at 17:06
  • Thanks again sir. Your answer really solve my problem, thank you for helping me, i hope i can be as good as you in the future haha. Once again. Thanks a lot – Gery Ruslandi Jun 25 '17 at 21:04
  • You're most welcome my friend. One advise, do as I do and keep on asking always about anything, as it helps us all to learn, and probably I could learn something else from you in the near future. – Jair Hernandez Jun 27 '17 at 02:35