Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 63 Insurance
- 536.4K On-Premises Infrastructure
- 138.3K Analytics Software
- 38.6K Application Development Software
- 5.8K Cloud Platform
- 109.5K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71.1K Infrastructure Software
- 105.3K Integration
- 41.6K Security Software
EXPDP and IMPDP via SQL*Net
IMPDP and EXPDP are undoubtedly amazing evolutions of ancient IMP/EXP.
However, why did Oracle remove the capability of generating remote dumps via SQL*Net? I don't want to backup to a local directory!
Many places still use the original export simply because the DBA does not have access to the server, or the server does not have sufficient free space, or many other limitations related do the DB server.
Please oracle, give me the option to run expdp transferring the data via Oracle Net and we can definitely say goodbye to exp! =]
Comments
-
Hi,
Workaround: install a XE database on your client and create database link.
I know... a ligher solution can be good...
Regards,
Franck.
-
Why do you want an entire export file on your local system. Shouldn't it be more helpful if you can export single table or tables and relations directly on the client, e.g. with SQL Developer or SQL*Plus?
-
"because the DBA does not have access to the server"
Are you kidding?
"server does not have sufficient free space"
You can use a compressed datapump dump file, and remember compression isn't possible with the exp/imp utilities.
Really if you want a dumpfile as a backup, then you should be using RMAN for backups, and the backup can also be compressed.
-
"because the DBA does not have access to the server"
Are you kidding?
"server does not have sufficient free space"
You can use a compressed datapump dump file, and remember compression isn't possible with the exp/imp utilities.
Really if you want a dumpfile as a backup, then you should be using RMAN for backups, and the backup can also be compressed.
"Are you kidding?"
No. This is called "Layered security Architecture". The Infra OS Team is not the same as the DBA Team. They have some constraints on OS layer, like avoiding any user (including the DBA) from accessing the FS (unless needed for maintenance).
You can use a compressed datapump dump file, and remember compression isn't possible with the exp/imp utilities.
Not native, but it is possible using pipe. Datapump does not work via SQL*Net.
Really if you want a dumpfile as a backup, then you should be using RMAN for backups, and the backup can also be compressed.
I have physical backups. The point of logical backup is to fast restore a single object, or a line of a table or to check how the object was in the previous day.
-
"Are you kidding?"
No. This is called "Layered security Architecture". The Infra OS Team is not the same as the DBA Team. They have some constraints on OS layer, like avoiding any user (including the DBA) from accessing the FS (unless needed for maintenance).
You can use a compressed datapump dump file, and remember compression isn't possible with the exp/imp utilities.
Not native, but it is possible using pipe. Datapump does not work via SQL*Net.
Really if you want a dumpfile as a backup, then you should be using RMAN for backups, and the backup can also be compressed.
I have physical backups. The point of logical backup is to fast restore a single object, or a line of a table or to check how the object was in the previous day.
1) The DBA needs access to the Unix account where Oracle is installed. Our Unix team allows us to use 'sudo su - oracle' to access the Oracle account. All DBAs should be allowed to do this.
2) Native compression using COMPRESSION=ALL clause in the datapump export.
3) To fast restore a single object the dump file needs to be on the local volume. If the dump file is located remotely you'll get a slow restore.
-
1) The DBA needs access to the Unix account where Oracle is installed. Our Unix team allows us to use 'sudo su - oracle' to access the Oracle account. All DBAs should be allowed to do this.
2) Native compression using COMPRESSION=ALL clause in the datapump export.
3) To fast restore a single object the dump file needs to be on the local volume. If the dump file is located remotely you'll get a slow restore.
Hi,
Just a warning about the fact that COMPRESSION=ALL requires Advanced Compression Option to be licenced.
Regards,
Franck.
-
You can use NETWORK_LINK based Data Pump transfers (over SQL*Net based database link). Mind the restrictions like LONG data types.
Regards,
Pavel
-
You can do this:
Export remotely using DB link:
expdp system/oracle NETWORK_LINK=PROD SCHEMAS=SH DIRECTORY=EXPORTS DUMPFILE=prod_sh.dmpImport directly into DB from source without need of dump file:
impdp system/oracle NETWORK_LINK=PROD SCHEMAS=SH REMAP_TABLESPACE=EXAMPLE:USERS DIRECTORY=EXPORTSHope this helps
ZedDBA
-
You can use NETWORK_LINK based Data Pump transfers (over SQL*Net based database link). Mind the restrictions like LONG data types.
Regards,
Pavel
This option will work fine. It doesn't require creating local directory.
-
Please remember, that there are lots of situations where expdp is NOT working over a database links.
For example when the source database is using long columns (i know longs are deprecated, but the still exist in lots of applications).
With the old exp/imp i could "outsource" copies of application-data to test-databases or for investigation to the applications owner.
With datapump i don't know a way to do this without creating stored procedures, NFS-mounts and so on.
It's a lot more complicated.