EXPDP-IMPDP- REMAP_TABLE in same SCHEMA in Oracle
command -impdp saanjh/.. tables=test.ATTRIBUTE directory=TEST_DIR dumpfile=test.dmp logfile=impdpTEST.log remap_table=TEST.ATTRIBUTE:ATTRIBUTE_14June2023aaj
source and target schema - TEST
Schema which is being used to import - saanjh
grants on the saanjh schema - connect, resource, alter any sequence, grant 100g tablespace quota to saanjh
CONN / AS SYSDBA ALTER USER saanjh IDENTIFIED BY ....; CREATE OR REPLACE DIRECTORY test_dir AS 'C:\db_home\TEST'; GRANT READ, WRITE ON DIRECTORY test_dir TO saanjh;
alter user saanjh quota 100g on saanjh;
grant EXP_FULL_DATABASE to saanjh ; and grant IMP_FULL_DATABASE to saanjh;expdp saanjh/saanjh schemas=TEST directory=TEST_DIR dumpfile=test.dmp logfile=expdpTEST.log
impdp saanjh/saanjh tables=TEST.ATTRIBUTE directory=TEST_DIR dumpfile=test.dmp logfile=impdpTEST.log remap_schema=TEST:MANOJSHIKHAMISHRAC:\Users\0027Q6744>impdp saanjh/saanjh tables=test.ATTRIBUTE directory=TEST_DIR dumpfile=test.dmp logfile=impdpTEST.log remap_table=TEST.ATTRIBUTE:ATTRIBUTE_14June2023aaj
Import: Release 19.0.0.0.0 - Production on Wed Jun 14 18:04:25 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SAANJH"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SAANJH"."SYS_IMPORT_TABLE_01": saanjh/******** tables=test.ATTRIBUTE directory=TEST_DIR dumpfile=test.dmp logfile=impdpTEST.log remap_table=TEST.ATTRIBUTE:ATTRIBUTE_14June2023aaj
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."ATTRIBUTE_14JUNE2023AAJ" 6.812 KB 17 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SAANJH"."SYS_IMPORT_TABLE_01" successfully completed at Wed Jun 14 18:04:29 2023 elapsed 0 00:00:03
C:\Users\0027Q6744>
select owner,object_name,created from dba_objects where object_name like '%ATTRIBUTE_14JUN%' and owner not in ('SYS','SYSTEM','CTXSYS','OJVMSYS','PUBLIC','OLAPSYS','XDB') ORDER BY 3 DESC;
OWNER OBJECT_NAME CREATED
-------------- -------------------------------------- ------------------------
TEST ATTRIBUTE_14JUNE2023AAJ 2023-06-14 18:04:28
SQL> SELECT * FROM TEST.ATTRIBUTE_14JUNE2023AAJ;
ID_NO USER_NAME ATTR VALUE
---------- -------------------- -------------------- ------------------------------------------------------------
11 RLGOVEL DOMAIN NA
12 RLGOVEL DOMAIN NA
13 ABC DOMAIN NA
14 FFF GGH NA
15 DVDF DOMAIN NA
16 RLGOVEL DOMAIN NA
17 RLGOVEL DOMAIN NA
18 RLGOVEL DOMAIN NA
19 RLGOVEL DOMAIN NA
11 RLGOVEL DOMAIN BIRTP.NET
12 RLGOVEL DOMAIN BIRTP.NET
13 ABC DOMAIN BIRTP.NET
15 DVDF DOMAIN BIRTP.NET
16 RLGOVEL DOMAIN BIRTP.NET
17 RLGOVEL DOMAIN BIRTP.NET
18 RLGOVEL DOMAIN BIRTP.NET
19 RLGOVEL DOMAIN BIRTP.NET
17 rows selected.
SQL> SELECT * FROM TEST.ATTRIBUTE;
ID_NO USER_NAME ATTR VALUE
---------- -------------------- -------------------- ------------------------------------------------------------
11 RLGOVEL DOMAIN NA
12 RLGOVEL DOMAIN NA
13 ABC DOMAIN NA
14 FFF GGH NA
15 DVDF DOMAIN NA
16 RLGOVEL DOMAIN NA
17 RLGOVEL DOMAIN NA
18 RLGOVEL DOMAIN NA
19 RLGOVEL DOMAIN NA
11 RLGOVEL DOMAIN BIRTP.NET
12 RLGOVEL DOMAIN BIRTP.NET
13 ABC DOMAIN BIRTP.NET
15 DVDF DOMAIN BIRTP.NET
16 RLGOVEL DOMAIN BIRTP.NET
17 RLGOVEL DOMAIN BIRTP.NET
18 RLGOVEL DOMAIN BIRTP.NET
19 RLGOVEL DOMAIN BIRTP.NET
17 rows selected.
SQL> select owner,object_name,created from dba_objects where object_name like '%ATTRIBUTE' and owner not in ('SYS','SYSTEM','CTXSYS','OJVMSYS','PUBLIC','OLAPSYS','XDB') ORDER BY 3 DESC;
OWNER OBJECT_NAME CREATED
-------------- -------------------------------------- ------------------------
TEST ATTRIBUTE 2023-03-16 14:52:54
Comments
Post a Comment