Solved

EXPORT in SQL-Plus

Posted on 2001-07-24
20
7,058 Views
Last Modified: 2007-12-19
Hi,

From our client (an NT Workstation), we want to lunch an export that would create a DMP file on our server (HP-Unix 11). For security reason, we're only allowed to use SQL-Plus.
 
Is there an SQL command that we could use to start an export in SQL-Plus (NOT from the OS command line)?

0
Comment
Question by:michonfr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 2
  • +7
20 Comments
 
LVL 4

Expert Comment

by:jtrifts
ID: 6312803
Have you tried issueing a HOST command from within SQL*PLUS?

Host is able to run an executable that is actually sitting on the host machine...thus mimicking running from the command line.

(I have used it to run SQLLDR successfully)

JT
0
 
LVL 1

Expert Comment

by:saschek
ID: 6312844
No, but you can invoke the export utillity with a double click too.
Or aren't you allowed to start programs on your client?

If you can connect via SQL*Plus, you can establish any access to
the db over Net8-Protocoll. The export utillity connects just this way.
It should allready be installed at your NT-client as
[ORACLE_HOME]/bin/exp.exe - if not install it!
You can launch it from the command line (best) or with double click.
-- Sven
0
 
LVL 3

Expert Comment

by:jkstill
ID: 6312918
If I were you, I would examine the security policy
regarding the use of import/export and other exe's
and talk with the security folks to help them understand
the implications of their policy.

They're certainly not affording any protection by
preventing you from using export, but allowing you
to use sqlplus.

0
Veeam gives away 10 full conference passes

Veeam is a VMworld 2017 US & Europe Platinum Sponsor. Enter the raffle to get the full conference pass. Pass includes the admission to all general and breakout sessions, VMware Hands-On Labs, Solutions Exchange, exclusive giveaways and the great VMworld Customer Appreciation Part

 
LVL 2

Expert Comment

by:jammalk
ID: 6312940
Yeah..!
You need to use the following 2 commands.
1. Host exp userid=name/passwd .... (This creates dump file on the client)
2. ftp server_name .....! (to send it to UNIX)
Let me know if it works!
Jammalk

0
 
LVL 4

Expert Comment

by:jtrifts
ID: 6313110
I think the correct syntax for host is to simply pass the command (and switches) in as a parameter (i.e. in brackets):

HOST(exp username/password@database);

You should not need to specify the location of exp assuming that Oracle settings are already in the registry...

I suspect that you also have the option to leave out the password in the call (as it is not blanked out)...then exp will probably prompt you for the password (that's the way sqslldr works too...)

As for FTP syntax...I'm not sure of that, but I'd appreciate it if you'd post it after you've got it working so we'll all know...

JT
0
 
LVL 4

Expert Comment

by:dda
ID: 6313141
HOST command will launch a program at NT box, and the question was about executing exp on the server. I think that it is impossible.
0
 
LVL 4

Expert Comment

by:dda
ID: 6313142
HOST command will launch a program at NT box, and the question was about executing exp on the server. I think that it is impossible.
0
 
LVL 4

Expert Comment

by:dda
ID: 6313143
HOST command will launch a program at NT box, and the question was about executing exp on the server. I think that it is impossible.
0
 
LVL 4

Expert Comment

by:dda
ID: 6313159
Sorry for multiple input, our proxy is getting mad.
0
 
LVL 4

Expert Comment

by:jtrifts
ID: 6313161
dda...

you can run exp80 on a client machine though...or am I missing something here?
0
 
LVL 4

Expert Comment

by:jtrifts
ID: 6313173
ahhh....they want to launch it on the client but have the export occur on the server...

That may indeed be impossible...

When you run an export, can you specify the output directory?

If so, then you could set up a Samba share on the client pointing to the server, run the exp using HOST in SQL*PLUS, writing to the Samba share...

BUt don't ask me about Samba as I always get Tech Svcs to set them up...
0
 
LVL 4

Expert Comment

by:dda
ID: 6313192
This is all network solutions, and they want to do server-based export. So I think that is impossible.
0
 
LVL 1

Expert Comment

by:leolkk
ID: 6313220
i want to know too
0
 

Expert Comment

by:ianand
ID: 6313437
Hi
I don't think there is any such facility available. But you can try a roundabout way.

Steps
1.  Create a SHELL script in Unix as below and run on server at startup.
## Start of Shell script
while [ "a" = "a" ]
do
  i=`cat abc.txt`
  if [ $i = "1" ]
  then
    exp username/password@database ...
    echo "0" > abc.txt
  else
    sleep 5    # Check after every five seconds
  fi
loop
## End of Shell script

2.  Create a file abc.txt with permissions 666 containing character "0".
3.  Use UTL_FILE in PL/SQL to overwrite the file abc.txt to "1" whenever you want the export to run.
    example : run_export.sql
    DECLARE
      g_file_ptr     utl_file.file_type ;
    BEGIN
      g_file_ptr := fopen('<directory>','abc.txt','w');
      utl_file.put_line(g_file_ptr,'1');
      utl_file.fclose(g_file_ptr);
    END ;
    /

This will help you in a roundabout way to run your export.

Best of luck.

Anand

0
 

Expert Comment

by:ianand
ID: 6313452
Hi,

Just to add ...

Steps 1 and 2 are to be run in the Server.
Step 3 is to be run in the Client SQL-Plus.

Anand
0
 
LVL 4

Expert Comment

by:dda
ID: 6314045
ianand: all this stuff require too much privilegies, I suppose, they do not have shell access at all :)
0
 

Author Comment

by:michonfr
ID: 6314170
I realy need an SQL command. We don't have the privilegies to use the command line and the space to create an export on the client side.
0
 
LVL 4

Expert Comment

by:dda
ID: 6314209
I guess that you should ask server admin to create some cron job for you to perform export. Anyway, what is the benefit of having export file on server without access to that file?
0
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 30 total points
ID: 6317913
If you have the Oracle utilities (export, import, SQL*Loader) installed on the client, and if it is the same (or higher) Oracle version than your server's Oracle version then yes, you can run export on the client.  And yes, under some circumstances you can create the dump file on the server, but this is true only if your client can map a drive to a directory on the server and if your client has write privileges in that directory.  There is also the issue of network traffic though with this approach.

No, you cannot from SQL*Plus on a client launch Oracle export to run on a separate Oracle server.
0
 
LVL 1

Expert Comment

by:ishick
ID: 6326760
One of the way to create data on other server  from SQL  is simple copy command

copy from system/password@<tnsnames alien> to system/password@<tnsmanes> -
append <owner>.<table_name> -
using -
select * from <owner>.<table_name>
To run this command open sql plus where data needs to be insert
you may create dynamic sql to populate # on copies or create hard sql

Thanks


0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Suggested Courses

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question