This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Dec 4, 2012 5:05 AM by 864103 RSS

indexes

864103 Newbie
Currently Being Moderated
hi all ,

oracle 11g.2 asm with rac in RHEL 5 standard edition


please help me in this :


QL> create index CALLINGPARTYNUMBER.ix
2 on ABS_MEDIATION.MTR_EPPC_CALLING_DATA(CALLINGPARTYNUMBER)
3 tablespace indx;
create index CALLINGPARTYNUMBER.ix
*
ERROR at line 1:
ORA-01917: user or role '' does not exist
thanks
  • 1. Re: indexes
    864103 Newbie
    Currently Being Moderated
    SQL> create index CALLINGPARTYNUMBER.ix
    2 on ABS_MEDIATION.MTR_EPPC_CALLING_DATA(CALLINGPARTYNUMBER);
    on ABS_MEDIATION.MTR_EPPC_CALLING_DATA(CALLINGPARTYNUMBER)
    *
    ERROR at line 2:
    ORA-01129: user's default or temporary tablespace does not exist




    SQL> select DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username='ABS_MEDIATION';

    DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
    ------------------------------ ------------------------------
    ABS_MID TEMP



    ????
  • 2. Re: indexes
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    ORA-01917:user or role 'string' does not exist
    Cause:There is not a user or role by that name.
    Action:Re-specify the name.
  • 3. Re: indexes
    864103 Newbie
    Currently Being Moderated
    thanks dear for quick reply



    SQL> create index CALLINGPARTYNUMBER.ix
    2 on ABS_MEDIATION.MTR_EPPC_SMS_DATA(CALLINGPARTYNUMBER);
    on ABS_MEDIATION.MTR_EPPC_SMS_DATA(CALLINGPARTYNUMBER)
    *
    ERROR at line 2:
    ORA-01129: user's default or temporary tablespace does not exist



    SQL> select DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username='ABS_MEDIATION';

    DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
    ------------------------------ ------------------------------
    ABS_MID TEMP


    ???
  • 4. Re: indexes
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Check this thread
    ora 01129
  • 5. Re: indexes
    864103 Newbie
    Currently Being Moderated
    please advice it's the same error

    SQL> select DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username='ABS_MEDIATION';

    DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
    ------------------------------ ------------------------------
    ABS_MID_IND TEMP1



    QL> create index CALLINGPARTYNUMBER.ix
    2 on ABS_MEDIATION.MTR_EPPC_SMS_DATA(CALLINGPARTYNUMBER);
    on ABS_MEDIATION.MTR_EPPC_SMS_DATA(CALLINGPARTYNUMBER)
    *
    ERROR at line 2:
    ORA-01129: user's default or temporary tablespace does not exist



    i'm confuse i always create indexes???
  • 6. Re: indexes
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    CREATE TEMPORARY TABLESPACE TEMP  TEMPFILE '/path_to_temp/temp01.dbf' SIZE 500M REUSE  AUTOEXTEND ON NEXT 100M MAXSIZE unlimited;
      ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
    Read the link i post please
  • 7. Re: indexes
    864103 Newbie
    Currently Being Moderated
    beleive me i read this link please see the below


    SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp1;
    ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp1
    *
    ERROR at line 1:
    ORA-12907: tablespace TEMP1 is already the default temporary tablespace


    SQL> select name from v$tablespace;

    NAME
    ------------------------------
    SYSTEM
    SYSAUX
    UNDOTBS1
    TEMP
    UNDOTBS2
    USERS
    ABS_MID
    ABS_MID_IND
    INDX
    TEMP1

    10 rows selected.

    SQL> select DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username='ABS_MEDIATION';

    DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
    ------------------------------ ------------------------------
    INDX TEMP1

    SQL> create index CALLINGPARTYNUMBER.ix
    2 on ABS_MEDIATION.MTR_EPPC_SMS_DATA(CALLINGPARTYNUMBER);
    on ABS_MEDIATION.MTR_EPPC_SMS_DATA(CALLINGPARTYNUMBER)
    *
    ERROR at line 2:
    ORA-01129: user's default or temporary tablespace does not exist


    SQL>


    your coopertion with me will be a high appreciated
  • 8. Re: indexes
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Recreate temp tablespace i post steps do you read what i wrote
  • 9. Re: indexes
    864103 Newbie
    Currently Being Moderated
    please your advice :



    SQL> CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE
    2 size 10m autoextend on next 100m maxsize 5g;

    Tablespace created.

    SQL>
    SQL>
    SQL>
    SQL>
    SQL>
    SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1;

    Database altered.

    SQL>
    SQL>
    SQL>
    SQL>
    SQL>
    SQL>
    SQL>
    SQL>
    SQL> desc dba_users;
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    USERNAME NOT NULL VARCHAR2(30)
    USER_ID NOT NULL NUMBER
    PASSWORD VARCHAR2(30)
    ACCOUNT_STATUS NOT NULL VARCHAR2(32)
    LOCK_DATE DATE
    EXPIRY_DATE DATE
    DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
    TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
    CREATED NOT NULL DATE
    PROFILE NOT NULL VARCHAR2(30)
    INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)
    EXTERNAL_NAME VARCHAR2(4000)
    PASSWORD_VERSIONS VARCHAR2(8)
    EDITIONS_ENABLED VARCHAR2(1)
    AUTHENTICATION_TYPE VARCHAR2(8)

    SQL> select DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username='ABS_MEDIATION';

    DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
    ------------------------------ ------------------------------
    INDX TEMP1

    SQL> select name from v$tablespace;

    NAME
    ------------------------------
    SYSTEM
    SYSAUX
    UNDOTBS1
    TEMP
    UNDOTBS2
    USERS
    ABS_MID
    ABS_MID_IND
    TEMP1

    9 rows selected.

    SQL> alter user ABS_MEDIATION
    2 default tablespace ABS_MID;

    User altered.

    SQL> select DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username='ABS_MEDIATION';

    DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
    ------------------------------ ------------------------------
    ABS_MID TEMP1

    SQL>
    SQL>
    SQL>
    SQL> create index CALLINGPARTYNUMBER.ix
    2 on ABS_MEDIATION.MTR_EPPC_CALLING_DATA(CALLINGPARTYNUMBER);
    on ABS_MEDIATION.MTR_EPPC_CALLING_DATA(CALLINGPARTYNUMBER)
    *
    ERROR at line 2:
    ORA-01129: user's default or temporary tablespace does not exist


    SQL>



    i do that ???but why
  • 10. Re: indexes
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    create index ABS_MEDIATION.indexname_indx
    on ABS_MEDIATION.MTR_EPPC_CALLING_DATA(CALLINGPARTYNUMBER);
  • 11. Re: indexes
    864103 Newbie
    Currently Being Moderated
    sorry for this my friend


    SQL> create index ABS_MEDIATION.CALLINGPARTYNUMBER.indx
    2 on ABS_MEDIATION.MTR_EPPC_CALLING_DATA(CALLINGPARTYNUMBER);
    create index ABS_MEDIATION.CALLINGPARTYNUMBER.indx
    *
    ERROR at line 1:
    ORA-00969: missing ON keyword


    SQL> create index CALLINGPARTYNUMBER.indx
    2 on ABS_MEDIATION.MTR_EPPC_CALLING_DATA(CALLINGPARTYNUMBER);
    on ABS_MEDIATION.MTR_EPPC_CALLING_DATA(CALLINGPARTYNUMBER)
    *
    ERROR at line 2:
    ORA-01129: user's default or temporary tablespace does not exist


    any help please , i google it but without reply
  • 12. Re: indexes
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    861100 wrote:
    sorry for this my friend


    SQL> create index ABS_MEDIATION.CALLINGPARTYNUMBER.indx
    2 on ABS_MEDIATION.MTR_EPPC_CALLING_DATA(CALLINGPARTYNUMBER);
    create index ABS_MEDIATION.CALLINGPARTYNUMBER.indx
    *
    ERROR at line 1:
    ORA-00969: missing ON keyword


    SQL> create index CALLINGPARTYNUMBER.indx
    2 on ABS_MEDIATION.MTR_EPPC_CALLING_DATA(CALLINGPARTYNUMBER);
    on ABS_MEDIATION.MTR_EPPC_CALLING_DATA(CALLINGPARTYNUMBER)
    *
    ERROR at line 2:
    ORA-01129: user's default or temporary tablespace does not exist


    any help please , i google it but without reply
    please please I start gave up to helping you ...

    why you put CALLINGPARTYNUMBER.indx "." CALLINGPARTYNUMBER is not schema because of this the error generated ... Listen to what i said if you don't Do it by yourself
    SQL> create index ABS_MEDIATION.CALLINGPARTYNUMBER_indx
    on ABS_MEDIATION.MTR_EPPC_CALLING_DATA(CALLINGPARTYNUMBER);
  • 13. Re: indexes
    864103 Newbie
    Currently Being Moderated
    thanks a lot , idon't know what i say.
  • 14. Re: indexes
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    I appreciate that i could help you
1 2 Previous Next

Legend

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