-
create user generating 'tablespace not found' error
Hi all, I just created tablespace xxx. Now when I try to create a user with the default tablespace as xxx, I get the error 'tablespace xxx not found', but the datafile is there. Does anyone know why is my tablespace not being recognized ?
Thanks,
Shiva.
-
because the tablespace doesnt exist
-
I did create the tablespace and did a COMMIT - I can see the datafiles that I created for my tablespace.
Is there any place where I can lookup if my tablespace is logically present or not ?
Shiva.
-
commit has nothing to do with tablespaces.
Lets see an exact cut and paste from sql*plus showing exactly what u typed
-
Here you go :
SQL> create tablespace "erint"
2 datafile '/data/oracle/dgenolt2/d05/erint_01.dbf' size 400M
3 autoextend on next 20971520 maxsize 1024M,
4 '/data/oracle/dgenolt2/d05/erint_02.dbf' size 400M
5 autoextend on next 20971520 maxsize 1024M
6 extent management local online permanent;
Tablespace created.
SQL> create tablespace "erint_idx"
2 datafile '/data/oracle/dgenolt2/d05/erint_idx_01.dbf' size 400M
3 autoextend on next 20971520 maxsize 1024M,
4 '/data/oracle/dgenolt2/d05/erint_idx_02.dbf' size 400M
5 autoextend on next 20971520 maxsize 1024M
6 extent management local online permanent;
Tablespace created.
SQL> commit;
Commit complete.
SQL> create user erint
2 identified by erint_007
3 default tablespace erint
4 profile default
5 account unlock;
create user erint
*
ERROR at line 1:
ORA-00959: tablespace 'ERINT' does not exist
-
Tablespace ERINT doesn't exist.
Tablespace "erint" does exist.
-
Upper or Lower case does'nt matter.
-
I think you'll find that in this case it does.
You need to keep the quotes: "erint"
-
The upper case/lower case/with & without quotes nothing is working - and I do not see that datafile in v$datafile or dba_data_files, but it is physically present in the OS -If I try creating the tablespace again with the same parameters, I get 'datafile already exists' error. So will just physically dropping that data file from OS and recreating the tablespace again using the same data file work right?
Thanks for ur continued response.
Shiva.
-
Can you see the tablespace?
select tablespace_name from dba_tablespaces;
Are the sizes of the datafiles 400Mb?
The thing that puzzles me, if the tablespace command completed successfully, the datafiles are on the machine, why can't you see the tablespace and datafiles in the database. I know this sounds stupid, but are you sure you are looking at the correct database?
If you are sure that neither the tablespace or datafiles belong to the database (or any other database), then yes, recreate the tablespace.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|