Forum OpenACS Q&A: Oracle import too slow?
I was somewhat surprised by just how slow the import step is; in my case, maybe 45 minutes or so for less than 430 MB of data (the size of the tablespace file). This is certainly still feasible on a weekly or nightly basis, but it sure is annoying. And then when my data grows...
Other possible options I've thought of are:
- Use the
transport_tablespace=yoption to imp and exp. This is supposed to be as very fast, as fast as just copying the raw database files. But there are limitations. E.g., you have to plug the tablespace it into a different instance, and you can't rename it.
- Use sqlldr to get the data in. Use imp only to create the tables, etc., not to put in any data. This will definitely be faster than imp, but means writing scripts to dump the data out in a format sqlldr can understand (there is no option to have exp do that, unfortunately...), etc.
- Do our own clone of the data entirely within Oracle. If Dev and Prod are on the same instance, Dev just needs read access to Prod. If they're on different instances, you could probably create a database link between the two. Of course, you have to take measures to get a consistant copy, and oops, make sure rollback buffers can handle this one massive transaction, etc.
I'm very curious about what solutions you've used...
Please correct me if I'm wrong--I'm pretty sure transportable tablespaces is an Oracle Extended Edition feature only.
have a script tar and gzip it up, and it sftp's the ora8.tar.gz file
over every day. Whenever I want to have a copy of the production
data, I just unzip and untar it. It seems to work just fine.
Caveat: Oracle has to be shut down while the copy is being
made on both ends. I'm on an Intranet, so that's not a big deal.
I need to import data from a production database into two different tablespaces (two different users). Using 'exp' doesn't allow me to do that, as it always try to create in the tablespace with the username used while doing 'exp'. I want to import into 'development' and 'staging' but I don't want to have two different oracle instances, I want to use two different users. Is this possible without having to do, as Andrew says, create scripts that will write data out in formats for SQLLLDR?
Thank you in advance,
You can use sqlplus to format the data you want and then spool the data to a file. You can then use sqlldr to load in the data. Try something like this:
SQL> set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on SQL> spool mydata.txt SQL> select column1 || ',' || column2 || ',' || column3 from <tablename>; SQL> spool off
This works fine for a table or two but I'm not sure how useful it is if you want to do a full export of the tablespace.
it with any other versions). cf http://www.galileo.edu/obonilla/writing/oracle/
The other way is to change the permissions for the user which unfortunately I am not entirely sure how to do...
something like "revoke unlimited tablespace from username;" and maybe some other stuff. I think there might still be problems with clobs though.
This page talks about it for v7 but I would guess things are similiar for v8.
imp user1/<password> fromuser=user2 touser=user1 file=export.dmp
$ exp myprod/password consistent=Y owner=myprod file=/tmp/myprod.dmp
$ imp mydev/password rows=y show=n ignore=n fromuser=myprod touser=mydev file=/tmp/myprod.dmp
That will definitely work for what you want to do too. But, it was the slowness of the import step that led me to start this thread...
select OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BYTES from dba_segments order by OWNER, SEGMENT_NAMEYou can end up with clobs from the prod tablespace in the dev tablespace and vice versa depending on the permissions for the importing users.
Other simple things to check with import:
-- make sure you have enough space for your redo logs. IMP uses sql statements to insert data and thus generates lots of redo.
-- turn off archive logs
Another option to consider (enterprise edition only) is to do less frequent imports and to spend more time synchronizing only the statistics between the two databases.
Michael, can you comment more on "synchronizing only the statistics between the two databases"? I haven't heard of that.
The DBMS_STAT pl/sql package contains procedures for transferring statistics (http://oradoc.photo.net/ora817/DOC/appdev.817/a76936/dbms_st2.htm#1010202)
Looking over the docs again, it looks like this feature is available in standard edition.
Nice thing about fastreader ( www.wisdomforce.com ) that it is very fast and has built in compression on the fly which reduce the disk space required. we using fastreader to write data into pipe and load data into sql server