当前位置： 首页 > news >正文

段指导-示例

news 2025/6/29 12:59:16

RDBMS 19.20

参考文档：

Database Administrator’s Guide
19 Managing Space for Schema Objects
19.3.2.4 Running the Segment Advisor Manually

针对表SOE.CUSTOMERS进行段指导
 

-- 创建段指导

variable id number;
begindeclarename varchar2(100);descr varchar2(500);obj_id number;beginname:='Manual_Employees';descr:='Segment Advisor Example';dbms_advisor.create_task (advisor_name     => 'Segment Advisor',task_id          => :id,task_name        => name,task_desc        => descr);dbms_advisor.create_object (task_name        => name,object_type      => 'TABLE',attr1            => 'SOE',attr2            => 'CUSTOMERS',attr3            => NULL,attr4            => NULL,attr5            => NULL,object_id        => obj_id);dbms_advisor.set_task_parameter(task_name        => name,parameter        => 'recommend_all',value            => 'TRUE');dbms_advisor.execute_task(name);end;
end; 
/

-- 查看段指导是否运行完毕

select task_name, statusfrom dba_advisor_taskswhere task_name = 'Manual_Employees'and advisor_name = 'Segment Advisor';

-- 查看段指导对应的结果

select af.task_name, ao.attr2 segname, ao.attr3 partition, ao.type, af.message from dba_advisor_findings af, dba_advisor_objects aowhere ao.task_id = af.task_idand ao.object_id = af.object_idand af.task_name = 'Manual_Employees'select task_name,message,more_info from dba_advisor_findings  where task_name = 'Manual_Employees'
--select * from DBA_ADVISOR_RECOMMENDATIONS where task_name = 'Manual_Employees'
--select * from DBA_ADVISOR_ACTIONS where task_name = 'Manual_Employees'
select task_name,command,attr1,attr2,attr3 from DBA_ADVISOR_ACTIONS where task_name like '%SYS_AUTO_SPCADV%'
select * from DBA_ADVISOR_OBJECTS where task_name = 'Manual_Employees'

-- 查看近期的段指导建议

select tablespace_name,segment_name,segment_type,partition_name,recommendations,c1from table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'));

--部分结果示例

SYS@test>select task_name,message,more_info from dba_advisor_findings  where task_name = 'Manual_Employees'2  ;TASK_NAME
--------------------------------------------------------------------------------
MESSAGE
--------------------------------------------------------------------------------
MORE_INFO
--------------------------------------------------------------------------------
Manual_Employees
The free space in the object is less than 10MB.
Allocated Space:180355072: Used Space:178803055: Reclaimable Space :1552017:SYS@test>
SYS@test>select task_name,command,attr1,attr2,attr3 from DBA_ADVISOR_ACTIONS where task_name like '%SYS_AUTO_SPCADV%';TASK_NAME
--------------------------------------------------------------------------------
COMMAND
----------------------------------------------------------------
ATTR1
--------------------------------------------------------------------------------
ATTR2
--------------------------------------------------------------------------------
ATTR3
--------------------------------------------------------------------------------
SYS_AUTO_SPCADV126200902092023
SHRINK SPACE
alter table "OGG"."GGS_DDL_HIST" shrink spaceTASK_NAME
--------------------------------------------------------------------------------
COMMAND
----------------------------------------------------------------
ATTR1
--------------------------------------------------------------------------------
ATTR2
--------------------------------------------------------------------------------
ATTR3
--------------------------------------------------------------------------------
alter table "OGG"."GGS_DDL_HIST" shrink space COMPACT
alter table "OGG"."GGS_DDL_HIST" enable row movementTASK_NAME
--------------------------------------------------------------------------------
COMMAND
----------------------------------------------------------------
ATTR1
--------------------------------------------------------------------------------
ATTR2
--------------------------------------------------------------------------------
ATTR3
--------------------------------------------------------------------------------
SYS_AUTO_SPCADV126200902092023
ENABLE COMPRESSION
alter table "SOE"."ORDERS"  compress for oltpTASK_NAME
--------------------------------------------------------------------------------
COMMAND
----------------------------------------------------------------
ATTR1
--------------------------------------------------------------------------------
ATTR2
--------------------------------------------------------------------------------
ATTR3
--------------------------------------------------------------------------------
alter table "SOE"."ORDERS"  moveTASK_NAME
--------------------------------------------------------------------------------
COMMAND
----------------------------------------------------------------
ATTR1
--------------------------------------------------------------------------------
ATTR2
--------------------------------------------------------------------------------
ATTR3
--------------------------------------------------------------------------------
SYS_AUTO_SPCADV126200902092023
ENABLE COMPRESSION
alter table "SOE"."CUSTOMERS"  compress for oltpTASK_NAME
--------------------------------------------------------------------------------
COMMAND
----------------------------------------------------------------
ATTR1
--------------------------------------------------------------------------------
ATTR2
--------------------------------------------------------------------------------
ATTR3
--------------------------------------------------------------------------------
alter table "SOE"."CUSTOMERS"  moveTASK_NAME
--------------------------------------------------------------------------------
COMMAND
----------------------------------------------------------------
ATTR1
--------------------------------------------------------------------------------
ATTR2
--------------------------------------------------------------------------------
ATTR3
--------------------------------------------------------------------------------
SYS_AUTO_SPCADV126200902092023
ENABLE COMPRESSION
alter table "OGG"."GGS_DDL_HIST"  compress for oltpTASK_NAME
--------------------------------------------------------------------------------
COMMAND
----------------------------------------------------------------
ATTR1
--------------------------------------------------------------------------------
ATTR2
--------------------------------------------------------------------------------
ATTR3
--------------------------------------------------------------------------------
alter table "OGG"."GGS_DDL_HIST"  moveTASK_NAME
--------------------------------------------------------------------------------
COMMAND
----------------------------------------------------------------
ATTR1
--------------------------------------------------------------------------------
ATTR2
--------------------------------------------------------------------------------
ATTR3
--------------------------------------------------------------------------------
SYS_AUTO_SPCADV258251302092023
SHRINK SPACE
alter table "OGG"."GGS_DDL_HIST" shrink spaceTASK_NAME
--------------------------------------------------------------------------------
COMMAND
----------------------------------------------------------------
ATTR1
--------------------------------------------------------------------------------
ATTR2
--------------------------------------------------------------------------------
ATTR3
--------------------------------------------------------------------------------
alter table "OGG"."GGS_DDL_HIST" shrink space COMPACT
alter table "OGG"."GGS_DDL_HIST" enable row movementTASK_NAME
--------------------------------------------------------------------------------
COMMAND
----------------------------------------------------------------
ATTR1
--------------------------------------------------------------------------------
ATTR2
--------------------------------------------------------------------------------
ATTR3
--------------------------------------------------------------------------------
SYS_AUTO_SPCADV616480909092023
SHRINK SPACE
alter table "OGG"."GGS_DDL_HIST" shrink spaceTASK_NAME
--------------------------------------------------------------------------------
COMMAND
----------------------------------------------------------------
ATTR1
--------------------------------------------------------------------------------
ATTR2
--------------------------------------------------------------------------------
ATTR3
--------------------------------------------------------------------------------
alter table "OGG"."GGS_DDL_HIST" shrink space COMPACT
alter table "OGG"."GGS_DDL_HIST" enable row movementTASK_NAME
--------------------------------------------------------------------------------
COMMAND
----------------------------------------------------------------
ATTR1
--------------------------------------------------------------------------------
ATTR2
--------------------------------------------------------------------------------
ATTR3
--------------------------------------------------------------------------------
SYS_AUTO_SPCADV705541309092023
SHRINK SPACE
alter table "OGG"."GGS_DDL_HIST" shrink spaceTASK_NAME
--------------------------------------------------------------------------------
COMMAND
----------------------------------------------------------------
ATTR1
--------------------------------------------------------------------------------
ATTR2
--------------------------------------------------------------------------------
ATTR3
--------------------------------------------------------------------------------
alter table "OGG"."GGS_DDL_HIST" shrink space COMPACT
alter table "OGG"."GGS_DDL_HIST" enable row movementTASK_NAME
--------------------------------------------------------------------------------
COMMAND
----------------------------------------------------------------
ATTR1
--------------------------------------------------------------------------------
ATTR2
--------------------------------------------------------------------------------
ATTR3
--------------------------------------------------------------------------------
SYS_AUTO_SPCADV705541309092023
ENABLE COMPRESSION
alter table "OGG"."GGS_DDL_HIST"  compress for oltpTASK_NAME
--------------------------------------------------------------------------------
COMMAND
----------------------------------------------------------------
ATTR1
--------------------------------------------------------------------------------
ATTR2
--------------------------------------------------------------------------------
ATTR3
--------------------------------------------------------------------------------
alter table "OGG"."GGS_DDL_HIST"  move8 rows selected.SYS@test>

END

http://www.lryc.cn/news/165978.html

相关文章：

  • LeetCode 面试题 04.02. 最小高度树
  • 华为云云耀云服务器L实例评测｜初始化centos镜像到安装nginx部署前端vue、react项目
  • python项目制作docker镜像，加装引用模块，部署运行！
  • Redis缓存设计与性能优化
  • 免杀对抗-Python-混淆算法+反序列化-打包生成器-Pyinstall
  • C#__线程池的简单介绍和使用
  • 安全员（岗位职责）
  • unity 使用声网（Agora）实现语音通话
  • vue2.X 中使用 echarts5.4.0实现项目进度甘特图
  • 《PostgreSQL与NoSQL：合作与竞争的关系》
  • 【FAQ】视频监控管理平台/视频汇聚平台EasyCVR安全检查相关问题及解决方法3.0
  • Java 8 新特性解读及应用实践
  • C++项目实战——基于多设计模式下的同步异步日志系统-④-日志系统框架设计
  • 计算机专业毕业设计项目推荐02-个人医疗系统（Java+原生Js+Mysql）
  • Nginx__高级进阶篇之LNMP动态网站环境部署
  • Zebec 生态 AMA 回顾：Nautilus 以及 $ZBC 的未来
  • NXP iMX8MM 修改 UART4至 Cortex-A53 核心
  • C#控制台程序中使用log4.net来输出日志
  • 用GPT干的18件事，能够真正提高学习生产力，建议收藏
  • 线性代数的本质(二)——线性变换与矩阵
  • JavaScript获取字符串的字节长度
  • [2023.09.13]: Rust Lang，避不开的所有权问题
  • Redux中间件源码解析与实现
  • 关于rsync用不了之后
  • 由一个多线程并发保存而引发的思考
  • python-vlc
  • 2023长城杯 web部分题目(seekingeasy_extension)
  • 2-1 张量数据结构
  • QSqlQuery查询语句
  • 用c语言编写出三底模型