The Oracle Segment Advisor, created with Oracle
10g Release 2, can identify objects that have space for reclamation
or objects that have too much row chaining. The process is run
automatically by Oracle using the dbms_advisorpackage.
Use the function
asa_recommendations to verify if there are recommendations
for improvement in a particular segment. See an example in the code
below:
<
Code
2.12 - dbms_space_asa_recomm.sql
conn pkg/pkg#123
--Show tablespace reclaimable space
select
tablespace_name,
allocated_space,
used_space reclaimable_space
from
table(dbms_space.asa_recommendations('TRUE', 'TRUE', 'ALL'));
TABLESPACE_NAME
ALLOCATED_SPACE
RECLAIMABLE_SPACE
----------------- ---------------
-----------------
sysaux
292627118
205913743
ts_sa1
229874497
213349934
ts_sa2
4389377192
3465313855
ts_sa3
4853596429
3902675147
--Show index, table and lob reclaimable space
select
tablespace_name,
segment_owner,
segment_name,
segment_type,
allocated_space,
used_space,
reclaimable_space
from (
select
*
from
table(
dbms_space.asa_recommendations(
all_runs
=> TRUE,
show_manual
=> TRUE,
show_findings
=> ALL);
TABLESPACE_NAME
SEGMENT_OWNER
SEGMENT_NAME
SEGMENT_TYPE
ALLOCATED_SPACE USED_SPACE RECLAIMABLE_SPACE
------------------------------
------------------------------ ------------------------------
------------------ --------------- ---------- -----------------
ts_sales02_acom_wlp_data_m
sales102_acom_wlp
ad_bucket
table
65536
8192
57344
ts_sales02_acom_wlp_data_m
sales102_acom_wlp
ad_count
table
65536
8192
57344
TS ts_sales02_acom_wlp_data_m
sales102_acom_wlp
bt_event
table
65536
8192
57344
|
|
 |
|
Inside the DBMS Packages
The DBMS packages form the foundation of
Oracle DBA functionality. Now, Paulo Portugal writes a landmark book
Advanced Oracle DBMS Packages: The Definitive Reference.
This is a must-have book complete with a code
depot of working examples for all of the major DBMS packages.
Order directly from Rampant and save 30%.
|
|
|
| |
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
Burleson
Oracle ®
is the registered trademark of Oracle Corporation.
|
|
