Re: Who is using my datafile
- From: "Jared Still" <jkstill@xxxxxxxxx>
- To: adar666@xxxxxxxxxxxx
- Date: Tue, 8 Jul 2008 12:33:40 -0700
On Tue, Jul 8, 2008 at 10:09 AM, Yechiel Adar <adar666@xxxxxxxxxxxx> 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
Other related posts:
