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.

tinlyx
  • 3,540
  • 13
  • 46
  • 72
user152745
  • 21
  • 2
  • Are you trying to create an ERD/ERD-like diagram? – Michael Kutz Jun 04 '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): https://dba.stackexchange.com/questions/208709/exporting-a-database-schema-and-triggers – Michael Kutz Jun 04 '18 at 21:36
  • This formulation of the story of your DBA made me laughing on the floor :-) – peterh Jun 05 '18 at 00:05
  • @MichaelKutz To vote as duplicate you have to click on the flag link and select the duplicate option in the windows that appears. – miracle173 Jun 05 '18 at 06:31
  • 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 05 '18 at 08: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 05 '18 at 14:25
  • since all CREATE scripts should exist in a Code Repository - just like @peterh - I had a good laugh when I read this! A Code Repository indeed... I'd say a good many companies would be hard pushed to produce one if required! I worked for a company which ran the rail infrastructure of a large European country! I looked for the FKs in the system. None! Puzzled I asked if I'd made a mistake. No I was informed by senior programmer-we don't use FK's?I thought WTF? Nope, no FK's "all our searchs are on FK's!" System was on Oracle 9 - why not notepad I thought! – Vérace Jun 05 '18 at 18:05

1 Answers1

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.

Balazs Papp
  • 40,680
  • 2
  • 26
  • 46