ORA-00959 Error
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
Software/Hardware used:
Software/Hardware used:
ASKED:
May 10, 2005 7:57 PM
UPDATED:
May 16, 2005 4:12 PM
Answer Wiki:
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.
To see all answers submitted to the Answer Wiki: View Answer History.
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;
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!!!!
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
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
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.
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
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.