space manage [message #577276] |
Wed, 13 February 2013 23:04 |
babuknb
Messages: 1736 Registered: December 2005 Location: NJ
|
Senior Member |
|
|
Hello All,
I have one generic question about space management. I have one table with size of 1TB. This table stored in ORC1 tablespace. This tablespace contains 70 datafiles.
Since it's 10.2.0.4 database. I have dropped this table by using purge
drop table <<table_name>> purge;
Once table drop was completed. When I check the tablespace space it was 100% free but due to HWM was unable to resize the datafile from current size to small size.
What was the reason behind this. Is there any process needs to follow when dropping big tables ? like instead of dropping the tables do I need to truncate first & then drop .
Appreciate if any response to my question.
[Updated on: Wed, 13 February 2013 23:30] Report message to a moderator
|
|
|
|
Re: space manage [message #577314 is a reply to message #577276] |
Thu, 14 February 2013 03:33 |
hkchital
Messages: 128 Registered: September 2008 Location: Singapore
|
Senior Member |
|
|
>was unable to resize the datafile from current size to small size.
Check if you have any other segment(s) (Table(s) or Index(es)) in the same tablespace.
Hemant K Chitale
|
|
|
Re: space manage [message #577700 is a reply to message #577314] |
Tue, 19 February 2013 13:16 |
|
alan.kendall@nfl.com
Messages: 163 Registered: June 2012 Location: Culver City, California
|
Senior Member |
|
|
You can use the following simple mapper script to see what is in each datafile. You can get the datafile file# from v$datafile.
ENWEBPD > @mapperi 1
OWNER OBJECT FILE_ID BLOCK_ID BLOCKS
------------- --------------- ------- ---------- ----------
SYS TABLE AUD$. 1 97920 1024
SYS TABLE AUD$. 1 98944 1024
SYS INDEX I_COL3. 1 99968 128
SYS CLUSTER C_OBJ#_INTCOL#. 1 100096 128
SYS INDEX I_H_OBJ#_COL#. 1 100224 128
SYS CLUSTER C_OBJ#_INTCOL#. 1 100352 128
SYS CLUSTER C_OBJ#_INTCOL#. 1 100480 128
SYS TABLE AUD$. 1 100608 1024
free space 1 101632 29440
ENWEBPD > list
1 select /*+ Rule */ 'free space' owner /*"owner" of free space*/
2 , ' ' object /*blank object name*/
3 , file_id /*file id for the extent header*/
4 , block_id /*block id for the extent header*/
5 , blocks /*length of the extent, in blocks*/
6 from dba_free_space
7 where file_id=&1
8 union
9 select /*+ Rule */ substr(owner,1,20)||' '||substr(segment_type,1,9)
10 , substr(segment_name,1,32)||'.'||partition_name /*segment name*/
11 , file_id /*file id for the extent header*/
12 , block_id /*block id for the extent header*/
13 , blocks /*length of the extent, in blocks*/
14 from dba_extents
15 where file_id=&1
16* order by 3,4
|
|
|
|
Re: space manage [message #577703 is a reply to message #577702] |
Tue, 19 February 2013 14:22 |
|
alan.kendall@nfl.com
Messages: 163 Registered: June 2012 Location: Culver City, California
|
Senior Member |
|
|
It is a very old script from the 90's that used to perform better in some version of Oracle 8 with the rule hint. Thanks for the catch. I took the rule hint out.
|
|
|