drop schema or tablespace
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: drop schema or tablespace

  1. #1
    Join Date
    Oct 2000
    Posts
    449
    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

  2. #2
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    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,
    OCP 8i, 9i DBA
    Brisbane Australia

  3. #3
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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
    Thanx
    Sam



    Life is a journey, not a destination!


  4. #4
    Join Date
    Oct 2000
    Posts
    449
    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

  5. #5
    Join Date
    Oct 2001
    Location
    Pelham, AL
    Posts
    40

    Lightbulb

    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.

  6. #6
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    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
    OCP 8i, 9i DBA
    Brisbane Australia

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width