deleted tables still present? 2

[aking]

Hi, a simple question about deleting tables.
i'm trying to clear out my production database.
i have a script - which is basically DELETE FROM statements for a whole list of tables.
I have run this script - successfully.
and i have bounced the database.
But i can still see the tables in Enterprise Manager and they still all have exactly the same number of rows as before. The database is still exactly the same size.
If i run a select statement on any table i get 'no rows selected'

The question is are these tables truly empty?
How can i prove this?
And if they are empty why do they look identical to before running the clear_all script in Enterprise Manager?

i suspect that Enterprise Manager is telling me old information but i need to be sure as it is the production environment.

 

[Dagon]

Where are you getting the information about the number of rows ? Is it from SYS.DBA_TABLES ? If so, you are looking at the statistical information that was collected last time DBMS_STATS or analyze table was run. You will need to re-run your stats commands against all the tables to get up-to-date row counts.

 

[Thargy]

I'm starting with the obvious one here, but does your script contain the immortal word "commit"?

If it doesn't, a different tool used to view the tables will correctly display all the records.

Regards

T

 

[LKBrwnDBA]

Using the DELETE command will only remove the rows from a table but will not release the storage (allocated extents).
If you want to remove ALL rows and release all extents for a table use:

TRUNCATE TABLE {Table Name} DROP STORAGE;

If you use DELETE to remove a set of rows, then you would need to re-organize the table to release the extents.

ALTER TABLE {Table Name} MOVE {TABLESPACE...};





----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[aking]

yes i've got commit in the script - the last line.
i also rebuilt the indexes.
releasing the extents is the thing i was missing.
And Dagon i think you've put your finger on it.
I am now sure that Enterprise Manager is telling me old information (i assumed that a view of tables within EM would give up to date information) but judging from the response from metalink - they say to trust the output from sqlplus, and have transferred me to the Enterprise Manager team - i think i've been looking at old statistics.
I'll go and learn how to analyze tables.
Thanks for the comments guys.

 

[LKBrwnDBA]

PS: If you actually want to remove the tables, use:

DROP TABLE {table_name};


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb