Export or Import for the single table data/metadata in Oracle

Export the single table with data and metadata in Oracle

-- Only rows:
exp hr/hr@xepdb1 file=dumpfilelocation log=logfilelocation tables=employees rows=y

-- With metadata and rows:
exp hr/hr@xepdb1 file=dumpfilelocation log=logfilelocation tables=employees

Import the single table with data and metadata in Oracle

--If table already exists then use ignore=y
imp hr/hr@xepdb1 file=dumpfilelocation log=logfilelocation tables=employees ignore=y  

--if import into another schema then simply run with that username schema or use fromuser or touser
--- insert into scott user then run from scott user:
imp scott/tiger@xepdb1 file=dumpfilelocation log=logfilelocation tables=employees

--use fromuser touser as dump take from HR user transfer to scott user
imp system@xepdb1 file=dumpfilelocation log=logfilelocation tables=employees fromuser=HR touser=Scott

Example: Take single user backup

C:\>exp hr/hr@xepdb1 file=C:\dbbackup\hr.dmp log=c:\dbbackup\hr.log tables=employees rows=y

Export: Release 21.0.0.0.0 - Production on Fri Apr 14 15:19:27 2023
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                      EMPLOYEES        107 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.

Example: Import into the same table in thesame user from which export taken. If table already exists then it will give error that table already exists to overcome this we need to place ignore=y in export statement.

:\>imp hr/hr@xepdb1 file=C:\dbbackup\hr.dmp log=c:\dbbackup\hr.log tables=employeesbkp  ignore=y

Import: Release 21.0.0.0.0 - Production on Fri Apr 14 17:45:49 2023
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

Export file created by EXPORT:V21.00.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing HR's objects into HR
. importing HR's objects into HR
. . importing table                 "EMPLOYEESBKP"        107 rows imported
Import terminated successfully without warnings.

Import to other user: Like we took backup from HR schema but when we need to import into another schema then we use fromuser and touser to import table in another schema.

C:\>imp file=C:\dbbackup\hr.dmp log=c:\dbbackup\hr.log tables=employeesbkp fromuser=hr touser=scott ignore=y

Import: Release 21.0.0.0.0 - Production on Fri Apr 14 17:54:45 2023
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.

Username: sys@xepdb1 as sysdba
Password:

Connected to: Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

Export file created by EXPORT:V21.00.00 via conventional path

Warning: the objects were exported by HR, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
IMP-00403:

Warning: This import generated a separate SQL file "c:\d\hr_sys.sql" which contains DDL that failed due to a privilege issue.

. importing HR's objects into SCOTT
. . importing table                 "EMPLOYEESBKP"        107 rows imported
Import terminated successfully with warnings.