Excellant Sir. and a Q
A reader, August 04, 2003 - 10:10 am UTC
Thanks for your convincing Answer.
Wrt answer 2: This means
1)I extract the DDLs for table creation using indexfile option
2)Modify it by removing the storage clause
3)Run that on the user
4)Run imp again with ignore=y. This will get the data in the precreated tables.
Q) What to do about Table constraints will the IMP take care of it or I need to precreate those also ?
Please correct me I am wrong.
August 04, 2003 - 10:21 am UTC
imp will put the constraints on for you. although you might find you want to put on the indexes manually as well since they too have storage clauses.
Thank You sir
A reader, August 04, 2003 - 10:26 am UTC
You are a Greatest service to the ORACLE CLAN
IMP-00013: only a DBA can import a file exported by another DBA
Yogeeraj, November 24, 2003 - 6:53 am UTC
hi sir,
Since only a DBA can import a file exported by another DBA, what can be the workaround that can allow non-dba users to IMP from the DMP file created by the DBA (Full Export)?
We do not want to grant either of DBA or IMP_FULL_DATABASE roles to our developerss yet allow them import from the weekly dump of our dev database.
thank you for your guidance.
best regards
Yogeeraj
=========================
SQL> create user demo_imp identified by demo_imp;
User created.
Elapsed: 00:00:00.01
SQL> grant connect, resource to demo_imp;
Grant succeeded.
Elapsed: 00:00:00.04
SQL> connect demo_imp/demo_imp@dev
Connected.
SQL> create table t (a number(1));
Table created.
SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Producti
on
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production
$ exp system/manager@dev file=demo_imp.dmp owner=demo_imp
Export: Release 8.1.7.4.0 - Production on Mon Nov 24 15:42:09 2003
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production
Export done in US7ASCII character set and US7ASCII NCHAR character set
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user DEMO_IMP
. exporting object type definitions for user DEMO_IMP
About to export DEMO_IMP's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export DEMO_IMP's tables via Conventional Path ...
. . exporting table T 0 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting snapshots
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
$ imp demo_imp/demo_imp@dev file=demo_imp.dmp ignore=yes
Import: Release 8.1.7.4.0 - Production on Mon Nov 24 15:43:19 2003
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production
Export file created by EXPORT:V08.01.07 via conventional path
IMP-00013: only a DBA can import a file exported by another DBA
IMP-00000: Import terminated unsuccessfully
$
November 24, 2003 - 8:56 am UTC
you'll either
a) import for them
b) do the export in a format that they can use.
Even after granting IMP_FULL_DATABASE its not working.
PJ, July 21, 2004 - 7:09 am UTC
Hi Tom,
I'm having some problem here. I'll explain you the scenario.
There are 3 users, with the following roles.
user1 - exp_full_database
user2 - exp_full_database, imp_full_database
user3 - imp_full_database
user2 took the export of a table partition of user1's table.
And now I'm trying to import from this dump file with user user3 using "fromuser=user1 touser=user3". It's throwing an error (IMP-00013) But my user3 has imp_full_database role.
ANd when i try to do the imp with user2 (who initially took the export) its working.
What am I missing here ??
July 21, 2004 - 8:46 am UTC
[tkyte@tkyte-pc tkyte]$ oerr imp 13
00013, 00000, "only a DBA can import a file exported by another DBA"
// *Cause: The privileges needed to Import an export file generated by a
// database administrator do not exist. Only a database administrator
// can import such files.
// *Action: Ask the database administrator to do the import.
[tkyte@tkyte-pc tkyte]$
PJ, July 21, 2004 - 12:22 pm UTC
Tom,
the user2 already have IMP_FULL_DATABASE privilege granted to it. Still is showing the error why?
I even granted DBA role to user2..Still it fails with the same message IMP-00013.
July 21, 2004 - 1:57 pm UTC
prove it to me. show me a cut and paste of the roles granted to user2 (from the dba views) and then show me the entire cut and paste of the command line and the ENTIRE error stack you are recieving.
sort of like I do with my example -- full conclusive disclosure.
I am thinking that no, user2 does not have DBA.
show me a test like this where we can see everything...
ops$tkyte@ORA817DEV> create user u1 identified by u1;
User created.
ops$tkyte@ORA817DEV> create user u2 identified by u2;
User created.
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> grant dba to u1;
Grant succeeded.
ops$tkyte@ORA817DEV> grant create session to u2;
Grant succeeded.
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> create table u1.t ( x int );
Table created.
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> !exp userid=u1/u1 tables=t
Export: Release 8.1.7.4.0 - Production on Wed Jul 21 13:59:51 2004
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
Export done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table T 0 rows exported
Export terminated successfully without warnings.
ops$tkyte@ORA817DEV> !imp userid=u2/u2 fromuser=u1 touser=u2
Import: Release 8.1.7.4.0 - Production on Wed Jul 21 13:59:52 2004
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
Export file created by EXPORT:V08.01.07 via conventional path
IMP-00013: only a DBA can import a file exported by another DBA
IMP-00000: Import terminated unsuccessfully
ops$tkyte@ORA817DEV> grant dba to u2;
Grant succeeded.
ops$tkyte@ORA817DEV> !imp userid=u2/u2 fromuser=u1 touser=u2
Import: Release 8.1.7.4.0 - Production on Wed Jul 21 13:59:52 2004
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
Export file created by EXPORT:V08.01.07 via conventional path
Warning: the objects were exported by U1, not by you
import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set
. . importing table "T" 0 rows imported
Import terminated successfully without warnings.
migrate user account to another server
Goh, August 10, 2004 - 7:24 am UTC
Dear Tom,
Is there a way to migrate 100 users without objects from one instance to another instance ? I thaught of creating the users in the new instance itself, but I don't know what is the password. Thanks again.
Rgds
Goh
August 10, 2004 - 8:05 am UTC
select 'create user ' || username || ' identified by values ''' || password || ''';' from dba_users;
What about the password??!!
A reader, August 10, 2004 - 5:48 pm UTC
Hi Tom,
The above query works like a charm:-
select 'create user ' || username || ' identified by values ''' || password ||
''';' from dba_users;
But aren't the passwords encrypted. So these passwords cannot be used. Or one needs to decrypt them.
I tried using the password generated by the query but, as was expected, it showed me the password is wrong.
So does that mean we have to change the passwords of each and every user and then inform them. Or is there an easy way out?
Please explain,
Thanks as always
Atin
August 10, 2004 - 7:46 pm UTC
the passwords are not encrypted, they are hashed. this just puts the hash into the data dictionary.
try it, watch:
ops$tkyte@ORA9IR2> create user a identified by argle_bargle;
User created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> column password new_val PW
ops$tkyte@ORA9IR2> select password from dba_users where username = 'A';
PASSWORD
------------------------------
E5A7B00DB8FDC2D1
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> drop user a;
User dropped.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create user a identified by values '&PW';
old 1: create user a identified by values '&PW'
new 1: create user a identified by values 'E5A7B00DB8FDC2D1'
User created.
ops$tkyte@ORA9IR2> grant connect to a;
Grant succeeded.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @connect a/argle_bargle
ops$tkyte@ORA9IR2> set termout off
a@ORA9IR2> set termout on
a@ORA9IR2>
you don't use the hashed password to "connect", you use the hashed password to stuff their password in the data dictionary - you use their REAL password (which you never actually know) to connect.
Exporting Tables with XML types
MSU, September 20, 2004 - 2:01 am UTC
Hi Tom,
Thank you very much for such a wonderful site. I wish to know how to Exp / Imp tables sucessfully which has XML Type as a column. I tried - unsucessfully. The log file says Export terminated successfully with warnings. All the other columns in the table along with the data are exported - Except for the specifc - XML Type Column. Please do explain how to accomplish the same. Thanking you in anticipation
September 20, 2004 - 8:10 am UTC
This falls into my favorite category of question :)
the old "my car won't start, why not" class.
"unsuccessfully" -- not very "clear".
You should just be able to export:
ops$tkyte@ORA9IR2> create table t ( x int, y sys.xmltype );
Table created.
ops$tkyte@ORA9IR2> insert into t values ( 1, xmltype( '<foo>bar</foo>' ) );
1 row created.
ops$tkyte@ORA9IR2> commit;
Commit complete.
ops$tkyte@ORA9IR2> !exp userid=/ 'owner=ops$tkyte';
Export: Release 9.2.0.5.0 - Production on Mon Sep 20 07:52:00 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user OPS$TKYTE
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user OPS$TKYTE
About to export OPS$TKYTE's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export OPS$TKYTE's tables via Conventional Path ...
. . exporting table T 1 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @recreateme
sys@ORA9IR2> drop user ops$tkyte cascade;
User dropped.
sys@ORA9IR2> create user ops$tkyte identified externally;
User created.
sys@ORA9IR2> grant connect, dba to ops$tkyte;
Grant succeeded.
sys@ORA9IR2> alter user ops$tkyte default tablespace users;
User altered.
sys@ORA9IR2> grant select any dictionary to ops$tkyte;
Grant succeeded.
sys@ORA9IR2> connect /
ops$tkyte@ORA9IR2> !imp userid=/ full=y
Import: Release 9.2.0.5.0 - Production on Mon Sep 20 07:52:08 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
. importing OPS$TKYTE's objects into OPS$TKYTE
. . importing table "T" 1 rows imported
Import terminated successfully without warnings.
ops$tkyte@ORA9IR2> select * from t;
X
----------
Y
-------------------------------------------------------------------------------
1
<foo>bar</foo>
'SYS.DBMS_EXPORT_EXTENSION'
friend, February 01, 2005 - 1:15 pm UTC
Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
Export done in WE8MSWIN1252 character set and UTF8 NCHAR character set
server uses UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table SRI_12
EXP-00008: ORACLE error 6550 encountered
ORA-06550: line 1, column 27:
PLS-00201: identifier 'SYS.DBMS_EXPORT_EXTENSION' must be declared
ORA-06550: line 1, column 14:
PL/SQL: Statement ignored
Export terminated successfully with warnings.
Please suggest..
February 01, 2005 - 3:38 pm UTC
when you searched for DBMS_EXPORT_EXTENSION on this site, what did you find?
exp
friend, February 01, 2005 - 5:06 pm UTC
Import from SQL Server 2000
Dawar, April 17, 2005 - 8:08 pm UTC
can we exp/imp data from sql server 2000 to Oracle 10.1.0.3.0?
Is this utility available in sql server 2000?
Nice
Carolyn, April 18, 2005 - 12:12 pm UTC
Hi Tom,
Whether Export uses Read consistency??
April 18, 2005 - 12:17 pm UTC
everything uses it.
with exp, you can even say "consistent=y" so as to have the entire export happen as if it all took place against an idle, unchanging database -- all queries run by export would be "as of" the start of the export, all consistent with respect to eachother.
alt, April 19, 2005 - 9:57 am UTC
is it possible to imort a in oracle 9i using oracle 8i's export dump file(load data from oracle 8i to oracle 9i)
when tried for this i got a followng warnings
Warning: the objects were exported by MAIN, not by you
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
export client uses WE8ISO8859P1 character set (possible charset conversion)
export server uses WE8ISO8859P1 NCHAR character set (possible ncharset conversio
n)
IMP-00025: for DBA export files, FROMUSER must be specified with TOUSER option
IMP-00000: Import terminated unsuccessfully
April 19, 2005 - 10:24 am UTC
sure it is -- you have to use the right set of command line options.
IMP-00025: for DBA export files, FROMUSER must be specified with TOUSER option
sounds like you did a touser, without a fromuser perhaps.
User passwords
Donna, July 01, 2005 - 6:21 pm UTC
Tom,
I think I know the answer, but I'm asking anyway in the spirit of "no stupid questions".
I can create a user in another schema using the method outlined above, but we are changing user id standards and I'd like to create a new user in the same schema using the old id's password (ex. t123456 to 000123456 using t123456's pw).
So far, I can't log in successfully with the new id.
Can it be done?
Thanks!
July 01, 2005 - 7:22 pm UTC
no, because the passwords are not stored encrypted, they are stored hashed
what you see in the dictionary is a function of (username,password,salt)
those three things hash to something, that is stored. the database will take the user/pass add salt and hash it, see if they match
therefore, taking the values of someone elses hashed user/pass/salt and giving it to someone else results not in the same password.
user a export user b
AJ Allen, August 31, 2005 - 2:49 pm UTC
I have user a_mgr, which owns all of the objects for an application within a database. There are also users b_mgr, c_mgr, etc -- which own other sets of objects for other business applications. I have a need to grant a user, let us call him a_usr, the ability to export a_mgr ( exp userid=a_usr/foo owner=a_mgr ).
I may not grant broad-privileged roles such as exp_full_database or dba to a_usr because then he could have access to b_mgr, c_mgr, etc.
Is there a set or privileges I could grant to a_usr so that he could export a_mgr only? I have tried a number of combinations of the privileges granted to exp_full_database, but the 'ANY' privileges result in security concerns and I do not understand the execute privileges on the various procs owned by SYS so have no idea what I am granting -- or if I should.
If there is a solution, I would greatly appreciate your help with it.
August 31, 2005 - 3:31 pm UTC
see the other place you put this same question
reader
A reader, September 08, 2005 - 3:16 pm UTC
$ imp username/password full=y show =y
Import: Release 9.2.0.5.0 - Production on Thu Sep 8 12:36:00 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
JServer Release 9.2.0.5.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
Warning: the objects were exported by HRG0110, not by you
import done in US7ASCII character set and AL16UTF16 NCHAR character set
. importing USER1's objects into SYS
"CREATE TABLE "DMT1" ("COL1" VARCHAR2(2000)) PCTFREE 10 PCTUSED 40 INITRANS"
" 1 MAXTRANS 255 STORAGE(INITIAL 98304 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 50"
"5 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPA"
"CE "DM" LOGGING NOCOMPRESS"
. . skipping table "DMT1"
"AUDIT ALTER,AUDIT,COMMENT,DELETE,GRANT,INDEX,INSERT,LOCK,SELECT,UPDATE ON "
""DMT1" BY SESSION"
"AUDIT RENAME ON "DMT1" BY ACCESS"
Import terminated successfully without warnings.
The line
". importing USER1's objects into SYS" from
the above imp log
1. What is the significance of this statement. What exactly
does this statement do
September 08, 2005 - 5:52 pm UTC
did you use the import as sys?
reader
A reader, September 08, 2005 - 6:27 pm UTC
$ imp user1/user1 full=y show =y
Import: Release 9.2.0.5.0 - Production on Thu Sep 8 17:52:52 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
JServer Release 9.2.0.5.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
. importing USER1's objects into USER1
. importing SYS's objects into SYS
"ALTER SESSION SET CURRENT_SCHEMA= "SYS""
"CREATE TABLE "DMT1" ("COL1" VARCHAR2(2000)) PCTFREE 10 PCTUSED 40 INITRANS"
" 1 MAXTRANS 255 STORAGE(INITIAL 98304 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 50"
"5 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPA"
"CE "DM" LOGGING NOCOMPRESS"
. . skipping table "DMT1"
"AUDIT ALTER,AUDIT,COMMENT,DELETE,GRANT,INDEX,INSERT,LOCK,SELECT,UPDATE ON "
""DMT1" BY SESSION"
"AUDIT RENAME ON "DMT1" BY ACCESS"
Import terminated successfully without warnings.
I am trying to find what is the significance of
"
. importing SYS's objects into SYS
"
Does this statement restores data dictionary
information relating to "user1" ( password, assigned
privileges, grants etc. ).
If I drop and recreate the user. Then imp rows=n, will I
be able to restore the user1 credentials completely
September 08, 2005 - 6:35 pm UTC
you have a list of what is being done right there?
don't touch sys, don't create things in sys, don't log in as sys, leave sys alone in the future, it is 'special'
if you drop user1, and user1 is recreated entirely by the import process, you'll have what you had before you dropped them
reader
A reader, September 09, 2005 - 9:13 am UTC
your comment
<don't log in as sys>
Nobody is logging in as SYS . If you are in a DBA group
"connect / as sysdba" logs you to SYS account. For
maintanance purpose, it is utmost essential to log in
as sysdba
September 09, 2005 - 9:35 am UTC
tell me, who own DMT1?
Thanks for your help
A reader, September 09, 2005 - 12:37 pm UTC
Sometimes when we test simple concepts on test databases
we lose track of the current_schema :)
contents of export
Alay, September 16, 2005 - 9:41 am UTC
Hi tom,
Suppose i have exported a user schema and created a export.dmp file. I want to know that is there any way to see the contents of export.dmp file without importing it?
September 16, 2005 - 10:01 am UTC
.... show=yes .....
exp and imp with none existing object references
MG, October 05, 2005 - 9:04 am UTC
Hi tom,
Oracle Version:
---------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
I have a database : DB1 in location LOC1
I need to migrate DB1 to another location : LOC2.
Note:
-----
(1).DB1 ( in LOC1) referes (by Procedures, Functions & Packages) --> another databases called : DB2, DB3 & DB4
But DB1 ( in Loc2 ) doesn't have these referenced databases. Because in future we need use the same source in Oracle(Procedures, Functions & Packages) without these referenced databases.
(2). I can't shutdown the DB1(in Loc1).
(3). So I have taken the full db dump from DB1(Loc1).
(4). Create a sake structure on DB1(Loc2)
(5). Import the dump from DB1(LOC1) to DB1(LOC2).
My problem is:
--------------
When I import the dump file it takes very very long time. because it is searching for missing references.
What I want:
-----------
Could you please tell me, how do I update the new Database DB1(LOC2), in fast way?
Thank you in advance.
October 05, 2005 - 11:13 am UTC
to me this is called "source code control" and exp/imp are not source code control tools
Where do you MANAGE the real code and why are you not using that?
MG, October 05, 2005 - 11:18 am UTC
Actually Our company has buy by another company. So we have to seperate applications from old platform to new platform.
So in future we can't access these reference databases.
We are taking necessary data as flat file and load data to the database via SQL Loader.
So we are going to change the source code.
October 05, 2005 - 11:47 am UTC
sorry, imp exp are what they are - a method to copy from A to B, without change.
Exp and Imp issue
A reader, October 13, 2005 - 4:45 am UTC
Hi Tom,
Oracle Version 8i Rel 2
O/S Windows 2000
We are in the process of shifting an existing db to another server.
The DBA earlier to me created all the tablespaces in DMT.
Now, I have to change them all into LMT.
This server has lack of space therefore the move or rebuild options have to be ignored.
I am going to take an export of the database.
But is it possible that while importing the database the tablespaces are created in LMT mode.
I a not sure about the indexfile=y as I donot know the size of the tablespaces at that moment.
Therefore is it possible?
Using indexfile=y I have to write the tablespace creation in that file and save it as a script and run it.
While importing I'll do an ignore=y
But I am looking for another alternative.
Thanks in advance.
October 13, 2005 - 10:39 am UTC
precreate the tablespaces.
You know, for like $300usd you could get like 300gig of storage temporarily, through it out later when you don't need it anymore. Probably be less expensive than the amount of time you'll spend doing work arounds.
And if you don't have enough freespace on your system for a dual copy of a given table or index - you don't have enough freespace for anything.
Thanks for the Insight!!!
A reader, October 14, 2005 - 12:40 am UTC
Hi again,
I knew that you are going to suggest something like this only ;)
Well the fact is that it is I myself do not wish to buy anothet hard disk of such capacity.
The server now is just a normal PC sort.
The new one is like 300 GB, 1GB RAM and Xeon Processor.
So i was thinking that I'll do a normal exp and imp. And then on the new server I will do all the move and rebuilds.
Well it has to be done online.
My question is that will it effect the transactions if I do it online?
It is 8i Rel 3 and not Rel 2
Thanks as always.
October 14, 2005 - 7:36 am UTC
where are you going to store the dmp file...... if you have room for the dmp file, you have room to do alter moves.
exp/imp is OFFLINE.
8i has no online reorg for tables, only index rebuilds.
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
oracle user, October 22, 2005 - 6:07 pm UTC
Tom,
On the similar lines, how to export the schema from oracle 9i on solaris, using 10g installation on windows xp?
I tried playing with couple of options on exp but could successfully export and import.
Thanks for all the responses and suggestions.
Regards
C:\oracle\product\10.1.0\db_1\BIN>exp
Export: Release 10.1.0.2.0 - Production on Sat Oct 22 16:57:33 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Username: usr1/pwd2@uat.world
Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
Enter array fetch buffer size: 4096 >
Export file: EXPDAT.DMP >
(2)U(sers), or (3)T(ables): (2)U >
Export grants (yes/no): yes >
Export table data (yes/no): yes >
Compress extents (yes/no): yes >
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
server uses US7ASCII character set (possible charset conversion)
EXP-00056: ORACLE error 942 encountered
ORA-00942: table or view does not exist
EXP-00000: Export terminated unsuccessfully
C:\oracle\product\10.1.0\db_1\BIN>
October 23, 2005 - 4:47 am UTC
you should use the version of exp that came with the database - you'll use the 9i export (which is expecting the 9i export views), not the 10g exp (which is expecting the 10g export views - which don't exist in 9i).
Alexander the ok, December 13, 2005 - 2:37 pm UTC
Tom,
I've just created a new db that I'm trying to import data into. I've applied the 9.2.0.7 patch. No one else I work with has ever had this problem thus doesn't know what this means I hope you can explain it to me:
D:\>IMP system/password FILE=D:\PRO3_9i_expdat_full.dmp log=D:\imp.log ignore=y grants=y full=y
Import: Release 9.2.0.7.0 - Production on Tue Dec 13 14:14:55 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
IMP-00017: following statement failed with ORACLE error 3113:
"BEGIN "
"SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(SYS.DBMS_RULE_ADM.CREATE_EVALUATIO"
"N_CONTEXT_OBJ, 'DBA',TRUE);"
""
"SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(SYS.DBMS_RULE_ADM.CREATE_EVALUATIO"
"N_CONTEXT_OBJ, 'AQ_ADMINISTRATOR_ROLE',TRUE);"
""
"SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(SYS.DBMS_RULE_ADM.CREATE_EVALUATIO"
"N_CONTEXT_OBJ, 'SYS',TRUE);"
""
"SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(SYS.DBMS_RULE_ADM.CREATE_ANY_EVALU"
"ATION_CONTEXT, 'DBA',TRUE);"
""
"SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(SYS.DBMS_RULE_ADM.CREATE_ANY_EVALU"
"ATION_CONTEXT, 'SYS',TRUE);"
""
"SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(SYS.DBMS_RULE_ADM.ALTER_ANY_EVALUA"
"TION_CONTEXT, 'DBA',TRUE);"
""
"SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(SYS.DBMS_RULE_ADM.ALTER_ANY_EVALUA"
"TION_CONTEXT, 'SYS',TRUE);"
""
"SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(SYS.DBMS_RULE_ADM.DROP_ANY_EVALUAT"
"ION_CONTEXT, 'DBA',TRUE);"
""
"SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(SYS.DBMS_RULE_ADM.DROP_ANY_EVALUAT"
"ION_CONTEXT, 'SYS',TRUE);"
""
"SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(SYS.DBMS_RULE_ADM.EXECUTE_ANY_EVAL"
"UATION_CONTEXT, 'DBA',TRUE);"
""
"SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(SYS.DBMS_RULE_ADM.EXECUTE_ANY_EVAL"
"UATION_CONTEXT, 'SYS',TRUE);"
""
"COMMIT; END;"
IMP-00003: ORACLE error 3113 encountered
ORA-03113: end-of-file on communication channel
IMP-00003: ORACLE error 3114 encountered
ORA-03114: not connected to ORACLE
I hope the answer is not "3113 is bad, your database is hosed"....Thank you.
December 13, 2005 - 5:19 pm UTC
3113 = contact support.
database isn't "hosed", but something is wrong.
Alexander the ok, December 13, 2005 - 4:58 pm UTC
Hi Tom sorry I am getting bad at this but I know sort of what the problem is and the imp is running now so you can disregard above.
OEM and Export
Tom Fox, December 14, 2005 - 1:18 am UTC
Tom,
I've been trying to figure out a possible issue using OEM and Export.
We have OEM 9.2.0.7 installed, and the OEM repository is UTF8. The agents on our database servers are started with NLS_LANG and NLS_NLANG set to AMERICAN_AMERICA.UTF8. Our databases are a mixture of UTF8, US7ASCII, and WE8ISO8859P1 on v8.1.7 and 9.2.0.4.
All of our UTF8 databases are exported fine. However, any non-UTF8 database gets the warning about character conversion.
I would like to be able to export each database in its particular characterset in order to avoid any conversion. Is there a way in OEM to set the NLS_LANG and NLS_NLANG to be used during this particular session?
Kazmi, January 04, 2006 - 1:24 pm UTC
Can you please provide me the link for (AJ Allen) question, I am looking for the answer for the same question.
January 04, 2006 - 2:24 pm UTC
indexfile exp parameter on 817?
John Griffiths, January 05, 2006 - 10:43 am UTC
Checking the allowed export parameters on 817 doesn't list indexfile as an option, is it introduced in a later version of Oracle?
Keyword Description (Default) Keyword Description (Default)
--------------------------------------------------------------------------
USERID username/password FULL export entire file (N)
BUFFER size of data buffer OWNER list of owner usernames
FILE output files (EXPDAT.DMP) TABLES list of table names
COMPRESS import into one extent (Y) RECORDLENGTH length of IO record
GRANTS export grants (Y) INCTYPE incremental export type
INDEXES export indexes (Y) RECORD track incr. export (Y)
ROWS export data rows (Y) PARFILE parameter filename
CONSTRAINTS export constraints (Y) CONSISTENT cross-table consistency
LOG log file of screen output STATISTICS analyze objects (ESTIMATE)
DIRECT direct path (N) TRIGGERS export triggers (Y)
FEEDBACK display progress every x rows (0)
FILESIZE maximum size of each dump file
QUERY select clause used to export a subset of a table
The following keywords only apply to transportable tablespaces
TRANSPORT_TABLESPACE export transportable tablespace metadata (N)
TABLESPACES list of tablespaces to transport
January 05, 2006 - 11:04 am UTC
it is an IMP parameter.
IMP can read the dmp file and create an indexfile.
Answered my own question...
John Griffiths, January 05, 2006 - 10:57 am UTC
Indexfile is an import parameter, so perform the import with show=Y indexfile=foo.sql
Then work out a fast way to edit foo.sql into a useable format without the storage options.
Thanks.
Export Import Problem
Srinivas, February 02, 2006 - 4:42 am UTC
Hi Tom,
I have received a multi file export dump from the client. When i tried to import into the our local database, we are getting errors. I give below an extract the error look like.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
Warning: the objects were exported by SYSTEM, not by you
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
IMP-00046: using FILESIZE value from export file of 681564000
. importing SYSTEM's objects into SYSTEM
IMP-00015: following statement failed because the object already exists:
"CREATE UNDO TABLESPACE "UNDOTBS1" BLOCKSIZE 8192 DATAFILE '/ora003/app/ora"
"cle/oradata/BRC1/undotbs01.dbf' SIZE 4928M AUTOEXTEND ON NEXT 5242880"
" MAXSIZE 32767M EXTENT MANAGEMENT LOCAL "
IMP-00017: following statement failed with ORACLE error 1119:
"CREATE TEMPORARY TABLESPACE "LMTEMP1" BLOCKSIZE 8192 TEMPFILE '/ora002/app"
"/oracle/oradata/BRC1/LMTEMP1_01.dbf' SIZE 851443712 AUTOEXTEND ON NEX"
"T 2097152 MAXSIZE 32767M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576"
IMP-00003: ORACLE error 1119 encountered
ORA-01119: error in creating database file '/ora002/app/oracle/oradata/BRC1/LMTEMP1_01.dbf'
ORA-27040: skgfrcre: create error, unable to create file
Linux Error: 2: No such file or directory
IMP-00017: following statement failed with ORACLE error 1119:
"CREATE TABLESPACE "ACDATA" BLOCKSIZE 8192 DATAFILE '/ora002/app/oracle/ora"
"data/BRC1/ACDATA01.dbf' SIZE 6000M AUTOEXTEND ON NEXT 2097152 MAXSIZ"
"E 32767M EXTENT MANAGEMENT LOCAL AUTOALLOCATE ONLINE PERMANENT SEGMENT S"
"PACE MANAGEMENT AUTO"
IMP-00003: ORACLE error 1119 encountered
ORA-01119: error in creating database file '/ora002/app/oracle/oradata/BRC1/ACDATA01.dbf'
ORA-27040: skgfrcre: create error, unable to create file
Linux Error: 2: No such file or directory
IMP-00017: following statement failed with ORACLE error 1119:
"CREATE TABLESPACE "ACINDEX" BLOCKSIZE 8192 DATAFILE '/ora003/app/oracle/or"
"adata/BRC1/ACINDEX01.dbf' SIZE 2000M AUTOEXTEND ON NEXT 2097152 MAXS"
"IZE 32767M EXTENT MANAGEMENT LOCAL AUTOALLOCATE ONLINE PERMANENT SEGMENT"
" SPACE MANAGEMENT AUTO"
IMP-00003: ORACLE error 1119 encountered
ORA-01119: error in creating database file '/ora003/app/oracle/oradata/BRC1/ACINDEX01.dbf'
ORA-27040: skgfrcre: create error, unable to create file
Linux Error: 2: No such file or directory
IMP-00015: following statement failed because the object already exists:
Can i now ask the client to give me more information regarding the creation of database(like tablespaces to be created, users to be created, roles to be created etc.) or i have to find the information myself from the dump. Please guide.
Thanks
Srinivas
February 02, 2006 - 11:56 am UTC
if you haven't precreated the tablespaces, you would need to understand what directories you need to have in place.
You can either ask the person that sent this to you OR
You can run the import with show=y and redirect the output to a file
show=y will get the DDL that would be executed into a file so you can see what tablespaces/files it will try to create.
exp imp issue
David Andrew, February 20, 2006 - 12:46 pm UTC
Dear Tom,
I have a table level export with me. Now the development team has added 3 new addtional columns to the table. When I tried to add these 3 new columns with Not null constraints, it does not allow me to, simply because the table has to be empty first. So, I truncated the table data hoping that I've a table data export backup. After adding those 3 new columns with Not null constraints, I tried to Import...and the import failed,
Error is, the table has more columns than the export file.
How do I Import the table data into a table with New Columns in it?
Tom, I remember reading your book "Expert One on One" where you had mentioned exactly this problem and a Work around for this.
Can you please suggest me those ideas please? Since I have the left the book back in India, i dont have soft copy either.
Thanks in advance.
David.
February 20, 2006 - 2:07 pm UTC
you can certainly add a column with NOT NULL - you have to tell us what to set the column to though with a DEFAULT and if you don't want that as the default forever, you can:
a) add column
b) update it
c) then alter column to be not null
or
a) add column not null default 'default for existing rows'
b) alter column set default to NULL (which is the default default of course)
ops$tkyte@ORA9IR2> create table t ( x int );
Table created.
ops$tkyte@ORA9IR2> insert into t values ( 1 );
1 row created.
ops$tkyte@ORA9IR2> commit;
Commit complete.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> !exp userid=/ tables=t
Export: Release 9.2.0.6.0 - Production on Mon Feb 20 13:52:30 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table T 1 rows exported
Export terminated successfully without warnings.
ops$tkyte@ORA9IR2> alter table t add y int default 42 not null;
Table altered.
<b>that shows you can certainly add that not null column to a table with rows</b>
ops$tkyte@ORA9IR2> truncate table t;
Table truncated.
ops$tkyte@ORA9IR2> !imp userid=/ full=y ignore=y
Import: Release 9.2.0.6.0 - Production on Mon Feb 20 13:52:31 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing OPS$TKYTE's objects into OPS$TKYTE
. . importing table "T" 1 rows imported
Import terminated successfully without warnings.
ops$tkyte@ORA9IR2> select * from t;
X Y
---------- ----------
1 42
<b>and that shows the import works just fine - what happens when you do what I did?</b>
exp imp issues
David Andrew, February 22, 2006 - 12:59 pm UTC
That was a wonderful explanation with an example...
I dint think of working out with the Default. NOw I've a clear picture of work around.
Thank you so much Tom.
Regards,
David.
Lostless export and import?
DJ, March 22, 2006 - 4:39 pm UTC
Hi, Tom
Is it possible to export data from a AL16UTF16 NCHAR database and import into a UTF8 NCHAR database with lostless conversion?
If so, can you detail out to do it?
Thanks a lot,
March 22, 2006 - 5:17 pm UTC
I'm not a character set expert - are they equivalent, if you research what they can hold is there a one to one mapping between them.
If so, sure
If not, no
and how to do it? Just do it, we'll convert from one to the other as the data comes in. If it should be/could be done lossless - it will be. If it cannot be - it won't.
tables not exported by DBA and full=n why still imp-00013 error ?
Parag Jayant Patankar, July 20, 2006 - 2:13 am UTC
Hi Tom,
I am doing export with following command on 9.2 database on AIX. tablename getting passed as parameter
exp / tables=test.$table file=$table.dmp log=export.log full=n
SQL> show user
USER is "OPS$test
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
BACKUP ANY TABLE
SELECT ANY TABLE
SELECT ANY SEQUENCE
EXECUTE ANY PROCEDURE
ALTER ANY TRIGGER
ANALYZE ANY
EXECUTE ANY TYPE
ADMINISTER RESOURCE MANAGER
Now I am trying to importtable with test
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
CREATE SYNONYM
CREATE PUBLIC SYNONYM
DROP PUBLIC SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE ANY DIRECTORY
10 rows selected.
SQL> show user
USER is "TEST"
Now I am trying to import with following command after passing table parameter
imp test/test fromuser=/ touser=test tables=$table commit=yes file=$table.dmp
But I am getting following error
Export file created by EXPORT:V09.02.00 via conventional path
IMP-00013: only a DBA can import a file exported by another DBA
IMP-00000: Import terminated unsuccessfully
Kindly let me know where is error ( I belive in exp but not sure ) and secondly how to correct it without changing privileges.
thanks & regards
pjp
July 22, 2006 - 4:36 pm UTC
why are you using "fromuser" "touser"
you just want to use full=y, the user is identical.
select * from dba_role_privs where grantee = "that user"
is the query you wanted to run to verify things.
cross platform export-import
Alay, September 13, 2006 - 6:06 am UTC
Hi Tom,
Can we do export one table from database A which is on HP-UNIX machine and import that table into the database B which is on Solaris-9 machine?
September 13, 2006 - 2:34 pm UTC
yes
exporting only procedures and functions
abz, November 14, 2006 - 9:35 am UTC
I want to export all procedures and functions in a user,
but nothing else, no tables, no indexes etc. How can I do this, which options to use?
November 15, 2006 - 6:31 am UTC
using exp - not going to happen.
search this site for
getallcode
for a script that you might find useful.
what about expdp
abz, November 16, 2006 - 5:20 am UTC
Ok, is it possible in 10g expdp?
November 16, 2006 - 3:16 pm UTC
full export/import
Dawar, November 16, 2006 - 10:51 am UTC
OS: Unix
DB: 10.2
Tom,
I have two database servers running on same OS with same version of oracle.
I need to import two schemas from Server A to Server B.
Currently we have three schemas on Server A.
I am thinking to do full export from server A and do the full import to server B.
After that I will remove unwanted schemas from server B.
Is it right approach?
Do I need to create same tablespace format as like server A in Server B prior to full import?
I have been informed that in 10 g you do not need to create tablespace or in other words do not need to do any thing,
it will be take care by full export/import.
Is it correct?
cheers,
Dawar
November 16, 2006 - 3:26 pm UTC
why not just get what you need, what is your thought process here?
you didn't need to create them in 9i or 8i in general either. a full export would export tablespace definitions as well.
exp from 10g,
A reader, November 16, 2006 - 1:01 pm UTC
Can I take export using "exp" from 10.1.0.5 of a schema which is on 9.2.0.6 database?
If I do I am getting the following error:
EXP-00008: ORACLE error 904 encountered
ORA-00904: "TYPE": invalid identifier
However, If I take export from the 9.2.0.6 database (the schema belongs to this database), it works. I am using exp.pile to compress the file before it dumps into the disk.
thanks,
November 16, 2006 - 3:36 pm UTC
you use the 9ir2 export again 9ir2, not 10g.
more explanation,
A reader, November 16, 2006 - 6:04 pm UTC
Thanks Tom, but I did not clearly understood the steps you mentioned. Could you please explain it again?
Thanks,
November 17, 2006 - 3:14 am UTC
if you are "the reader" from right above, then:
a) to export from 9iR2 you use the exp tool supplied with 9ir2.
period. You do not use the 10g export tool against 9i
secure export
Aladdin, February 20, 2007 - 3:14 am UTC
DEAR TOM , IM USING DATBASE 10 G AND IM EXPORTING THE DUMP FILE BUT ANY ONE KNOW THE USER CAN IMPORT THIS FILE , I ASK IF THERS IS A WAY TO PREVENT THIS
February 20, 2007 - 9:40 am UTC
DON'T GIVE THEM THE FILE?
got capslock?
I have problem with exp_full_database
ghassem koolivand, December 16, 2008 - 8:41 am UTC
Dear mr.kyte
my scenario :
I decide create one role like EXP_FULL_DATABASE
-- Create the role
create role tsnback;
-- Grant/Revoke object privileges
grant read on directory DATA_PUMP_DIR to tsnback;
grant execute on DBMSZEXP_SYSPKGGRNT to tsnback;
grant execute on DBMS_AQ_IMPORT_INTERNAL to tsnback;
grant execute on DBMS_IAS_INST_UTL_EXP to tsnback;
grant execute on DBMS_IJOB to tsnback;
grant execute on DBMS_IREFRESH to tsnback;
grant execute on DBMS_PSWMG_IMPORT to tsnback;
grant execute on DBMS_RULE_EXIMP to tsnback;
grant execute on DBMS_TRANSFORM_EXIMP to tsnback;
grant select on EXU10LNK to tsnback;
grant select on EXU8LNK to tsnback;
grant select on EXU9LNK to tsnback;
grant insert, update, delete on INCEXP to tsnback;
grant insert, update, delete on INCFIL to tsnback;
grant insert, update, delete on INCVID to tsnback;
grant select on "_DBA_APPLY" to tsnback;
grant select on "_DBA_APPLY_CONF_HDLR_COLUMNS" to tsnback;
grant select on "_DBA_APPLY_ERROR" to tsnback;
grant select on "_DBA_APPLY_ERROR_HANDLER" to tsnback;
grant select on "_DBA_APPLY_ERROR_TXN" to tsnback;
grant select on "_DBA_APPLY_MILESTONE" to tsnback;
grant select on "_DBA_APPLY_PROGRESS" to tsnback;
grant select on "_DBA_APPLY_SOURCE_OBJ" to tsnback;
grant select on "_DBA_APPLY_SOURCE_SCHEMA" to tsnback;
grant select on "_DBA_APPLY_SPILL_TXN" to tsnback;
grant select on "_DBA_CAPTURE" to tsnback;
grant select on "_DBA_STREAMS_MESSAGE_CONSUMERS" to tsnback;
grant select on "_DBA_STREAMS_MESSAGE_RULES" to tsnback;
grant select on "_DBA_STREAMS_MSG_NOTIFICATIONS" to tsnback;
grant select on "_DBA_STREAMS_PRIVILEGED_USER" to tsnback;
grant select on "_DBA_STREAMS_RULES" to tsnback;
grant select on "_DBA_STREAMS_TRANSFORMATIONS" to tsnback;
-- Grant/Revoke role privileges
grant execute_catalog_role to tsnback;
grant select_catalog_role to tsnback;
-- Grant/Revoke system privileges
--grant administer resource manager to tsnback;
execute DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE('tsnback','ADMINISTER_RESOURCE_MANAGER',FALSE);
grant backup any table to tsnback;
grant execute any procedure to tsnback;
grant execute any type to tsnback;
grant read any file group to tsnback;
grant resumable to tsnback;
grant select any sequence to tsnback;
grant select any table to tsnback;
this permission exactly like EXP_FULL_DATABASE permissions.
---------------------
-- connect with sys user
SQL > create user a identified by a ;
SQL > create user b identified by b ;
SQL > create user c identified by c ;
SQL > grant create session to a;
SQL > grant create session to b;
SQL > grant create session to c;
SQL > grant create table to a;
SQL > grant EXP_FULL_DATABASE to b;
SQL > grant tsnback to c;
-- connect with a user
SQL > create table test (c1 number,c2 nubmer);
--connect with sys user
SQL > revoke create table from a;
now when i export with b user ,i have not problem but if i export c user i get below error :
oracle@linux-sec:~> exp c/c owner=a
Export: Release 10.2.0.1.0 - Production on Tue Dec 16 17:46:29 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AR8MSWIN1256 character set (possible charset conversion)
EXP-00032: Non-DBAs may not export other users
EXP-00000: Export terminated unsuccessfully
what is my mistake?
tank you in advance
December 29, 2008 - 11:12 am UTC
it is looking for a specific role, not a set of privileges equivalent to that role.
ghasem, January 05, 2009 - 7:14 am UTC
Hi Mr. kyte
Thanks for your reply
so,I can`nt create one role and grant it to one user.then export with that user.I need grant EXP_FULL_BACKUP to user for export opration.
that`s ok ???
thanks
January 05, 2009 - 11:39 am UTC
well, I'd be questioning why this user would need to export another users data - that seems to be a problem. but the error message is clear - you need the grants.
ghassem koolivand, January 05, 2009 - 7:15 am UTC
Hi Mr. kyte
Thanks for your reply
so,I can`nt create one role and grant it to one user.then export with that user.I need grant
EXP_FULL_BACKUP to user for export opration.
that`s ok ???
thanks
error
A reader, February 04, 2009 - 10:03 am UTC
Tom:
could this error be due to a bug in oracle? when you try to backup a schema.
EXP-00056: ORACLE error 19206 encountered
ORA-19206: Invalid value for query or REF CURSOR parameter
ORA-06512: at "SYS.DBMS_XMLGEN", line 83
ORA-06512: at "SYS.DBMS_METADATA", line 353
ORA-06512: at "SYS.DBMS_METADATA", line 418
ORA-06512: at "SYS.DBMS_METADATA", line 457
ORA-06512: at "SYS.DBMS_METADATA", line 1181
ORA-06512: at "SYS.DBMS_METADATA", line 1162
ORA-06512: at line 1
EXP-00056: ORACLE error 19206 encountered
ORA-19206: Invalid value for query or REF CURSOR parameter
ORA-06512: at "SYS.DBMS_XMLGEN", line 83
ORA-06512: at "SYS.DBMS_METADATA", line 345
ORA-06512: at "SYS.DBMS_METADATA", line 410
ORA-06512: at "SYS.DBMS_METADATA", line 449
ORA-06512: at "SYS.DBMS_METADATA", line 1156
ORA-06512: at "SYS.DBMS_METADATA", line 1141
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully
February 04, 2009 - 12:13 pm UTC
cannot be from backing up, you are using export. Export is not a backup tool, it just copies data out of the database so it can sometimes be reloaded using import.
RMAN does backups....
please see support Note 206313.1 regarding this
backup
A reader, February 09, 2009 - 7:54 pm UTC
tom:
Thanks. For small dbs would not be using exp/imp for backups?
What are the major benefits for RMAN? more reliable backsups? so this error would not occur.
i want to tell a DBA we might be doing the wrong thing here.
February 10, 2009 - 6:56 am UTC
the benefit of rman is that is actually provides *a backup*
whereas exp provides a logical copy of the data that you might, *might* be able to import with again.
ask your DBA what user they use to export with - I'd be very interested, if I could see all of the parameters, that'd be great.
A reader, May 16, 2009 - 5:15 am UTC
Mr tom
I read some documents but still not sure if the sys,system and other users are exported or imported with full=y option
When I try to import with full=y.
I got warnings such as:
. importing SYS's objects into SYS
PL/SQL: Statement ignored
IMP-00017: following statement failed with ORACLE error 6550
..
..
..
May 23, 2009 - 10:54 am UTC
the logs show you what is exported when using exp.
the logs show you what is imported when using IMP
IMP with show=y will show you exactly what is going to be imported.
yes, some sys and system objects are exported (public synonyms are owned... by sys, application context's are owned... by sys - for example) - the data, if not moved, would be necessary on the new database.
A reader, May 30, 2009 - 2:43 am UTC
Since the
user rights,packages,procedures stored in data dictionary which is in system tablespace.
This should be exported as well.
Am I right tom?
June 01, 2009 - 7:44 pm UTC
eh? not any idea what you mean by that
this is a BIG page
over six years of comments
you need to be much much much (as in a LOT) more clear as to what you are referring to
A reader, June 06, 2009 - 9:49 pm UTC
Sir,
Suppose I export table
and then drop table.
and then decided to import back.
I figured out that if there is a foreign key in some other table that has referenced column in the table I dropped, this foreign key will not recreated after import !!!!
I think I should consider this, before dropping the table.
Is there anything like this I should consider regarding the export/import issue.
June 08, 2009 - 1:09 pm UTC
in order to drop the table - you had to have said:
drop table t cascade constraints;
without cascade constraints (or an explicit drop) you would not be able to drop it.
so, this will NOT be a big surprise (like you seem to make it sound like it would be). It is an obvious side effect - before dropping that table you HAVE to drop the constraints on other tables that point to it - you do that explicitly, it is not at all hidden from you.
I cannot imagine what else you might want to "consider" as I'm not sure what you need to account for. To me, if I dropped a table, I dropped it - poof - it is gone. I'm not sure why you would be turning around and importing it immediately (or doing that more than say "once" in a lifetime).
If you are using export/import to "reorganize" - stop it, don't, danger lurks there. Use alter table T move or use dbms_redefinition or just do what I normally do which is:
[this space left intentionally blank because most reorgs are a complete waste of time]
A reader, June 13, 2009 - 4:49 am UTC
Hi Tom
I took a full export from oracle 7.
I want to import the entire data into 9i.
During import even I issue ignore=y option, import is terminated with the below message:
importing TSI510743's objects into TSI510743
"ALTER SESSION SET CURRENT_SCHEMA= "TSI510743""
IMP-00003: ORACLE error 1435 encountered
ORA-01435: user does not exist
IMP-00000: Import terminated unsuccessfully
Oracle should create the users during import, so why do I hit this error?
A reader, July 11, 2009 - 11:12 pm UTC
Hi Tom;
why does dmp file stored as a binary? since it is set of sql statements?
July 14, 2009 - 5:27 pm UTC
it is not a set of sql statements.
It is sql, it is data, it is statistics, it is a lot of things.
how to import just one schema from export file -password of schema not known
Anand, September 04, 2009 - 12:01 pm UTC
How can we import just one schema from export dump file (Oracle 10.2.0.4) when password of that schema/user is not known. (Password is not known, so can't create that user in target database with same password)
Trying to move just one schema of third-party Application from TEST to Production. It gives error ORA-01435: user does not exist.
Thanks a lot for your valuable answers.
Anand
September 04, 2009 - 3:57 pm UTC
create the user
imp fromuser=x touser=y
Tablespace Import and tables import !!!
INDRANIL DAS, September 19, 2009 - 11:26 am UTC
Hi Tom,
As per my undersatnding,while importing a very large table(300-500GB) from a source database to destination Database,
Import at tablespace level is better than table level import. What do you suggest even we are using expdp and impdp.
Thanks in advance
INDRANIL DAS
IBM
September 28, 2009 - 12:09 pm UTC
do you mean "transporting a tablespace" versus "importing a table", as you do not "import a tablespace", you can import all of the segments in a tablespace - or you can TRANSPORT a tablespace
if you gave me the task of copying/moving data of that size - it would likely be a tablespace transport. That way we can move the already loaded data and avoid reloading it again - just copy datafiles.
A reader, October 19, 2009 - 4:16 am UTC
Hi Tom;
When I check the size of the two schemas, the result is below:
select tablespace_name, sum(bytes)/1024/1024
from dba_segments
where owner in ( 'AIT_USER' , 'AIT_SCHEMA' )
and segment_type != 'INDEX'
group by tablespace_name;
TABLESPACE_NAME SUM(BYTES)/1024/1024
------------------------------ --------------------
AIT_DATA_AUTO 5902.125
USERS 4.25
However, when I take the export of these schemas, I notice that the size is much more bigger. What is the reason for this?
-rw-r----- 1 oracle dba 6814898176 Oct 19 12:36 aitprd19oct2009.dmp
par file
===============
userid=system/********
file=aitprd19oct2009.dmp
direct=y
buffer=349175808
indexes=N
consistent=Y
statistics=none
owner=(AIT_USER,AIT_SCHEMA)
October 22, 2009 - 4:06 pm UTC
is the data compressed in the database?
exp/imp of user Kills Grants given to the user
A reader, January 25, 2010 - 12:59 pm UTC
I do an exp of a user say "x"with default options
I drop the user "x"
I re-create the user "x".
I re-import the user in the same DB with defautl options.
I see that the grants of other users which had granted access to their objects to the user x are missing.
Why does exp not capture these grants ?
How to get these grants back.
Thanx
January 29, 2010 - 8:35 am UTC
it does not "kill them"
the grants are not part of the X schema, they were outside of the schema.
You would typically get these 'back' by having them in your source code control - you should know how to create your user.
The issue is - import runs as the user you are importing into, that user cannot grant to themselves these privs on others objects - the OTHERS have to grant them.
Expdp with Imp
A reader, June 03, 2010 - 8:36 am UTC
Can we import through standard import method ( "imp" method) a dump file generated using Data pump export utility( "expdp" method) , With the source and target database to be on the same Oracle version( 10g and above)?
June 08, 2010 - 11:47 am UTC
no.
imp issue
A reader, April 23, 2012 - 6:06 pm UTC
Tom:
I want to refresh a dev schema with production schema for my account scott.
The scott account in production has dba role but not in dev.
I created a script that drops all the objects in scott in prod db.
However, I am having an issue with exp/imp since because of the dba role.
IMP-00013: only a DBA can import a file exported by another DBA
IMP-00000: Import terminated unsuccessfully.
so i did create another user account *without* dba role to export the scott schema and then import it into the dev schema
create user test identified by test;
grant create session to test;
grant exp_full_database to test;
host exp test/test@proddb owner=scott file=scott.dmp log=scott-exp.log
But when i did the imp
imp test/test@devdb file=scott.dmp full=y
i got the same error.
Do you know why and how to workaround it?
April 24, 2012 - 7:40 am UTC
since only a DBA can export another user, your TEST user must be a DBA as well. you got the same issue because it is the same problem - you have a DBA
I cannot imagine why a production schema would have DBA, that sounds crazy, totally.
Please as your development DBA to facilitate this transfer - and start asking reasonable questions like "why the heck do we compromise security so greatly by doing something as crazy as having our application schema have DBA"
(as a developer, you could do some wicked things with that fact - you could have some real fun - even though you don't have real time access to production - you could GIVE yourself real time access to production just by knowing that DBA exists in production. This is really bad)
export
A reader, April 24, 2012 - 10:06 am UTC
Tom:
very long history and politics.
<<since only a DBA can export another user>>
I do not think so. Any user with EXPORT_FULL_DATABASE priv can export other users. you do not need DBA role.
Do you know why the workaround i used did not work? I created another user with no dba role and exported scott but when i tried to import it told me the same thing.
Is there a way to tell export to ignore the privileges when it creates the DDL in dump file.
April 24, 2012 - 11:20 am UTC
this is the reason:
IMP-00013: only a DBA can import a file exported by another DBA
basically - export full database is something a DBA has and the error message is just saying "you cannot do that". The user attempting to import doesn't have sufficient privileges.
they, like I just did, associated exp_full_database with DBA ....
ops$tkyte%ORA11GR2> !imp a/a full=y file=b.dmp
Import: Release 11.2.0.3.0 - Production on Tue Apr 24 12:19:55 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
IMP-00013: only a DBA can import a file exported by another DBA
IMP-00000: Import terminated unsuccessfully
ops$tkyte%ORA11GR2> grant imp_full_database to a;
Grant succeeded.
ops$tkyte%ORA11GR2> !imp a/a full=y file=b.dmp
Import: Release 11.2.0.3.0 - Production on Tue Apr 24 12:19:55 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
. importing A's objects into A
. importing B's objects into B
. . importing table "T" 50 rows imported
Import terminated successfully without warnings.
ops$tkyte%ORA11GR2>
the importer would need DBA (which has imp_full_database) or IMP_FULL_DATABASE to succeed.