Skip to Main Content
  • Questions

  • EXP: how to include the "CREATE USER" statement without perform a full export?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, aaaa.

Asked: December 07, 2010 - 8:38 am UTC

Last updated: February 22, 2019 - 6:37 am UTC

Version: 10.2.1

Viewed 10K+ times! This question is

You Asked

Hello.
I need to export an oracle schema using exp, included the "CREATE USER" statement.
Now, for do that the only think to do that I know is perform a full export of the database. The problem is that in my database there are a lot of users and the exp takes long time for export ( about 45 minutes ). There is a way to include a "create user" statement into an export not full or alternatively to filter unwanted users from an export full?
Thank you.

and Tom said...

There is not, not with EXP

However, you do have:

ops$ora11gr2%ORA11GR2> select dbms_metadata.get_ddl( 'USER', user ) from dual;

DBMS_METADATA.GET_DDL('USER',USER)
-------------------------------------------------------------------------------

   CREATE USER "OPS$ORA11GR2" IDENTIFIED BY VALUES 'S:438548458805A3183B4FEA9A6
5A9F8B6BE5F855085A2FE398F392244EE8E;9AF59B3564F2019E'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"


or using datapump:

$ expdp / include=user

Export: Release 11.2.0.2.0 - Production on Wed Dec 8 14:57:28 2010

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "OPS$ORA11GR2"."SYS_EXPORT_SCHEMA_01":  /******** include=user 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/USER
Master table "OPS$ORA11GR2"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for OPS$ORA11GR2.SYS_EXPORT_SCHEMA_01 is:
  /home/ora11gr2/app/ora11gr2/admin/ora11gr2/dpdump/expdat.dmp
Job "OPS$ORA11GR2"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:57:34

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

thank you

Angelo Pellecchia, December 09, 2010 - 12:23 pm UTC

Ok, with exp is not possible but with expdp it does.
Thank you very much!

Not working in 12c

Nishant Baurai, February 12, 2017 - 4:05 am UTC

Not working in 12c

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01": sys/******** AS SYSDBA include=user
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
ORA-39168: Object path USER was not found.
ORA-31655: no data or metadata objects selected for job
Job "SYS"."SYS_EXPORT_SCHEMA_01" completed with 2 error(s) at Sat Feb 11 22:59:28 2017 elapsed 0 00:00:03

Connor McDonald
February 13, 2017 - 10:37 pm UTC

Because you never need the definition for the SYS user. Run it as non-SYSDBA

C:\Users\comcdona>expdp userid=mcdonac include=user directory=temp

Export: Release 12.1.0.2.0 - Production on Tue Feb 14 06:36:21 2017

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics
and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "MCDONAC"."SYS_EXPORT_SCHEMA_01":  userid=mcdonac/******** include=user directory=temp
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/USER
Master table "MCDONAC"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MCDONAC.SYS_EXPORT_SCHEMA_01 is:
  C:\TEMP\EXPDAT.DMP
Job "MCDONAC"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Feb 14 06:36:26 2017 elapsed 0 00:00:03


A reader, February 20, 2019 - 11:11 pm UTC

But question is why?

You can ignore the following block/.
Export: Release 12.2.0.1.0 - Production on Wed Feb 20 15:09:07 2019

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "JC"."SYS_EXPORT_SCHEMA_01": jc/********@epmdev03_wphz4 parfile=/home/epm_dev03_db/dev03-workingdir/exp_b4_cname_flip/exp_3_schemas.par.1
ORA-39168: Object path USER was not found.
ORA-31655: no data or metadata objects selected for job
Job "JC"."SYS_EXPORT_SCHEMA_01" completed with 2 error(s) at Wed Feb 20 15:09:10 2019 elapsed 0 00:00:02


Connor McDonald
February 22, 2019 - 6:37 am UTC

Make sure its a DBA user that runs the datapump
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.