maxshrink script rocks.
Abhijit, November 14, 2002 - 8:36 am UTC
Still giving problem..
Rahul Sharma, April 21, 2003 - 1:57 am UTC
Hi Tom
I am using LMT with uniform extent allocation for Temp tablespace. Right now my tempfile is 11GB which is a problamatic sign for us.
I ran your script after changing it for temporary file (replace DBA_DATA_FILES to DBA_TEMP_FILES) and get the Alter commands. But at the time of running that command I am still getting the same error..
ORA-03297: file contains used data beyond requested RESIZE value
What is the solution of this problem? I have searched your site but didn't get the exact response.
Thanks.
April 21, 2003 - 7:22 am UTC
just
a) create a new temp of the right size
b) alter users to use this new one
c) drop this one
TEMP allocates the space once and holds it there ( to avoid the expensive overhead of allocate/free/allocate/free). temp always appears full -- so the 3297 is sort of unavoidable.
but you know, if it is 11gig today, it'll be 11gig again real soon. sounds like you might need 11gig of temp (about $100 of disk these days)
Still giving problem..
Rahul Sharma, April 21, 2003 - 1:57 am UTC
Hi Tom
I am using LMT with uniform extent allocation for Temp tablespace. Right now my tempfile is 11GB which is a problamatic sign for us.
I ran your script after changing it for temporary file (replace DBA_DATA_FILES to DBA_TEMP_FILES) and get the Alter commands. But at the time of running that command I am still getting the same error..
ORA-03297: file contains used data beyond requested RESIZE value
What is the solution of this problem? I have searched your site but didn't get the exact response.
Thanks.
A reader, July 21, 2003 - 9:36 am UTC
Trivial space info.
Sai, February 29, 2004 - 2:12 am UTC
Hi Tom,
I would really appreciate if you can help me write a select query to get space information as below.
We have a 2T database with one standby database. We are planning to build another standby with sub-set of data, fewer tablespaces, from the existing standby. Here are the requirements:
The filesystem lay-out on sub-set standby host (Host-B) is completely different from the existing standby host (HOST-A).
On HOST-B, each filesystem can be filled with 80G of data, like that we have 8 filesystems.
On HOST-A, datafiles range from 1G to 8G in size.
Now the question is: How do I get the list of datafiles from the HOST-A, with sum size around 80G, which will be copied over to HOST-B filesystem. Like this, we need 8 sets of datafiles list. Our target standby database size would be around 600G.
Thanks.
February 29, 2004 - 9:09 am UTC
bin fitting problems like that do not lend themselves nicely to SQL based solutions as you have to sort of develop all of the combinations and find the best fit.
sounds like a maintenance nightmare waiting to happen here -- keeping track of all of the renamed files, etc.
maxshrink
A reader, January 07, 2005 - 10:33 am UTC
Hi Tom,
I just downloaded the maxshrink.sql script from this article and created the alter database statements. I have a doubt :
For eg., I have this statement generated
alter database datafile 'C:\ORADATA\SIDNAME\IMG\IMG1.DBF' resize 1808m;
Is it ok for me to run the script just like that? Is 1808 a valid value? If I take a backup of this database and restore it in another machine, will it create any problems? I need your help.
Thanks.
January 08, 2005 - 3:23 pm UTC
it is not a problem. 1808m is a perfectly valid value. it won't affect your backup/restore scenario.
maxshrink.sql
satish, January 24, 2005 - 12:41 pm UTC
Hi Tom, great sql, but it did not work on my system tablespace.
We have a really huge TS about 28 gig which keeps growing on the system Ts. This happened because we do not plan to create a seperate TS, we created a user with no specific tablespace and kept adding data, 800MB per day.
Now how to reduce the system table size and recover system(OS) diskspace.
PIV with 80 GB HDD with all s/w and data installed in one partition.
January 24, 2005 - 2:07 pm UTC
my car didn't start either, why not?
there, we have the same amount of information.... can you answer my question :)
"but it did not work", I've a feeling it certainly did work, but that it is just not possible to "shrink" your system (eg: any and all free space you might have is in the INTERIOR portions of the file.
Trying to shrink 'system' is probably a 'no go', you cannot re-org objects in system (many won't reorg, they are "magic").
You would be looking at a database recreate (where you should definitely give some thought to placing objects!)
maxshrink
satish, January 25, 2005 - 11:54 am UTC
Hi Tom, continuing from my previous question.,
The System TS is of 30 gig in size with only 13 gig occupied, so is there a way to de-frag the system TS? and regain os hdd space. The system TS also has another file added to it with 15 gig having 4 gig occupied.
Also all the data/table on the system TS is test data and can afford to lose. (only lose is the time to sqlload again )
January 25, 2005 - 1:17 pm UTC
as stated - sys owned objects are special, reorgs are pretty much out of the question.
you could
a) create a new database
b) outlaw activity in system beyond the sys stuff
c) move the existing objects out of system into their proper tablespaces (alter table t move tablespace right_one, alter index i rebuild tablespace right_one)
d) transport them from old database to new database
e) erase old database after making sure you got everything you need.
one-on-one
satish, January 26, 2005 - 11:48 am UTC
after going thro' some of your crisp/to-the-point answers i was tempted to buy the book with lot of hesitations., it cost me well., Rs 850/-
This is bulky to carry around., it could have been divided into 3 -4 volumes.,
resizing script doesn't work for 10g
jim, April 30, 2005 - 2:39 pm UTC
Tom:
With recyclbe bin concept come into picture, the resizing script might fail because dropped table is still consuming space but it is not part of dba_extent anymore.
if you compare the definition of dba_extents for 9i and 10g, you will know what i mean.
Any suggestion on that?
not purge
jim, April 30, 2005 - 2:42 pm UTC
One thing we can try is purge recyclebin first.but that's not what i wanted. Is there any way I can resize to the point where both existing object and dropped object are taken into consideration?
April 30, 2005 - 3:16 pm UTC
with just a quick glance, don't see a view that would help.
ony thing off the top of my head is to create our own "dba_extents_for_maxshrink" view that doesn't filter them out.
resizing SYSTEM tablespace
Rana Prathap, June 24, 2005 - 11:34 am UTC
Tom,
There is only one datafile for system tablespace. It is 10 GB in size and is having more than 5GB of free space. So many schema objects were in system tablespace. Most of them (tables and indexes) are moved to some other tablespaces. I am unable to move tables with LONG datatypes. How can this be done? Another thing is even though 5GB is free I am unable to resize the file to less than 9GB. I am getting the error
ora-03297: file contains used data beyond requested resize value
How should I procede to regain the free space?
Thanks
Rana
June 24, 2005 - 6:33 pm UTC
reorging system isn't something you'll be able to do. Basically, you cannot resize down beyond where the last extent is (which is at the 9gig boundary apparently).
You can use the sqlplus copy command to copy the tables with longs.
you can use export/import to move them.
but, that space is probably going to belong to system for the life of this database, if any of the sys objects have an extent out near the end of the file. short of rebuilding the database.
truncate tables
Sean, July 26, 2005 - 9:36 am UTC
I have 8.1.7.4 database, and I have truncated a few large tables. One of them was over 5 G. But I found the high water marks of the data files have not been reset after the tables truncated. I used your maxshrink to find the high water marks of the database files.
July 26, 2005 - 9:55 am UTC
that is normal, files do not shrink unless you shrink them.
space reuse
Sean, July 26, 2005 - 11:57 am UTC
we have the 8.1.7.4 and the tablespace the local managed.
I have a program does the purge on tab1 by inserting the records into the archive table tab2 and then delete the archived records from tab1. The program does the iteration.
data to be deleted/archived about 5g.
To my surpprise, the deleted spaces were not utilized by the inserts. the tablespacde just keeps growing.
July 26, 2005 - 11:59 am UTC
what kind of insert are you using?
here is the program for archiving/deleting
Sean, July 26, 2005 - 12:09 pm UTC
pa.pa_budget_lines_arch is the archive table;
pa.pa_budget_lines is the table being purged.
FOR i IN 1..65 LOOP
insert /*+ append */ into pa.pa_budget_lines_arch pbld
select /*+ parallel (pbl,4) */
*
from pa.pa_budget_lines pbl
where pbl.resource_assignment_id in
(SELECT ra.resource_assignment_id
FROM pa.pa_resource_assignments ra,
pa.pa_budget_versions bv
WHERE bv.current_flag <> 'Y'
AND bv.budget_status_code = 'B'
AND bv.baselined_date IS NOT NULL
AND bv.budget_version_id = ra.budget_version_id
and bv.last_update_date <'24-JUL-05')
and rownum <500001;
delete /*+ index(pbl PA_BUDGET_LINES_U1) */
pa.pa_budget_lines pbl
where pbl.resource_assignment_id in
(SELECT ra.resource_assignment_id
FROM pa.pa_resource_assignments ra,
pa.pa_budget_versions bv
WHERE bv.current_flag <> 'Y'
AND bv.budget_status_code = 'B'
AND bv.baselined_date IS NOT NULL
AND bv.budget_version_id = ra.budget_version_id
and bv.last_update_date <'24-JUL-05')
and rownum <500001;
commit;
END LOOP
July 26, 2005 - 12:33 pm UTC
direct path (/*+ APPEND */) by it's very definition -- does not reuse existing free space.
that is a direct path insert that writes ABOVE the high water mark. By design, that allows it to skip undo generation for the table. remove the append (and lose the parallel) and it will reuse space.
that code is scary -- do you believe the same 500001 rows will be deleted as selected in the first query? (hint: it ain't necessarily so!)
You should just do this in ONE INSERT by the way.
A reader, July 26, 2005 - 1:38 pm UTC
"
You should just do this in ONE INSERT by the way.
"
Can you elaborate this?
July 26, 2005 - 1:41 pm UTC
no loop, no procedural code. insert it all in one insert.
Sean, July 26, 2005 - 1:46 pm UTC
"
no loop, no procedural code. insert it all in one insert.
"
could this be done for insert 35 million records and delete 35 million records? we can take it--apps off to do the CTAS you had advised in the other threads.
July 26, 2005 - 3:20 pm UTC
35million records (assuming idexes are disabled on the receiving table) will generate "not that much undo, even in conventional path", couple 10's of megabytes maybe.
The delete will do the most and I'd be looking for opportunites to TRUNCATE or to use partitions to slide the data out.
sorry, meant we could not table apps off line to purge the table
Sean, July 26, 2005 - 1:47 pm UTC
truncate large table would not reset the hwm of the datafile
Sean, August 04, 2005 - 10:40 pm UTC
Tom,
i have truncated a 13 GB table. And run your maxshrink,
the sum of "Poss.Savings" shown zero.
Why the table space moved does not reduce the HWM of the datafile?
August 05, 2005 - 10:20 am UTC
because you have something "at the end" of the file. Say you had two tables in that tablespace, XX and T2. XX is small. T2 was 13 GB
Initially the file looked like this:
XX XX T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 T2 XX
XX had an extent or two "at the front" and one at the very "end" of the file. When you truncated you ended up with:
XX XX free free free free free free free free free free free free free free free free free free free free free free free free free free free free free free free free free free free XX
the file cannot be shrunk because XX is still "out there"
you can query dba_extents (which is basically what i do) to retreive the name of the segment that is "out there"
RE: truncate large table would not reset the hwm of the datafile
Sean, August 04, 2005 - 10:46 pm UTC
forgot to state it's 8.1.7.4 database
sean, August 05, 2005 - 1:21 pm UTC
"
XX had an extent or two "at the front" and one at the very "end" of the file.
When you truncated you ended up with:
XX XX free free free free free free free free free free free free free free free
free free free free free free free free free free free free free free free free
free free free free XX
the file cannot be shrunk because XX is still "out there"
"
does it look the tablespace fregmentation? the tablespace interested is local managed and the db is 8.1.7.4
August 05, 2005 - 2:43 pm UTC
fragmentation is when you have lots of freespace in the tablespace, but in so many non-contigious places and of such strange sizes, you cannot allocate any new objects.
with a uniform locally managed tablespace - fragmentation is *impossible*. with an autoallocate locally managed tablespace - fragmentation is extremely *unlikely*.
But fragmentation would result in a "unable to allocate another extent" when you have lots of free space in the tablespace.
this is not fragementation.
Great Information
oj, August 22, 2005 - 1:09 pm UTC
Tom,
Thanks for all the great information.
We recently moved from an 8.1.7 system to 10G. Everything else seems to be working fine but for 2 things:
a) System tablespace keeps growing. (No user objects in it anywhere and none of the users have it specified as their temp tablespace). The rate of increase is not that much. About 10MB per day. But considering that it almost never grew by much in 8i it is a little surprising. Is that normal behaviour? And yes, this is an OLTP system growing at about 50-100 megs a day.
b) We have started witnessing a slowdown involving large cursors. The SGA size on 8i was around 1GB which we increased to 1.4 GB for 10g. Is that not enough?
I know this probably might not be enough information, but I just needed a quick link on where to start looking.
thanks again for all you help
August 24, 2005 - 3:21 am UTC
a) find out what is growing (likely the workload repository tables, and they'll stabalize after a while). monitor the tables in there and see which are growing, that'll go along way to answering the question.
b) that doesn't compute. well, insufficient data is more like it. cannot really comment.
did the plans change?
what else changed...
Space Reclaim
J B, July 24, 2006 - 12:15 pm UTC
Hi tom,
Yesterday we freed up space to the HWM and for a particular tablespace we got 24G of space, but the tablespace data is inserted by one procedure which uses INSERT /*+ APPEND */ hint to push the rows.
I know that the APPEND hint will always write above the HWM so the free space is not going to be reused.
But we are maintaing a Primary - Standby relationship which means that the primary will always genetrate redo and cannot bypass redo generation becuause the logs need to get applied to Standby.
Will in this case it will ignore the APPEND hint as FORCE_LOGGING is set to TRUE on the primary database.will the reclaimed space can be used so that I can put these files in AUTOEXTEND off.
Pl. suggest and confirm
July 24, 2006 - 2:48 pm UTC
append has nothing really do to with "logging" (logging and nologging do).
append bypasses undo generation on a table and will generate redo on a table if archivelog mode is enabled, the table is logging OR force logging is set at the database level.
so no, it'll not ignore append. it'll just generate redo because it has to.
Re-claim space
J B, July 25, 2006 - 8:58 am UTC
Hi Tom,
But the prime question was if the tablespace has free space below HWM and the files have been resized to HWM and the autoextend for these files has been put to OFF will the INSERT /*+ APPEND */ fail?
Or it will try to use the free space available within the tablespace below the HWM.
I know that conventional inserts will reclaim the space which is below HWM, not sure what INSERT /*+ APPEND */ will do.
Will it fail since the files are not extendable as AUTOEXTEND is OFF.
Thanks
July 25, 2006 - 11:51 am UTC
sorry, you didn't prioritize anything as far as I could see.
It will use any unused space - space in the files that is not allocated to some other segment, space in the files that is allocated to the segment in question but is above the high water mark.
append writes above the high water mark. period. That is the "definition" sort of - of the append hint. Do a direct path load, do it above the high water mark.
Resizing of datafiles
Hitesh, July 28, 2006 - 12:50 am UTC
Hi Tom,
Based on your suggestions I resized some of the datafiles to get the OS file systems show me 80% full rather than a scary picture of 90% full, even if I know that the logical tablespace utilization is just over 70%.
The resizing dropped the OS file systems from 90% to 82% but after 2 days I am seeing a further dip to 72% at the OS level and all the existing files existing.
Questions:
1. Was something holding up at the OS level to free space straight from 90% to 72%.
2. How did it happen, after a period of 2 days
OR it has nothing to do with my resizing of dtafiles
3. Does SMON plays a role to coalesce the tablespace and automagically shrink the free space above HWM.
Thanks
July 28, 2006 - 7:44 pm UTC
could have been - any process that had the files open would prevent the space from being truely reclaimed.
if you write a program that opens a file and then sits there and waits for you to press enter to close it
and you run it (and it is waiting, file open - waiting to be told to close it)
and you erase that file from another window
you'll find the OS still reports the disk used by the file (because the file still exists in its entirety).
until you close the file, then the space is truly given up.
SMON didn't do anything to shrink here.
Disk Space
Su Baba, October 30, 2006 - 2:41 pm UTC
If I want to write a PL/SQL script to find out not only how full a tablespace is but also how much disk space I have on the OS for my tablespace to grow, how would I approach that? Does Oracle provide an API that would allow me to get this info?
October 30, 2006 - 3:17 pm UTC
no, you can use java stored procedures if you like. Java has api's (no, I don't have an example, no, I'm not coding one either :) that interact with the OS.
Lots of control file backups doing 'alter tablespace'
Stuart, December 13, 2009 - 5:52 pm UTC
Hi Tom,
I'm wanting to shrink a lot of the datafiles in our Production database where the tablespace is 'read only' to reclaim disk space. Your maxshrink script was very helpful identifying which ones could be shrunk.
When I run the 'alter tablespace x read write' to put the tablespace online, Oracle generates a backup of the controlfile in my $ORACLE_HOME/dbs directory (about 15Mb). Another backup controlfile is also generated when I put the tablespace back in read only mode.
I have ~100 data files in my Test environment which were shrunk, and I ended up with 3Gb of backup control files, which I didn't want.
Is there a way to disable the control file backups temporarily? Obviously, I'd be taking a full backup after the resizing.
December 14, 2009 - 9:19 am UTC
ops$tkyte%ORA11GR2> !ls -l $ORACLE_HOME/dbs
total 32
-rw-rw---- 1 ora11gr2 ora11gr2 1544 Sep 14 17:11 hc_ora11gr2.dat
-rw-rw---- 1 ora11gr2 ora11gr2 1544 Sep 14 15:06 hc_orcl.dat
-rw-r--r-- 1 ora11gr2 ora11gr2 2851 May 15 2009 init.ora
-rw-rw-r-- 1 ora11gr2 ora11gr2 1289 Nov 10 14:46 initora11gr2.ora
-rw-r----- 1 ora11gr2 ora11gr2 24 Sep 14 15:08 lkORCL
-rw-r----- 1 ora11gr2 ora11gr2 1536 Sep 14 15:12 orapworcl
drwx------ 2 ora11gr2 ora11gr2 4096 Sep 14 15:06 peshm_orcl_0
-rw-r----- 1 ora11gr2 ora11gr2 3584 Dec 11 14:33 spfileora11gr2.ora
ops$tkyte%ORA11GR2> alter tablespace users read only;
Tablespace altered.
ops$tkyte%ORA11GR2> alter tablespace users read write;
Tablespace altered.
ops$tkyte%ORA11GR2> alter tablespace users read only;
Tablespace altered.
ops$tkyte%ORA11GR2> alter tablespace users read write;
Tablespace altered.
ops$tkyte%ORA11GR2> !ls -l $ORACLE_HOME/dbs
total 32
-rw-rw---- 1 ora11gr2 ora11gr2 1544 Sep 14 17:11 hc_ora11gr2.dat
-rw-rw---- 1 ora11gr2 ora11gr2 1544 Sep 14 15:06 hc_orcl.dat
-rw-r--r-- 1 ora11gr2 ora11gr2 2851 May 15 2009 init.ora
-rw-rw-r-- 1 ora11gr2 ora11gr2 1289 Nov 10 14:46 initora11gr2.ora
-rw-r----- 1 ora11gr2 ora11gr2 24 Sep 14 15:08 lkORCL
-rw-r----- 1 ora11gr2 ora11gr2 1536 Sep 14 15:12 orapworcl
drwx------ 2 ora11gr2 ora11gr2 4096 Sep 14 15:06 peshm_orcl_0
-rw-r----- 1 ora11gr2 ora11gr2 3584 Dec 11 14:33 spfileora11gr2.ora
ops$tkyte%ORA10GR2> !ls -l $ORACLE_HOME/dbs
total 16
-rw-rw---- 1 ora10gr2 ora10gr2 1544 Dec 14 10:18 hc_ora10gr2.dat
-rw-r--r-- 1 ora10gr2 ora10gr2 831 Aug 28 16:03 initora10gr2.ora
-rw-rw---- 1 ora10gr2 ora10gr2 24 Dec 14 10:18 lkORA10GR2
-rw-rw---- 1 ora10gr2 ora10gr2 2560 Dec 14 10:18 spfileora10gr2.ora
ops$tkyte%ORA10GR2> alter tablespace users read only;
Tablespace altered.
ops$tkyte%ORA10GR2> alter tablespace users read write;
Tablespace altered.
ops$tkyte%ORA10GR2> alter tablespace users read only;
Tablespace altered.
ops$tkyte%ORA10GR2> alter tablespace users read write;
Tablespace altered.
ops$tkyte%ORA10GR2> !ls -l $ORACLE_HOME/dbs
total 16
-rw-rw---- 1 ora10gr2 ora10gr2 1544 Dec 14 10:18 hc_ora10gr2.dat
-rw-r--r-- 1 ora10gr2 ora10gr2 831 Aug 28 16:03 initora10gr2.ora
-rw-rw---- 1 ora10gr2 ora10gr2 24 Dec 14 10:18 lkORA10GR2
-rw-rw---- 1 ora10gr2 ora10gr2 2560 Dec 14 10:18 spfileora10gr2.ora
sorry, but we do not do that, we don't backup your control file like that. Any chance you have a system event trigger or something in place?
Lots of control file backups doing 'alter tablespace'
Stuart, December 14, 2009 - 12:59 pm UTC
Ah, found it! See 4d below,...
Persistent Controlfile configurations for RMAN in 9i and 10g. [Metalink: ID 305565.1]
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
4) CONFIGURE CONTROLFILE AUTOBACKUP OFF;
If CONFIGURE CONTROLFILE AUTOBACKUP is ON (by default it is OFF), then RMAN
automatically backs up the control file and the current server parameter file
a) After every BACKUP or COPY command issued at the RMAN prompt.
b) Whenever a BACKUP or COPY command within a RUN block is followed by a command
that is neither BACKUP nor COPY.
c) At the end of every RUN block if the last command in the block was either BACKUP or COPY.
d) After database structural changes such as adding a new tablespace, altering the
state of a tablespace or datafile (for example, bringing it online), adding a new
online redo log, renaming a file, adding a new redo thread, and so forth.
Lots of control file backups doing 'alter tablespace
Stuart, December 14, 2009 - 1:24 pm UTC
Further to the above,...
I found this by tracing my 'alter tablespace x read write' sysdba session.
There were a couple of RMAN wait events in the .trc file which made me suspect it was an RMAN parameter setting. Learn something new every day!
WAIT #1: nam='RMAN backup & recovery I/O' ela= 67437 count=15 intr=256 timeout=-1 obj#=-1 tim=1231265824082533
WAIT #1: nam='RMAN backup & recovery I/O' ela= 3278 count=1 intr=256 timeout=-1 obj#=-1 tim=1231265824089858
...
December 14, 2009 - 3:42 pm UTC
I agree... Learn something new, I did not know that it would do that for a read only/read write switch.
A reader, December 16, 2009 - 4:36 am UTC
Hi Tom,
for some reason, maxshrink doesn't work for one of my
datafiles. It tells me that I should be able to shrink it
to 1251 MB, but I cannot shrink to less than 1315 MB.
I am using Oracle 11gR1 with a blocksize of 8 KB for all my tablespaces.
There seems to be data in the datafile that is not visible in dba_extents. What am I missing here?
HWM should indeed be 1251 MB (file 4 is my USERS01.DBF file):
SQL> select max(block_id+blocks) maxblock,8192*max(block_id+blocks) maxbytes,ceil(8192*max(block_id+blocks)/1024/1024) maxmb from dba_extents where file_id=4;
MAXBLOCK MAXBYTES MAXMB
---------- ---------- ----------
160049 1311121408 1251
Output from maxshrink:
Smallest
Size Current Poss.
FILE_NAME Poss. Size Savings
-------------------------------------------------- -------- -------- --------
D:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF 787 787 0
D:\ORACLE\ORADATA\ORCL\SYSAUX01.DBF 828 828 0
D:\ORACLE\ORADATA\ORCL\USERS01.DBF 1,251 1,315 64
D:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF 68 68 0
--------
sum 64
SQL>
SQL> column cmd format a75 word_wrapped
SQL>
SQL> select 'alter database datafile ''' || file_name || ''' resize ' ||
2 ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
3 from dba_data_files a,
4 ( select file_id, max(block_id+blocks-1) hwm
5 from dba_extents
6 group by file_id ) b
7 where a.file_id = b.file_id(+)
8 and ceil( blocks*&&blksize/1024/1024) -
9 ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
10 /
CMD
---------------------------------------------------------------------------
alter database datafile 'D:\ORACLE\ORADATA\ORCL\USERS01.DBF' resize 1251m;
SQL> alter database datafile 'D:\ORACLE\ORADATA\ORCL\USERS01.DBF' resize 1251m;
alter database datafile 'D:\ORACLE\ORADATA\ORCL\USERS01.DBF' resize 1251m
*
FEHLER in Zeile 1:
ORA-03297: Datei enthΣlt benutzte Daten ⁿber angeforderten RESIZE-Wert hinaus
SQL> alter database datafile 'D:\ORACLE\ORADATA\ORCL\USERS01.DBF' resize 1271m;
alter database datafile 'D:\ORACLE\ORADATA\ORCL\USERS01.DBF' resize 1271m
*
FEHLER in Zeile 1:
ORA-03297: Datei enthΣlt benutzte Daten ⁿber angeforderten RESIZE-Wert hinaus
SQL> alter database datafile 'D:\ORACLE\ORADATA\ORCL\USERS01.DBF' resize 1300m;
alter database datafile 'D:\ORACLE\ORADATA\ORCL\USERS01.DBF' resize 1300m
*
FEHLER in Zeile 1:
ORA-03297: Datei enthΣlt benutzte Daten ⁿber angeforderten RESIZE-Wert hinaus
SQL> alter database datafile 'D:\ORACLE\ORADATA\ORCL\USERS01.DBF' resize 1314m;
alter database datafile 'D:\ORACLE\ORADATA\ORCL\USERS01.DBF' resize 1314m
*
FEHLER in Zeile 1:
ORA-03297: Datei enthΣlt benutzte Daten ⁿber angeforderten RESIZE-Wert hinaus
SQL> alter database datafile 'D:\ORACLE\ORADATA\ORCL\USERS01.DBF' resize 1315m;
Datenbank wurde geΣndert.
December 16, 2009 - 10:14 am UTC
probably stuff in the recycle bin, you might have to purge the recycle bin for that tablespace.
Thomas, December 17, 2009 - 1:00 am UTC
Thanks for your previous comment. You are totally correct, my recyclebin
contained two objects. After running "purge dba_recyclebin" I was able to shrink
the datafile to 1251 MB.
Is there any way to take extents that belong to recyclebin objects into account
when determining the HWM of a datafile? Obviously, dba_extents ignores these.
December 17, 2009 - 9:58 am UTC
you would have to rewrite my query.
You could use dba_lmt_used_extents/free_space to determine what is used - or just purge the recyclebin first.
Controlfile autobackup does not work for 11.2 ?
Stuart., January 10, 2011 - 2:26 pm UTC
Re: Controlfile autobackup does not work for 11.2
Stuart., January 10, 2011 - 3:32 pm UTC
Ignore that previous posting....
I see in the 11gR2 Backup and Recovery Reference Guide, page 98, there is a new paragraph as follows:
"Starting with Oracle 11g Release 2, RMAN creates a single autobackup file encompassing all of the structural changes that have occurred within a few minutes of each other rather than creating a new backup of the controlfile on each structural change to the database."
I checked all my databases again, and there is now a contolfile backup for all of them. I just needed to wait a few minutes.
January 10, 2011 - 4:09 pm UTC
ah, good, thanks for the followup! really appreciate that.
after runing this script not getting enough space and Disk space is about 100%
Muhammad Irfan, May 31, 2013 - 5:05 pm UTC
Hi,
I used this script on Oracle database 10.2 64-bits running on Sun Solaris 10. Disk space on sever
is now about 100 % and i want to shrink it as earliest as possible. running command after this
script i am not able to get even 1% free space. I am not a database administrator, but this is high
priority task for me.
all result are attached if you can help me to resolve this issue.
your help in this regard will be high appreciated.
SQL> @/export/home/maxshrink.sql
VALUE
-------------------------------------------------------------
----------------------------------------------------------------------
8192
Smallest
Size Current Poss.
FILE_NAME Poss. Size Savings
-------------------------------------------------- -------- -------- --------
/u02/oradata/PROD/axdata1.dbf 22,296 22,297 1
/u02/oradata/PROD/system01.dbf 566 567 1
/u02/oradata/PROD/sysaux01.dbf 529 531 2
/u02/oradata/PROD/undotbs01.dbf 85 107 22
/u02/oradata/PROD/users01.dbf 1 2 1
/u02/oradata/PROD/AXDATA.dbf 1 1 0
/u02/oradata/PROD/AXINDEX.dbf 1 1 0
--------
sum 27
7 rows selected.
CMD
---------------------------------------------------------------------------
alter database datafile '/u02/oradata/PROD/axdata1.dbf' resize 22296m;
alter database datafile '/u02/oradata/PROD/system01.dbf' resize 566m;
alter database datafile '/u02/oradata/PROD/sysaux01.dbf' resize 529m;
alter database datafile '/u02/oradata/PROD/undotbs01.dbf' resize 85m;
alter database datafile '/u02/oradata/PROD/users01.dbf' resize 1m;
SQL>
SQL> alter database datafile '/u02/oradata/PROD/axdata1.dbf' resize 22296m;
Database altered.
SQL>
May 31, 2013 - 5:14 pm UTC
go look at the other place you posted this same exact thing. and stop posting it in more than one place, what is the point? there is only one of me.