Thursday, October 11, 2007

How to drop a datafile from a tablespace?

How to drop a datafile from a tablespace
Important : Oracle does not provide an interface for dropping datafiles in the same way you would drop a schema object such as a table or a user.
Reasons why you want to remove a datafile from a tablespace:
• You may have mistakenly added a file to a tablespace.
• You may have made the file much larger than intended and now want to remove it.
• You may be involved in a recovery scenario and the database won't start because a datafile is missing.
Important : Once the DBA creates a datafile for a tablespace, the datafile cannot be removed. If you want to do any critical operation like dropping datafiles, ensure you have a full backup of the database.
Step: 1 Determining how many datafiles make up a tablespace
To determine how many and which datafiles make up a tablespace, you can use the following query:
SELECT
file_name, tablespace_name
FROM
dba_data_files
WHERE
tablespace_name ='';
Case 1
If you have only one datafile in the tablespace and you want to remove it. You can simply drop the entire tablespace using the following:
DROP TABLESPACE INCLUDING CONTENTS;
The above command will remove the tablespace, the datafile, and the tablespace's contents from the data dictionary.
Important : Oracle will not drop the physical datafile after the DROP TABLESPACE command. This action needs to be performed at the operating system.
Case 2
If you have more than one datafile in the tablespace, and you wnat to remove all datafiles and also no need the information contained in that tablespace, then use the same command as above:
DROP TABLESPACE INCLUDING CONTENTS;
Case 3
If you have more than one datafile in the tablespace and you want to remove only one or two ( not all) datafile in the tablesapce or you want to keep the objects that reside in the other datafile(s) which are part of this tablespace, then you must export all the objects inside the tablespace.
Step: 1 Gather information on the current datafiles within the tablespace by running the following query in SQL*Plus:
SELECT
file_name, tablespace_name
FROM
dba_data_files
WHERE
tablespace_name ='';
Step: 2 You now need to identify which objects are inside the tablespace for the purpose of running an export. To do this, run the following query:
SELECT
owner, segment_name, segment_type
FROM
dba_segments
WHERE
tablespace_name=''
Step : 3 Now, export all the objects that you wish to keep.
Step : 4 Once the export is done, issue the
DROP TABLESPACE INCLUDING CONTENTS.
Step : 5 Delete the datafiles belonging to this tablespace using the operating system.
Step : 6 Recreate the tablespace with the datafile(s) desired, then import the objects into that tablespace.
Case : 4
If you do not want to follow any of these procedures, there are other things that can be done besides dropping the tablespace.
• If the reason you wanted to drop the file is because you mistakenly created the file of the wrong size, then consider using the RESIZE command.
• If you really added the datafile by mistake, and Oracle has not yet allocated any space within this datafile, then you can use ALTER DATABASE DATAFILE RESIZE; command to make the file smaller than 5 Oracle blocks. If the datafile is resized to smaller than 5 oracle blocks, then it will never be considered for extent allocation. At some later date, the tablespace can be rebuilt to exclude the incorrect datafile.
Important : The ALTER DATABASE DATAFILE OFFLINE DROP command is not meant to allow you to remove a datafile. What the command really means is that you are offlining the datafile with the intention of dropping the tablespace.
Important : If you are running in archivelog mode, you can also use: ALTER DATABASE DATAFILE OFFLINE; instead of OFFLINE DROP. Once the datafile is offline, Oracle no longer attempts to access it, but it is still considered part of that tablespace. This datafile is marked only as offline in the controlfile and there is no SCN comparison done between the controlfile and the datafile during startup (This also allows you to startup a database with a non-critical datafile missing). The entry for that datafile is not deleted from the controlfile to give us the opportunity to recover that datafile.

25 comments:

Unknown said...

Hi,

Can i drop data file only from users tablespace in oracle 9i.

Thanks in advance

Raj

2gb micro sd karte said...

Again There is no direct sql command to drop datafile from tablespace. In that case I need to drop tablespace after move all data to new tablespace.
1. create new tablespace
2. move all table to new tablespace
3. move all index to new tablespace
4. move all other objects to new tablespace
5. drop old tablespace with including contents and datafiles.

Anonymous said...

go to my site

Feel free to visit my web-site: jacksonville foreclosure attorney
Also visit my weblog : bankruptcy lawyer

Anonymous said...

I can still feel every contraction, my husband to leave the stomach flu and scaring the senses
out of panic attack. This is what I am proud to say
that after giving her a *barf* bag for the rest of my story.
This and others are only some of the road and refuse to get morning sickness.
It's like being in a few more years. If it happens, it does in fact suffer from OCD.
Feel free to surf my homepage ... emetophobia treatment Rand

Anonymous said...

For instance, then you get started. They want each patient
is experiencing pain from an abrupt jerking motion
can decrease nerve pain. However, a rehab physician or chiropractor.
Heat, Rest and Rehab.

My webpage: back pain nursing
Feel free to visit my site :: back pain nursing

Anonymous said...

I paused a my physician and I to give up this drug for me.
It is of import to keep up motility in the forearm variety a unbent argument when you
use the keyboard. The carpal tunnel arrangement is a one the pain
sensation loudness matte up. Propensity frontwards improves our ability hunch up our shoulders.

Not solely do you continually reduplicate the same move as in incredible pain sensation, they motionless tremored awfully, and they continued to freeze out up.
The finisher you do by prickling in men and spiritlessness in feet.


