This discussion is archived
13 Replies Latest reply: Jan 30, 2013 5:41 AM by Nicosa RSS

Error:- ORA-01795: maximum number of expression in a list is 1000

948368 Newbie
Currently Being Moderated
Hi All,


we are using database 11g.

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production



i am getting one error while selecting the data from table.

SELECT interco_type, entity, chapter_entity, tran_currency, source_id_entity,
tran_id_entity, mirror_id, gaap_type, counterpart
FROM t_gri_reconid_dtl_agg_gcr;
or
select * from t_gri_reconid_dtl_agg_gcr where rownum = 1 ;

Error:- ORA-01795: maximum number of expression in a list is 1000 ( error is same in both the case )


Please help me if any body got to this situaltion.

regards
shyam~
  • 1. Re: Error:- ORA-01795: maximum number of expression in a list is 1000
    rp0428 Guru
    Currently Being Moderated
    Is that a table or a view?

    Does it have any object-type columns?

    Post the DDL for the table involved.
  • 2. Re: Error:- ORA-01795: maximum number of expression in a list is 1000
    sb92075 Guru
    Currently Being Moderated
    POST RESULTS FROM ISSUING SQL BELOW

    DESC t_gri_reconid_dtl_agg_gcr
  • 3. Re: Error:- ORA-01795: maximum number of expression in a list is 1000
    948368 Newbie
    Currently Being Moderated
    Hi ,

    Table :- t_gri_reconid_dtl_agg_gcr


    DESC t_gri_reconid_dtl_agg_gcr
    Name Null Type
    ------------------------------ -------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    RECON_ID NUMBER
    PUBLISH_FREQ NOT NULL CHAR(1)
    PUBLISH_DATE NOT NULL DATE
    INVENTORY_DATE NOT NULL DATE
    INTERCO_TYPE NOT NULL NUMBER(1)
    ENTITY NOT NULL VARCHAR2(25)
    BUSINESS_UNIT NOT NULL VARCHAR2(5)
    CHAPTER_ENTITY NOT NULL VARCHAR2(25)
    ACCOUNT VARCHAR2(10)
    ALTACCT VARCHAR2(10)
    DEPTID VARCHAR2(10)
    EVENT_NATURE VARCHAR2(255)
    OPERATION_TYPE VARCHAR2(10)
    TRADE_DATE DATE
    MATURITY_DATE DATE
    VALUE_DATE DATE
    FIN_CLASS VARCHAR2(10)
    TRAN_CURRENCY NOT NULL VARCHAR2(3)
    SOURCE_ID_ENTITY VARCHAR2(3)
    GOP_ENTITY VARCHAR2(10)
    OBJECT_CODE_ENTITY VARCHAR2(10)
    TRAN_ID_ENTITY VARCHAR2(200)
    MIRROR_ID NUMBER(15)
    TRAN_AMOUNT_TOTAL_ENTITY NUMBER(26,3)
    BREAK_TRAN_CURRENCY NUMBER(26,3)
    BREAK_ABSOLUTE_VALUE NUMBER(26,3)
    BREAK_GROUP_CURRENCY NUMBER(26,3)
    MIRROR_STATUS VARCHAR2(10)
    GAAP_TYPE VARCHAR2(10)
    TRAN_AMOUNT_TOTAL_CPT NUMBER(26,3)
    AFFILIATE VARCHAR2(10)
    COUNTERPART VARCHAR2(25)
    CHAPTER_CPT VARCHAR2(25)
    TRAN_ID_CPT VARCHAR2(200)
    EVENT_NATURE_CPT VARCHAR2(255)
    MIRROR_SOURCE VARCHAR2(10)

    36 rows selected


    Error is coming for selecting data from table
  • 4. Re: Error:- ORA-01795: maximum number of expression in a list is 1000
    rp0428 Guru
    Currently Being Moderated
    >
    Error is coming for selecting data from table
    >
    We understand that but the only time I have seen an ORA-01795 exception is when the number of items of an IN list (e.g. WHERE xxx IN (1,2,3 ... 1050) is greater than 1000.

    Are there possibly any system triggers enabled? Or do you have auditing in the DB that could be executing other code?

    Does the error occur even if you select NO data? For example a query with a 'WHERE 1=2' predicate?
  • 5. Re: Error:- ORA-01795: maximum number of expression in a list is 1000
    948368 Newbie
    Currently Being Moderated
    Hi,

    we are not using any IN clause and also trigger is not enabled to this table.

    Note:- This is partitioned table on recon id so if we specify partition key it works but without partition it is not working.


    for example :-


    select * from T_GRI_RECONID_DTL_AGG_GCR
    returns error message : ORA-01795: maximum number of expressions in a list is 1000


    select * from T_GRI_RECONID_DTL_AGG_GCR where rownum=1
         returns error message : ORA-01795: maximum number of expressions in a list is 1000


    select count(1) from T_GRI_RECONID_DTL_AGG_GCR partition (GRI_1482)
         returns 4453583 rows

    select count(1) from T_GRI_RECONID_DTL_AGG_GCR partition (GRI_1483)
         returns 515566 rows



    not sure what went wrong ..at least for
    select * from T_GRI_RECONID_DTL_AGG_GCR

    it should not give any error but we are getting.


    Please help if you have any idea to resolve this or is it a bug not sure.

    regards
    Shyam~

    Edited by: 945365 on Jan 29, 2013 10:49 PM
  • 6. Re: Error:- ORA-01795: maximum number of expression in a list is 1000
    Manik Expert
    Currently Being Moderated
    Are you able to get explain plan for this ...
    select * from T_GRI_RECONID_DTL_AGG_GCR;
    AND
    select * from T_GRI_RECONID_DTL_AGG_GCR partition (GRI_1483)
    I reckon there is some policy around this table which is causing this problem.

    Cheers,
    Manik.
  • 7. Re: Error:- ORA-01795: maximum number of expression in a list is 1000
    948368 Newbie
    Currently Being Moderated
    Hi ,

    we try to take the explain plan.

    it is throughing an error..

    Operation     Object Name     Rows     Bytes     Cost     Object Node     In/Out     PStart     PStop

    ORA-01795: maximum number of expressions in a list is 1000


    we can't able to select data then we can't see the plan.

    regards
    shyam~
  • 8. Re: Error:- ORA-01795: maximum number of expression in a list is 1000
    948368 Newbie
    Currently Being Moderated
    Hi,

    can anyone help on this issue?

    regards
    Shyam~
  • 9. Re: Error:- ORA-01795: maximum number of expression in a list is 1000
    Nicosa Expert
    Currently Being Moderated
    Hi,

    Do you have VPD and/or FGAC installed on your database ?
    Can you copy-paste us the result of this :
    select * from all_objects where object_name=upper('t_gri_reconid_dtl_agg_gcr');
  • 10. Re: Error:- ORA-01795: maximum number of expression in a list is 1000
    948368 Newbie
    Currently Being Moderated
    Hi,

    thanks for your response.

    i dont have VPD and FGAC installed in my desktop.

    select * from all_objects where object_name=upper('t_gri_reconid_dtl_agg_gcr');

    here is the few rows from the above query as there are lot of rows. Please let me know if you need more rows.



    PUBLIC     T_GRI_RECONID_DTL_AGG_GCR          10114957          SYNONYM     26-NOV-12     26-NOV-12     2012-11-26:22:09:41     VALID     N     N     N     1     
    GCRGRIT$OWNER     T_GRI_RECONID_DTL_AGG_GCR     GRI_1     10114668     10320109     TABLE PARTITION     26-NOV-12     28-JAN-13     2012-11-26:22:02:14     VALID     N     N     N     1     
    GCRGRIT$OWNER     T_GRI_RECONID_DTL_AGG_GCR     GRI_10     10159558     10320125     TABLE PARTITION     12-DEC-12     28-JAN-13     2012-12-12:16:51:49     VALID     N     N     N     1     
    GCRGRIT$OWNER     T_GRI_RECONID_DTL_AGG_GCR     GRI_1003     10255173     10322000     TABLE PARTITION     15-JAN-13     28-JAN-13     2013-01-15:00:02:58     VALID     N     N     N     1     
    GCRGRIT$OWNER     T_GRI_RECONID_DTL_AGG_GCR     GRI_1004     10255219     10322002     TABLE PARTITION     15-JAN-13     28-JAN-13     2013-01-15:00:05:29     VALID     N     N     N     1     
    GCRGRIT$OWNER     T_GRI_RECONID_DTL_AGG_GCR     GRI_1005     10255263     10322004     TABLE PARTITION     15-JAN-13     28-JAN-13     2013-01-15:00:09:36     VALID     N     N     N     1     
    GCRGRIT$OWNER     T_GRI_RECONID_DTL_AGG_GCR     GRI_1006     10255311     10322006     TABLE PARTITION     15-JAN-13     28-JAN-13     2013-01-15:00:12:40     VALID     N     N     N     1     
    GCRGRIT$OWNER     T_GRI_RECONID_DTL_AGG_GCR     GRI_1007     10256040     10322008     TABLE PARTITION     15-JAN-13     28-JAN-13     2013-01-15:06:41:32     VALID     N     N     N     1     
    GCRGRIT$OWNER     T_GRI_RECONID_DTL_AGG_GCR     GRI_1008     10256200     10322010     TABLE PARTITION     15-JAN-13     28-JAN-13     2013-01-15:08:03:39     VALID     N     N     N     1     
    GCRGRIT$OWNER     T_GRI_RECONID_DTL_AGG_GCR     GRI_1009     10256241     10322012     TABLE PARTITION     15-JAN-13     28-JAN-13     2013-01-15:08:08:42     VALID     N     N     N     1     
    GCRGRIT$OWNER     T_GRI_RECONID_DTL_AGG_GCR     GRI_1010     10256294     10322014     TABLE PARTITION     15-JAN-13     28-JAN-13     2013-01-15:08:17:15     VALID     N     N     N     1     
    GCRGRIT$OWNER     T_GRI_RECONID_DTL_AGG_GCR     GRI_1011     10256337     10322016     TABLE PARTITION     15-JAN-13     28-JAN-13     2013-01-15:08:20:12     VALID     N     N     N     1     
    GCRGRIT$OWNER     T_GRI_RECONID_DTL_AGG_GCR     GRI_1012     10256393     10322018     TABLE PARTITION     15-JAN-13     28-JAN-13     2013-01-15:08:25:29     VALID     N     N     N     1     
    GCRGRIT$OWNER     T_GRI_RECONID_DTL_AGG_GCR     GRI_1013     10256434     10322020     TABLE PARTITION     15-JAN-13     28-JAN-13     2013-01-15:08:28:25     VALID     N     N     N     1     
    GCRGRIT$OWNER     T_GRI_RECONID_DTL_AGG_GCR     GRI_1014     10256475     10322022     TABLE PARTITION     15-JAN-13     28-JAN-13     2013-01-15:08:32:11     VALID     N     N     N     1     
    GCRGRIT$OWNER     T_GRI_RECONID_DTL_AGG_GCR     GRI_1015     10256516     10322024     TABLE PARTITION     15-JAN-13     28-JAN-13     2013-01-15:08:37:25     VALID     N     N     N     1     
    GCRGRIT$OWNER     T_GRI_RECONID_DTL_AGG_GCR     GRI_1016     10256557     10322026     TABLE PARTITION     15-JAN-13     28-JAN-13     2013-01-15:08:39:29     VALID     N     N     N     1     
    GCRGRIT$OWNER     T_GRI_RECONID_DTL_AGG_GCR     GRI_1017     10256607     10322028     TABLE PARTITION     15-JAN-13     28-JAN-13     2013-01-15:08:43:24     VALID     N     N     N     1     

    regards
    shyam~
  • 11. Re: Error:- ORA-01795: maximum number of expression in a list is 1000
    Nicosa Expert
    Currently Being Moderated
    945365 wrote:
    i dont have VPD and FGAC installed in my desktop.
    Your desktop ?
    Is the database on your desktop ? (The question was if VPD or FGAC are "installed/used" in your Oracle Database no matter where it is)
    945365 wrote:
    here is the few rows from the above query as there are lot of rows. Please let me know if you need more rows.
    PUBLIC     T_GRI_RECONID_DTL_AGG_GCR          10114957          SYNONYM     26-NOV-12     26-NOV-12     2012-11-26:22:09:41     VALID     N     N     N     1     
    GCRGRIT$OWNER     T_GRI_RECONID_DTL_AGG_GCR     GRI_1     10114668     10320109     TABLE PARTITION     26-NOV-12     28-JAN-13     2012-11-26:22:02:14     VALID     N     N     N     1     
    Ok, the first line shows that there is a public synonym.
    We'd better check that it is pointing to the real table.
    Does the error happens if you do the select like that :
    select ...
    FROM GCRGRIT$OWNER.t_gri_reconid_dtl_agg_gcr;
    Can you copy-paste us the result of this :
    select * from all_synonyms where synonym_name='T_GRI_RECONID_DTL_AGG_GCR';
  • 12. Re: Error:- ORA-01795: maximum number of expression in a list is 1000
    948368 Newbie
    Currently Being Moderated
    Is the database on your desktop ? (The question was if VPD or FGAC are "installed/used" in your Oracle Database no matter where it is)

    i am not sure because i am working as a developer. Please let me know if i can check some how?


    select * FROM GCRGRIT$OWNER.t_gri_reconid_dtl_agg_gcr;

    Yes i am getting the same error .

    select * from all_synonyms where synonym_name='T_GRI_RECONID_DTL_AGG_GCR';

    Owner Synonym_name Table_owner Table_anme DB_LINK
    ------ ------------------------- ------------- -------------------------- --------
    PUBLIC     T_GRI_RECONID_DTL_AGG_GCR     GCRGRIT$OWNER     T_GRI_RECONID_DTL_AGG_GCR



    regards
    Shyam~
  • 13. Re: Error:- ORA-01795: maximum number of expression in a list is 1000
    Nicosa Expert
    Currently Being Moderated
    945365 wrote:
    Is the database on your desktop ? (The question was if VPD or FGAC are "installed/used" in your Oracle Database no matter where it is)

    i am not sure because i am working as a developer. Please let me know if i can check some how?
    Do a :
    select host_name from v$instance;
    Or check your connection string +(and the definition of the TNS alias if you're using one)+

    Is there a DBA (DataBase Administrator) you could ask about VPD or FGAC (and tell your problem also) ?

    My guess is that your query is rewritten on-the-fly either by VPD or FGAC.
    I'm not really into those options of Oracle, so I can't be sure.

Legend

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