Is there a way without shutting down the server and restarting to get space back from files created when I use global temporary tables? In this case I load 5 million rows into a global temporary table, work with it then delete it. I see a file created on my system that is about 500 meg, if I do the load and work again it's a gig and so on till I run out of space. I check to make sure that at some point the table has 0 rows in it but the size of the file used by the table is still the same. Is there a command/switch/flag I can set that will cause the file to give back the space and reduce in size without having to bounce the system?
Thank You Matthew
The file you speak of is called the "temporary dbspace". While the database is running, it will grow but not shrink. However, deletions should cause space to be freed up for reuse within the file, so when you delete the rows and then re-insert them, the file should not grow... I have verified this behavior with version 22.214.171.12487 (see below).
Show us the code you are using to create the table, and to delete the rows.
-- temporary file size 1M, free space 248k CREATE GLOBAL TEMPORARY TABLE fat ( fat VARCHAR ( 100 ) ) ON COMMIT PRESERVE ROWS; INSERT fat SELECT REPEAT ( 'x', 100 ) FROM sa_rowgenerator ( 1, 1000000, 1 ); COMMIT; -- temporary file size 237M, free space 131M DELETE fat; COMMIT; -- temporary file size 237M, free space 236M INSERT fat SELECT REPEAT ( 'x', 100 ) FROM sa_rowgenerator ( 1, 1000000, 1 ); COMMIT; -- temporary file size 237M, free space 131M
answered 23 Aug '13, 16:11