This discussion is archived
3 Replies Latest reply: Dec 18, 2009 3:06 AM by krishanJaglan RSS

Unable to create tablespace as system user

688604 Newbie
Currently Being Moderated
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 Oracle ACE
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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
    krishanJaglan Newbie
    Currently Being Moderated
    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

Legend

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