SYSTEM Table Space size is too large
I want some help on decreasing the database size.
My SYSTEM Table Space size is too large. 4.09GB.
How can I reduce it?
Please check the free space for system ts.
SQL> SELECT TABLESPACE_NAME, SUM(BYTES) FROM DAB_FREE_SPACE
GROUP BY TABLESPACE_NAME;
and try to find out how much of contiguous free space is available.
use that size to minimize your TS size by finding related datafile
SQL> alter database datafile '/disk1/oradata/XXXXX/system01.dbf' resize 2gb;
I have checked the free space it's only 2.47MB. The DataFile is 99% Full, but I know that my whole data is not more than 700-800MB.
Actually I imported this whole data (I mean for other Table Spaces) from UNIX.
In this situation what you will suggest.
Follow these steps only if U have not used these tables for any DML perpose.
When you imported tables Did u had Export log associated with the Dump file ?
1. If Yes then drop all those table from System tablespace by connecting to system users. (if No then create index file using Import utility specifying index file name )
2. Create diffent tablespace say "TBS1" , create differnt user say "ABC" and give this tablespace name as default tablespace name.
grant privilegs to ABC
3. Locate the script of tables if possible and change tablespace name to "TBS1" for each table.
(If u dont have tables script create Index file using Import utility Copy contents and make script for creating tables )
4. Run this script in newly created user.
5. Import the dump saying
Imp system/systempassword file=*****.dmp rows=y ignore=y fromuser=system touser=ABC
6. Then go for resizing datafiles assoicited with System tablespace
OCP 9i DBA
Last edited by virajvk; 01-27-2003 at 02:41 AM.
A Wise Man Knows How much he doesn't know !!!
Check for segments that does not belong to SYS/SYSTEM schema but reside in SYSTEM tablespace and move them to designated tablespaces:
select owner, segment_type, segment_name
where owner not in ('SYS','SYSTEM')
and tablespace_name = 'SYSTEM';
For each table, simply move to other tablespace using
alter table tab_name move tablespace ts_name storage (...
for each index, you can rebuild it using:
alter index ind_name rebuild tablespace ts_name storage (...
It is better to ask and appear ignorant, than to remain silent and remain ignorant.
Oracle OCP DBA 9i,
C++, Java developer
Before you do anything, first check what are default and temporary tablespaces for the users. NEVER leave them default. Make sure they are not SYSTEM.
select username, default_tablespace, temporary_tablespace from dba_users where username<>'SYSTEM';
If you find SYSTEM tablespace here change them using ALTER USER command. Then you can continue with what rotem_fo has said.
After you transfer all the objects to different tablespaces, you can use the following command:
alter database datafile 'xxxx.dbf' resize 100M;
If this command fails, which is possible, then you will have to export and import the entire database with possibly COMPRESS=Y option.
Problem still Exists
Results From DBA_DATA_FILES
FILE_NAME = E:\ORACLE\ORADATA\SFPL\SYSTEM01.DBF
TABLESPACE_NAME = SYSTEM
BYTES = 4293787648
BLOCKS = 524144
STATUS = AVAILABLE
RELATIVE_FNO = 1
AUTOEXTENSIBLE = YES
MAXBYTES = 3.4360E+10
MAXBLOCKS = 4194302
INCREMENT_BY = 80
USER_BYTES = 4293779456
USER_BLOCKS = 524143
Results From DBA_FREE_SPACE
TABLESPACE_NAME = SYSTEM
SUM(BYTES/1048576) = 2699.28906 (It's in MB)
when I tried to resize the data file it gives the following error.
ORA-03297: file contains used data beyond requested Resize Value.
quick n dirty answer to you...on how to resize :-D
export the database with compress=y
import the database ..
then you can resize your datafile..i suspect you have non system database objects in the system tablespace.
ahh..i hope your database is small enough to do export import..
Click Here to Expand Forum to Full Width