Home » RDBMS Server » Server Administration » Tablespace issue (Oracle 10G on solaris and Toad)
Tablespace issue [message #483680] Wed, 24 November 2010 03:09 Go to next message
hari_bk
Messages: 110
Registered: March 2006
Senior Member
Hi I am checking in Toad that my tablespace TEST_TS is almost full. To check the tables in this tablespace, I given below commands.

select * from user_tables where tablespace_name='TEST_TS'

select * from user_tab_partitions where tablespace_name='TEST_TS'

Both are displaying no rows.

My doubt is when the tablespace is not used by any tables how It's usage is nearly full? Is there any way to check?

I want to make use of unused tablespace.

Thanks
HK

Report message to a moderator

Re: Tablespace issue [message #483693 is a reply to message #483680] Wed, 24 November 2010 03:36 Go to previous messageGo to next message
Frank Naude
Messages: 4579
Registered: April 1998
Senior Member
USER_TABLES will only show your user's tables.
Try DBA_TABLES instead.

Report message to a moderator

Re: Tablespace issue [message #483694 is a reply to message #483693] Wed, 24 November 2010 03:49 Go to previous messageGo to next message
hari_bk
Messages: 110
Registered: March 2006
Senior Member
Frank Naude wrote on Wed, 24 November 2010 15:06
USER_TABLES will only show your user's tables.
Try DBA_TABLES instead.



Thanks Frank.

I checked DBA_TABLES as well. Yet the query returns no rows.

Also I am creating an index and specified a tablespace name with Create index(Index_tablespace) and I am getting error ORA-01652: unable to extend temp segment by 1024 in tablespace Index_tablespace.

Thanks
HK

Report message to a moderator

Re: Tablespace issue [message #483696 is a reply to message #483694] Wed, 24 November 2010 03:52 Go to previous messageGo to next message
cookiemonster
Messages: 13922
Registered: September 2008
Location: Rainy Manchester
Senior Member
Could be there are no tables in there but lots of indexes. Try querying dba_extents.

Report message to a moderator

Re: Tablespace issue [message #483701 is a reply to message #483680] Wed, 24 November 2010 03:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68650
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To know what's inside a tablespace query DBA_SEGMENTS.
A segment is the physical part of an object (a table for instance).
A table is a logical object which may be spread over one or many segments.
A tablespace contains segments not tables (but the segments of tables).

Regards
Michel

Report message to a moderator

Re: Tablespace issue [message #483702 is a reply to message #483696] Wed, 24 November 2010 04:00 Go to previous messageGo to next message
hari_bk
Messages: 110
Registered: March 2006
Senior Member
cookiemonster wrote on Wed, 24 November 2010 15:22
Could be there are no tables in there but lots of indexes. Try querying dba_extents.



Yes! the tablespace is having only Indexes. Will move these indexes to another tablespace. Thanks a lot.

Now the other question is I can see a particular tablespace usage is only 1%. Yet when i tried to create index in that tablespace, I am getting error ORA-01652: unable to extend temp segment by 1024 in tablespace TEMP_TS.

Thanks
HK

Report message to a moderator

Re: Tablespace issue [message #483703 is a reply to message #483702] Wed, 24 November 2010 04:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68650
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Enlarge the tablespace.
Do not explain what you have as you don't understand what happens you are not able to clearly, precisely and accurately explain it.
Instead, show it: copy and paste your SQL*Plus session.

Regards
Michel

[Updated on: Wed, 24 November 2010 04:02]

Report message to a moderator

Re: Tablespace issue [message #483706 is a reply to message #483703] Wed, 24 November 2010 04:07 Go to previous messageGo to next message
hari_bk
Messages: 110
Registered: March 2006
Senior Member
Michel Cadot wrote on Wed, 24 November 2010 15:32
Enlarge the tablespace.
Do not explain what you have as you don't understand what happens you are not able to clearly, precisely and accurately explain it.
Instead, show it: copy and paste your SQL*Plus session.

Regards
Michel



ORA-12801: error signaled in parallel query server P000
ORA-01652: unable to extend temp segment by 1024 in tablespace TEMP_INDEX_TS

But I can see in Toad TEMP_INDEX_TS usage is less than 1%

Thanks

Report message to a moderator

Re: Tablespace issue [message #483707 is a reply to message #483706] Wed, 24 November 2010 04:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68650
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
TOAD is a mess ("Those who live by the GUI, die by the GUI.").
Tablespace usage is meaningless for a temporary tablespace.

Regards
Michel

Report message to a moderator

Re: Tablespace issue [message #483709 is a reply to message #483707] Wed, 24 November 2010 04:20 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
search for the objects/query using temp tablespace
V$SORT_USAGE and V$SORT_SEGMENT

[Updated on: Wed, 24 November 2010 04:24]

Report message to a moderator

Re: Tablespace issue [message #483725 is a reply to message #483709] Wed, 24 November 2010 04:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68650
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
...and do NOT use TOAD.

Regards
Michel

Report message to a moderator

Re: Tablespace issue [message #483726 is a reply to message #483725] Wed, 24 November 2010 04:54 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Especially for administration tasks.

And Contact your DBA he will do some magic (Smile)

sriram

Report message to a moderator

Re: Tablespace issue [message #483729 is a reply to message #483680] Wed, 24 November 2010 05:16 Go to previous messageGo to next message
hari_bk
Messages: 110
Registered: March 2006
Senior Member
Thanks all!

Regards

Report message to a moderator

Re: Tablespace issue [message #483730 is a reply to message #483729] Wed, 24 November 2010 05:20 Go to previous message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Next time please post the actuall message at your first post
Thankyou
sriram

Report message to a moderator

Previous Topic: how to get password for database users.
Next Topic: MR Lock
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat May 11 15:35:03 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.