Chapter 8

Overview

Import (IMP) and Export (EXP) are among the oldest surviving Oracle tools. They are command line tools used to extract tables, schemas, or entire database definitions from one Oracle instance, to be imported into another instance or schema.

Traditionally, Import and Export have been considered to be in the domain of the DBA. I actually find them to be more useful to the individual developer than as a DBA tool. As databases have grown, both in size and importance, the tools needed to manage them have grown as well. In days gone by, it would not have been unreasonable to use import and export to rebuild your database (perhaps to change the block size or to move your database to another platform) or as your backup method. With 'small' databases today starting in the gigabytes range, simple tools that process everything serially (such as import and export), just do not scale. While not useless to the DBA, they are definitely not as useful as they once were. Other tools and methods have come along to take their place. For example, we would now use Recovery Manager (RMAN) to perform an incremental backup of very large databases, not EXP. They still have many other uses such to detect logical and physical corruption, to transport datafiles from database to data, and so on.

EXP and IMP are tools you will probably use at some point or another. If you wish to copy a schema from one user to another, the easiest method will be to use EXP and IMP. If you wish to extract the DDL for a schema, EXP and IMP will be the tools you use. If you wish to partition an existing table into many physical partitions (which requires a rebuild of the table), EXP and IMP might be a valid approach for you. In this chapter, we will look how to:

Subset data.
Get our DDL back out of the database.
Import small to medium amounts of data into different structures (structures different from those they were exported from).
Clone a schema in the database - this looks easy with this tool but there are various 'gotchas' of which you must be aware.

We will tackle EXP and IMP mainly from the developer's perspective. I am going to cover many of the frequently asked about issues regarding the practical use of EXP and IMP. I use these tools myself and have either encountered these issues, or have been asked to provide a solution for them.

A Quick Example

To demonstrate the value of IMP and EXP, and their ease-of-use, we'll use them to extract the DDL of a table in the SCOTT/TIGER schema. I've seen many people search around for utilities to do this, or attempt to write their own DDL extractors - never realizing that there is one supplied with the database. It is as easy as this:

C:\ImpExp>exp userid=scott/tiger tables=emp      C:\ImpExp>imp userid=scott/tiger full=y indexfile=emp.sql      C:\ImpExp>type emp.sql      REM  CREATE TABLE "SCOTT"."EMP" ("EMPNO" NUMBER(4, 0) NOT NULL ENABLE, REM  "ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4, 0), REM  "HIREDATE" DATE, "SAL" NUMBER(7, 2), "COMM" NUMBER(7, 2), "DEPTNO" REM  NUMBER(2, 0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING REM  STORAGE(INITIAL 32768 NEXT 32768 MINEXTENTS 1 MAXEXTENTS 4096 REM  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) REM  TABLESPACE "TOOLS" ; REM  ... 14 rows CONNECT SCOTT; CREATE UNIQUE INDEX "SCOTT"."EMP_PK" ON "EMP" ("EMPNO" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 32768 NEXT 32768 MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TOOLS" LOGGING ; REM  ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "EMP_PK" PRIMARY KEY REM  ("EMPNO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 REM  STORAGE(INITIAL 32768 NEXT 32768 MINEXTENTS 1 MAXEXTENTS 4096 REM  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) REM  TABLESPACE "TOOLS" ENABLE ; REM  ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "EMP_FK_DEPT" FOREIGN KEY REM  ("DEPTNO") REFERENCES "DEPT" ("DEPTNO") ENABLE NOVALIDATE ; REM  ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "EMP_FK_EMP" FOREIGN KEY REM  ("MGR") REFERENCES "EMP" ("EMPNO") ENABLE NOVALIDATE ; REM  ALTER TABLE "SCOTT"."EMP" ENABLE CONSTRAINT "EMP_PK" ; REM  ALTER TABLE "SCOTT"."EMP" ENABLE CONSTRAINT "EMP_FK_DEPT" ; REM  ALTER TABLE "SCOTT"."EMP" ENABLE CONSTRAINT "EMP_FK_EMP" ;

There it is in all of its glorious verbosity (yours will probably look a little different - you are seeing some of the many 'enhancements' I've made to the SCOTT.EMP table during the writing of this book). That is the DDL you need to recreate the EMP table, as it currently exists. A little later, we'll take a look in some detail at using IMP and EXP to successfully extract DDL from the database (and at some scripts that are useful when IMP/EXP fall short).

Note

Full documentation on EXP and IMP is available in the Oracle Server Utilities Guide. Rather than repeat much of what is there, I would encourage you to read through it as a supplement to this chapter.

EXP creates a proprietary binary file, known as a dump file (often abbreviated to DMP), which is transportable across operating systems - you can transfer it from Windows 2000 to Sun Solaris, from Sun to MVS and it will still work.

What this means is that you will not use EXP to unload data from Oracle to be imported into SQLServer - it is not the appropriate tool for that. If you wish to do this, you could use the SQL*PLUS copy command in conjunction with either the Transparent Gateway to SQLServer or, even more simply, with the ODBC Net8 drivers provided with Oracle Developer/2000 (the latter allow SQL*PLUS to connect to an ODBC data source). You will not be editing the DMP file using your text editor (or any editor in fact). A DMP file has one use, and one use only - to be read and processed by the IMP tool.

Why You Might Use IMP and EXP

EXP has many uses, some are briefly mentioned above. Here I will list some of the situations where I find them most useful.

Detecting Corruption

I use EXP as a tool to proactively detect corruption, physical or logical, in my database. If you use EXP to perform a full database export, it will completely exercise the data dictionary, finding almost any logical dictionary corruption for us. Additionally, it will full scan every table in your database, reading out all of the rows. If there is a table somewhere with a bad block, this is bound to find it. It won't find certain types of logical corruption, such as an index that points to non-existent rows, since it simply full scans the tables, but it will find the most important types of error (we can always rebuild an index, rebuilding a table may not be possible).

Since EXP reads every table fully, it also takes care of any blocks that need cleaning out. This gives us the added bonus of preventing the occurrence of a spurious ORA-01555 (see the Chapter 5 on Redo and Rollback for more details on this). After I export the full database and verify the results (look for errors in the logs), I do a full import in SHOW mode. This has the nice side effect of creating a large log file with all of the DDL, the text of all procedures, triggers, views, and so on. On more than one occasion, I have used this to rescue a missing piece of code in an emergency. Additionally, if a table is 'accidentally' dropped, I may be able to quickly restore it from the DMP file, rather than going to my real backups to restore an object. If you have the space to store the DMP files (they are very compressible), I would encourage you to suggest exporting be done on your system during 'off peak' time. In the section on Large Exports below, I'll supply a script for UNIX systems that exports directly into compressed files saving considerably on space.

Extracting DDL

EXP is an excellent tool for extracting DDL from the database (as demonstrated in Chapter 6, Tables), providing an very easy way to see the verbose CREATE statement for many objects. In the section below on Getting the DDL, we'll revisit this idea and explore it further.

Cloning Schemas

EXP and IMP can be used to clone a schema for testing. Using the FROMUSER and TOUSER options of the IMP command, we can easily export data from one user account to another. This is also a supported method for 'renaming' a user - you would perform a user-level export, import into the new user account and then, upon verification of success, you would drop the old user.

Transporting Tablespaces

EXP and IMP can be used to 'transport' a tablespace or set of tablespaces. Only available in Oracle 8i, this allows us to take the formatted datafiles from one instance and 'plug' them into another instance. Consider the case where you have an online web site and wish to publish a large catalog. You can build the catalog on your side of the firewall, refining and testing it. Then upon publication of that catalog, you can simply EXP that tablespace, and all related tablespaces (with all supporting structures like indexes and such), and copy those datafiles to the various servers you have outside the firewall. No more 'dump and load' to publish data - the switchover from the old catalog to the new takes place very quickly. The uses in a data warehousing environment are obvious - instead of ETL (Extract, Transform, Load) we can just L (Load) by attaching the datafiles of our operational system to the data warehouse and using SQL to reformat the data. I've also used this feature heavily in my testing and development. In order to test software, we always need a way to 'reset' the database. Using transportable tablespaces, we are able to do this quickly without having to restore the entire database, thus allowing many different projects to share the same database (we won't be resetting each others data). In this fashion, we can use one database instance, instead of one database instance per project.

Rebuilding Instances

Using EXP and IMP is a good way to rebuild a modestly sized instance. If you would like to change your database block size for example, EXP and IMP might be the right tool for you. On a large instance, it may be prohibitive time-wise to use EXP and IMP but for systems up to a couple of gigabytes, this is an option. I would not use this on my terabyte instance (or anything over 15 or so gigabytes)!

Copying Data between Platforms

EXP and IMP provide a good way to copy data from one platform to another, or even as a way to 'email' some data to someone else. If we create a DMP file on one platform, we can import that on any other platform - the data is platform-independent, even though the DMP file is a binary file.

There are other creative uses of these tools, but the above covers their main uses. What I will do now is answer the frequently ask questions concerned with the practical use of EXP and IMP, and how to use them to solve common problems.

How They Work

In this section, we'll cover many of the frequently asked 'how do I' questions about IMP and EXP. Before we begin, we'll go over the important options for each and what they are used for.

The Options

The inputs for EXP and IMP are all name-value pairs. You will either use:

exp parameter_name = value

or:

exp parameter_name = (value1,value2,value3...)

The second method is useful for certain operations, such as for table level export and for exporting more then one table at a time. You can save your IMP and EXP options in a parameter file, saving yourself the chore of typing in the same options over and over again.

Both EXP and IMP support a HELP = Y option, which will display brief usage information on screen. This is very handy when you can't remember the exact name of a specific parameter. If you just type EXP or IMP on the command line and hit Enter, they will go into 'interactive' mode and you will be prompted for the values of each parameter one by one.

EXP Parameters

The following is what EXP will display if you pass only the HELP = Y parameter:

C:\exp>exp help=y      Export: Release 8.1.6.0.0 - Production on Mon Mar 19 14:11:23 2001      (c) Copyright 1999 Oracle Corporation.  All rights reserved.      You can let Export prompt you for parameters by entering the EXP command followed by your username/password:           Example: EXP SCOTT/TIGER      Or, you can control how Export runs by entering the EXP command followed by various arguments. To specify parameters, you use keywords:           Format:  EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)      Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)                or TABLES=(T1:P1,T1:P2), if T1 is partitioned table      USERID must be the first parameter on the command line.      Keyword  Description (Default)       Keyword      Description (Default) -------------------------------------------------------------------------- USERID   username/password           FULL         export entire file (N) BUFFER   size of data buffer         OWNER        list of owner usernames FILE     output files (EXPDAT.DMP)   TABLES       list of table names COMPRESS import into one extent (Y)  RECORDLENGTH length of IO record GRANTS   export grants (Y)           INCTYPE      incremental export type INDEXES  export indexes (Y)          RECORD       track incr. export (Y) ROWS     export data rows (Y)        PARFILE      parameter filename CONSTRAINTS export constraints (Y)   CONSISTENT   cross-table consistency LOG      log file of screen output   STATISTICS   analyze objects (ESTIMATe) DIRECT   direct path (N)             TRIGGERS     export triggers (Y) FEEDBACK display progress every x rows (0) FILESIZE maximum size of each dump file QUERY    select clause used to export a subset of a table      The following keywords only apply to transportable tablespaces TRANSPORT_TABLESPACE export transportable tablespace metadata (N) TABLESPACES list of tablespaces to transport Export terminated successfully without warnings.

We'll take a look at the most important parameters and those that require clarification, in detail. Parameters that are obvious, such as USERID, will not be covered. Parameters that I consider obsolete, such as INCTYPE, will not be covered either:

