Skip to Main Content
  • Questions
  • i want to export only the objects owned by given schema using EXPDP

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: October 13, 2021 - 9:42 am UTC

Last updated: October 28, 2021 - 2:53 am UTC

Version: 11.2

Viewed 1000+ times

You Asked

Hi,

i want to export only the objects owned by given schema using EXPDP, but unfortunately it is exporting all the objects which schema has access to.

i am using below syntax.

expdp school/school@orcl schemas=(demo) directory=DATA_PUMP_DIR dumpfile=demo.dmp logfile=expdp.log


Please help.

Thanks
Fahd

and Connor said...

I think you might be mistaken

For example.

I created an object in my DEMO schema and gave ALL privileges on it to schema SCOTT

SQL> create table demo.customers ( x int );

Table created.

SQL> grant all on demo.customers to scott;

Grant succeeded.

SQL> create synonym scott.customers for demo.customers;

Synonym created.



Now when I export the SCOTT schema, I still do not get a copy of the customers table

C:\>expdp scott/tiger@db19_pdb1 schemas=(scott) directory=TEMP dumpfile=scott.dmp logfile=scott.log

Export: Release 19.0.0.0.0 - Production on Thu Oct 21 10:11:36 2021
Version 19.12.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/********@db19_pdb1 schemas=(scott) directory=TEMP dumpfile=scott.dmp logfile=scott.log
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
. . exported "SCOTT"."EMP"                               8.781 KB      14 rows
. . exported "SCOTT"."DEPT"                              6.031 KB       4 rows
. . exported "SCOTT"."SALGRADE"                          5.960 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  X:\TEMP\SCOTT.DMP
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Oct 21 10:12:23 2021 elapsed 0 00:00:46


We'd need to see you export log if you have a different experience

Rating

  (7 ratings)

Comments

A reader, October 21, 2021 - 11:12 am UTC

export log is kind of same, but when i import it is importing another database it is trying to import even apex schema objects also.
Connor McDonald
October 22, 2021 - 3:15 am UTC

We'd need to *see* your logs

ok, this is log

A reader, October 22, 2021 - 10:01 am UTC

*** string too long, truncated *** (31592), ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Microsoft Windows [Version 10.0.17134.1]
(c) 2018 Microsoft Corporation. All rights reserved.

C:\Users\Dell>"C:\Users\Dell\Desktop\Export School Users.lnk"

C:\darearqam>expdp school1/sc@orcl schemas=(aone,awant,dairy1,dairy2,dairy3,dairy_admin,demo,dev,falconsalt,flows_files,home,logger,school1,school10,school11,school12,school13,school14,school15,school16,school17,school18,school19,school2,school20,school21,school22,school3,school4,school5,school6,school7,school8,school9,school_admin) directory=DATA_PUMP_DIR dumpfile=users_2021_22_10_144432.dmp logfile=expdp.log

Export: Release 11.2.0.1.0 - Production on Fri Oct 22 14:44:32 2021

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

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
Starting "SCHOOL1"."SYS_EXPORT_SCHEMA_01": school1/********@orcl schemas=(aone,awant,dairy1,dairy2,dairy3,dairy_admin,demo,dev,falconsalt,flows_files,home,logger,school1,school10,school11,school12,school13,school14,school15,school16,school17,school18,school19,school2,school20,school21,school22,school3,school4,school5,school6,school7,school8,school9,school_admin) directory=DATA_PUMP_DIR dumpfile=users_2021_22_10_144432.dmp logfile=expdp.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 818.2 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/EVENT/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
. . exported "DAIRY1"."AUDIT_TRANS" 95.51 KB 713 rows
. . exported "DAIRY1"."STORE_DETAIL" 272.4 KB 6808 rows
. . exported "SCHOOL1"."FEESLIP" 16.33 MB 229942 rows
. . exported "SCHOOL10"."FEESLIP" 565.9 KB 7174 rows
. . exported "SCHOOL15"."FEESLIP" 31.35 KB 232 rows
. . exported "SCHOOL16"."FEESLIP" 572.4 KB 7248 rows
. . exported "SCHOOL17"."FEESLIP" 165.9 KB 2031 rows
. . exported "SCHOOL18"."FEESLIP" 13.75 KB 1 rows
. . exported "SCHOOL21"."FEESLIP" 192.0 KB 2218 rows
. . exported "SCHOOL22"."FEESLIP" 160.1 KB 1796 rows
. . exported "SCHOOL6"."FEESLIP" 396.8 KB 5115 rows
. . exported "SCHOOL7"."FEESLIP" 454.6 KB 5635 rows
. . exported "SCHOOL_ADMIN"."REPORTS_LOG" 15.98 MB 184875 rows
. . exported "DAIRY1"."TRANS_MASTER" 405.2 KB 5702 rows
. . exported "DEV"."FEESLIP140115" 8.459 MB 128947 rows
. . exported "SCHOOL2"."FEESLIP" 1.706 MB 24534 rows
. . exported "SCHOOL3"."FEESLIP" 1.750 MB 25074 rows
. . exported "SCHOOL4"."FEESLIP" 163.4 KB 1981 rows
. . exported "SCHOOL5"."FEESLIP" 489.7 KB 6086 rows
. . exported "SCHOOL8"."FEESLIP" 3.045 MB 44063 rows
. . exported "SCHOOL9"."FEESLIP" 6.692 MB 96879 rows
. . exported "DEMO"."PROCESS_SETUP" 6.414 KB 6 rows
. . exported "SCHOOL10"."PROCESS_SETUP" 194.3 KB 7764 rows
. . exported "SCHOOL15"."PROCESS_SETUP" 13.90 KB 296 rows
. . exported "SCHOOL16"."PROCESS_SETUP" 245.7 KB 9192 rows
. . exported "SCHOOL6"."PROCESS_SETUP" 152.3 KB 5952 rows
. . exported "SCHOOL7"."PROCESS_SETUP" 158.2 KB 6196 rows
. . exported "DEV"."PROCESS_SETUP" 6.289 KB 3 rows
. . exported "SCHOOL1"."FEE" 3.065 MB 57397 rows
. . exported "SCHOOL1"."PROCESS_SETUP" 723.6 KB 29414 rows
. . exported "SCHOOL17"."PROCESS_SETUP" 63.43 KB 2308 rows
. . exported "SCHOOL18"."PROCESS_SETUP" 6.382 KB 5 rows
. . exported "SCHOOL2"."FEE" 3.065 MB 57397 rows
. . exported "SCHOOL21"."PROCESS_SETUP" 78.94 KB 2858 rows
. . exported "SCHOOL22"."PROCESS_SETUP" 57.91 KB 2056 rows
. . exported "SCHOOL3"."FEE" 3.065 MB 57397 rows
. . exported "SCHOOL4"."PROCESS_SETUP" 62.55 KB 2243 rows
. . exported "SCHOOL5"."PROCESS_SETUP" 166.3 KB 6623 rows
. . exported "SCHOOL8"."FEE" 3.065 MB 57397 rows
. . exported "SCHOOL9"."FEE" 3.065 MB 57397 rows
. . exported "DEMO"."TRANS_MASTER" 13.39 KB 2 rows
. . exported "SCHOOL1"."TRANS_MASTER" 4.178 MB 45597 rows
. . exported "SCHOOL10"."TRANS_MASTER" 223.8 KB 2424 rows
. . exported "SCHOOL15"."TRANS_MASTER" 26.99 KB 133 rows
. . exported "SCHOOL16"."TRANS_MASTER" 186.5 KB 1682 rows
. . exported "SCHOOL17"."TRANS_MASTER" 54.95 KB 468 rows
. . exported "SCHOOL18"."TRANS_MASTER" 13.39 KB 2 rows
. . exported "SCHOOL21"."TRANS_MASTER" 167.5 KB 1729 rows
. . exported "SCHOOL22"."TRANS_MASTER" 103.9 KB 1043 rows
. . exported "SCHOOL6"."TRANS_MASTER" 53.31 KB 427 rows
. . exported "SCHOOL7"."TRANS_MASTER" 62.25 KB 527 rows
. . exported "DEV"."TRANS_MASTER" 13.49 KB 3 rows
. . exported "SCHOOL2"."PROCESS_SETUP" 214.1 KB 7032 rows
. . exported "SCHOOL3"."PROCESS_SETUP" 159.1 KB 5913 rows
. . exported "SCHOOL4"."TRANS_MASTER" 64.86 KB 541 rows
. . exported "SCHOOL5"."TRANS_MASTER" 56.37 KB 469 rows
. . exported "SCHOOL9"."PROCESS_SETUP" 386.4 KB 14862 rows
. . exported "SCHOOL9"."TRANS_MASTER" 2.619 MB 29170 rows
. . exported "DAIRY1"."ICD" 2.290 MB 20633 rows
. . exported "DEMO"."SALARY" 9.898 KB 3 rows
. . exported "DEMO"."TRANS_DETAIL" 12.53 KB 2 rows
. . exported "DEV"."TRANS_MASTER140115" 1.381 MB 15761 rows
. . exported "SCHOOL1"."STUDENTREGISTRATION" 1.810 MB 8304 rows
. . exported "SCHOOL10"."SALARY" 20.65 KB 185 rows
. . exported "SCHOOL10"."TRANS_DETAIL" 102.4 KB 2424 rows
. . exported "SCHOOL15"."FEESLIP_PRINT" 9 KB 2 rows
. . exported "SCHOOL15"."SALARY" 12.60 KB 43 rows
. . exported "SCHOOL15"."TRANS_DETAIL" 18 KB 133 rows
. . exported "SCHOOL16"."EXAMFEE" 11.82 KB 45 rows
. . exported "SCHOOL16"."FEESLIP_PRINT" 13.20 KB 103 rows
. . exported "SCHOOL16"."SALARY" 46.71 KB 609 rows
. . exported "SCHOOL16"."TRANS_DETAIL" 75.85 KB 1682 rows
. . exported "SCHOOL6"."FEESLIP_PRINT" 15.12 KB 179 rows
. . exported "SCHOOL6"."SALARY" 36.71 KB 461 rows
. . exported "SCHOOL6"."TRANS_DETAIL" 28.74 KB 427 rows
. . exported "SCHOOL7"."EXAMFEE" 41.58 KB 557 rows
. . exported "SCHOOL7"."FEESLIP_PRINT" 45.35 KB 970 rows
. . exported "SCHOOL7"."SALARY" 17.77 KB 125 rows
. . exported "SCHOOL7"."TRANS_DETAIL" 32.89 KB 527 rows
. . exported "SCHOOL8"."PROCESS_SETUP" 555.7 KB 22905 rows
. . exported "SCHOOL_ADMIN"."MESSAGEOUT" 1.447 MB 11487 rows
. . exported "DAIRY1"."STORE_DETAIL_TRANSFER" 910.6 KB 20466 rows
. . exported "DEMO"."STUDENTREGISTRATION" 25.23 KB 12 rows
. . exported "DEV"."SALARY" 10.23 KB 1 rows
. ....

A reader, October 22, 2021 - 10:16 am UTC

log is truncated, it is longer then this

Download full log from here

A reader, October 22, 2021 - 10:57 am UTC

Connor McDonald
October 26, 2021 - 2:25 am UTC

credentials?

it is a public page

A reader, October 26, 2021 - 12:18 pm UTC

No need for credential, its a public page
Connor McDonald
October 27, 2021 - 2:14 am UTC

Ah...its because you kept the session ID in the link - you need to remove that :-)

Looking at the log - you explicitly nominated an APEX schema (FLOWS...) so its not surprising that you got APEX objects

Flows_Files

Peter G, October 27, 2021 - 1:43 am UTC

"it is trying to import even apex schema objects also."

Couldn't those be from having FLOWS_FILES as one of the schemas?
Connor McDonald
October 27, 2021 - 2:19 am UTC

yup

You solved the problem.

A reader, October 27, 2021 - 8:15 am UTC

ooh, i see now, i don't know how "flows_files" got their.
But thanks team for help.
Connor McDonald
October 28, 2021 - 2:53 am UTC

glad we could help

More to Explore

Utilities

All of the database utilities are explained in the Utilities guide.