Home » RDBMS Server » Server Administration » space manage (10.2.0.4 & 11.2)
space manage [message #577276] Wed, 13 February 2013 23:04 Go to next message
babuknb
Messages: 1703
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 #577289 is a reply to message #577276] Thu, 14 February 2013 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 57650
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
What was the reason behind this.


Because it is designed as it: DROP TABLE do not change the size of the files, it just free space inside the tablespace. DROP TABLE means you have the privilege to manage the table not to manage the tablespace.

Quote:
Is there any process needs to follow when dropping big tables ?


ALTER DATABASE DATAFILE ... RESIZE ...

Regards
Michel
Re: space manage [message #577314 is a reply to message #577276] Thu, 14 February 2013 03:33 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #577702 is a reply to message #577700] Tue, 19 February 2013 14:13 Go to previous messageGo to next message
Michel Cadot
Messages: 57650
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just for my information, why the RULE hint?

Regards
Michel
Re: space manage [message #577703 is a reply to message #577702] Tue, 19 February 2013 14:22 Go to previous message
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.
Previous Topic: Tablespace Read Stats
Next Topic: why temporary tablespace have uniform extents
Goto Forum:
  


Current Time: Thu Apr 24 17:44:05 CDT 2014

Total time taken to generate the page: 0.09860 seconds