drop schema or tablespace

[st2000]

Hi.. I have 2 tablespaces called siebel_data and siebel_index which are 1 and 2 GB each respectively. I have 1 schema only in these 2 tablespaces called siebel. Siebel has about 1500 tables and 7500 indexes occupying 250MB of siebel_data and 900mb of siebel_index.. Due to some mess, I have to drop the schema and re-create and it took almost 2 hours.. Can I do this?? Drop the tablespace siebel_data and siebel_index and recreate them again, so I can create the schema and populate the schema.. I don't know if this is a good alternative.. or what impacts i might have on the db..
Oracle 817 on IBM Aix 4.3 rs/6000

Also, I just took a logical backup of the siebel schema and it came up to 130MB approximately.. What happens if I run the import without dropping the schema siebel, in terms of data.. Immediately after I take an export and as well after playing with the schema for some time and when I feel messy simply run the import.. Can the running of import bring it back to where it was before export was taken?? In regards to structure I can choose ignore for existance, but data....

Thanks, ST2000

[grjohnson]

What you are saying sounds pretty good. I mean, taking a logical export of the data i.e. export via the USER, then

DROP USER seibel CASACADE;

Will get rid of the user and ANY objects it owns. And then, drop the tablespaces, recreate them and then import the USER back in. This sounds fine.... as long as that is what you want to do.

You might want to investigate the COMPRESS=Y option, if you are doing the export import to minimise extent fragmentation.

Cheers,

[sambavan]

But remember on doing a COMPRESS=Y would alter your storage parameter specifications... Since you are planning to drop the schema, as johnson pointed out, take a user level export and then drop and recreate.

As far as importing to the existing schema wouldn't fix any of your corrupted data. In this case I wouldn't think that you would have to drop the tablespace, instead just Drop the user with the cascade to drop the schema and then do an import and it would solve the issue. Remember to note down the user permissions and etc... Your import will not take the dump on the tiggers if any were to exist...

Sam

[st2000]

The whole concern of dropping the tablespace instead of dropping the schema was to save time and I wanted to know if it is a good idea to do that.. My dropping schema is taking 2 hours Vs dropping of tablespaces, create user and Import schema (not estimated yet).. If the second approach is swift and safe, well I want to adapt that.. Need comments ??
Thanks, ST2000

[ratcheer]

I have a large, complex schema (1800 tables, much larger number of indexes, plus constraints, sequences, triggers, packages, etc).

Instead of dropping the schema, I have a script that generates drops for all the objects in the schema.

First, I export all the tables. Then, I export the user with rows=n. Then I drop all the objects, coalesce the tablespaces, import the tables, and import the user. Finally, I run scripts to check for invalid objects and recompile, as needed.

It always works like a charm. The hardest part was devising the script that generates the drop commands - they have to be in the correct order to avoid certain dependencies.

Finally, I ALWAYS use compress=n. I work too hard to have compress=y mess up my storage management. Also, just try finding a contiguous 4GB extent for an object.

[grjohnson]

2 Things.

1).
DROP USER CASCADE;

is a very CLEAN way to remove a user and all associated objects, is leaves nothing to chance of forgetting to remove any objects. It's the safest and defianatly the one I prefer.

2). I'm not sure what Oracle 9i does but in 8i you won't get a single extents 4 Gb object using the compress = Y.

eg. If you currently have an object that has 1 MB extents and is currently 2500 extents (2500MB). Then you use the COMPRESS = Y. Drop the object and then imported the object back in, you'd end up with the object with:

INITIAL 2000 GB NEXT 1 M and EXTENTS 501;

That my friends, is a fact... don't ya love Oracle sometimes.

Therefore, sure you can change the next extent size AFTER the object is imported... BUT, the only way to reduce the number of extents is to completly recreate the object.

Cheers peps