Export Data

Estimate size of exports

Before doing any export (using the old exp command) of your data in oracle, you need to have enough space on your disk.
You can estimate the place that will take all the files needed for the export with :

SELECT SUM(BYTES) FROM USER_SEGMENTS WHERE SEGMENT_TYPE='TABLE';

Don't forget that the size displayed is in bytes
Note
When you will do your import into the new database, you will probably need more space than this. Because there is free space in the datafiles, and you export only the real data and not the datafile in a stupid way…

So, to have an idea to estimate the needed room for datafiles, simply do a :

ls -l datafile.dbf

or

du -k datafile.dbf

on the datafile, and you will see the needed space.

Export full database

I will do it with a parameter file, easier than on a command line, as you can re-use the file, and change it to your needs.
So, here is what contains my parameter file :

full=y
file=full-db-1.dmp full-db-2.dmp full-db-3.dmp full-db-4.dmp
log=full-db.log
statistics=none
feedback=10000
filesize=1000mb

Let's explain a bit these options.
  • full=y
    • will create a full database export, as we wanted. So, don't forget it !!
  • file=full-db-1.dmp full-db-2.dmp full-db-3.dmp full-db-4.dmp
    • we specify here, where to put the data of the export in different files. It's related to the option filesize(see lower)
  • log=full-db.log
    • will write to the file full-db.log all the informations that is writtend to stdout. Usefull to have a report after the export is done.
  • statistics=none
    • This will prevent you to have a warning, for more information go here
  • feedback=10000
    • This will print a point on stdout, and logfile if provided, each 10000 row exported. Usefull to known if your export is still working, as sometime it needs really a lot of time to do his work.
  • filesize=1000mb
    • It will create file(exported data) not greater than 1000 mb in size. More easier to transport than a file of 6gigabytes. Change it according to your needs.

Note that the options :

  • file
  • filesize

are related to each other. By instance, if you specify

filesize=1000mb

and

file=full-db-1.dmp full-db-2.dmp

Exp command will put your exported data to your first file :

full-db-1.dmp

and it will contains only the first :

1000mb

of data, and the next exported data will be put to :

full-db-2.dmp

and so on, if you specify other file. Otherwise, exp will prompt you to specify a name. So, when you launch your process to export your data, don't forget to specify enough file name, otherwise it will stop the execution and will wait for a name where to export his data.

Now, we can launch the process of exporting our database.
As a full database export need to see some special data, you need to do it with sysdba privilege.
Moreover, the full database export will only be related to a user. So, by instance to have all the table and so on of user :

scott

with his password :

tigger

You have to do that :

$ exp \'scott/tigger as sysdba\' parfile=parameter-file.txt

If all goes properly, it's time to do an import.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License