Solved

Set up or drop Default tablespace

Posted on 2001-08-30
5
477 Views
Last Modified: 2008-03-10
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!!
0
Comment
Question by:mingichu
5 Comments
 
LVL 1

Accepted Solution

by:
dbrower earned 50 total points
ID: 6440528
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
0
 
LVL 3

Expert Comment

by:UsamaMunir
ID: 6440563
If ur talking about System tablespace then i am afraid it sounds trouble, u will probablyhave to retore from a previous backup.
0
 

Expert Comment

by:FarazMSyed
ID: 6443859
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 ...

regards
Faraz
0
 
LVL 5

Expert Comment

by:Netminder
ID: 6767033
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.

mingichu,

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.

http://www.experts-exchange.com/jsp/qShow.jsp?ta=oracle&qid=20260860
http://www.experts-exchange.com/jsp/qShow.jsp?ta=oracle&qid=20176286
http://www.experts-exchange.com/jsp/qShow.jsp?ta=vbdatabases&qid=20156941
http://www.experts-exchange.com/jsp/qShow.jsp?ta=lotusnotes&qid=20138024

Thanks,

Netminder
Community Support Moderator
Experts Exchange
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 7058222
Zero response from anyone, finalized.
Moondancer - EE Moderator
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
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
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

746 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now