SAP HANA DBA 应该掌握的 100 条命令（建议收藏）

SAP HANA 是以内存计算和列式存储为核心的数据库平台。HANA DBA 的日常工作横跨 SQL、系统监控视图、操作系统脚本、备份恢复、内存管理、持久化和 System Replication不能只依赖图形化管理界面。下面整理 SAP HANA DBA 常用的 100 条命令主要面向 SAP HANA Platform 2.0 SPS 08 本地部署环境。HANA Cloud、单容器旧版本及不同 SPS 的系统视图和权限可能存在差异生产环境应以对应版本 SAP Help Portal 文档为准。文中的 SID、实例号、主机、端口、数据库、用户和路径均为示例。停库、重启服务、取消会话、修改 INI 参数、卸载表和恢复数据库等操作应先确认业务影响。一、连接与基础信息1. 使用 hdbsql 连接租户数据库hdbsql -n hana01:30015 -d APPDB -u SYSTEM -p Password生产环境建议使用安全用户存储避免在命令行明文输入密码。2. 创建安全连接键hdbuserstore SET APPKEY hana01:30015 SYSTEM命令会交互式要求输入密码。3. 使用安全连接键登录hdbsql -U APPKEY4. 查看数据库版本SELECT VERSION FROM M_DATABASE;5. 查看当前数据库SELECT CURRENT_DATABASE FROM DUMMY;6. 查看当前用户SELECT CURRENT_USER FROM DUMMY;7. 查看当前时间SELECT CURRENT_TIMESTAMP, CURRENT_UTCTIMESTAMP FROM DUMMY;8. 查看系统 ID 和数据库名称SELECT SYSTEM_ID, DATABASE_NAME, HOST, VERSION FROM M_DATABASE;9. 查看所有数据库SELECT DATABASE_NAME, ACTIVE_STATUS, ACTIVE_STATUS_DETAILS, RESTART_MODE FROM M_DATABASES ORDER BY DATABASE_NAME;该视图的完整内容通常需要从 SYSTEMDB 查询。10. 查看许可证SELECT * FROM M_LICENSE;二、系统、主机与服务11. 查看 HANA 进程HDB info12. 查看实例状态sapcontrol -nr 00 -function GetProcessList13. 查看系统概览SELECT * FROM M_SYSTEM_OVERVIEW;14. 查看主机SELECT HOST, KEY, VALUE FROM M_HOST_INFORMATION;具体列可能随 SPS 版本变化可先使用SELECT *确认。15. 查看服务SELECT HOST, PORT, SERVICE_NAME, PROCESS_ID, ACTIVE_STATUS, SQL_PORT, START_TIME FROM M_SERVICES ORDER BY HOST, PORT;16. 查看服务线程SELECT HOST, PORT, THREAD_TYPE, THREAD_STATE, COUNT(*) AS THREADS FROM M_SERVICE_THREADS GROUP BY HOST, PORT, THREAD_TYPE, THREAD_STATE ORDER BY HOST, PORT, THREAD_TYPE;17. 查看服务内存SELECT HOST, PORT, SERVICE_NAME, ROUND(TOTAL_MEMORY_USED_SIZE / 1024 / 1024 / 1024, 2) AS USED_GB, ROUND(EFFECTIVE_ALLOCATION_LIMIT / 1024 / 1024 / 1024, 2) AS LIMIT_GB FROM M_SERVICE_MEMORY ORDER BY USED_GB DESC;18. 查看主机资源SELECT HOST, FREE_PHYSICAL_MEMORY, USED_PHYSICAL_MEMORY, FREE_SWAP_SPACE, USED_SWAP_SPACE FROM M_HOST_RESOURCE_UTILIZATION;19. 查看 CPU 使用情况SELECT HOST, TIME, CPU, SYSTEM_CPU, USER_CPU, WAITIO FROM M_LOAD_HISTORY_HOST ORDER BY TIME DESC LIMIT 20;历史视图的采样和保留策略取决于系统配置。20. 查看磁盘使用情况SELECT HOST, PORT, VOLUME_ID, FILE_TYPE, FILE_NAME, TOTAL_SIZE, USED_SIZE FROM M_VOLUME_FILES ORDER BY HOST, PORT, FILE_TYPE;三、数据库对象与表21. 查看 SchemaSELECT SCHEMA_NAME, SCHEMA_OWNER, CREATE_TIME FROM SCHEMAS ORDER BY SCHEMA_NAME;22. 查看表SELECT SCHEMA_NAME, TABLE_NAME, TABLE_TYPE, IS_COLUMN_TABLE, IS_PARTITIONED FROM TABLES WHERE SCHEMA_NAME APP ORDER BY TABLE_NAME;23. 查看列定义SELECT COLUMN_NAME, POSITION, DATA_TYPE_NAME, LENGTH, SCALE, IS_NULLABLE FROM TABLE_COLUMNS WHERE SCHEMA_NAME APP AND TABLE_NAME ORDERS ORDER BY POSITION;24. 查看索引SELECT SCHEMA_NAME, TABLE_NAME, INDEX_NAME, INDEX_TYPE, CONSTRAINT FROM INDEXES WHERE SCHEMA_NAME APP ORDER BY TABLE_NAME, INDEX_NAME;25. 查看视图SELECT SCHEMA_NAME, VIEW_NAME, VIEW_TYPE FROM VIEWS WHERE SCHEMA_NAME APP ORDER BY VIEW_NAME;26. 创建列存表CREATE COLUMN TABLE APP.ORDERS ( ORDER_ID BIGINT PRIMARY KEY, CUSTOMER_ID BIGINT NOT NULL, ORDER_TIME TIMESTAMP, AMOUNT DECIMAL(18,2), STATUS NVARCHAR(20) );27. 创建行存表CREATE ROW TABLE APP.CONFIG_ITEM ( CONFIG_KEY NVARCHAR(100) PRIMARY KEY, CONFIG_VALUE NVARCHAR(1000) );28. 添加字段ALTER TABLE APP.ORDERS ADD (REMARK NVARCHAR(500));29. 创建索引CREATE INDEX IDX_ORDERS_CUSTOMER ON APP.ORDERS (CUSTOMER_ID);30. 查看表约束SELECT SCHEMA_NAME, TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE, IS_VALID FROM CONSTRAINTS WHERE SCHEMA_NAME APP AND TABLE_NAME ORDERS ORDER BY CONSTRAINT_NAME;完整 DDL 可通过 SAP HANA Database Explorer 的导出功能获取。四、表容量与列存管理31. 查看列存表大小SELECT SCHEMA_NAME, TABLE_NAME, RECORD_COUNT, ROUND(MEMORY_SIZE_IN_TOTAL / 1024 / 1024, 2) AS MEMORY_MB FROM M_CS_TABLES WHERE SCHEMA_NAME APP ORDER BY MEMORY_SIZE_IN_TOTAL DESC;32. 查看行存表大小SELECT SCHEMA_NAME, TABLE_NAME, RECORD_COUNT, ROUND(ALLOCATED_FIXED_PART_SIZE / 1024 / 1024, 2) AS FIXED_MB, ROUND(ALLOCATED_VARIABLE_PART_SIZE / 1024 / 1024, 2) AS VARIABLE_MB FROM M_RS_TABLES WHERE SCHEMA_NAME APP ORDER BY ALLOCATED_FIXED_PART_SIZE ALLOCATED_VARIABLE_PART_SIZE DESC;33. 查看表持久化大小SELECT SCHEMA_NAME, TABLE_NAME, DISK_SIZE, MEMORY_SIZE_IN_TOTAL FROM M_TABLE_PERSISTENCE_STATISTICS WHERE SCHEMA_NAME APP ORDER BY DISK_SIZE DESC;34. 查看列内存排行SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, MEMORY_SIZE_IN_TOTAL, DISTINCT_COUNT FROM M_CS_ALL_COLUMNS WHERE SCHEMA_NAME APP ORDER BY MEMORY_SIZE_IN_TOTAL DESC LIMIT 30;35. 查看表加载状态SELECT SCHEMA_NAME, TABLE_NAME, LOADED, MEMORY_SIZE_IN_TOTAL FROM M_CS_TABLES WHERE SCHEMA_NAME APP ORDER BY LOADED, TABLE_NAME;36. 将表加载到内存LOAD APP.ORDERS ALL;37. 从内存卸载表UNLOAD APP.ORDERS;卸载会影响后续首次访问延迟不应对活跃业务表随意执行。38. 查看 Delta Merge 状态SELECT HOST, PORT, SCHEMA_NAME, TABLE_NAME, RAW_RECORD_COUNT_IN_DELTA, LAST_MERGE_TIME FROM M_CS_TABLES WHERE SCHEMA_NAME APP ORDER BY RAW_RECORD_COUNT_IN_DELTA DESC;39. 手动执行 Delta MergeMERGE DELTA OF APP.ORDERS;通常应由系统自动管理仅在明确诊断后手动执行。40. 查看分区SELECT SCHEMA_NAME, TABLE_NAME, PART_ID, PARTITION_SPEC, RECORD_COUNT FROM M_TABLE_PARTITIONS WHERE SCHEMA_NAME APP AND TABLE_NAME ORDERS ORDER BY PART_ID;五、连接、事务与锁41. 查看当前连接SELECT HOST, PORT, CONNECTION_ID, USER_NAME, CLIENT_HOST, CLIENT_IP, CONNECTION_STATUS FROM M_CONNECTIONS ORDER BY CONNECTION_ID;42. 查看活动语句SELECT HOST, PORT, CONNECTION_ID, STATEMENT_ID, USER_NAME, START_TIME, STATEMENT_STATUS, STATEMENT_STRING FROM M_ACTIVE_STATEMENTS ORDER BY START_TIME;43. 查看长时间运行语句SELECT CONNECTION_ID, STATEMENT_ID, USER_NAME, START_TIME, SECONDS_BETWEEN(START_TIME, CURRENT_TIMESTAMP) AS ELAPSED_SECONDS, STATEMENT_STRING FROM M_ACTIVE_STATEMENTS WHERE START_TIME ADD_SECONDS(CURRENT_TIMESTAMP, -300) ORDER BY START_TIME;44. 查看事务SELECT HOST, PORT, CONNECTION_ID, TRANSACTION_ID, TRANSACTION_STATUS, START_TIME, UPDATE_TRANSACTION_ID FROM M_TRANSACTIONS ORDER BY START_TIME;45. 查看阻塞事务SELECT * FROM M_BLOCKED_TRANSACTIONS ORDER BY BLOCKED_TIME;46. 查看记录锁SELECT HOST, PORT, CONNECTION_ID, TRANSACTION_ID, SCHEMA_NAME, TABLE_NAME, LOCK_MODE, LOCK_OWNER_UPDATE_TRANSACTION_ID FROM M_RECORD_LOCKS ORDER BY SCHEMA_NAME, TABLE_NAME;47. 查看对象锁SELECT HOST, PORT, CONNECTION_ID, SCHEMA_NAME, OBJECT_NAME, OBJECT_TYPE, LOCK_MODE FROM M_OBJECT_LOCKS ORDER BY SCHEMA_NAME, OBJECT_NAME;48. 取消正在运行的会话语句ALTER SYSTEM CANCEL SESSION 123456;49. 断开会话ALTER SYSTEM DISCONNECT SESSION 123456;50. 查看连接来源统计SELECT USER_NAME, CLIENT_HOST, COUNT(*) AS CONNECTIONS FROM M_CONNECTIONS GROUP BY USER_NAME, CLIENT_HOST ORDER BY CONNECTIONS DESC;六、SQL 性能分析51. 查看昂贵语句SELECT START_TIME, DURATION_MICROSEC, CPU_TIME, MEMORY_SIZE, RECORDS, STATEMENT_STRING FROM M_EXPENSIVE_STATEMENTS ORDER BY START_TIME DESC LIMIT 50;52. 查看 SQL Plan CacheSELECT STATEMENT_HASH, EXECUTION_COUNT, TOTAL_EXECUTION_TIME, TOTAL_CPU_TIME, TOTAL_MEMORY_SIZE, STATEMENT_STRING FROM M_SQL_PLAN_CACHE ORDER BY TOTAL_EXECUTION_TIME DESC LIMIT 30;53. 查看平均耗时较高的 SQLSELECT STATEMENT_HASH, EXECUTION_COUNT, TOTAL_EXECUTION_TIME / NULLIF(EXECUTION_COUNT, 0) AS AVG_EXECUTION_TIME, STATEMENT_STRING FROM M_SQL_PLAN_CACHE WHERE EXECUTION_COUNT 0 ORDER BY AVG_EXECUTION_TIME DESC LIMIT 30;54. 清空 SQL Plan CacheALTER SYSTEM CLEAR SQL PLAN CACHE;该操作会造成计划重新编译不应作为常规优化手段。55. 查看执行计划EXPLAIN PLAN FOR SELECT * FROM APP.ORDERS WHERE CUSTOMER_ID 2001;56. 读取 Explain PlanSELECT * FROM EXPLAIN_PLAN_TABLE ORDER BY OPERATOR_ID;57. 查看线程采样SELECT HOST, PORT, THREAD_ID, THREAD_TYPE, THREAD_METHOD, THREAD_STATE, STATEMENT_HASH FROM M_SERVICE_THREADS WHERE THREAD_STATE Inactive ORDER BY HOST, PORT, THREAD_ID;58. 查看活动线程调用栈SELECT HOST, PORT, SERVICE_NAME, THREAD_ID, FRAME_LEVEL, FRAME_NAME FROM M_SERVICE_THREAD_CALLSTACKS WHERE IS_ACTIVE TRUE ORDER BY HOST, PORT, THREAD_ID, FRAME_LEVEL;读取调用栈通常需要较高权限只应在性能诊断时短时查询。59. 查看当前告警SELECT ALERT_TIMESTAMP, ALERT_ID, ALERT_RATING, ALERT_NAME, ALERT_DETAILS FROM _SYS_STATISTICS.STATISTICS_CURRENT_ALERTS ORDER BY ALERT_RATING DESC, ALERT_TIMESTAMP DESC;60. 查看历史告警SELECT ALERT_TIMESTAMP, ALERT_ID, ALERT_RATING, ALERT_NAME, ALERT_DETAILS FROM _SYS_STATISTICS.STATISTICS_ALERTS_BASE WHERE ALERT_TIMESTAMP ADD_DAYS(CURRENT_TIMESTAMP, -1) ORDER BY ALERT_TIMESTAMP DESC;七、内存、持久化与日志61. 查看全局内存限制SELECT FILE_NAME, SECTION, KEY_VALUE, VALUE FROM M_INIFILE_CONTENTS WHERE FILE_NAME global.ini AND SECTION memorymanager AND KEY_VALUE global_allocation_limit;62. 查看内存分配器SELECT HOST, PORT, CATEGORY, INCLUSIVE_SIZE_IN_USE, EXCLUSIVE_SIZE_IN_USE FROM M_HEAP_MEMORY ORDER BY INCLUSIVE_SIZE_IN_USE DESC LIMIT 30;63. 查看内存对象排行SELECT HOST, PORT, COMPONENT, NAME, INCLUSIVE_SIZE_IN_USE FROM M_MEMORY_OBJECTS ORDER BY INCLUSIVE_SIZE_IN_USE DESC LIMIT 30;64. 查看服务分配限制SELECT HOST, PORT, SERVICE_NAME, TOTAL_MEMORY_USED_SIZE, EFFECTIVE_ALLOCATION_LIMIT FROM M_SERVICE_MEMORY ORDER BY HOST, PORT;65. 修改全局内存限制ALTER SYSTEM ALTER CONFIGURATION (global.ini, SYSTEM) SET (memorymanager, global_allocation_limit) 512000 WITH RECONFIGURE;单位和取值范围必须以当前版本参数说明为准。66. 查看数据卷SELECT HOST, PORT, VOLUME_ID, SERVICE_NAME, SUBPATH, VOLUME_REPLICATION_ROLE FROM M_VOLUMES ORDER BY HOST, PORT, VOLUME_ID;67. 查看数据卷统计SELECT HOST, PORT, VOLUME_ID, FILE_TYPE, SUM(TOTAL_SIZE) AS TOTAL_SIZE, SUM(USED_SIZE) AS USED_SIZE, COUNT(*) AS FILE_COUNT FROM M_VOLUME_FILES GROUP BY HOST, PORT, VOLUME_ID, FILE_TYPE ORDER BY USED_SIZE DESC;68. 查看日志段SELECT HOST, PORT, VOLUME_ID, FILE_NAME, FILE_SIZE, STATE FROM M_LOG_SEGMENTS ORDER BY HOST, PORT, FILE_NAME;69. 查看 SavepointSELECT HOST, PORT, LAST_SAVEPOINT_START_TIME, LAST_SAVEPOINT_DURATION, LAST_SAVEPOINT_SIZE FROM M_SAVEPOINTS ORDER BY HOST, PORT;70. 触发 SavepointALTER SYSTEM SAVEPOINT;通常由系统自动执行手工触发前应确认用途。八、备份与恢复检查71. 执行文件型全量数据备份BACKUP DATA USING FILE (COMPLETE_20260727);生产环境通常应使用 Backint 或集中备份平台。72. 查看备份目录SELECT ENTRY_ID, BACKUP_ID, ENTRY_TYPE_NAME, SYS_START_TIME, SYS_END_TIME, STATE_NAME, COMMENT FROM M_BACKUP_CATALOG ORDER BY SYS_START_TIME DESC;73. 查看失败备份SELECT ENTRY_ID, BACKUP_ID, ENTRY_TYPE_NAME, SYS_START_TIME, STATE_NAME, MESSAGE FROM M_BACKUP_CATALOG WHERE STATE_NAME successful ORDER BY SYS_START_TIME DESC;74. 查看备份进度SELECT BACKUP_ID, HOST, PORT, SERVICE_NAME, STATE_NAME, BACKUP_SIZE, TRANSFERRED_SIZE FROM M_BACKUP_PROGRESS;75. 查看备份文件SELECT ENTRY_ID, BACKUP_ID, HOST, SERVICE_TYPE_NAME, DESTINATION_PATH FROM M_BACKUP_CATALOG_FILES ORDER BY ENTRY_ID DESC;具体路径列名可能随版本不同应先查看视图结构。76. 估算下一次备份大小SELECT HOST, PORT, SERVICE_NAME, ENTRY_TYPE_NAME, ESTIMATED_SIZE FROM M_BACKUP_SIZE_ESTIMATIONS;77. 查看备份配置SELECT * FROM M_BACKUP_CONFIGURATION;78. 删除过期备份目录记录BACKUP CATALOG DELETE ALL BEFORE BACKUP_ID 123456789;是否同时删除物理文件取决于语法选项和备份介质必须先核对当前版本文档。79. 检查自动日志备份SELECT FILE_NAME, SECTION, KEY_VALUE, VALUE FROM M_INIFILE_CONTENTS WHERE FILE_NAME global.ini AND SECTION persistence AND KEY_VALUE IN (enable_auto_log_backup, basepath_logbackup);80. 查看最近成功全备SELECT TOP 10 BACKUP_ID, SYS_START_TIME, SYS_END_TIME, STATE_NAME FROM M_BACKUP_CATALOG WHERE ENTRY_TYPE_NAME complete data backup AND STATE_NAME successful ORDER BY SYS_START_TIME DESC;九、用户与权限81. 查看用户SELECT USER_NAME, USER_DEACTIVATED, LAST_SUCCESSFUL_CONNECT, PASSWORD_CHANGE_NEEDED FROM USERS ORDER BY USER_NAME;82. 创建用户CREATE USER APPUSER PASSWORD Replace_With_Strong_Password NO FORCE_FIRST_PASSWORD_CHANGE;是否允许取消首次改密应遵循企业安全规范。83. 修改用户密码ALTER USER APPUSER PASSWORD Replace_With_New_Strong_Password;84. 停用用户ALTER USER APPUSER DEACTIVATE USER NOW;85. 激活用户ALTER USER APPUSER ACTIVATE USER NOW;86. 创建角色CREATE ROLE APP_READONLY;87. 授予 Schema 权限GRANT SELECT ON SCHEMA APP TO APP_READONLY;88. 将角色授予用户GRANT APP_READONLY TO APPUSER;89. 查看用户角色SELECT GRANTEE, ROLE_NAME, GRANTOR, IS_GRANTABLE FROM GRANTED_ROLES WHERE GRANTEE APPUSER;90. 查看对象权限SELECT GRANTEE, OBJECT_TYPE, SCHEMA_NAME, OBJECT_NAME, PRIVILEGE, IS_GRANTABLE FROM GRANTED_PRIVILEGES WHERE GRANTEE APPUSER;十、启停、高可用与巡检91. 启动 HANA 系统HDB start92. 停止 HANA 系统HDB stop93. 通过 sapcontrol 启动实例sapcontrol -nr 00 -function StartSystem HDB94. 通过 sapcontrol 停止实例sapcontrol -nr 00 -function StopSystem HDB95. 查看 System Replication 状态hdbnsutil -sr_state96. 查看复制整体状态python3 \ /usr/sap/HDB/HDB00/exe/python_support/systemReplicationStatus.py实际 SID、实例号及 Python 启动方式以安装环境为准。97. 查看主机故障转移配置python3 \ /usr/sap/HDB/HDB00/exe/python_support/landscapeHostConfiguration.py98. 查看系统复制服务状态SELECT * FROM M_SERVICE_REPLICATION;99. 查看 Trace 文件cd /usr/sap/HDB/HDB00/hana01/trace ls -ltr | tail100. 执行系统快速巡检HDB info sapcontrol -nr 00 -function GetProcessList hdbsql -U APPKEY \ SELECT HOST,PORT,SERVICE_NAME,ACTIVE_STATUS FROM M_SERVICES ORDER BY HOST,PORT hdbsql -U APPKEY \ SELECT ENTRY_TYPE_NAME,SYS_START_TIME,STATE_NAME FROM M_BACKUP_CATALOG ORDER BY SYS_START_TIME DESC LIMIT 10 df -h每日巡检还应覆盖活动告警、内存分配、长 SQL、阻塞事务、数据卷、日志备份、System Replication 和证书有效期。结语SAP HANA 运维的关键是把内存、持久化、列存表、服务和 SQL 统一起来分析。出现性能问题时应结合M_ACTIVE_STATEMENTS、M_SQL_PLAN_CACHE、线程、锁和内存视图逐层定位而不是仅看整机 CPU。HANA 的系统视图和管理语法会随 SPS 版本演进备份恢复、INI 参数和 System Replication 操作应严格匹配当前版本并优先在 SAP HANA Cockpit 或受控运维流程中执行。