运维管理19 分钟阅读
SAP HANA 运维命令 100 条
SAP HANA 是以内存计算和列式存储为核心的数据库平台。HANA DBA 的日常工作横跨 SQL、系统监控视图、操作系统脚本、备份恢复、内存管理、持久化和 System Replication，不能只依赖图形化管理界面。
2026年8月6日阅读—点赞—收藏—
dba100hana墨力计划
内容阅读
在知识库中专注阅读，并随时返回相关工具与课程
在知识库中专注阅读，并随时返回相关工具与课程
SAP HANA 是以内存计算和列式存储为核心的数据库平台。HANA DBA 的日常工作横跨 SQL、系统监控视图、操作系统脚本、备份恢复、内存管理、持久化和 System Replication，不能只依赖图形化管理界面。
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 参数、卸载表和恢复数据库等操作，应先确认业务影响。
1hdbsql -n hana01:30015 -d APPDB -u SYSTEM -p 'Password'生产环境建议使用安全用户存储，避免在命令行明文输入密码。
1hdbuserstore SET APPKEY hana01:30015 SYSTEM命令会交互式要求输入密码。
1hdbsql -U APPKEY1SELECT VERSION2FROM M_DATABASE;1SELECT CURRENT_DATABASE2FROM DUMMY;1SELECT CURRENT_USER2FROM DUMMY;1SELECT CURRENT_TIMESTAMP, CURRENT_UTCTIMESTAMP2FROM DUMMY;1SELECT SYSTEM_ID, DATABASE_NAME, HOST, VERSION2FROM M_DATABASE;1SELECT DATABASE_NAME, ACTIVE_STATUS,2 ACTIVE_STATUS_DETAILS, RESTART_MODE3FROM M_DATABASES4ORDER BY DATABASE_NAME;该视图的完整内容通常需要从 SYSTEMDB 查询。
1SELECT *2FROM M_LICENSE;1HDB info1sapcontrol -nr 00 -function GetProcessList1SELECT *2FROM M_SYSTEM_OVERVIEW;1SELECT HOST, KEY, VALUE2FROM M_HOST_INFORMATION;具体列可能随 SPS 版本变化，可先使用 SELECT * 确认。
1SELECT HOST, PORT, SERVICE_NAME, PROCESS_ID,2 ACTIVE_STATUS, SQL_PORT, START_TIME3FROM M_SERVICES4ORDER BY HOST, PORT;1SELECT HOST, PORT, THREAD_TYPE, THREAD_STATE,2 COUNT(*) AS THREADS3FROM M_SERVICE_THREADS4GROUP BY HOST, PORT, THREAD_TYPE, THREAD_STATE5ORDER BY HOST, PORT, THREAD_TYPE;1SELECT HOST, PORT, SERVICE_NAME,2 ROUND(TOTAL_MEMORY_USED_SIZE / 1024 / 1024 / 1024, 2) AS USED_GB,3 ROUND(EFFECTIVE_ALLOCATION_LIMIT / 1024 / 1024 / 1024, 2) AS LIMIT_GB4FROM M_SERVICE_MEMORY5ORDER BY USED_GB DESC;1SELECT HOST,2 FREE_PHYSICAL_MEMORY,3 USED_PHYSICAL_MEMORY,4 FREE_SWAP_SPACE,5 USED_SWAP_SPACE6FROM M_HOST_RESOURCE_UTILIZATION;1SELECT HOST, TIME,2 CPU,3 SYSTEM_CPU,4 USER_CPU,5 WAITIO6FROM M_LOAD_HISTORY_HOST7ORDER BY TIME DESC8LIMIT 20;历史视图的采样和保留策略取决于系统配置。
1SELECT HOST, PORT, VOLUME_ID,2 FILE_TYPE, FILE_NAME,3 TOTAL_SIZE, USED_SIZE4FROM M_VOLUME_FILES5ORDER BY HOST, PORT, FILE_TYPE;1SELECT SCHEMA_NAME, SCHEMA_OWNER, CREATE_TIME2FROM SCHEMAS3ORDER BY SCHEMA_NAME;1SELECT SCHEMA_NAME, TABLE_NAME, TABLE_TYPE,2 IS_COLUMN_TABLE, IS_PARTITIONED3FROM TABLES4WHERE SCHEMA_NAME = 'APP'5ORDER BY TABLE_NAME;1SELECT COLUMN_NAME, POSITION, DATA_TYPE_NAME,2 LENGTH, SCALE, IS_NULLABLE3FROM TABLE_COLUMNS4WHERE SCHEMA_NAME = 'APP'5 AND TABLE_NAME = 'ORDERS'6ORDER BY POSITION;1SELECT SCHEMA_NAME, TABLE_NAME, INDEX_NAME,2 INDEX_TYPE, CONSTRAINT3FROM INDEXES4WHERE SCHEMA_NAME = 'APP'5ORDER BY TABLE_NAME, INDEX_NAME;1SELECT SCHEMA_NAME, VIEW_NAME, VIEW_TYPE2FROM VIEWS3WHERE SCHEMA_NAME = 'APP'4ORDER BY VIEW_NAME;1CREATE COLUMN TABLE APP.ORDERS (2 ORDER_ID BIGINT PRIMARY KEY,3 CUSTOMER_ID BIGINT NOT NULL,4 ORDER_TIME TIMESTAMP,5 AMOUNT DECIMAL(18,2),6 STATUS NVARCHAR(20)7);1CREATE ROW TABLE APP.CONFIG_ITEM (2 CONFIG_KEY NVARCHAR(100) PRIMARY KEY,3 CONFIG_VALUE NVARCHAR(1000)4);1ALTER TABLE APP.ORDERS2ADD (REMARK NVARCHAR(500));1CREATE INDEX IDX_ORDERS_CUSTOMER2ON APP.ORDERS (CUSTOMER_ID);1SELECT SCHEMA_NAME, TABLE_NAME,2 CONSTRAINT_NAME, CONSTRAINT_TYPE,3 IS_VALID4FROM CONSTRAINTS5WHERE SCHEMA_NAME = 'APP'6 AND TABLE_NAME = 'ORDERS'7ORDER BY CONSTRAINT_NAME;完整 DDL 可通过 SAP HANA Database Explorer 的导出功能获取。
1SELECT SCHEMA_NAME, TABLE_NAME,2 RECORD_COUNT,3 ROUND(MEMORY_SIZE_IN_TOTAL / 1024 / 1024, 2) AS MEMORY_MB4FROM M_CS_TABLES5WHERE SCHEMA_NAME = 'APP'6ORDER BY MEMORY_SIZE_IN_TOTAL DESC;1SELECT SCHEMA_NAME, TABLE_NAME,2 RECORD_COUNT,3 ROUND(ALLOCATED_FIXED_PART_SIZE / 1024 / 1024, 2) AS FIXED_MB,4 ROUND(ALLOCATED_VARIABLE_PART_SIZE / 1024 / 1024, 2) AS VARIABLE_MB5FROM M_RS_TABLES6WHERE SCHEMA_NAME = 'APP'7ORDER BY ALLOCATED_FIXED_PART_SIZE +8 ALLOCATED_VARIABLE_PART_SIZE DESC;1SELECT SCHEMA_NAME, TABLE_NAME,2 DISK_SIZE, MEMORY_SIZE_IN_TOTAL3FROM M_TABLE_PERSISTENCE_STATISTICS4WHERE SCHEMA_NAME = 'APP'5ORDER BY DISK_SIZE DESC;1SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME,2 MEMORY_SIZE_IN_TOTAL, DISTINCT_COUNT3FROM M_CS_ALL_COLUMNS4WHERE SCHEMA_NAME = 'APP'5ORDER BY MEMORY_SIZE_IN_TOTAL DESC6LIMIT 30;1SELECT SCHEMA_NAME, TABLE_NAME,2 LOADED, MEMORY_SIZE_IN_TOTAL3FROM M_CS_TABLES4WHERE SCHEMA_NAME = 'APP'5ORDER BY LOADED, TABLE_NAME;1LOAD APP.ORDERS ALL;1UNLOAD APP.ORDERS;卸载会影响后续首次访问延迟，不应对活跃业务表随意执行。
1SELECT HOST, PORT, SCHEMA_NAME, TABLE_NAME,2 RAW_RECORD_COUNT_IN_DELTA,3 LAST_MERGE_TIME4FROM M_CS_TABLES5WHERE SCHEMA_NAME = 'APP'6ORDER BY RAW_RECORD_COUNT_IN_DELTA DESC;1MERGE DELTA OF APP.ORDERS;通常应由系统自动管理，仅在明确诊断后手动执行。
1SELECT SCHEMA_NAME, TABLE_NAME, PART_ID,2 PARTITION_SPEC, RECORD_COUNT3FROM M_TABLE_PARTITIONS4WHERE SCHEMA_NAME = 'APP'5 AND TABLE_NAME = 'ORDERS'6ORDER BY PART_ID;1SELECT HOST, PORT, CONNECTION_ID, USER_NAME,2 CLIENT_HOST, CLIENT_IP, CONNECTION_STATUS3FROM M_CONNECTIONS4ORDER BY CONNECTION_ID;1SELECT HOST, PORT, CONNECTION_ID,2 STATEMENT_ID, USER_NAME,3 START_TIME, STATEMENT_STATUS, STATEMENT_STRING4FROM M_ACTIVE_STATEMENTS5ORDER BY START_TIME;1SELECT CONNECTION_ID, STATEMENT_ID, USER_NAME,2 START_TIME,3 SECONDS_BETWEEN(START_TIME, CURRENT_TIMESTAMP) AS ELAPSED_SECONDS,4 STATEMENT_STRING5FROM M_ACTIVE_STATEMENTS6WHERE START_TIME < ADD_SECONDS(CURRENT_TIMESTAMP, -300)7ORDER BY START_TIME;1SELECT HOST, PORT, CONNECTION_ID,2 TRANSACTION_ID, TRANSACTION_STATUS,3 START_TIME, UPDATE_TRANSACTION_ID4FROM M_TRANSACTIONS5ORDER BY START_TIME;1SELECT *2FROM M_BLOCKED_TRANSACTIONS3ORDER BY BLOCKED_TIME;1SELECT HOST, PORT, CONNECTION_ID,2 TRANSACTION_ID, SCHEMA_NAME, TABLE_NAME,3 LOCK_MODE, LOCK_OWNER_UPDATE_TRANSACTION_ID4FROM M_RECORD_LOCKS5ORDER BY SCHEMA_NAME, TABLE_NAME;1SELECT HOST, PORT, CONNECTION_ID,2 SCHEMA_NAME, OBJECT_NAME,3 OBJECT_TYPE, LOCK_MODE4FROM M_OBJECT_LOCKS5ORDER BY SCHEMA_NAME, OBJECT_NAME;1ALTER SYSTEM CANCEL SESSION '123456';1ALTER SYSTEM DISCONNECT SESSION '123456';1SELECT USER_NAME, CLIENT_HOST, COUNT(*) AS CONNECTIONS2FROM M_CONNECTIONS3GROUP BY USER_NAME, CLIENT_HOST4ORDER BY CONNECTIONS DESC;1SELECT START_TIME, DURATION_MICROSEC,2 CPU_TIME, MEMORY_SIZE,3 RECORDS, STATEMENT_STRING4FROM M_EXPENSIVE_STATEMENTS5ORDER BY START_TIME DESC6LIMIT 50;1SELECT STATEMENT_HASH, EXECUTION_COUNT,2 TOTAL_EXECUTION_TIME,3 TOTAL_CPU_TIME,4 TOTAL_MEMORY_SIZE,5 STATEMENT_STRING6FROM M_SQL_PLAN_CACHE7ORDER BY TOTAL_EXECUTION_TIME DESC8LIMIT 30;1SELECT STATEMENT_HASH,2 EXECUTION_COUNT,3 TOTAL_EXECUTION_TIME / NULLIF(EXECUTION_COUNT, 0)4 AS AVG_EXECUTION_TIME,5 STATEMENT_STRING6FROM M_SQL_PLAN_CACHE7WHERE EXECUTION_COUNT > 08ORDER BY AVG_EXECUTION_TIME DESC9LIMIT 30;1ALTER SYSTEM CLEAR SQL PLAN CACHE;该操作会造成计划重新编译，不应作为常规优化手段。
1EXPLAIN PLAN FOR2SELECT *3FROM APP.ORDERS4WHERE CUSTOMER_ID = 2001;1SELECT *2FROM EXPLAIN_PLAN_TABLE3ORDER BY OPERATOR_ID;1SELECT HOST, PORT, THREAD_ID,2 THREAD_TYPE, THREAD_METHOD,3 THREAD_STATE, STATEMENT_HASH4FROM M_SERVICE_THREADS5WHERE THREAD_STATE <> 'Inactive'6ORDER BY HOST, PORT, THREAD_ID;1SELECT HOST, PORT, SERVICE_NAME,2 THREAD_ID, FRAME_LEVEL, FRAME_NAME3FROM M_SERVICE_THREAD_CALLSTACKS4WHERE IS_ACTIVE = 'TRUE'5ORDER BY HOST, PORT, THREAD_ID, FRAME_LEVEL;读取调用栈通常需要较高权限，只应在性能诊断时短时查询。
1SELECT ALERT_TIMESTAMP, ALERT_ID, ALERT_RATING,2 ALERT_NAME, ALERT_DETAILS3FROM _SYS_STATISTICS.STATISTICS_CURRENT_ALERTS4ORDER BY ALERT_RATING DESC, ALERT_TIMESTAMP DESC;1SELECT ALERT_TIMESTAMP, ALERT_ID, ALERT_RATING,2 ALERT_NAME, ALERT_DETAILS3FROM _SYS_STATISTICS.STATISTICS_ALERTS_BASE4WHERE ALERT_TIMESTAMP > ADD_DAYS(CURRENT_TIMESTAMP, -1)5ORDER BY ALERT_TIMESTAMP DESC;1SELECT FILE_NAME, SECTION, KEY_VALUE, VALUE2FROM M_INIFILE_CONTENTS3WHERE FILE_NAME = 'global.ini'4 AND SECTION = 'memorymanager'5 AND KEY_VALUE = 'global_allocation_limit';1SELECT HOST, PORT, CATEGORY,2 INCLUSIVE_SIZE_IN_USE,3 EXCLUSIVE_SIZE_IN_USE4FROM M_HEAP_MEMORY5ORDER BY INCLUSIVE_SIZE_IN_USE DESC6LIMIT 30;1SELECT HOST, PORT, COMPONENT, NAME,2 INCLUSIVE_SIZE_IN_USE3FROM M_MEMORY_OBJECTS4ORDER BY INCLUSIVE_SIZE_IN_USE DESC5LIMIT 30;1SELECT HOST, PORT, SERVICE_NAME,2 TOTAL_MEMORY_USED_SIZE,3 EFFECTIVE_ALLOCATION_LIMIT4FROM M_SERVICE_MEMORY5ORDER BY HOST, PORT;1ALTER SYSTEM ALTER CONFIGURATION2('global.ini', 'SYSTEM')3SET ('memorymanager', 'global_allocation_limit') = '512000'4WITH RECONFIGURE;单位和取值范围必须以当前版本参数说明为准。
1SELECT HOST, PORT, VOLUME_ID,2 SERVICE_NAME, SUBPATH,3 VOLUME_REPLICATION_ROLE4FROM M_VOLUMES5ORDER BY HOST, PORT, VOLUME_ID;1SELECT HOST, PORT, VOLUME_ID, FILE_TYPE,2 SUM(TOTAL_SIZE) AS TOTAL_SIZE,3 SUM(USED_SIZE) AS USED_SIZE,4 COUNT(*) AS FILE_COUNT5FROM M_VOLUME_FILES6GROUP BY HOST, PORT, VOLUME_ID, FILE_TYPE7ORDER BY USED_SIZE DESC;1SELECT HOST, PORT, VOLUME_ID,2 FILE_NAME, FILE_SIZE,3 STATE4FROM M_LOG_SEGMENTS5ORDER BY HOST, PORT, FILE_NAME;1SELECT HOST, PORT,2 LAST_SAVEPOINT_START_TIME,3 LAST_SAVEPOINT_DURATION,4 LAST_SAVEPOINT_SIZE5FROM M_SAVEPOINTS6ORDER BY HOST, PORT;1ALTER SYSTEM SAVEPOINT;通常由系统自动执行，手工触发前应确认用途。
1BACKUP DATA USING FILE ('COMPLETE_20260727');生产环境通常应使用 Backint 或集中备份平台。
1SELECT ENTRY_ID, BACKUP_ID, ENTRY_TYPE_NAME,2 SYS_START_TIME, SYS_END_TIME,3 STATE_NAME, COMMENT4FROM M_BACKUP_CATALOG5ORDER BY SYS_START_TIME DESC;1SELECT ENTRY_ID, BACKUP_ID, ENTRY_TYPE_NAME,2 SYS_START_TIME, STATE_NAME, MESSAGE3FROM M_BACKUP_CATALOG4WHERE STATE_NAME <> 'successful'5ORDER BY SYS_START_TIME DESC;1SELECT BACKUP_ID, HOST, PORT, SERVICE_NAME,2 STATE_NAME, BACKUP_SIZE, TRANSFERRED_SIZE3FROM M_BACKUP_PROGRESS;1SELECT ENTRY_ID, BACKUP_ID, HOST,2 SERVICE_TYPE_NAME, DESTINATION_PATH3FROM M_BACKUP_CATALOG_FILES4ORDER BY ENTRY_ID DESC;具体路径列名可能随版本不同，应先查看视图结构。
1SELECT HOST, PORT, SERVICE_NAME,2 ENTRY_TYPE_NAME, ESTIMATED_SIZE3FROM M_BACKUP_SIZE_ESTIMATIONS;1SELECT *2FROM M_BACKUP_CONFIGURATION;1BACKUP CATALOG DELETE ALL BEFORE BACKUP_ID 123456789;是否同时删除物理文件取决于语法选项和备份介质，必须先核对当前版本文档。
1SELECT FILE_NAME, SECTION, KEY_VALUE, VALUE2FROM M_INIFILE_CONTENTS3WHERE FILE_NAME = 'global.ini'4 AND SECTION = 'persistence'5 AND KEY_VALUE IN ('enable_auto_log_backup',6 'basepath_logbackup');1SELECT TOP 10 BACKUP_ID, SYS_START_TIME,2 SYS_END_TIME, STATE_NAME3FROM M_BACKUP_CATALOG4WHERE ENTRY_TYPE_NAME = 'complete data backup'5 AND STATE_NAME = 'successful'6ORDER BY SYS_START_TIME DESC;1SELECT USER_NAME, USER_DEACTIVATED,2 LAST_SUCCESSFUL_CONNECT,3 PASSWORD_CHANGE_NEEDED4FROM USERS5ORDER BY USER_NAME;1CREATE USER APPUSER2PASSWORD "Replace_With_Strong_Password"3NO FORCE_FIRST_PASSWORD_CHANGE;是否允许取消首次改密应遵循企业安全规范。
1ALTER USER APPUSER2PASSWORD "Replace_With_New_Strong_Password";1ALTER USER APPUSER DEACTIVATE USER NOW;1ALTER USER APPUSER ACTIVATE USER NOW;1CREATE ROLE APP_READONLY;1GRANT SELECT2ON SCHEMA APP3TO APP_READONLY;1GRANT APP_READONLY TO APPUSER;1SELECT GRANTEE, ROLE_NAME, GRANTOR,2 IS_GRANTABLE3FROM GRANTED_ROLES4WHERE GRANTEE = 'APPUSER';1SELECT GRANTEE, OBJECT_TYPE,2 SCHEMA_NAME, OBJECT_NAME,3 PRIVILEGE, IS_GRANTABLE4FROM GRANTED_PRIVILEGES5WHERE GRANTEE = 'APPUSER';1HDB start1HDB stop1sapcontrol -nr 00 -function StartSystem HDB1sapcontrol -nr 00 -function StopSystem HDB1hdbnsutil -sr_state1python3 \2/usr/sap/HDB/HDB00/exe/python_support/systemReplicationStatus.py实际 SID、实例号及 Python 启动方式以安装环境为准。
1python3 \2/usr/sap/HDB/HDB00/exe/python_support/landscapeHostConfiguration.py1SELECT *2FROM M_SERVICE_REPLICATION;1cd /usr/sap/HDB/HDB00/hana01/trace2ls -ltr | tail1HDB info2sapcontrol -nr 00 -function GetProcessList3hdbsql -U APPKEY \4"SELECT HOST,PORT,SERVICE_NAME,ACTIVE_STATUS5 FROM M_SERVICES ORDER BY HOST,PORT"6hdbsql -U APPKEY \7"SELECT ENTRY_TYPE_NAME,SYS_START_TIME,STATE_NAME8 FROM M_BACKUP_CATALOG9 ORDER BY SYS_START_TIME DESC LIMIT 10"10df -h每日巡检还应覆盖活动告警、内存分配、长 SQL、阻塞事务、数据卷、日志备份、System Replication 和证书有效期。
SAP HANA 运维的关键是把内存、持久化、列存表、服务和 SQL 统一起来分析。出现性能问题时，应结合 M_ACTIVE_STATEMENTS、M_SQL_PLAN_CACHE、线程、锁和内存视图逐层定位，而不是仅看整机 CPU。
HANA 的系统视图和管理语法会随 SPS 版本演进，备份恢复、INI 参数和 System Replication 操作应严格匹配当前版本，并优先在 SAP HANA Cockpit 或受控运维流程中执行。