| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Re: Import with new table & index extent sizes
In article <39AEBD1C.AB5CCBF7_at_dteenergy.com>,
Gregory P Lechkun <lechkung_at_dteenergy.com> wrote:
>
> --------------616E89D0651821B3847CDEDD
> Content-Type: text/plain; charset=us-ascii
> Content-Transfer-Encoding: 7bit
>
> Hello,
>
> I have a full export and I want to import it into a new instance so
that
> the table and index extent sizes default to what my new instance's
> tablespace extents default sizes are. I'm doing this so that all my
> objects in the tablespaces are set to a uniform size (4MB).
>
> Question: How do I make the import command ignore the table and index
> sizes that are set in my old instance and use the tablespace default
> setting?
>
> This is what par file looks like:
>
> BUFFER=5242880
> COMPRESS=N
> CONSISTENT=Y
> CONSTRAINTS=Y
> FILE=CIL_EXPORTS:UP02_FULL_EXPORT
> FULL=Y
> GRANTS=Y
> INDEXES=Y
> LOG=CIL_EXPORTS:UP02_EXPORT.LOG
> ROWS=Y
>
> --
> Thanks in advance,
> Greg Lechkun
> gpl :-)
>
> DTE
>
Energy====================================================================
size=-1>FILE=CIL_EXPORTS:UP02_FULL_EXPORT</font></font>> <br><font face="Arial,Helvetica"><font size=-1>INDEXES=Y</font></font>
> <br><font face="Arial,Helvetica"><font size=-1>FULL=Y</font></font>
> <br><font face="Arial,Helvetica"><font size=-1>GRANTS=Y</font></font>
Energy====================================================================</font></font>
//
You cannot ignore the extent sizing in the import file. You can, however, dump the table and index definitions from the import file using the indexfile parameter:
imp <user/pass> file=... full=y indexfile=myschema.sql
This will send the create table and create index statements to a file named myschema.sql (you can name the file anything you wish). Edit this file -- the CREATE TABLE statements are REMarked so that they will not execute from the script as generated. However, all necessary parameters, including tablespace and extent sizing, for both the tables and the indexes are in this file. Remove the connect statement, unremark the table creates, change the extent sizes for the tables and indexes then run this script prior to importing the data. This will create the tables and indexes with the uniform sizing you desire (presuming, of course, that you set the sizing uniform in the script). Import the data after the script has finished using the ignore=y parameter to imp:
imp <user/pass> file=.... full=y ignore=y
This will ignore the errors generated because the tables and indexes already exist and will proceed to load the data into the tables. All extents created will then be uniform.
-- David Fitzjarrell Oracle Certified DBA Sent via Deja.com http://www.deja.com/ Before you buy.Received on Thu Aug 31 2000 - 16:02:00 CDT
 |
 |