datapump 2

Enovia Test

11.16.21.1

drop user username cascade;

　

And

　

create user username (with all the parameters .can generate the create user script from TOAD)

　

And try your import (impdp) again.

　

　

　

V6R2015X/V6R2015X

　

impdp V6R2015X/V6R2015X directory=DATA_PUMP_DIR dumpfile=TESTSERVER_31Jan2018.dmp logfile=TESTSERVER_02Feb2018_EBS.log remap_schema=V6R2015X:V6R2015X_EBS

REMAP_TABLESPACE=MX_DATA15X:MX_DATA_EBS REMAP_TABLESPACE=MX_INDEX15XV6:MX_INDEX_EBS table_exists_action=REPLACE



impdp V6R2015X_EBS/V6R2015X directory=DATA_PUMP_DIR dumpfile=TESTSERVER_31Jan2018.dmp logfile=TESTSERVER_02Feb2018_EBS.log remap_schema=V6R2015X:V6R2015X_EBS

REMAP_TABLESPACE=MX_DATA15X:MX_DATA_EBS REMAP_TABLESPACE=MX_INDEX15XV6:MX_INDEX_EBS table_exists_action=REPLACE

　

In both the above cases the source tablespace (MX_DATA15X) is getting updated. Actually the import was to update destination tablespace (MX_DATA_EBS).

Request you to kindly help us with this.

　

MX_DATA15X

MX_INDEX15XV6

MX_DATA_EBS

MX_INDEX_EBS

　

/home/app/oracle/admin/orcl/dpdump/

DATA_PUMP_DIR

　

SELECT /* + RULE */ df.tablespace_name "Tablespace",

df.bytes / (1024 * 1024) "Size (MB)", SUM(fs.bytes) / (1024 * 1024) "Free (MB)",

Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",

Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"

FROM dba_free_space fs,

(SELECT tablespace_name,SUM(bytes) bytes

FROM dba_data_files

GROUP BY tablespace_name) df

WHERE fs.tablespace_name (+) = df.tablespace_name

GROUP BY df.tablespace_name,df.bytes

UNION ALL

SELECT /* + RULE */ df.tablespace_name tspace,

fs.bytes / (1024 * 1024), SUM(df.bytes_free) / (1024 * 1024),

Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),

Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)

FROM dba_temp_files fs,

(SELECT tablespace_name,bytes_free,bytes_used

FROM v$temp_space_header

GROUP BY tablespace_name,bytes_free,bytes_used) df

WHERE fs.tablespace_name (+) = df.tablespace_name

GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used

ORDER BY 4 DESC;

　

Tablespace Size (MB) Free (MB) % Free % Used

------------------------------ ---------- ---------- ---------- ----------

MX_INDEX_EBS 300 299 100 0

MX_DATA_EBS 300 299 100 0

MX_INDEX15XV6 300 299 100 0

UNDOTBS1 1355 1337.75 99 1

EXAMPLE 100 21.25 21 79

USERS 5 .8125 16 84

SYSAUX 910 58.125 6 94

MX_DATA15X 4691.6875 242 5 95

SYSTEM 770 40.6875 5 95

TEMP 216 0 0 100

　

10 rows selected.

　

/home/app/oracle/oradata/orcl/system01.dbf

/home/app/oracle/oradata/orcl/

　

select TABLESPACE_NAME, FILE_NAME,AUTOEXTENSIBLE,MAXBYTES from dba_Data_files where TABLESPACE_NAME like '__EBS ';

select TABLESPACE_NAME,AUTOEXTENSIBLE from dba_Data_files;

　

ORA-01652: unable to extend temp segment by 128 in tablespace MX_DATA_EBS;

　

alter tablespace temp add tempfile '/home/app/oracle/oradata/orcl/temp02.dbf' size 700m;

/home/app/oracle/oradata/orcl/temp01.dbf

　

　

impdp directory=DATA_PUMP_DIR dumpfile=TESTSERVER_31Jan2018.dmp logfile=TESTSERVER_02Feb2018_EBS.log remap_schema=V6R2015X:V6R2015X_EBS REMAP_TABLESPACE=MX_DATA15X:MX_DATA_EBS REMAP_TABLESPACE=MX_INDEX15XV6:MX_INDEX_EBS table_exists_action=TRUNCATE ignore=y

　

　

　

You may need to re-add object grants to the new martin schema eg

SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','SCOTT') FROM DUAL

before you remove the SCOTT schema of course.

　

　

-- Query to find all responsibilities of a user

　

-------------------------------------------------------------------------------

　

SELECT fu.user_name "User Name",

　

frt.responsibility_name "Responsibility Name",

　

furg.start_date "Start Date",

　

furg.end_date "End Date",

　

fr.responsibility_key "Responsibility Key",

　

fa.application_short_name "Application Short Name"

　

FROM fnd_user_resp_groups_direct furg,

　

applsys.fnd_user fu,

　

applsys.fnd_responsibility_tl frt,

　

applsys.fnd_responsibility fr,

　

applsys.fnd_application_tl fat,

　

applsys.fnd_application fa

　

WHERE furg.user_id = fu.user_id

　

AND furg.responsibility_id = frt.responsibility_id

　

AND fr.responsibility_id = frt.responsibility_id

　

AND fa.application_id = fat.application_id

　

AND fr.application_id = fat.application_id

　

AND frt.language = USERENV('LANG')

　

AND UPPER(fu.user_name) = UPPER('AMOHSIN') -- <change it>

　

-- AND (furg.end_date IS NULL OR furg.end_date >= TRUNC(SYSDATE))

　

ORDER BY frt.responsibility_name;

　

　

ORA-31684: Object type VIEW:"V6R2015X_EBS

EXEC DBMS_UTILITY.compile_schema(schema => 'V6R2015X_EBS');