Re: Who is using my datafile

From: Jared Still <jkstill_at_gmail.com>
Date: Tue, 8 Jul 2008 12:33:40 -0700
Message-ID: <bf46380807081233v5b45ee32g906dfd5d76e04e6a@mail.gmail.com>

On Tue, Jul 8, 2008 at 10:09 AM, Yechiel Adar <adar666_at_inter.net.il> wrote:

> I have a RAC on windows.
> Oracle 9206.
> OCFS.
>
> I have five datafiles that have nothing in them.
> I mean that select * from sys.seg$ where file# between 6 and 11 return
> nothing.
>
> When I tried to decrease the size of the datafiles I got: the file contain
> data beyond the space I want to decrease to.
> I thought that maybe there are some temporary segments but all the users
> use temp as their temporary tablespace.
>
>

Objects have been created in the tablespace, increasing the file high water mark.
Even after the objects have been dropped, the HWM remains.

Example:

12:28:39 SQL>create tablespace shrinkme datafile '/u01/oradata/dv11/shrinkme.dbf' size 1m 12:29:35 2 extent management local uniform size 256k 12:29:53 3 /

Tablespace created.

12:29:56 SQL>alter database datafile '/u01/oradata/dv11/shrinkme.dbf' size 512k;
alter database datafile '/u01/oradata/dv11/shrinkme.dbf' size 512k

                                                         *

ERROR at line 1:
ORA-01916: keyword ONLINE, OFFLINE, RESIZE, AUTOEXTEND or END/DROP expected

12:30:22 SQL>alter database datafile '/u01/oradata/dv11/shrinkme.dbf' resize 512k;

Database altered.

12:30:34 SQL>alter database datafile '/u01/oradata/dv11/shrinkme.dbf' resize 1m;

Database altered.

12:30:45 SQL>create table t1 ( c1 number) tablespace shrinkme;

Table created.

12:31:06 SQL>c/t1/t2
  1* create table t2 ( c1 number) tablespace shrinkme 12:31:09 SQL>/ Table created.

12:31:10 SQL>c/t2/t3
  1* create table t3 ( c1 number) tablespace shrinkme 12:31:15 SQL>/ Table created.

12:31:16 SQL>drop table t1;

Table dropped.

12:31:21 SQL>drop table t2;

Table dropped.

12:31:24 SQL>drop table t3;

Table dropped.

12:31:28 SQL>alter database datafile '/u01/oradata/dv11/shrinkme.dbf' resize 512k;
alter database datafile '/u01/oradata/dv11/shrinkme.dbf' resize 512k *
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

12:31:35 SQL>alter database datafile '/u01/oradata/dv11/shrinkme.dbf' resize 700k;
alter database datafile '/u01/oradata/dv11/shrinkme.dbf' resize 700k *
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

12:32:59 SQL>alter database datafile '/u01/oradata/dv11/shrinkme.dbf' resize 800k;
alter database datafile '/u01/oradata/dv11/shrinkme.dbf' resize 800k *
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

12:33:08 SQL>alter database datafile '/u01/oradata/dv11/shrinkme.dbf' resize 900k;

Database altered.

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jul 08 2008 - 14:33:40 CDT