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

asked 23 Aug '13, 15:21

Matthew%20Kelly's gravatar image

Matthew Kelly
31113
accept rate: 0%

edited 24 Aug '13, 04:34

Volker%20Barth's gravatar image

Volker Barth
30.6k305455662


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 11.0.1.2587 (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 
permanent link

answered 23 Aug '13, 16:11

Breck%20Carter's gravatar image

Breck Carter
25.7k428592852
accept rate: 20%

More facts on that in this FAQ:

How to reduce the size of temporary files

(24 Aug '13, 04:37) Volker Barth

Thank you,

I'll try the solution above, one thing I wanted to point out is I didn't see a spot for it but I'm not on 11 I'm running on ASA 12.0.1 ebf 3942. The results you posted is what I thought I should be seeing but at this time am not. I see the space in tempdb free up but when I right to it a second time more space is added to tempdb. The free space isn't used. so the tempdb file grows and grows. Over time my server will go down. Now I only see this growing when I"m uinsg global temp tables if I use tempdb for any other reason it behaves normally.

Matthew

(26 Aug '13, 08:13) Matthew Kelly
Replies hidden

The code shown is not a "solution", just a demonstration that free space is re-used.

The test used V11 because you tagged your question "sa-11"... the results should be the same with V12.

To repeat, "Show us the code you are using to create the table, and to delete the rows"... without seeing your code, all responses are pointless guesswork.

(26 Aug '13, 11:23) Breck Carter
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×137
×20
×12

question asked: 23 Aug '13, 15:21

question was seen: 850 times

last updated: 26 Aug '13, 11:25