exp command accepts host and port to export remote DB tables

Question

In oracle we have exp utility. we use that as exp username/password@database. Here can we use provide host and port details to export to remote db tables? If yes, what is the format to provide those? Thanks

Answer 1

You can use an alias from your tnsnames.ora, or provide a connection string in a similar format, or use the Easy Connect syntax:

exp user/password@host:port/service_name

Note that this requires the service name of the database, which might not be the same as the SID. You can get the services by running lsnrctl services on the remote server (if you can) or by querying the v$parameter view (if you have access and permission), or by asking the DBA for the remote server.

The connection options are the same as for any other tool, so if you can connect to the remote database using SQL*Plus then you can use the same connect string for exp. If you're using @database to connect to a local instance then that is probably already defined in your tnsnames.ora, and you could add entries for the remote database there too. If this is a one-off then the Easy Connect is simpler.

Also, if you're not still on an old version (pre-10g), you should use datapump (expdp) rather than the old exp tool.