Re: DROP TABLESPACE efficiently??

dominica_at_gmail.com wrote:
> Hi Joel,
>
> What is DMT and LMT ?

You've presumably now seen the definition, the practical consideration is that if you have a DMT and are deleting tables with 1000's of extents while other users need to extend, that can grind your system to a halt. You should See Your DBA to determine if that might be an issue. It is important for you to post information like this and the Oracle version for people to give reasonable suggestions for you. Note that truncate defaults to "drop storage," so if you have 1000's of extents...

> I am on Solaris , E4500 machine.

Too long since I've been on Solaris, can't relate to it.

>
> My work would like to do it , when Oracle is up and other people are
> on the machine.
> The thing is, there is a contract from my work and
> with those ASP customers that oracle could only down for
> very short time, once a week.
>
> So I hope to do my work , just like after 8:00pm some night, but
still
> let user to see or do things they like to do. (this is ideal).
> Basically, I don't want to request a down time.

You might try it on a test machine... if it impacts too much, just do a few tables at a time in the middle of the night.

In general, I've found just dropping the user cascade and then the ts including contents during a light load time works fine, but YMMV. You shouldn't need actual down time, just a time when people won't complain so much about a slow system. If you have a proper modern configuration, it shouldn't take all that long.

>
> How come you talk about "import" back data? (just curious).
> I need to do some time estimation too.

That's just what my requirement is, refreshing some schemata, not just dropping them.

jg

--
@home.com is bogus.
"perl is an OK operating system, but it lacks a lightweight scripting
language." - Walter Dnes