Databases Reference
In-Depth Information
13. Count the used database blocks by
MY_SALES_ALL
and
MY_SALES_2
tables after
space shrinking:
SELECT BLOCKS FROM DBA_TABLES
WHERE TABLE_NAME IN ('MY_SALES_ALL', 'MY_SALES_2');
14. View some statistics on full table scans:
SELECT NAME, VALUE FROM V$SYSSTAT
WHERE NAME LIKE '%table scan%';
15. Drop the tables created earlier:
DROP TABLE sh.MY_SALES_ALL;
DROP TABLE sh.MY_SALES_2;
How it works...
We have created two tables,
MY_SALES_ALL
and
MY_SALES_2
; they are equal and take
up 5157 database blocks, as shown in the following screenshot:
We have deleted more than 900,000 records from
MY_SALES_2
, which are about 90 percent
of the original records in the table. After re-analyzing the table, we execute the same
SELECT
statement, and the results are the same, as shown in the following screenshot:
