/* This script creates DDL statements for an entire database. The DDL are presented as a single-column result-set and are ready for copy/paste into a text editor or SQL-editor for saving. Originally mentioned in article https://exasol.my.site.com/s/article/Create-DDL-for-the-entire-Database?language=en_US */ CREATE SCHEMA IF NOT EXISTS exa_toolbox; OPEN SCHEMA exa_toolbox; --/ CREATE OR REPLACE LUA SCRIPT exa_toolbox.CREATE_DB_DDL (add_user_structure,add_rights,store_in_table) RETURNS TABLE AS /* /* PARAMETERS: - add_user_structure: boolean If true then DDL for adding roles and users is added (at the top, before everything else). - add_rights: boolean If true then DDL for user & role privileges is added (at the bottom, after everything else). - store_in_table: boolean If true, the entire output is stored in the table "DB_HISTORY"."DATABASE_DDL" before the output is diplayed. ISSUE [PRJ-1156]: Lua script - generates DDL for all objects in a database: - all schemas - all tables, constraints, distribution keys. - all views (paying respect to dependencies) - all scripts - all functions - all connections - all users, roles, rights - all virtual schemas PREREQUISITES: - user executing the script needs "SELECT ANY DICTIONARY" privilege LIMITATIONS: - views and functions which are created in a renamed schema will cause an error on DDL execution - GRANTOR of all privileges will be the user that runs the SQL code returned by the script - passwords of all users will be 'Start123', except for connections where passwords will be left empty - functions with dependencies will not be created in the appropriate order - UDF scripts with delimited identifiers (in- or output) will cause an error on DDL execution - UDF scripts using languages other than the defaults will fail if bucketfs is not set up beforehand - Virtual Schemas will not be created if the required drivers are not installed beforehand TODO: - omit privs for invalid views? CHANGE LOG: 2018-06-15 - Script now creates DDL for users who are authenticated using LDAP (using force). - Added OPEN SCHEMA commands before Script DDL - Allowed option to write the data into a table (only one-line supported). To change the table location, please change lines 670 and 673 2018-08-30 - return in row is simply not possible, the output is longer than 2.000.000 - the same applies to writing in a single table column - return is splitted in parts the same way the table is written - ddl.sql file remains same (export with group_concat) - fixed creation errors with added 'or replace' to get a newer version installed 2018-12-28 - Enabled versioning for 6.1 (still compatible for version 6) - Fixed Kerberos authentication (no lines need to be uncommented) - check that calling user has "SELECT ANY DICTIONARY" privilege - 6.1 FEATURE: Added support for Custom Priority groups - 6.1 FEATURE: Added support for impersonation - 6.1 FEATURE: Added support for partition keys - 6.1 FEATURE: Added support for schema quotas - 6.1 FEATURE: Added support for password policies on system-wide or user-based - Adds ALTER SYSTEM/SESSION commands to set the parameters as they were on the old system - Virtual schemas are created - Script execution now works in DBVisualizer - Removed return_in_one_row parameter - Removed nulls from the output - Invalid views WILL be created 2019-01-30 - Added new LUA script exa_toolbox."RESTORE_SYS" - Replace LUA script exa_toolbox."BACKUP_SYS" - Format. Removed blank lines. Adjust Tab and spaces 2019-03-22 - Added handling of DDL that was over 2 million characters - Fixed bug regarding empty connection strings in connection objects 2021 - Added consumer groups (v7.0) 2021-10-07 - Changes in EXA_Parameters, new values to consider - Fix compile error against 7.1 - Added new options IDLE_TIMEOUT and QUERY_TIMEOUT to consumer groups 2021-12-10 - Changed add_script according to community request (handle duplicate names) 2022-01-07 - Added snapshot execution (for 6.2 and 7.0 users) 2022-08-30 - Improved delimitation of identifiers - Added support for OpenID users - Simplified some queries and replaced some loops with Lua's 1 - Accounted the split of EXA_DBA_VIRTUAL_SCHEMA.ADAPTER_SCRIPT column in 8.0 - Fixed schemas order - Changed backup format from .csv to .csv.gz 2022-11-27 - Added group_concat for obj-grants (reduces file size and exec time on restore) 2023-06-26 - Grants on invalid views are now included by default. Executing GRANT on an invalid view requires system privilege "GRANT ANY OBJECT PRIVILEGE". If users want to exclude grants on invalid views they need to adapt the part of script responsible for object privileges. */ -- sqlstring concatination function check_version() version_suc, version = pquery([[/*snapshot execution*/SELECT SUBSTR(PARAM_VALUE, 0, 3) VERSION_NUMBER, PARAM_VALUE version_full FROM EXA_METADATA WHERE PARAM_NAME = 'databaseProductVersion']]) if not (version_suc) then error('error determining version') else version_short = version[1].VERSION_NUMBER version_full = version[1].VERSION_FULL end end function sqlstr_add(str) sqlstr = sqlstr..str end -- set an empty sqlstring function sqlstr_flush() sqlstr = '' end -- set an empty ddl function ddl_flush() ddl = '' end -- adds sqlstring to dll or adds sqlstring to summary table function sqlstr_commit() ddllength=string.len(ddl)+string.len(sqlstr) -- Check if the length of the total string is greater than 2,000,000, insert into the table first to avoid string being too long if ddllength > 2000000 then write_table('SPLIT', ddl) ddl_flush() end ddl = ddl..sqlstr sqlstr_flush() end -- add linefeed to sqlstring function sqlstr_lf() sqlstr_add('\n') end function ddl_endings() sqlstr_flush() sqlstr_lf() sqlstr_add('COMMIT;') sqlstr_commit() end function add_all_connections() -- ADD ALL CONNECTIONS ------------------------------------------------------------------------------------- ac1_success,ac1_res = pquery([[/*snapshot execution*/select CONNECTION_NAME, nvl(CONNECTION_STRING, ' ') as CONNECTION_STRING, USER_NAME, CREATED, CONNECTION_COMMENT from EXA_DBA_CONNECTIONS]]) if not ac1_success then error('Error at ac1') end sqlstr_add('-- CONNECTIONS --------------------------------------------------------------------\n') sqlstr_lf() -- if (#ac1_res) == 0 then -- sqlstr_add('\t--no connections specified\n') -- end for i=1,(#ac1_res) do sqlstr_add('\tCREATE CONNECTION "'..ac1_res[i].CONNECTION_NAME..'" \n\t\tTO \''..ac1_res[i].CONNECTION_STRING..'\'') if (ac1_res[i].USER_NAME ~= NULL) then sqlstr_add('\n\t\tUSER \''..ac1_res[i].USER_NAME..'\' \n\t\t IDENTIFIED BY \'\';\n\n') else sqlstr_add(';\n\n') end sqlstr_commit() end sqlstr_lf() sqlstr_commit() end function add_all_roles() -- ADD ALL ROLES ----------------------------------------------------------------------------------------- ar1_success, ar1_res = pquery([[/*snapshot execution*/SELECT * FROM EXA_DBA_ROLES]]) if not ar1_success then error('Error at ar1') end if (#ar1_res) > 2 then -- if more than system roles 'public' and 'dba' -- sqlstr_flush() sqlstr_add('-- ROLES --------------------------------------------------------------------\n') sqlstr_commit() sqlstr_lf() for i=1, (#ar1_res) do if (ar1_res[i].ROLE_NAME~='PUBLIC' and ar1_res[i].ROLE_NAME~='DBA') then sqlstr_add('\tCREATE ROLE "'..ar1_res[i].ROLE_NAME..'";\n') if ar1_res[i].ROLE_COMMENT ~= null then sqlstr_commit() sqlstr_add('\t\tCOMMENT ON ROLE "'..ar1_res[i].ROLE_NAME..'" IS \''..ar1_res[i].ROLE_COMMENT..'\';\n') end end sqlstr_commit() end sqlstr_add('\n') else sqlstr_add('-- ROLES --------------------------------------------------------------------\n') sqlstr_lf() sqlstr_add('\t-- only system roles defined.\n') sqlstr_lf() sqlstr_commit() end end function add_all_users() -- ADD ALL USERS ----------------------------------------------------------------------------------------- aau1_success, aau1_res = pquery([[/*snapshot execution*/SELECT * FROM EXA_DBA_USERS]]) if not aau1_success then error('Error at aau1') end -- sqlstr_flush() sqlstr_add('-- USERS --------------------------------------------------------------------\n') sqlstr_commit() sqlstr_lf() if (#aau1_res) > 1 then -- if more than only user 'sys' for i=1,(#aau1_res) do if aau1_res[i].USER_NAME ~= 'SYS' then sqlstr_add('\tCREATE USER "'..aau1_res[i].USER_NAME..'\"') if aau1_res[i].DISTINGUISHED_NAME~=null then -- if LDAP info given, create username with ldap, otherwise use password ldap_string = (string.gsub(aau1_res[i].DISTINGUISHED_NAME, "'", "''")) sqlstr_add(' IDENTIFIED AT LDAP AS \''..ldap_string..'\' FORCE;\n') elseif (aau1_res[i].KERBEROS_PRINCIPAL)~=null then -- if Kerberos info given, include Kerberos information sqlstr_add(' IDENTIFIED BY KERBEROS PRINCIPAL \''..aau1_res[i].KERBEROS_PRINCIPAL..'\';\n') elseif (version_short >= ('7.1')) then if (aau1_res[i].OPENID_SUBJECT)~=null then -- if OpenID info given, include OpenID information sqlstr_add(' IDENTIFIED BY OPENID SUBJECT \''..aau1_res[i].OPENID_SUBJECT..'\';\n') else sqlstr_add(' IDENTIFIED BY "Start123";\n') end else sqlstr_add(' IDENTIFIED BY "Start123";\n') end if aau1_res[i].USER_COMMENT ~= NULL then sqlstr_commit() sqlstr_add('\t\tCOMMENT ON USER "'..aau1_res[i].USER_NAME..'"'.." IS '"..aau1_res[i].USER_COMMENT.."';\n") end -- change V7 if (version_short >= ('7.0')) then sqlstr_commit() if aau1_res[i].USER_CONSUMER_GROUP~=null then sqlstr_add('\t\tALTER USER "'..aau1_res[i].USER_NAME..'" SET CONSUMER_GROUP = "'..aau1_res[i].USER_CONSUMER_GROUP..'";\n') end else if aau1_res[i].USER_PRIORITY~=null then sqlstr_commit() if (version_short ~= ('6.0')) and (version_short ~= ('5.0')) then sqlstr_add('\t\tGRANT PRIORITY GROUP '..aau1_res[i].USER_PRIORITY..' TO "'..aau1_res[i].USER_NAME..'";\n') else sqlstr_add('\t\tGRANT PRIORITY '..aau1_res[i].USER_PRIORITY..' TO "'..aau1_res[i].USER_NAME..'";\n') end end end if (version_short ~= ('6.0')) and (version_short ~= ('5.0')) then if aau1_res[i].PASSWORD_EXPIRY_POLICY ~= null then sqlstr_commit() sqlstr_add('\tALTER USER "'..aau1_res[i].USER_NAME..'" SET PASSWORD_EXPIRY_POLICY=\''.. aau1_res[i].PASSWORD_EXPIRY_POLICY..'\' ;\n') end end end sqlstr_commit() end sqlstr_add('\n') else sqlstr_add('\t-- only system users defined.\n') sqlstr_lf() sqlstr_commit() end end function add_all_rights() -- ADD ALL RIGHTS ----------------------------------------------------------------------------------------- -- role privileges art1_success, art1_res = pquery([[/*snapshot execution*/SELECT * FROM EXA_DBA_ROLE_PRIVS WHERE NOT (GRANTEE='SYS' AND GRANTED_ROLE='DBA')]]) if not art1_success then error('Error in art1') end sqlstr_flush() sqlstr_add('-- RIGHTS --------------------------------------------------------------------\n') sqlstr_lf() sqlstr_commit() sqlstr_add('\t--Please note that the grantor & owner of all grants will be the user who runs the script!\n') sqlstr_lf() sqlstr_commit() if (#art1_res) >0 then for i=1,(#art1_res) do sqlstr_add('\tGRANT "'..art1_res[i].GRANTED_ROLE..'" TO "'..art1_res[i].GRANTEE..'"') if art1_res[i].ADMIN_OPTION then sqlstr_add(' WITH ADMIN OPTION') end sqlstr_add(';\n') sqlstr_commit() end else sqlstr_add('\t-- No user is granted any role except for standard.\n') sqlstr_lf() sqlstr_commit() end -- system privileges art12_success, art12_res = pquery([[/*snapshot execution*/SELECT * FROM EXA_DBA_SYS_PRIVS WHERE NOT GRANTEE in ('SYS', 'DBA')]]) if not art12_success then error('Error in art12') elseif (#art12_res)>0 then sqlstr_flush() for i=1,(#art12_res) do sqlstr_add('\tGRANT '..art12_res[i].PRIVILEGE..' TO "'..art12_res[i].GRANTEE..'";\n') sqlstr_add('\n') sqlstr_commit() end elseif (#art12_res)==0 then sqlstr_flush() sqlstr_add('\t-- No system privileges granted to users other than SYS or DBA. \n') sqlstr_lf() sqlstr_commit() end -- object privileges -- Both UNION ALL branches are preserved to keep a simple possibility to define special behavior for grants on invalid views art2_success, art2_res = pquery([[/*snapshot execution*/SELECT 'GRANT '||GROUP_CONCAT(PRIVILEGE)||' ON "'||case when OBJECT_SCHEMA is not null then OBJECT_SCHEMA||'"."'||OBJECT_NAME||'"' else OBJECT_NAME||'"' end || ' TO "'||GRANTEE||'"' grant_text FROM (select * from EXA_DBA_OBJ_PRIVS where object_type = 'VIEW') op /*join (select distinct COLUMN_SCHEMA, COLUMN_TABLE from exa_dba_columns where status is null) cols on cols.COLUMN_TABLE = op.OBJECT_NAME and cols.COLUMN_SCHEMA = op.OBJECT_SCHEMA*/ group by OBJECT_SCHEMA,OBJECT_NAME,GRANTEE union all SELECT 'GRANT '||GROUP_CONCAT(PRIVILEGE)||' ON "'||case when OBJECT_SCHEMA is not null then OBJECT_SCHEMA||'"."'||OBJECT_NAME||'"' else OBJECT_NAME||'"' end || ' TO "'||GRANTEE||'"' grant_text FROM EXA_DBA_OBJ_PRIVS where object_type <> 'VIEW' group by OBJECT_SCHEMA,OBJECT_NAME,GRANTEE]]) if not art2_success then error('Error in art2') elseif (#art2_res)>0 then sqlstr_flush() for i=1,(#art2_res) do sqlstr_add('\t'..art2_res[i].GRANT_TEXT..';\n') sqlstr_add('\n') sqlstr_commit() end elseif (#art2_res)==0 then sqlstr_flush() sqlstr_add('\t-- No object privileges granted to users other than SYS or DBA. \n') sqlstr_lf() sqlstr_commit() end -- connection privileges art3_success,art3_res = pquery([[/*snapshot execution*/select 'GRANT CONNECTION "' || granted_connection ||'" to ' || group_concat('"' || grantee || '"' order by grantee) || case ADMIN_OPTION when 'TRUE' then ' WITH ADMIN OPTION;' else ';' end as expr from exa_dba_connection_privs group by granted_connection, admin_option]]) if not art3_success then error('Error in art3.') elseif (#art3_res) == 0 then sqlstr_flush() sqlstr_add('\t-- No connection privileges found. \n') sqlstr_lf() sqlstr_commit() else sqlstr_flush() for i=1, (#art3_res) do sqlstr_add('\t'..art3_res[i].EXPR..'\n') sqlstr_lf() sqlstr_commit() end end -- impersonation privileges (version >= 6.1) if (version_short >= ('6.1')) then art4_success, art4_res = pquery([[/*snapshot execution*/SELECT 'GRANT IMPERSONATION ON "'|| IMPERSONATION_ON || '" TO "' || GRANTEE || '";' EXPR FROM EXA_DBA_IMPERSONATION_PRIVS]]) if not art4_success then error('Error in art4: Creating impersonation privileges') elseif (#art3_res) == 0 then sqlstr_flush() sqlstr_add('\t-- No impersonation privileges found. \n') else sqlstr_flush() for i=1, (#art4_res) do sqlstr_add('\t'..art4_res[i].EXPR..'\n') end sqlstr_lf() sqlstr_commit() end end end function change_schema_owners() co1_success, co1_res = pquery([[/*snapshot execution*/SELECT * from EXA_SCHEMAS]]) if not co1_success then error('Error in co1') elseif (#co1_res)==0 then sqlstr_flush() sqlstr_lf() sqlstr_add('-- CHANGE SCHEMA OWNERS -----------------------------------------------------------------\n') sqlstr_add('\t -- No user schemas in the database') sqlstr_commit() sqlstr_lf() elseif (#co1_res)>0 then sqlstr_flush() sqlstr_lf() sqlstr_add('-- CHANGE SCHEMA OWNERS -----------------------------------------------------------------\n') sqlstr_commit() sqlstr_lf() for i=1, (#co1_res) do if co1_res[i].SCHEMA_IS_VIRTUAL then sqlstr_add('\tALTER VIRTUAL SCHEMA "'..co1_res[i].SCHEMA_NAME..'" CHANGE OWNER "'..co1_res[i].SCHEMA_OWNER..'";\n') sqlstr_commit() else sqlstr_add('\tALTER SCHEMA "'..co1_res[i].SCHEMA_NAME..'" CHANGE OWNER "'..co1_res[i].SCHEMA_OWNER..'";\n') sqlstr_commit() end end sqlstr_add('\n') -- sqlstr_commit() end end function add_all_views_to_DDL() --ADD ALL VIEWS-------------------------------------------------------------------------------------------- av1_success, av1_res=pquery([[/*snapshot execution*/WITH all_views AS( with view_dep AS( SELECT * FROM EXA_DBA_DEPENDENCIES_RECURSIVE WHERE REFERENCE_TYPE = 'VIEW' ) SELECT view_schema, scope_schema, view_name, 'CREATE VIEW "' || REPLACE(VIEW_SCHEMA,'"','""') || '"."' || REPLACE(VIEW_NAME,'"','""') || '"' || "$VIEW_MIGRATION_TEXT"(VIEW_TEXT) VIEW_TEXT FROM ( view_dep AS re RIGHT OUTER JOIN EXA_DBA_VIEWS AS av ON re.OBJECT_ID = av.VIEW_OBJECT_ID INNER JOIN (SELECT distinct COLUMN_TABLE, COLUMN_SCHEMA from exa_dba_columns where column_object_type = 'VIEW' and status is null) cols ON cols.COLUMN_TABLE = av.VIEW_NAME and cols.COLUMN_SCHEMA = av.VIEW_SCHEMA ) UNION ALL SELECT view_schema, scope_schema, view_name, 'CREATE FORCE VIEW "' || REPLACE(VIEW_SCHEMA,'"','""') || '"."' || REPLACE(VIEW_NAME,'"','""') || '"' || "$VIEW_MIGRATION_TEXT"(VIEW_TEXT) VIEW_TEXT FROM ( view_dep AS re RIGHT OUTER JOIN EXA_DBA_VIEWS AS av ON re.OBJECT_ID = av.VIEW_OBJECT_ID INNER JOIN (SELECT distinct COLUMN_TABLE, COLUMN_SCHEMA from exa_dba_columns where column_object_type = 'VIEW' and status is not null) cols ON cols.COLUMN_TABLE = av.VIEW_NAME and cols.COLUMN_SCHEMA = av.VIEW_SCHEMA ) ) SELECT view_schema, scope_schema, view_name, rtrim(view_text, CHR(9) || CHR(10) || CHR(13)) || CHR(13) || CHR(10) || ';'as view_text, count(view_name) as view_count FROM all_views GROUP BY view_schema, scope_schema, view_name, view_text ORDER BY view_count, view_schema, view_name; ]]) if not av1_success then error('Error at av1') elseif (#av1_res) == 0 then sqlstr_flush() sqlstr_add('-- ALL VIEWS ---------------------------------------------------------------------------------\n') sqlstr_add('\t -- No views in the database') sqlstr_commit() sqlstr_lf() elseif #av1_res > 0 then sqlstr_flush() sqlstr_add('-- ALL VIEWS ---------------------------------------------------------------------------------\n') sqlstr_commit() sqlstr_lf() if av1_res[1].SCOPE_SCHEMA==NULL then sqlstr_add('CLOSE SCHEMA;\n')-- close schema else sqlstr_add('\nOPEN SCHEMA "'..av1_res[1].SCOPE_SCHEMA..'";\n') end sqlstr_add('\t'..av1_res[1].VIEW_TEXT..'\n\n') sqlstr_commit() for j=2, (#av1_res) do if (av1_res[j].SCOPE_SCHEMA~=av1_res[j-1].SCOPE_SCHEMA)then if av1_res[j].SCOPE_SCHEMA==NULL then sqlstr_add('CLOSE SCHEMA;\n')-- close schema else sqlstr_add('\nOPEN SCHEMA "'..av1_res[j].SCOPE_SCHEMA..'";\n') end end sqlstr_add('\t'..av1_res[j].VIEW_TEXT..'\n\n') sqlstr_commit() end -- for -- sqlstr_add('\n') -- sqlstr_lf() -- sqlstr_commit() end -- else end function add_table_to_DDL(schema_name, tbl_name, tbl_comment) --ADD TABLE------------------------------------------------------------------------------------------- sqlstr_flush() at1_success, at1_res = pquery([[/*snapshot execution*/SELECT * FROM EXA_DBA_COLUMNS WHERE COLUMN_SCHEMA=:s AND COLUMN_TABLE=:t ORDER BY COLUMN_ORDINAL_POSITION]], {s=schema_name, t=tbl_name}) if not at1_success then error('Error at at1 -- probably table not found') else sqlstr_add([[CREATE TABLE "]]..at1_res[1].COLUMN_SCHEMA..[["."]]..at1_res[1].COLUMN_TABLE..[["(]]) -- CREATE schema_name.table_name ( distr={} part={} for i=1, (#at1_res) do if i>1 then sqlstr_add(',') end sqlstr_add('\n\t\t"'..at1_res[i].COLUMN_NAME..'" '..at1_res[i].COLUMN_TYPE) -- (beginn of column definition) column_name, column_datatype if at1_res[i].COLUMN_DEFAULT~=null then sqlstr_add(' DEFAULT '..at1_res[i].COLUMN_DEFAULT) -- default end if at1_res[i].COLUMN_IDENTITY~=null then sqlstr_add(' IDENTITY') -- identity end if not at1_res[i].COLUMN_IS_NULLABLE then -- not null sqlstr_add(' NOT NULL') end if at1_res[i].COLUMN_COMMENT~=null then sqlstr_add([[ COMMENT IS ']]..string.gsub(at1_res[i].COLUMN_COMMENT, [[']], [['']])..[[']]) -- column comment end if at1_res[i].COLUMN_IS_DISTRIBUTION_KEY == true then table.insert(distr, '"'..at1_res[i].COLUMN_NAME..'"') end end --for if #distr > 0 then sqlstr_add(',\n\t\tDISTRIBUTE BY '..table.concat(distr, ', ')) end if (version_short ~= ('6.0')) and (version_short ~= ('5.0')) then --partition by at2_success, at2_res = pquery([[/*snapshot execution*/SELECT * FROM EXA_DBA_COLUMNS WHERE COLUMN_SCHEMA=:s AND COLUMN_TABLE=:t AND COLUMN_PARTITION_KEY_ORDINAL_POSITION IS NOT NULL ORDER BY COLUMN_PARTITION_KEY_ORDINAL_POSITION]], {s=schema_name, t=tbl_name}) if not at2_success then error('Error at at2 -- probably table not found') elseif #at2_res > 0 then for p=1,#at2_res do table.insert(part, '"'..at2_res[p].COLUMN_NAME..'"') end end if #part > 0 then sqlstr_add(', \n\t\tPARTITION BY '..table.concat(part, ', ')) end end sqlstr_add(' )') if tbl_comment ~= null then -- table comment sqlstr_add('\n\tCOMMENT IS \''..tbl_comment..'\'') end sqlstr_add(';\n') sqlstr_lf() sqlstr_commit() end end function add_schemas_constraint_to_DDL(schema_name) --ADD THE SCHEMA'S CONSTRAINTS-------------------------------------------------------------------------------------------- sqlstr_flush() -- ac1_success, ac1_res = pquery([[SELECT * FROM EXA_DBA_CONSTRAINT_COLUMNS WHERE CONSTRAINT_SCHEMA=:s AND (CONSTRAINT_TYPE='PRIMARY KEY' OR CONSTRAINT_TYPE='FOREIGN KEY') ORDER BY CONSTRAINT_TYPE desc, COLUMN_NAME]], {s=schema_name}) ac1_success, ac1_res = pquery([[/*snapshot execution*/with sel1 as ( select COL.constraint_schema, COL.constraint_table, COL.constraint_type, COL.constraint_name, COL.column_name, AC.constraint_enabled, COL.REFERENCED_SCHEMA, COL.REFERENCED_TABLE, COL.REFERENCED_COLUMN from EXA_DBA_constraints AC join EXA_DBA_constraint_columns COL on AC.constraint_name=COL.constraint_name and AC.CONSTRAINT_SCHEMA = COL.CONSTRAINT_SCHEMA and AC.CONSTRAINT_TABLE = COL.CONSTRAINT_TABLE) select constraint_schema, constraint_table, constraint_type, constraint_name, constraint_enabled, REFERENCED_TABLE, group_concat(column_name separator '","') column_names, group_concat(REFERENCED_COLUMN separator '","') REFERENCED_COLUMNS from sel1 where constraint_schema=:s AND (REFERENCED_SCHEMA=:s or REFERENCED_SCHEMA is null) AND (CONSTRAINT_TYPE='PRIMARY KEY' OR CONSTRAINT_TYPE='FOREIGN KEY') group by constraint_schema, constraint_table, constraint_type, constraint_name, constraint_enabled, REFERENCED_TABLE, REFERENCED_SCHEMA order by constraint_type desc, constraint_table]], {s=schema_name}) if not ac1_success then error('Error in ac1') else for i=1,(#ac1_res) do if ac1_res[i].CONSTRAINT_TYPE=='PRIMARY KEY' then sqlstr_add('\tALTER TABLE "'..ac1_res[i].CONSTRAINT_SCHEMA..'"."'..ac1_res[i].CONSTRAINT_TABLE..'"\n\t\tADD CONSTRAINT "'..ac1_res[i].CONSTRAINT_NAME..'"\n\t\t '..ac1_res[i].CONSTRAINT_TYPE..' ("'..ac1_res[i].COLUMN_NAMES..'");\n') sqlstr_lf() elseif ac1_res[i].CONSTRAINT_TYPE=='FOREIGN KEY' then sqlstr_add('\tALTER TABLE "'..ac1_res[i].CONSTRAINT_SCHEMA..'"."'..ac1_res[i].CONSTRAINT_TABLE..'"\n\t\tADD CONSTRAINT "'..ac1_res[i].CONSTRAINT_NAME..'"\n\t\t '..ac1_res[i].CONSTRAINT_TYPE..' ("'..ac1_res[i].COLUMN_NAMES..'")\n\t\t\tREFERENCES "'..ac1_res[i].REFERENCED_TABLE..'"("'..ac1_res[i].REFERENCED_COLUMNS..'");\n') sqlstr_lf() end end -- for sqlstr_commit() end -- else end -- function function add_all_constraints_to_DDL(only_cross_schema) --ADD ALL CONSTRAINTS-------------------------------------------------------------------------------------------- sqlstr_flush() -- aac1_success, aac1_res = pquery([[/*snapshot execution*/SELECT * FROM EXA_DBA_CONSTRAINT_COLUMNS WHERE (CONSTRAINT_TYPE='PRIMARY KEY' OR CONSTRAINT_TYPE='FOREIGN KEY') ORDER BY CONSTRAINT_TYPE desc, CONSTRAINT_SCHEMA, COLUMN_NAME]]) aac1_success, aac1_res = pquery([[/*snapshot execution*/with sel1 as ( select COL.constraint_schema, COL.constraint_table, COL.constraint_type, COL.constraint_name, COL.column_name, AC.constraint_enabled, COL.REFERENCED_SCHEMA, COL.REFERENCED_TABLE, COL.REFERENCED_COLUMN from EXA_DBA_constraints AC join EXA_DBA_constraint_columns COL on AC.constraint_name=COL.constraint_name and AC.CONSTRAINT_SCHEMA = COL.CONSTRAINT_SCHEMA and AC.CONSTRAINT_TABLE = COL.CONSTRAINT_TABLE) select constraint_schema, constraint_table, constraint_type, constraint_name, constraint_enabled, REFERENCED_TABLE, REFERENCED_SCHEMA, group_concat(column_name separator '","') column_names, group_concat(REFERENCED_COLUMN separator '","') REFERENCED_COLUMNS from sel1 where (CONSTRAINT_TYPE='PRIMARY KEY' OR CONSTRAINT_TYPE='FOREIGN KEY') and ((constraint_schema <> REFERENCED_SCHEMA and :only_cs) or not :only_cs) group by constraint_schema, constraint_table, constraint_type, constraint_name, constraint_enabled, REFERENCED_TABLE, REFERENCED_SCHEMA order by constraint_type desc,constraint_schema, constraint_table]], {only_cs=only_cross_schema}) if not aac1_success then error('Error in aac1') else sqlstr_add('-- ALL CONSTRAINTS ---------------------------------------------------------------------------------\n') sqlstr_lf() sqlstr_commit() for i=1,(#aac1_res) do if aac1_res[i].CONSTRAINT_TYPE=='PRIMARY KEY' then sqlstr_add('\tALTER TABLE "'..aac1_res[i].CONSTRAINT_SCHEMA..'"."'..aac1_res[i].CONSTRAINT_TABLE..'"\n\t\tADD CONSTRAINT "'..aac1_res[i].CONSTRAINT_NAME..'"\n\t\t '..aac1_res[i].CONSTRAINT_TYPE..' ("'..aac1_res[i].COLUMN_NAMES..'");\n') sqlstr_lf() sqlstr_commit() elseif aac1_res[i].CONSTRAINT_TYPE=='FOREIGN KEY' then sqlstr_add('\tALTER TABLE "'..aac1_res[i].CONSTRAINT_SCHEMA..'"."'..aac1_res[i].CONSTRAINT_TABLE..'"\n\t\tADD CONSTRAINT "'..aac1_res[i].CONSTRAINT_NAME..'"\n\t\t '..aac1_res[i].CONSTRAINT_TYPE..' ("'..aac1_res[i].COLUMN_NAMES..'")\n\t\t\tREFERENCES "'..aac1_res[i].REFERENCED_SCHEMA..'"."'..aac1_res[i].REFERENCED_TABLE..'"("'..aac1_res[i].REFERENCED_COLUMNS..'");\n') sqlstr_lf() sqlstr_commit() end end -- for sqlstr_commit() end -- else end -- function function add_function_to_DDL(function_text) --ADD FUNCTION------------------------------------------------------------------------------------------- sqlstr_flush() sqlstr_add('--/ \n'..function_text..'\n') sqlstr_lf() sqlstr_commit() end function add_script_to_DDL(schema_name, script_name) --ADD SCRIPT------------------------------------------------------------------------------------------- sqlstr_flush() as1_success, as1_res = pquery([[/*snapshot execution*/SELECT SCRIPT_SCHEMA, SCRIPT_TEXT FROM EXA_DBA_SCRIPTS WHERE SCRIPT_SCHEMA=:ss AND SCRIPT_NAME=:sn]], {ss=schema_name, sn=script_name}) if not as1_success then error('Error at as1') end sqlstr_lf() sqlstr_add('-- BEGIN OF SCRIPT: '..schema_name..'.'..script_name..' ======================================================================================================\n') sqlstr_commit() sqlstr_add('\nOPEN SCHEMA \"'..schema_name..'\";') --Open schema to create the script sqlstr_commit() sqlstr_add('\n--/\n'..as1_res[1].SCRIPT_TEXT..'\n/') sqlstr_commit() sqlstr_add('\nCLOSE SCHEMA;') sqlstr_commit() sqlstr_add('\n-- END OF SCRIPT: '..schema_name..'.'..script_name..' ======================================================================================================\n') sqlstr_lf() sqlstr_commit() end function add_schema_to_DDL(schemaname, schema_comment) --ADD SCHEMA------------------------------------------------------------------------------------------- sqlstr_flush() sqlstr_add('--SCHEMA: '..schemaname..' -------------------------------------------------------------------------------------------\n') sqlstr_lf() sqlstr_commit() sqlstr_add([[CREATE SCHEMA "]]..schemaname..'\";\n') if schema_comment ~= null then sqlstr_add('COMMENT ON SCHEMA \"'..schemaname..'\" IS \''..schema_comment..'\';\n') end if (version_short ~= ('6.0')) and (version_short ~= ('5.0')) then -- Add schema size limit ads1_suc, ads1_res = pquery([[/*snapshot execution*/SELECT * FROM EXA_DBA_OBJECT_SIZES WHERE OBJECT_TYPE = 'SCHEMA' AND OBJECT_NAME = :s]],{s=schemaname}) if not (ads1_suc) then error('Error checking schema size limit') else if ads1_res[1].RAW_OBJECT_SIZE_LIMIT ~= null then sqlstr_add('\n\tALTER SCHEMA "'..ads1_res[1].OBJECT_NAME..'" SET RAW_SIZE_LIMIT='..ads1_res[1].RAW_OBJECT_SIZE_LIMIT..';\n') end end end --if sqlstr_lf() sqlstr_commit() end function write_table(p_type_in, p_txt_in) summary[#summary+1] = {p_txt_in} if store_in_table == true then idx = idx + 1 suc, res = pquery([[INSERT INTO DB_HISTORY.DATABASE_DDL VALUES (:ct, :rn, :type, :txt)]] ,{ct = t[1].CURRENT_TIMESTAMP, rn = idx, type = p_type_in, txt=p_txt_in}) if (suc) then else output(string.len(p_txt_in)) output(string.sub(p_txt_in, 1, 150000)) errtext = "Type is :"..type(summary[1]).."Error in script!!: "..res.error_message error(errtext) end end ddl_flush() end function add_all_priority_groups() -- ADD PRIORITY GROUPS (AFTER VERSION 6.1) aapg1_suc, aapg1_res = pquery([[/*snapshot execution*/select * from exa_priority_groups where PRIORITY_GROUP_NAME NOT IN ('HIGH', 'MEDIUM', 'LOW')]]) if not (aapg1_suc) then error('ERROR CREATING PRIORITY GROUPS') end sqlstr_add('-- PRIORITY GROUPS --------------------------------------------------------------------\n') sqlstr_lf() if (#aapg1_res) == 0 then sqlstr_add('\t--no Priority Groups\n') end for i=1,(#aapg1_res) do sqlstr_add('\tCREATE PRIORITY GROUP \"'..aapg1_res[i].PRIORITY_GROUP_NAME..'\" WITH WEIGHT = '..aapg1_res[i].PRIORITY_GROUP_WEIGHT..';\n\t\t') if (aapg1_res[i].PRIORITY_GROUP_COMMENT ~= NULL) then sqlstr_add('\n\t\tCOMMENT ON PRIORITY GROUP "'..aapg1_res[i].PRIORITY_GROUP_NAME..'" IS \''..aapg1_res[i].PRIORITY_GROUP_COMMENT..'\'; \n\t\t ') else sqlstr_add('\n\n') end sqlstr_commit() end end function add_system_parameters() --ADD SYSTEM PARAMETERS asp1_suc, asp1_res = pquery([[/*snapshot execution*/SELECT * FROM EXA_PARAMETERS WHERE SYSTEM_VALUE IS NOT NULL AND PARAMETER_NAME != 'NICE']]) if not (asp1_suc) then error('Error retrieving system parameters') else sqlstr_add('--SYSTEM PARAMETERS --------------------------------------------------------------------\n') for i=1, #asp1_res do -- FOR NUMBERS if asp1_res[i].PARAMETER_NAME == ('NLS_FIRST_DAY_OF_WEEK') or asp1_res[i].PARAMETER_NAME == ('QUERY_TIMEOUT') or asp1_res[i].PARAMETER_NAME == ('IDLE_TIMEOUT') or asp1_res[i].PARAMETER_NAME == ('ST_MAX_DECIMAL_DIGITS') then sqlstr_add('ALTER SYSTEM SET '..asp1_res[i].PARAMETER_NAME..' = '..asp1_res[i].SYSTEM_VALUE..';\n') -- FOR DOUBLE QUOTES elseif asp1_res[i].PARAMETER_NAME == ('DEFAULT_PRIORITY_GROUP') or asp1_res[i].PARAMETER_NAME == ('DEFAULT_CONSUMER_GROUP') then sqlstr_add('ALTER SYSTEM SET '..asp1_res[i].PARAMETER_NAME..' = "'..asp1_res[i].SYSTEM_VALUE..'";\n') -- FOR SINGLE QUOTES else sqlstr_add('ALTER SYSTEM SET '..asp1_res[i].PARAMETER_NAME..' = \''..asp1_res[i].SYSTEM_VALUE..'\';\n') end end end asp2_suc, asp2_res = pquery([[/*snapshot execution*/SELECT * FROM EXA_PARAMETERS WHERE SESSION_VALUE IS NOT NULL AND PARAMETER_NAME NOT IN ('QUERY_TIMEOUT', 'DEFAULT_PRIORITY_GROUP','DEFAULT_CONSUMER_GROUP','PASSWORD_EXPIRY_POLICY','PASSWORD_SECURITY_POLICY','TEMP_DB_RAM_LIMIT','USER_TEMP_DB_RAM_LIMIT')]]) if not (asp2_suc) then error('Error retrieving session parameters') else sqlstr_add('--SESSION PARAMETERS --------------------------------------------------------------------\n') for i=1, #asp2_res do if asp2_res[i].PARAMETER_NAME == ('NLS_FIRST_DAY_OF_WEEK') or asp2_res[i].PARAMETER_NAME == ('IDLE_TIMEOUT') or asp2_res[i].PARAMETER_NAME == ('ST_MAX_DECIMAL_DIGITS') then sqlstr_add('ALTER SESSION SET '..asp2_res[i].PARAMETER_NAME..' = '..asp2_res[i].SESSION_VALUE..';\n') elseif asp2_res[i].PARAMETER_NAME == 'DEFAULT_PRIORITY_GROUP' then sqlstr_add('ALTER SESSION SET '..asp2_res[i].PARAMETER_NAME..' = "'..asp2_res[i].SESSION_VALUE..'";\n') else sqlstr_add('ALTER SESSION SET '..asp2_res[i].PARAMETER_NAME..' = \''..asp2_res[i].SESSION_VALUE..'\';\n') end end end sqlstr_commit() end function add_all_virtual_schemas() -- ADD ALL VIRTUAL SCHEMAS ----------------------------------------------------------------------------------------- if (version_short >='8.0') then avs1_success, avs1_res = pquery([[/*snapshot execution*/select 'CREATE VIRTUAL SCHEMA "' || s.SCHEMA_NAME || '" USING "' || ADAPTER_SCRIPT_SCHEMA || '"."' || ADAPTER_SCRIPT_NAME || '" WITH ' || GROUP_CONCAT(PROPERTY_NAME || ' = ''' || PROPERTY_VALUE || '''' ORDER BY PROPERTY_NAME SEPARATOR ' ') || '; ' AS TEXT from EXA_DBA_VIRTUAL_SCHEMAS s join EXA_DBA_VIRTUAL_SCHEMA_PROPERTIES p on s.SCHEMA_NAME=p.SCHEMA_NAME group by s.schema_name, ADAPTER_SCRIPT_SCHEMA, ADAPTER_SCRIPT_NAME;]]) else avs1_success, avs1_res = pquery([[/*snapshot execution*/select 'CREATE VIRTUAL SCHEMA "' || s.SCHEMA_NAME || '" USING ' || ADAPTER_SCRIPT || ' WITH ' || GROUP_CONCAT(PROPERTY_NAME || ' = ''' || PROPERTY_VALUE || '''' ORDER BY PROPERTY_NAME SEPARATOR ' ') || '; ' AS TEXT from EXA_DBA_VIRTUAL_SCHEMAS s join EXA_DBA_VIRTUAL_SCHEMA_PROPERTIES p on s.SCHEMA_NAME=p.SCHEMA_NAME group by s.schema_name, adapter_script;]]) end output(#avs1_res) if not avs1_success then error('Error Creating virtual Schemas') end if (#avs1_res) >= 1 then -- if more than system roles 'public' and 'dba' -- sqlstr_flush() sqlstr_add('-- VIRTUAL SCHEMAS --------------------------------------------------------------------\n') sqlstr_commit() sqlstr_lf() for i=1, #avs1_res do sqlstr_add('\t'..avs1_res[i].TEXT..'\n\n') end sqlstr_commit() sqlstr_lf() else sqlstr_add('-- VIRTUAL SCHEMAS --------------------------------------------------------------------\n') sqlstr_lf() sqlstr_add('\t-- no virtual schemas defined.\n') sqlstr_lf() sqlstr_commit() end end function add_all_consumer_groups() -- ADD CONSUMER GROUPS (AFTER VERSION 7.0) aapg1_suc, aapg1_res = pquery([[/*snapshot execution*/select * from exa_consumer_groups where CONSUMER_GROUP_NAME NOT IN ('HIGH', 'MEDIUM', 'LOW', 'SYS_CONSUMER_GROUP')]]) if not (aapg1_suc) then error('ERROR CREATING CONSUMER GROUPS') end sqlstr_add('-- CONSUMER GROUPS --------------------------------------------------------------------\n') sqlstr_lf() if (#aapg1_res) == 0 then sqlstr_add('\t--no Consumer Groups\n') end for i=1,(#aapg1_res) do -- mandatory sql_cons_group = '\tCREATE CONSUMER GROUP \"'..aapg1_res[i].CONSUMER_GROUP_NAME..'\" WITH PRECEDENCE = '..aapg1_res[i].PRECEDENCE..' ,CPU_WEIGHT = '..aapg1_res[i].CPU_WEIGHT -- optional if (aapg1_res[i].GROUP_TEMP_DB_RAM_LIMIT ~= NULL) then sql_cons_group = sql_cons_group..' ,GROUP_TEMP_DB_RAM_LIMIT = '..aapg1_res[i].GROUP_TEMP_DB_RAM_LIMIT end if (aapg1_res[i].USER_TEMP_DB_RAM_LIMIT ~= NULL) then sql_cons_group = sql_cons_group..' ,USER_TEMP_DB_RAM_LIMIT = '..aapg1_res[i].USER_TEMP_DB_RAM_LIMIT end if (aapg1_res[i].SESSION_TEMP_DB_RAM_LIMIT ~= NULL) then sql_cons_group = sql_cons_group..' ,SESSION_TEMP_DB_RAM_LIMIT = '..aapg1_res[i].SESSION_TEMP_DB_RAM_LIMIT end -- new in V7.1 if (version_short >= ('7.1')) then if (aapg1_res[i].IDLE_TIMEOUT ~= NULL) then sql_cons_group = sql_cons_group..' ,IDLE_TIMEOUT = '..aapg1_res[i].IDLE_TIMEOUT end if (aapg1_res[i].QUERY_TIMEOUT ~= NULL) then sql_cons_group = sql_cons_group..' ,QUERY_TIMEOUT = '..aapg1_res[i].QUERY_TIMEOUT end end sql_cons_group = sql_cons_group..'; \n\t\t' sqlstr_add(sql_cons_group) if (aapg1_res[i].CONSUMER_GROUP_COMMENT ~= NULL) then sqlstr_add('\n\t\tCOMMENT ON CONSUMER GROUP "'..aapg1_res[i].CONSUMER_GROUP_NAME..'" IS \''..aapg1_res[i].CONSUMER_GROUP_COMMENT..'\'; \n\t\t ') else sqlstr_add('\n\n') end sqlstr_commit() end end -- MAIN -------------------------------------------------------------------------------------------------------------------------------------------- -- Check if the user has SELECT ANY DICTIONARY privilege: privsuc, privcheck = pquery([[/*snapshot execution*/SELECT * FROM EXA_DBA_USERS LIMIT 1]]) if not (privsuc) then error('The User does not have SELECT ANY DICTIONARY privilege') end check_version() -- Prepare Output Table if requested if store_in_table == true then if version_short ~= '5.0' then cschemsucc,cschemres = pquery([[CREATE SCHEMA IF NOT EXISTS "DB_HISTORY";]]) if (cschemsucc) then ctabsuc, ctabres = pquery ([[CREATE TABLE IF NOT EXISTS "DB_HISTORY"."DATABASE_DDL" (BACKUP_TIME TIMESTAMP, rn decimal(5), type varchar(20), DDL varchar(2000000));]]) if (ctabsuc) then query ([[COMMIT]]) else error('error in creating DDL Table') end else error('error in create DDL schema') end else checkschemsucc,checkschemres = pquery([[/*snapshot execution*/SELECT SCHEMA_NAME FROM EXA_SCHEMAS WHERE SCHEMA_NAME = 'DB_HISTORY']]) if not (checkschemsucc) then error('Error checking for DB_HISTORY schema') else if (#checkschemres) > 0 then cschemsucc,cschemres= pquery([[OPEN SCHEMA "DB_HISTORY"]]) else cschemsucc,cschemres = pquery([[CREATE SCHEMA "DB_HISTORY";]]) end end if (cschemsucc) then ctabchecksuc, ctabcheckres = pquery([[/*snapshot execution*/SELECT TABLE_NAME FROM EXA_DBA_TABLES WHERE TABLE_SCHEMA='DB_HISTORY' AND TABLE_NAME='DATABASE_DDL';]]) if not (ctabchecksuc) then error('Error checking for Database_ddl table') else if (#ctabcheckres) == 0 then ctabsuc, ctabres = pquery ([[CREATE TABLE "DB_HISTORY"."DATABASE_DDL" (BACKUP_TIME TIMESTAMP, rn decimal(5), type varchar(20), DDL varchar(2000000));]]) if (ctabsuc) then query([[COMMIT]]) else error('error in creating DDL Table') end end end else error('error in create DDL schema') end end end t=query([[/*snapshot execution*/SELECT CURRENT_USER,CURRENT_TIMESTAMP]]) constraints_separately = true return_in_one_row = true ddl = '' summary = {{'START'}} sqlstr =[[]] sqlstr_add('\n--DDL created by user '..t[1].CURRENT_USER..' at '..t[1].CURRENT_TIMESTAMP..'\n\n') sqlstr_commit() sqlstr_flush() --Check Versioning and insert into string check_version() sqlstr_add([[--Database Version: ]]..version_full..'\n') if version_short == '5.0' then sqlstr_add([[--WARNING: Version 5 is not supported]].. '\n') end -- ENABLE PROFILING sqlstr_add([[ALTER SESSION SET PROFILE='ON';]]..'\n') sqlstr_add([[SET DEFINE OFF;]]..'\n') sqlstr_commit() idx = 0 write_table('HEADER',ddl) add_system_parameters() write_table('SYSTEM PARAMETERS', ddl) -- roles, users if add_user_structure then if (version_short == ('6.1') or version_short == ('6.2')) then add_all_priority_groups() write_table('PRIORTY GROUPS', ddl) elseif (version_short == ('6.0') or version_short == ('5.0')) then a=1 elseif (version_short >= ('7.0')) then add_all_consumer_groups() write_table('CONSUMER GROUPS',ddl) else a=1 end add_all_roles() write_table('ROLES',ddl) add_all_users() write_table('USERS',ddl) end -- schemas if version_short == '5.0' then m1_success, m1_res = pquery([[select OBJECT_NAME, OBJECT_COMMENT from EXA_DBA_OBJECTS WHERE OBJECT_TYPE = 'SCHEMA' ORDER BY OBJECT_NAME]]) else m1_success, m1_res = pquery([[/*snapshot execution*/select OBJECT_NAME, OBJECT_COMMENT from EXA_DBA_OBJECTS WHERE OBJECT_TYPE = 'SCHEMA' AND OBJECT_IS_VIRTUAL IS FALSE ORDER BY OBJECT_NAME]]) end if not m1_success then error('Error at m1') else for i=1,(#m1_res) do -- iterate through all schemas add_schema_to_DDL(m1_res[i].OBJECT_NAME, m1_res[i].OBJECT_COMMENT) m2_success, m2_res = pquery([[/*snapshot execution*/select table_schema,table_name,table_comment from EXA_DBA_TABLES WHERE TABLE_SCHEMA=:s ORDER BY TABLE_NAME]], {s = m1_res[i].OBJECT_NAME}) if not m2_success then error('Error at m2') else for j=1,(#m2_res) do -- iterate through all tables of the current schema add_table_to_DDL(m2_res[j].TABLE_SCHEMA,m2_res[j].TABLE_NAME, m2_res[j].TABLE_COMMENT) end if not constraints_separately then -- outline constraints within schema block add_schemas_constraint_to_DDL(m1_res[i].OBJECT_NAME) end -- if end -- else (tables) -- add all functions to the schema m21_success, m21_res=pquery([[/*snapshot execution*/SELECT FUNCTION_NAME , 'CREATE ' || rtrim(FUNCTION_TEXT, '/' || CHR(13) || CHR(10)) || CHR(13) || CHR(10) || '/' AS function_text FROM EXA_DBA_FUNCTIONS WHERE FUNCTION_SCHEMA=:s]],{s = m1_res[i].OBJECT_NAME}) if not m21_success then error('Error at m21') else for j=1,(#m21_res) do add_function_to_DDL(m21_res[j].FUNCTION_TEXT) end -- for end --else -- get all script names of the current schema m4_success, m4_res=pquery([[/*snapshot execution*/SELECT SCRIPT_SCHEMA,SCRIPT_NAME FROM EXA_DBA_SCRIPTS WHERE SCRIPT_SCHEMA=:s]],{s = m1_res[i].OBJECT_NAME}) if not m4_success then error('Error at m3') end -- if for j=1,(#m4_res) do -- add scripts of the schema add_script_to_DDL(m4_res[j].SCRIPT_SCHEMA,m4_res[j].SCRIPT_NAME) end -- for (scripts) write_table('SCHEMA', ddl) end --for (schemas) -- add all views add_all_views_to_DDL() write_table('VIEWS',ddl) -- add all connections add_all_connections() write_table('CONNECTIONS',ddl) --add all virtual schemas if version_short ~= '5.0' then add_all_virtual_schemas() write_table('VIRTUAL SCHEMAS', ddl) end -- add constraints - all or only cross-schema, based on constraints_separately variable add_all_constraints_to_DDL(not constraints_separately) write_table('CONSTRAINTS',ddl) if add_user_structure then change_schema_owners() write_table('SCHEMA OWNERS',ddl) end if add_rights then add_all_rights() write_table('RIGHTS',ddl) end ddl_endings() write_table('FOOTER',ddl) end -- else -- ##### Return results summary[#summary+1] = {'STOP'} -- Remove nulls from the summary table summary_new={} for i=1, #summary do if (string.match(summary[i][1], '%a')) then summary_new[#summary_new+1] = summary[i][1] else end end summary={{}} for i=1, #summary_new do summary[#summary+1] = {summary_new[i]} end table.remove(summary,1) return summary, "DDL varchar(2000000)" / --/ CREATE OR REPLACE LUA SCRIPT exa_toolbox."BACKUP_SYS" (file_location) RETURNS TABLE AS summary={} local suc1, res1 = pquery([[select schema_name, object_name from exa_syscat where instr(object_name,'EXA_USER_') = 0 and instr(object_name,'EXA_ALL_') = 0 and instr(object_name,'AUDIT') = 0 ; ]]) if suc1 then for i=1, #res1 do summary[#summary+1] = {[[export ]]..res1[i].SCHEMA_NAME..[[.]]..res1[i].OBJECT_NAME..[[ into local csv file ']]..file_location..[[]]..res1[i][2]..[[.csv.gz' truncate;]]} end else local error_msg = "ERR: ["..res1.error_code.."] "..res1.error_message exit(query([[SELECT :error as ERROR FROM DUAL;]], {error=error_msg})) end return summary, "DDL varchar(2000000)" / --/ CREATE OR REPLACE LUA SCRIPT exa_toolbox."RESTORE_SYS" RETURNS TABLE AS summary={} summary[#summary+1] = {[[ALTER SESSION SET PROFILE='on';]]} summary[#summary+1] = {[[CREATE SCHEMA IF NOT EXISTS SYS_OLD;]]} local suc1, res1 = pquery([[select schema_name, object_name from exa_syscat where instr(object_name,'EXA_USER_') = 0 and instr(object_name,'EXA_ALL_') = 0 and instr(object_name,'AUDIT') = 0 ; ]]) if suc1 then for i=1, #res1 do summary[#summary+1] = {[[create or replace table SYS_OLD.]]..res1[i][2]..[[ like ]]..res1[i][1]..[[.]]..res1[i][2]..[[;]]} summary[#summary+1] = {[[import into SYS_OLD.]]..res1[i][2]..[[ from local csv file ']]..'&'..[[1/]]..res1[i][2]..[[.csv.gz';]]} end else local error_msg = "ERR: ["..res1.error_code.."] "..res1.error_message exit(query([[SELECT :error as ERROR FROM DUAL;]], {error=error_msg})) end return summary, "DDL varchar(2000000)" /