Creating an empty database with a different user

[mmannion]

Hi,

I am trying to create a structural copy of a database with a different dba user. I have a number of database objects owned by different users e.g. tables and views so I havent been able to get copydb to work. I dont want the data in the database so I cant use unloaddb.

Is there a way of doing this?

Thanks,
Martin

[rhann]

Use unloaddb. It won't give you any data. It generates two scripts, one of which (copy.out) would give you the data if you were to run it. But unloaddb also generates the copy.in script with all the DDL for all the database objects. Edit it to change \nocontinue to \continue and to change the effective session IDs as appropriate for your purpose. Run it in an empty directory so all the COPY...FROM statements fail.

[kschendel]

Do you want all of the variously-owned tables/views/etc in the source database to be duplicated and owned by the single alternate-user in the result database? That's what it sounds like to me.

I can't offhand think of a one-step way to do it. The simplest, assuming that the database data isn't large enough to be a problem, might be to run unloaddb and run the resulting unload.ing script. Then, edit the copy.in and take out the "set session authorization foo" statements for all of the object owners except for the ones that set the session to the original dba. Change those set-session statements to reflect the new dba. Edit the object owner names for all of the non-table objects (views, db procedures, etc) to reflect the new dba. Cat /dev/null to the copy output files for everything EXCEPT the iixxx copy files (i.e. you now have zero-length copy-in data files). Create the new database with createdb -u and run reload.ing.

If you have character-forms objects (reports, forms, etc), they can pose a challenge since the catalog entries include the owner names. Your best bet there is probably to unload the objects using forms tools such as copyform, copyapp out, etc and reload them into the new database as the new user.

Anyone have any better suggestions?