12

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 ...


8

This is caused by transform=DISABLE_ARCHIVE_LOGGING:Y. Because of that parameter, the database creates indexes with NOLOGGING property to avoid generating redo, and at the end, it changes the index to the original value, which is in this case LOGGING. But for indexes on GTTs, specifying NOLOGGING or LOGGING is not supported. Data Pump manages to create the ...


8

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 ...


5

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 ...


5

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.


5

If you look up the description of error ORA-39324 in the Oracle documentation you will see: Cause: A dump file was specified for a load operation that had been created using the Data Pump. This type of dump file cannot be processed by external tables using the ORACLE_DATAPUMP access driver. Action: Access the data in this dump file using the Data ...


5

There could be many reasons. But generally, it may be due to: Free space in data files or temporary files Undo data Row chaining and migrating Data blocks fragmentation


3

Data Pump is a fully integrated feature of Oracle Database 11g. Data Pump is installed automatically during database creation and database upgrade...more It should be installed during the database creation. I found logical error in your query to check whether or not the data pump package is installed. The object type PACKAGE should be in upper case to ...


3

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, ...


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=...


3

The PAR file is a parameter file, which contains parameters for data pump that you don't need to enter on the command line. Without knowing what those are, we can't advise what you need to use in any detail. Generally speaking, if you have a parameter file, you just put that on the command line: impdp user/pass parfile=/path/to/parfile However, there may ...


2

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, ...


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

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

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 ...


2

impdp is able to create the tablespaces. You can even change the location of the datafiles if needed. Check the documentation for more information since there are many parameters with this tool (REMAP_DATAFILES and REUSE_DATAFILES for example).


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. Source:...


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 dropped.


2

There is no such one command to do this kind of migration of a schema because as you mentioned, Oracle Express edition doesn't support all the features that are used in the Enterprise edition. However, I tried to collect some of the useful parameters which can be used during expdp/impdp to modify or include/exclude (un)wanted objects and features. ...


2

You can use Oracle Datapump to perform export-import in schema(user) level. Here are some examples. expdp scott/tiger schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log The above command exports SCOTT schema in a dump file called SCOTT.dmp in a directory specified using DIRECTORY parameter, and writes it's log in expdpSCOTT.log ...


2

I don't think there is a "Replace" option for non-table objects like procedures, packages etc. The best option would be to drop the schema entirely before the datapump import. This way, datapump will re-create the schema and all of the contained objects.


2

Check the size of your temp tablespace and the redo tablespace. The reduction may come from there.


2

It is enough to specify the schemas parameter. expdp \'/ as sysdba\' schemas=siddhu If you want to export only the tables, you can also add include=table: expdp \'/ as sysdba\' schemas=siddhu include=table The above exports the tables with indexes and triggers, but it skips procedures, sequences and other objects not directly related to tables.


2

So in the end it's just a time issue you have. You would have to open up a ticket with Oracle if you want to speed things up. This is probably not an Oracle issue, but possibly an issue with your database design. You have to understand that the Data Pump Export Utility has to determine all relevant constraints and relations before it can create a valid ...


1

You do not need to enumerate each object you want to export/import, you can pass a query that returns the list: Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> create table t1 as select * from dba_objects; Table created. SQL> create table tables_to_export(table_name varchar(128 char)); Table created. ...


1

[oracle@orcluat ~]$ oerr ora 01555 01555, 00000, "snapshot too old: rollback segment number %s with name \"%s\" too small" // *Cause: rollback records needed by a reader for consistent read are // overwritten by other writers // *Action: If in Automatic Undo Management mode, increase undo_retention // setting. Otherwise, use larger rollback ...


1

Check the documentation of the current database release Oracle Database Error Messages 12c Release 2 (12.2) E49883-13 In the documentation of the current release you will find only ORA-01775: looping chain of synonyms This is not helpfull (The 11.2 documentation does not contain the message at all). In this case check the documentation of the 9i ...


1

No, there is not. You have already defined manually the filters for each table, why not use that? Just dynamically generate the parameterfile before running expdp: select 'QUERY=' || table_owner || '.' || table_name || ':"' || where_condition || '"' from t_definition; QUERY=DEV.TBL1:"WHERE CMP_ID=0" QUERY=DEV.TBL2:"WHERE 1=0" QUERY=DEV.TBL3:"WHERE SEQ_ID=...


1

Oracle XE has an 11GB data limit. You need to upgrade to Standard Edition.


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