No question at this time
DBA Top 10
1 M. Cadot 17900
2 A. Kavsek 15900
3 M. Hidayathullah ... 9200
4 B. Vroman 8600
5 T. Boles 4900
6 P. Wisse 4700
7 J. Schnackenberg 3800
8 K. Van Nieuwenhov... 2100
9 G. Van Cauter 1400
10 A. Hudspith 800
10 T. P 800
About
DBA-Village
Download PLATO
The free tool for auditing and tuning your database
Version 55 now available
Sep 02, 2016
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48079
Total active users1704
Act. users last 24h9
Act. users last hour0
Registered user hits last week295
Registered user hits last month1831
Go up

exporting oracle tables on command line as sql files
Next thread: query
Prev thread: SQLLoader and RAC

Message Score Author Date
Hi All, Could you please advise on the followi...... Rastislav Farar Nov 07, 2017, 12:08
Hi, you can try writing a script for Oracle sql...... Ales Kavsek Nov 07, 2017, 13:33
Doesn't SQLDevelopper have this functionality buil...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts jacques roelens Nov 07, 2017, 16:08
Hi, OP asked for <b>command line</b>, that's wh...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Ales Kavsek Nov 07, 2017, 16:22
Rastislav, Why do you want actual insert statem...... Tim Boles Nov 09, 2017, 18:48
Thx Tim the final goal of this issue is just do...... Rastislav Farar Nov 10, 2017, 07:39
Rastislav, Something missing in the translation...... Tim Boles Nov 10, 2017, 17:17
ok so finnaly I've done through SQLDevelopper Tim...... Rastislav Farar Nov 13, 2017, 09:29
Hi, > .... but do not over DataPump because thi...... Score: 800 PtsScore: 800 PtsScore: 800 PtsScore: 800 PtsScore: 800 Pts Ales Kavsek Nov 13, 2017, 12:08
hi Ales, that's a really cool idea. I'll try to...... Jan Schnackenberg Nov 13, 2017, 12:28
Hi Jan, currently I'm using XE 11g to dump some...... Ales Kavsek Nov 13, 2017, 12:51

Follow up by mail Click here


Subject: exporting oracle tables on command line as sql files
Author: Rastislav Farar, Slovakia
Date: Nov 07, 2017, 12:08, 15 days ago
Os info: Linux
Oracle info: 12c
Message: Hi All,

Could you please advise on the following issue ?
I want to export/import tables as .sql* files on command line
Is there a specific command/tools for this or extra parameters for exp/expdp?
I dont want to generate DDL statements using imp/impdp utilities this isn't what i want.
This *sql file must contain table creation and insert statements.


Thank you for your help
Best Regards
Rastislav
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: exporting oracle tables on command line as sql files
Author: Ales Kavsek, Slovenia
Date: Nov 07, 2017, 13:33, 15 days ago
Message: Hi,

you can try writing a script for Oracle sqlcl tool:

http://www.oracle.com/technetwork/issue-archive/2015/15-sep/o55sql-dev-2692807.html


you can get table ddl as simple as:

sql> ddl emp;

and data formatted as insert statements with:

sql> set sqlformat insert
sql> select * from emp;


Regards,
Ales

p.s. btw. I'm not aware of any command line tool that would do this for you without your scripting effort....if you find one, please share it with us...
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: exporting oracle tables on command line as sql files
Author: jacques roelens, France
Date: Nov 07, 2017, 16:08, 15 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Doesn't SQLDevelopper have this functionality built-in ?
I used it some years ago and I guess it still exists... drawback being that generated files might be huge

Check https://docs.oracle.com/database/sql-developer-17.3/RPTUG/sql-developer-dialogs.htm#f1_idedexportobjects_html

Regards
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: exporting oracle tables on command line as sql files
Author: Ales Kavsek, Slovenia
Date: Nov 07, 2017, 16:22, 15 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hi,

