Exporting the Tablespace in Oracle8i

Posted on 2002-04-24
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
  • 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 50 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

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!
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

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.

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
levels for reporting 5 63
Schema creation in Oracle12c 6 38
Fastest way to replace data in Oracle 5 50
Oracle Listener Not Starting 11 18
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…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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 setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

932 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

11 Experts available now in Live!

Get 1:1 Help Now