Solved

import oracle 8i fulldb data into oracle 9i

Posted on 2004-09-09
14
654 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
 
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
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 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle - Query Insert and Update multiple tables 5 56
File generation using utl_file 4 45
Schema creation in Oracle12c 6 38
su - oracle could not open session 6 47
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

932 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now