Generate Database Script with Existing Database
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Generate Database Script with Existing Database

  1. #1
    Join Date
    Mar 2000
    Location
    CA
    Posts
    317
    Hi

    I would like to generate Database Script with Existing database as I am planning to rebuild the database which I can use it while regenerating.

    Any ideas. As I would like to use same Storage parameters and everything same. Cloning the database can be done, but I don't want to do that as there is a lot of fragmentation in System tablespace which I can do it only by rebulding the database.

    Any idea's are appreciated.

    Thanks
    Kishore Kumar

  2. #2
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    Export/Import should take care of the fragmentation.
    So, you cna clone the database (so that you have the same storage parameters for all the objects) and then export/import to import in the existing tables.

    - Rajeev
    Rajeev Suri

  3. #3
    Join Date
    Mar 2000
    Location
    CA
    Posts
    317
    Hi Suri,

    Can I export and import for SYSTEM tablespace also to fix the fragmentation issue?

    Thanks
    Kishore Kumar

  4. #4
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    well, because Export can be done only at a "Database", "User" or "Table" level, you will have to do it at the full database level to defragment the SYS objects residing in SYSTEM tablespace.

    - Rajeev
    Rajeev Suri

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    No, no, SYS's objects are never exported, so import can not possibly defragment the SYSTEM tablespace (on the contrary, it can only additionaly fragment it).

    If you realy wan't to defragment the SYSTEM tablespace, your only option is to recreate the database. In your case, I would:
    1. Perform full export of the existing database.
    2. Drop the old database and create the new one. The best way would be to install only the minimum of the database, that is only the SYSTEM tablespace with user SYS, alternatively also user SYSTEM.
    3. Perform FULL import which will take care of the rest, including creating tablespaces, rollback segments, users, etc.

    Alternatively, if you want to have the tablespaces in different locations in your new DB, you would have to create them manually before performing the import.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Mar 2000
    Location
    CA
    Posts
    317
    Thanks Jmodic

    That's what I thougt that, I cannot fix the Fragmentation for SYSTEM tablespace without rebuilding the database. But I want to generate the CREATE database script using the existing database to reflect the present database which I can use, is there any way of doing it or just EXPORT/IMPORT with creating the new database with creating minimum tablespaces.
    Thanks
    Kishore Kumar

  7. #7
    Join Date
    Jan 2001
    Posts
    126
    Hi,

    Export/Import is the right method. If you want to re-layout the database, take an inventory of the tablespaces & datafiles of your existing database. Then see if you can merge datafiles of some tablespaces. You can re-distribute some of the datafiles on different devices.

    Get a controlfile script from existing database. From this you can check the parameter used, such as max_datafiles, max-log_history etc.

    Now create the new database, create all the tablespaces and then import the database.

    Baliga

  8. #8
    Join Date
    Mar 2000
    Location
    CA
    Posts
    317
    Thanks every one.
    Thanks
    Kishore Kumar

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