Hot answers tagged

9

Yes, by using # at the start of a line. Actually everything after the # sign will be ignored, so this can be used after a parameter as well. This is a bit hidden in the manual: Table names specified on the command line cannot include a pound sign (#), unless the table name is enclosed in quotation marks. Similarly, in the parameter file, if a table ...


7

Use the sqlfile= parameter of impdp to generate a file containing all of the DDL/DML in the dump. For example: [oracle@oel61 ~]$ impdp phil/phil directory=oracledmp dumpfile=phil.dmp logfile=phil.log sqlfile=philddl.txt Import: Release 11.2.0.2.0 - Production on Wed Mar 13 15:15:03 2013 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights ...


6

Ionice can limit disk usage Take a look at the ionice utility for linux, as it seems to suit your needs quite well.


4

path to data files is included in full dump. No, SIDs don't have to match You may want to add REMAP_DATAFILE option (https://docs.oracle.com/database/121/SUTIL/GUID-7C9DDBED-91AC-4FA2-AB91-1FF214DA89B0.htm#SUTIL926 ) to impdp . Alternatively, you can just pre-create all tablespaces on target database.


4

Short answer: CREATE TABLE must be explictly granted to the object owner for DBMS_DATAPUMP to be called from within a package or stored procedure. Long Answer: In an effort to solve this question, I interrogated the data dictionary to get all of the object and system privileges granted by the IMP_FULL_DATABASE and EXP_FULL_DATABASE roles (and all roles ...


3

A DMP file is just a copy of the whole database contents. During the Import, it does not get modified, so you can use that file to "jump back" to the time when that dump file was created as often as you want.


3

Just import your data into a a staging table at the target database. Then write a SQL staement to copy the rows from the staging table into the target table while giving the two new columns an explicit value. So something like this; The following syntax should work on 11g impdp user/password directory=import_directory dumpfile=data.dmp REMAP_TABLE=...


2

Send it over the network While extensive searching found no way to limit disk io natively (and serverFault will probably have more details on that) a "natural" way of limiting IO is to send the output directly across the network without ever touching the local disk. This then allows you to take advantage of all the network IO throttling resources out there. ...


2

Datapump is much more efficient than exp: Datapump runs within the Oracle server processes, and can read directly from the database files and write directly to a file on the server. As I understand it, data access is direct, not via SQL. exp uses the ordinary Oracle Call Interface (OCI) -- the library that Oracle provides to connect to the database. It can ...


2

Check the Oracle Utilities documentation. When using Datapump tools, you can reset paths using the REMAP_DATAFILE parameter for import jobs. Also, insure your OS session variables reflect the new DB. This is often over-looked when working with a new DB on a server that already has a DB working on it.


2

This is expected behaviour. FULL A full export does not, by default, export system schemas that contain Oracle-managed data and metadata. Examples of system schemas that are not exported by default include SYS, ORDSYS, and MDSYS. 12.1.0.1 EXPDP Does Not Export APEX Schema (Doc ID 1912162.1) This is the expected behavior in 12.1.0.X version, ...


2

You should be using the system user for full datapump export/import operations. Using "/ as sysdba" or "sys as sysdba" may give you unpredictable results. Do not start Export as SYSDBA, except at the request of Oracle technical support. SYSDBA is used internally and has specialized functions; its behavior is not the same as for general users. ...


2

You can drop export/import job using the following command First of all find the name of the job table under 'DataPump>Import Jobs or Export Jobs'. Use the drop command to drop that table by the user who has created that import/export job. In my case it was created by SYSTEM user. SQL>conn system/password SQL>DROP TABLE EXPORT_JOB_SQLDEV_41; Table ...


1

Basically Advanced Replication works with materialized views, materialized view logs and triggers. To replicate data from one side to another, you need to have INSERT, UPDATE or DELETE statements to make the replication mechanism recognize that there is new/changed data. When you import data with direct path method you skip all this stuff. This means you ...


1

Filtering options in Data Pump are still something that should be improved. Anyway, you could try the below parameters: directory=data_pump_dir dumpfile=dump.dmp logfile=export.log schemas=schema1,schema2,schema3 exclude=TABLE_DATA:" IN (select table_name from dba_tables where owner in ('SCHEMA1', 'SCHEMA2') union select partition_name from ...


1

it never happens. You should check your script. If you are using impdp utility, there is no option for deleting the file after the import is done.


1

if you grant privileges (as you mention above), the problem may seem to go away, but your code executing in SCHEMA_B may be accessing data from tables in SCHEMA_A, unless that is what you want. Unfortunately there are no easy answers to this problem, since remap_schema option of datapump doesn't really support what you are trying to do. However you may try ...


1

That error is most likely due to incorrect environment settings indeed, probably your PATH is mixed up too. In a command prompt, assuming that your Oracle 11g home is indeed as you posted above, run: set ORACLE_HOME=C:\app\Administrator\product\11.1.0\db_1 set ORACLE_SID=ORCL11R1 set PATH=%ORACLE_HOME%\bin;%PATH% Make sure you can connect with sqlplus, ...


1

If I read the error message correctly, it also states that the dump files you use for import, do not have content for the schema name you specified. These dump files likely contain content associated with different schema and Oracle (more specifically IMPDP) won't import that content. When you install the Oracle client software, it comes with a graphical ...


1

When I run into issues like this, I like to simplify the command as much as possible. I would try putting all of the parameters directly in the command line and removing the parameters that are not required. And I would use a dba user to do it. Something like this. expdp {dbausername}/{dbapassword} SCHEMAS=OFDAT DUMPFILE=dev_ofdat_2-1-3-0_afterbackfill....


1

De-tune the datapump The docs list a number of methods for improving the performance of a data pump, including suggested alterations to the SGA, and considerations about encryption and compression. By inverting this advice, especially about compression and SGA, and then limiting CPU usage, IO usages may be reduced as a consequence.



Only top voted, non community-wiki answers of a minimum length are eligible