Parameter Name	Default Value	Meaning/Notes
BUFFER	OS dependent	This setting sizes the array fetch buffer used by EXP. If you divide the BUFFER parameter by the maximum row size for a given table, this will tell you how many rows EXP will fetch at a time from that table. Larger array sizes improve performance. I have found 100 rows to be an all around 'good' array size. Note that some tables, in particular tables with LONG or LOB columns, are fetched one row at a time regardless of the buffer setting. You should ensure the buffer is set large enough for your largest LONG column.
COMPRESS	Y	This parameter does not compress the contents of the exported data. It controls how the STORAGE clause for exported objects will be generated. If left as Y, the storage clause for objects will have an initial extent that is equal to the sum of its current extents. That is, EXP will generate a CREATE statement that attempts to fit the object into one single extent. I recommend compress = N and the use of locally managed tablespaces.
ROWS	Y	Tells EXP whether to export the rows of data within exported tables or just the structure. I use this frequently with a setting of N just to do structural exports.
FILESIZE	0	If set to a positive value, sets the maximum size of DMP file that export will create. Used when exporting more then two gigabytes of data. See Large Exports later for details.
QUERY	N/A	Allows you to associate a WHERE clause to the tables being exported. This WHERE clause will be applied to the rows during a table level export, and only rows that satisfy the WHERE clause will be exported. This lets you export a 'slice' of a table. See the section Subsetting Data for an example.
FULL	N	If set to Y, export would perform a full database export. This will extract all users, tablespace definitions, system grants, and everything from the database.
OWNER	N/A	This lets you specify a list of schemas to export. Useful for cloning a schema or to 'rename' a user.
TABLES	N/A	Allows you to specify a list of tables to be exported.
PARFILE	N/A	Specifies the name of a parameter file that contains the parameter_name = values. Can be used in place of specifying the parameters on the command line. Most useful with long lists of tables to export or when specifying a query.
CONSISTENT	N	Specifies if the export should be done in a read-only transaction. This would ensure cross table consistency. If you recall from Chapter 3 on Locking and Concurrency in, each individual query is executed in a read consistent fashion. A read-only transaction (or an isolation level of serializable) extends that read consistency to the transaction level. If you are exporting tables that are linked via declarative RI (Referential Integrity), or nested table objects and need to be able to import them together later, using consistent = Y would be advised. This is especially true if the tables are likely to be modified as the export takes place.
TRANSPORT_ TABLESPACE	N	Specifies whether EXP will be used to export the meta data for a transportable tablespace set or not. See the section on Transporting Data for details.
TABLESPACES	NA	Used with Transport_tablespace to list the tablespaces being transported.

IMP Parameters

The following is the IMP output when passed the HELP = Y parameter:

C:\exp>imp help=y      Import: Release 8.1.6.0.0 - Production on Mon Mar 19 16:10:14 2001      (c) Copyright 1999 Oracle Corporation.  All rights reserved.      You can let Import prompt you for parameters by entering the IMP command followed by your username/password:           Example: IMP SCOTT/TIGER      Or, you can control how Import runs by entering the IMP command followed by various arguments. To specify parameters, you use keywords:           Format:  IMP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)      Example: IMP SCOTT/TIGER IGNORE=Y TABLES=(EMP,DEPT) FULL=N                or TABLES=(T1:P1,T1:P2), if T1 is partitioned table      USERID must be the first parameter on the command line.      Keyword  Description (Default)       Keyword      Description (Default) -------------------------------------------------------------------------- USERID   username/password           FULL         import entire file (N) FILE     input files (EXPDAT.DMP)    TOUSER       list of usernames SHOW     just list file contents (N) TABLES       list of table names IGNORE   ignore create errors (N)    RECORDLENGTH length of IO record GRANTS   import grants (Y)           INCTYPE      incremental import type INDEXES  import indexes (Y)          COMMIT       commit array insert (N) ROWS     import data rows (Y)        PARFILE      parameter filename LOG      log file of screen output   CONSTRAINTS  import constraints (Y) DESTROY  overwrite tablespace data file (N) INDEXFILE write table/index info to specified file SKIP_UNUSABLE_INDEXES  skip maintenance of unusable indexes (N) ANALYZE  execute ANALYZE statements in dump file (Y) FEEDBACK display progress every x rows(0) TOID_NOVALIDATE  skip validation of specified type ids FILESIZE maximum size of each dump file RECALCULATE_STATISTICS recalculate statistics (N)      The following keywords only apply to transportable tablespaces TRANSPORT_TABLESPACE import transportable tablespace metadata (N) TABLESPACES tablespaces to be transported into database DATAFILES datafiles to be transported into database TTS_OWNERS users that own data in the transportable tablespace set      Import terminated successfully without warnings.

We'll take a look at the important parameters that were not already discussed for EXP:

PARAMETER NAME	Default Value	Meaning/Notes
SHOW	N	If set to Y, import will SHOW you what it would have done; it will not actually do it. With SHOW = Y, no data will be added and no objects created.
IGNORE	N	When set to Y, IMP will ignore most object creation errors. Useful when you have pre-created the objects in the database and wish to use IMP just to populate the tables with data.
INDEXFILE	N/A	If specified, IMP will import all CREATE INDEX commands, and many other DDL statements, to the specified file (with REMs - comments - in front of them). No other objects are processed in the DMP file, only the index file is created.
FROMUSER	N/A	If set, this specifies a list of users to import from the DMP file. You can use this to restore a single schema from a full database export.
TOUSER	N/A	If set, this will import the objects from the user specified in the FROMUSER parameter, into the user specified by the TOUSER parameter. This allows you to 'clone' a user.
COMMIT	N	Specifies whether IMP will commit after each array insert. This is controlled by the BUFFER parameter. Normally, IMP will COMMIT after it loads a table fully. Since an insert generates the least amount of rollback possible, and because COMMITting frequently will slow down the insertion of data, as well as it causing generation of more redo logs, an IMP cannot be restarted after a failure. I recommend a setting of N for this parameter.
TTS_OWNERS	N/A	Used with TRANSPORTABLE_TABLESPACES, this will list the owners of objects in the transportable tablespace.

Large Exports

When EXP is used to write to a device that supports 'seeking', as a normal file does, it is limited in the size of the file it can generate. EXP uses the normal OS file APIs, which, on a 32-bit OS, limits the size of the file to 2GB. I know four solutions to this issue (although there are probably others), and we'll take a look at each of these.

Use the FILESIZE Parameter

This option first became available with Oracle 8i. Using the FILESIZE parameter we can set the maximum size (in bytes) of the DMP files that constitute our export, and EXP will create as many DMP files as necessary in order to export the data. For example, in order to export to a series of files that should be no larger then 500 MB apiece we can use:

exp userid=tkyte/tkyte file = f1,f2,f3,f4,f5 filesize = 500m owner = scott

This would create DMP files f1.dmp, f2.dmp, and so on, each of which would be up to 500 MB in size. If the total size of the export were less then 2GB, EXP would not need to create the f5.dmp file.

The downside to this feature however, is that unless you know approximately how big the export will be, the export will be interactive and hard to automate. Consider this export session. It should export about 2.3 MB of data into 500 KB DMP files:

C:\exp>exp userid=tkyte/tkyte tables=t file=(t1,t2,t3) filesize=500k      Export: Release 8.1.6.0.0 - Production on Mon Mar 19 14:54:12 2001      (c) Copyright 1999 Oracle Corporation.  All rights reserved.           Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production With the Partitioning option JServer Release 8.1.6.0.0 - Production Export done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set      About to export specified tables via Conventional Path ... . . exporting table                              T continuing export into file t2.DMP      continuing export into file t3.DMP      Export file: EXPDAT.DMP > t4      continuing export into file t4.DMP      Export file: EXPDAT.DMP > t5      continuing export into file t5.DMP       21899 rows exported Export terminated successfully without warnings.

The text Export file: EXPDAT.DMP> was an interactive prompt. After EXP used the filenames provided on the command line (t1, t2, t3), it started interactively prompting for the next file names to use. If this had been part of a background script, running late at night, EXP would have just waited for a response or, depending on the circumstances, it might have just failed since it would never get an answer. This feature may be acceptable in many cases - if you know the export will not go over 100 GB (a reasonable assumption on a 50 GB database for example), you could use a FILESIZE of two GB and generate a list of 50 filenames into a parameter file, (PARFILE) using a script. Then you could just use PARFILE = thatlist.par instead of FILE = (a very long list).

To import this data, we simply use IMP and list the files in the order they should be applied. IMP will not verify they are in the correct order and will fail miserably if you list them out of sequence. Fortunately, you can list more files then it needs, so if you use the PARFILE suggestion above, it can list files that do not exist and IMP will not complain. Here is an example:

C:\exp>imp userid=tkyte/tkyte full=y file=(t1,t2,t3,t4,t5,t6)      Import: Release 8.1.6.0.0 - Production on Mon Mar 19 15:49:24 2001      (c) Copyright 1999 Oracle Corporation.  All rights reserved.           Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production With the Partitioning option JServer Release 8.1.6.0.0 - Production      Export file created by EXPORT:V08.01.06 via conventional path import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set IMP-00046: using FILESIZE value from export file of 512000 . importing TKYTE's objects into TKYTE . . importing table                            "T"      21899 rows imported Import terminated successfully with warnings.

Export Smaller Pieces

This solves the problem by avoiding it altogether. If you have a 10 GB database with 50 application schemas, and each schema is under 2 GB in size, you can use user-level exports. You'll end up with 50 export files, each of which contain an application schema.

Export to an OS Pipe

This is a solution that works very well on UNIX. To date, I have not found a way to do this in the Windows environment. In this case I use the command mknod to create a named pipe. A named pipe is device by which one process can write into the pipe and another process can read out from the other end. EXP can write an unlimited amount of data to pipes, since they do not support 'seeking'. Further, the process that reads off of the other end of the pipe could be a compression routine. Using this, we can simultaneously export the data and compress it. In the event that the compressed file is still larger than 2 GB in size, we can use the split utility to break the file up into smaller pieces. Below is the commented script I use to do this on Unix. This script also shows how to import this compressed split data as well, since immediately after doing an export I run a full import with SHOW = Y to test the integrity of the DMP file I've just created:

#!/bin/csh -f      # Set this to the userid you want to perform the export as I always use OPS$ (os # authenticated) accounts for all jobs that will be run in the background. In that # way a password never appears in a script file or in the ps output. setenv UID /      # This is the name of the export file. SPLIT will use this to name the pieces of # the compressed DMP file. setenv FN  exp.`date +%j_%Y`.dmp      # This is the name of the named pipe we will use. setenv PIPE /tmp/exp_tmp_ora8i.dmp      # Here I limit the size of the compressed files to 500 MG each. Anything less # than 2 GB would be fine. setenv MAXSIZE 500m      # This is what we are going to export. By default I am doing a full database # export. setenv EXPORT_WHAT "full=y COMPRESS=n"           # This is where the export will go to. cd /nfs/atc-netapp1/expbkup_ora8i      # Clear out the last export. rm expbkup.log export.test exp.*.dmp* $PIPE      # Create the named pipe. mknod $PIPE p      # Write the datetime to the log file. date > expbkup.log      # Start a gzip process in the background. Gzip will read the pipe and put the # compressed data out to split.  Split will then create 500 MB files out of the # input data adding .aa, .ab, .ac, .ad, ... file extensions to the template name # found in $FN. ( gzip < $PIPE ) | split -b $MAXSIZE - $FN. &      # Now, start up export. The Gzip above is waiting for export to start filling the # pipe up. exp userid=$UID buffer=20000000 file=$PIPE $EXPORT_WHAT >>& expbkup.log date >> expbkup.log      # Now the export is done, this is how to IMP. We need to sort the filenames and # then simply cat their contents into gunzip. We write that into the pipe. IMP # will then read that pipe and write what it would do to stderr.  The >>& in the # csh redirects both stdout and stderr for us.      date > export.test cat `echo $FN.* | sort` | gunzip > $PIPE & imp userid=$UID file=$PIPE show=y full=y >>& export.test date >> export.test      # Clean up the pipe, we don't need it anymore. rm -f $PIPE

If you are using UNIX, I personally feel that the above is a better approach than using FILESIZE = with multiple file names specified on the command line for two reasons. Firstly, it allows us to compress the data before storing it and secondly, it will not prompt us for a filename as EXP might.

Export to a Device that does not Support Seeking

This again is a UNIX-only solution. You can export directly to a tape device just by specifying the name of the device. For example:

exp userid=tkyte/tkyte file=/dev/rmt/0 volsize = 6000m full = y

This will export directly to tape, stopping every 6000 MB to let us change the tapes if needed.

Subsetting Data

Oracle 8i introduced the ability for EXP to export only selected rows from a table. Prior to this release, EXP was an all or nothing event; either every row was exported or no rows were. We can now use the QUERY= parameter to supply a WHERE clause that will be applied to each exported table. It should be noted that when using a WHERE clause (the QUERY parameter) that the direct mode of export is not allowed; if you want a subset of data, you will be using the conventional path mode of EXP.

