Oracle 10g XE GUI

Question

I've designed a database on Oracle 10g xe (Windows Platform) and I want to export the tables I created in a sql file. I've googled all night and didn't find anything. I also tried to install a Windows GUI for that database but I'm not able to connect to database. Please help me out with this :D

Answer 1

There are a few different ways to do this:

• Install Oracle SQL Developer which I thought was installed as a default. Then use this to export your data on a per table basis or as a .dmp file

• The old fashioned way, from the dos command line using the [export command]. This gives you a .dmp file which you can import into another database2

• if you want to send the data to another 10g or 11g database you can use the datapump which is fast and easy but takes a few minutes to set up

Answer 2

If you just want to extract the DDL required to create the same objects in another database, see DBMS_METADATA package. It's quite versatile, and easy to use.

If you want the data as well as the DDL, you should look at Datapump export/import. It will write the DDL and the data into a dump file, but you won't be able to read the file format.

If you really want a .SQL file, that has all the DDL and the data, I guess you could script something. Perhaps use DBMS_METADATA for the DDL, and then write some SQL that generates SQL for the DATA in a whole bunch of INSERT statements.

But, really, if you just want to be able to move an entire schema, including data, then Datapump is the answer. If you just want the DDL, in human readable form, use DBMS_METADATA.

If the requirement is for DDL and data, and it all to be human readable, then please explain your requirement in further detail.

Hope that helps.