Solved

import oracle 8i fulldb data into oracle 9i

Posted on 2004-09-09
14
655 Views
Last Modified: 2008-03-17
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)
0
Comment
Question by:pratheshshah
  • 3
  • 2
  • 2
  • +3
14 Comments
 
LVL 1

Expert Comment

by:peledc
ID: 12023832
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
 

Author Comment

by:pratheshshah
ID: 12024084
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
 
LVL 47

Expert Comment

by:schwertner
ID: 12024166
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Expert Comment

by:peledc
ID: 12025107
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
 
LVL 23

Expert Comment

by:seazodiac
ID: 12025482
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
 
LVL 47

Expert Comment

by:schwertner
ID: 12025768
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
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 12026224
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
 
LVL 9

Expert Comment

by:konektor
ID: 12054563
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
 

Author Comment

by:pratheshshah
ID: 12131280
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
 
LVL 47

Accepted Solution

by:
schwertner earned 125 total points
ID: 12132178
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
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 125 total points
ID: 12133870
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question