Solved

How to exclude users from full import ?

Posted on 2011-03-03
22
2,775 Views
Last Modified: 2012-11-19
I created a new database and importing database using full export data dmp file.
exporting is done by exp utility in oracle AND its full export dmp file

How can i import all the data while excluding some default users like SYSTEM, SYS, USERS, sysaux...etc...

current target db version 11.1.0.7...thanks
0
Comment
Question by:Dba_Oracledb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
  • 3
  • +2
22 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 35029991
the Oracle system schemas will automatically be excluded (like SYSTEM and SYS)

 USERS, SYSAUX  - those look like tablespace names,  not schemas.

I'm not sure what you're trying to do with those
0
 

Author Comment

by:Dba_Oracledb
ID: 35030035
i just need to do a full import from export dump file using imp utility to the new database. (exp is full data export )

i created all the table spaces from the source in the target db, what else do i need to do before importing.

please suggest..
0
 

Author Comment

by:Dba_Oracledb
ID: 35030108
so we dont need to import default tablespaces, how can we prevent ?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 249 total points
ID: 35030111
imp user/pass@yourdb  fromuser=(user1,user2,user3)


fromuser is an inclusive list,  simply leave out any users you don't want.
0
 
LVL 12

Expert Comment

by:Praveen Kumar Chandrashekatr
ID: 35030387
why you want to exclude the users?

when you import the full export dump you have to create the database structure i.e all the relevant tablespace in the database.

when you import if the user already present in the database it will just give u and warning that user is already present but it will successfully imported.

imp system/password@yourdb full=y file=exp.dmp
0
 

Author Comment

by:Dba_Oracledb
ID: 35030401
i am getting user doesnot exist error....
0
 

Author Comment

by:Dba_Oracledb
ID: 35030419
i havent tried the above option, will try and let you know..

imp system/password@yourdb full=y file=exp.dmp
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 249 total points
ID: 35030436
using full=y  - you will import all users (except the oracle system ones)

you can always drop any others you don't want
0
 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 83 total points
ID: 35030481
The default tablespaces have to be created in advance (I mean before import of the data) - but only if you import the schemata that use them.

So there are two methods:

1. If the default tablespaces are not created AND full import i used AND the directory structure is the same - the tablespaces will be gracefully created.

2. If you need them on another place - then create them in advance. In this case by the full import you will get ignorable message thatthe tablespaces exist. Ignore this message.

if you use Data Pump if the tablespace doesn't exists ( at least for the indexes) for your surprise they will be placed in the other default tablespace.
0
 
LVL 12

Expert Comment

by:Praveen Kumar Chandrashekatr
ID: 35030539
and don't forget to compile all invaild objects after successfully importing the dump.
0
 

Author Comment

by:Dba_Oracledb
ID: 35030870
here are the error messages

imp \"/ as sysdba\" full=y file=exp_oras_full.dmp log=exp_oras_full.log ignore=y buffer=10000000

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

Export file created by EXPORT:V10.02.01 via direct path

Warning: the objects were exported by RMAN, not by you

import done in US7ASCII character set and UTF8 NCHAR character set
import server uses UTF8 character set (possible charset conversion)
export client uses UTF8 character set (possible charset conversion)
. importing SYSTEM's objects into SYSTEM
IMP-00017: following statement failed with ORACLE error 29339:
 "CREATE UNDO TABLESPACE "UNDOTBS1" BLOCKSIZE 4096 DATAFILE  '/ora_stage/ora_"
 "roll/oradata/oras/undotbs01.dbf' SIZE 7500M       EXTENT MANAGEMENT LOCAL "
IMP-00003: ORACLE error 29339 encountered
ORA-29339: tablespace block size 4096 does not match configured block sizes
IMP-00017: following statement failed with ORACLE error 29339:
 "CREATE TEMPORARY TABLESPACE "TEMP" BLOCKSIZE 4096 TEMPFILE  '/ora_stage/ora"
 "_temp/oradata/oras/temp01.dbf' SIZE 1900M      , '/ora_stage/ora_temp/orada"
 "ta/oras/temp05.dbf' SIZE 1900M      , '/ora_stage/ora_temp/oradata/oras/tem"
 "p02.dbf' SIZE 1900M      , '/ora_stage/ora_temp/oradata/oras/temp03.dbf' SI"
 "ZE 1900M      , '/ora_stage/ora_temp/oradata/oras/temp04.dbf' SIZE 1900M   "
 "    EXTENT MANAGEMENT LOCAL  UNIFORM SIZE 1048576"
