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
| #!/bin/bash
getTabSql()
{
sqlplus -S / as sysdba >>./$logfile <<!
set heading off;
set echo off;
set pages 999;
set long 90000;
set linesize 1000;
column DDLS format a1000;
EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'PRETTY', TRUE);
EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'SQLTERMINATOR', TRUE);
EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'CONSTRAINTS', FALSE);
EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'REF_CONSTRAINTS', FALSE);
EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'CONSTRAINTS_AS_ALTER', FALSE);
EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'STORAGE', FALSE);
EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'TABLESPACE', FALSE);
EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'SEGMENT_ATTRIBUTES', FALSE);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
spool getsql.$1.tmp;
select dbms_metadata.get_ddl('TABLE','$1','$2') as ddls FROM DUAL;
spool off
exit
!
}
getIdxSql()
{
sqlplus -S / as sysdba >>./$logfile <<!
set heading off;
set echo off;
set pages 999;
set long 90000;
set linesize 1000;
column DDLS format a1000;
EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'PRETTY', TRUE);
EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'SQLTERMINATOR', TRUE);
EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'CONSTRAINTS', FALSE);
EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'REF_CONSTRAINTS', FALSE);
EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'CONSTRAINTS_AS_ALTER', FALSE);
EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'STORAGE', FALSE);
EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'TABLESPACE', FALSE);
EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'SEGMENT_ATTRIBUTES', FALSE);
spool getsql.$1.tmp;
select dbms_metadata.get_ddl('INDEX','$1','$2') as ddls FROM DUAL;
spool off
exit
!
}
getIdxName()
{
sqlplus -S / as sysdba >>./$logfile <<!
set echo off
set termout off
set trimspool on
set feedback off
set verify off
set pagesize 0
spool getsql.$1.idxname.tmp
select index_name from dba_indexes where table_name='$1' and owner='$2';
spool off
exit
!
}
rm -rf getsql.*.tmp
rm -rf getsql.log
logfile=getsql.log
for rec in `cat getsql.lst`
do
OWNER=`echo $rec|awk -F\. '{print $1}'`
TABNAME=`echo $rec|awk -F\. '{print $2}'`
typeset -u $OWNER
typeset -u $TABNAME
getTabSql $TABNAME $OWNER
getIdxName $TABNAME $OWNER
for rec in `cat getsql.$TABNAME.idxname.tmp`
do
IDXNAME=`echo $rec|awk '{print $1}'`
getIdxSql $IDXNAME $OWNER
cat getsql.$IDXNAME.tmp >> getsql.$TABNAME.tmp
done
sed 's/[ \t]*$//' getsql.$TABNAME.tmp|sed -e '/^$/d'>$TABNAME.sql
done
rm -rf getsql.*.tmp
|
