Hot answers tagged

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


7

Use expdp and impdp, in combination with the remap_schema option. For example... Firstly, make sure the target user/schema is already created. It may also temporarily need the IMP_FULL_DATABASE role, but I haven't tested this. Create a directory object to hold the dump files and logs (make sure this exists on your filesystem): create directory DUMPDIR as ...


5

The error you receive means that the user ANC does not have any quota on the TOOLS tablespace, as FreshGrinchOfSO says. From your code, it seems that your default permanent tablespace is TOOLS (and not USERS as usual). SELECT * FROM database_properties WHERE property_name LIKE 'DEFAULT%TABLESPACE'; PROPERTY_NAME PROPERTY_VALUE ...


5

Well you can stop the job and make changes in the table and later continue the job either skipping the current object or not. Demo: [oracle@localhost]$ impdp jay/password directory=dp_dir tables=imptest, imptest2 content=data_only dumpfile=exp_jay_imptest.dmp ... ORA-02374: conversion error loading table "JAY"."IMPTEST" ORA-12899: value too large for ...


5

Data Pump utilities are installed automatically during the installation of Oracle Database. It should be working without any issues as long as the environment variables(PATH, ORACLE_HOME) are set properly. To set PATH and ORACLE_HOME variable permanently- 1) Log in as oracle user in the OS. 2) [oracle@ora12c Desktop]$ vi ~/.bash_profile ORACLE_BASE=/u01/...


5

It's not stuck. It just does not print every rebuilded index. You can view status of the process using DBA_RESUMABLE. select * from dba_resumable;


5

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.


4

If you run export command against live database without parameters like flashback_scn or flashback_time which ensures consistency during the export, then this can be an expected behavior because these objects are exported in different time and one can become inconsistent with another. For example, if the sequence object is exported before the table and the ...


4

During the impdp process, it doesn't import indexes, in fact it runs number of CREATE INDEX statements. There could be various reasons of slow index creations such as inefficient PGA memory allocation and IO performance of underlying storage. Parallel(depending on available server resources-CPU,IO) option could improve performance of index-creation. ...


4

Of course Oracle did not make this information public, but if you can access MOS, then go ahead: Export/Import DataPump Parameter VERSION - Compatibility of Data Pump Between Different Oracle Versions (Doc ID 553337.1) 12.1 uses dump file sets of version 4.1. 12.2 uses dump file sets of version 5.1 You can not import a dump file created in 12.2 to 12.1, ...


3

To respond to your comment, all is the default value of content keyword, so you can ignore this one. Anyway, I have done some tests. I don't know how is structured your old database, but from your question I note that you want to import only two tablespaces (TAB_ANC, IDX_ANC) and with all object/schemas within them. If you want to do this, you can perform ...


3

No, that is not a possible way: Most of the non-table objects will already exist in your schema. You already get such error messages. The import will not overwrite it. You can configure how export should work if a table already exists but if there is a table that s referenced by another table then TRUNCATE and REPLACE will not work. Even if there is ...


3

I haven't found any impdp commands that would allow you to drop existing schema prior to importing data. But you should know that Data Pump command-line utilities are based around PL/SQL packages DBMS_DATAPUMP and DBMS_METADATA. Thus you may well write your own PL/SQL scripts to automate your data move calling programs in DBMS_DATAPUMP directly. And since ...


3

Data Pump uses directory objects. Read this: Default Locations for Dump, Log, and SQL Files First create the directory object as: create directory dump_dir as '/home/oracle/rmanscripts/dumbbackup'; Then you can use that in your impdp command: impdp full=y direcotry=dump_dir dumpfile=sysfull.dmp logfile=full_db.log Also: while database is not mounted ...


3

A full export/import contains tablespace definitions with datafile paths as well, so impdp tries to create the datafiles with the original path. Just create the tablespaces manually before running impdp, that is usually what I do. impdp will notice that the tablespaces already exist, and continue with the remaining objects. You can extract the tablespace ...


3

