[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1200
  • Last Modified:

Exporting the Tablespace in Oracle8i

hi all,

    when i am exporting the tablespace from one database to another with the following steps

a)alter tablespace tsp1 read only;

b) exp username/pwd@instance TRANSPORT_TABLESPACE=y TABLESPACES=tsp1 FILE=export.dmp .

I am getting this error

EXP-00044: must be connected 'AS SYSDBA' to do Point-in-time Recovery or Transportable Tablespace import
--

I have given the dba persmission to my user also.

Thanks in Advance.
0
myexpert2002
Asked:
myexpert2002
  • 3
  • 2
  • 2
  • +2
1 Solution
 
WHITER99Commented:
Command line should read

exp 'username/pwd@instance AS SYSDBA' TRANSPORT_TABLESPACE=y TABLESPACES=tsp1 FILE=export.dmp

0
 
p_yaroslavCommented:
Hi!

WHITER99, you may be rather post comment but not answer.

myexpert2002,
 The problem is :
If you want export tablespaces for transport to another database you must export under SYSDBA. I.e. username must have SYSDBA privilege for this.
You may export under user SYS:
exp sys/passwd@instance as sysdba ......

or grant SYSDBA role to another user
SQL>connect sys/passwd@instance as sysdba;
SQL>grant sysdba to NEWUSER;

and export tablespaces
exp newuser/passwd@instance as sysdba ...........

Hope it helps.
Yaroslav.
0
 
myexpert2002Author Commented:
hi whiter99,


       You r some extent right, but i accept Yaroslav comment as it is working fine for me.

I have Exported the tablespace successfully, when i am importing the same,

imp user1/pwd1@instance1 fromuser=user2/pwd2@instance2 TRANSPORT_TABLESPACE=y DATAFILES='/d01/t1.dbf' TABLESPACES=tsp1 FILE=export.dmp


I am facing these errors:

import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set
IMP-00003: ORACLE error 6550 encountered
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_PLUGTS.NEWTABLESPACE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
IMP-00000: Import terminated unsuccessfully


Thanks in Advance
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
p_yaroslavCommented:
Hi!


If you are importing the tablespace as a user other than 'SYS', create a public synonym for SYS.DBMS_PLUGTS as DBMS_PLUGTS else you would get the errors similar to the following :

import done in US7ASCII character set and US7ASCII NCHAR character set
IMP-00003: ORACLE error 6550 encountered
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_PLUGTS.NEWTABLESPACE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
IMP-00000: Import terminated unsuccessfully

The user should also have dba privilege.


Best regards!
Yaroslav.
0
 
myexpert2002Author Commented:
hi Yaroslav,

    I have created the synonym as u mentioned but i am facing the following error.

import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set
. importing SYS's objects into PRAMOD
IMP-00017: following statement failed with ORACLE error 1565:
 "BEGIN   sys.dbms_plugts.beginImpTablespace('DEMO_TS',11,'SYS',1,0,8192,1,10"
 "173001,1,505,5,5,0,50,1,0,0,4226617559,1,0,1753663,NULL,0,0,NULL,NULL); END"
 ";"
IMP-00003: ORACLE error 1565 encountered
ORA-01565: error in identifying file 'C:/DEMO1.dbf'
ORA-06512: at "SYS.DBMS_PLUGTS", line 1347
ORA-06512: at line 1
IMP-00000: Import terminated unsuccessfully


my datafile is in c:\.

Thanks in Advance,
0
 
p_yaroslavCommented:
Hi,

It looks like the import is failing because the file specified does not exist ( 'C:/DEMO1.dbf' ). Does this file actually exist?

Yaroslav.
0
 
BajwaCommented:
Well With transportable tablesspaces what you do is

1.  Make sure that both databases are exact (if 8i).
2.  run the export command as sysdba for tablespace metadata.

3.  ftp/copy the datafile/datafiles to the database to be imported.

4.  Import the metadata using import as above.  You are getting error because you have not copied the data file., or have copied in any other location.
0
 
MoondancerCommented:
You have nine open questions here.  ADMINISTRATION WILL BE CONTACTING YOU SHORTLY.  Moderators Computer101 or Netminder will return to finalize these if they are still open in 14 days.  Experts, please post closing recommendations before that time.

Below are your open questions as of today.  Questions which have been inactive for 21 days or longer are considered to be abandoned and for those, your options are:
1. Accept a Comment As Answer (use the button next to the Expert's name).
2. Close the question if the information was not useful to you, but may help others. You must tell the participants why you wish to do this, and allow for Expert response.  This choice will include a refund to you, and will move this question to our PAQ (Previously Asked Question) database.  If you found information outside this question thread, please add it.
3. Ask Community Support to help split points between participating experts, or just comment here with details and we'll respond with the process.
4. Delete the question (if it has no potential value for others).
   --> Post comments for expert of your intention to delete and why
   --> YOU CANNOT DELETE A QUESTION with comments; special handling by a Moderator is required.

For special handling needs, please post a zero point question in the link below and include the URL (question QID/link) that it regards with details.
http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
 
Please click this link for Help Desk, Guidelines/Member Agreement and the Question/Answer process.  http://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp

Click you Member Profile to view your question history and please keep them updated. If you are a KnowledgePro user, use the Power Search option to find them.  

Questions which are LOCKED with a Proposed Answer but do not help you, should be rejected with comments added.  When you grade the question less than an A, please comment as to why.  This helps all involved, as well as others who may access this item in the future.  PLEASE DO NOT AWARD POINTS TO ME.

To view your open questions, please click the following link(s) and keep them all current with updates.
http://www.experts-exchange.com/questions/Q.20257077.html
http://www.experts-exchange.com/questions/Q.20265022.html
http://www.experts-exchange.com/questions/Q.20266286.html
http://www.experts-exchange.com/questions/Q.20281036.html
http://www.experts-exchange.com/questions/Q.20292581.html
http://www.experts-exchange.com/questions/Q.20282355.html
http://www.experts-exchange.com/questions/Q.20284375.html
http://www.experts-exchange.com/questions/Q.20285908.html
http://www.experts-exchange.com/questions/Q.20293112.html



*****  E X P E R T S    P L E A S E  ******  Leave your closing recommendations.
If you are interested in the cleanup effort, please click this link
http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=commspt&qid=20274643 
POINTS FOR EXPERTS awaiting comments are listed in the link below
http://www.experts-exchange.com/commspt/Q.20277028.html
 
Moderators will finalize this question if in @14 days Asker has not responded.  This will be moved to the PAQ (Previously Asked Questions) at zero points, deleted or awarded.
 
Thanks everyone.
Moondancer
Moderator @ Experts Exchange
0
 
MoondancerCommented:
Finalized by Moondancer - EE Moderator
0

Featured Post

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!

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now