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…
- 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
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.
- 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)
- 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.
- This will prevent you to have a warning, for more information go here
- 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.
- 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 :
are related to each other. By instance, if you specify
Exp command will put your exported data to your first file :
and it will contains only the first :
of data, and the next exported data will be put to :
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 :
with his password :
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.