1. In the target database
Get the target schema metadata and privileges.
Get the target schema metadata and privileges.
2. drop target schema.
drop user user_name cascade.
3. In the source database
Check dba_directories for the datapump directory.
4. Check datapump directory mount point has enough space or not.
note: use expdp with estimate_only to know datapunp dump size.
5. export source schema dump file to target server datapunp directory.
6. recreate target schema using target schema metadata.
7. import source schema data your target schema
impdp parfile=impdp.par
drop user user_name cascade.
3. In the source database
Check dba_directories for the datapump directory.
4. Check datapump directory mount point has enough space or not.
note: use expdp with estimate_only to know datapunp dump size.
5. export source schema dump file to target server datapunp directory.
6. recreate target schema using target schema metadata.
7. import source schema data your target schema
impdp parfile=impdp.par
export or impport errors or warnings.
-----------------------------------
1. temparary tablespace errors.
2. index or table already exist.
3. indexes are created with warnings.
4. fine grain previliges.
=========== Get DDL of USER ===========
set long 9999999
set lines 100
set page size 0
set head off
set feedback off
SPOOL MDM_ETL_STG_ddl.sql
execute dbms_METADATA.SET_TRANSFORM_PARAM
set lines 100
set page size 0
set head off
set feedback off
SPOOL MDM_ETL_STG_ddl.sql
execute dbms_METADATA.SET_TRANSFORM_PARAM
(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR',true);
select (case
when ((select count(*)
from dba_users
where username = 'MDM_ETL_STG') > 0)
then dbms_metadata.get_ddl ('USER', 'MDM_ETL_STG')
else to_clob (' -- Note: User not found!')
end ) Extracted_DDL from dual
UNION ALL
select (case
when ((select count(*)
from dba_ts_quotas
where username = 'MDM_ETL_STG') > 0)
then dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', 'MDM_ETL_STG')
else to_clob (' -- Note: No TS Quotas found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_role_privs
where grantee = 'MDM_ETL_STG') > 0)
then dbms_metadata.get_granted_ddl ('ROLE_GRANT', 'MDM_ETL_STG')
else to_clob (' -- Note: No granted Roles found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_sys_privs
where grantee = 'MDM_ETL_STG') > 0)
then dbms_metadata.get_granted_ddl ('SYSTEM_GRANT', 'MDM_ETL_STG')
else to_clob (' -- Note: No System Privileges found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_tab_privs
where grantee = 'MDM_ETL_STG') > 0)
then dbms_metadata.get_granted_ddl ('OBJECT_GRANT', 'MDM_ETL_STG')
else to_clob (' -- Note: No Object Privileges found!')
end ) from dual
/
spool off;
select (case
when ((select count(*)
from dba_users
where username = 'MDM_ETL_STG') > 0)
then dbms_metadata.get_ddl ('USER', 'MDM_ETL_STG')
else to_clob (' -- Note: User not found!')
end ) Extracted_DDL from dual
UNION ALL
select (case
when ((select count(*)
from dba_ts_quotas
where username = 'MDM_ETL_STG') > 0)
then dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', 'MDM_ETL_STG')
else to_clob (' -- Note: No TS Quotas found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_role_privs
where grantee = 'MDM_ETL_STG') > 0)
then dbms_metadata.get_granted_ddl ('ROLE_GRANT', 'MDM_ETL_STG')
else to_clob (' -- Note: No granted Roles found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_sys_privs
where grantee = 'MDM_ETL_STG') > 0)
then dbms_metadata.get_granted_ddl ('SYSTEM_GRANT', 'MDM_ETL_STG')
else to_clob (' -- Note: No System Privileges found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_tab_privs
where grantee = 'MDM_ETL_STG') > 0)
then dbms_metadata.get_granted_ddl ('OBJECT_GRANT', 'MDM_ETL_STG')
else to_clob (' -- Note: No Object Privileges found!')
end ) from dual
/
spool off;
