4 Replies Latest reply: Jan 4, 2013 3:05 AM by yasirdba RSS

    EXP-00056: ORACLE error 1555 encountered

    yasirdba
      Window server 2003 32bit
      Enterprise Edition Release 9.2.0.1.0 - Production
      ****************************************************************
      I have exported schema dump successively regularly, one day we run the online exam
      Student post large data(text) in exam.
      and Database stop responding for specific tool in the application.
      after this
      I exported my schema successfully, just one table could not exported. this is the table where student data stored.
      i checked the log file and found the following error


      **EXP-00056: ORACLE error 1555 encountered**
      **ORA-01555: snapshot too old: rollback segment number with name "" too small**
      **ORA-22924: snapshot too old**

      then i try to export this single table using following script
      exp system/pass@orcl205 FIlE=SAM_ITEMGRADING_T.dmp log=SAM_ITEMGRADING_T.log TABLES=SAM_ITEMGRADING_T

      Found this error
      Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
      With the Partitioning, OLAP and Oracle Data Mining options
      JServer Release 9.2.0.1.0 - Production
      Export done in WE8MSWIN1252 character set and UTF8 NCHAR character set
      server uses AL32UTF8 character set (possible charset conversion)

      About to export specified tables via Conventional Path ...
      . . exporting table SAM_ITEMGRADING_T
      EXP-00056: ORACLE error 1555 encountered
      ORA-01555: snapshot too old: rollback segment number with name "" too small
      ORA-22924: snapshot too old
      Export terminated successfully with warnings.



      Following is the script of the table
      CREATE TABLE "SLATEEXAM"."SAM_ITEMGRADING_T"
      (
      "ITEMGRADINGID" NUMBER(19,0) NOT NULL ENABLE,
      "ASSESSMENTGRADINGID" NUMBER(19,0) NOT NULL ENABLE,
      "PUBLISHEDITEMID" NUMBER(19,0) NOT NULL ENABLE,
      "PUBLISHEDITEMTEXTID" NUMBER(19,0) NOT NULL ENABLE,
      "AGENTID" VARCHAR2(255 CHAR) NOT NULL ENABLE,
      "SUBMITTEDDATE" TIMESTAMP (6),
      "PUBLISHEDANSWERID" NUMBER(19,0),
      "RATIONALE" VARCHAR2(4000 CHAR),
      "AUTOSCORE" FLOAT(126),
      "OVERRIDESCORE" FLOAT(126),
      "COMMENTS" VARCHAR2(4000 CHAR),
      "GRADEDBY" VARCHAR2(255 CHAR),
      "GRADEDDATE" TIMESTAMP (6),
      "REVIEW" NUMBER(1,0),
      "ATTEMPTSREMAINING" NUMBER(10,0),
      "LASTDURATION" VARCHAR2(36 CHAR),
      "ANSWERTEXT" CLOB,
      CONSTRAINT "FKB68E675667B430D5" FOREIGN KEY ("ASSESSMENTGRADINGID") REFERENCES "SLATEEXAM"."SAM_ASSESSMENTGRADING_T" ("ASSESSMENTGRADINGID") ENABLE
      )
      SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
      (
      INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
      )
      TABLESPACE "NUTES" LOB
      (
      "ANSWERTEXT"
      )
      STORE AS BASICFILE
      (
      TABLESPACE "NUTES" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10 NOCACHE LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
      ) ;
      CREATE UNIQUE INDEX "SLATEEXAM"."SYS_IL0000093338C00017$$" ON "SLATEEXAM"."SAM_ITEMGRADING_T"
      (
      PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "NUTES" PARALLEL (DEGREE 0 INSTANCES 0) ;



      the total no. of rows in table are 549576

      Can you plz help me how to export this table.


      also suggest me what changes required so that our database does not stop responding while taking exam.
        • 1. Re: EXP-00056: ORACLE error 1555 encountered
          damorgan
          Two thoughts.

          1. Anything involving version 9.2.0.1 is a total waste of time unless the point of the exercise is to move to a fully supported version. There is no excuse for having not patched software in more than a decade and no excuse for working with a desupported version.

          2. ORA-01555 is phenomenally well documented. Google this exception and "Tom Kyte" and read Tom's advice.

          I'd give you the answer to the problem here but I think you really need to read what Tom wrote and then format your hard disk and install a real operating system (Linux) and a real database (version 11.2.0.3). A quick fix won't encourage you to do either.
          • 2. Huge text problem in db
            yasirdba
            hello damorgan
            we are using
            oracle 9i
            window svr 2003


            we are taking online exam, when student post huge data(text) in the text box then they could not submit the paper.
            we found that specific table(where data stored) does not respond.
            when student post small data(text), there is no problem faced.

            there is enough space in table space.
            we also increase the undo table space
            also increase the temp table space.

            but could not resolve the problem.

            only the problem occurred with specific table, column data type of this table is " CLOB.
            while problem occurred we can perform other task on database(mean to say database still working).

            i am sure that we are not using more than 60000 character.

            can u please suggest me some tips.
            • 3. Re: Huge text problem in db
              Srini Chavali-Oracle
              One possible cause could be LOB corruption - pl see if this MOS Doc can help

              ORA-01555 And Other Errors while Exporting Table With LOBs, How To Detect Lob Corruption. [ID 452341.1]

              HTH
              Srini
              • 4. Re: Huge text problem in db
                yasirdba
                i have resolved the problem of export. it is because of one row is corrupted. i have deleted that row. now export is working.

                i have export this oracle dump to 10g 32 bit and oracle 11g 64 bit.
                there is no problem of large text in exam.

                i have also exported the same dump to other server oracle 9i.
                But the same problem occurred, i.e we can can not post the large data in table.
                one thing more before submitting the paper student can save the data successfully.
                problem only occurred when the click the submit button.


                if we say it is problem of application, then why it is running successfully with oracle 10g & 11g.

                i want to know is there any limitation in oracle of large text 9i.