Generate Database Script with Existing Database

[Kishore]

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.

[rsuri]

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

[Kishore]

Hi Suri,

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

[rsuri]

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

[jmodic]

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.

[Kishore]

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.

[mbaliga]

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

[Kishore]

Thanks every one.