0

In the process of moving data from live to the dev system (completely isolated), I have been given a series of "insert into" files for the affected tables.

One of these tables contains a clob that houses stupidly massive sql queries, so the file contains stupidly massive amounts of text that cannot be inserted via the insert statement...

So, the question is, how can I import the data for this table?

1
  • What kind of file did you get? Is it a data pump dump or is it a kind of CSV file?
    – jmk
    Jun 17, 2017 at 16:52

2 Answers 2

1

Since they are sql insert files you can only import them using the sqlplus utility. I'd say a dumpfile in this case is the best solution.

1

You'll need to ask for a DataPump export file of this particular table. Here's how you you do it:

create or replace directory DATA_PUMP_DIR AS '/path to dump file/';

expdp userid=SYSTEM/PW@\"localhost:1521:[source database name]\" full=n include=tableowner.tablename directory=DATA_PUMP_DIR dumpfile=tablename%U.dmp logfile=tablename_expdp.log

Then import:

create or replace directory DATA_PUMP_DIR AS '/path to dump file/';

impdp userid=SYSTEM/PW@\"localhost:1521:[target database name]\" FULL=Y DIRECTORY=DATA_PUMP_DIR CONTENT=ALL LOGFILE=tablename_impdp.log DUMPFILE=tablename%U.dmp

If the person doesn't have the system password, use the schema owner instead, or connect using os authentication: expdp userid=\"/ as sysdba\" ...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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