The method by which you specify the QUERY = parameter depends on the operating system. A WHERE clause will generally contain many special characters, such as =, > ,<, and spaces. The shell command prompt in UNIX and Windows is not going to like those characters very much. They will have to be escaped and the way to do this is dependent on the OS. What I prefer to do is to always use a PARFILE with the QUERY option. In this way, I can use the same exact methods regardless of platform.

As an example of this, I have created a table T as SELECT * FROM ALL_OBJECTS. I want to export all rows such that the object_id is less than 5000. On Windows, I would have to execute:

C:\exp>exp userid=tkyte/tkyte tables=t query="""where object_id < 5000"""

Note that, in Windows, we need three double quotes on either side of the WHERE clause. Now, the equivalent command on UNIX is:

$ exp userid=tkyte/tkyte tables=t query=\"where object_id \< 5000\"

However, if I simply use a parameter file, exp.par containing the following argument:

query="where object_id < 5000"

I can now use the single command on both systems without change:

exp userid=tkyte/tkyte tables=t parfile=exp.par

I find this to be much easier than trying to properly escape the QUERY strings on each platform.

Transporting Data

A transportable tablespace is a mechanism for taking the formatted datafiles of one database, and attaching them to another. Instead of unloading the data from one database to a flat file, or a DMP file, and then inserting that data into the other database, transporting a tablespace lets you move the data as fast as you can copy files.

There are some restrictions on transporting tablespaces, namely:

The source and target databases must be running on the same hardware platforms - You cannot take the datafiles from Windows NT and transfer them to HP/UX, for example. A DMP file can be copied from OS to OS - the DATAFILES of a database cannot be; the datafiles are not OS independent like a DMP file is.
The source and target databases must be using the same character set - You cannot take a set of files from a database with a WE8ISO8859P1 character set and attach them to a UTF8 instance, for example.
The source database must not have a tablespace by the same name - The tablespace name from the originating database will be used. If the source database already has a tablespace by that name, Oracle cannot attach another with that name.
The source and target database must have the same block sizes - You cannot attach the files from a 4 KB blocksize database to an 8 KB blocksize database.
You must transport a self-contained set of objects - For example, you cannot transport a tablespace that contains an index without also transporting the tablespace that contains the table, which the index is on.
There are some objects that cannot be transported - These include snapshots/materialized views, function-based indexes, domain indexes (such as those produced by interMedia), scoped refs, and advanced queues with more then one recipient.
The source database must set the tablespace to be transported in READ ONLY mode for a short period of time - This is the period of time it takes to export the tablespace meta data and copy the datafiles elsewhere.
SYS owned objects cannot be transported - If a tablespace contains any object own by SYS, the transport will fail. This means that objects like rollback segments, the system tablespace, and so on cannot be transported (which is reasonable, as there would be no reason to transport those objects in any case).

The following example works through all of the steps involved in transporting a tablespace. Just to make it interesting, I'm using two tablespaces. I'll begin by setting up the tablespaces, tables, and creating a new user for the example:

SQL> create tablespace tts_ex1   2  datafile 'c:\oracle\oradata\tkyte816\tts_ex1.dbf' size 1m   3  extent management local uniform size 64k; Tablespace created.      SQL> create tablespace tts_ex2   2  datafile 'c:\oracle\oradata\tkyte816\tts_ex2.dbf' size 1m   3  extent management local uniform size 64k; Tablespace created.      SQL> create user tts_user identified by tts_user   2  default tablespace tts_ex1   3  temporary tablespace temp; User created.      SQL> grant dba to tts_user; Grant succeeded.      SQL> connect tts_user/tts_user Connected.      SQL> create table emp as select * from scott.emp; Table created.      SQL> create table dept as select * from scott.dept; Table created.      SQL> create index emp_idx on emp(empno) tablespace tts_ex2; Index created.      SQL> create index dept_idx on dept(deptno) tablespace tts_ex2; Index created.      SQL> select object_type, object_name,   2                  decode(status,'INVALID','*','') status,   3                  tablespace_name   4  from user_objects a, user_segments b   5  where a.object_name = b.segment_name (+)   6  order by object_type, object_name   7  /      OBJECT_TYPE  OBJECT_NAME                    S TABLESPACE_NAME ------------ ------------------------------ - ------------------------------ INDEX        DEPT_IDX                         TTS_EX2              EMP_IDX                          TTS_EX2      TABLE        DEPT                             TTS_EX1              EMP                              TTS_EX1

Prior to attempting to export, we need to ensure we have a self-contained set of objects for transport. We can transport a table without its indexes, but we cannot transport an index without its tables. The following shows the routine that we should use to check if a tablespace, or set of tablespaces, are self-contained:

SQL> exec sys.dbms_tts.transport_set_check( 'tts_ex1', TRUE ); PL/SQL procedure successfully completed.      SQL> select * from sys.transport_set_violations; no rows selected      SQL> exec sys.dbms_tts.transport_set_check( 'tts_ex2', TRUE ); PL/SQL procedure successfully completed.      SQL> select * from sys.transport_set_violations;      VIOLATIONS ------------------------------------------------------------------------- Index TTS_USER.EMP_IDX in tablespace TTS_EX2 points to table TTS_USER.EMP in tablespace TTS_EX1 Index TTS_USER.DEPT_IDX in tablespace TTS_EX2 points to table TTS_USER.DEPT in tablespace TTS_EX1      SQL> exec sys.dbms_tts.transport_set_check( 'tts_ex1, tts_ex2', TRUE ); PL/SQL procedure successfully completed.      SQL> select * from sys.transport_set_violations; no rows selected

This shows that we can transport TTS_EX1, because it only contains table data and is self-contained. Any attempt to transport TTS_EX2, however, would fail since it contains indexes, but not the tables on which they are based. Lastly, both the tablespaces, TTS_EX1 and TTS_EX2 may be transported together, as we would be transporting both the tables and the indexes.

SYS.DBMS_TTS is executable by any DBA (they have EXECUTE ANY PROCEDURE) or any user with the EXECUTE_CATALOG_ROLE. It populates a dynamic table with any errors that would occur if we attempted to transport the tablespace(s). Now we are ready to 'detach' or transport these tablespace. We begin by making them READ ONLY:

SQL> alter tablespace tts_ex1 read only; Tablespace altered.      SQL> alter tablespace tts_ex2 read only; Tablespace altered.

We then issue the EXP command:

SQL> host exp userid="""sys/change_on_install as sysdba"""               transport_tablespace=y  tablespaces=(tts_ex1,tts_ex2)      Export: Release 8.1.6.0.0 - Production on Mon Mar 19 19:26:26 2001      (c) Copyright 1999 Oracle Corporation.  All rights reserved.      Production With the Partitioning option JServer Release 8.1.6.0.0 - Production Export done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set Note: table data (rows) will not be exported About to export transportable tablespace metadata... For tablespace TTS_EX1 ... . exporting cluster definitions . exporting table definitions . . exporting table                            EMP . . exporting table                           DEPT For tablespace TTS_EX2 ... . exporting cluster definitions . exporting table definitions . exporting referential integrity constraints . exporting triggers . end transportable tablespace metadata export Export terminated successfully without warnings.

Note the need for the triple, double quotes in order to specify the userid on the command line. On UNIX, we would have to escape the / as well. If we want to avoid that, we can just let EXP prompt us for the username. Also, note that we used as SYSDBA. Only a SYSDBA (internal) account may perform a transport in Oracle 8.1.6 and later versions. In Oracle 8.1.5 the DBA role was sufficient. (Note that this command has to be put into SQL*PLUS on one line. However, it is shown on two lines in the example below.)

Now all we need to do is copy the datafiles to another location. This could have been done in parallel with the export above to reduce the amount of time spent in read-only mode:

SQL> host XCOPY c:\oracle\oradata\tkyte816\tts_ex?.dbf c:\temp C:\oracle\oradata\tkyte816\TTS_EX1.DBF C:\oracle\oradata\tkyte816\TTS_EX2.DBF 2 File(s) copied      SQL> alter tablespace tts_ex1 read write; Tablespace altered.      SQL> alter tablespace tts_ex2 read write; Tablespace altered.

The tablespace is available for READs and WRITEs. Now, we can take this set of files to another database and attach them:

C:\exp> imp file=expdat.dmp userid="""sys/manager as sysdba"""    transport_tablespace=y    "datafiles=(c:\temp\tts_ex1.dbf,c:\temp\tts_ex2.dbf)"      Import: Release 8.1.6.0.0 - Production on Mon Mar 19 19:26:39 2001      (c) Copyright 1999 Oracle Corporation.  All rights reserved.           Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production With the Partitioning option JServer Release 8.1.6.0.0 - Production      Export file created by EXPORT:V08.01.06 via conventional path About to import transportable tablespace(s) metadata... import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set . importing SYS's objects into SYS . importing TTS_USER's objects into TTS_USER . . importing table                          "EMP" . . importing table                         "DEPT" Import terminated successfully without warnings.      SQL> update emp set ename=lower(ename); update emp set ename=lower(ename)        * ERROR at line 1: ORA-00372: file 9 cannot be modified at this time ORA-01110: data file 9: 'C:\TEMP\TTS_EX1.DBF'      SQL> alter tablespace tts_ex1 read write; Tablespace altered.      SQL> alter tablespace tts_ex2 read write; Tablespace altered.      SQL>  update emp set ename=lower(ename); 14 rows updated.

And that's it; the files are attached to the database. The final step shows they get attached in a READ ONLY mode (this makes sense, as they were read-only when we transported them). We might need to alter them after attachment. If you would like to test this out on a single database, you could execute these commands, or their equivalent, on your database, after you make the tablespace READ WRITE again, but before you perform the IMP:

SQL> drop tablespace tts_ex1 including contents; Tablespace dropped.      SQL> drop tablespace tts_ex2 including contents; Tablespace dropped.      SQL> host erase c:\oracle\oradata\tkyte816\tts_ex?.dbf

This is how I 'reset' a database for testing purposes. I transport the 'seed' database before testing and then, when I need to reset, simply drop the existing tablespaces and reattach the 'seed' database.

Transportable tablespaces may also be used to manually perform a tablespace point-in-time recovery. Suppose that you have 'accidentally' dropped a table. You can perform a restore of this database's SYSTEM, ROLLBACK and the affected tablespace on another machine. You would recover this mini-database to the point in time immediately prior to the errant drop table. Now you can transport the tablespace containing this table to the other database and re-attach it. This is in effect what the Recovery Manager (RMAN) does when it performs a point-in-time recovery of a tablespace. If you are not using RMAN, you can perform this operation easily yourself.

Another interesting use of this feature could be to share a large amount of read-only (or mostly read-only) data between two instances on the same machine. You could build a large tablespace, set it read-only, export the metadata and then import it to the other instance. You now have two databases that have read-only access to the same set of files. If you ever needed to modify the information, you would:

Drop the tablespace, including contents on the database to which you attached the datafiles
Alter the tablespace to be read-write in the source
Make the modifications
Alter the tablespace to be read-only
Export the metadata and re-import it to the other database.

The tablespace must be read-only whenever it is used by more than one database.

Getting the DDL

This is a nice side effect of EXP. We can use it to generate much of the DDL from our database. We've seen how this works in Chapter 6, Tables, where I used EXP and IMP to see a more verbose CREATE TABLE statement.

There are two ways to get the DDL: SHOW = Y and INDEXFILE = filename. I always recommend using the INDEXFILE option and never the SHOW = Y option. The latter is designed to show us what EXP would do if it was actually executed. The format of the output it produces is unusable as it is - it tends to wrap the DDL statements at awkward places and adds double quotes. Additionally, there is no clear delineation between the individual commands themselves. SHOW = Y is OK as a last ditch effort to extract some DDL, if that is all you have. We'll compare the results of the two options below and you'll see why INDEXFILE is the way to go.

Using the INDEXFILE option, we can reconstruct most of the DDL for a schema into a script file. For example, suppose I started with:

tkyte@TKYTE816> create table t1 ( x int primary key, y int ); Table created.      tkyte@TKYTE816> create table t2 (col1 int references t1, col2 int check (col2>0)); Table created.      tkyte@TKYTE816> create index t2_idx on t2(col2,col1); Index created.      tkyte@TKYTE816> create trigger t2_trigger before insert or update of col1, col2                                                                on t2 for each row   2  begin   3      if ( :new.col1 < :new.col2 ) then   4         raise_application_error(-20001,   5                      'Invalid Operation Col1 cannot be less then Col2');   6      end if;   7  end;   8  / Trigger created.      tkyte@TKYTE816> create view v   2  as   3  select t1.y t1_y, t2.col2 t2_col2 from t1, t2 where t1.x = t2.col1   4  / View created.

