?
Solved

Adding UNDOTBS tablespace

Posted on 2003-03-27
6
Medium Priority
?
3,801 Views
Last Modified: 2008-03-06
My UNDOTBS01 is showing full. How can I purge this tablespace, or extend it. I tried to extend it with

ALTER TABLESPACE UNDOTBS
  ADD DATAFILE 'D:\oracle\oradata\MyDB\UNDOTBS02.DBF'
  SIZE 70M REUSE;

But I get this error:

ALTER TABLESPACE UNDOTBS
*
ERROR at line 1:
ORA-00959: tablespace 'UNDOTBS' does not exist

Yet I know the tablespace is there. Any help would be greatly appreciated.

Thanks
0
Comment
Question by:beefsayduhr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 3

Expert Comment

by:bkowalski
ID: 8218997
If Oracle says it's not there, then it's not. Check with the following query:

select tablespace_name from dba_tablespaces;
0
 

Author Comment

by:beefsayduhr
ID: 8219054
I had run that and here is the result. The table is clearly there, right?

SQL> select tablespace_name from dba_tablespaces

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1  #########Here is the tablespace referenced#####
TEMP
INDX
ACTIVE_LIST_INDEX
EVENT_INDEX
EVENT_PAYLOAD
SYSTEM_INDEX
REPLAY_INDEX
REPLAY_DATA
UNDO

TABLESPACE_NAME
------------------------------
ACTIVE_LIST_DATA
TEMP
EVENT_DATA
SYSTEM_DATA
V_DATA
V_INDEX

17 rows selected.

SQL>
0
 
LVL 3

Accepted Solution

by:
bkowalski earned 80 total points
ID: 8219106
No, you typed UNDOTBS and it should be UNDOTBS1.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:beefsayduhr
ID: 8219172
Ahhhh! I thought the UNDOTBS1 was the number of the datafile, as in UNDOTBS1, UNDOTBS2. I'll give it another shot.

Thanks
0
 
LVL 3

Expert Comment

by:bkowalski
ID: 8219177
One other thing.  How big is your datafile UNDOTBS01.DBF?

select file_name, bytes from dba_data_files where tablespace_name = 'UNDOTBS1';

Instead of adding another datafile, if the current one is not at it's o/s limit, you can increase its size, for example to increase to 500 MB:

alter database datafile 'D:\oracle\oradata\MyDB\UNDOTBS01.DBF' resize 500M

0
 

Author Comment

by:beefsayduhr
ID: 8219230
It is only 70M. It was affecting the insertion rate, that's why I broke it out to another file. I did increase the size on the second file to 256M. Thanks for your help!

Regards
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question