Re: Altering table owners

On Mon, 9 Feb 1998 18:10:12 -0800, in article <6bof04$57t$1@news.xmission.com>, "Leffler, Jonathan" <jleffler@visa.com> wrote: : :This has been covered in 1997 in this news group. :Please investigate DejaNews - try looking for stuff I said, 'cos I'm sure I :commented on it last year. Nearly sure - I may have said stuff off-line :since you can play games but it is totally unsupported. : :Yours, :Jonathan Leffler (jleffler@visa.com) #include <do.not.do.it.if.you.need.to.ask.h> : :---------- :From: Alastair Kimbell[SMTP:al@ackone.demon.co.uk] :Sent: Monday, February 09, 1998 7:03 AM : :I have to change a load of table owners from user1, user2 and user3 to :user4 as part of tidying up some areas of our system :We are using Inf 7.23 SE :The only way I know so far is to export the database, hack the :export file and alter the owners from user1, 2 & 3 to user4 then :import the database using the new sql script :However I have noticed the owner of a table is stored in the :system table systables : :select tabname, owner, tabid from systables :order by tabid : :Can I alter this using :UPDATE systables SET owner = User4 where tabname = tablename : :Is this a good idea, are there any implications I am missing :This will save exporting and reimporting my whole database It'd be safer to create new empty tables of the same name as the old with the new owner then do a: insert into newowner.tablename select * from oldowner.tablename Once the data is in the new table, drop the old and THEN create indexes on the new table. You _can_ change the owner the other way (in systables among other places) as informix but one mistake and your data is toast. -- gburnore@databasix dot com or gburnore@netcom dot com --------------------------------------------------------------------------- How you look depends on where you go. --------------------------------------------------------------------------- Gary L. Burnore | '۳'''''''ۺ''''''''''۳ | '۳'''''''ۺ''''''''''۳ DOH! | '۳'''''''ۺ''''''''''۳ | '۳ 3 4 1 4 2 '' 6 9 0 6 9 '۳ spamgard(tm): zamboni | Official Proof of Purchase =========================================================================== PGPprint: C63B CF4E 1B71 4D7E C6F8 AF4E 338D 5CB4 KeyID: 0x0F7EDBD9 (RSA) finger gburnore@netcom.com for public key ---------------------------------------------------------------------------