This discussion is archived
11 Replies Latest reply: Apr 3, 2013 1:33 AM by Richard Harrison . RSS

export IOT tables

999373 Newbie
Currently Being Moderated
Hello Experts,

i am migrating database from one server to another server. i am using export and import. while exporting the database
somehow, oracle is skipping few tables, specially IOT tables under a schema called SDE. i am trying to do expdp/exp
as schema owner, as well as sys user.

i have tried to export a single IOT table and i am getting table or view doesn't exist.

i have verfied,deferred_segment_creation and it was set to false. all objects got allocated extents. still i did see the error.

could you please advice how to export IOT tables.
also i have a another question,
do i need to follow any special procedure to export spatial/srdi application schema?

here is the error message i am getting :

Export terminated successfully with warnings.
PREPROD sidspre4db3: /home/oracle # exp sde/pwd file=/dbbackup/sde_exp/sde_tab_s11idx.dmp log=sde_s11idx.log tables="SDE.S11_IDX$"

Export: Release 11.2.0.1.0 - Production on Tue Apr 2 13:27:22 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Tes
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
EXP-00011: SDE.S11_IDX$ does not exist
Export terminated successfully with warnings.
  • 1. Re: export IOT tables
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Original export is desupported in 11gr2 - http://docs.oracle.com/cd/E11882_01/server.112/e22490/original_export.htm#BABBHCIE

    Have you tried with expdp ? http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#i1007466

    HTH
    Srini
  • 2. Re: export IOT tables
    TSharma-Oracle Guru
    Currently Being Moderated
    Export terminated successfully with warnings.
    PREPROD sidspre4db3: /home/oracle # exp sde/pwd file=/dbbackup/sde_exp/sde_tab_s11idx.dmp log=sde_s11idx.log tables="SDE.S11_IDX$" >

    Try 2 things:

    1) Try without schema extension.

    PREPROD sidspre4db3: /home/oracle # exp sde/pwd file=/dbbackup/sde_exp/sde_tab_s11idx.dmp log=sde_s11idx.log tables="S11_IDX$"

    2) Put a slash(\) ahead of $ sign

    PREPROD sidspre4db3: /home/oracle # exp sde/pwd file=/dbbackup/sde_exp/sde_tab_s11idx.dmp log=sde_s11idx.log tables="S11_IDX\$"
  • 3. Re: export IOT tables
    999373 Newbie
    Currently Being Moderated
    Srini,

    Thanks for help. yes, i did export with expdp, still i have same issue.

    PREPROD sidspre4db3: /dbbackup/exp_backup # expdp sde/PWD directory=DPEXP DUMPFILE=sde_s11idx.dmp logfile=exp_sde_s11idx.log tables="SDE.S11_IDX$"

    Export: Release 11.2.0.1.0 - Production on Tue Apr 2 14:08:21 2013

    Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
    Data Mining and Real Application Testing options
    Starting "SDE"."SYS_EXPORT_TABLE_01": sde/******** directory=DPEXP DUMPFILE=sde_s11idx.dmp logfile=exp_sde_s11idx.log tables=SDE.S11_IDX$
    Estimate in progress using BLOCKS method...
    Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
    Total estimation using BLOCKS method: 0 KB
    ORA-39166: Object SDE.S11_IDX$ was not found.
    ORA-31655: no data or metadata objects selected for job
    Job "SDE"."SYS_EXPORT_TABLE_01" completed with 2 error(s) at 14:08:28

    #! Kumar
  • 4. Re: export IOT tables
    999373 Newbie
    Currently Being Moderated
    Sharma,

    i have tried both way's. it got failed.

    PREPROD sidspre4db3: /dbbackup/exp_backup # exp sde/pwd file=/dbbackup/sde_exp/sde_tab_s11idx.dmp log=sde_s11idx.log tables="S11_IDX\$"

    Export: Release 11.2.0.1.0 - Production on Tue Apr 2 14:15:19 2013

    Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.


    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
    Data Mining and Real Application Tes
    Export done in US7ASCII character set and AL16UTF16 NCHAR character set
    server uses AL32UTF8 character set (possible charset conversion)

    About to export specified tables via Conventional Path ...
    EXP-00011: SDE.S11_IDX$ does not exist
    Export terminated successfully with warnings.
    PREPROD sidspre4db3: /dbbackup/exp_backup #
  • 5. Re: export IOT tables
    Dean Gagne Expert
    Currently Being Moderated
    I would try without the quotes and since we don't know what operating system you are using, it would be best to put the table name in a parameter file:

    tab_list.par

    tables=S11_IDX$

    Then see if Data Pump can find it. Are you sure the table is there?

    Dean
  • 6. Re: export IOT tables
    999373 Newbie
    Currently Being Moderated
    Dean,

    thank you so much help. i have done every possible option. still i got same issue.

    PREPROD sidspre4db3: /home/oracle/dbascripts/psunkara/sql # expdp sde/pwd directory=DPEXP DUMPFILE=sde_s11idx.dmp logfile=exp_sde_s11idx.log tables=SDE.S11_IDX$

    Export: Release 11.2.0.1.0 - Production on Tue Apr 2 14:39:22 2013

    Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
    Data Mining and Real Application Testing options
    Starting "SDE"."SYS_EXPORT_TABLE_01": sde/******** directory=DPEXP DUMPFILE=sde_s11idx.dmp logfile=exp_sde_s11idx.log tables=SDE.S11_IDX$
    Estimate in progress using BLOCKS method...
    Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
    Total estimation using BLOCKS method: 0 KB
    ORA-39166: Object SDE.S11_IDX$ was not found.
    ORA-31655: no data or metadata objects selected for job
  • 7. Re: export IOT tables
    999373 Newbie
    Currently Being Moderated
    Dean,

    yes, table is there SQL> desc sde.s11_idx$
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    GX NOT NULL NUMBER(38)
    GY NOT NULL NUMBER(38)
    MINX NOT NULL NUMBER(38)
    MINY NOT NULL NUMBER(38)
    MAXX NOT NULL NUMBER(38)
    MAXY NOT NULL NUMBER(38)
    SP_ID NOT NULL ROWID


    SQL> select count(*) from sde.s11_idx$
    2 ;

    COUNT(*)
    ----------
    54181
  • 8. Re: export IOT tables
    999373 Newbie
    Currently Being Moderated
    Dean,

    i ran the quick test. i have create a new IOT table and ran the export. i was able to run the export successfully. but i was not able to run the export of S11_IDX$ table. i am not sure the cause for it

    SQL> CREATE TABLE MY_IOT_TEST (id INTEGER PRIMARY KEY, value VARCHAR2(50))
    ORGANIZATION INDEX;14:44:26 2

    Table created.

    SQL> EXIT
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
    Data Mining and Real Application Testing options
    PREPROD sidspre4db3: /dbbackup/exp_backup/secureol # exp sde/pwd file=/dbbackup/sde_exp/my_iot_tab.dmp log=/dbbackup/sde_exp/myiot_tab_exp tables=MY_IOT_TEST

    Export: Release 11.2.0.3.0 - Production on Tue Apr 2 14:45:44 2013

    Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.


    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
    Data Mining and Real Application Tes
    Export done in US7ASCII character set and AL16UTF16 NCHAR character set
    server uses AL32UTF8 character set (possible charset conversion)

    About to export specified tables via Conventional Path ...
    . . exporting table MY_IOT_TEST 0 rows exported
    Export terminated successfully without warnings.
    PREPROD sidspre4db1: /dbbackup/exp_backup/secureol #
  • 9. Re: export IOT tables
    TSharma-Oracle Guru
    Currently Being Moderated
    Does this object created when you created any Text Index? OR Is this object specifically created by you?
    Check this metalink note, it might be related:

    DR#SS_IDX Tables Can't Be Exported [ID 1324656.1]
  • 10. Re: export IOT tables
    999373 Newbie
    Currently Being Moderated
    Sharma,

    i don't think these are context external table. is IOT can be called as "context internal table" ?
  • 11. Re: export IOT tables
    Richard Harrison . Expert
    Currently Being Moderated
    Hi,
    Is it because the table was created with a non upper case name?

    create table "test"

    creates a table which is then stored in the dictionary in lower case. This may mean it can;t then be found by the utilities. Check the value in DBA_TABLES for that name and see what case it is in. You can then probably get it to work by putting the lower case version of the name in double quotes in your dataump config file?

    Cheers,
    Harry

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points