Import (IMP) and Export (EXP) = COMPRESS (Y)

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.

COMPRESS (Y) -  This Parameter in EXP tool 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.

idle> connect scott/oracle
Connected.
scott@10G> drop table t purge;



Table dropped.

Elapsed: 00:00:02.51
scott@10G> create table t as select * from all_objects;

Table created.



Elapsed: 00:00:10.13
scott@10G> select sum(blocks) as blocks, sum(bytes) as bytes
  2  from user_extents
  3  where segment_name ='T';

    BLOCKS      BYTES
---------- ----------
       768    6291456

Elapsed: 00:00:00.11
scott@10G> SELECT dbms_metadata.get_ddl('TABLE','T') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------
  CREATE TABLE "SCOTT"."T"
   (    "OWNER" VARCHAR2(30) NOT NULL ENABLE,
        "OBJECT_NAME" VARCHAR2(30) NOT NULL ENABLE,
        "SUBOBJECT_NAME" VARCHAR2(30),
        "OBJECT_ID" NUMBER NOT NULL ENABLE,
        "DATA_OBJECT_ID" NUMBER,
        "OBJECT_TYPE" VARCHAR2(19),
        "CREATED" DATE NOT NULL ENABLE,
        "LAST_DDL_TIME" DATE NOT NULL ENABLE,
        "TIMESTAMP" VARCHAR2(19),
        "STATUS" VARCHAR2(7),
        "TEMPORARY" VARCHAR2(1),
        "GENERATED" VARCHAR2(1),
        "SECONDARY" VARCHAR2(1)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"



D:\>exp userid=scott/oracle file=d:\t.dmp log=d:\t_log.txt tables=T COMPRESS=Y

Export: Release 10.2.0.3.0 - Production on Tue Feb 16 23:51:52 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                              T      51353 rows exported
Export terminated successfully without warnings.


D:\>imp userid=scott/tiger@10GR2 file=d:\t.dmp log=d:\imp_log.txt fromuser=scott touser=scott

Import: Release 10.2.0.3.0 - Production on Tue Feb 16 23:53:07 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. . importing table                            "T"      51353 rows imported
Import terminated successfully without warnings.




scott@10GR2> select sum(blocks) as blocks, sum(bytes) as bytes
  2  from user_extents
  3  where segment_name ='T';

    BLOCKS      BYTES
---------- ----------
       768    6291456

Elapsed: 00:00:00.79


scott@10GR2> SELECT dbms_metadata.get_ddl('TABLE','T') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------

  CREATE TABLE "SCOTT"."T"
   (    "OWNER" VARCHAR2(30) NOT NULL ENABLE,
        "OBJECT_NAME" VARCHAR2(30) NOT NULL ENABLE,
        "SUBOBJECT_NAME" VARCHAR2(30),
        "OBJECT_ID" NUMBER NOT NULL ENABLE,
        "DATA_OBJECT_ID" NUMBER,
        "OBJECT_TYPE" VARCHAR2(19),
        "CREATED" DATE NOT NULL ENABLE,
        "LAST_DDL_TIME" DATE NOT NULL ENABLE,
        "TIMESTAMP" VARCHAR2(19),
        "STATUS" VARCHAR2(7),
        "TEMPORARY" VARCHAR2(1),
        "GENERATED" VARCHAR2(1),
        "SECONDARY" VARCHAR2(1)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 6291456 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"

Elapsed: 00:00:01.23



D:\>exp userid=scott/oracle file=d:\t.dmp log=d:\t_log.txt tables=T COMPRESS=N

Export: Release 10.2.0.3.0 - Production on Wed Feb 17 00:03:19 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                              T      51353 rows exported
Export terminated successfully without warnings.



D:\>imp userid=scott/tiger@10GR2 file=d:\t.dmp fromuser=scott touser=scott log=d:\imp_log.txt

Import: Release 10.2.0.3.0 - Production on Wed Feb 17 00:06:16 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. . importing table                            "T"      51353 rows imported
Import terminated successfully without warnings.


scott@10GR2> select sum(blocks) as blocks, sum(bytes) as bytes
  2  from user_extents
  3  where segment_name ='T';

    BLOCKS      BYTES
---------- ----------
       768    6291456

Elapsed: 00:00:00.71

scott@10GR2> SELECT dbms_metadata.get_ddl('TABLE','T') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------

  CREATE TABLE "SCOTT"."T"
   (    "OWNER" VARCHAR2(30) NOT NULL ENABLE,
        "OBJECT_NAME" VARCHAR2(30) NOT NULL ENABLE,
        "SUBOBJECT_NAME" VARCHAR2(30),
        "OBJECT_ID" NUMBER NOT NULL ENABLE,
        "DATA_OBJECT_ID" NUMBER,
        "OBJECT_TYPE" VARCHAR2(19),
        "CREATED" DATE NOT NULL ENABLE,
        "LAST_DDL_TIME" DATE NOT NULL ENABLE,
        "TIMESTAMP" VARCHAR2(19),
        "STATUS" VARCHAR2(7),
        "TEMPORARY" VARCHAR2(1),
        "GENERATED" VARCHAR2(1),
        "SECONDARY" VARCHAR2(1)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"




ROT : It's safe to use COMPRESS = N and the use of locally managed tablespaces.