I can now run EXP and IMP as follows:

C:\>exp userid=tkyte/tkyte owner=tkyte C:\>imp userid=tkyte/tkyte full=y indexfile=tkyte.sql

Inspecting tkyte.sql shows:

REM  CREATE TABLE "TKYTE"."T1" ("X" NUMBER(*,0), "Y" NUMBER(*,0)) PCTFREE REM  10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 524288) REM  TABLESPACE "DATA" ; REM  ... 0 rows REM  ALTER TABLE "TKYTE"."T1" ADD PRIMARY KEY ("X") USING INDEX PCTFREE 10 REM  INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 524288) TABLESPACE "DATA" REM  ENABLE ; REM  CREATE TABLE "TKYTE"."T2" ("COL1" NUMBER(*,0), "COL2" NUMBER(*,0)) REM  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL REM  524288) TABLESPACE "DATA" ; REM  ... 0 rows CONNECT TKYTE; CREATE INDEX "TKYTE"."T2_IDX" ON "T2" ("COL2" , "COL1" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 524288) TABLESPACE "DATA" LOGGING ; REM  ALTER TABLE "TKYTE"."T2" ADD CHECK (col2>0) ENABLE ; REM  ALTER TABLE "TKYTE"."T2" ADD FOREIGN KEY ("COL1") REFERENCES "T1" REM  ("X") ENABLE ;

If I remove the REM statements, I have the DDL for objects that consume space, but not my trigger or view (procedures and such would be missing as well). EXP exports these objects, but IMP does not show them to me in the INDEXFILE option. The one way we can get IMP to show us these objects is via the SHOW option:

C:\ImpExp> imp userid=tkyte/tkyte show=y full=y Import: Release 8.1.6.0.0 - Production on Mon Apr 23 15:48:43 2001 (c) Copyright 1999 Oracle Corporation.  All rights reserved. Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production With the Partitioning option JServer Release 8.1.6.0.0 - Production      Export file created by EXPORT:V08.01.06 via conventional path import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set . importing TKYTE's objects into TKYTE  "CREATE TABLE "T1" ("X" NUMBER(*,0), "Y" NUMBER(*,0))  PCTFREE 10 PCTUSED 40"  " INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 524288) TABLESPACE "DATA"" . . skipping table "T1"       "CREATE TABLE "T2" ("COL1" NUMBER(*,0), "COL2" NUMBER(*,0))  PCTFREE 10 PCTU"  "SED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 524288) TABLESPACE ""  "DATA"" . . skipping table "T2"       "CREATE INDEX "T2_IDX" ON "T2" ("COL2" , "COL1" )  PCTFREE 10 INITRANS 2 MAX"  "TRANS 255 STORAGE(INITIAL 524288) TABLESPACE "DATA" LOGGING"  "CREATE FORCE VIEW "TKYTE"."V"                           ("T1_Y","T2_COL2") "  "AS "  "select t1.y t1_y, t2.col2 t2_col2 from t1, t2 where t1.x = t2.col1"      "CREATE TRIGGER "TKYTE".t2_trigger before insert or update of col1, col2 on "  "t2 for each row"  ""  "begin"  "    if ( :new.col1 < :new.col2 ) then"  "        raise_application_error(-20001,'Invalid Operation Col1 cannot be le"  "ss then Col2');"  "    end if;"  "end;"  "ALTER TRIGGER "T2_TRIGGER"  ENABLE" Import terminated successfully without warnings.

You should note that this output is wholly unsuitable for general-purpose use. For example, consider:

"CREATE TABLE "T2" ("COL1" NUMBER(*,0), "COL2" NUMBER(*,0))  PCTFREE 10 PCTU"  "SED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 524288) TABLESPACE ""

IMP breaks lines at arbitrary places - the word PCTUSED is broken in half. Additionally each line begins and ends with a double quote. Simply removing these quotes will not make this script file usable because the commands are broken at inappropriate points. Not only that, but our source code is 'damaged' as well:

 "    if ( :new.col1 < :new.col2 ) then"  "        raise_application_error(-20001,'Invalid Operation Col1 cannot be le"  "ss then Col2');"  "    end if;"

IMP stuck a newline right into the middle of our line of code. Lastly, the commands themselves are not separated by anything:

 "CREATE INDEX "T2_IDX" ON "T2" ("COL2" , "COL1" )  PCTFREE 10 INITRANS 2 MAX"  "TRANS 255 STORAGE(INITIAL 524288) TABLESPACE "DATA" LOGGING"  "CREATE FORCE VIEW "TKYTE"."V"                           ("T1_Y","T2_COL2") "  "AS "  "select t1.y t1_y, t2.col2 t2_col2 from t1, t2 where t1.x = t2.col1"  "CREATE TRIGGER "TKYTE".t2_trigger before insert or update of col1, col2 on "  "t2 for each row"

The CREATE INDEX command runs into the CREATE VIEW, which runs into the CREATE TRIGGER, and so on (you get the point!). This file would need some serious editing to be useful. The things we were missing are here now - they are not in a format that is ready to run but they are salvageable. I find this useful in a pinch, when someone has accidentally dropped their last month's work (their code) and needs to retrieve it. That is why I export my database twice a week and do the IMP...SHOW = Y on it (as demonstrated in the section on Large Exports). More then once, I was able to resurrect a relatively recent copy of the code for them from the output of that command. This removed the need to do a database point-in-time recovery to get their code back (that would be the other alternative - the data is never lost in a database!).

Getting around the limitations with scripts

If I have to move PL/SQL code from one schema to another, I prefer to use scripts. I have scripts to retrieve a package, procedure, or function. Another script extracts views. Yet another does triggers. Moving these types of objects is not something EXP/IMP is adept at. Ask EXP/IMP for a table and it is great. Ask EXP/IMP to give your view definition back and it is not so good.

Since they are so useful, I'll include the scripts for extracting the above objects here in the EXP section. You probably came to this section trying to find out how to get IMP to give you back your code anyway. Now, you know that IMP is not going to give it to you in a format that is usable.

So, here is a script that will retrieve any package (including the package body), function, or procedure, and write it to a SQL file of that same name. If you execute SQL> @getcode my_procedure, this script will create a file my_procedure.sql that contains that PL/SQL routine:

REM getcode.sql - extract any procedure, function or package set feedback off set heading off set termout off set linesize 1000 set trimspool on set verify off spool &1..sql prompt set define off select decode( type||'-'||to_char(line,'fm99999'),                'PACKAGE BODY-1', '/'||chr(10),                 null) ||        decode(line,1,'create or replace ', '' ) ||        text text   from user_source  where name = upper('&&1')  order by type, line; prompt / prompt set define on spool off set feedback on set heading on set termout on set linesize 100

For those of you who want to extract all code from a schema, I have a script called getallcode.sql. This will create a script file per PL/SQL object in the current working directory, and then create a script getallcode_INSTALL that will install the code in another schema for you:

set termout off set heading off set feedback off set linesize 50 spool xtmpx.sql select '@getcode ' || object_name from user_objects where object_type in ( 'PROCEDURE', 'FUNCTION', 'PACKAGE' ) / spool off spool getallcode_INSTALL.sql select '@' || object_name from user_objects where object_type in ( 'PROCEDURE', 'FUNCTION', 'PACKAGE' ) / spool off set heading on set feedback on set linesize 130 set termout on @xtmpx.sql

Now, the following script is used to extract a single view. If you execute SQL> @getaview view_name, it will create a file view_name.sql in the current directory with the CREATE VIEW statement in it:

REM getaview.sql set heading off set long 99999999 set feedback off set linesize 1000 set trimspool on set verify off set termout off set embedded on      column column_name format a1000 column text format a1000      spool &1..sql prompt create or replace view &1 ( select decode(column_id,1,'',',') || column_name  column_name   from user_tab_columns  where table_name = upper('&1')  order by column_id / prompt ) as select text   from user_views  where view_name = upper('&1') / prompt / spool off      set termout on set heading on set feedback on set verify on

Of course, if you want all views, there is the script getallviews:

set heading off set feedback off set linesize 1000 set trimspool on set verify off set termout off set embedded on      spool tmp.sql select '@getaview ' || view_name from user_views / spool off      set termout on set heading on set feedback on set verify on @tmp

Lastly, there is gettrig.sql. This does not handle every case of triggers. For example, I do not retrieve the referencing OLD as ... statement as I've never used it. The concept is the same as above and would be easy enough to modify if you find the trigger construct useful:

set echo off set verify off set feedback off set termout off set heading off set pagesize 0 set long 99999999 spool &1..sql      select 'create or replace trigger "' ||          trigger_name || '"' || chr(10)||  decode( substr( trigger_type, 1, 1 ),          'A', 'AFTER', 'B', 'BEFORE', 'I', 'INSTEAD OF' ) ||               chr(10) ||  triggering_event || chr(10) ||  'ON "' || table_owner || '"."' ||        table_name || '"' || chr(10) ||  decode( instr( trigger_type, 'EACH ROW' ), 0, null,             'FOR EACH ROW' ) || chr(10) ,  trigger_body from user_triggers where trigger_name = upper('&1') / prompt /      spool off set verify on set feedback on set termout on set heading on

So, this shows how we can use EXP/IMP to get our DDL; our tables and indexes, things that consume space. For things that do not really consume space including, but not limited to, triggers, procedures, views, sequences, synonyms, and the like, simple SQL*PLUS scripts are the best approach. IMP with the SHOW = Y can be used in a pinch, but if you have an ongoing need to extract these objects, a script is what you want.

Backup and Recovery

EXP and IMP should not be considered backup tools. They are not appropriate to be your backup and recovery mechanism. RMAN and operating system backups are the only true backups. The reasons EXP/IMP should not be considered as your backup tools are:

They present at best, a point-in-time picture of the database. Using CONSISTENT = Y will allow you to extract a point in time picture of the database (perhaps, remember the chance for the ORA-01555 snapshot too old increases with the length of your transaction), but it is just that - a single point-in-time. If you use this export to recover, you will lose all work that happened after the start of the EXP. Also, archived redo logs cannot be applied to an IMP.
Restoring a database of any significant size using IMP is a slow process; all data will have to be inserted (through the SQL engine, generating rollback and redo), all indexes will have to be rebuilt, all constraints must be validated, all code must be compiled, and so on. What might take a few minutes with a real backup will take hours or days using IMP.
Incremental EXP/IMP will soon no longer be a supported feature. The use of the INCTYPE = parameter will be removed. The quote from Oracle is 'Important: Incremental, cumulative, and complete Exports are obsolete features that will be phased out in a subsequent release. You should begin now to migrate to Oracle's Backup and Recovery Manager for database backups'. See Oracle8i Operating System Backup and Recovery Guide for more information.

Does this mean that EXP/IMP utilities are not useful as part of a larger backup and recovery plan? Well, I believe they can play an important role as part of a larger backup and recovery plan. Your production database must be running in archive log mode to allow you to perform 'point in time' and media recovery (recovery from a failed disk). This is crucial and there is no substitute for it. In addition to this, some proactive fault detection is a good idea as part of a well-rounded backup/recovery plan. As part of this, I use EXP as mentioned above. This fully exercises the data dictionary, using almost all of its indexes and objects, ensuring it is OK. It also scans all table data, ensuring that it is safe (if an index goes bad it is easily recreated so I'm not as worried about testing those). The resulting DMP file can also be useful to extract that lost piece of code, or that accidentally dropped table at times as well, saving us from having to perform a point in time recovery in many cases.

Other tools such as DBV (the database verifier), may be run against the datafiles periodically as well to ensure the physical integrity of the data, checking out those index structures that EXP might not get to.

IMP/EXP is not a Reorganization Tool (Any More)

This used to be one of the major uses of EXP/IMP. In order to 'defragment' a fragmented tablespace, DBAs would spend time exporting a set of objects, dropping them, and then importing them back in. Many DBAs spent a considerable amount of time doing this on a recurring basis. The fact is, they should never have had to do this more then once and, in most cases, never really needed to do it at all. They should never have had to do it more than once because if the tablespace really was fragmented, one would hope they would use more appropriate planning for storage during the export/import process; one that would avoid fragmentation. In most cases however, they did not, so history was doomed to repeat itself. In other cases, they were doing it because they heard it was a 'good thing', something that needed to be done, when in fact they probably did not need to do it at all.

That aside, using EXP/IMP in this manner was fraught with danger. You were taking all of the data out of the database, dropping it, and bringing it back in. There would be a period of time where the data was no longer protected by the database. There was the chance the IMP wouldn't work (it is just a program after all). There is a chance that someone would change the data as you were exporting it (you wouldn't see these changes in the export) and you would lose their changes. You could lose grants on objects and so on. It required a lot of planning and downtime to carry this out.

