-
Notifications
You must be signed in to change notification settings - Fork 13
/
create_db_ddl.sql
1175 lines (1041 loc) · 56.8 KB
/
create_db_ddl.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
/*
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