31
impdp will create the user if it's not present yet, so you don't have to worry about it unless that's not what you want.
Do not run impdb or expdp as sysdba, only do that if Oracle support requests it in specific circumstances. Use an ordinary user for that - one that has been granted the dba role for instance. (There are the [IMPORT|EXPORT]_FULL_DATABASE ...
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 ...
4
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/...
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
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 ...
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
You need to fully understand how undo works to troubleshoot this error. I recommend this blog post:
http://blog.oracle48.nl/wordpress/oracle-database-undo-space-explained/
You can use this query to see the current profile of your Undo tablespace(s):
SELECT DISTINCT STATUS,TABLESPACE_NAME, SUM(BYTES), COUNT(*)
FROM DBA_UNDO_EXTENTS
GROUP BY ...
3
When it comes to software downloads for production use, the Oracle sites, thankfully, are a little less of a maze - provided that you are familiar with the entrance.
You can find the latest media packs for production use on the Oracle Software Delivery Cloud.
After logging in and accepting terms and export restrictions, select Oracle Database and Linux x86-...
3
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.
3
Are you sure about the target and source version?
I did here and worked right.
My source DB version was 12.1.0.2.0 and the target was 12.2.0.1.0.
Please, see my example below:
$ cat myfile.par
directory=DUMP
dumpfile=MYFILE.DMP
logfile=MYFILE.LOG
content=ALL
schemas="DIEGO","HR","USER"
$ expdp user@mypdb parfile=myfile.par
Exporting and Importing Between ...
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
I made a quick and dirty solution which worked for me today (Oracle 12.1), no reason to wait for the next 11 years.
create or replace trigger T_mutate_len_semantics after create on database
when (SYS.DICTIONARY_OBJ_TYPE = 'TABLE' and SYS.DICTIONARY_OBJ_OWNER not in ('SYS','SYSTEM'))
declare
stmt varchar2(2000);
begin
--debug
dbms_system.ksdwrt(2,'...
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
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
No you cannot. I'm not entirely sure what you are after here - I'm assuming that you want to unload a table in CSV format ? If that's the case, then an easy way would be to download sqcl
http://www.oracle.com/technetwork/developer-tools/sqlcl/downloads/index.html
It's free and small (10mb), and then its just a case of running a query against your table
...
1
This feature was requested 11 years ago, and it is still not implemented:
Bug 5034478 : ENH: ADD PARAMETER TO IMPDP TO OVERWRITE NLS_LENGTH_SEMANTICS
At the moment there is no way of moving all data, or single
columns/tables from BYTE to CHAR with expdp/impdp.
1
If you can't change your "retention policy" (aka UNDO_RETENTION), you should
search for other ways to improve your expdp performance.
You can try one (or more) of the following:
Try to higher your parallel parameter;
Use faster disks to the expdp destination;
Do not use COMPRESS, if you have few and slow CPUs.
1
I assume that the database runs on a Linux OS.
I assume that you connect with a tool like sqldeveloper to the database using a tns-connection string (maybe from a machine different)
I assume that you start expdp from the database server using not a tns-connection string but set ORACLE_SID and create a local connection.
Then, yes, it is possible that such ...
1
Do I need to increase undo_size? …
Yes, that is exactly what you need to do. The export needs a consistent MVCC snapshot from the start of the operation. UNDO_RETENTION is ignored if your UNDO tablespace is fixed size or can't be extended:
UNDO_RETENTION specifies (in seconds) the low threshold value of undo retention. For AUTOEXTEND undo tablespaces, ...
1
ROWID is the "physical" location of a row, including the internal ID of the object, file number, block number, and position in the block. This is just a pseudocolumn, and it is not exported. It is not even stored in the database. When you move a table, ROWIDs change.
So no, do not use the above.
ROWID Pseudocolumn
...
If you delete and reinsert a row ...
1
By having your database is no archive log mode and doing a daily warm backup, you are saying that is acceptable to lose a day's worth of data. If that is the case then fine, you don't need archive log mode. Doing a data pump export will give you a logical backup of your database at a point in time. The next question that you should ask yourself is that if it ...
1
Even with a long undo retention and a large undo tablespace, it's still possible to run into this if you're frequently committing while the job is running. This is because you'll be scattering your work across many undo segments, increasingly the likelihood that one ages out.
Does your job take longer than four hours thirty-five minutes? Your undo_retention ...
1
The requirement to specify a directory object is only there with the new expdp utility. The older exp tool allows specification of an absolute path.
I was able to take a backup using exp without having any special privileges and without creating any directory objects, just using my username and password.
Though succeeded by expdp, exp is still included in ...
1
Creating Directory Objects and Export/Import are two different operations. Directory Objects are owned by SYS.
A directory object maps a name
to a directory path on the file system. DBAs must ensure that only approved users are
allowed access to the directory object associated with the directory path.
If you are not a privileged user, then before ...
1
A schema export of 111 schemas is not the same as a full export that excludes all but this 111 schemas.
The full export contains roles, public synonyms, datafiles and a lot of other things.
So make a schema export of these 111 schemas.
Use a parameterfile to specify your schema names.
expdp user/password@connect parfile=parameterfile
Within a parameter ...
1
As @a_horse_with_no_name mentioned, you can use the exclude parameter to limit the objects to be exported.
It's very simple to use and it supports in/ like... operators.
Usage example:
expdp username/password directory=datapump dumpfile=x.dmp logfile=y.log full=y exclude=schema:"in ('SYS', 'SYSTEM', ..., ...)"
1
impdp SCOTT/password directory=DUMPDIR dumpfile=fromuser.dmp logfile=impdp.log remap_schema=SCOTT:DEV_LOCAL FULL=YES
DEV_LOCAL will be the local empty schema that you should create!
1
If anyone is looking for 11.2.0.4, go to this link https://updates.oracle.com/Orion/PatchDetails/process_form?patch_num=13390677
and download Oracle client (11.2.0.4) 64-bit which is p13390677_112040_platform_4of7.zip
For 32-bit you need to download p13390677_112040_platform_3of6.zip
1
You have no direct control over the order on objects creation. Anyway, you can try to perform a full export, and then, run an impdp only of the schemas you need.
For example:
expdp system/**** full=y directory=<dir> dumpfile=<dumpfile>
and then import all the schemas you want (in the order you need):
impdp system/**** directory=<dir> ...
Only top voted, non community-wiki answers of a minimum length are eligible