In Oracle8i, you never need to use EXP/IMP to reorganize data. If you truly believe you need to (and I am a firm believer that you should never need to more than once to correct a bad implementation), then you can use the ALTER TABLE MOVE command to move tables from tablespace to tablespace, change their storage characteristics, and so on. The table will be available for querying during this period of reorganization, but not for updates. Immediately after the move, the indexes will become unusable and will need to be rebuilt, so queries will be affected at that point in time. The downtime is considerably less than the corresponding downtime using EXP/IMP and none of the issues listed above come into play; there is never any time where the data is not protected by the database, there is no chance of losing an update to the data, grants are not touched by this process, and so on.

In short, the days of EXP/IMP as a reorganization tool are over. Do not even consider them for this job.

Importing into Different Structures

This is an issue that comes up frequently; you have an export of some data and need to import it into a slightly different structure. Is there any way to do that? I've seen this when exporting data from version 1 of a software package into a database that has version 2 of the software (or vice versa). The answer is yes, but we might need some help in doing so. There are 3 cases to consider here:

You have added a column (no work required on your part, Oracle will put a Null or whatever default you have set in it)
You have dropped a column (some work on your part)
You have changed the data type of a column (again, some work on your part)

For the additional column case, we need to do nothing. Oracle will insert into the table normally using null values, or whatever default value that we have specified. For the dropped and modified columns, we will need to import into a view, using an INSTEAD OF trigger to perform whatever data mapping we need. Note that the use of an INSTEAD OF trigger will obviously add overhead - this is a fine solution for medium sets of data, but you would not want to load tens of millions of rows in this fashion! Here are the tables:

tkyte@TKYTE816> create table added_a_column ( x int ); Table created.      tkyte@TKYTE816> create table dropped_a_column ( x int, y int ); Table created.      tkyte@TKYTE816> create table modified_a_column( x int, y int ); Table created.      tkyte@TKYTE816> insert into added_a_column values ( 1 ); 1 row created.      tkyte@TKYTE816> insert into dropped_a_column values ( 1, 1 ); 1 row created.      tkyte@TKYTE816> insert into modified_a_column values ( 1, 1 ); 1 row created.      tkyte@TKYTE816> commit; Commit complete.

We will start by exporting the three tables (this command should be in one line, otherwise you will export the whole schema):

tkyte@TKYTE816> host exp userid=tkyte/tkyte                   tables=(added_a_column,dropped_a_column,modified_a_column)      Export: Release 8.1.6.0.0 - Production on Tue Mar 20 09:02:34 2001 (c) Copyright 1999 Oracle Corporation.  All rights reserved.      Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production With the Partitioning option JServer Release 8.1.6.0.0 - Production Export done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set      About to export specified tables via Conventional Path ... . . exporting table                 ADDED_A_COLUMN          1 rows exported . . exporting table               DROPPED_A_COLUMN          1 rows exported . . exporting table              MODIFIED_A_COLUMN          1 rows exported Export terminated successfully without warnings.

So, that constitutes our test case. We have exported the three tables 'as is'. Now, let's modify them:

tkyte@TKYTE816> alter table added_a_column add ( y int ); Table altered.      tkyte@TKYTE816> alter table dropped_a_column drop column y; Table altered.      tkyte@TKYTE816> delete from modified_a_column; 1 row deleted.      tkyte@TKYTE816> alter table modified_a_column modify y date; Table altered.

Now, if we attempt to import, we will find that ADDED_A_COLUMN works OK, but the rest fail:

tkyte@TKYTE816> host imp userid=tkyte/tkyte full=y ignore=y      Import: Release 8.1.6.0.0 - Production on Tue Mar 20 09:02:34 2001 (c) Copyright 1999 Oracle Corporation.  All rights reserved.      Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production With the Partitioning option JServer Release 8.1.6.0.0 - Production      Export file created by EXPORT:V08.01.06 via conventional path import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set . importing TKYTE's objects into TKYTE . . importing table               "ADDED_A_COLUMN"          1 rows imported . . importing table             "DROPPED_A_COLUMN" IMP-00058: ORACLE error 904 encountered ORA-00904: invalid column name . . importing table            "MODIFIED_A_COLUMN" IMP-00058: ORACLE error 932 encountered ORA-00932: inconsistent datatypes Import terminated successfully with warnings.

The next step is to create views in the database- views that look like the original tables. To accomplish this we will:

Rename the tables for the duration of the import
Create a view that selects constants of the correct type - for example, select 1 for a number, SELECT SYSDATE for a date, SELECT RPAD('*', 30, '*') for a VARCHAR2(30), and so on
Create an INSTEAD OF trigger that 'does the right thing' for us, performing any data conversion/mapping we need

Here is the code to do this:

tkyte@TKYTE816> rename modified_a_column to modified_a_column_TEMP; Table renamed.      tkyte@TKYTE816> create or replace view modified_a_column   2  as   3  select 1 x, 1 y from modified_a_column_TEMP; View created.      tkyte@TKYTE816> create or replace trigger modified_a_column_IOI   2  instead of insert on modified_a_column   3  begin   4          insert into modified_a_column_TEMP   5          ( x, y )   6          values   7          ( :new.x, to_date('01012001','ddmmyyyy')+:new.y );   8  end;   9  / Trigger created.

Here, we are converting the NUMBER that was stored in Y, into an offset from January 1, 2001. You would perform whatever conversion you required: from STRING to DATE, DATE to NUMBER, NUMBER to STRING, and so on. Now we take care of the dropped column:

tkyte@TKYTE816> rename dropped_a_column  to dropped_a_column_TEMP; Table renamed.      tkyte@TKYTE816> create or replace view dropped_a_column   2  as   3  select 1 x, 1 y from dropped_a_column_TEMP; View created. tkyte@TKYTE816> create or replace trigger dropped_a_column_IOI   2  instead of insert on dropped_a_column   3  begin   4          insert into dropped_a_column_TEMP   5          ( x )   6          values   7          ( :new.x );   8  end;   9  / Trigger created.

Here, we are just getting rid of :new.y. We do not do anything with it - we just ignore it. It needs to be in the view so IMP has something to insert into. Now we are ready to import again:

tkyte@TKYTE816> host imp userid=tkyte/tkyte full=y ignore=y      Import: Release 8.1.6.0.0 - Production on Tue Mar 20 09:21:41 2001 (c) Copyright 1999 Oracle Corporation.  All rights reserved.      Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production With the Partitioning option JServer Release 8.1.6.0.0 - Production      Export file created by EXPORT:V08.01.06 via conventional path import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set . importing TKYTE's objects into TKYTE . . importing table               "ADDED_A_COLUMN"          1 rows imported . . importing table             "DROPPED_A_COLUMN"          1 rows imported . . importing table            "MODIFIED_A_COLUMN"          1 rows imported Import terminated successfully without warnings.

The import runs cleanly. Next, we need to go into the database, drop our views, and rename our tables:

tkyte@TKYTE816> drop view modified_a_column; View dropped.      tkyte@TKYTE816> drop view dropped_a_column; View dropped.      tkyte@TKYTE816> rename dropped_a_column_TEMP to dropped_a_column; Table renamed.      tkyte@TKYTE816> rename modified_a_column_TEMP to modified_a_column; Table renamed.

When we look at the data, we expect to see the following:

Three rows in added_a_column from the original insert and two imports
Two rows in dropped_a_column from the original insert and one import that worked on it
One row in modified_a_column since we had to empty this table prior to altering the column type

And that is what we get:

tkyte@TKYTE816> select * from added_a_column;               X          Y ---------- ----------          1          1          1      tkyte@TKYTE816> select * from dropped_a_column;               X ----------          1          1      tkyte@TKYTE816> select * from modified_a_column;               X Y ---------- ---------          1 02-JAN-01

Direct Path Exports

A direct path export should not be considered the converse of a direct path load using SQLLDR (see Chapter 9). A direct path export does not read directly from the datafiles and write to the DMP file. SQLLDR does write directly to the datafiles from the DAT files. Export in direct path mode simply bypasses the SQL evaluation buffer (WHERE clause processing, column formatting and such). Ninety percent of the path is the same. EXP is still reading buffers into the buffer cache, doing the same consistent read processing, and so on.

The speedup obtained using the direct path export can be large, however. The ten percent of the processing that it cuts out, accounts for a much larger percentage of the run-time. For example, I have just exported about 100MB of data, and 1.2 million records. The direct path export took about one minute. The conventional path export on the other hand took three minutes. There is unfortunately, no corresponding 'direct path import'. Import uses conventional SQL to insert the data back into tables. SQLLDR is still the tool to use for high performance data loads.

It should be noted that in direct path mode, you cannot use the QUERY = parameter to select a subset of rows. This makes sense given that DIRECT = Y is simply a way to bypass the SQL evaluation buffer and that is where the where clause processing would normally take place.

Caveats and Errors

In this section, I would like to cover some of the issues and problems with EXP/IMP that people frequently encounter. Here, we will look into issues surrounding:

Using EXP/IMP to 'clone' a schema
Using EXP/IMP across heterogeneous versions of Oracle
The case of the 'disappearing' Index
The impact of constraints that have system-assigned names
Issues with regards to NLS (National Language Support)
Issues with regards to objects that may have multiple tablespace name references, such as tables with LOBs

Cloning

This is a common use of EXP/IMP; you want to copy an entire application schema. You need to copy all of the tables, triggers, views, procedures, and so on. In general, this works great, I simply use the following:

Exp userid=tkyte/tkyte owner=old_user Imp userid=tkyte/tkyte fromuser=old_user touser=new_user

A potentially nasty issue arises, however, when the application schema you are copying from uses schema-qualified references to its own objects. What I mean is that, given a user A, they code something like:

create trigger MY_trigger before insert on A.table_name begin    ...; end; /

This creates a trigger that is explicitly on the table A.TABLE_NAME. If we export that and import it into another schema, this trigger will still be on A.TABLE_NAME, it will not be on the table named TABLE_NAME in the other schema. EXP/IMP are a little inconsistent in the way they handle this condition, however. Consider the following schema:

tkyte@TKYTE816> create table t1   2  ( x int primary key ); Table created.      tkyte@TKYTE816> create table t4 ( y int references TKYTE.t1 ); Table created.      tkyte@TKYTE816> create trigger t2_trigger   2  before insert on TKYTE.t4   3  begin   4    null;   5  end;   6  / Trigger created.      tkyte@TKYTE816> create or replace view v   2  as   3  select * from TKYTE.t1; View created.

We have a referential integrity constraint that explicitly references TKYTE.T1, a trigger explicitly on TKYTE.T4, and a view that explicitly references TKYTE.T1. I'll export this schema and create a user to import it into and perform the import (note that the user performing the import with the FROMUSER and TOUSER options must have the role IMP_FULL_DATABASE granted to them):

tkyte@TKYTE816> host exp userid=tkyte/tkyte owner=tkyte   tkyte@TKYTE816> grant connect,resource to a identified by a; Grant succeeded.      tkyte@TKYTE816> host imp userid=system/change_on_install fromuser=tkyte touser=a      Import: Release 8.1.6.0.0 - Production on Tue Mar 20 09:56:17 2001      (c) Copyright 1999 Oracle Corporation.  All rights reserved.           Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production With the Partitioning option JServer Release 8.1.6.0.0 - Production      Export file created by EXPORT:V08.01.06 via conventional path      Warning: the objects were exported by TKYTE, not by you      import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set . importing TKYTE's objects into A . . importing table                           "T1"          0 rows imported . . importing table                           "T4"          0 rows imported IMP-00041: Warning: object created with compilation warnings  "CREATE FORCE VIEW "A"."V"                               ("X") AS "  "select "X" from TKYTE.t1" Import terminated successfully with warnings.

