1

I'd like to export database with all the triggers, indexes, grants and user accounts from Oracle 10g database. Problem is I only have SYSDBA access to the server, so I can't access the servers files or ssh to it. Also, I can't shutdown the database. What I'm trying to achieve is to get a test db from our production db without interfering with db operations. I dont need all the data to be up to date in test environment.

Seems all the usual file backups and RMAN require either direct access to files or require me to shutdown the database. Expdp also dumps the files to the server, not to the client. Is there any other method than to dump db with expdp directly to other server? The other server is on a remote location so that would probably take too long...

Thanks

JukkaA
  • 13
  • 4

1 Answers1

2

You can use the EXP tools out of another oracle setup or a full client install. Make sure to use the CONSISTENT=Y flag and do not run the export during a business-critical time. A good primer on EXP/IMP available here.

@Derfk points out a way to do network export/import using the datapump tools, documented in the Network Import section of this page. Note you will need to create a database link between production and development to copy across, but won't need to create the intermediate file. Full disclosure: I haven't ever done that particular thing.

khoxsey
  • 725
  • 4
  • 9
  • That's for older versions of oracle. They also can't export over the network. Update the question with a link to the 10g utilities that are linked from that page and I'll give you a point. – DerfK Jul 24 '12 at 20:15
  • OP is using [10g, which ships with EXP/IMP](http://docs.oracle.com/cd/B19306_01/server.102/b14215/exp_imp.htm) and should work fine. I switched over to the DataPump tools a while ago, but I recall doing network exports on the 10g platform. Do you have a pointer to an Oracle doc showing that you can't use EXP over sqlnet? – khoxsey Jul 24 '12 at 20:21
  • The link you gave says `These new utilities introduce much needed performance improvements, network based exports and imports, etc.` – DerfK Jul 24 '12 at 20:25
  • Exp/imp certainly works over the network, and would typically be the preferred way of doing a transfer like this. These tools are available across all releases of Oracle Server the past decade. – Roy Aug 10 '12 at 06:46