2

We are with a small problem at work. Our DBA decided to go around the world on a journey of self-discovery. I really love the idea and wish the best for him. The only detail is that he left without leaving anything on the database that he created.

I already searched the site but couldn't find a concrete answer for my trouble or I wasn't searching the right way.

This means that we do not have the schemas, tables with data types for each field, triggers, connections with other tables and bases. For this same problem I would like to know if it is possible to export from TOAD (the application I use to connect to the base and edit information BUT I can use another one if you tell me) in which you have the tables with names, the fields with the data types, schemas, triggers, connections between tables and other bases.

It's an Oracle Database.

I appreciate all the help you can give me, it's very important to us because we asked for a DBA but it will take long for the company to hire one.

  • Are you trying to create an ERD/ERD-like diagram? – Michael Kutz Jun 4 '18 at 21:26
  • I'd "vote as duplicate" if I knew how. All databases must have a Data Dictionary. it doesn't matter if it is MS-SQL or Oracle or PostgreSQL or DB2 or ... Therefore, you should just keep one thread. The answer/tool should be the same. (only if you use direct SQL statements will it be different): dba.stackexchange.com/questions/208709/… – Michael Kutz Jun 4 '18 at 21:36
  • This formulation of the story of your DBA made me laughing on the floor :-) – peterh says reinstate Monica Jun 5 '18 at 0:05
  • 2
    Can't you hook SQL Developer up to your db and get the schema from there - IIRC (and it's been a while), you can even get the CREATE DATABASE script from this? – Vérace Jun 5 '18 at 8:22
  • 1
    @Vérace - Looking at the other thread, the author needs to do this job across multiple RDBMS systems. Getting SQL*Developer to work against MS-SQL was a PITA when I tried. A 3rd party tool would be recommended. If it was only Oracle, I would have suggested SQL*Developer Data Modeler for the reverse engineering. (actually I'd suggest git export first since all CREATE scripts should exist in a Code Repository) – Michael Kutz Jun 5 '18 at 14:25
4

You can generate an SQL file (of metadata) with built-in commandline tools (available on all database installations).

Just log in to the database server, create a full=y, content=metadata_only export:

expdp \'/ as sysdba\' full=y content=metadata_only

Then use impdp to generate an SQL file:

impdp \'/ as sysdba\' sqlfile=metadata.sql

Without specifying a location, Data Pump places its files by default in the directory DATA_PUMP_DIR, but expdp will display the full path of the file it created.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.