Now, we can see already that the view is a problem - it explicitly references TKYTE.T1 and the user A cannot create a view on that object, due to insufficient privileges. Fortunately, IMP makes it clear that the view created with an error. What is not so clear is what happened with the declarative RI and the trigger. Inspection of the data dictionary when logged in as A shows us:

a@TKYTE816> select table_name, constraint_name,   2    constraint_type, r_constraint_name   3    from user_constraints   4  /      TABLE_NAME           CONSTRAINT_NAME               C R_CONSTRAINT_NAME ------------------- ------------------------------ - ------------------- T1                  SYS_C002465                    P T4                  SYS_C002466                    R SYS_C002465      a@TKYTE816> select trigger_name, table_owner, table_name   2    from user_triggers   3  /      TRIGGER_NAME      TABLE_OWNER          TABLE_NAME ----------------- -------------------- ------------------------------ T2_TRIGGER        TKYTE                T4

Surprisingly, our referential integrity constraint points to user A's table. The R constraint is the reference and the constraint it points to, SYS_C002465, is the primary key constraint on table T1 - user A's table T1. If you continue further in this train of thought, if the schema TKYTE had a qualified RI constraint that pointed to a table B.T (a table T owned by B), this RI constraint would be imported into A's schema as pointing to B.T as well. If the qualified schema name on the referenced table in the constraint is the same as the owner of the table at export time, EXP will not preserve the name.

Consider this in comparison to the trigger. The trigger T2_TRIGGER is not on user A's table - it is actually on user TKYTE's table! This is a side effect that is potentially disastrous. Consider that the trigger is duplicated on TKYTE.T4 - its logic will execute two times alongside the fact that the trigger is not on A.T4 at all.

I would urge you to consider these issues when using EXP/IMP to clone a user. Be aware that they exist and look for them. If you execute the following, you'll be able to review all of the DDL, triggers, procedures, and so on, before running them in the database:

Imp userid=sys/manager fromuser=tkyte touser=a INDEXFILE=foo.sql Imp userid=sys/manager fromuser=tkyte touser=a SHOW=Y

At the very least, consider doing the import into a database that does not have the FROMUSER account. For example, here I do the above import into a database where A exists, but TKYTE does not:

C:\exp>imp userid=sys/manager fromuser=tkyte touser=a      Import: Release 8.1.6.0.0 - Production on Tue Mar 20 10:29:37 2001 (c) Copyright 1999 Oracle Corporation.  All rights reserved.      Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production With the Partitioning option JServer Release 8.1.6.0.0 - Production      Export file created by EXPORT:V08.01.06 via conventional path      Warning: the objects were exported by TKYTE, not by you      import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set . importing TKYTE's objects into A . . importing table                           "T1"          0 rows imported . . importing table                           "T4"          0 rows imported . . importing table                           "T5"          0 rows imported IMP-00041: Warning: object created with compilation warnings  "CREATE FORCE VIEW "A"."V"                               ("X") AS "  "select "X" from TKYTE.t1" IMP-00017: following statement failed with ORACLE error 942:  "CREATE TRIGGER "A".t2_trigger"  "before insert on TKYTE.t4"  ""  "begin"  "  null;"  "end;" IMP-00003: ORACLE error 942 encountered ORA-00942: table or view does not exist Import terminated successfully with warnings.

I will be able to discover these nuances immediately. I would highly recommend this approach to discovering the objects that have qualified schema names in them, and determining whether or not they are correct.

A similar, but slightly different issue occurs with Oracle's object types. Oracle allows you to create new datatypes in the database. In the same manner that they provide you with the NUMBER, DATE, VARCHAR2... types, you can add types to the database. You can then create tables of that type or create tables with columns, which are of that type. A common course of action is therefore, to create a schema and set about creating types in the schema and objects that rely on those types - everything in one user account. If you ever want to 'clone' that schema, you'll run into a serious problem. I'll demonstrate the problem first, and then describe why it happens and how to partially solve it.

First, we'll start with a schema like this:

tkyte@TKYTE816> create type my_type   2  as object   3  ( x int,   4    y date,   5    z varchar2(20)   6  )   7  / Type created.      tkyte@TKYTE816> create table t1 of my_type   2  / Table created.      tkyte@TKYTE816> create table t2 ( a int, b my_type ); Table created.      tkyte@TKYTE816> insert into t1 values ( 1, sysdate, 'hello' ); 1 row created.      tkyte@TKYTE816> insert into t2 values ( 55, my_type( 1, sysdate, 'hello') ); 1 row created.      tkyte@TKYTE816> commit; Commit complete.      tkyte@TKYTE816> host exp userid=tkyte/tkyte owner=tkyte

This gives us a copy of this schema. When we try to use the FROMUSER/TOUSER option however, we quickly discover this:

tkyte@TKYTE816> host imp userid=sys/manager fromuser=tkyte touser=a;      Import: Release 8.1.6.0.0 - Production on Tue Mar 20 12:44:26 2001      (c) Copyright 1999 Oracle Corporation.  All rights reserved.           Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production With the Partitioning option JServer Release 8.1.6.0.0 - Production Export file created by EXPORT:V08.01.06 via conventional path      Warning: the objects were exported by TKYTE, not by you      import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set . importing TKYTE's objects into A IMP-00017: following statement failed with ORACLE error 2304:  "CREATE TYPE "MY_TYPE" TIMESTAMP '2001-03-20:12:44:21' OID '4A301F5AABF04A46"  "88552E4AF5793176'  "  "as object"  "( x int,"  "  y date,"  "  z varchar2(20)"  ")" IMP-00003: ORACLE error 2304 encountered ORA-02304: invalid object identifier literal IMP-00063: Warning: Skipping table "A"."T1" because object type "A"."MY_TYPE" cannot be created or has different identifier IMP-00063: Warning: Skipping table "A"."T2" because object type "A"."MY_TYPE" cannot be created or has different identifier Import terminated successfully with warnings.

We are basically stuck at this point. We cannot create the type in A's schema, and even if we could, it would be a different type and the import would still not be happy. It would be like having two different NUMBER types in the database - they would be different from each other. In the above, we were trying to create two different MY_TYPE types, but treat them as one.

The issue, and this is not clear in the documentation, is that we should not create a schema that has both types and objects, especially if we want to import/export the schema in this fashion. In a similar manner to the way in which CTXSYS and ORDSYS are setup for Oracle's interMedia, we should setup a schema to contain our types. If we wish to use interMedia Text, we use the types available from CTXSYS. If we wish to use interMedia image capabilities, we use ORDSYS types. We should do the same here. What we do is set up a schema that will hold our types - any and all of our types:

our_types@TKYTE816> connect OUR_TYPES      our_types@TKYTE816> create type my_type   2  as object   3  ( x int,   4    y date,   5    z varchar2(20)   6  )   7  / Type created.      our_types@TKYTE816> grant all on my_type to public; Grant succeeded.

All schemas will use these types, never their own personal types, which will become strongly tied to their account. Now, we will redo our example from above:

tkyte@TKYTE816> connect tkyte      tkyte@TKYTE816> create table t1 of our_types.my_type   2  / Table created.      tkyte@TKYTE816> create table t2 ( a int, b our_types.my_type ); Table created.      tkyte@TKYTE816> insert into t1 values ( 1, sysdate, 'hello' ); 1 row created.      tkyte@TKYTE816> insert into t2 values ( 55,   2  our_types.my_type( 1, sysdate, 'hello') ); 1 row created.      tkyte@TKYTE816> commit; Commit complete.      tkyte@TKYTE816> host exp userid=tkyte/tkyte owner=tkyte

So, the only difference here is that we are using OUR_TYPES.MY_TYPE, not just MY_TYPE. Since we can never create synonyms for types, this schema qualification is mandatory - we must fully qualify the object name with the schema name that owns them. This is the way we must do it when using interMedia types (CTXSYS, ORDSYS objects for example) and indexes as well- we always fully qualify them. Pick the schema name of your type holder with care for this reason, you will be living with it for a while!

Now, let's see how the import goes this time:

tkyte@TKYTE816> host imp userid=sys/manager fromuser=tkyte touser=a;      Import: Release 8.1.6.0.0 - Production on Tue Mar 20 12:49:33 2001 (c) Copyright 1999 Oracle Corporation.  All rights reserved.      Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production With the Partitioning option JServer Release 8.1.6.0.0 - Production      Export file created by EXPORT:V08.01.06 via conventional path      Warning: the objects were exported by TKYTE, not by you      import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set . importing TKYTE's objects into A IMP-00017: following statement failed with ORACLE error 2304:  "CREATE TABLE "T1" OF "OUR_TYPES"."MY_TYPE" OID 'AC60D4D90ED1428B84D245357AD"  "F2DF3' OIDINDEX (PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 524288)"  " TABLESPACE "DATA") PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING S"  "TORAGE(INITIAL 524288) TABLESPACE "DATA"" IMP-00003: ORACLE error 2304 encountered ORA-02304: invalid object identifier literal . . importing table                           "T2"          1 rows imported Import terminated successfully with warnings.

We fared better, but it is still not perfect yet. The pure OBJECT table failed, but the relational table with the object type succeeded. This is to be expected. The object table is not the same object table, technically, and objects are very picky about that. We can work around this particular issue however, since these two tables are in fact to be constructed on the same type. What we must do is pre-create the object table in A's schema. We can use the IMP INDEXFILE= option to get the DDL:

a@TKYTE816> host imp userid=a/a tables=t1 indexfile=t1.sql

If we edit the resulting T1.SQL we'll find:

REM  CREATE TABLE "A"."T1" OF "OUR_TYPES"."MY_TYPE" OID REM  'AC60D4D90ED1428B84D245357ADF2DF3' OIDINDEX (PCTFREE 10 INITRANS 2 REM  MAXTRANS 255 STORAGE(INITIAL 524288) TABLESPACE "DATA") PCTFREE 10 REM  PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 524288) REM  TABLESPACE "DATA" ; REM  ALTER TABLE "A"."T1" MODIFY ("SYS_NC_OID$" DEFAULT SYS_OP_GUID()) ; REM  ... 1 rows

We need to remove the REM characters as well as the OID xxxxx clause, and then execute:

a@TKYTE816> CREATE TABLE "A"."T1" OF "OUR_TYPES"."MY_TYPE"   2  OIDINDEX (PCTFREE 10 INITRANS 2   3  MAXTRANS 255 STORAGE(INITIAL 524288) TABLESPACE "DATA") PCTFREE 10   4  PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 524288)   5  TABLESPACE "DATA" ;      Table created.      a@TKYTE816> ALTER TABLE "A"."T1" MODIFY ("SYS_NC_OID$" DEFAULT SYS_OP_GUID()) ;      Table altered.

Now we can execute:

a@TKYTE816> host imp userid=a/a tables=t1 ignore=y      Import: Release 8.1.6.0.0 - Production on Tue Mar 20 13:01:24 2001      (c) Copyright 1999 Oracle Corporation.  All rights reserved.           Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production With the Partitioning option JServer Release 8.1.6.0.0 - Production      Export file created by EXPORT:V08.01.06 via conventional path      Warning: the objects were exported by TKYTE, not by you      import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set . importing TKYTE's objects into A . . importing table                           "T1"          1 rows imported Import terminated successfully without warnings. a@TKYTE816> select * from t1;               X Y         Z ---------- --------- --------------------          1 20-MAR-01 hello

and we have our data back.

Using IMP/EXP Across Versions

You can easily IMP and EXP across different versions of Oracle. You can even EXP and IMP to and from version 7 databases and version 8 databases. However, you have to use the proper version of EXP and IMP when doing so. The rules for picking the version of IMP and EXP are:

Always use the version of IMP that matches the version of the database. If you are going to import into version 8.1.6, use the 8.1.6 import tool.
Always use the version of EXP that matches the lowest of the two versions of the database. If you were exporting from version 8.1.6 to 8.1.5, you would use the version 8.1.5 EXP tool, over Net8, against the version 8.1.6 database. If you were exporting from version 8.1.5 to 8.1.6, you would use the version 8.1.5 EXP tool directly against the 8.1.5 database.

This is crucial - if you attempt to export from 8.1.6 to 8.0.5, for example, and you use the version 8.1.6 EXP tool, you'll find that the 8.0.5 IMP tool cannot read the DMP file. Furthermore, you cannot use the version 8.1.6 IMP tool against version 8.0.5; it will not work. There are things in the 8.1.6 database that simply did not exist in 8.0.5.

