1 2 Previous Next 20 Replies Latest reply: Dec 4, 2012 7:05 AM by 864103 RSS

    indexes

    864103
      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
          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
            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
              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
                Check this thread
                ora 01129
                • 5. Re: indexes
                  864103
                  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
                    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
                      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
                        Recreate temp tablespace i post steps do you read what i wrote
                        • 9. Re: indexes
                          864103
                          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
                            create index ABS_MEDIATION.indexname_indx
                            on ABS_MEDIATION.MTR_EPPC_CALLING_DATA(CALLINGPARTYNUMBER);
                            • 11. Re: indexes
                              864103
                              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
                                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
                                  thanks a lot , idon't know what i say.
                                  • 14. Re: indexes
                                    Osama_Mustafa
                                    I appreciate that i could help you
                                    1 2 Previous Next