Re: EXP / IMP ISSUE
Date: Wed, 15 Jun 2011 21:10:48 -0300
Message-ID: <BANLkTik12DWKw376R7QjBSZqTYsrP2oEvg_at_mail.gmail.com>
Hi friends,
Many thanks for suggestions.
Look at the one more solution that I got with a friend mine:
1.) Start the imp as it were exported
with big initial values
2.) While the imp command is running and writting tables, we can run the following script, that will DEALOCATTE unsed spaces.
DECLARE
CURSOR C_TABLE IS
select TABLE_NAME
from user_tables
where INITIAL_EXTENT > 262144;
CURSOR C_index IS
select index_name
from user_indexes
where INITIAL_EXTENT > 262144;
v_command VARCHAR2(400);
BEGIN
FOR T IN C_TABLE LOOP
v_command := 'ALTER TABLE ' || t.table_name || ' DEALLOCATE UNUSED KEEP 64K';
execute immediate v_command;
dbms_output.put_line ('tab = ' || t.table_name);
END LOOP;
FOR T IN C_INDEX
LOOP
v_command := 'ALTER INDEX ' || t.index_name || ' DEALLOCATE UNUSED KEEP
64K';
execute immediate v_command;
dbms_output.put_line ('ind = ' || t.index_name);
END LOOP;
END;
/
or
3.) Run the statment sql below and run the result in a session apart
select 'alter table ' || table_name || ' DEALLOCATE UNUSED KEEP 64K;' from user_tables where INITIAL_EXTENT > 262144;
select 'alter index ' || index_name || ' DEALLOCATE UNUSED KEEP 64K;' from user_indexes where INITIAL_EXTENT > 262144;
Well,
It is not the best solution, but, can be used too.
Best Regards
Eriovaldo
On Wed, Jun 15, 2011 at 6:30 AM, Sreejith S Nair < Sreejith.Sreekantan_at_ibsplc.com> wrote:
> if you are using datapump you can use TRANSFORM:SEGMENT_ATTRIBUTES:n
>
>
> *TRANSFORM*
> Default: none
> *Purpose*
> Enables you to alter object creation DDL for specific objects, as well as
> for all
> applicable objects being loaded.
> *Syntax and Description*
> TRANSFORM = *transform_name*:*value*[:*object_type*]
>
> The *transform_name *specifies the name of the transform. The possible
> options are
> as follows:
> ■ SEGMENT_ATTRIBUTES - If the value is specified as y, then segment
> attributes
> (physical attributes, storage attributes, tablespaces, and logging) are
> included,
> with appropriate DDL. The default is y.
>
>
>
>
>
> From: Remigiusz Sokolowski <remigiusz.sokolowski_at_nordea.com>
> To: <oracle-l_at_freelists.org>
> Date: 06/15/2011 12:37 PM
> Subject: Re: EXP / IMP ISSUE
> Sent by: oracle-l-bounce_at_freelists.org
> ------------------------------
>
>
>
> W dniu 14.06.2011 21:55, Eriovaldo Andrietta pisze:
> > Hello,
> >
> > I would like to get a way to export data withoout storage.
> >
> > STORAGE(INITIAL 400293888 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
> > DEFAULT) TABLESPACE "xxx" LOGGING NOCOMPRESS.
> >
> > Because the initial extent is too big and I am getting error.
> >
> > The solution can be in exp for do not consider storage when export
> > or
> > when using the Imp in order to do not consider the storage data
> exported.
> >
> > Note:
> > I used list the DDL via indexfile, but now I need to change all
> > INITIAL that has big value. This is too exaustive, because there are lot
> > of tables.
> >
> >
> > Best Regards
> > Eriovaldo
> >
>
> Assuming imp/exp only (and not data pump) then, not remember well, but
> the key is first to create empty table without any constraints or
> indexes or rows. I think I used for it exp/imp as well and with rows=N
> the definition was different, but not sure at the moment, it was few
> years ago - if that will not work You may build this table manually by
> pulling out a definition with dbms_metadata.
> Then set parameter IGNORE=Y and imp loads to already existing table so
> no problem with previous storage settings.
>
> Data pump is much more convenient with such manipulations, but suppose
> You have no such option
>
> Regards
> Remigiusz
> --
> Pole nakazi
>
> ----------------------------------------------------------------------
> Remigiusz Sokolowski <remigiusz.sokolowski_at_nordea.com>
> pos : DBA at DIiUSI
> addr : Nordea Bank Polska SA, Luzycka 6A Street, 81-537 Gdynia, Poland
> phone : +48 58 667 17 43
> mobile: +48 602 42 42 77
> Nordea Bank Polska S.A. z siedziba w Gdyni, ul. Kielecka 2, 81-303 Gdynia,
> wpisana do Rejestru Przedsiebiorców Krajowego Rejestru Sadowego pod
> numerem: 0000021828,
> dla której dokumentacje przechowuje Sad Rejonowy Gdansk - Pólnoc w Gdansku,
>
> VIII Wydzial Gospodarczy Krajowego Rejestru Sadowego,
> o kapitale zakladowym i wplaconym w wysokosci: 277.493.500,00 zlotych,
> NIP: 586-000-78-20, REGON: 190024711--
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
>
>
>
>
>
> DISCLAIMER:
>
> "The information in this e-mail and any attachment is intended only for the
> person to whom it is addressed and may contain confidential and/or
> privileged material. If you have received this e-mail in error, kindly
> contact the sender and destroy all copies of the original communication. IBS
> makes no warranty, express or implied, nor guarantees the accuracy, adequacy
> or completeness of the information contained in this email or any attachment
> and is not liable for any errors, defects, omissions, viruses or for
> resultant loss or damage, if any, direct or indirect."
>
>
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Jun 15 2011 - 19:10:48 CDT