- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: Table Space - Very Urgent
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-29-2003 07:06 AM
тАО07-29-2003 07:06 AM
Table Space - Very Urgent
Task is to find out how much space is been occupied by our tables( 32 tables)
We have 7 table spaces and we access only two table spaces for our tables
and indexes.These TSs also been shared by other group.
Now we need to find out how much space is been occupied by our 32 tables and
related indexes.
How to find this out.??
Thanks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-29-2003 07:19 AM
тАО07-29-2003 07:19 AM
Re: Table Space - Very Urgent
you just query the dba_free_space:
select tablespace_name, sum(bytes)
from dba_free_space
gorup by tablespace_name;
This will reveal the free space in each tablespace.
Massimo
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-29-2003 07:22 AM
тАО07-29-2003 07:22 AM
Re: Table Space - Very Urgent
from dba_data_files
order by tablespace_name;
will show you allocated space.
if you subtract this value from the value from previous post, you will get the used space.
Massimo
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-29-2003 07:31 AM
тАО07-29-2003 07:31 AM
Re: Table Space - Very Urgent
here is a oracle command to show tables size:
select a.owner "Owner", a.table_name "Name", round(b.bytes/1024) "Total Kb", round(a.blocks*4) "HWM Kb",
round(((a.blocks*a.avg_space)/1024)+(a.empty_blocks*4)) "Total Free Space Kb",
decode(a.blocks,0,'Vide',round(((a.blocks*(4-a.avg_space/1024))/(a.blocks*4))*100)) "Pct used HWM",
round((((b.bytes/1024)-(a.blocks*4))/(b.bytes/1024))*100) "HWM Pct"
from sys.dba_tables a, sys.dba_segments b
where a.table_name=b.segment_name
and b.segment_type='TABLE'
and a.owner not like 'SYS%'
order by 1,2;
Here is another one for tablespaces size:
select tablespace_name "Tablespace",round(min(bytes)/1024) "Min Free Kb",
round(max(bytes)/1024) "Max Free Kb",round (sum(bytes)/1024) "Total Free Kb",count(*) "Nb Free Segments"
from sys.dba_free_space
group by tablespace_name;
Hope this helps,
FiX
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-29-2003 08:40 AM
тАО07-29-2003 08:40 AM
Re: Table Space - Very Urgent
my sqlplus session says thsi.
ERROR at line 1:
ORA-00937: not a single-group function
logon as oracle
sqlplus internal
I would note that the next two sql statements after Massimo's report product good reports.
I would like to try Massimo's stuff out. Why the error?
SEP
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-29-2003 08:44 AM
тАО07-29-2003 08:44 AM
Re: Table Space - Very Urgent
SQL should read :
select tablespace_name, sum(bytes)
from dba_data_files
GROUP by tablespace_name;
Are the tablespaces locally managed ?
JL
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-29-2003 08:53 AM
тАО07-29-2003 08:53 AM
Re: Table Space - Very Urgent
Jan-Luc, you are right, i used ""group by""
but when doing the cut&paste i did some garbage.
Here are correct ones:
SQL> select tablespace_name, sum(bytes) from dba_free_space
2 group by tablespace_name;
TABLESPACE_NAME SUM(BYTES)
------------------------------ ----------
CATATS 25812992
CATIP50 40755200
CATMA77 40755200
CATPITE 40755200
INDX 56614912
RBS 302587904
RCVCAT 40755200
SYSTEM 8994816
TEMP 4775936
USERS 52420608
10 rows selected.
SQL> select tablespace_name, sum(bytes) from dba_data_files
2 group by tablespace_name;
TABLESPACE_NAME SUM(BYTES)
------------------------------ ----------
CATATS 52428800
CATIP50 52428800
CATMA77 52428800
CATPITE 52428800
INDX 56623104
RBS 429916160
RCVCAT 52428800
SYSTEM 73400320
TEMP 5242880
USERS 52428800
10 rows selected.
Sorry :)
Massimo
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-29-2003 09:15 AM
тАО07-29-2003 09:15 AM
Re: Table Space - Very Urgent
Thanks.
I think his last post deserves a rabbit.
SEP
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-29-2003 02:26 PM
тАО07-29-2003 02:26 PM
Re: Table Space - Very Urgent
For a more detailed one you may wish to use this one:
-- free.sql
-- usage @free
-- @free 1
-- This SQL Plus script lists freespace by tablespace
(attached)
for example on my system it shows: Max
Max pct
Tablespa KBytes Used Free Used Largest Kbytes Used
SYSTEM 201,728 200,960 768 99.6 704 33,554,416 .6
shows that my system tablespace is currently 201m and has 0.4% fre HOWEVER it can grow to 33gig and hence has only used 0.6% of its potential.
hope this helps!
regards
Yogeeraj
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-29-2003 02:27 PM
тАО07-29-2003 02:27 PM