SYSTEM Table Space size is too large
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: SYSTEM Table Space size is too large

  1. #1
    Join Date
    Jan 2003
    Location
    Pakistan
    Posts
    5

    Question SYSTEM Table Space size is too large

    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.

  2. #2
    Join Date
    Jan 2003
    Location
    India
    Posts
    7
    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.

  3. #3
    Join Date
    Jan 2003
    Location
    Pakistan
    Posts
    5

    Unhappy

    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

  4. #4
    Join Date
    Jan 2002
    Location
    India
    Posts
    105
    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
    Last edited by virajvk; 01-27-2003 at 01:41 AM.
    A Wise Man Knows How much he doesn't know !!!

  5. #5
    Join Date
    Nov 2000
    Location
    Israel
    Posts
    268
    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.
    It is better to ask and appear ignorant, than to remain silent and remain ignorant.

    Oracle OCP DBA 9i,
    C++, Java developer

  6. #6
    Join Date
    Feb 2001
    Location
    UAE
    Posts
    304
    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.
    Agasimani
    OCP(10g/9i/8i/8)

  7. #7
    Join Date
    Jan 2003
    Location
    Pakistan
    Posts
    5

    Unhappy 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.

  8. #8
    Join Date
    Jan 2001
    Posts
    2,828
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width