unable to export schema or owner scott using export command
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: unable to export schema or owner scott using export command

Hybrid View

  1. #1
    Join Date
    Jul 2010
    Posts
    2

    unable to export schema or owner scott using export command

    Hello Friends,

    Few months back I transfered my database from 1 machine to other machine.

    I have oracle 11g.

    I did following.

    1. Created database with name mytestdb.

    2. exported schema=scott from old database using export command which gave me scott.dmp

    3. imported scott.dmp using import command to database mytestdb.

    4. Edited database tables for new needs.

    Both new and old databse is same oracle 11g.

    I am able to connect to database using oracle sql developer using following

    user: scott
    password: tiger
    host: 192.168.1.20
    port: 1521
    sid: mytestdb

    It shows all tables and I am able connect it and manipulate all tables form my java script. So it should be fine..

    Now problem is following

    Following is command and its output from command prompt.



    C:\>exp scott/tiger file=c:/latest.dmp full=y;

    Export: Release 11.1.0.6.0 - Production on Fri Jul 9 17:31:20 2010

    Copyright (c) 1982, 2007, Oracle. All rights reserved.


    Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Produc
    tion
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    EXP-00023: must be a DBA to do Full Database or Tablespace export
    (2)U(sers), or (3)T(ables): (2)U >

    Export grants (yes/no): yes >

    Export table data (yes/no): yes >

    Compress extents (yes/no): yes >

    Export done in WE8MSWIN1252 character set and UTF8 NCHAR character set
    server uses AL32UTF8 character set (possible charset conversion)
    . exporting pre-schema procedural objects and actions
    . exporting foreign function library names for user SCOTT
    . exporting PUBLIC type synonyms
    . exporting private type synonyms
    . exporting object type definitions for user SCOTT
    About to export SCOTT's objects ...
    . exporting database links
    . exporting sequence numbers
    . exporting cluster definitions
    . about to export SCOTT's tables via Conventional Path ...
    . . exporting table BONUS 0 rows exported
    EXP-00091: Exporting questionable statistics.
    . . exporting table DEPT 4 rows exported
    EXP-00091: Exporting questionable statistics.
    EXP-00091: Exporting questionable statistics.
    . . exporting table EMP 14 rows exported
    EXP-00091: Exporting questionable statistics.
    EXP-00091: Exporting questionable statistics.
    . . exporting table SALGRADE 5 rows exported
    EXP-00091: Exporting questionable statistics.
    . exporting synonyms
    . exporting views
    . exporting stored procedures
    . exporting operators
    . exporting referential integrity constraints
    . exporting triggers
    . exporting indextypes
    . exporting bitmap, functional and extensible indexes
    . exporting posttables actions
    . exporting materialized views
    . exporting snapshot logs
    . exporting job queues
    . exporting refresh groups and children
    . exporting dimensions
    . exporting post-schema procedural objects and actions
    . exporting statistics
    Export terminated successfully with warnings.

    C:\>


    In above, it is obvious that export terminated successfully.

    But my problem is that in my scott user there are no such tables like BONUS,EMP,DEPT or SALGRADE because I have deleted all of them using oracle sql developer.

    Currently I have tables like country, city, channel, client, client_data etc..
    But none of these table exported?

    Also using SQL PLUS I am not able to run query for table channel.. it says 'table does not exists'.
    But using sql oracle developer and my java application I am able to access these tables with user scott.

    Can any one help me how to export these tables?

    Please let me know if you need more information from me on the issue.

    Thanks.

    Amar4kintu

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    Combination of not using commands and options correctly. Does your scott user have dba granted to him? If not, then full what? Why are you using old export and import in 11g? No one here has any idea what your export session's connection is pointing to.

    Currently I have tables like country, city, channel, client, client_data etc..
    But none of these table exported?
    Because obviously they do not exist in the scott schema you connected to in the export session.

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by amar4kintu View Post
    ... using SQL PLUS I am not able to run query for table channel.. it says 'table does not exists'.
    But using sql oracle developer and my java application I am able to access these tables with user scott.
    Either you are pointing to two different databases or you are using different accounts - with different sets of privileges - when connecting via sqlplus and via sqldeveloper.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    Jul 2010
    Posts
    2
    Hello Stecal,

    Thanks for reply.

    yes. my scott user have dba granted to him.. I did not understand exactly about session you said .. but I was able to export using following command

    C:\>exp USERID=scott/tiger@mytestdb file=c:/latest.dmp full=y;

    I think problem was because of 2 databases I have 1 is mytestdb and other is db1 and when I tried with previous command it might be taking scott user of db1 and not of mytestdb.. and I think by session you mean to say with which database I was connected. may be I am wrong.

    Regarding old export .. it seems to work fine using 11g also. yes you are right that it is old version but.. I used what I find first during my google search.. though I am learning things during my project progress.

    Again thanks for your reply. May be I did not ask question as much perfectly as it should be.

    But I found the solution using @serviceid in export command.

    Thanks.

    Amar4kintu

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width