Solved

Exporting the Tablespace in Oracle8i

Posted on 2002-04-24
9
1,189 Views
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.
0
Comment
Question by:myexpert2002
  • 3
  • 2
  • 2
  • +2
9 Comments
 

Expert Comment

by:WHITER99
ID: 6965784
Command line should read

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

0
 
LVL 3

Accepted Solution

by:
p_yaroslav earned 50 total points
ID: 6966837
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
 

Author Comment

by:myexpert2002
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
0
 
LVL 3

Expert Comment

by:p_yaroslav
ID: 6967847
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
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

by:myexpert2002
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,
0
 
LVL 3

Expert Comment

by:p_yaroslav
ID: 6968098
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
 
LVL 5

Expert Comment

by:Bajwa
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.
0
 
LVL 1

Expert Comment

by:Moondancer
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.
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
 
LVL 1

Expert Comment

by:Moondancer
ID: 7096853
Finalized by Moondancer - EE Moderator
0

Featured Post

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.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

708 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

12 Experts available now in Live!

Get 1:1 Help Now