Solved

How to exclude users from full import ?

Posted on 2011-03-03
22
2,224 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
  • 9
  • 5
  • 3
  • +2
22 Comments
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
Comment Utility
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
Comment Utility
so we dont need to import default tablespaces, how can we prevent ?
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 249 total points
Comment Utility
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:praveencpk
Comment Utility
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
Comment Utility
i am getting user doesnot exist error....
0
 

Author Comment

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

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

Assisted Solution

by:sdstuber
sdstuber earned 249 total points
Comment Utility
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 47

Assisted Solution

by:schwertner
schwertner earned 83 total points
Comment Utility
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:praveencpk
Comment Utility
and don't forget to compile all invaild objects after successfully importing the dump.
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.

 

Author Comment

by:Dba_Oracledb
Comment Utility
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 73

Assisted Solution

by:sdstuber
sdstuber earned 249 total points
Comment Utility
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
Comment Utility
i created all the tables spaces already ...
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
Comment Utility
i created all the source tablespace before importing...dont why its trying to create again....
0
 

Author Comment

by:Dba_Oracledb
Comment Utility
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 34

Assisted Solution

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

Author Comment

by:Dba_Oracledb
Comment Utility
thats the command i used before, please check previous post...with error log
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
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:
praveencpk earned 85 total points
Comment Utility
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

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.

Join & Write a Comment

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…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
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.

762 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

9 Experts available now in Live!

Get 1:1 Help Now