| resize of datafile in system [message #126442] |
Mon, 04 July 2005 09:18  |
balasmg
Messages: 31
Registered: June 2005
Location: bangalore
|
Member |
|
|
i am having system tablespace size has 26 gb with 6 users
before :
size of tablespace : used/free size in gb ==> 26 / 3.99
i have removed 3 users which are not required.
after
size of tablespace : used/free size in gb ==> 26 / 13.5
when i want to resize the datafile using
ALTER DATABASE
DATAFILE '/u01/home/oracle/data/oradata/EMCOVER/system01.dbf'
RESIZE 15000M
ORA-03297 : file contains user data beyond requested RESIZE value
when i read from asktom in the following
url : http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:7149039425561
i came to know if there '
"because at least one of the extents in your datafiles is at the "end" of the
data file."
how can i reduce the datafile size. and resize ? pl. let me know
thanks
|
|
|
|
| Re: resize of datafile in system [message #126445 is a reply to message #126442] |
Mon, 04 July 2005 09:42  |
Frank Naude
Messages: 4587
Registered: April 1998
|
Senior Member |
|
|
Hi,
For objects in non-SYSTEM tablespaces, the easiest solution would be to export the tablespace, drop and recreate it, and to import the data again. Alternatively, one can query DBA_EXTENTS to find out what segments are allocated above the mark, and try to MOVE/REBUILD them.
Nevertheless, as this is your SYSTEM tablespace, you would need to recreate the entire database!
Best regards.
Frank
|
|
|
|
|
|
| Re: resize of datafile in system [message #126553 is a reply to message #126442] |
Tue, 05 July 2005 10:28 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
|
Just to add, and this is a guess, don't put your user data in the system tablespace. Create a separate tablespace for that and let oracle have exclusive use of system (and sysaux).
|
|
|
|
| Re: resize of datafile in system [message #126616 is a reply to message #126442] |
Tue, 05 July 2005 23:56 |
raghuraja_r
Messages: 22
Registered: February 2005
Location: chennai
|
Junior Member |
|
|
Hi Bala
You cannot reduce the size of the datafile.
eventhough there is a command to resize u can extend its size but cant reduce it. i have tried 100 times with this alter table space command.
my option is
create a temperory table space.
move all three user to the new tablespace
move all the tables to new tablespace.
now drop the old tablespace and
create a new table space with same name and
restore all ur user and tables.
so that u can regain ur memory space.
all the best
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
