Finding index and primary keys of tables<\/b><\/p>\n
SELECT IFNULL(CONSTRAINT,’NUNIQUE’),
\nINDEX_NAME,COLUMN_NAME
\nFROM INDEX_COLUMNS WHERE SCHEMA_NAME = ‘%s’
\nAND TABLE_NAME = ‘%s’
\nORDER BY INDEX_OID,POSITION<\/p>\n
Show details of users that have been logged-in<\/b><\/p>\n
select * from “SYS”.”USERS” where “LAST_SUCCESSFUL_CONNECT” is not null order by 9 desc;<\/p>\n
To get all Libraries<\/b><\/p>\n
select to_char(definition) from public.libraries where schema_name=’EXT’;<\/p>\n
Check invalid custom DB views<\/b><\/p>\n
select * from “SYS”.”VIEWS” where schema_name not like ‘SAP%’ and is_valid = ‘FALSE’;<\/p>\n
Check inactive custom DB objects<\/b><\/p>\n
select * from “_SYS_REPO”.”INACTIVE_OBJECT” where “PACKAGE_ID” not like ‘sap%’;<\/p>\n
Check which SAP language settings are being used by current user<\/b><\/p>\n
select session_context(‘LOCALE_SAP’), session_context(‘LOCALE’) from dummy;<\/p>\n
To find out who deleted a table<\/b><\/p>\n
select * from “SYS”.”M_EXECUTED_STATEMENTS” where “STATEMENT_STRING” LIKE ‘DROP TABLE%’;<\/p>\n
Show custom settings within global.ini and indexserver.ini<\/b><\/p>\n
select * from “SYS”.”M_INIFILE_CONTENTS” where (“LAYER_NAME” = ‘SYSTEM’ or “HOST” <> “)
\nand (“FILE_NAME” = ‘global.ini’ or “FILE_NAME” = ‘indexserver.ini’);<\/p>\n
Show assigned user roles<\/b><\/p>\n
select * from “SYS”.”GRANTED_ROLES” where “GRANTEE_TYPE” = ‘USER’;<\/p>\n
Show assigned repository privileges<\/b><\/p>\n
select * from “SYS”.”GRANTED_PRIVILEGES” where object_type = ‘REPO’;<\/p>\n
Show objects owned by non-system users<\/b><\/p>\n
select * from “SYS”.”OWNERSHIP” where owner_name not like ‘SAP%’ and owner_name not like ‘%SYS%’
\norder by 1,2;<\/p>\n
Show Corresponding Tables referring to which Schema<\/b><\/p>\n
SELECT BASE_SCHEMA_NAME, BASE_OBJECT_NAME,DEPENDENT_SCHEMA_NAME,
\nDEPENDENT_OBJECT_NAME,DEPENDENT_OBJECT_TYPE
\nFROM “SYS”.”OBJECT_DEPENDENCIES”
\nWHERE BASE_SCHEMA_NAME = ‘TCMP’ \/* <== Schema Name *\/\nAND BASE_OBJECT_NAME = 'CS_SALESORDER' \/* <== Table Name *\/\n\nAnalyze expensive statement trace<\/b><\/p>\n
select
\nto_varchar(“STATEMENT_START_TIME”,’DD.MM.YYYY’) “EXEC_DATE”,
\nto_varchar(“STATEMENT_START_TIME”,’HH24:MI:SS’) “EXEC_TIME”,
\nto_int(“DURATION_MICROSEC”\/1000000) “DURATION_S”,
\nto_decimal(“MEMORY_SIZE”\/1073741824,10,1) “MEM_GB”,
\n“RECORDS”,
\n“DB_USER”,
\n“APP_USER”,
\n“APPLICATION_NAME”,
\n“STATEMENT_STRING”,
\nlength(“STATEMENT_STRING”) “SQL_LENGTH”,
\nOCCURRENCES_REGEXPR(‘JOIN’ FLAG ‘i’ IN “STATEMENT_STRING”) “JOIN”,
\nOCCURRENCES_REGEXPR(‘CASE’ FLAG ‘i’ IN “STATEMENT_STRING”) “DISTINCT”,
\n“ERROR_TEXT”,
\n“PARAMETERS”
\nfrom “SYS”.”M_EXPENSIVE_STATEMENTS”
\nwhere “OPERATION” in
\n(‘INSERT’,’SELECT’,’AGGREGATED_EXECUTION’) \u2013exclude background activity
\nand “RECORDS” > 0
\nand to_varchar(“STATEMENT_START_TIME”, ‘YYYYMMDD’) = current_date
\nand to_int(to_varchar(“STATEMENT_START_TIME”,’HH24\u2032))
\nbetween 8 and 17 \u2013business hours
\norder by 3 desc;<\/p>\n