If you bear this rule in mind - that the database into which you are importing dictates the version of IMP that should be used and the version of EXP to use is the lower of the two versions, then you'll be able to EXP/IMP across versions easily.

One last note: if you still have some Oracle 7 databases kicking around, you will need to run a script in your Oracle 8 databases to allow the version 7 EXP tool to work. This script is cat7exp.sql and is found in [ORACLE_HOME]/rdbms/admin. This script should be run by the user SYS when connected via the SVRMGRL command line tool. This will set up the version 7 compatible export scripts in the version 8 database. It will not replace the version 8 export views - they will remain intact. This script will simply add the additional version 7 views to your database allowing the version 7 EXP tool to function.

Where did my Indexes go?

You might expect that if you exported a schema, dropped all of the objects in that database schema, and then re-imported your exported schema, then you would end up with the same set of objects. Well, you might be surprised. Consider this simple schema:

tkyte@TKYTE816> create table t   2  ( x int,   3    y int,   4    constraint t_pk primary key(x)   5  )   6  / Table created.      tkyte@TKYTE816> create index t_idx on t(x,y)   2  / Index created.      tkyte@TKYTE816> create table t2   2  ( x int primary key,   3    y int   4  )   5  / Table created.      tkyte@TKYTE816> create index t2_idx on t2(x,y)   2  / Index created.

Two very similar tables, the only difference being that one used a named primary key and the other let the system generate one. Let's take a look at the objects created in the database:

tkyte@TKYTE816> select object_type, object_name,   2                  decode(status,'INVALID','*','') status,   3                  tablespace_name   4  from user_objects a, user_segments b   5  where a.object_name = b.segment_name (+)   6  order by object_type, object_name   7  /      OBJECT_TYPE  OBJECT_NAME                    S TABLESPACE_NAME ------------ ------------------------------ - ------------------- INDEX        SYS_C002559                      DATA              T2_IDX                           DATA              T_IDX                            DATA              T_PK                             DATA      TABLE        T                                DATA              T2                               DATA           6 rows selected.

We see that each of our primary keys had an index generated for them - these are SYS_C002559 and T_PK. We also see the two extra indexes we created as well. After I dropped tables T and T2, I ran a full IMP. To my surprise, I discovered the following:

tkyte@TKYTE816> select object_type, object_name,   2                  decode(status,'INVALID','*','') status,   3                  tablespace_name   4  from user_objects a, user_segments b   5  where a.object_name = b.segment_name (+)   6  order by object_type, object_name   7  /      OBJECT_TYPE  OBJECT_NAME                    S TABLESPACE_NAME ------------ ------------------------------ - --------------------- INDEX        T2_IDX                           DATA              T_IDX                            DATA              T_PK                             DATA      TABLE        T                                DATA              T2                               DATA

One of my indexes is 'missing'. What happened here is that Oracle used the index T2_IDX on (X,Y) to enforce the primary key. This is perfectly valid. We can reproduce this behavior ourselves just by running the CREATE commands in a slightly different order (run in a 'clean' schema with no other objects created):

tkyte@TKYTE816> create table t ( x int, y int ); Table created.      tkyte@TKYTE816> create index t_idx on t(x,y); Index created.      tkyte@TKYTE816> alter table t add constraint t_pk primary key(x); Table altered.      tkyte@TKYTE816> select object_type, object_name,   2                  decode(status,'INVALID','*','') status,   3                  tablespace_name   4  from user_objects a, user_segments b   5  where a.object_name = b.segment_name (+)   6  order by object_type, object_name   7  /      OBJECT_TYPE  OBJECT_NAME                    S TABLESPACE_NAME ------------ ------------------------------ - --------------------- INDEX        T_IDX                            DATA      TABLE        T                                DATA

Here, Oracle will use the index T_IDX to enforce the primary key. We can see this clearly if we try to drop it:

tkyte@TKYTE816> drop index t_idx; drop index t_idx            * ERROR at line 1: ORA-02429: cannot drop index used for enforcement of unique/primary key

Well, a similar event is occurring with EXP/IMP. It does not export the index definition for indexes that have system-generated names. There would be an error if it did. EXP/IMP relies on the fact that the index is created implicitly upon object creation (if at all). If it did actually export our index SYS_C002559, and attempted to create it upon import, one of two errors might occur. First, the generated name SYS_C002559 may very well conflict with an already existing generated name in the database (for a check constraint, say). Second, the object creation itself may have already created the index - making this index redundant (and in error). So here, EXP and IMP are doing the right thing - you are just seeing a side effect of the fact that a constraint does not have to create an index.

By naming the primary key, we created an index that has a constant name; every time we create that object, the name of the index is immutable. EXP exports the definition of this index and IMP will import it.

The moral to this story is that default names for objects should be avoided, not only for the reason above, but also for the reason below. This, of course is apart from the fact that the name SYS_C002559 does not mean anything to anyone, whereas the name T_PK might mean 'primary key for table T' to someone.

Named versus Default-Named Constraints

Another issue with regards to system-generated named constraints is the fact that the import may cause a redundant constraint to be added to a table (I could have called this section Where Did All of These Constraints Come From?). Let's take a look at an example. We start with a table T:

tkyte@TKYTE816> create table t   2  ( x int check ( x > 5 ),   3    y int constraint my_rule check ( y > 10 ),   4    z int not null ,   5    a int unique,   6    b int references t,   7    c int primary key   8  ); Table created.      tkyte@TKYTE816> select constraint_name name, constraint_type type, search_condition   2    from user_constraints where table_name = 'T';      NAME                           T SEARCH_CONDITION ------------------------------ - ------------------------- SYS_C002674                    C "Z" IS NOT NULL SYS_C002675                    C x > 5 MY_RULE                        C y > 10 SYS_C002677                    P SYS_C002678                    U SYS_C002679                    R      6 rows selected.

It has lots of constraints on it - six all together. I'll export it, drop the table and import it again:

tkyte@TKYTE816> host exp userid=tkyte/tkyte owner=tkyte      tkyte@tkyte816> drop table T; Table dropped.      tkyte@TKYTE816> host imp userid=tkyte/tkyte full=y ignore=y rows=n      tkyte@TKYTE816> select constraint_name name, constraint_type type, search_condition   2    from user_constraints where table_name = 'T';      NAME                           T SEARCH_CONDITION ------------------------------ - ------------------------- SYS_C002680                    C "Z" IS NOT NULL SYS_C002681                    C x > 5 MY_RULE                        C y > 10 SYS_C002683                    P SYS_C002684                    U SYS_C002685                    R      6 rows selected.

Looks normal so far. Let's say, however, that we rerun the import for whatever reason (it failed part way through for example). What we'll then discover is:

tkyte@TKYTE816> host imp userid=tkyte/tkyte full=y ignore=y      Import: Release 8.1.6.0.0 - Production on Tue Mar 20 15:42:26 2001      (c) Copyright 1999 Oracle Corporation.  All rights reserved.           Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production With the Partitioning option JServer Release 8.1.6.0.0 - Production      Export file created by EXPORT:V08.01.06 via conventional path import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set . importing TKYTE's objects into TKYTE . . importing table                            "T"          0 rows imported IMP-00017: following statement failed with ORACLE error 2264:  "ALTER TABLE "T" ADD  CONSTRAINT "MY_RULE" CHECK ( y > 10 ) ENABLE NOVALIDAT"  "E" IMP-00003: ORACLE error 2264 encountered ORA-02264: name already used by an existing constraint IMP-00017: following statement failed with ORACLE error 2261:  "ALTER TABLE "T" ADD  UNIQUE ("A") USING INDEX PCTFREE 10 INITRANS 2 MAXTRAN"  "S 255 STORAGE(INITIAL 524288) TABLESPACE "DATA" ENABLE" IMP-00003: ORACLE error 2261 encountered ORA-02261: such unique or primary key already exists in the table About to enable constraints... Import terminated successfully with warnings.      tkyte@TKYTE816> select constraint_name name, constraint_type type, search_condition   2    from user_constraints where table_name = 'T';      NAME                           T SEARCH_CONDITION ------------------------------ - ------------------------- SYS_C002680                    C "Z" IS NOT NULL SYS_C002681                    C x > 5 MY_RULE                        C y > 10 SYS_C002683                    P SYS_C002684                    U SYS_C002685                    R SYS_C002686                    C x > 5      7 rows selected.

We have an extra constraint. In fact, every time we run this, we'll have an extra constraint added. My named constraint however, generates a warning on screen - you cannot have the same named constraint twice. The unnamed constraint for x > 5 on the other hand - gets created again. This is because the database just generated a new name for it.

I've seen cases where people have been using EXP on one database, truncating the data on another, and using IMP to put the data back in. Over time, they had accumulated hundreds of check constraints on many columns. Performance was starting to go downhill and they wanted to know why. This is the reason why: every time they copied the data, they added yet another bunch of check constraints, all doing the same work. Lets see the effect that just one hundred redundant check constraints can have:

tkyte@TKYTE816> create table t   2  ( x int check ( x > 5 )   3  )   4  / Table created.      tkyte@TKYTE816> declare   2      l_start number default dbms_utility.get_time;   3  begin   4      for i in 1 .. 1000   5      loop   6          insert into t values ( 10 );   7      end loop;   8      dbms_output.put_line   9      ( round((dbms_utility.get_time-l_start)/100,2) || ' seconds' );  10  end;  11  / .08 seconds      PL/SQL procedure successfully completed.      tkyte@TKYTE816> begin   2      for i in 1 .. 100   3      loop   4          execute immediate   5          'ALTER TABLE "TKYTE"."T" ADD CHECK ( x > 5 ) ENABLE ';   6      end loop;   7  end;   8  / PL/SQL procedure successfully completed.      tkyte@TKYTE816> declare   2      l_start number default dbms_utility.get_time;   3  begin   4      for i in 1 .. 1000   5      loop   6          insert into t values ( 10 );   7      end loop;   8      dbms_output.put_line   9      ( round((dbms_utility.get_time-l_start)/100,2) || ' seconds' );  10  end;  11  / .17 seconds      PL/SQL procedure successfully completed.

Yet another good reason to name your constraints!

National Language Support (NLS) Issues

NLS stands for National Language Support. It allows us to store, process, and retrieve data in native languages. It ensures that database utilities and error messages, sort order, date, time, monetary, numeric, and calendar conventions automatically adapt to the native language and locale - for example so that numbers are displayed with commas and periods in the correct place. In some countries a number should be displayed as 999.999.999,99, in others 999,999,999.00. There are issues you should consider when using EXP/IMP to move data around in an environment with different character sets. Of particular importance are the character sets of:

The EXP client versus the database being exported from
The IMP client versus the EXP clients
The IMP client versus the database being imported into

If any of them differ, you could end up unintentionally damaging your data. Consider this very trivial example (trivial, but common):

ops$tkyte@DEV816> create table t ( c varchar2(1) ); Table created.      ops$tkyte@DEV816> insert into t values ( chr(235) ); 1 row created.      ops$tkyte@DEV816> select dump(c) from t;      DUMP(C) ------------------------------------------------------------ Typ=1 Len=1: 235      ops$tkyte@DEV816> commit; Commit complete.

So far, so good. Now lets export:

ops$tkyte@DEV816> host exp userid=tkyte/tkyte tables=t      Export: Release 8.1.6.2.0 - Production on Tue Mar 20 16:04:55 2001      (c) Copyright 1999 Oracle Corporation.  All rights reserved.           Connected to: Oracle8i Enterprise Edition Release 8.1.6.2.0 - Production With the Partitioning option JServer Release 8.1.6.2.0 - Production Export done in US7ASCII character set and US7ASCII NCHAR character set server uses WE8ISO8859P1 character set (possible charset conversion)      About to export specified tables via Conventional Path ... . . exporting table                              T          1 rows exported Export terminated successfully without warnings.

This message (possible charset conversion) needs to be noticed! We have just taken 8-bit data and exported it into a 7-bit character set. Let's now import this data back in:

