1

We can export the entire database schema(skeleton) along with its data in one .dmp file using oracles "exp" tool. But how do I do it using Toad only.

I can't use this

C:\Users\kaunds_oliver>exp PORTAL_xxx/xxx@asdb file=E:\\Oliver\\userexpo
rt.dmp log=E:\\Oliver\\userexport.log

As there is no entry in the TNSNAMES.ora for the remote server ="asdb" Also, I tried doing this

ASDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP) (HOST = 172.27.xx.xx) (PORT = 1530))
    )
    (CONNECT_DATA = (SERVICE_NAME = asdb))
  )

But I don't have the privileges to edit anything in C Drive i.e. the TNSNAMES.ora file

How do I create a dump file using specifically "Toad" tool of a database that I am connected to remotely?

1

Not directly answering the question as I don't use Toad, but you can use exp without a tnsnames.ora by using the 'easy connect' syntax for the connection string, at least in recent versions:

exp PORTAL_xxx/xxx@//172.27.xxx.xxx:1530/asdb file=...

... where asdb is the database service name registered with the listener.

You can also create your own tnsnames.ora in a different directory and set the TNS_ADMIN environment variable to that directory path. But is asdb is the SID and not the service name then you'd need to modify what you'd proposed. For example, create E:\Oliver\tnsnames.ora containing:

ASDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP) (HOST = 172.27.xx.xx) (PORT = 1530))
    )
    (CONNECT_DATA = (SID = asdb))
  )

then do:

set TNS_ADMIN=E:\Oliver\
exp PORTAL_xxx/xxx@asdb file=...

You should also consider using the data pump equivalent, expdp, if you're on 10g or higher, which puts the dump file on the server; and you can create the dump using the API calls rather than with the command line tool if you need to. Getting the dump file off the server might then be an issue - not sure if you're using exp for a reason, of course.

  • Doesn't work. This is what it says. :( EXP-00056: ORACLE error 12514 encountered ORA-12514: TNS:listener does not currently know of service requested in connect descriptor EXP-00000: Export terminated unsuccessfully – Oliver Jul 31 '14 at 10:00
  • That's the same service name you proposed for the tnsnames.ora entry. So it is actually the SID? How do you connect through Toad, with a device name or a SID? Do you know the actual service name of the database? – Alex Poole Jul 31 '14 at 10:01
  • I don't know anything about expdp do you think I should try with that? and what API commands are you talking about? Are they some Java stuff ? – Oliver Jul 31 '14 at 10:01
  • :I use the SID to connect to the database using Toad. asdb is the SID. – Oliver Jul 31 '14 at 10:02
  • 1
    OK, so what is the service name? Can you see it from Toad, e.g. select value from v$parameter where name = 'service_names'? If you don't have privs to do that you'd need to ask your DBA. Or use your own tnsnames.ora file, specifying the SID instead. – Alex Poole Jul 31 '14 at 10:04
2

What is your Toad version?

I have 9.5 here and there are three options:

  • Database > Export > Export Utility Wizard

  • Database > Export > Export DDL

  • Database > Export > Generate Database Script

There's one last option, in case you only need to get the 'skeleton' for one schema:

  • Database > Export > Generate schema script

Have you tried any of that?

[EDIT] I've tried Alex Poole approach and it didn't work for me either.

Sqlplus to the entry:

p08[oracle] (sigepsti) /oracle/product/11.2.0/db/network/admin$ sqlplus system@sigepsti

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 31 17:07:23 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

(sigepsti)SQL> exit

Expdp error (exp gets the same error):

p08[oracle] (sigepsti) /oracle/product/11.2.0/db/network/admin$ tnsping sigepsti

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 31-JUL-2014 17:07:37

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
/grid/product/11.2.0/grid/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dborasigepsti)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = sigepsti)))
OK (0 msec)
p08[oracle] (sigepsti) /oracle/product/11.2.0/db/network/admin$ expdp system@'dborasigepsti:1521/sigepsti'

Export: Release 11.2.0.4.0 - Production on Thu Jul 31 17:07:50 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

UDE-12504: operation generated ORACLE error 12504
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
  • Well I am using version 11.6 of Toad. I want the data as well as the skeleton. I tried Export Utility Wizard, but at the last step it asks for an .exe file of where the Wizard is located. Any inputs on this ? – Oliver Aug 1 '14 at 4:58
  • 1
    documents.software.dell.com/DOC127938 If Toad cannot find this executable, the error "The Oracle Export Utility executable must be specified" displays. To specify the location of the Oracle Export Utility Select View | Toad Options | Executables. Enter the path in the Export box. – Daniel Stolf Aug 4 '14 at 13:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.