Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.


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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

618 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