SearchOracle.com's resident Oracle experts Karen Morton and Brian Peasland guide users through some importing tips and techniques, such as how to load text files without SQL*Loader, importing a dump multiple times, importing from different versions, and more in this Ask the Experts compilation.
I need to export from user 'A' in a production database and import to a user 'B' in a development database with a different tablespace. Do you have very detailed step-by-step procedure?
Brian Peasland: This is a pretty common procedure, which isn't too hard to accomplish. So here is a step-by-step guide.
- Export the entire schema from production:
exp userid=system file=user_a.dmp log=user_a.log owner=a
- FTP the dump file (in BINARY mode) to the development server.
- Make sure user B exists in your development instance.
- Run this command:
imp userid=system file=user_a.dmp log=imp.log fromuser=a touser=b
The only big sticking point is that user B may have a different default tablespace than user A, if user A exists. If this is the case, and user B has been granted the CONNECT role, then user B's tables will be created in user A's default tablespace. To stop this, make sure that user B has the correct default tablespace and make sure that you revoke UNLIMITED TABLESPACE from user B.
I am trying to load a text file into an Oracle table by writing the code inside a stored procedure using SQL*Plus. Is there any way to do that without using SQL*Loader?
Brian Peasland: You can do this a few ways. If you are using Oracle 9i, then you can "mount" the file so that it appears as a table in the database. Oracle 9i calls this new feature External Tables. It uses SQL*Loader as the engine for bringing the data from outside the database to inside the database. The benefit of this is that one the external table has been mounted to the database, you can use simple SQL statements to pull the data from that file.
If you are not using 9i, or do not wish to use this method, then you can use the UTL_FILE supplied package. This package will let you open a file for input or output. So open the file, read the data, and process it, all from within your PL/SQL block!
How can I import data (with the full import option) successfully more than one time from an exported dump file, even if we have to overwrite tablespaces, tables and users?
Karen Morton: You can run imports of the same dump file multiple times BUT you must make sure that if you do, you do it in such a way as to not create duplicates or attempt to "re-do" work you accomplished in a previous import. It takes a bit of planning and knowing exactly what the various parameters you use control in order to be successful.
It appears that you might find it very helpful to read the Oracle Utilities guide documentation. Trying to walk you through all the issues here would be a bit too lengthy, and I think everything you need to have answered could be done by reading just the two chapters in the Utilities Guide on Export and Import.
I need to insert a *.pdf file into a specific column. My problem is that I can't use BLOB because it requires having the file system on the server side, and that isn't possible. I thought about using a LONG RAW data type, but I saw an article that said that the only way to load data to a LONG RAW is via C or VB. Is this info correct? Is there any other way of accomplishing this?
Brian Peasland: Your assertion that a BLOB must reside on the server's file system to be loaded is only correct if you are using the BFILE datatype. If you use the BLOB datatype, then your binary file can be on any system so long as you have an application on that system to load the file into the database. This is done all the time. For instance, many Web sites let you upload files from the client's Web browser to the database.
I would stay away from LONG RAWS since they are more restrictive and will be desupported soon.
Is possible import an 8.0 export file(dmp) into to 7.3 (under Windows NT)?
Karen Morton: Here's the answer straight from the Oracle Utilities guide:
You can create an Oracle release 7 export file from an Oracle8 database by running Oracle release 7 Export against an Oracle8 server. To do so, however, the user SYS must first run the CATEXP7.SQL script, which creates the export views that make the database look, to Export, like an Oracle release 7 database.
Note: An Oracle8 Export requires that the CATEXP.SQL script is run against the database before performing the Export. CATEXP.SQL is usually run automatically when the user SYS runs CATALOG.SQL to create the necessary views. CATEXP7.SQL, however, is not run automatically and must be executed manually. CATEXP7.SQL and CATEXP.SQL can be run in any order; after one of these scripts has been run, it need not be run again.
So, in order to import the data from 8.0 into 7.3, you must create the export dmp file using the Oracle 7 release of Export. Then when you use the Oracle 7 release of Import it will understand the contents of the dmp file. If you tried to just export from your 8.0 version and directly import that using 7, it would fail.
I have an Oracle8i V08.01.06 database dump file, which was created using conventional path. In my system, Oracle8i V08.01.04 is installed. When I tried to import the file, I get an error message, and the process quits. Is there any solution for importing the higher version to a lower version database?
Karen Morton: When creating an export dump file for an import into a lower release database, use a version of the EXPORT utility that is equal to the version of the target database (lowest version = 08.01.04 in this case) Also, you should always use a version of the IMPORT utility that is equal to the version of the target database as well. Try creating your dump file again, but this time make sure and use the export from your 8.01.04 version. That should take care of your problem!
How does one import data (not restore the full database) from a .dmp file into an existing Oracle database with the same (metadata) structure, using the IMP command?
Karen Morton: You can restore as little as a single table or an individual schema from your .dmp file. Also if you are importing into a database with existing tables, you may run into referential integrity problems (foreign keys) or other constraint errors when loading data. It is often a good idea to disable those things before running your import and then re-enabling them after it is complete.
OK. Now on to how to do the import! There are four basic import modes: FULL, TABLES, FROMUSER, and TRANSPORT_TABLESPACE. All modes (but FULL since you don't want to do a full import) can be used to accomplish what you desire at varying levels. Let's look at two in particular: TABLES and FROMUSER. TABLES is used to import only specified tables. FROMUSER allows import of all objects owned by a specified user. If you use TABLES mode, you will simply list the tables you want to import:
IMP id/password TABLES=(emp,dept,cust)
If you need to import the tables from a specific schema, you'd use the following:
IMP id/password FROMUSER(scott) TABLES=(emp,dept) IMP id/password FROMUSER(adams) TABLES=(cust)
If you want to import just a given user's schema, you'd use:
IMP id/password FROMUSER(scott)
For more information see the Oracle Utilities Reference document.
In import/export, how do I import only triggers in a dump file?
Karen Morton: IMPORT does not have a parameter for TRIGGERS only. EXPORT has the ability to include or exclude triggers using the TRIGGERS parameter (TRIGGERS=N indicates to not include triggers), but there is no corresponding parameter available for IMPORT. I'm afraid that if all you want to import is just the triggers, you'll not be able to accomplish just that alone.
I tried to copy tables from user A (on tablespace A) to user B (with default tablespace B) using the import utility. But, the tables are always going to tablespace A, even after I set quota to 0 or 1k for user B on tablespace A. Is there any way to get user A's tables to user B on tablespace B? Here is what I did:
- export user A
- create user b default to tablespace B (with role: connect &resource)
- alter user b quota 0 on tablespace A
- import from user A to user B (tried both with ignore=y or ignore=n)
Note, I even made tablespace A offline, renamed it, then did the import, but that caused an error on import indicating tablespace A not found!
Brian Peasland: Your problem lies in one problem with the roles you granted. When you grant RESOURCE to a user, you are also granting the UNLIMITED TABLESPACE system privilege to that user. This privilege is not part of the role, but a side effect of granting RESOURCE to the user. Luckily, you can REVOKE UNLIMITED TABLESPACE from that user. Then make sure that the quotas are set to 0 on the old tablespace and some non-zero value on the destination tablespace. The import utility will use the DEFAULT tablespace for that user.
For more information
- Over 100 tips and scripts about Oracle backup and recovery
- More Oracle answers from Brian Peasland
- More Oracle answers from Karen Morton