OP asked for command line, that's why I suggested sqlcl. I'm sure there are "plenty" of GUI tools that can do this as well (...and I don't care about them).

You're right about the drawbacks....personally, I would rather shoot myself in the leg before I would use such approach for "my" databases, but I get this "request" surprisingly often, sometimes I'm even successful to convenience them that there are better options to move data around. I promise, I won't try in this thread ;-).

Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: exporting oracle tables on command line as sql files
Author: Tim Boles, United States
Date: Nov 09, 2017, 18:48, 13 days ago
Message: Rastislav,

Why do you want actual insert statements? Depending on the size of your table these could be huge files. Also, if you have multiple tables that have foreign key constraints indexes and other similar constructions you would have trouble using inserts.

So really what is the final goal here. Perhaps we have other ideas how to accomplish it that might be helpful.

Tim
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: exporting oracle tables on command line as sql files
Author: Rastislav Farar, Slovakia
Date: Nov 10, 2017, 07:39, 13 days ago
Message: Thx Tim

the final goal of this issue is just don't do this export/import via Data Pump because this does not under control


BR
Rastislav
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: exporting oracle tables on command line as sql files
Author: Tim Boles, United States
Date: Nov 10, 2017, 17:17, 12 days ago
Message: Rastislav,

Something missing in the translation here. Your original posting said "Is there a specific command/tools for this or extra parameters for exp/expdp? "

So now are you saying that you do not want to use exp or expdp because you don't have access to it?

tim
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: exporting oracle tables on command line as sql files
Author: Rastislav Farar, Slovakia
Date: Nov 13, 2017, 09:29, 9 days ago
Message: ok so finnaly I've done through SQLDevelopper
Tim let me explain, at first point i want to do just sql file contain table creation and insert statements from user schema but do not over DataPump because this does not under control. This was the major reason for that. The second idea was try to do over DataPump anyway but try to figure out how to make it.


thanks for your quick response
BR
Rastislav
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: exporting oracle tables on command line as sql files
Author: Ales Kavsek, Slovenia
Date: Nov 13, 2017, 12:08, 9 days ago
Score:   Score: 800 PtsScore: 800 PtsScore: 800 PtsScore: 800 PtsScore: 800 Pts
Message: Hi,

> .... but do not over DataPump because this does not under control.

I hear this quite often, we can't use datapump because we don't have access to file system on DB server.
And my answer is always the same. If this is not some restriction defined as company policy, then technically you don't really need access to file system on DB server as long as you have login access to your APP schema. If you can login to app schema, you can "dump" data locally with expdp to your workstation. How?

I have Oracle XE on my workstation on which I created some dedicated user (non DBA!), let's say APPDUMP. In APPDUMP schema I create db link to APP@DBSERVER. Then I can run something like:

expdp appdump@xe directory=my_local_dir file=f.dmp logfile=f.log schemas=app network_link=app_dbserver

What is going on is this...

Me -> XE (local WKS) <-- db link --> APP@DB
|
|-> f.dmp


Personally, I'm using a small dedicated Virtual Box VM, running Oracle XE 11g on Oracle Linux with SHARED folder configured in a way that I can dump directly to host file system, so I can dump really big files without increasing the file system size of the guest.

Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: exporting oracle tables on command line as sql files
Author: Jan Schnackenberg, Germany
Date: Nov 13, 2017, 12:28, 9 days ago
Message: hi Ales,

that's a really cool idea. I'll try to remember that. Even if XE is kind of dated by now and there are of course some restrictions regarding datapump over database links.

Regards,
Jan
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: exporting oracle tables on command line as sql files
Author: Ales Kavsek, Slovenia
Date: Nov 13, 2017, 12:51, 9 days ago
Message: Hi Jan,

currently I'm using XE 11g to dump some data from 11g and 12c R1 without a problem. The only "show stopper" so far was an attempt to dump data from EBCDIC platform (some legacy 10g database running on OS/390), due to character set conversion problem.
Anyway, I expect Oracle XE 18 (SIZE max. 12GB, SGA max. 2GB) to be released in the first half of 2018, so we can count for this technique to work in the future.

Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here