Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2011
    Posts
    18

    Unanswered: Query from DBA_TAB_STATISTICS or DBA_IND_STATISTICS results in ORA-20001 error

    Hi, I'm getting the following error for some reasons unknown to me.
    Code:
    ORA-20001:  BIN$3EIUALEIUCZ7GQD0C9M7IIM==$0  is an invalid identifier
    ORA-06512:  at "SYS.DBMS_STATS", line 2242
    ORA-06512:  at "SYS.DBMS_STATS", line 2258
    ORA-06512:  at "SYS.DBMS_STATS", line 30041
    ORA-06512:  at line 1
    From the following query.
    SELECT OBJ.OBJECT_NAME, OBJ.OBJECT_TYPE,
    CASE WHEN OBJ.OBJECT_TYPE = 'TABLE' THEN TSTAT.STALE_STATS
    WHEN OBJ.OBJECT_TYPE = 'INDEX' THEN ISTAT.STALE_STATS
    ELSE 'NO STAT'
    END STAT_STATS
    FROM DBA_OBJECTS OBJ
    LEFT JOIN DBA_TAB_STATISTICS TSTAT ON TSTAT.TABLE_NAME = OBJ.OBJECT_NAME AND TSTAT.OWNER = OBJ.OWNER
    LEFT JOIN DBA_IND_STATISTICS ISTAT ON ISTAT.TABLE_NAME = OBJ.OBJECT_NAME AND ISTAT.OWNER = OBJ.OWNER
    WHERE OBJ.OBJECT_TYPE IN ('TABLE', 'INDEX');
    Does anyone know what is the cause? Thanks.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    empty the RECYCLEBIN
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2011
    Posts
    18
    Quote Originally Posted by anacedent View Post
    empty the RECYCLEBIN
    Can you let me know how is this related to the recycle bin and if there is a way to handle this without clearing the recycle bin? I don't own the schema and only have a read only account so can't do anything that removes information. Thanks.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    the object name indicates it resides in the RECYCLEBIN.

    The bottom line is a READ ONLY account has to live with what exists

    somebody with requisite privilege will have to intervene.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Oct 2011
    Posts
    18
    Quote Originally Posted by anacedent View Post
    the object name indicates it resides in the RECYCLEBIN.

    The bottom line is a READ ONLY account has to live with what exists

    somebody with requisite privilege will have to intervene.
    Okay I just read this Oracle guide:
    7.4 Oracle Flashback Drop: Undo a DROP TABLE Operation

    But still more questions ...
    1) I created a new schema on a test database, created a new table, and then dropped this table. But the following are not returning the results I'm expecting. I was expecting a row in either cases same as what's shown 7.4.5 Viewing and Querying Objects in the Recycle Bin of the guide.

    This one has no rows returned.
    Code:
    SELECT  *
    FROM    RECYCLEBIN;
    This one is only returning the message "table COUNTRY dropped."
    Code:
    SHOW    RECYCLEBIN;
    2) The guide explains that the BIN$$globalUID$version name appears only in the RECYCLEBIN table. So why my query is giving an indication that it has found this type of names?

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    AFAIK, "FLASHBACK" is not enabled by default.

    post results from SQL below

    SELECT * FROM V$VERSION;

    15 Using Flashback Features
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Oct 2011
    Posts
    18
    Quote Originally Posted by anacedent View Post
    AFAIK, "FLASHBACK" is not enabled by default.

    post results from SQL below

    SELECT * FROM V$VERSION;

    15 Using Flashback Features
    Okay this is the output from the query.
    Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
    PL/SQL Release 11.2.0.2.0 - Production
    "CORE 11.2.0.2.0 Production"
    TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
    NLSRTL Version 11.2.0.2.0 - Production

    But isn't recycle bin enabled by default? Per the article I read it says "The recycle bin is enabled by default." Digging into the Flashback feature you posted it appears to enable this require a bit more reading than I can do at the moment which is really trying to let me see whether there will be a BIN$$globalUID$version entry in the table I'm querying from. I've actually setup a test database to play around with to try to replicate the problem but seems this may be a lot more work than I was anticipating.

    Can you let me know why my query is giving an indication that it has found names like BIN$$globalUID$version? And how I can opt to ignore these entries?

  8. #8
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    You might want to read this (particularly the section "Viewing and Querying Objects in the Recycle Bin"):

    Using Flashback Drop and Managing the Recycle Bin
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  9. #9
    Join Date
    Oct 2011
    Posts
    18
    Quote Originally Posted by cis_groupie View Post
    You might want to read this (particularly the section "Viewing and Querying Objects in the Recycle Bin"):

    Using Flashback Drop and Managing the Recycle Bin
    Hi there thanks for the reference. Something is wrong with my setup as my recyclebin is enabled, but whenever I drop a table, it doesn't place it in the recycle bin.

    My understanding from that link is that if I drop a table, the table is still there but just renamed, and an entry is added to the RECYCLEBIN table. Therefore any other tables such as DBA_OBJECTS or DBA_TAB_STATISTICS would still have this entry in them except it's just been renamed to the BIN$xyz$version format. This is why when I query from them it complains. Is this correct?

    Here's what I've done.
    Check to make sure recycle bin is on
    Code:
    show parameter recyclebin;
    NAME                                               TYPE        VALUE                                                                                                
    -------------------------------------------------- ----------- ---------------------------------------------------------------------------------------------------- 
    recyclebin                                         string      on

    Create table
    Code:
    --------------------------------------------------------
    --  DDL for Table COUNTRY
    --------------------------------------------------------
    
      CREATE TABLE "CHRIS"."COUNTRY" 
       (	"COUNTRY_ID" VARCHAR2(20 BYTE), 
    	"CONTINENT_ID" VARCHAR2(20 BYTE), 
    	"COUNTRY_NAME" VARCHAR2(20 BYTE)
       ) 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 "SYSTEM" ;
    --------------------------------------------------------
    --  DDL for Index COUNTRY_PK
    --------------------------------------------------------
    
      CREATE UNIQUE INDEX "CHRIS"."COUNTRY_PK" ON "CHRIS"."COUNTRY" ("COUNTRY_ID") 
      PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
      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 "SYSTEM" ;
    --------------------------------------------------------
    --  Constraints for Table COUNTRY
    --------------------------------------------------------
    
      ALTER TABLE "CHRIS"."COUNTRY" ADD CONSTRAINT "COUNTRY_PK" PRIMARY KEY ("COUNTRY_ID")
      USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
      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 "SYSTEM"  ENABLE;
      ALTER TABLE "CHRIS"."COUNTRY" MODIFY ("COUNTRY_NAME" NOT NULL ENABLE);
      ALTER TABLE "CHRIS"."COUNTRY" MODIFY ("CONTINENT_ID" NOT NULL ENABLE);
      ALTER TABLE "CHRIS"."COUNTRY" MODIFY ("COUNTRY_ID" NOT NULL ENABLE);

    Check recycle bin state before drop
    Code:
    SELECT  *
    FROM    RECYCLEBIN;
    ... empty

    Drop table
    Code:
    DROP TABLE COUNTRY;

    Check recycle bin state after drop
    ... empty still!

    What has gone wrong?!

  10. #10
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    What's in DBA_RECYCLEBIN?
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  11. #11
    Join Date
    Oct 2011
    Posts
    18
    Quote Originally Posted by cis_groupie View Post
    What's in DBA_RECYCLEBIN?
    Nothing. What else should I be checking?

  12. #12
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    If nothing is in the recyclebin then it means that nothing is being put there when you drop objects. Which means that either you are dropping objects using 'purge' (so objects bypass the recyclebin), or that the recyclebin is not enabled, or that you have a problem with your database.

    Have you tried turning on the recyclebin for your session & then testing to see what happens? (ALTER SESSION SET recyclebin = ON
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  13. #13
    Join Date
    Oct 2011
    Posts
    18
    Quote Originally Posted by cis_groupie View Post
    If nothing is in the recyclebin then it means that nothing is being put there when you drop objects. Which means that either you are dropping objects using 'purge' (so objects bypass the recyclebin), or that the recyclebin is not enabled, or that you have a problem with your database.

    Have you tried turning on the recyclebin for your session & then testing to see what happens? (ALTER SESSION SET recyclebin = ON
    Um... I can't remember for sure but I think I actually tried that also. But isn't the following an indication that it's on?
    Code:
    show parameter recyclebin;
    NAME                                               TYPE        VALUE                                                                                                
    -------------------------------------------------- ----------- ---------------------------------------------------------------------------------------------------- 
    recyclebin                                         string      on

  14. #14
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    I can't remember for sure but I think I actually tried that also
    Well have you or haven't you? And what was the result?
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

Posting Permissions

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