export full of a database.


  I need to do a full export of my database.      I am planning to first kill all the current sessions, put the database in restricted mode and do the full export.

  a) How can i identify and kill all the users sessions ?

  b) How can i check which users has the privelige to connet even when the database is in restricted mode ?

 c) What rollback segments problems i could have ?

 d) Any tips or consideration for doing an export to a 1Tb database ?

 e) I will use the expdp for the export.  

 f) How much time could the export take ?
 g) How big the size of the export file could be ?


joe_echavarriaDatabase AdministratorAsked:
Who is Participating?
slightwv (䄆 Netminder)Connect With a Mentor Commented:
>>You do not need to bounce the database to get it into restricted mode

Thanks for the clarification!

>>is there an option or a way to speed it up ?

What is the throughput of your disks?  Get an estimate my copying know sized files between the disks to get an idea of Megs per minute.

If you get a time of around 21 hours for the size of your export,  you are being limited by your disks.  The only way to speed it up is get faster disks or reconfigure them for better performance.

If the disks are fast enough, maybe you are CPU or memory bound during the export.

You might also look into Parallel Export.

Figure out where the slowdown is and work from there.
slightwv (䄆 Netminder) Commented:
>> I am planning to first kill all the current sessions, put the database in restricted mode and do the full export.

I would not kill sessions.  This can result in lost data.

Best to do a shutdown immediate.

a) select sid and serial# from v$session then issue alter system kill session.

b) The docs:


Opening a database in restricted mode allows database access only to users with both the CREATE SESSION and RESTRICTED SESSION system privilege. Only database administrators should have the RESTRICTED SESSION system privilege.

c) none really since you are restricted.

d) Why are you wanting to use Export?

f) No way for us to answer this.  A lot depends on your system.

g)  The docs:

expdp has an ESTIMATE flag.
Devinder Singh VirdiLead Oracle DBA TeamCommented:
Mainly import takes more time than export.
You can also use Parallel parameter to speedup the process.
7 new features that'll make your work life better

It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

joe_echavarriaDatabase AdministratorAuthor Commented:
The purpose of the export is for importing it in another server.

I will put the database in restricted mode without restarting it..

slightwv (䄆 Netminder) Commented:
export does a read-consistent export.  Not seeing the reason for the shutdown.

Do you not have the redo to do on a live system?
Devinder Singh VirdiLead Oracle DBA TeamCommented:
Adding more to Slightwv comments:-
You can use FLASHBACK_SCN and FLASHBACK_TIME to take export in consistent state.
Therefore you dont need to kill users.
I believe this consumes more rollback and temp segments to maintain consistent image, but not sure.
slightwv (䄆 Netminder) Commented:
The old exp had a consistent option.  Are you saying you need to do something more involved than that with expdp?
joe_echavarriaDatabase AdministratorAuthor Commented:
No i will not shutdown.  I will only put the database in restricted mode and kill current users.
I think that will work.
slightwv (䄆 Netminder) Commented:
I believe you need to shutdown to restart in restricted mode.

I'm still advising to not kill sessions.
joe_echavarriaDatabase AdministratorAuthor Commented:
I am having a problem with the expdp.   It does not work on the directory i want, but if i use the old normal exp utility it works.

Here is the information :

Y:\MFG01PRD>expdp system/oradba@mfg01prd.domain.corp directory=EXPORT  dumpfile=
mfg01prd_01.dmp logfile=mfg01prd_log_01.log full=y;

Export: Release - 64bit Production on Friday, 09 September, 2011 17:4

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Release - 64bit Production
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 488
ORA-29283: invalid file operation

SQL> col privilege format a15;
SQL> col directory_name format a15;
SQL> col grantee format a15;
SQL> col grant format a15;
SQL> select privilege,directory_name,grantee,grantor from all_tab_privs t, all_d
irectories d where t.table_name(+)=d.directory_name
  2  and d.directory_name='EXPORT' order by 2,1;

--------------- --------------- --------------- ------------------------------
READ            EXPORT          SYSTEM          SYS
WRITE           EXPORT          SYSTEM          SYS

SQL> list
  1* select * from all_directories where directory_name='EXPORT'
SQL> /

--------------- --------------- ---------------
SYS             EXPORT          Y:\MFG01PRD
slightwv (䄆 Netminder) Commented:
Am I safe to assume Y:\MFG01PRD exists and Oracle has write access to it?

Oracle has had issues in the past with Network Folders.  For grins try a local disk.
joe_echavarriaDatabase AdministratorAuthor Commented:
If the normal exp utitly works on Y:\MFG01PRD  Oracle has write access to it  i guess.

And because i do not know how big this 1 TB database export file could be i have this Share drive mapped in the server.  And have available 7 TB for me.

I think it should work.

joe_echavarriaDatabase AdministratorAuthor Commented:
You are right, i changed the path the directory and the expdp works....

How can i solve this oracle problem with networks folders ?
joe_echavarriaDatabase AdministratorAuthor Commented:
How big an export of a 1TB could be ?

I have 1 TB in a local disk and i think it will be enough, what u think ?
slightwv (䄆 Netminder) Commented:
>>If the normal exp utitly works on

I'm thinking the old version actually executes as the user running the exp.exe program.

expdp runs as the Oracle System user.

>>And because i do not know how big this 1 TB database export file could be
>>How big an export of a 1TB could be ?

Did you run an estimate?

>>How can i solve this oracle problem with networks folders ?

Like I mentioned above, the oracle.exe does something it is different than than when a logged in user does something.  Going from memory here but oracle runs as a service.  This is the issue.

I thought they had fixed most of the problems in recent versions.  Maybe is still having issues.

There is a lot of information out there on Oracle and Networked mappings.

I have limited Internet access right now and cannot research it fully for you.

johnsoneSenior Oracle DBACommented:
Just to address one point that was brought up.

You do not need to bounce the database to get it into restricted mode.  All you need is an alter system:


It doesn't affect already connected session, which is the reason you need to kill all the existing sessions after running the ALTER SYSTEM.
joe_echavarriaDatabase AdministratorAuthor Commented:
Which will be the option to speed up a full export using the expdp tool ?

It took me 21 hours to finish..., is there an option or a way to speed it up ?
Devinder Singh VirdiLead Oracle DBA TeamCommented:
You can go for TTS instead.
For more info read the below link
joe_echavarriaDatabase AdministratorAuthor Commented:
Is the transportable tablespaces available only for the Enterprise Edition of Oracle ?

My current version is and i trying to
SQL> show user
SQL> select * from sys.transport_set_violations;
select * from sys.transport_set_violations                  *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select banner from v$version;

Oracle Database 10g Release - 64bit Production
PL/SQL Release - Production
CORE      Production
TNS for 64-bit Windows: Version - Production
NLSRTL Version - Production

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.