Another solution
Jan Chalupa, April 02, 2002 - 2:28 am UTC
And what about simply analyze all tables in your schema:
execute dbms_utility.analyze_schema('YOUR_SCHEMA_NAME', 'ESTIMATE');
(also exists procedure dbms_utility.analyze_database) and then:
SELECT *
FROM ALL_TABLES
WHERE owner = 'YOUR_SCHEMA_NAME'
AND num_rows = 0
April 02, 2002 - 8:27 am UTC
dbms_utility is legacy -- use dbms_stats.
Yes, you could analyze all of the tables as well. Brute force, works, but it could take a bit.
Very good solution!
kiro, April 02, 2002 - 7:33 am UTC
Hi Tom !
I have tested by myself. It is work.
I am using your show_space procedure litle changed - to work with partition tables and indexes.
Everyone can see result:
SQL> set pagesize 100;
SQL> set linesize 100;
SQL> set serverout on;
SQL> execute show_space_my ('TB_TRANSFER_ERR','MY_USER', 'TABLE');
Object Name *********** TB_TRANSFER_ERR
Object Type *********** TABLE
Free Blocks.............................1
Total Blocks............................152
% Free in Segment........................66
Total Space In MB.......................1.19
Unused Blocks...........................46
Unused Space In Mb.......................36
Last Used Ext FileId....................7
Last Used Ext BlockId...................114578
Last Used Block.........................10
Partition Name..........................NO PARTITIONS
PL/SQL procedure successfully completed.
SQL> delete from TB_TRANSFER_ERR;
4992 rows deleted.
SQL> execute show_space_my ('TB_TRANSFER_ERR','MY_USER', 'TABLE');
Object Name *********** TB_TRANSFER_ERR
Object Type *********** TABLE
Free Blocks.............................1
Total Blocks............................152
% Free in Segment........................66
Total Space In MB.......................1.19
Unused Blocks...........................46
Unused Space In Mb.......................36
Last Used Ext FileId....................7
Last Used Ext BlockId...................114578
Last Used Block.........................10
Partition Name..........................NO PARTITIONS
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> execute show_space_my ('TB_TRANSFER_ERR','MY_USER', 'TABLE');
Object Name *********** TB_TRANSFER_ERR
Object Type *********** TABLE
Free Blocks.............................105
Total Blocks............................152
% Free in Segment.......................69.08
Total Space In MB.......................1.19
Unused Blocks...........................46
Unused Space In Mb.......................36
Last Used Ext FileId....................7
Last Used Ext BlockId...................114578
Last Used Block.........................10
Partition Name..........................NO PARTITIONS
PL/SQL procedure successfully completed.
You can see 105(free) + 46 (above HWM) = 152(total) - 1
After that truncate table to deallocate space!
SQL> truncate table TB_TRANSFER_ERR;
Table truncated.
SQL> execute show_space_my ('TB_TRANSFER_ERR','MY_USER', 'TABLE');
Object Name *********** TB_TRANSFER_ERR
Object Type *********** TABLE
Free Blocks.............................0
Total Blocks............................16
% Free in Segment.......................0
Total Space In MB........................13
Unused Blocks...........................15
Unused Space In Mb.......................12
Last Used Ext FileId....................7
Last Used Ext BlockId...................5842
Last Used Block.........................1
Partition Name..........................NO PARTITIONS
Result is very good.