CODE DEPOT
This is the seventh of Kevin Loney's "10 Scripts Every DBA Should Have." Watch for a complete article on how to use these scripts in a future issue of Oracle Magazine. There are other valuable scripts as well, but these ten--covering backup/recovery, space management, account management, maintenance, and tuning--provide basic guideposts to start from during your administration activities.
Don't leave $ORACLE_HOME without them!
When you replace "sometable" with the name of a table in your database, the query below will return the number of blocks used by the data in that table. The query examines the Block_ID (columns 1-8) and the File_ID (columns 15-18) of the ROWID. The query then returns the number of distinct blocks used by the records within the table. The ANALYZE command can also provide this information, but this query is faster.
APRIL 15, 1996
No. 7: Blocks Used by Data in a Table
This "Code Depot" entry comes from Kevin Loney, the author of two of the first three books from Oracle Press: Oracle DBA Handbook and Oracle: The Complete Reference, Third Edition (with George Koch). See the Osborne/McGraw-Hill Web site at www.osborne.com. for sample chapters from these books and the scripts from the Oracle DBA Handbook.select COUNT(DISTINCT(SUBSTR(ROWID,1,8)||SUBSTR(ROWID,15,4)))
Blocks_Used
from sometable;
All specific technical questions from users of Oracle supported products should be directed to Oracle's Technical Support Department. Oracle does not provide any warranty as to the accuracy of any information provided through Oracle Magazine Interactive. Oracle shall not be liable for any damages incurred as a result of reliance on any information provided herein.
Copyright © 1994, 1995, 1996 & 1997 Oracle Corporation. All Rights Reserved.