Hi All,
I have a database 9i 9.2.0.4 on Sun Solaris 5.9.
- We have many permanent tablespaces and has "autoextend ON" and "maxsize UNLIMITED". Let call is TS1.
- We have different users and each user is exclusively assigned the above tablespace as default storage. No other is user is having any privilege to use anybody's else tablespace. Let call this user as U1.
"One User - One Tablespace" that is, U1 is using TS1.
Let call the user
.
ISSUES
======
1. The datafile of the tablespace TS1 shows the SIZE of 4.3 GB on OS. The Database file on FileSystem is 4.5 GB
2. And when i take the EXPORT of the above User Schema i.e. User U1, it shows me very less SIZE i.e. 388 MB approx. This is a BIG Surprise to me. I take EXPORT like this:
$exp system/<password> file=TS1.dmp log=TS1.log owner=U1
3. I also tried to check the Tablespace Used Space and TableSpace Free Space and it shows me the statistics like this:
+
The Query to see Used Space (For all Tablespaces of my DB Instance):
select b.name, (sum(bytes)/1024)/1024
from v$datafile a, v$tablespace b
where a.ts# = b.ts#
group by b.name;
[For TS1, it is: 4.512 GB]
+
The Query to see Free Space (For all Tablespaces of my DB Instance):
select tablespace_name, (SUM(bytes)/1024)/1024
from dba_FREE_SPACE
group by tablespace_name;
[For TS1, it is: 4.188 GB]
+=================
+ Due to the Whopping Size of TS1, it is threat to my File System Space management as my Unix Slice is 93% Full.
+ How Can i understand this Phenomenon...Is is OK in Database or We might be missing some configuraiton / settign etc. Why is this great diff. between datafile size & export size.
+ And what is the best way i can reduce / shrink the TabaseSpace Datafile Size almost equal to the EXPORT SIZE..
Please help on priority pls. I look forward to some tips / help.
Regards,
Kamesh Rastogi