IMP-00003: ORACLE error 29339 encountered
ORA-29339: tablespace block size 4096 does not match configured block sizes
IMP-00017: following statement failed with ORACLE error 29339:
 "CREATE TABLESPACE "DRSYS" BLOCKSIZE 4096 DATAFILE  '/ora_stage/ora_data1/or"
 "adata/oras/drsys01.dbf' SIZE 26869760       AUTOEXTEND ON NEXT 655360  MAXS"
 "IZE 16383M EXTENT MANAGEMENT LOCAL  AUTOALLOCATE  ONLINE PERMANENT  SEGMENT"
 " SPACE MANAGEMENT AUTO"
IMP-00003: ORACLE error 29339 encountered
ORA-29339: tablespace block size 4096 does not match configured block sizes
IMP-00017: following statement failed with ORACLE error 29339:
 "CREATE TABLESPACE "EXAMPLE" BLOCKSIZE 4096 DATAFILE  '/ora_stage/ora_data1/"
 "oradata/oras/example01.dbf' SIZE 145489920       AUTOEXTEND ON NEXT 655360 "
 " MAXSIZE 16383M EXTENT MANAGEMENT LOCAL  AUTOALLOCATE  ONLINE PERMANENT  SE"
 "GMENT SPACE MANAGEMENT AUTO"
IMP-00003: ORACLE error 29339 encountered
ORA-29339: tablespace block size 4096 does not match configured block sizes
IMP-00017: following statement failed with ORACLE error 29339:
 "CREATE TABLESPACE "ODM" BLOCKSIZE 4096 DATAFILE  '/ora_stage/ora_data1/orad"
 "ata/oras/odm01.dbf' SIZE 10485760       AUTOEXTEND ON NEXT 655360  MAXSIZE "
 "16383M EXTENT MANAGEMENT LOCAL  AUTOALLOCATE  ONLINE PERMANENT  SEGMENT SPA"
 "CE MANAGEMENT AUTO"


this error goes on.......
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 249 total points
ID: 35030934
precreate all of the tablespaces you'll need

if you don't know what they are, you can look in dba_tablespaces from the source system,  or simply look through the error log for all of the "create tablespace" commands that failed
0
 

Author Comment

by:Dba_Oracledb
ID: 35030954
i created all the tables spaces already ...
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35030978
are you sure you didn't miss some?  The errors above look like the tablespaces are being created again
0
 

Author Comment

by:Dba_Oracledb
ID: 35031046
i created all the source tablespace before importing...dont why its trying to create again....
0
 

Author Comment

by:Dba_Oracledb
ID: 35031228
now i created the user in target database, and imported that perticular user schema, it was imported successfully.


But there were 50 user, is their any faster way ???
0
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 83 total points
ID: 35031736
On the full import, add IGNORE=Y to the command line.
0
 

Author Comment

by:Dba_Oracledb
ID: 35032379
thats the command i used before, please check previous post...with error log
0
 
LVL 35

Expert Comment

by:johnsone
ID: 35037470
I believe that even with IGNORE=Y, that the messages are printed, but you can ignore them.  You should only get them on the create statements at the beginning of the import.

Sorry, I looked and the other posts and I missed the IGNORE=Y before.
0
 
LVL 12

Accepted Solution

by:
Praveen Kumar Chandrashekatr earned 85 total points
ID: 35039507
you have recreate the database with the DB_BLOCK_SIZE =4096 since the export dump is from the database with the db_block_size=4096.

but the new database may with 8192 bytes of block size which will not allow importing it.

please recreate the database with 4096 bytes block size and create all tablesapce with maximum size and try to import with the previous command.
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

726 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