There are many ways to export data from Oracle and automate the functionality. Be sure to understand exactly what the data export is being used for, though. If it is for interop between systems, then export in a format your receiving system can understand. If it is for backup purposes, go for the exp/expdp (data pump) method because a database backup needs to store much more than simply data. (Better yet, just use RMAN. But I know many DBAs who also like to do full db exports on a regular basis as well.)
You can use a number of tools to accomplish this, TOAD being the one that springs to mind. It has a powerful data export tool that supports scheduling. There is a free version available, but I am uncertain if it has the scheduling functionality. Worth a try, though.
Alternatively, use the tools already at your disposal: SQL*PLUS, PL/SQL, and cron (or the Windows Scheduler if you run Windows). For a good example of how to write a PL/SQL routine that exports a table to a CSV file, see http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:88212348059 . If you use a procedure, you can schedule it from Oracle's own job scheduler.
You can do similar things purely with SQL*PLUS as well (without getting into writing a procedure), but it isn't quite as flexible, but for what you want, it might be just what you need. SQL*PLUS is also easily scriptable so that you can then call it whenever you want via cron/Windows Scheduler.
SQL*PLUS works really well on its own to create good fixed-width reports, but it is possible to do HTML and CSV as well. XML will probably require a procedure of some sort, but I'm not an export here, so SQL*PLUS may not be perfect here (it will output to HTML, though, so that might be good enough). If exporting to Excel, remember that the current versions use XML as their file format, which makes things easy (in one way) and painful in other ways (like needing to know beforehand how many rows you're going to have in the output file).
Regardless, with a little bit of work and the combination of two or three tools, you should be able to export your data in any format you wish on any schedule you desire.
Hope that helps.
Unfortunately not, you cannot export from a 10g database with the 11g client. You can, though, use a dump file from a 10g database (exported with the 10g client) to import into an 11g database (with the 11g client).
Alternatively you can use the newer datapump functionality, and if you create a database link from 11g to 10g you can even transfer the data directly between them without needing to create an intermediate dump file at all.
Best Answer
The syntax should be
exp username/password@instance
, please also check if the listener is runningps -ef|grep tns
and if it has the service listedlsnrctl services
(the error "ORA-12154: TNS:could not resolve the connect identifier specified" points out that there is a problem related to that). You can also use the new tool expdp which in my opinion is more easy to use. Ex:expdp sys/password schema=userID dumpfile=userID.dmp directory=data_pump_dir logfile=userID_dmp.log
. You can see where the data_pump_dir points to from sqlplusselect * from dba_directories
and even create your own directoryCREATE DIRECTORY dmpdir AS '/opt/oracle';
and use it in the directory clause. But if you want to use exp, check the listener and also the syntax of your command.