import oracle 8i fulldb data into oracle 9i

hi,
i have the fulldb export file of oracle 8i.
i have another oracle server which is 9i
when i try to import the fulldb dmp(oracle8i) into oracle 9i using 9i client, it gives following errors

IMP-00003: ORACLE error 12913 encountered
ORA-12913: Cannot create dictionary managed tablespace

i open the dmp file but there is no where define "dictionary managed" in create tablespace command.

what should be the solutions for this.
i dont want to seperately creat all schema and then import using fromuser to touser....

pl.
Thanks
Prathesh(Prathesh@u-tosolutions.com)
pratheshshahAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
schwertnerConnect With a Mentor Commented:
Oracle 9i has precreated Rollback Tablespace and you do not need to create your own rollback tablespaces.
Do not do FULL import. Import only non-system schemas.
If your import fails before new attempt recreate the schema:

DROP USER LS CASCADE;
CREATE USER LS IDENTIFIED BY ... DEFAULT TABLESPACE ....;
ALTER USER LS QUOTA UNLIMITED ON TABLESPACE  ...;
0
 
peledcCommented:
Hi,

In the new DB the SYSTEM tablespace was created as LOCALLY MANAGED. Therefor you will not be able to create any DICTIONARY Managed TS.

What I would do is create just the TABLESPACES in advance and then perform the full import.

Good luck.
0
 
pratheshshahAuthor Commented:
hi,peledc
thanks fo replying
but my issue is that i have 12 different schema each one has 2 tablespaces, to create it manually, is very tidies.
is there a way that  change in exported file will allow import fulldb in oracle 9.2.0.1.0 from exported file.
and one more thing is oracle 9i is not allow to create dictionary managed tablespace?

thanks


0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
schwertnerCommented:
It is not only the problem with the tablespace definition. Also the directory structure in most cases is different.
The best approach is to precreate the nonsystem tablespaces and users.
Example:

CREATE TABLESPACE "TSDMEDICAL"
LOGGING
DATAFILE  'E:\ORACLE_DB_MAIN\ORADATA\MAIN\TSDMEDICAL00.DBF'  SIZE 100M
AUTOEXTEND ON    NEXT 100M
MAXSIZE 32768M
PERMANENT   ONLINE
EXTENT  MANAGEMENT LOCAL
SEGMENTSPACE MANAGEMENT  MANUAL;

CREATE TABLESPACE "TSIMEDICAL"
LOGGING
DATAFILE  'E:\ORACLE_DB_MAIN\ORADATA\MAIN\TSIMEDICAL00.DBF'  SIZE 100M
AUTOEXTEND ON    NEXT 100M
MAXSIZE 32768M
PERMANENT   ONLINE
EXTENT  MANAGEMENT LOCAL
SEGMENTSPACE MANAGEMENT  MANUAL;

CREATE TABLESPACE "TSTEMPORARY"
NOLOGGING
DATAFILE  'E:\ORACLE_DB_MAIN\ORADATA\MAIN\TSTEMPORARY00.DBF'  SIZE 100M
AUTOEXTEND ON    NEXT
100M   MAXSIZE 32768M
TEMPORARY   ONLINE
EXTENT  MANAGEMENT LOCAL
SEGMENTSPACE MANAGEMENT  MANUAL;

CREATE USER LSMEDICAL  IDENTIFIED BY LSMEDICAL
DEFAULT TABLESPACE TSDMEDICAL  TEMPORARY TABLESPACE   TSTEMPORARY
QUOTA UNLIMITED ON TSDMEDICAL;
ALTER USER LSMEDICAL QUOTA UNLIMITED ON TSIMEDICAL;
ALTER USER LSMEDICAL QUOTA UNLIMITED ON TSTEMPORARY;
0
 
peledcCommented:
What you can try is to remove your new Oracle instance, which I understand is empty
and create a new instance. This time choose the custom option and make the SYSTEM tablespace Dictionary managed.

Other wise,
SELECT .... FROM DBA_TABLESPACE
all the required details for manually creating the new Tablespaces.

Good Luck
0
 
seazodiacCommented:
Creating all the tablesapces are tedious, but this is something you have to LIVE with.

i.e., You have to do that for a succesfull IMPORT.

try to use a SCRIPT to automate this thing.  Peledc give you a nice direction.



Use this to generate all the tablespaces in script: just to save you from trouble,
configure OMF (oracle managed files in Oracle9i new instance) so that you don't need to specify file path when creating new tablespaces.
and assume the largest size of tablespace is 200M.


select 'create tablespace ' || tablespace_name|| 'size 200M ;' from dba_tablespaces where tablespace_name not in ('SYSTEM', 'TEMP', 'RBS');

0
 
schwertnerCommented:
If you run Import taking
show=yes
you will find there also thescripts for creating the tablespaces.
Modify it.
But blind craeting the tablespaces is not a good aproach.
Plan where to place the datafiles - otherwise you will have performance and placement problems.
0
 
Mark GeerlingsDatabase AdministratorCommented:
I agree with schwertner: create the tablespaces in the Oracle9 database manually, then run import with "ignore=Y".  This gives you the advantages of:
1. the data tablespaces can then be locally-managed (the default in Oracle9)
2. the data files can be in a different disk/directory than they were on the old database (this is almost always necessary when moving from one server to another anyway).
0
 
konektorCommented:
it's better not to do full import, but user import instead of it. u have to manually create all objects, which belonges to sys user in source database : create tablespaces, create users, roles ...
0
 
pratheshshahAuthor Commented:
i did the same thing
created tablespace manuallt in oracle 9i
and then run import with ignore=y usign fulldb exported file of 8i but it give following error

IMP-00017: following statement failed with ORACLE error 1119:
 "CREATE TABLESPACE "RBS" DATAFILE  'C:\ORACLE\ORADATA\ORCL\RBS01.DBF' SIZE 5"
 "2428800       AUTOEXTEND ON NEXT 5242880  MAXSIZE 32767M DEFAULT  STORAGE(I"
 "NITIAL 524288 NEXT 524288 MINEXTENTS 8 MAXEXTENTS 4096 PCTINCREASE 50) ONLI"
 "NE PERMANENT "
IMP-00003: ORACLE error 1119 encountered
ORA-01119: error in creating database file 'C:\ORACLE\ORADATA\ORCL\RBS01.DBF'
ORA-27040: skgfrcre: create error, unable to create file
OSD-04002: unable to open file

in current oracle 9i doesnt have RBS tablespace created,
now tell me what should i do

pl.
0
 
Mark GeerlingsConnect With a Mentor Database AdministratorCommented:
Or, create an RBS tablespace manually, then run a full import, then drop the RBS tablespace that you don't need.  This assumes that have used the default "undo" tablespace functionality of Oracle9 instead of a "rollback" tablespace.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.