reljica
asked on
Resize & reorganize SYSTEM talespace
Hello,
sys.c_ts# cluster reached big size and gets fragmented. After long period of using database (8.1.7.2 on Solaris 2.6), currently there's the last cluster's extent reaches almost end of datafile. Between other used extents and that one is couple of very large free space extents - almost 4.5 GB. I use tablespace Map to observer this.
I need to shrink SYSTEM's datafile size from 5GB to 500MB. How to reorganize the problematic cluster's extents, so i can use 'ALTER DATABASE ... SIZE' command ? I don't know way how to do this in the SYSTEM tablespace?
I already coalesced free extents in SYSTEM !
Is it possible to recreate SYSTEM tablespace if there no other solution ?
Thank You very much for any clues ASAP...
sys.c_ts# cluster reached big size and gets fragmented. After long period of using database (8.1.7.2 on Solaris 2.6), currently there's the last cluster's extent reaches almost end of datafile. Between other used extents and that one is couple of very large free space extents - almost 4.5 GB. I use tablespace Map to observer this.
I need to shrink SYSTEM's datafile size from 5GB to 500MB. How to reorganize the problematic cluster's extents, so i can use 'ALTER DATABASE ... SIZE' command ? I don't know way how to do this in the SYSTEM tablespace?
I already coalesced free extents in SYSTEM !
Is it possible to recreate SYSTEM tablespace if there no other solution ?
Thank You very much for any clues ASAP...
ASKER
O.K. I like Your answer! Just let me to put some notes before I accept it.
Are You sure that I cannot use "ALTER DATABASE DATAFILE '/d1/system.db' RESIZE 500M" command on SYSTEM tablespace as method for reorganization? From your answer I know that either it is possible, it will not help me.
Can You explain the process of importing entire database:
A. create database, create tablespaces and perform full import, or
B. create database, create dictionary, create tablespaces and perform import, or
C. ....? (diferent)
Do I have any faster alternative for getting data back instead import/export? Re-using existing tablespaces or similar... 2 of 5 tablespaces are locally managed.
Thank You
Are You sure that I cannot use "ALTER DATABASE DATAFILE '/d1/system.db' RESIZE 500M" command on SYSTEM tablespace as method for reorganization? From your answer I know that either it is possible, it will not help me.
Can You explain the process of importing entire database:
A. create database, create tablespaces and perform full import, or
B. create database, create dictionary, create tablespaces and perform import, or
C. ....? (diferent)
Do I have any faster alternative for getting data back instead import/export? Re-using existing tablespaces or similar... 2 of 5 tablespaces are locally managed.
Thank You
ASKER
O.K. I like Your answer! Just let me to put some notes before I accept it.
Are You sure that I cannot use "ALTER DATABASE DATAFILE '/d1/system.db' RESIZE 500M" command on SYSTEM tablespace as method for reorganization? From your answer I know that either it is possible, it will not help me.
Can You explain the process of importing entire database:
A. create database, create tablespaces and perform full import, or
B. create database, create dictionary, create tablespaces and perform import, or
C. ....? (diferent)
Do I have any faster alternative for getting data back instead import/export? Re-using existing tablespaces or similar... 2 of 5 tablespaces are locally managed.
Thank You
Are You sure that I cannot use "ALTER DATABASE DATAFILE '/d1/system.db' RESIZE 500M" command on SYSTEM tablespace as method for reorganization? From your answer I know that either it is possible, it will not help me.
Can You explain the process of importing entire database:
A. create database, create tablespaces and perform full import, or
B. create database, create dictionary, create tablespaces and perform import, or
C. ....? (diferent)
Do I have any faster alternative for getting data back instead import/export? Re-using existing tablespaces or similar... 2 of 5 tablespaces are locally managed.
Thank You
ASKER
O.K. I like Your answer! Just let me to put some notes before I accept it.
Are You sure that I cannot use "ALTER DATABASE DATAFILE '/d1/system.db' RESIZE 500M" command on SYSTEM tablespace as method for reorganization? From your answer I know that either it is possible, it will not help me.
Can You explain the process of importing entire database:
A. create database, create tablespaces and perform full import, or
B. create database, create dictionary, create tablespaces and perform import, or
C. ....? (diferent)
Do I have any faster alternative for getting data back instead import/export? Re-using existing tablespaces or similar... 2 of 5 tablespaces are locally managed.
Thank You
Are You sure that I cannot use "ALTER DATABASE DATAFILE '/d1/system.db' RESIZE 500M" command on SYSTEM tablespace as method for reorganization? From your answer I know that either it is possible, it will not help me.
Can You explain the process of importing entire database:
A. create database, create tablespaces and perform full import, or
B. create database, create dictionary, create tablespaces and perform import, or
C. ....? (diferent)
Do I have any faster alternative for getting data back instead import/export? Re-using existing tablespaces or similar... 2 of 5 tablespaces are locally managed.
Thank You
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
To prevent fragmentation problems in the SYSTEM tablespace, make sure that all users have their default tablespace set to something other than SYSTEM and their temp tablespace set to something other than SYSTEM. Finally in Oracle9, Oracle has changed the defaults when a user is created, but in all earlier verions of Oracle the SYSTEM tablespace was the default.
That particular cluster in Oracle8.1.7 contains the tables: ts$ and fet$. They contain information about tablespaces and free extents. If you drop tablespaces, the space in this cluster is not re-used. Also, if you use dictionary-managed tablespaces (rather than the locally-managed tablespaces that are much more efficient) that can cause fragementation in this cluster. Are your tablespaces (except SYSTEM) locally-managed?