Hi,

You could use this handy script to check the tail end of the datafile (Get
the File_Id first). Some times just one segment trailing at the tail end of
the datafile, keep you from shrinking it back to a smaller size.

Best regards - Phong Le


SET ECHO OFF
col ownr format a8 heading 'Owner' justify c;
col type format a8 heading 'Type' justify c trunc;
col name format a28 heading 'Segment Name' justify c;
col exid format 9990 heading 'Extent#' justify c;
col flid format 99990 heading 'File#' justify c;
col blid format 999,999,990 heading 'Bkock# ' justify c;
col blks format 999,999,990 heading 'Blocks ' justify c;
set feedback off
set pagesize 1000

spool FindExtent_FileID_&&1

SELECT owner ownr,
segment_name name,
segment_type type,
extent_id exid,
file_id flid,
block_id blid,
blocks blks
FROM dba_extents
where file_id = &file_id
and block_id > 300000
order by block_id;

spool off;
exit;

-----Original Message-----
From: Chris Pope
[mailto:oracledba-ezmlmshield-x89618087.[Email address protected]
Sent: Friday, November 30, 2007 1:43 PM
To: LazyDBA Discussion
Subject: Re: Users tablespace

Very true. My resizing luck hasn't been very good so I don't
even bother anymore. Plus...nothing like a little
tablespace re-org to warm the blood.

Kathy Wright wrote:
> Or you could just do
> alter datafile 'path/path/path/datafile_name.dbf' resize 14000M;
>
> If you get an unable to shrink because you have extents out further than
the 14gig, you will have to do what Chris suggests.
>
> No biggie either way, really!
> Have fun and Happy Friday!
>
> Kathy
>
>>>> "Chris Pope " <oracledba-ezmlmshield-x24983806.[Email address
protected] 11/30/2007 9:08:09 AM >>>
> You could...
>
> create a new tablespace, move everything in Users to it ,
> drop Users and recreate it , then move everything back.
>
>
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To post a dba job: http://jobs.lazydba.com
> To Subscribe : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>



---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


Oracle LazyDBA home page