Home » RDBMS Server » Server Administration » How to check what takes the most disk space ? (Oracle 12.2)
How to check what takes the most disk space ? [message #678687] Fri, 27 December 2019 07:32 Go to next message
a100
Messages: 34
Registered: March 2019
Member
Hello all,
is there any way I can check and display what DB object(s) (tables, indexes, etc ...) takes the most of disk space ?
Re: How to check what takes the most disk space ? [message #678688 is a reply to message #678687] Fri, 27 December 2019 08:11 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Query DBA_SEGMENTS.

More generally you can query DICT view to find such information.
For instance, for your question:
SQL> col comments format a80
SQL> select table_name, comments from dict where lower(comments) like '%storage%' order by 1;
TABLE_NAME                     COMMENTS
------------------------------ --------------------------------------------------------------------------------
DBA_RSRC_STORAGE_POOL_MAPPING  resource manager rules for mapping files to storage pools
DBA_SEGMENTS                   Storage allocated for all database segments
DBA_SEGMENTS_OLD               Storage allocated for all database segments
USER_SEGMENTS                  Storage allocated for all database segments
Re: How to check what takes the most disk space ? [message #678689 is a reply to message #678688] Fri, 27 December 2019 08:48 Go to previous messageGo to next message
a100
Messages: 34
Registered: March 2019
Member
I found the following:
Tablespace      Used MB.       Free MB. Total MB %Free
RDSADMIN	0	       7	7	100
UNDO_T1	        60	       780	840	93
USERS	        139	       3997	4136	97
SYSTEM	        507	       93	600	16
SYSAUX	        8008	       483	8491	6
And then in SYSAUX:
Server Manageability - Advisor Framework	6657.1875
Server Manageability - Optimizer Statistics History	688.5625
Server Manageability - Automatic Workload Repository	335.375
Unified Job Scheduler	79.75
Server Manageability - Other Components	63
XDB	62.25
LogMiner	14.625
Oracle Text	4.125
Transaction Layer - SCN to TIME mapping	3.25
SQL Management Base Schema	2.4375
PL/SQL Identifier Collection	2.375
Analytical Workspace Object Table	1.9375
OLAP API History Tables	1.9375
Logical Standby	1.5
Oracle Streams	1.1875
Enterprise Manager Monitoring User	0.9375
AUDSYS schema objects	0.625
Automated Maintenance Tasks	0.3125
Is there any way I can reduce "Server Manageability - Advisor Framework" disk usage ?
Re: How to check what takes the most disk space ? [message #678691 is a reply to message #678689] Fri, 27 December 2019 08:55 Go to previous messageGo to next message
EdStevens
Messages: 1250
Registered: September 2013
Senior Member
When presenting query results, you should also include the query that produced said results. That serves two purposes. First, it allows people to see what the data actually is. Second, it allows people to evaluate if the query was appropriate for actual problem resolution.

That said,

"Is there any way I can reduce "Server Manageability - Advisor Framework" disk usage ?"

Why bother? You could fit it on a thumb drive that is given away for free as marketing swag.

What actual business problem are you trying to solve?
Re: How to check what takes the most disk space ? [message #678693 is a reply to message #678691] Fri, 27 December 2019 09:26 Go to previous messageGo to next message
a100
Messages: 34
Registered: March 2019
Member
This database is in the cloud and I would like to reduce its storage cost.
Re: How to check what takes the most disk space ? [message #678694 is a reply to message #678693] Fri, 27 December 2019 09:27 Go to previous messageGo to next message
a100
Messages: 34
Registered: March 2019
Member
And this is the query:
select occupant_desc, space_usage_kbytes/1024 MB
from v$sysaux_occupants
where space_usage_kbytes > 0
order by space_usage_kbytes;
Re: How to check what takes the most disk space ? [message #678696 is a reply to message #678693] Fri, 27 December 2019 10:49 Go to previous messageGo to next message
EdStevens
Messages: 1250
Registered: September 2013
Senior Member
a100 wrote on Fri, 27 December 2019 09:26
This database is in the cloud and I would like to reduce its storage cost.
How much storage cost is saved reducing one 6gb component?
Re: How to check what takes the most disk space ? [message #678697 is a reply to message #678694] Fri, 27 December 2019 10:51 Go to previous messageGo to next message
EdStevens
Messages: 1250
Registered: September 2013
Senior Member
a100 wrote on Fri, 27 December 2019 09:27
And this is the query:
select occupant_desc, space_usage_kbytes/1024 MB
from v$sysaux_occupants
where space_usage_kbytes > 0
order by space_usage_kbytes;
And which result set did that produce? Don't make us guess or have to go back and piece things together from multiple messages. Use copy/paste from a sqlplus session to show both the query and the result as one contiguous, in-context, piece of information.
Re: How to check what takes the most disk space ? [message #678698 is a reply to message #678697] Fri, 27 December 2019 11:04 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

... and align the columns in the result.

Re: How to check what takes the most disk space ? [message #678699 is a reply to message #678693] Fri, 27 December 2019 11:35 Go to previous messageGo to next message
John Watson
Messages: 8348
Registered: January 2010
Location: Global Village
Senior Member
a100 wrote on Fri, 27 December 2019 15:26
This database is in the cloud and I would like to reduce its storage cost.
I hope you don't mind me asking a question, rather than providing an answer.

Which cloud provider are you using? What level of cost are you concerned about? For example, I work mostly with AWS (and a bit with Oracle Cloud and Rackspace). AWS EBS gp2 is USD0.10 per GB per month. When you add on space needed for backups and exports (and snapshots at USD0.05 per GB per month) I plan on $1 per GB per month. If I put all the non-live stuff on sc1 discs and move them to S3 pronto, I can get the price down to perhaps $0.20 per GB per month. What sort of figures are you looking at?
Re: How to check what takes the most disk space ? [message #678701 is a reply to message #678696] Fri, 27 December 2019 15:04 Go to previous messageGo to next message
EdStevens
Messages: 1250
Registered: September 2013
Senior Member
EdStevens wrote on Fri, 27 December 2019 10:49
a100 wrote on Fri, 27 December 2019 09:26
This database is in the cloud and I would like to reduce its storage cost.
How much storage cost is saved reducing one 6gb component?
My guess is that the time you've spent on this has already cost your company more than any potential savings on storage costs.
Re: How to check what takes the most disk space ? [message #678704 is a reply to message #678701] Sat, 28 December 2019 13:55 Go to previous message
a100
Messages: 34
Registered: March 2019
Member
You are right guys that it might be not worth optimizing the cost.
But at least I am learning something for myself.
Previous Topic: File I/O error
Next Topic: Out of Memory - Runaway Memory, not releasing ORA-04036: PGA memory used by the instance exceeds
Goto Forum:
  


Current Time: Mon Sep 28 09:12:04 CDT 2020