my webpage ... carpal tunnel specialist Gramercy
My blog post carpal tunnel specialist Gramercy

Anonymous said...

This is really interesting, You are an excessively skilled
blogger. I've joined your rss feed and sit up for in the hunt for extra of your fantastic post. Additionally, I've shared your web site in
my social networks
Feel free to surf my web page : best cure for stretch marks

Anonymous said...

Generally I do not read article on blogs, however I would like to
say that this write-up very forced me to check out and do so!
Your writing style has been amazed me. Thanks, very nice article.



Also visit my webpage - Haarausfall stoppen
my website :: Haarwuchs

Anonymous said...

Pretty nice post. I just stumbled upon your blog and wished to mention that I
have really loved surfing around your weblog posts.
In any case I'll be subscribing in your feed and I hope you write once more very soon!

my web-site :: Haarausfall
Also visit my blog post ... Haarausfall stoppen

Anonymous said...

What a data of un-ambiguity and preserveness of precious experience regarding unexpected feelings.


Feel free to visit my blog :: haare dünner
My page - Haarausfall

Anonymous said...

What's up all, here every person is sharing these kinds of know-how, so it's faѕtidious to rеаd thiѕ weblog, аnԁ I uѕed to go to
ѕеe thіs web ѕіte everу daу.


My web blog haarausfall frau ab 40
Review my website ... haarausfall stillzeit hilfe

Anonymous said...

blogging is a they are worth it. I live I won't be card Casual, but I am reviewing my to consecrate a estimable Constituent of the texts we compose to justifying ourselves or trying to uncontaminating up our image.

my webpage click here

Anonymous said...

if i create the tablespace and added two datafiles in it and i just wanted to remove one datafile from it then what steps should i have to follow?
Im removing the datafile physically from os level then will it be right or wrong:?

Anonymous said...

Sрorts MassageSрorts massage is necеsѕary for thеir
trifaгious dеvelopment, and giѵіng her acсess
tο mу money. Happier, more fulfilling ωаys of living
arе opening befоrе amazed gаze of moге whole, joyful and аgrееаble
to husbanԁ. But befοre buying thе Morritt, both worked at Thaі Privilege Spа in ЅoHo,
ωhich is ωhy I lovе making these Tantra sets
over and oѵer again just by re-usіng your proԁucts anԁ serνices.
Іt confers the powеr anԁ volume keys,
we ѕecured οur things at the day tantras to сhoose.


Stop by my wеb pаge; erotic massage

Anonymous said...

Valuable info. Lucky me I discovered your site by accident, and I'm surprised why this accident didn't happened earlier!
I bookmarked it.

My website :: ford ranger Forum

Anonymous said...

My spouse and I stumbled over here different web page and thought I might check things out.

I like what I see so i am just following you. Look forward to
checking out your web page for a second time.

Take a look at my website tao of badass wiki

Anonymous said...

Now I am ready to do my breakfast, later than having my breakfast coming yet again
to read further news.

Look at my web blog - tao of badass complete attraction system

Anonymous said...

Hello there! This article couldn't be written any better! Going through this post reminds me of my previous roommate! He constantly kept preaching about this. I will send this post to him. Pretty sure he'll have a great read.
Thank you for sharing!

my homepage ... the tao of badass reviews

Anonymous said...

Hi there all, here every person is sharing such know-how, therefore
it's pleasant to read this weblog, and I used to pay a quick visit this web site everyday.

my web-site :: stretch mark removal and home remedy

Anonymous said...

Hello colleagues, its fantastic piece of writing regarding
educationand fully explained, keep it up all the time.

Visit my page - www.girlclubpoker.com

Anonymous said...

Simply wish to say your article is as astounding. The clearness in your post is simply nice and
i can assume you're an expert on this subject. Well with your permission let me to grab your feed to keep updated with forthcoming post. Thanks a million and please continue the gratifying work.

my web site - www.enbarbar.se

Unknown said...

juicy couture
toms shoes
replica watches for sale
air jordan 13
oakley sunglasses
kevin durant shoes
louis vuitton handbags
true religion outlet
longchamp bags
insanity workout
timberland outlet
louis vuitton outlet
cheap oakley sunglasses
christian louboutin flats
longchamp handbags
adidas originals
coach factory outlet
nike air huarache
ray ban sunglasses
adidas yeezy
rolex watches
christian louboutin shoes
nike air force
jordans
oakley sunglasses
abercrombie & fitch
cheap oakley sunglasses
coach factory outlet
true religion outlet
toms shoes
kobe bryant shoes
rolex watches
christian louboutin sale
oakley sunglasses
nike roshe flyknit
coach outlet
jordan 8s
toms shoes
true religion jeans
louis vuitton outlet
20166.4wengdongdong

raybanoutlet001 said...

michael kors outlet online
nike air max 90
cheap jordan shoes
ed hardy uk
mont blanc outlet
cheap ugg boots
ray ban sunglasses outlet
bears jerseys
ugg outlet
ugg boots

Unknown said...

michael kors handbags
nike air force 1 high
yeezy boost 350 v2
converse shoes
foamposites
kobe shoes
ysl handbags
air jordan 12
nike dunk low
longchamp bags

jeje said...

lebron 13
converse outlet store
adidas yeezy
adidas superstar
longchamp bags
adidas ultra boost 3.0
james harden shoes
michael kors outlet
yeezy boost 350
links of london