Server Utilities :: Expdp Using Remap_schema Will It Also Remap Grants And Synonyms

May 1, 2010

If I would be using expdp using remap_schema will it also remap grants and synonyms ?

View 5 Replies


ADVERTISEMENT

Export/Import/SQL Loader :: Grants And Synonyms While Importing Through Data Pump

Jul 22, 2012

OS: RHEL
DB: 11.2.0.2

Every time i try to refresh my production DB with the a old expdp dumpfile using data pump i always face the issue of grants and creation of synonym. I would like to tell you that my DB has three schemas which have lots of dependencies among them and before refreshing them i drop the schemas and recreate the same.

Drop user user_name cascade;So i want to know, is there a script from which i can get all the grants of the DB before dropping the schemas, so that after import i can grant the same and also a query with which i will be able to get all the synonyms of the DB.

View 8 Replies View Related

Server Utilities :: Remap Database Schema During Export?

Mar 3, 2011

is it possible to remap the database schema during export?

Our developers have their databases stored within individual schemas and i want to provide a dumpfile that each developer is possible to easily import to his schema. But when i want to impdp the dumpfile i have to know the schema name within the dumpfile to do a remap to the individual developers schema -> so providing a specific schema name for within the dumpfile would be great.

At the moment i'm getting the ORA-39146, schema does not exist on importing the database..

View 3 Replies View Related

Server Utilities :: Synonyms Compilation Error

Sep 23, 2010

I did an export from 8.1.7 and imported in 10g. All the tables were imported. I created all the users and gave them necessary grants. when i try to compile synonyms for this schema i imported,I get the following error in enterprise manager:

Failed to compile: ORA-00980: synonym translation is no longer valid

I cannot login to my application with these users even though they have all grants. I receive the following error:

FRM-40735:PRE-FORM trigger raised unhandled exception ORA-00942

When i click ok, the screen disappears.

View 11 Replies View Related

Server Utilities :: Impdp Getting Grants And Privs

May 8, 2012

I did an export using the following parfile (see below) I want to import all the objects associated with this schema into another DB but I want don't want to over-write any of the permissions such as grants.

Is there a way I can get the grants into a sql file before I do the import. If so, provide an example.

cat exp_par

DUMPFILE=exp.dmp
LOGFILE=exp.log
DIRECTORY=DBBACKUP
schemas=t1

View 1 Replies View Related

Server Utilities :: Will Schema Export All Roles / Procedures / Packages / Synonyms / Functions And Triggers

Sep 21, 2010

i want to do a schema export from Database A. There are hundreds of users under this schema.I have to import this schema into other database say B. My question's are:

1) Do i need to pre-create only schema user or all the users under it.

2) Will the schema export all the roles,procedures,packages,synonyms,funsctions and triggers?

View 1 Replies View Related

Server Utilities :: Do EXPDP Utilities Does Backup At Block Level As What RMAN Is Doing

May 29, 2013

I have one doubt on Expdp & RMAN. Do EXPDP utilities does backup at block level as what RMAN is doing? Which one is faster, expdp or RMAN?

View 16 Replies View Related

Server Utilities :: Export Sequence Using Expdp

Apr 13, 2011

I would like to export specific tables(not entire schema) including metadata. I am using a parameter file for expdp.

Tables=emp,dept

Does this also include all metadata or should i also add the below Include in the parfile ?

INCLUDE =Indexes,Sequences,Procedures,Views

View 3 Replies View Related

Server Utilities :: Slow Expdp After Upgrade

Oct 1, 2012

We had AIX OS on 570 machine and database 10.2.0.4. We took expdp and it took 2 and hour to complete every night.

Now we upgrade to 10.2.0.5 and 770 machine and now same command takes 6 hours to complete even database and hardware is upgraded

Command is

expdp T24SILK/oracle directory=backup dumpfile=exp_beod_T24_%U_$dt
.dmp logfile=exp_T24_$dt.log EXCLUDE=TABLE:"LIKE '%TRACE'" parallel=6

View 1 Replies View Related

Server Utilities :: Expdp Hangs In Oracle 11g?

May 27, 2011

I am trying to export Schema using expdp command. but its going hang after few minutes. it seems that it stucks any where. Even I am trying with normal scott schema it is also hanging.

View 16 Replies View Related

Server Utilities :: Performance Diff In Exp And Expdp?

Jun 4, 2010

I export a table using exp utility it take 30 mins to complete the export.The same i have done in expdp utility it take 10 mins to complete the export.

How it happens?

View 3 Replies View Related

Server Utilities :: EXPDP With Query Error?

Jun 3, 2010

While trying to expdp using Query logics, getting syntax related erros shown below:

expdp system/xxxx SCHEMAS=LOG NETWORK_LINK=DBLINK1 INCLUDE=TABLE:"IN('DAILY_LOG')" QUERY=LOG.DAILY_LOG:"where entry_date< to_char(sysdate -1,'yyyymmdd')" DIRECTORY=dump DUMPFILE=log_exp.dmp logfile=log_exp.log

