OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » Tablespace problem

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Tablespace problem [message #133549]

Fri, 19 August 2005 10:33

orcl_dba
Messages: 84
Registered: March 2005

Member

Tablespace is not being acceseed.

when i give the querry
select * from dba_tablespaces;
the name of the tbalespace is there , but i cant make it default tablespace for any user ..

cant drop tablespace and cant add datafile even to that tablespae
it gives error like ..

ora 02166 or 2140 etc.. invalid tablespace name ..

give name of tablespace.... etc..

BR

Report message to a moderator

Re: Tablespace problem [message #133550 is a reply to message #133549]

Fri, 19 August 2005 10:39

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Please post your session.
We can understand a little better.

With broken messages like this, we have no idea. We cannot get into your mind to understand things.

Quote:

ora 02166 or 2140 etc.. invalid tablespace name ..

give name of tablespace.... etc.

Report message to a moderator

Re: Tablespace problem [message #133556 is a reply to message #133550]

Fri, 19 August 2005 11:14

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Did you check TS$.

ts$.online$ has specific entries to describe the status of tablespace.
1-available and normal
3-dropped
3-errored during creation and never been created.-->??????
Please see session.
Lately, Oracle support has documented this behaviour.

---
---
---

sys@9i > create tablespace mytbs datafile '/u01/app/oracle/oradata/mutation/mytbs.dbf' size 5m;

Tablespace created.

sys@9i > select name,online$ from ts$;

NAME ONLINE$
------------------------------ ----------
SYSTEM 1
UNDOTBS1 1
TEMP 1
USERS 1
TOOLS 1
MYTBS 1

6 rows selected.

sys@9i > drop tablespace mytbs;

Tablespace dropped.

sys@9i > select name,online$ from ts$;

NAME ONLINE$
------------------------------ ----------
SYSTEM 1
UNDOTBS1 1
TEMP 1
USERS 1
TOOLS 1
MYTBS 3

6 rows selected.

sys@9i > create tablespace another_mytbs datafile '/u01/app/oracle/oradata/mutation_invalid_directory/mytbs.dbf' size 5m;
create tablespace another_mytbs datafile '/u01/app/oracle/oradata/mutation_invalid_directory/mytbs.dbf' size 5m
*
ERROR at line 1:
ORA-01119: error in creating database file
'/u01/app/oracle/oradata/mutation_invalid_directory/mytbs.dbf'
ORA-27040: skgfrcre: create error, unable to create file
SVR4 Error: 2: No such file or directory


sys@9i > select name,online$ from ts$;

NAME ONLINE$
------------------------------ ----------
SYSTEM 1
UNDOTBS1 1
TEMP 1
USERS 1
TOOLS 1
MYTBS 3
ANOTHER_MYTBS 3

Report message to a moderator

Re: Tablespace problem [message #133767 is a reply to message #133556]

Mon, 22 August 2005 03:09

JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland

Senior Member

Hi
Just a stab in the dark, but did you name it with quotes around the name?
Try enclosing the name of the tbs in quotes when you drop it.:

system@DBA.WORLD> create tablespace "John" datafile '\Oraclass\Oradata\johnts.dbf' size 5m reuse;

Tablespace created.

system@DBA.WORLD> drop tablespace john;
drop tablespace john
*
ERROR at line 1:
ORA-00959: tablespace 'JOHN' does not exist


system@DBA.WORLD> select tablespace_name from user_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS
TEMP
USERS
INDX
SAMPLE
QUERY_DATA
STOCKS
John

9 rows selected.

system@DBA.WORLD> drop tablespace "John";

Tablespace dropped.

Jim

Report message to a moderator

Previous Topic:	sql query log
Next Topic:	Block updates

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 26 10:21:51 CST 2025