ops$tkyte@DEV816> host imp userid=tkyte/tkyte full=y ignore=y      Import: Release 8.1.6.2.0 - Production on Tue Mar 20 16:05:07 2001      (c) Copyright 1999 Oracle Corporation.  All rights reserved.           Connected to: Oracle8i Enterprise Edition Release 8.1.6.2.0 - Production With the Partitioning option JServer Release 8.1.6.2.0 - Production      Export file created by EXPORT:V08.01.06 via conventional path import done in US7ASCII character set and US7ASCII NCHAR character set import server uses WE8ISO8859P1 character set (possible charset conversion) . importing OPS$TKYTE's objects into OPS$TKYTE . . importing table                            "T"          1 rows imported Import terminated successfully without warnings.      ops$tkyte@DEV816> select dump(c) from t;      DUMP(C) --------------------------- Typ=1 Len=1: 235 Typ=1 Len=1: 101

The SQL DUMP command shows us the data we took out and put back in is different. It is clearer if we look at the numbers in binary:

235 decimal = 11101011 binary 101 decimal = 01100101 binary

Our data has been mapped from one character set to another - it has been changed. It would be terrible to discover this after you dropped the table.

If you see this warning message, stop and think about the consequences. In my case, the solution is easy. On UNIX or NT, I just set the NLS_LANG environment variable to match the database:

$ echo $NLS_LANG AMERICAN_AMERICA.WE8ISO8859P1

Now, neither EXP and IMP will perform any character set conversions. They will run much faster as well. On Windows NT/2000, the NLS_LANG may also be set in the registry.

Tables Spanning Multiple Tablespaces

In the beginning, CREATE TABLE statements were relatively simple. Over the years, they have gotten progressively more and more complex. The 'train tracks' or 'wire diagram' for the simple CREATE TABLE now spans eight pages. One of the newer features of tables is the ability for bits and pieces of them to exist in various tablespaces. For example, a table with a CLOB column will have a table segment, CLOB index segment, and CLOB data segment. We can specify the location of the table and the locations of the CLOB data. An index-organized table (IOT) can have the index segment, and an overflow segment. Partitioned tables of course may have many partitions, each in a separately specified tablespace.

With this complexity comes some confusion for EXP/IMP. It used to be that if you tried to import an object and it failed due to the tablespace either not existing, or because you exceeded your quota on that tablespace, IMP would rewrite the SQL for you to create the object in your DEFAULT tablespace. IMP will not do this with multi-tablespace objects, as it will with single tablespace object, even if all of the tablespaces specified in the CREATE command are the same. An example will demonstrate the problem, and then I'll describe how we can work around the situation.

First, we'll start with a schema that has a couple of multi-tablespace objects and a simple single tablespace table in a tablespace:

tkyte@TKYTE816> create tablespace exp_test   2  datafile 'c:\oracle\oradata\tkyte816\exp_test.dbf'   3  size 1m   4  extent management local   5  uniform size 64k   6  / Tablespace created.      tkyte@TKYTE816> alter user tkyte default tablespace exp_test   2  / User altered.      tkyte@TKYTE816> create table t1   2  ( x int primary key, y varchar2(25) )   3  organization index   4  overflow tablespace exp_test   5  / Table created.      tkyte@TKYTE816> create table t2   2  ( x int, y clob )   3  / Table created.      tkyte@TKYTE816> create table t3   2  ( x int,   3    a int default to_char(sysdate,'d')   4  )   5  PARTITION BY RANGE (a)   6  (   7  PARTITION part_1 VALUES LESS THAN(2),   8  PARTITION part_2 VALUES LESS THAN(3),   9  PARTITION part_3 VALUES LESS THAN(4),  10  PARTITION part_4 VALUES LESS THAN(5),  11  PARTITION part_5 VALUES LESS THAN(6),  12  PARTITION part_6 VALUES LESS THAN(7),  13  PARTITION part_7 VALUES LESS THAN(8)  14  )  15  / Table created.      tkyte@TKYTE816> create table t4 ( x int )   2  / Table created.

So, we started by creating a tablespace and making this our default tablespace. We then created an IOT with two segments - the index and overflow. We created a table with a CLOB that has three segments. Then we have a partitioned table with seven segments. Lastly, we have the normal, simple 'table'. We export this schema:

tkyte@TKYTE816> host exp userid=tkyte/tkyte owner=tkyte

and proceed to drop that tablespace:

tkyte@TKYTE816> drop tablespace exp_test including contents; Tablespace dropped.      tkyte@TKYTE816> alter user tkyte default tablespace data; User altered.

When we import the schema, we discover most of the tables won't come back in:

tkyte@TKYTE816> host imp userid=tkyte/tkyte full=y      Import: Release 8.1.6.0.0 - Production on Tue Mar 20 19:03:18 2001      (c) Copyright 1999 Oracle Corporation.  All rights reserved.           Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production With the Partitioning option JServer Release 8.1.6.0.0 - Production      Export file created by EXPORT:V08.01.06 via conventional path import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set . importing TKYTE's objects into TKYTE IMP-00017: following statement failed with ORACLE error 959:  "CREATE TABLE "T2" ("X" NUMBER(*,0), "Y" CLOB)  PCTFREE 10 PCTUSED 40 INITRA"  "NS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 65536) TABLESPACE "EXP_TEST" LOB "  "("Y") STORE AS  (TABLESPACE "EXP_TEST" ENABLE STORAGE IN ROW CHUNK 8192 PCT"  "VERSION 10 NOCACHE  STORAGE(INITIAL 65536))" IMP-00003: ORACLE error 959 encountered ORA-00959: tablespace 'EXP_TEST' does not exist IMP-00017: following statement failed with ORACLE error 959:  "CREATE TABLE "T3" ("X" NUMBER(*,0), "A" NUMBER(*,0))  PCTFREE 10 PCTUSED 40"  " INITRANS 1 MAXTRANS 255 LOGGING TABLESPACE "EXP_TEST" PARTITION BY RANGE ("  ""A" )  (PARTITION "PART_1" VALUES LESS THAN (2)  PCTFREE 10 PCTUSED 40 INIT"  "RANS 1 MAXTRANS 255 STORAGE(INITIAL 65536) TABLESPACE "EXP_TEST" LOGGING, P"  "ARTITION "PART_2" VALUES LESS THAN (3)  PCTFREE 10 PCTUSED 40 INITRANS 1 MA"  "XTRANS 255 STORAGE(INITIAL 65536) TABLESPACE "EXP_TEST" LOGGING, PARTITION "  ""PART_3" VALUES LESS THAN (4)  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 25"  "5 STORAGE(INITIAL 65536) TABLESPACE "EXP_TEST" LOGGING, PARTITION "PART_4" "  "VALUES LESS THAN (5)  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE"  "(INITIAL 65536) TABLESPACE "EXP_TEST" LOGGING, PARTITION "PART_5" VALUES LE"  "SS THAN (6)  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL "  "65536) TABLESPACE "EXP_TEST" LOGGING, PARTITION "PART_6" VALUES LESS THAN ("  "7)  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536) TA"  "BLESPACE "EXP_TEST" LOGGING, PARTITION "PART_7" VALUES LESS THAN (8)  PCTFR"  "EE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536) TABLESPACE "  ""EXP_TEST" LOGGING )" IMP-00003: ORACLE error 959 encountered ORA-00959: tablespace 'EXP_TEST' does not exist . . importing table                           "T4"          0 rows imported IMP-00017: following statement failed with ORACLE error 959:  "CREATE TABLE "T1" ("X" NUMBER(*,0), "Y" VARCHAR2(25),  PRIMARY KEY ("X") EN"  "ABLE) ORGANIZATION INDEX  NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOG"  "GING STORAGE(INITIAL 65536) TABLESPACE "EXP_TEST" PCTTHRESHOLD 50 OVERFLOW "  "PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING  STORAGE(INITIAL 6553"  "6) TABLESPACE "EXP_TEST"" IMP-00003: ORACLE error 959 encountered ORA-00959: tablespace 'EXP_TEST' does not exist Import terminated successfully with warnings.

Specifically, the only table that came back in without an error was the simple 'normal' table. For this table, IMP rewrote the SQL. It blanked out the first TABLESPACE EXP_TEST that it came across and retried the CREATE. This rewritten CREATE succeeded. The other CREATE commands, when similarly rewritten, did not succeed. The only solution to this is to create the tables beforehand, and then import with IGNORE=Y. If you do not have the DDL for the CREATE TABLE commands, you can retrieve it, of course, from the DMP file with INDEXFILE=Y. That will allow you to modify the DDL, supplying the correct tablespace information yourself. In this case, since I had the DDL to hand, I just created the three tables with new tablespaces specified, where necessary:

tkyte@TKYTE816> create table t1   2  ( x int primary key, y varchar2(25) )   3  organization index   4  overflow tablespace data   5  / Table created.      tkyte@TKYTE816> create table t2   2  ( x int, y clob )   3  / Table created.      tkyte@TKYTE816> create table t3   2  ( x int,   3    a int default to_char(sysdate,'d')   4  )   5  PARTITION BY RANGE (a)   6  (   7  PARTITION part_1 VALUES LESS THAN(2),   8  PARTITION part_2 VALUES LESS THAN(3),   9  PARTITION part_3 VALUES LESS THAN(4),  10  PARTITION part_4 VALUES LESS THAN(5),  11  PARTITION part_5 VALUES LESS THAN(6),  12  PARTITION part_6 VALUES LESS THAN(7),  13  PARTITION part_7 VALUES LESS THAN(8)  14  )  15  / Table created.

and was able to import cleanly:

tkyte@TKYTE816> host imp userid=tkyte/tkyte full=y ignore=y      Import: Release 8.1.6.0.0 - Production on Tue Mar 20 19:03:20 2001      (c) Copyright 1999 Oracle Corporation.  All rights reserved.           Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production With the Partitioning option JServer Release 8.1.6.0.0 - Production      Export file created by EXPORT:V08.01.06 via conventional path import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set . importing TKYTE's objects into TKYTE . . importing table                           "T2"          0 rows imported . . importing partition                  "T3":"PART_1"          0 rows imported . . importing partition                  "T3":"PART_2"          0 rows imported . . importing partition                  "T3":"PART_3"          0 rows imported . . importing partition                  "T3":"PART_4"          0 rows imported . . importing partition                  "T3":"PART_5"          0 rows imported . . importing partition                  "T3":"PART_6"          0 rows imported . . importing partition                  "T3":"PART_7"          0 rows imported . . importing table                           "T4"          0 rows imported . . importing table                           "T1"          0 rows imported Import terminated successfully without warnings.

Now, I have seen an issue with some objects whereby the above workaround does not work. IMP still raises the ORA-00959 tablespace 'name' does not exist. The only way temporary work around I have found for this is to pre-create the object (as above) and then create very small tablespaces with the names that IMP demands. You would create these tablespaces with a file too small to actually create anything. Now the IMP will work and you can drop the tablespaces afterwards.

Summary

In this chapter, we have covered many uses of the tools Import and Export. I have presented the common solutions to the problems and questions I hear most frequently regarding these tools. EXP and IMP are extremely powerful once you get a trick or two under your belt. Given the complexity of objects in the database today, I am sometimes surprised that it works as seamlessly as it does.

The roles of IMP and EXP are changing over time. In the Oracle version 5 and 6 days, it was considered a viable backup tool. Databases were small (a 100MB database might have been considered large) and 24x7 was just beginning to be a requirement. Over time, the usefulness of IMP/EXP in the area of backup and recovery has severely diminished, to the point where I would say outright that it is not a backup and recovery tool at all. Today EXP and IMP are relatively simple tools that should be used to move a modest amount of data back and forth between instances, or, by using transportable tablespaces, to move massive amounts of data. To use it to backup a 500GB database would be ludicrous. To use it to transport 100GB of that database would be perfectly valid.

It still has many of its conventional uses, such as the ability to 'clone' a schema (given that you understand the potential 'gotchas') or to extract the DDL for a schema to a file. Mixed with other database features like INSTEAD OF triggers on views, you can even teach it some new tricks. Interesting uses, such as the ability for two databases to share a set of read-only files, are still waiting to be discovered with this tool, I'm sure.

Table of content

Full Frontal PR: Getting People Talking about You, Your Business, or Your Product

ISBN: 1590595254
EAN: 2147483647

Year: 2005
Pages: 41

Authors: Richard Laermer, Michael Prichinello

BUY ON AMAZON

Mapping Hacks: Tips & Tools for Electronic Cartography

Logistics and Retail Management: Emerging Issues and New Challenges in the Retail Supply Chain

Junos Cookbook (Cookbooks (OReilly))

802.11 Wireless Networks: The Definitive Guide, Second Edition

Java All-In-One Desk Reference For Dummies

GDI+ Programming with C#