DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: missing tablespace!!!

  1. #1
    Join Date
    May 2003
    Posts
    6

    missing tablespace!!!

    have a problem.

    i'm trying to create a table in oracle ver 7.3

    but it says tablespace does not exist. when i add datafiles to any of the tablespaces it also says tablespace does not exist.

    but i can see the tablespaces in dba_tablespaces.

    its an old tablespace with several tables. i can access all of the tables in the tablespace(select,insert,etc)

    this happened after we moved to a new unix machine. we could use the tablespaces before.

    we copied all the contents of the old machine to the new machine.

    we have another instance in the same server and it works fine. its just this other database thats giving us problems.


    help!!!

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Can you post the result of "select * from dba_tablespaces" and the statement and error you are getting?
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  3. #3
    Join Date
    May 2003
    Posts
    6
    *****SELECT STATEMENT*****:

    SQL> select tablespace_name,status from dba_tablespaces
    2 where tablespace_name = 'TBPLMSTS';

    TABLESPACE_NAME STATUS
    ------------------------------ ---------
    TBPLMSTS ONLINE

    *****CREATE STATEMENT*****:

    CREATE TABLE TBTEST.TBSPLMAT
    (REF_TS_NO NUMBER(8) ,
    ISSUER_CD VARCHAR(10) ,
    NXT_DUE_DT DATE ,
    PN_NO CHAR(16))
    TABLESPACE TBPLMSTS
    STORAGE (
    INITIAL 12288
    NEXT 4096
    MINEXTENTS 1
    MAXEXTENTS 5
    PCTINCREASE 0
    )
    PCTFREE 10
    /

    *****ERROR MESSAGE in SQLPLUS*****:

    SQL> @cre_tbsplmat
    CREATE TABLE TBTEST.TBSPLMAT
    *
    ERROR at line 1:
    ORA-00959: tablespace 'TBPLMSTS' does not exist

  4. #4
    Join Date
    May 2002
    Posts
    2,645
    You query dba_tablespaces and ts$ and the tablespaces are there. SOLUTION DESCRIPTION
    =======================
    The ts# column of the ts$ tables must be in sequencial order. When the sequence is broken all tablespaces above the break number are not accessible. Creating a new tablespace with occupy the broken sequence number if only one number is missing in the sequence. If more than one number is missing in the sequence then you will have to create as many tablespaces as numbers missing

  5. #5
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Did you check ts$ as steven said? Also can you post the result of these two queries.
    Code:
    select count(*) from ts$;                             
    select count(*) from ts$ where ts# > -1;
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  6. #6
    Join Date
    May 2003
    Posts
    6
    thanks for you replies.

    i did a select on ts$. this is the result:

    TS# NAME
    ---------- ---------------
    0 SYSTEM
    1 RBS
    2 TEMP
    3 TOOLS
    4 USERS
    5 TBSTABTS
    6 TBSTGETS
    7 TBDTMPTS
    8 TBDIDXTS
    9 XP_TS
    10 PATROLTS
    15 TBPIDXTS
    16 TBPLMSTS
    17 TRTESTDB
    18 TRTESTTS
    11 TMPLMSTS
    12 TBPPMSTS
    13 TBPSECTS
    14 TBPSTCTS

    does the TS# have to be in sequence in the result? or is it ok that all the numbers from 0-18 are there?

    the select count results are:

    19 rows selected.
    >select count(*) from ts$
    COUNT(*)
    ----------
    19
    1 row selected.
    >select count(*) from ts$ where ts# > -1
    COUNT(*)
    ----------
    18
    1 row selected.

  7. #7
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    That's the problem. There is mismatch between ts$ and index of ts$

    I searched through Metalink but they say contact Oracle support if you have this proble. Here is the link.

    Note:18553.1
    Last edited by SANJAY_G; 05-22-2003 at 02:45 AM.
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  8. #8
    Join Date
    May 2003
    Posts
    6
    thanks to those who replied.

    have to contact oracle support...

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    create a dummy tablespace and drop it, sometimes this solves the problem

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