But gives the following error
ORA-31693: Table data object "LOG"."DAILY_LOG" failed to load/unload and is being skipped due to error:
ORA-00904: "YYYYMMDD": invalid identifier

I tried with simple sql with YYYMMDD and it works fine, the entry_date is a char field. in QUERY where i'm doing wrong here?

View 4 Replies View Related

Server Utilities :: Excluding Data Of Some Tables In EXPDP

Oct 5, 2013

i want to exclude only data of some particular tables not complete table object when exporting using expdp.

View 13 Replies View Related

Server Utilities :: Expdp Displays Output In German

Aug 26, 2012

I have a server configured to German & English. when i connect with SQLPLUS, i have German language server output, but when i do "alter session set nls_language='AMERICAN'" - it solves the issue for me.

I need the same for expdp command, but I don't know how to do this. I have tried to add a parameter nls_language, but expdp doesn't recognize it. Is it possible to somehow see server output of the expdp & writing it to the log file in English?

View 5 Replies View Related

Server Utilities :: Data Pump Expdp To ASM Daily?

Jun 16, 2011

i succeeded to expdp to ASM diskgroup such as

create directory asmexpdir as '+RECO/FILTDB/EXPDP';
grant read,write on directory asmexpdir to oraasfs;
expdp oraasfs/oraasfs2301 directory=asmexpdir dumpfile=SBSR_EXP.dmp tables=TM_SFS_CUST_01 logfile=EXPDP_LOG:SBSR_EXP.log

SUCCESS MESSAGE

. . exported "ORAASFS"."TM_SFS_CUST_01" 387.2 MB 817684 rows
Master table "ORAASFS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ORAASFS.SYS_EXPORT_TABLE_01 is:
+RECO/filtdb/expdp/sbsr_exp.dmp
Job "ORAASFS"."SYS_EXPORT_TABLE_01" successfully completed at 03:34:59

And I like to run this daily and delete after 14 days. but it show error, what can be the solution to run this script?

#!/bin/bash
#Script to Perform Datapump Export backup Every Day
################################################################
#Change History

[code]...

View 9 Replies View Related

Server Utilities :: Syntax Error In Using Query Parameter In Expdp

Aug 17, 2013

I want to take an export of table MESSAGE, and filter it for the day of 17 JUL 2013 (just to limit the size). i used the following expdp command but its not working.

expdp SYSTEM directory=DATA_PUMP_DIR dumpfile=DB_16_08_2013.dmp logfile=FA0001P_BG_16_08_2013.log TABLES=schema.MESSAGE QUERY=schema.MESSAGE:where created_on between to_date('17-July-13 00:00:00','DD-Mon-YY hh24:MI:SS') and to_date('17-July-13 23:59:00','DD-Mon-YY hh24:MI:SS')

But with select query i am able to retrieve the rows for the specific date.

