Charles Hooper (hooperc2000_at_yahoo.com) wrote:
> Helmut Schneider wrote:
[...]
>> The error log says:
>>
>> IMP-00003: ORACLE error 1119 encountered
>> ORA-01119: error in creating database file 'D:\ORADATA\ORCL\MYDB.DBF'
>> ORA-27040: file create error, unable to create file
>> OSD-04002: unable to open file
>>
>> On the new server $ORADATA is D:\oracle\product\10.2.0\oradata\orcl
>> and not D:\oradata\orcl
>
> Precreate the tablespaces before performing the import. First, find
> out the tablespace names on the old server. Use SQLPlus, Excel,
> Access, or some other query tool to execute:
> SELECT
> T.NAME TABLESPACE_NAME,
> D.NAME FILENAME,
> D.BYTES
> FROM
> V$DATAFILE D,
> V$TABLESPACE T
> WHERE
> D.TS#=T.TS#
> ORDER BY
> T.NAME;
>
> And then the same query on the new server:
> SELECT
> T.NAME TABLESPACE_NAME,
> D.NAME FILENAME,
> D.BYTES
> FROM
> V$DATAFILE D,
> V$TABLESPACE T
> WHERE
> D.TS#=T.TS#
> ORDER BY
> T.NAME;
>
> Then, use SQLPlus to create the missing tablespaces, likely each item
> listed except SYSTEM. For example, if you have a tablespace named
> USER_DATA that needs to be created to contain 9GB of data (create
> larger than identified by the above to allow room for additional data),
> you could execute a statement like this:
> CREATE SMALLFILE TABLESPACE "USER_DATA" LOGGING DATAFILE
> 'D:\oracle\product\10.2.0\oradata\orcl\usrORCL01.dbf' SIZE 9000M
> AUTOEXTEND ON NEXT 40M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL
> SEGMENT SPACE MANAGEMENT AUTO;
>
> The above will create a locally managed (rather than dictionary
> managed) tablespace that starts out as a roughly 9GB file, grows in
> 40MB increments to the full size of the hard drive array, and uses ASSM
> to automatically determine the extent size.
>
> You will need to repeat the process for the temp tablespace(s):
> SELECT
> T.NAME TABLESPACE_NAME,
> D.NAME FILENAME,
> D.BYTES
> FROM
> V$TEMPFILE D,
> V$TABLESPACE T
> WHERE
> D.TS#=T.TS#;
>
> If you need a temporary tablespace named TEMPORARY_DATA that is 600MB
> in size, you could create the tablespace using the following command:
> CREATE SMALLFILE TEMPORARY TABLESPACE "TEMPORARY_DATA" TEMPFILE
> 'D:\oracle\product\10.2.0\oradata\orcl\tmpORCL' SIZE 600M AUTOEXTEND ON
> NEXT 40M MAXSIZE 8000M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
>
> The above will create a locally managed temporary tablespace that
> starts out at 600MB, grows in 40MB increments, with a maximum size of
> roughly 8GB, and using 1MB extent sizes.
>
> I suggest that you make changes to the export command:
> exp system/my_old_system_pass_at_orcl file=d:\export.dat log=d:\export.log
> FULL=y GRANTS=y INDEXES=y ROWS=y CONSTRAINTS=y STATISTICS=none DIRECT=y
> CONSISTENT=y COMPRESS=n
I'm deeply impressed and thank you very much. Worked like a charm.
Helmut
--
Please do not feed my mailbox, Swen still does his job well
Received on Thu Nov 16 2006 - 07:12:45 CST
