Dropping Large Tablespace

[edsperez]

Does dropping a tablespace generates archive logs close to the size of tablespace being dropped?

Say a 100Gb tablespace, will it generate 100 Gb archive logs or just the information that says that the tablespace got dropped?

[gandolf989]

I wouldn't think that dropping a 100GB tablespace would generate 100GB worth of redo, even if it is nearly full. I'm thinking that dropping a tablespace is unrecoverable, and thus redo would be minimal. However, you should really try it, on a test system of course.

[edsperez]

This is also what I am thingking, the thing is, it's not too often that you drop large tablespace.

[PAVB]

I assume you are thinking in using...

drop tablespace mytablespace including contents and datafiles;

... if this is the case Oracle will:

1- drop all objects in the target tablespace(*)
2- drop any index or lob segment in other tablespaces that are related to dropped objects in the target tablespace.
3- drop target tablespace.
4- drop datafiles related to target tablespace.

As you might remember you do not have to worry about how much redo you are generating during a drop operation

(*) only exception is when some or the partitions of a partitioned object are sitting in target tablespace but not all of them. If this is the case -thanks god- Oracle would refuse to drop the tablespace .

[abhaysk]

(*) only exception is when some or the partitions of a partitioned object are sitting in target tablespace but not all of them. If this is the case -thanks god- Oracle would refuse to drop the tablespace .

i wonder why, when it can drop dependent structures, why not this?

Rgds
Abhay.

[PAVB]

i wonder why, when it can drop dependent structures, why not this?

Well, this is actually a question for Angelo but I find current behavior as being a very nice feature to keep.

Other partitions of the same table/index are not actually "dependent structures", they are part of the same table/index structure, I would say they are "peer structures".

Dropping "dependent structures" ensures consistency.

Dropping "peer structures" would destroy consistency.

Does it makes sense?

[abhaysk]

Well when oracle can drop objects like index/lob/nested table?/etc (dependent structures), why not drop a logical object which is part of TS in question. When we are dropping a TS we would obiviously know what are the objects in it. And if we have taken a decesion to drop, then we know that we dont need those objects any more.

- Abhay.

[PAVB]

You are not listening.

An index depends on a table.

A partition table does not depends on other partition of the same table, there is no dependency relationship, they are peers.

That being said, if you do not like the solution -which I find brilliant, go and talk to Angelo.

[abhaysk]

I do understand they are not dependents, all i was making here is a different point.. may be it does not make sense to you.. thats ok..