Toad World® Forums

import .pde file


#1

I exported .pde (PL/SQL developer format) file of my db. Now I want to import this to my db in toad(oracle) with all tables and datas
How will I do this ?

Note: I exported as .pde file becaue my database contains blob columns so now way out export as sql (actually I wanted to export .sql format because I want to reach script datas)
so You can tell me if there is a way to export this database as .sql file ?


#2

Toad doesn’t support .pde files, sorry.


#3

Instead of using client side tools like the PDE file, why didn’t you run export or data pump export to do the same thing in a format that you can take from one place to another?


#4

You also could have used a transportable tablespace - which might have been quicker (and assuming you have db objects of interest all in on place). While your old tool might not have screens for all these things I mention - Toad with db admin module does :slight_smile:


#5

>Note: I exported as .pde file becaue my database contains blob columns so now way out export as sql (actually I wanted to export .sql format because I want to reach script datas)

  1. expdp work nice with blobs (11 for sure, 10g not sure)

  2. make expdp (schema, database … whatever you need)

  3. On the same database run import (impdp) with “SQLFILE” option which will create ONLY plain text file with pure SQL’s of all objects… And it would not import anything!

Brg,

Damir Vadas


#6

[mention:228f8d2504734c859820ea0da5a1084e:e9ed411860ed4f2ba0265705b8793d05] Thank you for comment. I try to export data pump but it created .dmp file 4 KB size . There is thousands of data in my table… How can I take all tables with datas in my db with export .dmp file ?

Note:My db is in remote machine I remote connection to server and export db then transfer to my local pc.


#7

expdp:

asanga-pradeep.blogspot.com/…/expdp-impdp-with-network-link.html

And be aware that blob over db_link may be a slow operation with certain impact on local network


#8

[mention:f5e669462ed84c2185832f0ffc80c5b8:e9ed411860ed4f2ba0265705b8793d05] Thank you valueable answer. but;

  1. when I export table data pump, it creates 4 KB size .dmp file. I think only tables names and columns contained in it. Do you know how can I export completely data pump entire db(with scripts, datas vs.)

  2. I understand what you mean, I just cick Tools->Export tables then select Oracle Export tab(others: Sql Inserts, PL/SQL Developer (.pde file which export fine but cant import :S ))

Then I thicked Compress,Consistents,Constraints,Direct,Grants,Indexes,Rows,Triggers.

Buffer size:80KB (defeault value), Where clause: empty, then I click Export it and it creates 4 KB size .dmp file, whick contain only Table names. (I check with notepad++ there is some NULL strings and TABLE : “mytable1”

TABLE : “mytable2”

TABLE : “mytable3”

TABLE : “mytable4”

TABLE : “mytable5”

…)

So for my other db which not contains BLOB, I exported Sql Insert tab, it export fine and I copy scripts to my Toad db and inserts all datas. transfer is succesfully ( or I think it is, if I am wrong please warn me about its disadvantage)

But my current DB contains Blob columns and cant convert sql inserts,and cant expdp with datas… when I edit a table that contains BLOB columns to change datatype varchar2(), it gaves me ORA-22859 invalid mofiy column error… is the reason login as normal, not sysdba ?

What should I do all, there must be a way to do this.


#9

when I export table data pump, it creates 4 KB size .dmp

You haven’t show us your expdp statement.

Please do that as well as content of that 4kB file.

Use logfile option to see the result.

Brg

Damir


#10

[mention:f5e669462ed84c2185832f0ffc80c5b8:e9ed411860ed4f2ba0265705b8793d05] The contents of log file:

bout to export specified tables via Direct Path …

. . exporting table AYARLAR

EXP-00008: ORACLE error 904 encountered

ORA-00904: “MAXSIZE”: invalid identifier

. . exporting table BILDIRIM

EXP-00008: ORACLE error 1003 encountered

ORA-01003: no statement parsed

Table BILDIRIM_DOSYALARI will be exported in conventional path.

. . exporting table BILDIRIM_DOSYALARI

EXP-00008: ORACLE error 904 encountered

ORA-00904: “MAXSIZE”: invalid identifier

Table BILDIRIM_DOSYALARICOPY will be exported in conventional path.

. . exporting table BILDIRIM_DOSYALARICOPY

EXP-00008: ORACLE error 1003 encountered

ORA-01003: no statement parsed

. . exporting table BILDIRIM_INDEKSLERI

EXP-00009 no privilige to export Gunu’s table yellow_pack

What does these means ? What should I understand for these error ?


#11
  1. Still no expdp statement in your reply (i think you use exp not expdp as advised)

  2. I assume that your error is:

The most common cause of the EXP-0008 with the 904 and EXP-00008: ORACLE error 1003 encountered code is a mismatch between the Oracle client and the database.

Because this is not Toad area (in any way) from now on please contact Oracle Forum where you can find all the answers.

Brg,

Damir


#12

Don’t start going down road of using SYS or SYSDBA

http://www.toadworld.com/products/toad-for-oracle/b/weblog/archive/2013/08/12/common-toad-security-connection-mistakes.aspx

How about you send your complete data pump export command. For example did you create a directory (database object) or use the default one? Did you say export
schemas or tables? Etc.

The data pump export should work and does not care about data types (blobs). But the export file is a binary that you cannot open in notepad – so you cannot
open it to see anything. The size of 4K and the errors means you have done something wrong along the way – which you need to figure out …


#13

Ah – you need export privs to use data pump – what privs does your user have – do they include export privs ???


#14

[mention:f5e669462ed84c2185832f0ffc80c5b8:e9ed411860ed4f2ba0265705b8793d05] Yes I did Export only as dmp files… Actually dont know how to expdp in the way you mean. can you explain step by step. ? Thank you.

[mention:228f8d2504734c859820ea0da5a1084e:e9ed411860ed4f2ba0265705b8793d05] I just click export tables button in tool menu. I think you right I need to send all necessary parameters to export, but even I dont know how to do this. Shoul I use cmd to with like that “expdp user/user tables=employees directory=test dumpfile=employees.dmp logfile=expemployees.log” or another command ?