RE: How waiting that noboby is using a table in order to drop it .
Sebastien Cottin wrote:
Hello everybody,
>
> I have a small question in SQL (transactional Database) :
>
> I need to wait that noboby is using a table in order to drop it .
> I don't know how to do it .
>
> thank's
>
> Sebastien Cottin.
>
You've obviously discovered that setting LOCK MODE doesn't help.
Try this:
a) sit up all night running the command until it works, or
b) identify the part number of the table (from systables, assuming no =
fragmentation involved), and convert it to hexadecimal, lower case, no =
leading zeroes:
SELECT HEX(partnum) FROM systables WHERE tabname =3D "table-name";
result 0x0D00036 (or whatever)
converted, this is d00036
Then execute something like this:
until [ $(onstat -t | grep -c d00036) -eq 0 ]
do
sleep 30
done
dbaccess -e $DBNAME << EOSQL
ALTER TABLE ....EOSQL
The onstat/grep combination identifies when the tablespace is no longer =
open, ie no one is using the table.
If you can get away with it, running "REVOKE ALL ON table-name FROM =
PUBLIC" before all of the above ensures that only the current queries can =
complete, but no new ones can start. Don't forget to GRANT privileges =
again afterwards.....
HTH
RET
+------------------------------------------+
| Richard Thomas |
| DBA - Marketing Information Systems |
| Optus IT |
| email: richard_thomas@yes.optus.com.au |
| Ph: +61 2 9342 7188 |
| "My opinions are my opinions" |
+------------------------------------------+