- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: schema table storage size
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-05-2004 01:40 AM
тАО07-05-2004 01:40 AM
schema table storage size
We have a DB schema with many daily tables within a specific tablespace, what formula can one use to determine
what the space capacity(in GB/GB) a particular table is consuming within its tablespace.
Thanks in advance!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-05-2004 02:46 AM
тАО07-05-2004 02:46 AM
Re: schema table storage size
tablespace_name,
segment_type,
segment_name,
sum(bytes)/(1024*1024) size_gb
from
dba_segments
group by
tablespace_name,
segment_type,
segment_name;
Regards,
Fred
"Reality is just a point of view." (P. K. D.)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-05-2004 01:07 PM
тАО07-05-2004 01:07 PM
Re: schema table storage size
In addition to what Fred said, to determine Real Space used by a Table ie. are not empty,
you can do by the ROWID.
Each row in the table has pseudocolumn called ROWID. This pseudo contains information about physical location of the row in format block_number.row.file
If the table is stored in a tablespace which has one datafile, all we have to do is to get DISTINCT number of block_number from ROWID column of this table.
But if the table is stored in a tablespace with more than one datafile then you can have the same block_number but in different datafiles so we have to get DISTINCT number of block_number+file from ROWID.
The SELECT statements which give us the number of "really used" blocks is below. They are different for ORACLE 7 and ORACLE 8 because of different structure of ROWID column in these versions.
For ORACLE 7:
SELECT COUNT(DISTINCT SUBSTR(rowid,15,4)||
SUBSTR(rowid,1,8)) "Used"
FROM schema.table;
For ORACLE 8+:
SELECT COUNT (DISTINCT
DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)||
DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) "Used"
FROM schema.table;
or
SELECT COUNT (DISTINCT SUBSTR(rowid,1,15)) "Used"
FROM schema.table;
You could ask why the above information could not be determined by using the ANALYZE TABLE command. The ANALYZE TABLE command only identifies the number of 'ever' used blocks or the high water mark for the table.
I hipe this helps.
Indira A
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-05-2004 11:18 PM
тАО07-05-2004 11:18 PM
Re: schema table storage size
Thanks for the info. The value returned is it the no. of bytes/kb/Mb/Gb? Or does one have to do a conversion?
Thanks in advance!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-06-2004 08:42 PM
тАО07-06-2004 08:42 PM
Re: schema table storage size
some more clarifications and summary.
consider the attached script free.sql
it will generate a report like:
Tablespace Name KBytes Used Free Used Largest
________________ ____________ ____________ ____________ ______ ____________
DRSYS 86,016 4,232 81,784 4.9 81,784
OEM_REPOSITORY 30,728 27,912 2,816 90.8 2,816
...
------------ ------------ ------------
sum 25,699,592 19,860,136 5,839,456
which shows us:
Kbytes = space allocated to the tablespace currently.
Used = space allocated within the tablespace to specific objects
Free = space NOT yet allocated to any objects
Used = % of space allocated to objects in tablespace
Largest= Largest free contigous extent available (NEXT_EXTENTS larger then this will FAIL)
and now, to find the amount of free space within the allocated space, we have 2 choices:
a. analyze tables frequently. Then the "EMPTY_BLOCKS" column in user_tables will be populated
b. use the dbms_space package to find the free space available.
hope this helps!
regards
Yogeeraj
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-06-2004 08:44 PM
тАО07-06-2004 08:44 PM
Re: schema table storage size
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-06-2004 08:59 PM
тАО07-06-2004 08:59 PM
Re: schema table storage size
We need to find out the storage space of a table, not the tablespace size.
Thanks in advance!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-06-2004 11:07 PM
тАО07-06-2004 11:07 PM
Re: schema table storage size
About Indira's answer : Result is given in blocks. So you will need to convert. Bock size is given in bytes by the oracle parameter db_block_size :
show parameter db_block_size
Note, that it may be a little more complex if you have different block size on your tablespaces.
Regards,
Fred
"Reality is just a point of view." (P. K. D.)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-06-2004 11:27 PM
тАО07-06-2004 11:27 PM
Re: schema table storage size
The db_block_size is 8192.Any idea of what the conversion formula would be?
Thanks in advance!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-06-2004 11:47 PM
тАО07-06-2004 11:47 PM
Re: schema table storage size
SELECT 8192*(COUNT (DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)||DBMS_ROWID.ROWID_RELATIVE_FNO(rowid))) "bytes" FROM table_name;
Regards,
Fred
"Reality is just a point of view." (P. K. D.)