Backup of non-data items, e.g. users, settings, DDL

Here is script to export DDL, resource pool, users/rolse and grants:

--数据库对象
SELECT export_catalog('','design');;

--资源池
SELECT 'CREATE RESOURCE POOL ' || name
|| CASE WHEN memorysize IS NULL THEN ' ' ELSE ' MEMORYSIZE ' || '''' || memorysize || '''' END
|| CASE WHEN maxmemorysize = '' THEN ' ' ELSE ' MAXMEMORYSIZE ' || '''' || maxmemorysize || '''' END
|| CASE WHEN executionparallelism = 'AUTO' THEN ' ' ELSE ' EXECUTIONPARALLELISM ' || '''' || executionparallelism || '''' END
|| CASE WHEN NULLIFZERO(priority) IS NULL THEN ' ' ELSE ' PRIORITY ' || '''' || priority || '''' END
|| CASE WHEN runtimepriority IS NULL THEN ' ' ELSE ' RUNTIMEPRIORITY ' || runtimepriority END
|| CASE WHEN runtimeprioritythreshold IS NULL THEN ' ' ELSE ' RUNTIMEPRIORITYTHRESHOLD ' || runtimeprioritythreshold END
|| CASE WHEN queuetimeout IS NULL THEN ' ' ELSE ' QUEUETIMEOUT ' || queuetimeout END
|| CASE WHEN maxconcurrency IS NULL THEN ' ' ELSE ' MAXCONCURRENCY ' || maxconcurrency END
|| CASE WHEN plannedconcurrency IS NULL THEN ' ' ELSE ' plannedconcurrency ' || plannedconcurrency END
|| CASE WHEN runtimecap IS NULL THEN ' ' ELSE ' RUNTIMECAP ' || '''' || runtimecap || '''' END
|| CASE WHEN cascadeto IS NULL THEN ' ' ELSE ' CASCADETO ' || '''' || cascadeto || '''' END
|| ' ; '
FROM v_catalog.resource_pools
WHERE NOT is_internal
ORDER BY name;

--角色
SELECT 'CREATE ROLE ' || name || ' ;' AS TXT_CR
FROM v_catalog.roles
WHERE name NOT IN ('public','dbadmin','pseudosuperuser','dbduser')
ORDER BY 1;

--TODO: 角色到角色授权

--用户
SELECT 'CREATE USER ' || user_name || ' RESOURCE POOL ' || resource_pool || ' ;'
FROM v_catalog.users
WHERE user_name NOT IN ('dbadmin')
ORDER BY 1;

--角色到用户授权
SELECT 'GRANT ' || all_roles || ' TO ' || user_name || ';'
FROM v_catalog.users
WHERE user_name NOT IN ('dbadmin')
ORDER BY 1;

--用户缺省角色
SELECT 'ALTER USER ' || user_name || ' DEFAULT ROLE ' || default_roles || ' ;'
FROM v_catalog.users
WHERE user_name NOT IN ('dbadmin')
and default_roles <> '' and not default_roles is null
ORDER BY 1;

--对象授权
select 'GRANT '|| privileges_description
||case
when object_type = 'ROLE' then ' ON ' || object_name || ' to '|| grantee||';'
when object_type = 'SCHEMA' then ' ON SCHEMA ' || object_name || ' to '|| grantee||';'
when object_type = 'RESOURCEPOOL' then ' ON RESOURCE POOL ' || object_name || ' to '|| grantee||';'
when object_type = 'TABLE' then ' ON TABLE ' ||object_schema||'.'|| object_name || ' to '|| grantee||';'
end
from grants
where grantor<>grantee
and object_type != 'PROCEDURE'
order by object_type,object_name;

-- TODO: paramters