[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3819
  • Last Modified:

Adding UNDOTBS tablespace

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
beefsayduhr
Asked:
beefsayduhr
  • 3
  • 3
1 Solution
 
bkowalskiCommented:
If Oracle says it's not there, then it's not. Check with the following query:

select tablespace_name from dba_tablespaces;
0
 
beefsayduhrAuthor Commented:
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
 
bkowalskiCommented:
No, you typed UNDOTBS and it should be UNDOTBS1.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
beefsayduhrAuthor Commented:
Ahhhh! I thought the UNDOTBS1 was the number of the datafile, as in UNDOTBS1, UNDOTBS2. I'll give it another shot.

Thanks
0
 
bkowalskiCommented:
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
 
beefsayduhrAuthor Commented:
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now