本文共 3238 字，大约阅读时间需要 10 分钟。

---备份资源池

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 runtimecap                IS NULL THEN ' ' ELSE ' RUNTIMECAP '                 || '''' || runtimecap               || '''' END        || ' ; 'FROM v_catalog.resource_poolsWHERE NOT is_internalORDER BY name;

---备份角色

SELECT '-- Create Roles';SELECT 'CREATE ROLE ' || name || ' ;' AS TXT_CRFROM v_catalog.rolesWHERE name NOT IN ('public','dbadmin','pseudosuperuser','dbduser')ORDER BY 1;

SELECT '-- Add users to roles';SELECT 'GRANT ' || all_roles || ' TO ' || user_name || ';'FROM v_catalog.usersWHERE user_name NOT IN ('dbadmin')ORDER BY 1;

--备份schema

SELECT '-- Create Schema';SELECT 'CREATE SCHEMA ' || schema_name  ||  ';'FROM schemataWHERE schema_name NOT IN ('v_internal','v_catalog','v_monitor','TxtIndex')ORDER BY 1;

--备份用户

SELECT '-- Create Users';SELECT 'CREATE USER ' || user_name  || ' RESOURCE POOL ' || resource_pool ||  ' ;'FROM v_catalog.usersWHERE user_name NOT IN ('dbadmin')ORDER BY 1;

---各手shcema大小

SELECT /*+(estimated_raw_size)*/       pj.anchor_table_schema,       pj.used_compressed_gb,       pj.used_compressed_gb * la.ratio AS raw_estimate_gbFROM   (SELECT ps.anchor_table_schema,               SUM(used_bytes) / ( 1024^3 ) AS used_compressed_gb        FROM   v_catalog.projections p               JOIN v_monitor.projection_storage ps                 ON ps.projection_id = p.projection_id        WHERE  p.is_super_projection = 't'        GROUP  BY ps.anchor_table_schema) pj       CROSS JOIN (SELECT (SELECT database_size_bytes                           FROM   v_catalog.license_audits                           ORDER  BY audit_start_timestamp DESC                           LIMIT  1) / (SELECT SUM(used_bytes)                                        FROM   V_MONITOR.projection_storage) AS ratio) laORDER  BY pj.used_compressed_gb DESC;

--备份赋权语句

--backup grants

select 'grant '|| privileges_description || ' on '|| object_name || ' to '|| grantee||';'  from grants where grantor<>grantee  order by object_name;

备份建表语句以及schema语句

SELECT EXPORT_CATALOG('','DESIGN_ALL')"

转载地址：http://qnucf.baihongyu.com/