Hi there,
I want some help on decreasing the database size.
My SYSTEM Table Space size is too large. 4.09GB.
How can I reduce it?
Kind Regards.
Printable View
Hi there,
I want some help on decreasing the database size.
My SYSTEM Table Space size is too large. 4.09GB.
How can I reduce it?
Kind Regards.
Hi,
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
EX :
SQL> alter database datafile '/disk1/oradata/XXXXX/system01.dbf' resize 2gb;
Regards,
Singh.
Hi there,
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.
Kind Regards,
Shani
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
Regards
Viraj
----------
OCP 9i DBA
Hi,
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
from dba_segments
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 (...
Cheers,
R.
Hi shaniahmad
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.
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.
Hi
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..
regards
Hrishy