Can we do parallel exports on the same database?

Posted on 2002-05-03
Last Modified: 2007-11-27
When I did a full export of my test db it took 10 hours. There is a 30 mil row table which took major chunk of the time.

Is it possible to run a full export and a table export at the same time? This table comes in the end of the sequence of full export. So it should not conflict with the full export. Once the table export is done, I want to drop the table.

One last question, when I did the full export I got this msg for a few tables but it exported the table rows though - "EXP-00079: Data in table "table_name" is protected. Conventional path may only be exporting partial table." Do I need to worry about it?

Your feedback would be greatly appreciated.

Question by:masaimara
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1

Expert Comment

ID: 6988967
Th signle important thing you need to do is to export
using direct path, such as,
exp ... direct=y

[You may also specfiy a large buffer, for example,

LVL 17

Expert Comment

ID: 6989574

Yes, you can export a big table while exporting an entire schema or database. Once you got the big table dump with you, you can drop it. The main export is smooth except for this error:
EXP-00007: dictionary shows no columns for MYSCHEMA.BIG_TABLE

But this is not a problem. You can import it back whenever you may so please and it will be smooth without warnings. I actually checked it in my test database.

The second part of your question is partly answered by waynezhu. Keep direct=y and buffer=5000000 and your problem will mostly be solved. The error you are getting is just a warning which also come if privileges not there, if table already exists and ignore=y not given, duplicate constraints in schema.

The warnings also come if other objects like indexes, jobs etc. exist during export/import and PL/SQL cursors, triggers, subprograms and packages exist. The warnings mostly are not important to pay attention to and in your case also it is the same.

Just check the data, structure, indexes, constraints etc. for the tables for which this warning was thrown.

--- k_murli_krishna

Author Comment

ID: 6991471
Waynezhu and Murali Krishna,
Thankyou for your comments.

I am trying to do a full export and drop the database, recreate it with original create scripts, and import it back.

1. Can you tell me how to drop the database - is it deleting the datafiles and stuff OR can you use some tool to drop it before you recreate the database?

2. If no database create scripts exist, can we create the scripts somehow using the existing the database?

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

ID: 6991675
1. Depending upon OS and Oracle versions, there are various
ways to do that, for example, using Oracle Enterpise Manager, manual method, ...

2. Yes, you can. Again there are many ways to do it. You
can use various tools, export utility, SQL scripts, ...


Author Comment

ID: 6997165
Waynezhu and Murali Krishna,
I used direct=y and recordlength=db block size instead of buffer=5000000 and it ran like a horse.

Do you guys know about

'alter database backup controlfile to trace'

by any chance.

If you know then, can you please tell me if these statements make sense


These are from the trace file created. I am going to delete all the database files (datafiles,control files, and redo files) before full import. Do I need to modify the statements? I know for sure that i have to make ARCHIVELOG to NOARCHIVELOG as there is no archiving for this test database.

LVL 17

Accepted Solution

k_murli_krishna earned 50 total points
ID: 7000764
1) If you are going to do backup and recovery, then do offline/online backup and corresponding recovery.
2) If you want to create database creation scripts from existing database, then third party tools from toadsoft etc. can help you.
3) If you send me your mail ID, I can give a HTML page full of Oracle links.
4) You can on the other hand drop database and then recreate using same options or exactly same using database configuration assistant.
5) What you can do is in a custom db creation note down all parameters that come and their values. Then see full database syntax from any standard book for create database command and fill the gaps. One book is Oracle8i DBA Bible by Jonathan Gennick etc.
6) You can do a create from OEM as waynezhu suggested but remember database is not deleted just by removing control files, data files and redo log files. There is much more to it and you will not be sometimes allowed to delete some of them. Either you delete all or keep all.
7) ALTER DATABASE BACKUP CONTROLFILE TO 'filename' specifies that the control file backed up to the specified filename in specified path.
8) ALTER DATABASE BACKUP CONTROLFILE TO TRACE: Specifies that SQL statements will be written to the trace file(user_dump_dest='trace file') and that no control file will be created.
9) STARTUP NOMOUNT: You can start an instance without mounting a database. Typically, you do so only during
database creation.  Again, only some background processes are started.
REUSE: Specifies that existing control files are reused overwriting the existing data in them.
DATABASE "DATABASE_1": Specifies the database for which to create a new control file.
NORESETLOGS: Specifies that the content of the files specified in the LOGFILE clause are expected to be exactly the same as the configuration at the moment that the database was shut down.
ARCHIVELOG: Specifies that the mode of archiving isn't established. This is the default.

Masaimara: Having all the info in hand, if you have just a test DB and not important go ahead. Else take export, offline and online backups before doing anything for sake of recovery. If things spoil up, just unintall and install oracle once again with a new home and DB SID.

--- k_murli_krishna


Author Comment

ID: 7001791
As you said, we cannot delete a database by just deleting the datafiles,controlfiles, and redo files, what else should we do to completely erase a database. This database is running on a unix server and I cannot use db assistant from unix.

I have to completely erase the database; recreate it exactly as it was with the same init parameters (except for db_block_size), same datafiles, and modify the extent management for tablespaces (make them locally managed); do a full import. I just need to know the sequence of steps to carry out this task on a unix server without the help of db assist. I will do the full cold backup before the erase.

my id:


LVL 17

Expert Comment

ID: 7042184

1) Take export dumps of all schemas
2) Take cold/offline backup
3) Take hot/online backup

The above 3 steps to make sure that you can get it back at any cost. Something really goes wrong reinstall oracle in a new home and with new database SID.

A) One way is if you have windows m/c install oracle, custom create database and while deleting look at the messages and most importantly notice the files in directories which get deleted.

B) The other way is to use ALTER DATABASE to get the database state you want and also by editing init.ora.

C) A third way is for deleting database, delete all that you take backup for both hot/cold i.e. online/offline.

All the best:)

--- k_murli_krishna


Expert Comment

ID: 7096834
Please update and finalize this question.  If you need Moderator assistance to split points or otherwise help with special handling, let us know via comment here or by posting a Zero point question with this question link in the Community Support topic area.
Thank you,
Moondancer - EE Moderator

Author Comment

ID: 7149804
Thankyou very much for all your comments.


Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Field name with special character (Ñ) in Oracle 11 132
oracle date format checking 7 32
Error in creating a view. 8 32
why truncate is faster than delete in oracle ? 4 45
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question