DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: create user generating 'tablespace not found' error

  1. #1
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343

    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.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    because the tablespace doesnt exist

  3. #3
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    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.

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    commit has nothing to do with tablespaces.

    Lets see an exact cut and paste from sql*plus showing exactly what u typed

  5. #5
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    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

  6. #6
    Join Date
    Feb 2004
    Posts
    50
    Tablespace ERINT doesn't exist.
    Tablespace "erint" does exist.

  7. #7
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    Upper or Lower case does'nt matter.

  8. #8
    Join Date
    Feb 2004
    Posts
    50
    I think you'll find that in this case it does.

    You need to keep the quotes: "erint"

  9. #9
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    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.

  10. #10
    Join Date
    Feb 2004
    Posts
    50
    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
  •  


Click Here to Expand Forum to Full Width