3 Replies Latest reply: Dec 18, 2009 5:06 AM by krishan Jaglan RSS

    Unable to create tablespace as system user

    688604
      I am trying to create a temporary tablespace as system user but getting below error. Any help would be appecaiated.

      Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit

      CREATE TEMPORARY TABLESPACE A_TEMP TEMPFILE '+ORADATA' SIZE 1G AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M

      DBD::Oracle::db do failed: ORA-01119: error in creating database file '+ORADATA'
      ORA-17502: ksfdcre:4 Failed to create file +ORADATA
      ORA-15055: unable to connect to ASM instance
      ORA-01031: insufficient privileges
      ORA-15055: unable to connect to ASM instance
      ORA-01031: insufficient privileges (DBD ERROR: OCIStmtExecute) [for Statement "CREATE  TEMPORARY  TABLESPACE HIS_TEMP  TEMPFILE  '+ORADATA' SIZE 1G AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M "] at ./HisDbTs.pl line 132.
      Could not create tablespace HIS_TEMP: ORA-01119: error in creating database file '+ORADATA'
      ORA-17502: ksfdcre:4 Failed to create file +ORADATA
      ORA-15055: unable to connect to ASM instance
      ORA-01031: insufficient privileges
      ORA-15055: unable to connect to ASM instance
      ORA-01031: insufficient privileges (DBD ERROR: OCIStmtExecute)Tablespaces could not be created.HisDbObject done
        • 1. Re: Unable to create tablespace as system user
          Surachart Opun
          What did you see in alert of ASM?
          Your asm service and the oracle database service were started under different accounts ???
          The services must be running under the same account to be able to communicate with each other.
          • 2. Re: Unable to create tablespace as system user
            fjfranken
            I would indeed start by looking in the alert_log of the ASM instance.
            It looks like it is not completely started.

            A local test with seperate users for DB and ASM instance shows:
            $ sqlplus system/password

            SQL*Plus: Release 10.2.0.4.0 - Production on Thu Dec 17 13:40:35 2009

            ....
            SQL> create temporary tablespace a_temp tempfile '+DB01' size 1g;

            Tablespace created.

            SQL>
            So, als system user, it is possible to create a tablespace.

            We however don't use sysoper and sysdba seperation, but even that should not bring a problem here.
            • 3. Re: Unable to create tablespace as system user
              krishan Jaglan
              Hi user5461190,

              Its not System user which is not able to create tablespace, Even if you try sys it will still fail with same error. Check permission on the raw disk used for ORADATA diskgroup.

              Permission should be
              for all "asm disk string"
              chown oracle:dba
              chmod 660



              Confirm whether ASM and Database are installed under same user ? Also confirm the value of SQLNET.AUTHENTICATION_SERVICES from sqlnet.ora of $ORACLE_HOME


              Thanks
              Krishan Jaglan