RE: Dropping Temporary Dbspaces

Thanks, Mark! I went ahead and built the instance without the tempdbs. I'll migrate the tempdbs chunk later in the week when I have the planned downtime. Best regards, Edmund Nigel Gall <-- Making IT happen! --> Senior Information Systems Specialist Process Plant Services Limited Atlantic Avenue, Point Lisas Industrial Estate Point Lisas, Couva, Trinidad & Tobago, W.I. Tel: (868) 636 2906 x 274 Fax: (868) 679 3770 -----Original Message----- From: Mark D. Stock [mailto:mdstock@mydas.freeserve.co.uk] Sent: Tuesday, November 21, 2000 7:18 PM To: Nigel Gall Cc: 'Informix List' Subject: Re: Dropping Temporary Dbspaces Nigel Gall wrote: > > Hi! Can I drop a temporary dbspace without re-initializing the instance? > > In reviewing the manual for IDS 7.3, on page 14-19 it states that one should > run oncheck -pe to examine if any logs or tables reside in the dbspace > before removing. When I run it, I get the following for the tempdbs I wish > to remove (it has only one chunk): > DBspace Usage Report: tempdbs2 Owner: informix Created: 12/10/1998 > > Chunk: 4 /usr/informix/chunks/tchk2a Size Used Free > 1000000 1201 998799 > > Disk usage for Chunk 4 Start Length > ------------------------------------------- --------- --------- > OTHER RESERVED Pages 0 2 > CHUNK FREE LIST PAGE 2 1 > TBLSPACE TBLSPACE 3 50 > FREE 901 1176 > TBLSPACE TBLSPACE 2077 50 > FREE 2127 2400 > TBLSPACE TBLSPACE 4527 50 > FREE 4577 3920 > TBLSPACE TBLSPACE 8497 50 > FREE 8547 1600 > TBLSPACE TBLSPACE 10147 50 > FREE 10197 1600 > TBLSPACE TBLSPACE 11797 50 > FREE 11847 1600 > TBLSPACE TBLSPACE 13447 50 > FREE 13497 98650 > > This indicates to me that there are some temporary tables in the tempdbs. What indicates this? All I see is free space and tablespace data. > On page 13-20 of the same manual it says that the tempdbs is re-initialized > everytime the server is initialized (I'm reading this to mean first-time > disk initialization as well as normal, non-destructive startup). So > presumably, if the instance is re-initialized, I could drop the dbspace > without problems in quiescent mode. You can drop any dbspace while in on-line mode. However, I would not advise it if you have that dbspace listed in your DBSPACETEMP variable. Unless you can override this variable with the environment variable for all users. I have moved a temp dbspace while users were active before. That required that no one was using it and I re-created it quickly before anyone noticed (tried to use it). ;-) > I'd like to setup a second instance on my production server that mirrors the > existing instance in terms of disk layout, but I used all the tempdbs chunks > for the first instance. So I'd like to drop one of the tempdbs's and > reassign the freed chunk to the second instance's tempdbs. However, if > possible, I'd like to do this without bringing down my first instance. If you can stop people using these dbspaces, then it is possible. Otherwise you will have to bring the database server down. Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@mydas.freeserve.co.uk |//////// /| | http://www.informix.com http://www.informixhandbook.com |///// / //| | http://www.iiug.org +-----------------------------------+//// / ///| | |This email will self-destruct in |/// / ////| | |10 sec. If you received this email |// / /////| | |in error, sorry about the mess. |/ ////////| +----------------------+-----------------------------------+-----------+