| free tablespace [message #134717] |
Fri, 26 August 2005 06:59  |
prashanth da
Messages: 3
Registered: August 2005
|
Junior Member |
|
|
Hello,
Please let me know if there is an Oracle command to check if a table is using tablespace which could be released.
If there is no Oracle command, do provide me a stored procedure for the same.
Thanks in advance..
Prashanth
|
|
|
|
|
|
| Re: free tablespace [message #134970 is a reply to message #134724] |
Mon, 29 August 2005 04:38  |
prashanth da
Messages: 3
Registered: August 2005
|
Junior Member |
|
|
Create a tablespace with size 10MB.
Create a table inside the tablespace.
Add 10000 records into the table.
The query "select TABLESPACE_NAME, BYTES from sys.dba_data_files where TABLESPACE_NAME='' "
gives the total bytes in the tablespace.
The query "select TABLESPACE_NAME, TOTAL_BYTES from sys.dba_free_space_coalesced where TABLESPACE_NAME='' "
gives the free space that is available or left over in the tablespace.
Delete 2000 records from the table.
Run this query.
"select TABLESPACE_NAME, TOTAL_BYTES from sys.dba_free_space_coalesced where TABLESPACE_NAME='' "
The number of free tablespace (i.e bytes) displayed will be the same as the previous query results.
Perform the following.
ALTER TABLE <table name> ENABLE ROW MOVEMENT.
ALTER TABLE <table name> SHRINK SPACE CASCADE.
Run this query.
"select TABLESPACE_NAME, TOTAL_BYTES from sys.dba_free_space_coalesced where TABLESPACE_NAME='' "
The number of free tablespace displayed now will be more.
[This is the additional free tablespace that a table had which could be reclaimed using the SHRINK command]
I need an Oracle command or a stored procedure that gives me this. (To check if a table is using tablespace which could be released.)
Thanks & Best Regards
prashanth
|
|
|
|
|
|
| Re: free tablespace [message #135013 is a reply to message #135009] |
Mon, 29 August 2005 08:13 |
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
|
Senior Member |
|
|
| Quote: |
'Delete' will not release the space / reset the HWM ( high water mark).
'Truncate' will do.
|
Very true, but I don't think that the OP was saying that it would.
10g new feature SHRINK SPACE should release the memory (I don't have a 10g on my setup here at the moment so I can't double check that)
Truncate wouldn't really do though, would it? granted it will reset the HWM, but the point is the OP only deletes a fraction of the rows from the table. You could however use ALTER TABLE tablename MOVE.
@OP I would guess that you would have to do some bench testing first of all, and I don't think you'll ever get more than a ballpark figure but...
1. Get the Average roe length.
2. get the count of rows in the segment.
3. Multiply the 2 to get (very very very roughly) the actual size of the table.
4. Subtract this from the currently known value of used space (calculated as size of tbs - overhead - free space) the remainder will be your answer.
However I must re-emphasise this will be quite innaccurate as you have other things to consider such as segment, tablespace and block header space factors. (and I'm sure a plethora of other things to take into account that I haven't thought of)
Jim
|
|
|
|
|
|
| Re: free tablespace [message #135020 is a reply to message #135015] |
Mon, 29 August 2005 08:31 |
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
|
Senior Member |
|
|
Yeah mahesh, it's a little vague.
| Quote: |
This is the additional free tablespace that a table had which could be reclaimed using the SHRINK command
|
@OP I think that you may either be confused or mistyped but but the results returned would be simply the total number of free bytes in the tablespace not how many bytes COULD be reclaimed. The difference between the total bytes prior to and after the SHRINK SPACE command represents how much space WAS gained in bytes, again NOT how much COULD be gained.
Jim
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
