Correction to -> Re: Does export/import compress (deallocate) extents used by table ?

From: Kathy Jou <kjou_at_us.oracle.com>
Date: 6 Aug 92 18:23:03 GMT
Message-ID: <1992Aug6.182303.6887_at_oracle.us.oracle.com>

You are right, Chuck. I am incorrect in stating that compress=y during export will make the initial extent of the create table statement the size of the data. It actually just adds up all the separate extents and uses that as the size. If there's very little data left, then you might just do a create new-table as select * from fragmented-table.  

You need to pre-create the table with an initial extent that reflects the actual data that was exported if you want to compress all the unused space.

• Kathy

  ---

Received: 08-06-92 10:28                                 Sent: 08-06-92 09:32
From:     Chuck Coulson <HQPYR1:ccoulson_at_us.oracle.com>
To:       kjou_at_us.oracle.com
Subject:  Re: Does export/import compress (deallocate) extents used by table ?
Cc:       kortikar_at_mipos2.intel.com

Reply-To: HQPYR1:ccoulson_at_us.oracle.com Original-To: UNIX:kjou_at_us.oracle.com Original-Cc: UNIX:kortikar_at_mipos2.intel.com  

In-Reply-To: ccoulson.US1's message of 08-06-92 09:07  

Kathy-  

My understanding is that an export will only compress the total number of extents into one extent, but not compress the size of all of the extents. So, if you have table that grows to 10 extents of 10 meg, for a total of 100meg, and then you delete 95% of the records, do an export and an import, you will have table with 1 extent of 100meg.  

I believe the best way to do this is to drop an recreate the table with the appropriate storage parameters after the export but before the import.  

Chuck Coulson                                                            659509
Sr. Sales Consultant                                               415.506.5139
OEM/Strategic Accounts                                      ccoulson_at_oracle.com
===============================================================================

• Included Message ----

Received: 08-06-92 09:07                         Sent: 08-06-92 09:07

From: ccoulson.US1
To: ccoulson
Subject: Does export/import compress (deallocate) extents used by table ? Reply-To: UNIX:kjou_at_us.oracle.com  

Newsgroups: comp.databases.oracle
From: kjou_at_us.oracle.com (Kathy Jou)
Subject: Re: Does export/import compress (deallocate) extents used by table ? Message-ID: <1992Aug5.192201.29819_at_oracle.us.oracle.com> Sender: Kathy Jou
Nntp-Posting-Host: hqpyr1.us.oracle.com
Organization: Oracle Corporation, Redwood Shores CA 94065 References: <12694_at_inews.intel.com>
Date: Wed, 5 Aug 1992 19:22:01 GMT
X-Disclaimer: This message was written by an unauthenticated user

              at Oracle Corporation.  The opinions expressed are those
              of the user and not necessarily those of Oracle.
 

In article <12694_at_inews.intel.com> kortikar_at_mipos2.intel.com () writes:
>since oracle does not deallocate extents after a delete of say 95% rows from
>a 50 will export, drop table, import do the trick ?
 

Yes, but remember to specify compress=y during export so the initial extent of the table upon import will be the size of the exported data (ie, compressed into one extent).  

        % exp help=y  

The above will give you all the exp command line options and their default settings.  

• Kathy Jou
• Oracle Corporation

Received on Thu Aug 06 1992 - 20:23:03 CEST