Exporting the Tablespace in Oracle8i

Posted on 2002-04-24
Medium Priority
Last Modified: 2008-03-06
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.
Question by:myexpert2002
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
  • 3
  • 2
  • 2
  • +2

Expert Comment

ID: 6965784
Command line should read

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


Accepted Solution

p_yaroslav earned 200 total points
ID: 6966837

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

 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.

Author Comment

ID: 6967842
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Expert Comment

ID: 6967847

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!

Author Comment

ID: 6968093
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,

Expert Comment

ID: 6968098

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


Expert Comment

ID: 6969136
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.

Expert Comment

ID: 6978448
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.
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.

*****  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
POINTS FOR EXPERTS awaiting comments are listed in the link below
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.
Moderator @ Experts Exchange

Expert Comment

ID: 7096853
Finalized by Moondancer - EE Moderator

Featured Post

Industry Leaders: 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!

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 explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

752 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