Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Posts
    106

    Unanswered: EXP error with tables having partitions and subpartitions

    Hi,

    We use oracle 9.2.0.4 on Windows xp professional for oracle server and same for oracle client.

    I have some tables which uses partitions and sub partitions,I am able to use exp utility to export the data from the database which has this schema along with the tables.

    But when I try to import using imp utility from the same .dmp file created earlier,I get this error for only those tables which have partitions and subpartitions.

    ORA-14020: this physical attribute may not be specified for a table partition

    It shows some CREATE TABLE statment with al the columns.We cannot check whch attribut is wrong in the create table statment since this create table statment was written by exp utility when exp dump was taken.

    So how do we solve this problem.Is there some bug in exp utlity ,some patch need to be applied or some parameters need to be chnaged while using exp or imp utility.

    Pl. respond as this is urgent.
    Sachi

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Question

    Try executing the CREATE statement directly in SQL*Plus, if it works, import with ignore=Y option.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jan 2003
    Posts
    106

    no

    the create statment is generated by the exp utility which cannot be directly executed in SQL Plus because it has many other things.

    We noticed that the problem occurs only if the table has subpartition,it is able to import partitions but not subpartitions.
    Sachi

  4. #4
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    > the create statment is generated by the exp utility which cannot be directly executed in SQL Plus because it has many other things.

    It would still be worth extracting the offending CREATE statement from the dumpfile and seeing exactly what it is trying to do, if only to narrow down the problem.

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    run this:
    imp user/password file=mydump.dmp indexfile=my_script.sql full=y

    then open "my_script.sql" and search for that exact create table statement.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Or from the Windows command line you may be able to use findstr to extract the line from the dumpfile (in Unix you could use grep). SQL is saved in plain text even though the data part of the file is binary.

Posting Permissions

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