获取数据库或SHEME的DDL语句(二)
)
10 from user_objects u
11 where object_type = 'PROCEDURE';
12 spool off;
4. 获取HR下的全部函数的语法
1 set pagesize 0
2 set long 90000
3 set feedback off
4 set echo off
5 spool function.sql
6 connect HR/HR12345;
7 select DBMS_METADATA.GET_DDL('FUNCTION',u.object_name)
8 from user_objects u
9 where object_type = 'FUNCTION';
10 spool off;
5.得到所有表空间的ddl语句
1 set pagesize 0
2 set long 90000
3 set feedback off
4 set echo off
5 spool tablespace.sql
6
7 SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name)
8 FROM DBA_TABLESPACES TS;
9
10 spool off;
6.得到所有创建用户的ddl
1 set pagesize 0
2 set long 90000
3 set feedback off
4 set echo off
5 spool user.sql
6
7 SELECT DBMS_METADATA.GET_DDL('USER',U.username)
8 FROM DBA_USERS U;
9
10 spool off;
通过以上sql的组合，可以获取到一个数据库或SCHEMA的逻辑结构