-
I need space on my disk, and i have a datafile that takes 6 gig. it contain only 3gig of data.
I want to resize and it gives me:
ORA-03297: file contains used data beyond requested RESIZE value
I did an alter table move tablespace
On all my table in the same tablespace.
I did a rebuild on all my index.
NOTE: I CANT DO A EXPORT/IMPORT because 24/24 database.
1. What oracle table or view can tell me wich object is beyond resize value?
2. I have also package, sequence, function and procedure, do i have to recreate them?
-
-
-
-
Would you mind giving the querry please..
Regards,
K.Diwakar
Originally posted by pando
block_id?
-
column file_name format a50;
column tablespace_name format a15;
column highwater format 9999999999;
set pagesize 9999
select a.tablespace_name
,a.file_name
,(b.maximum+c.blocks-1)*d.db_block_size highwater
from dba_data_files a
,(select file_id,max(block_id) maximum
from dba_extents
group by file_id) b
,dba_extents c
,(select value db_block_size
from v$parameter
where name='db_block_size') d
where a.file_id = b.file_id
and c.file_id = b.file_id
and c.block_id = b.maximum
order by a.tablespace_name,a.file_name
/
The query gives the high water mark for each of the file beyond which they cannot be reduced by size.
-
You can see all the objects and allocated extents in that datafile by using this query.
Code:
SELECT SEGMENT_NAME, SEGMENT_TYPE, EXTENT_ID, BLOCK_ID
FROM DBA_EXTENTS
WHERE FILE_ID = n;
BLOCK_ID is the starting block_id for the extent.
Sanjay
[Edited by SANJAY_G on 07-19-2002 at 05:44 AM]
-
From Metalink
Script to find database object locations for a given datafile.
-----------CUT-----------------CUT-----------------CUT--------
REM findext.sql
REM
REM This script prompts user for a datafile ID number, and
REM then lists all the segments contained in that datafile,
REM the blockid where it starts, and how many blocks the
REM segment contains. It shows the owner, segment name, and
REM segment type.
REM
REM Janet Robinson Stern April 2, 1997
REM variation on Cary Millsap's script
REM
SET ECHO OFF
ttitle -
center 'Segment Extent Summary' skip 2
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 990 heading 'Extent#' justify c
col fiid format 9990 heading 'File#' justify c
col blid format 99990 heading 'Block#' justify c
col blks format 999,990 heading 'Blocks' justify c
select
owner ownr,
segment_name name,
segment_type type,
extent_id exid,
file_id fiid,
block_id blid,
blocks blks
from
dba_extents
where
file_id = &file_id
order by
block_id
/
-
Code:
SELECT SEGMENT_NAME, SEGMENT_TYPE, BLOCK_ID
FROM DBA_EXTENTS
WHERE FILE_ID=n AND
BLOCK_ID = (SELECT MAX(BLOCK_ID) FROM DBA_EXTENTS
WHERE FILE_ID=n);
Sanjay
-
May be DBMS_SPACE would be helpful.
Write a small PL/SQL block using DBMS_SPACE package and get a list of tables having more than enough unused blocks.
Then recover that space using ALTER TABLE ... DEALLOCATE UNUSED.
Sameer
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|