• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 495
  • Last Modified:

Set up or drop Default tablespace

Hi dear all experts:

How to set up default tablespace? In any ini or ora file?  

The default tablespace I have here seems corrupted. Can I re-create another default tablespace and drop the original one? Do I have to do anything in Storage Manager or anywhere to set it up as new default tablespace?

Thanks a lot!!
1 Solution
The default tablespace is set at creation time or by altering the user. If you default tablespace is corrupted you should try to restore and recover it. If you want to change the default tablespace use

ALTER user DEFAULT TABLESPACE my_tablespace;

You can create a new tablespace and drop the old one with

CREATE TABLESPACE new_tablespace
DATAFILE 'my_data_file' SIZE 25M
DEFAULT STORAGE ( initial 8k next 2k pctincrease 0);

ALTER user DEFAULT TABLESPACE new_tablespace;

DROP TABLESPACE old_tablespace;

*** remenber to delete the old datafile to free up your disk space

Good Luck
If ur talking about System tablespace then i am afraid it sounds trouble, u will probablyhave to retore from a previous backup.
hi mingichu!!!

Can u explain y did u say in your question that your tablespace is corrupt ... cause if u r having any error then please mention it in your question ...

Other then that if u want to create another default tablespace ... then u can by connecting through system user... and in SQLPLUS
CREATE TABLESPACE <new_tablespace_name> LOGGING DATAFILE '<path for Database file>' SIZE 200M EXTENT MANAGEMENT LOCAL;

and modify your user ....
ALTER USER <your_username>DEFAULT TABLESPACE <new_tablespace_name>;

if u have further problem please do ask ...

There has been no activity in this question in quite some time, and it looks like it has been abandoned. As part of our ongoing mission to clean up the topic areas, a Moderator will finalize this question within the next seven (7) days. At that time, either I or one of the other Moderators will force/accept the comment of

DO NOT ACCEPT THIS COMMENT AS AN ANSWER. If you have further comments on this question or the recommendation, please leave them here.


Some of these questions have been open for some time, and records indicate you logged in as recently as this week. Please resolve them appropriately as soon as possible. Continued disregard of your open questions will result in the force/acceptance of a comment as an answer; other actions affecting your account may also be taken. I will revisit these questions in approximately seven (7) days.



Community Support Moderator
Experts Exchange
Zero response from anyone, finalized.
Moondancer - EE Moderator
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

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