Главная : Informix : Рецепты

Получение списка временных таблиц

Полный список временных таблиц без указания принадлежности таблиц к сессиям можно получить запросом:

SET isolation TO dirty read;

SELECT
	t2.tabname as table_name,
	t2.owner,
	t2.dbsname as dbname,
	decode(t4.is_logging, 1, 'Y', 'N') as db_type,
	t3.name as dbspace,
	CURRENT - DBINFO('UTC_TO_DATETIME', ti_created) as duration,
	decode(HEX(MOD(ti_flags, 256) / 16), HEX(6), 'Y', 'N') as table_type,
	ti_npused as used_pages,
	ti_nptotal as total_pages,
	ti_nrows as number_of_rows,
	ti_npused * ti_pagesize as used_size_bytes,
	ti_nptotal * ti_pagesize as total_size_bytes
FROM	sysmaster:'informix'.systabinfo t1,
	sysmaster:'informix'.systabnames t2,
	sysmaster:'informix'.sysdbspaces t3,
	sysmaster:'informix'.sysdatabases t4
WHERE	t2.partnum = ti_partnum
	AND t4.name = t2.dbsname
	AND t3.dbsnum = TRUNC(t2.partnum / 1048576)
	AND (	HEX(MOD(ti_flags, 256) / 16) = HEX(6)
		OR HEX(MOD(ti_flags, 256) / 16) = HEX(2))
ORDER BY total_pages DESC;