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!!!

Can you post the result of "select * from dba_tablespaces" and the statement and error you are getting?

*****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

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

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;

---

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.

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

thanks to those who replied.

have to contact oracle support...

create a dummy tablespace and drop it, sometimes this solves the problem