ORA-00959 Error

Tags:
Oracle
Hi, I am new to Oracle and I am trying to create a simple table as USER1. I connect as SYS and create USER1, USER2 as follow: CREATE USER "USER1" IDENTIFIED BY "user1pass" DEFAULT TABLESPACE "users" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK; GRANT "CONNECT" TO "USER1"; GRANT CREATE TABLE TO "USER1"; When I connect as USER1 and create a simple table "mytable" using TABLESPACE users I got this error: ORA-00959: tablespace 'USERS' does not exist but the tablespace 'USERS' is there. What do I need to do to fix it? Thanks

Answer Wiki

Thanks. We'll let you know when a new response is added.

Try creating the table without specifying the TABLESPACE clause. Doing that will force the table to be created in the default tablespace specified in the ‘create user’ statement. If it still fails, do a select tablespace_name from dba_tablespaces; to ensure that the USERS tablespace does indeed exist.

Discuss This Question: 7  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • EvanOraDBA
    You do not have any quota/resource on tablespace USERS. Either: Give the user a specific quota to this tablespaceg. E.g. alter user user1 quota unlimited on users; or the role "resource" or more specifically the system privilege "UNLIMITED TABLESPACE" which is one of those included in the role "resource". E.g. grant UNLIMITED TABLESPACE to user1;
    0 pointsBadges:
    report
  • JamesNguyen
    Hi, Thank you all for helping. SQL> select * from v$tablespace; TS# NAME INC BIG FLA ---------- ------------------------------ --- --- --- 0 SYSTEM YES NO YES 1 UNDOTBS YES NO YES 2 SYSAUX YES NO YES 3 TEMP YES NO YES 4 tools YES NO YES 5 users YES NO YES 6 data YES NO YES 7 index YES NO YES 8 rows selected. SQL> As you can see, there is users tablespace. I am able to create a table if I don't use TABLESPACE users I will try to add some data now. Next I will export and import some data. Will let you know if I need help. THANKS!!!!
    0 pointsBadges:
    report
  • EvanOraDBA
    My original comment applies. You can create a segment in any tablespace (whether specified with the tablespace clause, or defualted from the users default) ONLY if you have unlimited tablespace system provilege or some quota on that tablespace. select * from dba_ts_quotas select * from dba_sys_privs select * from dba_role_privs
    0 pointsBadges:
    report
  • Randym
    You may just be testing, but you shouldn't create tables under the user SYS anyway. Create another user, set the default table space users to that user, grant resource to that user. Then create the table
    1,740 pointsBadges:
    report
  • Navinsamuga
    The problem is that the tablespace name is in lower case. Please specify the tablespace name as "users" in your create table command and it will work.
    0 pointsBadges:
    report
  • Catholic
    I believe this may be the case sensitivity. The real danger in Oracle is the use of quotation marks ("), which causes Oracle to actually store data, in your case tablespace name in lower case (users) where the intent is tablespace name USERS. Perform a 'select tablespace_name from dba_tablespaces;' in sql*plus and see for yourself. As a note I would resist using quotation marks (") in Oracle for it is rare that they are ever needed. Later, Keith Brockman
    0 pointsBadges:
    report
  • Gotcha
    Have you tried to use uppercase (i.e. USERS) for the tablespace name? Since you are using quotes around the tablespace name when you create the user, it probably needs to be uppercase.
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following