There is one trap - if you drop schema - you lost all grants to other schema objects that schema had - and some of vievs/procedures/functions which depends on objects in other schemas - will not compile. So - before you drop - you should prepare some scripts for rebuilding that object grants. select * from dba_tab_privs where grantee='<schema-for-drop/...


2

For importing the truncated tables, i.e you only want the data to be imported back to the table: impdp user/pass TABLES=dept DIRECTORY=TEST_dir dumpfile=TEST.dmp logfile=expdpTEST.log TABLE_EXISTS_ACTION=REPLACE Above my table is dept which I want to import. I have truncated it earlier. From the dumpfile which is TEST.dmp, and logfile which is expdpTEST....


2

From Oracle Data Pump utilities: Dump files are read and written directly by the server and, therefore, do not require any data movement to the client. impdp/expdp unlike imp/exp does not move the data. They only invoke DBMS_DATAPUMP package and actual data movement is done by the Oracle instance. So data pump can access external data the same way as all ...


2

Basically your database has stalled because it has ran out of space to store archive logs, and needs to switch one. To remedy, delete the generated logs (assuming you don't need them for backup and recovery purposes), then execute (as SYSDBA): alter system archive log all; alter system switch logfile; If you don't need to run your database in archivelog ...


2

Have you looked at the DBMS_METADATA option? http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_metada.htm#i1019414 This gives you the schema for objects. It is fairly easy to take the results of this and do what you want with it. An example to get the tables and views of a user would be: set serveroutput on; declare theSchema VARCHAR2(30) := '...


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

You need to move the dump-file to an area where the database has access to it referenced inside the database as a directory object. You can run impdp from your local computer.


2

Should be possible, provided the user that the DB service is running as can access the file share. If the above is true, then you would have to create the directory object: create directory MYIMPORTDIR as '\\mycomp\mydir'; then run your impdp like: impdp [username]/[pass] directory=MYIMPORTDIR dumpfile=expdat_1234.dat


2

One way to find the cause of ORA errors is to use oerr tool by Oracle. In the error stack the top most is the application error which is followed by other secondary errors. For example, the ORA-39002 and ORA-39070 are Datapump errors. Lets see what the oerr tool says for ORA-39070. [oracle@localhost ~]$ oerr ORA 39070 39070, 00000, "Unable to open the ...


2

Fromuser and touser are not valid using datapump. If you are trying to change the schema, you can use remap_schema: REMAP_SCHEMA=source_schema:target_schema https://docs.oracle.com/database/121/SUTIL/GUID-619809A6-1966-42D6-9ACC-A3E0ADC36523.htm#SUTIL927 Or you can list which schemas to import: SCHEMAS=schema_name [,...] https://docs.oracle.com/database/...


2

Typically caused by the segment attributes of the tables. You can use TRANSFORM=SEGMENT_ATTRIBUTES:N, so the tables will be created with default options. You can use TRANSFORM=SEGMENT_CREATION:N as well, so your segments will not be created at all, unless deferred segment creation is disabled in your database. TRANSFORM DEFERRED_SEGMENT_CREATION


2

Known issue: https://community.oracle.com/thread/4125750 ORA-39006, ORA-39065 and ORA-00942: table or view does not exist When Starting DataPump in 12.2 (Doc ID 2341607.1) Rebuild the missing part by running: catalog.sql catproc.sql utlrp.sql


2

The new database can have a different directory structure and even storage system, but you'd need some preparation before running the import. Especially, you'd need to create the tablespaces in advance with the correct (new) storage parameters (and of course the required size). Then impdp would not have to create them (an error might still be shown, but ...


2

You are right when stating I thought that by using INCLUDE=TABLE only the tables would be included, but it includes all related objects too. This means that constraints and triggers will be created for you when you use INCLUDE=TABLE when importing. Possible Solution If you have the possibility to re-create the dump using the expdp command then you might ...


2

https://docs.oracle.com/cd/B19306_01/server.102/b14215/dp_import.htm impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp SQLFILE=dpump_dir2:expfull.sql


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