select * from MESSAGE where created_on between to_date('17-July-13 00:00:00','DD-Mon-YY hh24:MI:SS') and to_date('17-July-13 23:59:00','DD-Mon-YY hh24:MI:SS')
Here is the command with syntax error.
[oracle@orcl log]$ expdp SYSTEM directory=DATA_PUMP_DIR dumpfile=DB_16_08_2013.dmp logfile= DB_16_08_2013.log TABLES=schema.MESSAGE QUERY=schema.MESSAGE:where created_on between to_date('17-July-13 00:00:00','DD-Mon-YY hh24:MI:SS') and to_date('17-July-13 23:59:00','DD-Mon-YY hh24:MI:SS')
-bash: syntax error near unexpected token `('

View 3 Replies View Related

Server Utilities :: Export Data Using Expdp To Remote Host

Jun 5, 2012

I have to servers 'A' and 'B', On Server there is a schema with the name "test" having a table "t1". I want to import this t1 table to server B.

Is it possible to export dump using expdp to remote host.

I found that there is an option for this like "network_link". for testing this, I created a dblink from Server "B" to "A" named "vxmldb".

When I am using the below command on Server B there I am getting the following error.

C:>expdp directory=data_pump_dir logfile=test.log network_link=vxmldb schemas=test dumpfile=test.dmp

Export: Release 11.1.0.6.0 - 64bit Production on Tuesday, 05 June, 2012 14:22:07
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Username: system/vxmldb@vxmldb
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39170: Schema expression 'TEST' does not correspond to any schemas.

In above command

directory ---> Server "B" location
network_link ----- > dblink name which is created on Server "B" to access Server "A"
schemas ------ > schema name which is to be exported . Exists on Server "A" DB
username/password ---- >> higher level username/password for Server "A".
@connectString ----- >> connecting to Server "A"

View 15 Replies View Related

Server Utilities :: Export / Dump With Expdp For Non-privileged Users?

May 29, 2012

We are DB users (not DBAs) and used always exp/imp bevore application upgrade.

Was googling arround and read something like "Oracle Data Pump - Time to let go of Exp / Imp". It seems exp/imp is obsolete.

Our system doesn't have "expdp" command

> find . -name expdp
>

is this because of too old SQL*Plus?

> sqlplus
SQL*Plus: Release 8.1.7.0.0 - Production on Tue May 29 16:05:28 2012
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Enter user-name: ^C^C

- does our DBA need to give us privileges to run expdp/impdp?

- is that true that a expdp/impdp dump will be on the Oracle server (not the client machine)?

View 4 Replies View Related

Server Utilities :: Using Expdp To Exclude The Stream Objects While Doing Import?

Feb 1, 2012

I am having one prod and one devl with prod having stream setup.

I have to refresh devl with prod , but if i will go by full expdp then db_links also get imported into the devl and may cause problem in devl.

Is there any other way using expdp to exclude the stream objects while doing import.

View 1 Replies View Related

Server Utilities :: Error During Expdp On Oracle 11gR2 On Solaris?

Dec 2, 2010

From some day I have this error during export data pump:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31687: error creating worker process with worker id 1
ORA-31687: error creating worker process with worker id 1
ORA-31688: Worker process failed during startup.

This error is random, if I retry after few minutes the expdp work correctly.

View 8 Replies View Related

Server Utilities :: Overwrite Existing Dump File In Expdp In Oracle 10g?

Apr 13, 2012

How we can overwrite existing dump file for expdp in oracle 10g because everytime we excute expdp and dmp file exist we get below error

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "C:scott_emp.dmp"
ORA-27038: created file already exists
OSD-04010: <create> option specified, file already exists

We have one feature in 11g reuse_dumpfiles=y ,which doesnt work in 10g, I want something which can overwrite existing dumpfile in 10g?

View 1 Replies View Related

Server Utilities :: Using Expdp / Impdp To Migrate 4 TB Database From Solaris To Linux

Aug 4, 2011

I am using expdp/impdp to migrate 4 TB database from solaris to Linux.But the import process is taking forever.

View 13 Replies View Related

Server Utilities :: Taking Export Dump Using Expdp Of Some Schema's Of Total Size Is 300GB

Mar 30, 2007

I'm taking export dump using expdp of some schema's of total size is 300GB. This is the par file:

DIRECTORY=expdp
FILESIZE=32212254720
DUMPFILE=expdp_schema01.dmp,expdp_schema02.dmp,expdp_schema03.dmp,expdp_schema04.dmp,expdp_schema05.dmp,expdp_schema06.dmp,expdp_sche ma07.dmp,expdp_schema08.dmp,expdp_schema09.dmp,expdp_schema10.dmp,expdp_schema11.dmp,expdp_schema12.dmp,expdp_schema13.d
[code]....

here one biggest schema size is 250GB and the total size of all the schema's is 300GB. The file where am taking the dump has 350GB space but even then the expdp failed saying

ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes

why it failed and how to restart it and make sure it runs successfully without error.

View 4 Replies View Related

Server Utilities :: Want To Convert Exp Script To Expdp Script

Jul 27, 2011

I am having export script which performs normal export operaion on full database. As no I want to convert the same script with expdp (datapump). So what are the changes I need to take care.As per my knowledge I have to perform following task:

1. Create a dicrectory
2. change command from exp to expdp with directory name mentioned.

View 2 Replies View Related

Server Administration :: User See Only Synonyms And Avoid Looking At Definitions

May 26, 2010

I have granted connect,resource privilege to a user 'TestU' and have create several synonyms in the schema using the public database link. What worries me is that the user 'TestU' can view the all_ and users_ dictionary views, which makes him accessible to all public database links and all user specific objects definition.

Is there a way to restrict the user to see only the synonyms and avoid looking at their definitions

View 6 Replies View Related

Backup & Recovery :: Remap Schema From Dump File To New User?

Jan 18, 2012

I am trying to remap schema from dump file to new user but my import fails and giving me error.

-----------------------------------
Using username "oracle".
Last login: Tue Jan 17 17:41:30 2012 from 192.168.100.11
[oracle@cvs ~]$ dbstart
Processing Database instance "cvsdbm": log file /home/oracle/oracle/product/10.2.0/db_1/startup.log

[code]....

View 1 Replies View Related

SQL & PL/SQL :: Synonyms Belonging To Other Schema?

Feb 25, 2012

There are plenty of synonyms say (80) and few tables say (3) exist under Jack schema. There are no records in all the 3 tables but the size of each tables shows 1MB and Schema (jack) size is 15mb.doubt it could be the synonym size.

Note:

All these synonyms belongs to other schema in same database.

View 11 Replies View Related

Create Synonyms Of Another User's Tables

Jan 10, 2013

Suppose two users (CONNECT and RESOURCE roles) A and B who work on the same project. How can B create synonyms of all tables of A without asking admin to do that? Of course, we assume that these users have the CREATE SYNONYM privilege. My problem is that B does not have access to user_tables of A, so he cannot obtain the list of A's tables to create synonyms.

View 3 Replies View Related

Grants And Privileges?

Jul 14, 2011

how to give grants and privileges that are assign to schema(clerk) to new schema(manager).

View 1 Replies View Related

Server Utilities :: Finding Versions Of Exp And Imp Utilities Of Database Server?

Feb 23, 2012

how to find the versions of exp and imp utilities of database server from windows command prompt?

Note: Currently i have 10.2.0.10 oracle software installed on my local machine.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved