HANA数据库常用语法记录（不定期更新）

HANA数据库常用语法记录（不定期更新）

增

--	2021/06/24
--	Add column to a table
ALTER TABLE TEPMLATE ADD (ID BIGINT);
--	Add primary key
ALTER TABLE TEMPLATE ADD PRIMARY KEY(ID)
--	Add unique constraint
ALTER TABLE TEMPLATE ADD UNIQUE(CODE)
--	2022/07/19
--	Add Object privilege from one schema to another
GRANT SELECT ON EPM.ZTC_NS_JK TO ZTCREAD;
--	Grant the SELECT privilege on any object in schema EPM to the READONLY user
GRANT SELECT ON SCHEMA "EPM" TO READONLY

删

--	2021/06/24
--	Drop primary key
ALTER TABLE TEMPLATE DROP PRIMARY KEY;	
--	Drop column
ALTER TABLE UCUBE DROP(DIMENSIONS);

改

--	2021/06/24
--	change some content in a table
UPDATE ROLES SET ROLE_NAME='PUBLIC' WHERE ID=100000264

--	2022/01/27
--	change data type
ALTER TABLE M_HXCL_V ALTER (YNHL DECIMAL(32,10));

--	2022/02/18
--	multiply a random number 
UPDATE M_SALEBOMO SET SL=SL*RAND();

--	2023/01/06
--	RENAME COLUMN CL22 TO CL2
RENAME COLUMN "EPM"."D_DEFAULT_CUSTOMER".CL22 TO CL2

-- 2023/04/07
-- REUSE LAST 5 PASSWORD
Alter SYSTEM ALTER CONFIGURATION ('indexserver.ini','SYSTEM') set ('password policy','last_used_passwords')='0' with reconfigure;

ALTER USER EPM PASSWORD Abc_20230407 NO FORCE_FIRST_PASSWORD_CHANGE;

Alter SYSTEM ALTER CONFIGURATION ('indexserver.ini','SYSTEM') set ('password policy','last_used_passwords')='5' with reconfigure;

-- RECOVER DATA FOR A DATABASE
RECOVER DATABASE FOR EPM UNTIL TIMESTAMP '2023-07-31 04:00:00'

查

HANA数据库监控：HANA数据库自身提供了内存内存池、内存管理器、外部指标等信息的监控，可通过控制台查看，也可通过SQL语句查询，这里只介绍部分SQL语句查询。

--	查询HANA系统中所有列存储的数据库表一共使用了多少内存
SELECT
	ROUND(SUM(MEMORY_SIZE_IN_TOTAL)/1024/1024) AS "Column Tables MB Used"
FROM M_CS_TABLES

--	显示当前SAP HANA系统中所有SCHEMA占用的内存空间
SELECT 
	SCHEMA_NAME AS "Schema",
	ROUND(SUM(MEMORY_SIZE_IN_TOTAL)/1024/1024) AS "MB Used"
FROM M_CS_TABLES
GROUP BY SCHEMA_NAME
ORDER BY "MB Used" DESC

--	列出某个SCHEMA中所有的表使用的内存大小
SELECT 
	TABLE_NAME AS "Table",
	ROUND(MEMORY_SIZE_IN_TOTAL/1024/1024,2) AS "MB Used"
FROM M_CS_TABLES
WHERE SCHEMA_NAME='<Schema 名称>'
ORDER BY "MB Used" DESC

--	显示某个数据库表未压缩前的大小，以及在Main区域，Delta区域中数据所占用的内存大小
SELECT 
	COLUMN_NAME AS "Column", LOADED,
	ROUND(UNCOMPRESSED_SIZE/1024/1024) AS "Uncompressed MB",
	ROUND(MEMORY_SIZE_IN_MAIN/1024/1024) AS "Main MB",
	ROUND(MEMORY_SIZE_IN_DELTA/1024/1024) AS "Delta MB",
	ROUND(MEMORY_SIZE_IN_TOTAL/1024/1024) AS "Total Used MB",
	ROUND(COMPRESSION_RATIO_IN_PERCENTAGE/100,2) AS "Compr ratio"
FROM M_CS_Columns
WHERE TABLE_NAME = '<数据库表名>'

--	得到某个数据库表在内存中的整体压缩比
SELECT 
	ROUND(SUM(MEMORY_SIZE_IN_MAIN)/1024/1024,2) AS "Memory Size in MAIN_MB",
	ROUND(SUM(MEMORY_SIZE_IN_DELTA)/1024/1024,2) AS "Memory Size in Delta-MB",
	ROUND(SUM(MEMORY_SIZE_IN_TOTAL)/1024/1024,2) AS "Total Size in Momory-MB",
	ROUND(SUM(UNCOMPRESSED_SIZE)/1024/1024,2) AS "Uncompressed Total Size in Memory-MB",
	ROUND(ROUND(SUM(UNCOMPRESSED_SIZE)/1024/1024,2)/ROUND(SUM(MEMORY_SIZE_IN_TOTAL)/1024/1024,2),2) AS "Compressed Rate"
FROM M_CS_COLUMNS
WHERE SCHEMA_NAME='<Schema 名称>' AND TABLE_NAME='<数据库表名称>'

--	显示当前HANA系统中所有行存储表使用了多少内存空间
SELECT 
	ROUND(SUM(USED_FIXED_PART_SIZE + USED_VARIABLE_PART_SIZE)/1024/1024) AS "Row Tables MB Used"
FROM M_RS_TABLES

--	显示所有Schema中每个行存储表单独使用的内存空间
SELECT 
	SCHEMA_NAME,TABLE_NAME,
	ROUND((USED_FIXED_PART_SIZE + USED_VARIABLE_PART_SIZE)/1024/1024,2) AS "MB Used"
FROM M_RS_TABLES
WHERE SCHEMA_NAME='SYS'
ORDER BY "MB Used" DESC,TABLE_NAME

--	读取当前HANA系统对内存分配限额的设定
SELECT 
	HOST,
	ROUND(ALLOCATION_LIMIT/1024/1024) AS "限制分配多少MB内存"
FROM "PUBLIC"."M_HOST_RESOURCE_UTILIZATION"

--	获得当前HANA系统的常驻内存和物理内存
SELECT 
	ROUND((USED_PHYSICAL_MEMORY + FREE_PHYSICAL_MEMORY)/1024/1024/1024,2) AS "Physical Memory GB",
	ROUND(USED_PHYSICAL_MEMORY/1024/1024/1024,2) AS "Resident GB"
FROM PUBLIC.M_HOST_RESOURCE_UTILIZATION


--	计算两个日期之间的天数
SELECT DAYS_BETWEEN(PURCHASE_DATE,DUE_DATE) AS DAYS FROM M_DEFAULT_HB1_WMP_LIST

--	计算某一列数据的平均值
SELECT AVG(ZQ) FROM M_SALEDIBAN