Home » RDBMS Server » Performance Tuning » re: freespace in database(oracle8i)
re: freespace in database(oracle8i) [message #64688] Wed, 24 December 2003 20:21 Go to next message
Jun Galolo
Messages: 3
Registered: December 2003
Junior Member
we have our oracle datebase partition to 2 drives..
I have already deleted some transactions_indexes in an ora_file but still my phisical freespace is still growing and the % used is not change...
hope you can help me..
Re: re: freespace in database(oracle8i) [message #64689 is a reply to message #64688] Thu, 25 December 2003 03:44 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
When you drop indexes,only the freespace within that tablespace (within the database) changes and not the filesystem space(physical space at OS level). The datafiles once allocated to the tablespace will consume that much space on the filesystem,unless you resize (shrink) them. Also dont attempt to delete your datafiles. You can resize them and/or drop unnecessary tablespaces to reclaim space.

-Thiru
Re: re: freespace in database(oracle8i) [message #64690 is a reply to message #64689] Thu, 25 December 2003 16:03 Go to previous messageGo to next message
Jun Galolo
Messages: 3
Registered: December 2003
Junior Member
hi,
how can I drop a 'indexes' in a given datafile?? is it the same when we delete logs??
actually I've tried to shrink the temporary datafile but an error promted>>
but I hav'nt tried to shrink the datafile where I've deleted logs..
Re: re: freespace in database(oracle8i) [message #64691 is a reply to message #64690] Fri, 26 December 2003 03:42 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Get the list of indexes on a given datafile using

select e.owner,e.segment_name,e.tablespace_name from dba_Extents e,dba_data_files f
where e.file_id=f.file_id and e.segment_type='INDEX' and f.file_name='&FILENAME';

and then you can drop them,if you want. No its not the same as when you delete logs !

-Thiru
Re: Re: re: freespace in database(oracle8i) [message #64720 is a reply to message #64691] Thu, 08 January 2004 21:20 Go to previous message
jun g.
Messages: 1
Registered: January 2004
Junior Member
Thiru,
I've tried to execute the said select command, but the
result was "0 rows selected"

please advise>>
Previous Topic: partitioned tables
Next Topic: understanding execution plans
Goto Forum:
  


Current Time: Sat Sep 19 01:56:14 CDT 2020