Re: Please help ---- Oracle free spaces

From: Someone <yong_feei_shun_at_uac.com.my>
Date: Fri, 22 Oct 2004 10:05:59 +0800
Message-ID: <41786b0b_2@news.tm.net.my>

Hi,

          I am having a problem here, hope you all could help me out.
          I intend to check what are the sizes of my database taken up after
the deletion of some of the data, so this is what I did.
          Frist, I deleted the data from my database, then I used the

analyzer to ensure the rows of the tables are actually reduced. Then, I did a export again, and then re-imported the database back to my PC. from there I wish to see the result of the before and after deletion effect. But, it failed. The result shows me both the after and before deletion is the same. What are the steps that I had done wrongly? The below is the script for analyzing the free spaces available. The output of the before and after for tablespace ftable is the same. I have no idea why the system still show me the same result after the deletion. Please help!!!!!!

Thank you

REM PRINT FREE SPACE FOR DATAFILES
REM 16/3/91
PROMPT BEGIN PROCESSING...
REM ACCEPT FNAME CHAR PROMPT 'SPOOL FILENAME : '; SPOOL c:\datafile.lst
SET TERMOUT OFF
PROMPT
PROMPT ** DATAFILES INFO **

PROMPT    --------------------------------------

      SELECT
      SUBSTR(FILE_NAME,1,32) FILE_NAME,
      FILE_ID,
      ROUND(SUM(BYTES)/1024)  KB,
      ROUND(SUM(BYTES)/(1024*1024))  MB
      FROM SYS.DBA_DATA_FILES
      GROUP BY TABLESPACE_NAME,FILE_ID,FILE_NAME
      ORDER BY TABLESPACE_NAME,FILE_ID;

PROMPT
PROMPT ** TABLESPACES FREE SPACES **

PROMPT    -------------------------------

      SELECT SUBSTR(TABLESPACE_NAME,1,13) TABLESPACE,
      ROUND(SUM(BYTES)/1024) FREE_KB,
      ROUND(SUM(BYTES)/(1024*1024)) FREE_MB
      FROM SYS.DBA_FREE_SPACE
      GROUP BY TABLESPACE_NAME;

SPOOL OFF
SET TERMOUT ON
PROMPT END PROCESSING....
REM PROMPT OUTPUT HAS BEEN SPOOLED TO &FNAME The output:
** DATAFILES INFO **

FILE_NAME                           FILE_ID         KB         MB
-------------------------------- ---------- ---------- ---------- 
C:\ORACLE\ORADATA\UAC\DRSYS01.DB          3      20480         20
C:\ORACLE\ORADATA\UAC\FTABLE.DBF          8   25600000      25000
C:\ORACLE\ORADATA\UAC\INDX01.DBF          4      25600         25
C:\ORACLE\ORADATA\UAC\SYSTEM01.D          1     337920        330
C:\ORACLE\ORADATA\UAC\TOOLS01.DB          5      10240         10
C:\ORACLE\ORADATA\UAC\UNDOTBS01.          2     460800        450
C:\ORACLE\ORADATA\UAC\USERS01.DB          6      25600         25
C:\ORACLE\ORADATA\UAC\XDB01.DBF           7      39040         38

8 rows selected.

TABLESPACES FREE SPACES **

TABLESPACE FREE_KB FREE_MB
------------- ---------- ----------

DRSYS              15552         15
FTABLE          14025216      13697
INDX               25536         25
SYSTEM               704          1
TOOLS              10176         10
UNDOTBS1          449216        439
USERS              25536         25
XDB                  192          0

8 rows selected. Received on Thu Oct 21 2004 - 21:05:59 CDT